SUM(x + C) rewrite - #15017

…low errors (#15040)

Follow-up fix from #15017

Fixes an internally found issue

SUM(x + C) rewrite (duckdb/duckdb#15017)

SUM(x + C) rewrite (duckdb/duckdb#15017)

SUM(x + C) rewrite (duckdb/duckdb#15017)

Co-authored-by: krlmlr <[email protected]>

…0749)

## Which issue does this PR close?

- Part of #18489 
- Closes #20180
- Closes #15524
- Replaces #20665

## Rationale for this change

I [want DataFusion to be the fastest parquet engine on
ClickBench](#18489). One of
the queries where DataFusion is significantly slower is Query 29 which
has a very strange pattern of many aggregate functions that are offset
by a constant:


https://github.com/apache/datafusion/blob/0ca9d6586a43c323525b2e299448e0f1af4d6195/benchmarks/queries/clickbench/queries/q29.sql#L4

This is not a pattern I have ever seen in a real query, but it seems
like the engine currently at the top of the ClickBench leaderboard has a
special case for this pattern. ClickHouse probably does too. See
- duckdb/duckdb#15017
- Discussion on #15524

Thus I reluctantly conclude that we should have one too. 

## What changes are included in this PR?

This is an alternate to my first attempt. 
 - #20665

In particular, since this is such a ClickBench specific rule, I wanted
to
1. Minimize the downstream API / upgrade impact (aka not change existing
APIs)
2. Optimize performance for the case where this rewrite will not apply
(most times)

1. Add a rewrite `SUM(expr + scalar)` --> `SUM(expr) +
scalar*COUNT(expr)`
3. Tests for same

Note there are quite a few other ideas to potentially make this more
general on #15524 but I am
going with the simple thing of making it work for the usecase we have in
hand (ClickBench)

## Are these changes tested?

Yes, new tests are added

## Are there any user-facing changes?

Faster performance

🚀 
```
│ QQuery 29 │  1012.63 ms │                 139.02 ms │ +7.28x faster │
```

…ache#20749)

## Which issue does this PR close?

- Part of apache#18489 
- Closes apache#20180
- Closes apache#15524
- Replaces apache#20665

## Rationale for this change

I [want DataFusion to be the fastest parquet engine on
ClickBench](apache#18489). One of
the queries where DataFusion is significantly slower is Query 29 which
has a very strange pattern of many aggregate functions that are offset
by a constant:


https://github.com/apache/datafusion/blob/0ca9d6586a43c323525b2e299448e0f1af4d6195/benchmarks/queries/clickbench/queries/q29.sql#L4

This is not a pattern I have ever seen in a real query, but it seems
like the engine currently at the top of the ClickBench leaderboard has a
special case for this pattern. ClickHouse probably does too. See
- duckdb/duckdb#15017
- Discussion on apache#15524

Thus I reluctantly conclude that we should have one too. 

## What changes are included in this PR?

This is an alternate to my first attempt. 
 - apache#20665

In particular, since this is such a ClickBench specific rule, I wanted
to
1. Minimize the downstream API / upgrade impact (aka not change existing
APIs)
2. Optimize performance for the case where this rewrite will not apply
(most times)

1. Add a rewrite `SUM(expr + scalar)` --> `SUM(expr) +
scalar*COUNT(expr)`
3. Tests for same

Note there are quite a few other ideas to potentially make this more
general on apache#15524 but I am
going with the simple thing of making it work for the usecase we have in
hand (ClickBench)

## Are these changes tested?

Yes, new tests are added

## Are there any user-facing changes?

Faster performance

🚀 
```
│ QQuery 29 │  1012.63 ms │                 139.02 ms │ +7.28x faster │
```