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.
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 │
└───────┴───────┴─────────┴───────┴───────┴─────────┘
Describe the bug
We don't handle nested
IS TRUEclauses correctly when converting outer joins to inner joins.To Reproduce
Expected behavior
DuckDB and Postgres produce the expected results:
Additional context
No response