lincoln lee created CALCITE-7192:
------------------------------------
Summary: FILTER condition wrongly ignored in STDDEV/VAR function
decomposition in AggregateReduceFunctionsRule
Key: CALCITE-7192
URL: https://issues.apache.org/jira/browse/CALCITE-7192
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.40.0
Reporter: lincoln lee
Fix For: 1.41.0
## Problem
The `AggregateReduceFunctionsRule` has a bug when decomposing aggregate
functions with FILTER conditions. When reducing variance and standard deviation
functions (STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP), the rule fails to
properly propagate the FILTER condition to all decomposed aggregate calls.
## Root Cause
In the `reduceStddev` method, the `SUM(x * x)` aggregate call is created
without applying the original FILTER condition:
```java
// Line 554-555: Bug - passing -1 instead of oldCall.filterArg
final AggregateCall sumArgSquaredAggCall =
createAggregateCallWithBinding(typeFactory, SqlStdOperatorTable.SUM,
argSquared.getType(), oldAggRel, oldCall, argSquaredOrdinal, -1);
```
While `SUM(x)` and `COUNT(x)` correctly use `oldCall.filterArg`, the `SUM(x *
x)` call ignores the filter condition.
## Impact
This affects all functions that use the `reduceStddev` method:
- **STDDEV_POP(x) FILTER (WHERE condition)**
- **STDDEV_SAMP(x) FILTER (WHERE condition)**
- **VAR_POP(x) FILTER (WHERE condition)**
- **VAR_SAMP(x) FILTER (WHERE condition)**
## Example
```sql
SELECT STDDEV_POP(salary) FILTER (WHERE salary > 1000) FROM employees;
```
Before fix:
- `SUM(salary)` applies filter → only salaries > 1000
- `COUNT(salary)` applies filter → only count salaries > 1000
- `SUM(salary * salary)` **ignores filter** → includes ALL salaries (incorrect)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
