Skip to content

Commit 20e143f

Browse files
committed
fix: decline sort pushdown through SortPreservingMergeExec with fetch (apache#26162)
SortPreservingMergeExec with fetch merges partitions using only its own keys, so pushing finer parent requirements down drops the needed outer sort. Declining pushdown keeps the parent SortExec to correctly order the final fetched rows.
1 parent 181a87a commit 20e143f

2 files changed

Lines changed: 47 additions & 14 deletions

File tree

‎datafusion/physical-optimizer/src/ensure_requirements/enforce_sorting/sort_pushdown.rs‎

Lines changed: 7 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -525,20 +525,13 @@ fn pushdown_requirement_to_children(
525525
Ok(None)
526526
}
527527
} else if plan.is::<SortPreservingMergeExec>() && plan.fetch().is_some() {
528-
// Push down through a fetched merge when:
529-
// - requirement is aligned with output ordering
530-
// - it preserves ordering during execution
531-
// Refining a fetched merge must preserve the ordering that selected its rows.
532-
let Some(ordering) = plan.output_ordering() else {
533-
return Ok(None);
534-
};
535-
Ok(plan
536-
.equivalence_properties()
537-
.requirements_compatible(
538-
parent_required.first().clone(),
539-
ordering.clone().into(),
540-
)
541-
.then(|| vec![Some(parent_required)]))
528+
// Why we decline pushdown here:
529+
// 1. SPM merges using only its own keys, so pushing additional
530+
// parent keys down cannot be preserved anyway.
531+
// 2. The subquery did not explicitly request the parent's sort, so forcing
532+
// it on the entire dataset before a fetch is an unnecessary cost.
533+
// 3. Decline pushdown so the parent sort is not dropped and orders the fetched rows.
534+
Ok(None)
542535
} else if is_union(plan) {
543536
// `UnionExec` does not have real sort requirements for its input, we
544537
// just propagate the sort requirements down:

‎datafusion/sqllogictest/test_files/limit.slt‎

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1331,3 +1331,43 @@ SET datafusion.optimizer.max_passes = 3;
13311331

13321332
statement ok
13331333
DROP TABLE t26431;
1334+
1335+
# Test for #26162: ORDER BY b, a over a subquery with ORDER BY b LIMIT n
1336+
# Ensure SortPreservingMergeExec with fetch does not push down finer sort requirements
1337+
statement ok
1338+
set datafusion.execution.target_partitions = 4;
1339+
1340+
statement ok
1341+
CREATE TABLE t_26162 AS
1342+
SELECT 1 AS a, 1 AS b UNION ALL SELECT 5, 2 UNION ALL SELECT 9, 3
1343+
UNION ALL SELECT 2, 1 UNION ALL SELECT 6, 2 UNION ALL SELECT 10, 3
1344+
UNION ALL SELECT 3, 1 UNION ALL SELECT 7, 2 UNION ALL SELECT 11, 3
1345+
UNION ALL SELECT 4, 1 UNION ALL SELECT 8, 2 UNION ALL SELECT 12, 3;
1346+
1347+
query II
1348+
SELECT * FROM (SELECT a, b FROM t_26162 ORDER BY b LIMIT 8) ORDER BY b, a;
1349+
----
1350+
1 1
1351+
2 1
1352+
3 1
1353+
4 1
1354+
5 2
1355+
6 2
1356+
7 2
1357+
8 2
1358+
1359+
query TT
1360+
EXPLAIN SELECT * FROM (SELECT a, b FROM t_26162 ORDER BY b LIMIT 8) ORDER BY b, a;
1361+
----
1362+
logical_plan
1363+
01)Sort: t_26162.b ASC NULLS LAST, t_26162.a ASC NULLS LAST
1364+
02)--Sort: t_26162.b ASC NULLS LAST, fetch=8
1365+
03)----TableScan: t_26162 projection=[a, b]
1366+
physical_plan
1367+
01)SortExec: expr=[b@1 ASC NULLS LAST, a@0 ASC NULLS LAST], preserve_partitioning=[false]
1368+
02)--SortPreservingMergeExec: [b@1 ASC NULLS LAST], fetch=8
1369+
03)----SortExec: TopK(fetch=8), expr=[b@1 ASC NULLS LAST], preserve_partitioning=[true]
1370+
04)------DataSourceExec: partitions=12, partition_sizes=[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
1371+
1372+
statement ok
1373+
DROP TABLE t_26162;

0 commit comments

Comments
 (0)