You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2026/10/09 17:39:23 UTC

[I] Incorrect results: ORDER BY b, a over a subquery with ORDER BY b LIMIT n is not sorted by (b, a) when SortPreservingMergeExec has a fetch [datafusion]

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: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Incorrect results: ORDER BY b, a over a subquery with ORDER BY b LIMIT n is not sorted by (b, a) when SortPreservingMergeExec has a fetch [datafusion]

Posted by "siddubakka (via GitHub)" <gi...@apache.org>.
siddubakka commented on issue #26162:
URL: https://github.com/apache/datafusion/issues/26162#issuecomment-6087401128

   > 5\. FYI [@siddubakka](https://github.com/siddubakka)  you probably need to wait for [#25408](https://github.com/apache/datafusion/pull/25408) to merge first
   okay i will w8 for that to merge 
   


-- 
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: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Incorrect results: ORDER BY b, a over a subquery with ORDER BY b LIMIT n is not sorted by (b, a) when SortPreservingMergeExec has a fetch [datafusion]

Posted by "siddubakka (via GitHub) via github" <gi...@datafusion.apache.org>.
siddubakka commented on issue #26162:
URL: https://github.com/apache/datafusion/issues/26162#issuecomment-6600931898

   @galaxytemple please take it, ive been busy with another PR and dont see myself starting soon


-- 
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: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Incorrect results: ORDER BY b, a over a subquery with ORDER BY b LIMIT n is not sorted by (b, a) when SortPreservingMergeExec has a fetch [datafusion]

Posted by "galaxytemple (via GitHub) via github" <gi...@datafusion.apache.org>.
galaxytemple commented on issue #26162:
URL: https://github.com/apache/datafusion/issues/26162#issuecomment-6615754358

   Thanks @siddubakka !


-- 
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: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Incorrect results: ORDER BY b, a over a subquery with ORDER BY b LIMIT n is not sorted by (b, a) when SortPreservingMergeExec has a fetch [datafusion]

Posted by "siddubakka (via GitHub)" <gi...@apache.org>.
siddubakka commented on issue #26162:
URL: https://github.com/apache/datafusion/issues/26162#issuecomment-6087069573

   take


-- 
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: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Incorrect results: ORDER BY b, a over a subquery with ORDER BY b LIMIT n is not sorted by (b, a) when SortPreservingMergeExec has a fetch [datafusion]

Posted by "jayzhan211 (via GitHub) via github" <gi...@datafusion.apache.org>.
jayzhan211 closed issue #26162: Incorrect results: ORDER BY b, a over a subquery with ORDER BY b LIMIT n is not sorted by (b, a) when SortPreservingMergeExec has a fetch
URL: https://github.com/apache/datafusion/issues/26162


-- 
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: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Incorrect results: ORDER BY b, a over a subquery with ORDER BY b LIMIT n is not sorted by (b, a) when SortPreservingMergeExec has a fetch [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #26162:
URL: https://github.com/apache/datafusion/issues/26162#issuecomment-6087319151

   FYI @siddubakka  you probably need to wait for https://github.com/apache/datafusion/pull/25408 to merge first
   


-- 
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: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Incorrect results: ORDER BY b, a over a subquery with ORDER BY b LIMIT n is not sorted by (b, a) when SortPreservingMergeExec has a fetch [datafusion]

Posted by "galaxytemple (via GitHub) via github" <gi...@datafusion.apache.org>.
galaxytemple commented on issue #26162:
URL: https://github.com/apache/datafusion/issues/26162#issuecomment-6588252365

   Hi @alamb, @siddubakka - since #25408 has merged, I was wondering if you're still planning to work on this? If not, I would like to take this.


-- 
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: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org