Repository navigation
FULL OUTER JOIN and LIMIT produces wrong results #14335
Description
Describe the bug
LIMITs are incorrectly pushed through FULL OUTER Joins
To Reproduce
COPY (values (1), (2), (3), (4), (5)) TO '/tmp/t1.csv' STORED AS CSV;
-- store t2 in different order so the top N rows are not the same as the top N rows of t1
COPY (values (5), (4), (3), (2), (1)) TO '/tmp/t2.csv' STORED AS CSV;
statement ok
create external table t1(a int) stored as CSV location '/tmp/t1.csv';
statement ok
create external table t2(b int) stored as CSV location '/tmp/t2.csv';
-- FULL join produces 5 rows (all have matches)
select * from t1 FULL JOIN t2 ON t1.a = t2.b;
/*
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+---+---+
*/
-- the output of this query should be two rows from the previous query, there
-- should be no nulls
select * from t1 FULL JOIN t2 ON t1.a = t2.b LIMIT 2;
/*
+------+------+
| a | b |
+------+------+
| 1 | NULL |
| NULL | 4 |
+------+------+
*/
Expected behavior
Any two rows from
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+---+---+There should be no nulls introduced
Additional context
Found while working with @zhuqi-lucas on:
Activity
alamb commentedon Jan 28, 2025
I think @zhuqi-lucas found the issue. This line is almost certainly wrong:
datafusion/datafusion/optimizer/src/push_down_limit.rs
Line 258 in e3db359
alamb commentedon Jan 28, 2025
I wrote some tests for this here:
alamb commentedon Jan 28, 2025
I don't think this is a regression (it has been like this for while)
It seems to have been introduced in
alamb commentedon Jan 28, 2025
I also filed this ticket to track making it easier to test for this kind of thing
fetchlimit for MemoryExec #14337zhuqi-lucas commentedon Jan 28, 2025
take
zhuqi-lucas commentedon Jan 28, 2025
Thank you @alamb!
I want to take this task, and for the first step, i think we should disable full outer join push down limit.
For further improvement, we can investigate if we have some cases which we can support push down limit for full outer join.
zhuqi-lucas commentedon Jan 28, 2025
Submitted a PR for review!