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: [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]
