Repository navigation
Sort Pushdown Causes an Invalid Plan #12558
Description
Describe the bug
Please see the reproducers below.
To Reproduce
statement ok
CREATE TABLE left_table(a INT, b INT, c INT)
statement ok
CREATE TABLE right_table(x INT, y INT, z INT)
query TT
EXPLAIN SELECT * FROM left_table JOIN right_table ON left_table.a<right_table.x ORDER BY x;
----
physical_plan
01)NestedLoopJoinExec: join_type=Inner, filter=a@0 < x@1
02)--MemoryExec: partitions=1, partition_sizes=[0]
03)--SortExec: expr=[x@3 ASC NULLS LAST], preserve_partitioning=[false]
04)----MemoryExec: partitions=1, partition_sizes=[0]
query TT
EXPLAIN SELECT * FROM left_table JOIN right_table ON left_table.a<right_table.x AND left_table.b=right_table.y ORDER BY x;
----
physical_plan
01)SortExec: expr=[x@3 ASC NULLS LAST], preserve_partitioning=[false]
02)--CoalesceBatchesExec: target_batch_size=1024
03)----HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(b@1, y@1)], filter=a@0 < x@1
04)------MemoryExec: partitions=1, partition_sizes=[0]
05)------MemoryExec: partitions=1, partition_sizes=[0]
Expected behavior
NestedLoopJoinExec pushes down the SortExec without adjusting its sort expression, so it became an invalid plan. It should be fixed.
HashJoinExec cannot push down the sort, therefore sort is applied over a larger data. It could actually push down the sort. If the source is ordered, sort is even removed thoroughly.
Additional context
No response
Activity