Skip to content

EliminateCrossJoin drops NullEqualsNull from nested inner joins #25704

Description

@neilconway

Describe the bug

EliminateCrossJoin flattens every inner join beneath an inner-join root, merges all their equijoin keys, and rebuilds the joins using only the root join's null_equality. If a nested inner join carries NullEqualsNull, the rebuilt join silently downgrades it to NullEqualsNothing, and NULL keys stop matching.

To Reproduce

CREATE TABLE t1(a INT, b INT) AS VALUES (NULL, 1), (5, 2);
CREATE TABLE t2(a INT) AS VALUES (NULL), (5);
CREATE TABLE t3(b INT) AS VALUES (1), (2);

-- Correct: 2 rows, NULL matches NULL
SELECT t1.a, t1.b, t2.a
FROM t1 JOIN t2 ON t1.a IS NOT DISTINCT FROM t2.a
ORDER BY t1.b;

-- Wrong: 1 row, the NULL-key row is gone
SELECT t1.a, t1.b, t2.a, t3.b
FROM t1 JOIN t2 ON t1.a IS NOT DISTINCT FROM t2.a
        JOIN t3 ON t1.b = t3.b
ORDER BY t1.b;

Output:

+------+---+------+
| a    | b | a    |
+------+---+------+
| NULL | 1 | NULL |
| 5    | 2 | 5    |
+------+---+------+
2 row(s) fetched.
Elapsed 0.022 seconds.

+---+---+---+---+
| a | b | a | b |
+---+---+---+---+
| 5 | 2 | 5 | 2 |
+---+---+---+---+
1 row(s) fetched.
Elapsed 0.002 seconds.

Expected behavior

Both queries should return the NULL-key row.

Additional context

No response

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

bugSomething isn't working

Type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions