Skip to content

Outer join elimination does not handle IS TRUE correctly #22441

@neilconway

Description

@neilconway

Describe the bug

We don't handle nested IS TRUE clauses correctly when converting outer joins to inner joins.

To Reproduce

DataFusion CLI v53.1.0
> create table t1(a int, b int, c varchar);
create table t2(x int, y int, z varchar);

insert into t1 values (1, 10, 'a'), (2, 20, 'b'), (3, 30, 'c'), (null, 40, 'd');
insert into t2 values (1, 100, 'p'), (2, 200, 'q'), (null, 300, 'r');

0 row(s) fetched.
Elapsed 0.057 seconds.

0 row(s) fetched.
Elapsed 0.001 seconds.

+-------+
| count |
+-------+
| 4     |
+-------+
1 row(s) fetched.
Elapsed 0.042 seconds.

+-------+
| count |
+-------+
| 3     |
+-------+
1 row(s) fetched.
Elapsed 0.001 seconds.

> select * from t1 left join t2 on t1.a = t2.x where not ((t2.y > 150) is true);
+---+----+---+---+-----+---+
| a | b  | c | x | y   | z |
+---+----+---+---+-----+---+
| 1 | 10 | a | 1 | 100 | p |
+---+----+---+---+-----+---+
1 row(s) fetched.
Elapsed 0.028 seconds.

Expected behavior

DuckDB and Postgres produce the expected results:

create table t1(a int, b int, c varchar);
create table t2(x int, y int, z varchar);
insert into t1 values (1, 10, 'a'), (2, 20, 'b'), (3, 30, 'c'), (null, 40, 'd');
insert into t2 values (1, 100, 'p'), (2, 200, 'q'), (null, 300, 'r');
create table t2(x int, y int, z varchar);

insert into t1 values (1, 10, 'a'), (2, 20, 'b'), (3, 30, 'c'), (null, 40, 'd');
insert into t2 values (1, 100, 'p'), (2, 200, 'q'), (null, 300, 'r');

select * from t1 left join t2 on t1.a = t2.x where not ((t2.y > 150) is true);
┌───────┬───────┬─────────┬───────┬───────┬─────────┐
│   a   │   b   │    c    │   x   │   y   │    z    │
│ int32 │ int32 │ varchar │ int32 │ int32 │ varchar │
├───────┼───────┼─────────┼───────┼───────┼─────────┤
│     1 │    10 │ a       │     1 │   100 │ p       │
│     3 │    30 │ c       │  NULL │  NULL │ NULL    │
│  NULL │    40 │ d       │  NULL │  NULL │ NULL    │
└───────┴───────┴─────────┴───────┴───────┴─────────┘

Additional context

No response

Metadata

Metadata

Assignees

Labels

bugSomething isn't working

Type

No fields configured for Bug.

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions