Skip to content

Commit 52e4e5c

Browse files
authored
fix: plan and run repeated SUM(x + literal) on decimals and intervals (apache#25966)
## Which issue does this PR close? - Closes apache#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 apache#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 (416002a) 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. --------- Signed-off-by: 1fanwang <[email protected]>
1 parent a893f40 commit 52e4e5c

2 files changed

Lines changed: 46 additions & 0 deletions

File tree

‎datafusion/functions-aggregate/src/sum.rs‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -397,6 +397,12 @@ impl AggregateUDFImpl for Sum {
397397
if lit_type == DataType::Null {
398398
return Ok(None);
399399
}
400+
// `CAST(COUNT(arg) AS <lit type>)` is not valid for intervals, and for
401+
// decimals it can overflow or change the SUM result type.
402+
// See https://github.com/apache/datafusion/issues/25943
403+
if !(lit_type.is_integer() || lit_type.is_floating()) {
404+
return Ok(None);
405+
}
400406

401407
// Build up SUM(arg)
402408
let mut sum_agg = agg_function.clone();

‎datafusion/sqllogictest/test_files/aggregates_simplify.slt‎

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -115,6 +115,46 @@ physical_plan
115115
02)--AggregateExec: mode=Single, gby=[], aggr=[sum(sum_simplify_t.column1), count(sum_simplify_t.column1)]
116116
03)----DataSourceExec: partitions=1, partition_sizes=[1]
117117

118+
statement ok
119+
CREATE TABLE sum_simplify_decimal_interval_t AS
120+
SELECT
121+
CAST(v AS DECIMAL(10,2)) AS d,
122+
CAST(v || ' days' AS INTERVAL) AS iv
123+
FROM generate_series(1, 12) AS g(v);
124+
125+
query ??
126+
SELECT
127+
SUM(iv + INTERVAL '1 day') AS a,
128+
SUM(iv + INTERVAL '2 days') AS b
129+
FROM sum_simplify_decimal_interval_t;
130+
----
131+
90 days 102 days
132+
133+
statement ok
134+
SET datafusion.sql_parser.parse_float_as_decimal = true;
135+
136+
query RR
137+
SELECT
138+
SUM(d + 1.5) AS a,
139+
SUM(d + 2.5) AS b
140+
FROM sum_simplify_decimal_interval_t;
141+
----
142+
96.00 108.00
143+
144+
query RTRT
145+
SELECT
146+
SUM(d + 1.00001) AS a,
147+
arrow_typeof(SUM(d + 1.00001)) AS a_type,
148+
SUM(d + 2.00001) AS b,
149+
arrow_typeof(SUM(d + 2.00001)) AS b_type
150+
FROM sum_simplify_decimal_interval_t
151+
WHERE d <= 5;
152+
----
153+
20.00005 Decimal128(24, 5) 25.00005 Decimal128(24, 5)
154+
155+
statement ok
156+
SET datafusion.sql_parser.parse_float_as_decimal = false;
157+
118158
# Reordered expressions that still compute the same thing
119159
query II
120160
SELECT SUM(1 + column1), SUM(column1 + 2) FROM sum_simplify_t;

0 commit comments

Comments
 (0)