Incorrect result by subquery and join

[DerZc]

Describe the problem

consider the following program:

CREATE DATABASE database1;
USE database1;

CREATE TABLE t0 (c0 SERIAL2);
CREATE TABLE t1 (c0 BYTES);

UPSERT INTO t1 (rowid) VALUES(1);

SELECT temp_table3.c2, temp_table3.c0 FROM (SELECT t1.c0 AS c0, t1.rowid AS c2 FROM t0 FULL OUTER JOIN t1 ON true ) AS temp_table3, t1 WHERE (temp_table3.c2) = (t1.rowid);  -- 1 | NULL

WITH temp_table3(c0, c2) AS (SELECT t1.c0 AS c0, t1.rowid AS c2 FROM t0 FULL OUTER JOIN t1 ON true ) SELECT ALL temp_table3.c2, temp_table3.c0 FROM temp_table3, t1 WHERE (temp_table3.c2) = (t1.rowid);  -- empty result

There two queries are equivalent and just put the subquery at different place. but the second query has empty results. I manually analyze it and think it should have the same result with the first query.

Also, if I remove the output column temp_table3.c0 of the second query, it will have 1 as result. This should not affect the result.

To Reproduce

this bug can reproduce with the last commit version (07c7d4b) and last release version (v22.2.7).

I run the cockroachdb in a single machine and execute this program with CLI

Expected behavior
The second query has the same results with the first one.

Environment:

• CockroachDB version [last commit version and last release version]
• Server OS: [ubuntu 22.04]
• Client app [CLI]

Jira issue: CRDB-26494

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[yuzefovich]

Thanks for the report! Indeed, both queries seem to be equivalent, and I believe they should return 1 row in the result. I also confirmed that this bug is present on master, 22.2.6, and 22.1.13, so it's not a recent regression.

[DerZc]

Thank you for your confirmation. Very happy this report was helpful to you.

[mgartner]

Here's a simplified reproduction:

CREATE TABLE a (a INT);
CREATE TABLE bc (b INT, c INT);

INSERT INTO bc (b) VALUES(1);

SELECT * FROM (
  SELECT bc.c + 1 AS y, bc.b + 1 AS x
  FROM a FULL OUTER JOIN bc ON true
) tmp, bc
WHERE tmp.x = bc.b + 1;

It returns a single row in Postgres but no rows in CRDB.

[mgartner]

The bug is in the RejectNullsProject rule. In some cases it can create null-rejecting filters for all of the columns that are candidates for null-rejection, not just the ones that can actually be null-rejected. The fix should be fairly simple.

I'm still trying to figure out how easy it is to hit this bug. It's been present since the RejectNullsProject rule was introduced in 20.2, which tells me that it's very rare. It's also difficult to concoct other reproductions.