alamb opened a new issue, #26162: URL: https://github.com/apache/datafusion/issues/26162
### Describe the bug `ORDER BY b, a` on top of a subquery with `ORDER BY b LIMIT n` returns rows that are **not** sorted by `(b, a)` when the input has multiple partitions. The sort pushdown optimizer pushes the finer `[b, a]` requirement below a `SortPreservingMergeExec` that has a `fetch`, but the merge itself keeps its original key `[b]`. The resulting plan has no operator that actually orders the output by `(b, a)`. Found by @jayzhan211 while reviewing https://github.com/apache/datafusion/pull/25408 (see https://github.com/apache/datafusion/pull/25408#discussion_r4156318100). The bug is not introduced by that PR; it reproduces on `main` (754d3c1ae3) as well. That comment also discusses the root cause and possible fixes. ### To Reproduce Run the following in `datafusion-cli`: ```sql set datafusion.execution.target_partitions = 4; CREATE TABLE src (a INT, b INT) AS VALUES (1, 3), (2, 1), (3, 2), (4, 3), (5, 1), (6, 2), (7, 3), (8, 1); CREATE TABLE t AS SELECT a, b FROM src UNION ALL SELECT a + 10, b FROM src UNION ALL SELECT a + 20, b FROM src UNION ALL SELECT a + 30, b FROM src; SELECT * FROM (SELECT a, b FROM t ORDER BY b LIMIT 10) ORDER BY b, a; ``` **Actual output** (rows are not ordered by `a` within `b = 1`): ``` +----+---+ | a | b | +----+---+ | 2 | 1 | | 22 | 1 | | 5 | 1 | | 25 | 1 | | 8 | 1 | | 12 | 1 | | 15 | 1 | | 18 | 1 | | 28 | 1 | | 32 | 1 | +----+---+ ``` Plan: ```sql EXPLAIN SELECT * FROM (SELECT a, b FROM t ORDER BY b LIMIT 10) ORDER BY b, a; ``` ``` logical_plan | Sort: t.b ASC NULLS LAST, t.a ASC NULLS LAST | Sort: t.b ASC NULLS LAST, fetch=10 | TableScan: t projection=[a, b] physical_plan | SortPreservingMergeExec: [b@1 ASC NULLS LAST], fetch=10 | SortExec: TopK(fetch=10), expr=[b@1 ASC NULLS LAST, a@0 ASC NULLS LAST], preserve_partitioning=[true] | DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1] ``` Note the `SortPreservingMergeExec` merges on `[b]` only, while the `SortExec` below it was rewritten to `[b, a]`. ### Expected behavior The output should be ordered by `(b, a)`: ``` +----+---+ | a | b | +----+---+ | 2 | 1 | | 5 | 1 | | 8 | 1 | | 12 | 1 | | 15 | 1 | | 18 | 1 | | 22 | 1 | | 25 | 1 | | 28 | 1 | | 32 | 1 | +----+---+ ``` ### Additional context - Root cause analysis and suggested fixes: https://github.com/apache/datafusion/pull/25408#discussion_r4156318100 - Related PR: https://github.com/apache/datafusion/pull/25408 - Relevant code: `datafusion/physical-optimizer/src/ensure_requirements/enforce_sorting/sort_pushdown.rs` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
