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/02/09 19:41:12 UTC
[I] Incorrect answers due to an incorrectly removed Sort before AggregateExec [datafusion]
alamb opened a new issue, #20244:
URL: https://github.com/apache/datafusion/issues/20244
### Describe the bug
While debugging the DataFusion 52 upgrade, I found a wrong results bug with pre-sorted data that was introduced in 52
### To Reproduce
```sql
CREATE TABLE agg_src(x INT, y INT, v INT) AS VALUES
(1, 1, 10),
(1, 2, 20),
(1, 3, 30),
(2, 1, 40),
(2, 2, 50),
(2, 3, 60);
-- Create an ordered table:
COPY (SELECT * FROM agg_src ORDER BY x, y) TO 'foo.parquet';
```
Then run
```sql
CREATE EXTERNAL TABLE agg_src_sorted(x INT, y INT, v INT) STORED AS PARQUET LOCATION 'foo.parquet' WITH ORDER (x ASC, y ASC);
-- This query orders by an expresson of y that breaks the ordering
SELECT
x,
CAST(y AS BIGINT) % 2,
SUM(v)
FROM agg_src_sorted
GROUP BY x, CAST(y AS BIGINT) % 2
ORDER BY x, CAST(y AS BIGINT) % 2;
```
With Datafusion 52, you get the wrong answer:
```shell
andrewlamb@Andrews-MacBook-Pro-3 ~ % ~/Software/datafusion-cli/datafusion-cli-52.1.0
```
```sql
> SELECT
x,
CAST(y AS BIGINT) % 2,
SUM(v)
FROM agg_src_sorted
GROUP BY x, CAST(y AS BIGINT) % 2
ORDER BY x, CAST(y AS BIGINT) % 2;
+---+-----------------------------+-----------------------+
| x | agg_src_sorted.y % Int64(2) | sum(agg_src_sorted.v) |
+---+-----------------------------+-----------------------+
| 1 | 1 | 40 |
| 1 | 0 | 20 | <---- the second column is 1 then 0, rather than 0 then 1
| 2 | 1 | 100 |
| 2 | 0 | 50 |
+---+-----------------------------+-----------------------+
4 row(s) fetched.
Elapsed 0.006 seconds.
```
On datafusion 51
```shell
andrewlamb@Andrews-MacBook-Pro-3 ~ % ~/Software/datafusion-cli/datafusion-cli-51.0.0
```
You get the expected answer
```shell
> SELECT
x,
CAST(y AS BIGINT) % 2,
SUM(v)
FROM agg_src_sorted
GROUP BY x, CAST(y AS BIGINT) % 2
ORDER BY x, CAST(y AS BIGINT) % 2;
+---+-----------------------------+-----------------------+
| x | agg_src_sorted.y % Int64(2) | sum(agg_src_sorted.v) |
+---+-----------------------------+-----------------------+
| 1 | 0 | 20 | <---- this row is in the correct sopt
| 1 | 1 | 40 |
| 2 | 0 | 50 |
| 2 | 1 | 100 |
+---+-----------------------------+-----------------------+
4 row(s) fetched.
Elapsed 0.002 seconds.
### Expected behavior
_No response_
### Additional context
_No response_
--
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 answers due to an incorrectly removed Sort before AggregateExec [datafusion]
Posted by "akurmustafa (via GitHub)" <gi...@apache.org>.
akurmustafa closed issue #20244: Incorrect answers due to an incorrectly removed Sort before AggregateExec
URL: https://github.com/apache/datafusion/issues/20244
--
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 answers due to an incorrectly removed Sort before AggregateExec [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #20244:
URL: https://github.com/apache/datafusion/issues/20244#issuecomment-3873500678
This still exists on main as well
--
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 answers due to an incorrectly removed Sort before AggregateExec [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #20244:
URL: https://github.com/apache/datafusion/issues/20244#issuecomment-3883930089
Added as a candidate to the 52.2.0 patch release
- https://github.com/apache/datafusion/issues/20287
--
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 answers due to an incorrectly removed Sort before AggregateExec [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #20244:
URL: https://github.com/apache/datafusion/issues/20244#issuecomment-3873843788
I have two proposed fixed
- https://github.com/apache/datafusion/pull/20245 (big hammer)
- https://github.com/apache/datafusion/pull/20247
I am now trying to figure out what the root cause was / why this wasn't detected before
--
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 answers due to an incorrectly removed Sort before AggregateExec [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #20244:
URL: https://github.com/apache/datafusion/issues/20244#issuecomment-3879087236
I used codex to track down that this issue started appearing with this issue
- eb30c19b30 / https://github.com/apache/datafusion/pull/19287
Specifically, at
- 6fa9c1ad11: EXPLAIN has SortExec above AggregateExec, output is correctly ordered.
```sql
+---+-----------------------------+-----------------------+
| x | agg_src_sorted.y % Int64(2) | sum(agg_src_sorted.v) |
+---+-----------------------------+-----------------------+
| 1 | 0 | 20 |
| 1 | 1 | 40 |
| 2 | 0 | 50 |
| 2 | 1 | 100 |
+---+-----------------------------+-----------------------+
explain SELECT
x,
CAST(y AS BIGINT) % 2,
SUM(v)
FROM agg_src_sorted
GROUP BY x, CAST(y AS BIGINT) % 2
ORDER BY x, CAST(y AS BIGINT) % 2;
+---------------+-------------------------------+
| plan_type | plan |
+---------------+-------------------------------+
| physical_plan | ┌───────────────────────────┐ |
| | │ SortExec │ |
| | │ -------------------- │ |
| | │ x@0 ASC NULLS LAST, │ |
| | │ agg_src_sorted.y │ |
| | │ % Int64(2)@1 ASC NULLS │ |
| | │ LAST │ |
| | └─────────────┬─────────────┘ |
| | ┌─────────────┴─────────────┐ |
| | │ AggregateExec │ |
| | │ -------------------- │ |
| | │ aggr: │ |
| | │ sum(agg_src_sorted.v) │ |
| | │ │ |
| | │ group_by: │ |
| | │ x, CAST(y AS Int64) % 2 as│ |
| | │ agg_src_sorted.y % Int64 │ |
| | │ (2) │ |
| | │ │ |
| | │ mode: Single │ |
| | └─────────────┬─────────────┘ |
| | ┌─────────────┴─────────────┐ |
| | │ DataSourceExec │ |
| | │ -------------------- │ |
| | │ files: 1 │ |
| | │ format: parquet │ |
| | └───────────────────────────┘ |
| | |
+---------------+-------------------------------+
1 row(s) fetched.
Elapsed 0.026 seconds.
```
Then, at the next commit here
- eb30c19b30: SortExec is gone, output is misordered:
```sql
SELECT
x,
CAST(y AS BIGINT) % 2,
SUM(v)
FROM agg_src_sorted
GROUP BY x, CAST(y AS BIGINT) % 2
ORDER BY x, CAST(y AS BIGINT) % 2;
+---+-----------------------------+-----------------------+
| x | agg_src_sorted.y % Int64(2) | sum(agg_src_sorted.v) |
+---+-----------------------------+-----------------------+
| 1 | 1 | 40 |
| 1 | 0 | 20 |
| 2 | 1 | 100 |
| 2 | 0 | 50 |
+---+-----------------------------+-----------------------+
4 row(s) fetched.
Elapsed 0.029 seconds.
> -- This query orders by an expresson of y that breaks the ordering
explain SELECT
x,
CAST(y AS BIGINT) % 2,
SUM(v)
FROM agg_src_sorted
GROUP BY x, CAST(y AS BIGINT) % 2
ORDER BY x, CAST(y AS BIGINT) % 2;
+---------------+-------------------------------+
| plan_type | plan |
+---------------+-------------------------------+
| physical_plan | ┌───────────────────────────┐ |
| | │ AggregateExec │ |
| | │ -------------------- │ |
| | │ aggr: │ |
| | │ sum(agg_src_sorted.v) │ |
| | │ │ |
| | │ group_by: │ |
| | │ x, CAST(y AS Int64) % 2 as│ |
| | │ agg_src_sorted.y % Int64 │ |
| | │ (2) │ |
| | │ │ |
| | │ mode: Single │ |
| | └─────────────┬─────────────┘ |
| | ┌─────────────┴─────────────┐ |
| | │ DataSourceExec │ |
| | │ -------------------- │ |
| | │ files: 1 │ |
| | │ format: parquet │ |
| | └───────────────────────────┘ |
| | |
+---------------+-------------------------------+
1 row(s) fetched.
Elapsed 0.016 seconds.
```
--
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