Rewriting SUM(x + c) to SUM(x) + c * COUNT(x) changes the result for floating point x

Describe the bug

With two or more SUM(x + <literal>) aggregates over the same x, the optimizer rewrites them to SUM(x) + <literal> * COUNT(x) (Sum::simplify_expr_op_literal, the ClickBench Q29 optimization). For Float64 this is not an equivalent transformation: floating point addition is not associative, so the rewritten expression can return a different value. The same aggregate returns one value on its own and another next to a similar aggregate.

This is related to #25943, which covers the same rewrite failing or changing the result type for intervals and decimals. Here the query succeeds and silently returns a different value.

To Reproduce

In datafusion-cli:

SELECT sum(x + 1.0) FROM (VALUES (1e16), (1.0), (-1e16)) AS t(x);
-- 2.0

SELECT sum(x + 1.0), sum(x + 2.0) FROM (VALUES (1e16), (1.0), (-1e16)) AS t(x);
-- 4.0 | 7.0

EXPLAIN of the second query shows the rewrite:

Projection: sum(t.x) + __common_expr_1 AS sum(t.x + Float64(1)), sum(t.x) + Float64(2) * __common_expr_1 AS sum(t.x + Float64(2))
  Projection: CAST(count(t.x) AS Float64) AS __common_expr_1, sum(t.x)
    Aggregate: groupBy=[[]], aggr=[[sum(t.x), count(t.x)]]

The values of x + 1.0 are 1e16 (1e16 + 1 rounds to 1e16), 2.0 and -1e16 (-1e16 + 1 rounds to -1e16), and their sum is 2.0. The rewrite instead computes sum(x) + 1.0 * count(x) = 1.0 + 3.0 = 4.0.

Expected behavior

sum(x + 1.0) returns 2.0 in both queries: the sum of the values of x + 1.0, independent of the other aggregates in the query.

For integer types the rewrite is exact (with wrapping arithmetic), so it could be skipped when the literal or the aggregate type is floating point.

Additional context

Reproduced on main at commit 6663e43 (debug build of datafusion-cli).

Willingness to contribute

None

take

I'll skip this rewrite when the aggregate argument or the literal is a floating point type, since it is only exact for integers. It touches the same lines as #25966, so I'll keep the change small and rebase on whichever lands first.

This is basically a standard floating point error accumulation, right? If the order of floating point operations is changed, the output may be (slightly different)

In other words I am not sure this is a bug

You're right, I worked through the example again. The exact value of sum(x + 1.0) over (1e16, 1.0, -1e16) is 4.0, which is what the rewritten plan returns. The 2.0 only appears because 1e16 + 1.0 rounds back to 1e16 before the sum, so the rewrite is not losing precision here, it just associates the additions differently, and the un-rewritten sum already depends on partition order. I'll close #26241. Happy for this issue to be closed as not a bug.

I understand the point about reassociation in the original example. I checked a finite-input overflow case on 6663e4348 (Rust 1.99.0) using the sqllogictest runner.

From datafusion/sqllogictest, create a headerless test_files/_sum_overflow_input.csv containing:

-1e308
-1e308

Then run:

SET datafusion.execution.target_partitions = 1;
SET datafusion.execution.batch_size = 8192;

CREATE EXTERNAL TABLE overflow_input (x DOUBLE)
STORED AS CSV LOCATION 'test_files/_sum_overflow_input.csv'
OPTIONS ('format.has_header' 'false');

SELECT SUM(x + 1e308) FROM overflow_input;
-- 0

SELECT SUM(x + 1e308), SUM(x + 5e307) FROM overflow_input;
-- NaN, -Infinity

EXPLAIN SELECT SUM(x + 1e308), SUM(x + 5e307) FROM overflow_input;

The paired query's plan scans the CSV, aggregates SUM(x) and COUNT(x), then projects SUM(x) + c * CAST(COUNT(x) AS Float64). I confirmed that x is Float64 and each original x + 1e308 is exactly zero, so that SUM is zero independently of reduction order. In the rewritten form, SUM(x) is -Infinity and 1e308 * COUNT(x) is Infinity, producing NaN.

With the same CSV and paired query:

Removing the whole SimplifyExpressions optimizer rule through with_optimizer_rules, retaining the other optimizer rules, gives 0, -1e308.
A local guard that skips this rewrite for floating-point literals also gives 0, -1e308.

These results were the same for (target_partitions, batch_size) = (1, 8192) and (4, 1). The latter plan uses RoundRobinBatch(4) and Partial/Final aggregation.

Is a rewrite-created NaN also an allowed consequence of reassociation when the original per-row expression and its SUM remain finite regardless of aggregation order?

@alamb

That overflow case looks different from the reassociation one to me: every x + 1e308 is exactly 0, so SUM(x + 1e308) is 0 in any summation order, while the rewritten form goes through SUM(x) = -Infinity and ends up NaN. The rewrite creates intermediate magnitudes the original expression never has. @alamb if you think that is worth guarding against, I can reopen #26241 with this overflow case as the test, the guard itself is the same.

@RishavTiwari25

The rewrite creates intermediate magnitudes the original expression never has

No I don't think this is worth guarding against as it can happen in other operations too (for example if the order of rows fed to an intermediate accumulator is changes).

Thanks for this report @RishavTiwari25 and the help with the analysis @Udonburo

Per discuss above, closing as not a bug

Navigation Menu

Repository navigation

Rewriting `SUM(x + c)` to `SUM(x) + c * COUNT(x)` changes the result for floating point `x` #26223

Describe the bug

To Reproduce

Expected behavior

Additional context

Willingness to contribute

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Rewriting SUM(x + c) to SUM(x) + c * COUNT(x) changes the result for floating point x #26223

Description

Describe the bug

To Reproduce

Expected behavior

Additional context

Willingness to contribute

Activity

RishavTiwari25 commented on Oct 12, 2026

alamb commented on Oct 12, 2026

RishavTiwari25 commented on Oct 12, 2026

Udonburo commented on Oct 13, 2026

RishavTiwari25 commented on Oct 13, 2026

alamb commented on Oct 13, 2026

alamb commented on Oct 13, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Rewriting `SUM(x + c)` to `SUM(x) + c * COUNT(x)` changes the result for floating point `x` #26223