You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "1fanwang (via GitHub)" <gi...@apache.org> on 2026/10/02 10:33:27 UTC
[PR] fix: plan and run repeated SUM(x + literal) on decimals and intervals [datafusion]
1fanwang opened a new pull request, #25966:
URL: https://github.com/apache/datafusion/pull/25966
## Which issue does this PR close?
- Closes https://github.com/apache/datafusion/issues/25943.
## Rationale for this change
A query with two or more SUM(expr + literal) aggregates over the same interval or decimal expression fails or changes its result. Two interval sums fail to plan with "Invalid interval arithmetic operation: Interval(MonthDayNano) * Interval(MonthDayNano)". Two decimal sums can fail at execution with "12.0 is too large to store in a Decimal128 of precision 2", or return a different decimal type than the same SUM written alone. Each aggregate works when it is the only one in the query.
The cause is the ClickBench Q29 optimization from https://github.com/apache/datafusion/pull/20749, which only fires when several such aggregates share an expression. It rewrites each SUM(expr + literal) into SUM(expr) + literal * COUNT(expr), with COUNT cast to the literal's type. That cast is not valid for intervals, and for decimals it can overflow the literal's narrow type or change the result type.
## What changes are included in this PR?
The rewrite now applies only when the literal is an integer or floating-point value. Interval and decimal sums are left as written, so they plan, run and keep the result type they have on their own. Integer literals, the ClickBench Q29 case, are rewritten as before. Making the rewritten expression reproduce SUM's decimal result type exactly would be another option, but skipping the rewrite for these types is the smaller change.
## What is the testing strategy for this PR?
New cases in aggregates_simplify.slt run two interval sums, two decimal sums that overflowed, and two decimal sums whose type changed, next to the existing rewrite tests.
### Testing Done
The issue's queries in datafusion-cli, built with cargo build --profile ci -p datafusion-cli on main (416002a5b) and on this branch:
```shell
target/ci/datafusion-cli -f sum-literals.sql
```
On main, a single interval SUM works but two fail to plan, two decimal sums fail at execution, and the decimal result type changes from Decimal128(24, 5) to Decimal128(29, 10) when a second SUM is added:
```text
DataFusion CLI v55.1.0
0 row(s) fetched.
Elapsed 0.024 seconds.
+---------+
| a |
+---------+
| 90 days |
+---------+
1 row(s) fetched.
Elapsed 0.006 seconds.
Optimizer rule 'simplify_expressions' failed
caused by
Error during planning: Cannot get result type for temporal operation Interval(MonthDayNano) * Interval(MonthDayNano): Invalid argument error: Invalid interval arithmetic operation: Interval(MonthDayNano) * Interval(MonthDayNano)
0 row(s) fetched.
Elapsed 0.000 seconds.
Failed to cast field 'count(t.d)' from Int64 to Decimal128(2, 1)
caused by
Arrow error: Invalid argument error: 12.0 is too large to store in a Decimal128 of precision 2. Max is 9.9
+----------+-------------------+
| a | a_type |
+----------+-------------------+
| 20.00005 | Decimal128(24, 5) |
+----------+-------------------+
1 row(s) fetched.
Elapsed 0.004 seconds.
+---------------+--------------------+---------------+--------------------+
| a | a_type | b | b_type |
+---------------+--------------------+---------------+--------------------+
| 20.0000500000 | Decimal128(29, 10) | 25.0000500000 | Decimal128(29, 10) |
+---------------+--------------------+---------------+--------------------+
1 row(s) fetched.
Elapsed 0.005 seconds.
```
On this branch, every query runs and the multi-SUM results match the single SUM's values and types:
```text
DataFusion CLI v55.1.0
0 row(s) fetched.
Elapsed 0.021 seconds.
+---------+
| a |
+---------+
| 90 days |
+---------+
1 row(s) fetched.
Elapsed 0.005 seconds.
+---------+----------+
| a | b |
+---------+----------+
| 90 days | 102 days |
+---------+----------+
1 row(s) fetched.
Elapsed 0.003 seconds.
0 row(s) fetched.
Elapsed 0.000 seconds.
+-------+--------+
| a | b |
+-------+--------+
| 96.00 | 108.00 |
+-------+--------+
1 row(s) fetched.
Elapsed 0.002 seconds.
+----------+-------------------+
| a | a_type |
+----------+-------------------+
| 20.00005 | Decimal128(24, 5) |
+----------+-------------------+
1 row(s) fetched.
Elapsed 0.003 seconds.
+----------+-------------------+----------+-------------------+
| a | a_type | b | b_type |
+----------+-------------------+----------+-------------------+
| 20.00005 | Decimal128(24, 5) | 25.00005 | Decimal128(24, 5) |
+----------+-------------------+----------+-------------------+
1 row(s) fetched.
Elapsed 0.003 seconds.
```
<details>
<summary>Reproducer source: sum-literals.sql</summary>
```sql
CREATE TABLE t AS SELECT CAST(v AS DECIMAL(10,2)) AS d, CAST(v || ' days' AS INTERVAL) AS iv FROM generate_series(1, 12) AS g(v);
SELECT SUM(iv + INTERVAL '1 day') AS a FROM t;
SELECT SUM(iv + INTERVAL '1 day') AS a, SUM(iv + INTERVAL '2 days') AS b FROM t;
SET datafusion.sql_parser.parse_float_as_decimal = true;
SELECT SUM(d + 1.5) AS a, SUM(d + 2.5) AS b FROM t;
SELECT SUM(d + 1.00001) AS a, arrow_typeof(SUM(d + 1.00001)) AS a_type FROM t WHERE d <= 5;
SELECT SUM(d + 1.00001) AS a, arrow_typeof(SUM(d + 1.00001)) AS a_type, SUM(d + 2.00001) AS b, arrow_typeof(SUM(d + 2.00001)) AS b_type FROM t WHERE d <= 5;
```
</details>
## Are there any user-facing changes?
Yes. Queries with several SUM(expr + literal) aggregates over interval or decimal values now plan, run and keep the single-aggregate result type. No API changes.
--
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: [PR] fix: plan and run repeated SUM(x + literal) on decimals and intervals [datafusion]
Posted by "1fanwang (via GitHub)" <gi...@apache.org>.
1fanwang commented on code in PR #25966:
URL: https://github.com/apache/datafusion/pull/25966#discussion_r4356521748
##########
datafusion/functions-aggregate/src/sum.rs:
##########
@@ -397,6 +397,9 @@ impl AggregateUDFImpl for Sum {
if lit_type == DataType::Null {
return Ok(None);
}
+ if !(lit_type.is_integer() || lit_type.is_floating()) {
Review Comment:
Done in https://github.com/apache/datafusion/commit/895c66e56d2b96077ce284ec799340845f007c00.
--
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: [PR] fix: plan and run repeated SUM(x + literal) on decimals and intervals [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on code in PR #25966:
URL: https://github.com/apache/datafusion/pull/25966#discussion_r4299222153
##########
datafusion/functions-aggregate/src/sum.rs:
##########
@@ -397,6 +397,9 @@ impl AggregateUDFImpl for Sum {
if lit_type == DataType::Null {
return Ok(None);
}
+ if !(lit_type.is_integer() || lit_type.is_floating()) {
Review Comment:
Nit: a short comment here helps the next reader know why decimals and intervals are skipped.
```suggestion
// `CAST(COUNT(arg) AS <lit type>)` is not valid for intervals, and for
// decimals it can overflow or change the SUM result type.
// See https://github.com/apache/datafusion/issues/25943
if !(lit_type.is_integer() || lit_type.is_floating()) {
```
--
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: [PR] fix: plan and run repeated SUM(x + literal) on decimals and intervals [datafusion]
Posted by "codecov-commenter (via GitHub)" <gi...@apache.org>.
codecov-commenter commented on PR #25966:
URL: https://github.com/apache/datafusion/pull/25966#issuecomment-5950933749
## [Codecov](https://app.codecov.io/gh/apache/datafusion/pull/25966?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) Report
:white_check_mark: All modified and coverable lines are covered by tests.
:white_check_mark: Project coverage is 82.59%. Comparing base ([`416002a`](https://app.codecov.io/gh/apache/datafusion/commit/416002a5b908d990f0497ad4fc9d4438e513c210?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)) to head ([`7a4558d`](https://app.codecov.io/gh/apache/datafusion/commit/7a4558d258d9ade84301318240da0f183c49d4bc?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)).
:warning: Report is 2 commits behind head on main.
<details><summary>Additional details and impacted files</summary>
```diff
@@ Coverage Diff @@
## main #25966 +/- ##
==========================================
- Coverage 82.59% 82.59% -0.01%
==========================================
Files 1145 1145
Lines 444096 444099 +3
Branches 444096 444099 +3
==========================================
- Hits 366806 366803 -3
- Misses 55021 55024 +3
- Partials 22269 22272 +3
```
</details>
[:umbrella: View full report in Codecov by Harness](https://app.codecov.io/gh/apache/datafusion/pull/25966?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).
<details><summary> :rocket: New features to boost your workflow: </summary>
- :snowflake: [Test Analytics](https://docs.codecov.com/docs/test-analytics): Detect flaky tests, report on failures, and find test suite problems.
- :package: [JS Bundle Analysis](https://docs.codecov.com/docs/javascript-bundle-analysis): Save yourself from yourself by tracking and limiting bundle sizes in JS merges.
</details>
--
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: [PR] fix: plan and run repeated SUM(x + literal) on decimals and intervals [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb merged PR #25966:
URL: https://github.com/apache/datafusion/pull/25966
--
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