Repository navigation
Commit 52e4e5c
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
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
397 | 397 | | |
398 | 398 | | |
399 | 399 | | |
| 400 | + | |
| 401 | + | |
| 402 | + | |
| 403 | + | |
| 404 | + | |
| 405 | + | |
400 | 406 | | |
401 | 407 | | |
402 | 408 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
115 | 115 | | |
116 | 116 | | |
117 | 117 | | |
| 118 | + | |
| 119 | + | |
| 120 | + | |
| 121 | + | |
| 122 | + | |
| 123 | + | |
| 124 | + | |
| 125 | + | |
| 126 | + | |
| 127 | + | |
| 128 | + | |
| 129 | + | |
| 130 | + | |
| 131 | + | |
| 132 | + | |
| 133 | + | |
| 134 | + | |
| 135 | + | |
| 136 | + | |
| 137 | + | |
| 138 | + | |
| 139 | + | |
| 140 | + | |
| 141 | + | |
| 142 | + | |
| 143 | + | |
| 144 | + | |
| 145 | + | |
| 146 | + | |
| 147 | + | |
| 148 | + | |
| 149 | + | |
| 150 | + | |
| 151 | + | |
| 152 | + | |
| 153 | + | |
| 154 | + | |
| 155 | + | |
| 156 | + | |
| 157 | + | |
118 | 158 | | |
119 | 159 | | |
120 | 160 | | |
| |||
0 commit comments