opt: left lookup join pair does not correctly null-extend unmatched rows #81968
Description
Describe the problem
Found a problem with subqueries which is using columns of the main query table.
To Reproduce
MRE:
CREATE TABLE items (
id INT NOT NULL PRIMARY KEY,
chat_id INT NOT NULL,
author_id INT NOT NULL,
INDEX chat_id_idx (chat_id) -- without this index everything works fine
);
CREATE TABLE views (
chat_id INT NOT NULL,
user_id INT NOT NULL,
PRIMARY KEY (chat_id, user_id)
);
-- create view of user(1) to chat(1)
INSERT INTO views(chat_id, user_id) VALUES (1, 1);
-- user(1) inserted items to chat(1)
INSERT INTO items(id, chat_id, author_id) VALUES
(1, 1, 1),
(2, 1, 1),
(3, 1, 1);
-- following request should return 0 records, because there is no items related to any other user.
-- but result is 1
SELECT (SELECT COUNT(items.id)
FROM items
WHERE items.chat_id = views.chat_id
AND items.author_id != views.user_id)
FROM views
WHERE chat_id = 1
AND user_id = 1;
-- flatten version of the same query works fine
SELECT COUNT(id)
FROM items
WHERE chat_id = 1
AND author_id != 1;Expected behavior
Select returns 0.
Additional data / screenshots
cockroach sql session log:
sh-4.4# cockroach sql --insecure
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v22.1.0 (x86_64-pc-linux-gnu, built 2022/05/23 16:27:47, go1.17.6) (same version as client)
# Cluster ID: ddfc36f6-1cca-48ad-b1b2-da3f90493b5e
#
# Enter \? for a brief introduction.
#
root@:26257/defaultdb> CREATE TABLE items (id INT NOT NULL PRIMARY KEY, chat_id INT NOT NULL, author_id INT NOT NULL, INDEX chat_id_idx (chat_id)); CREATE TABLE views (chat_id INT NOT NULL, user_id INT NOT NULL, PRIMARY KEY (chat_id, user_id));
CREATE TABLE
Time: 57ms total (execution 55ms / network 2ms)
CREATE TABLE
Time: 16ms total (execution 16ms / network 0ms)
root@:26257/defaultdb> INSERT INTO views(chat_id, user_id) VALUES (1, 1);
INSERT 1
Time: 75ms total (execution 74ms / network 1ms)
root@:26257/defaultdb> INSERT INTO items(id, chat_id, author_id) VALUES (1, 1, 1), (2, 1, 1), (3, 1, 1);
INSERT 3
Time: 47ms total (execution 46ms / network 1ms)
root@:26257/defaultdb> SELECT (SELECT COUNT(items.id) FROM items WHERE items.chat_id = views.chat_id AND items.author_id != views.user_id) FROM views WHERE chat_id = 1 AND user_id = 1;
count
---------
1
(1 row)
Time: 55ms total (execution 52ms / network 3ms)
root@:26257/defaultdb> SELECT COUNT(id) FROM items WHERE chat_id = 1 AND author_id != 1;
count
---------
0
(1 row)
Time: 23ms total (execution 22ms / network 1ms)
Log of the same execution but without chat_id_idx index:
root@:26257/defaultdb> CREATE TABLE items (id INT NOT NULL PRIMARY KEY, chat_id INT NOT NULL, author_id INT NOT NULL);
CREATE TABLE
Time: 53ms total (execution 52ms / network 1ms)
root@:26257/defaultdb> CREATE TABLE views (chat_id INT NOT NULL, user_id INT NOT NULL, PRIMARY KEY (chat_id, user_id));
CREATE TABLE
Time: 43ms total (execution 42ms / network 1ms)
root@:26257/defaultdb> INSERT INTO views(chat_id, user_id) VALUES (1, 1);
INSERT 1
Time: 47ms total (execution 46ms / network 1ms)
root@:26257/defaultdb> INSERT INTO items(id, chat_id, author_id) VALUES (1, 1, 1), (2, 1, 1), (3, 1, 1);
INSERT 3
Time: 43ms total (execution 42ms / network 1ms)
root@:26257/defaultdb> SELECT (SELECT COUNT(items.id) FROM items WHERE items.chat_id = views.chat_id AND items.author_id != views.user_id) FROM views WHERE chat_id = 1 AND user_id = 1;
count
---------
0
(1 row)
Time: 62ms total (execution 61ms / network 1ms)
Environment:
- CockroachDB version [v22.1.0]
- Server OS: [cockroachdb/cockroach docker image]
- Client app [
cockroach sql]
Additional context
The problem is not reproducible on the previous versions of CockroachDB (latest-v21.2 image tag), so I think some bug in the last update.
Jira issue: CRDB-16146
Metadata
Metadata
Assignees
Labels
Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.Caused a technical advisoryOriginated from the communityDatabase stores inconsistent data in some cases, or queries return invalid results silently.SQL Queries TeamUsed to mark GA and release blockers, technical advisories, and bugs for 22.1Indicates a release-blocker. Use with branch-release-2x.x label to denote which branch is blocked.
Type
Projects
Status