Closed
Description
Describe the bug
see the plan
| physical_plan after OutputRequirements | GlobalLimitExec: skip=0, fetch=10 |
| | ProjectionExec: expr=[a@2 as a, b@3 as b, a@0 as a, b@1 as b] |
| | CrossJoinExec |
| | GlobalLimitExec: skip=0, fetch=1 |
| | MemoryExec: partitions=1, partition_sizes=[1] |
| | GlobalLimitExec: skip=0, fetch=10 |
| | MemoryExec: partitions=1, partition_sizes=[1] |
| | |
| physical_plan after LimitAggregation | SAME TEXT AS ABOVE |
| physical_plan after ProjectionPushdown | SAME TEXT AS ABOVE |
| physical_plan after LimitPushdown | ProjectionExec: expr=[a@2 as a, b@3 as b, a@0 as a, b@1 as b] |
| | GlobalLimitExec: skip=0, fetch=10 |
| | CrossJoinExec |
| | MemoryExec: partitions=1, partition_sizes=[1] |
| | MemoryExec: partitions=1, partition_sizes=[1]
To Reproduce
> create table t(a int, b int) as values (1,2), (2,3), (3,4);
0 row(s) fetched.
Elapsed 0.036 seconds.
> select * from t as t1 join (select * from t limit 1) limit 10;
+---+---+---+---+
| a | b | a | b |
+---+---+---+---+
| 1 | 2 | 1 | 2 |
| 2 | 3 | 1 | 2 |
| 3 | 4 | 1 | 2 |
| 1 | 2 | 2 | 3 |
| 2 | 3 | 2 | 3 |
| 3 | 4 | 2 | 3 |
| 1 | 2 | 3 | 4 |
| 2 | 3 | 3 | 4 |
| 3 | 4 | 3 | 4 |
+---+---+---+---+
9 row(s) fetched.
Elapsed 0.013 seconds.Expected behavior
check in datafusion v43, v44 and main, both have this problem
Additional context
if add a order by, the result will correct
> create table t(a int, b int) as values (1,2), (2,3), (3,4);
0 row(s) fetched.
Elapsed 0.036 seconds.
> select * from t as t1 join (select * from t limit 1) limit 10;
+---+---+---+---+
| a | b | a | b |
+---+---+---+---+
| 1 | 2 | 1 | 2 |
| 2 | 3 | 1 | 2 |
| 3 | 4 | 1 | 2 |
| 1 | 2 | 2 | 3 |
| 2 | 3 | 2 | 3 |
| 3 | 4 | 2 | 3 |
| 1 | 2 | 3 | 4 |
| 2 | 3 | 3 | 4 |
| 3 | 4 | 3 | 4 |
+---+---+---+---+
9 row(s) fetched.
Elapsed 0.013 seconds.
> select * from t as t1 join (select * from t order by a limit 1) limit 10;
+---+---+---+---+
| a | b | a | b |
+---+---+---+---+
| 1 | 2 | 1 | 2 |
| 2 | 3 | 1 | 2 |
| 3 | 4 | 1 | 2 |
+---+---+---+---+
3 row(s) fetched.
Elapsed 0.014 seconds.