Unexpected Result by UNION

[bajinsheng]

Describe the problem

An unexpected result in UNION.
The third SELECT is the UNION of above two SELECTs, but the result is not the sum of the results of above two SELECTs.

To Reproduce

CREATE TABLE t1 (c0 BOOL AS (1 IS NULL) STORED, CONSTRAINT "primary" PRIMARY KEY(c0));
CREATE TABLE t2 (c0 INT);
INSERT INTO t2 (c0) VALUES(1);
INSERT INTO t1 DEFAULT VALUES;


SELECT t1.c0 FROM t2 FULL OUTER JOIN t1 ON false WHERE false IN (t1.c0 IS NULL); -- {f}
SELECT t1.c0 FROM t2 FULL OUTER JOIN t1 ON false WHERE NOT false IN (t1.c0 IS NULL); -- {NULL}

SELECT t1.c0 FROM t2 FULL OUTER JOIN t1 ON false WHERE false IN (t1.c0 IS NULL) UNION SELECT t1.c0 FROM t2 FULL OUTER JOIN t1 ON false WHERE NOT false IN (t1.c0 IS NULL); -- {f}

Expected behavior
The result of the third SELECT should be {f}, {NULL}, while it only return one row.

Additional data / screenshots

If applicable, add screenshots to help explain your problem.

Environment:

• CockroachDB version [5cd0309]
• Server OS: [Ubuntu 20.04]
• Client app [cockroach sql]

Additional context

Jira issue: CRDB-18273

[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 otan.}

[yuzefovich]

Thanks for the report!

The bug reproduces on 22.1.5 and 21.2.14, but not on 21.1.19, will take a closer look.

[yuzefovich]

I think ConvertUnionToDistinctUnionAll normalization rule, introduced in #64976, is to blame. The first condition for that rule to fire is that All columns from both inputs originate from the same base table, but it doesn't look like it's the case here (i.e. the conditions seem to be violated, yet the rule is applied):

cc @DrewKimball