[jira] [Updated] (CALCITE-7192) FILTER condition wrongly ignored in STDDEV/VAR function decomposition in AggregateReduceFunctionsRule

["lincoln lee (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-7192?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

lincoln lee updated CALCITE-7192:
---------------------------------
    Description: 
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` and `COUNT` 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 FILTER (WHERE condition)* 
*STDDEV_SAMP FILTER (WHERE condition)*
*VAR_POP FILTER (WHERE condition)* 
*VAR_SAMP 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)

  was:
## 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)


> 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
>            Priority: Major
>             Fix For: 1.41.0
>
>
> 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` and `COUNT` 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 FILTER (WHERE condition)* 
> *STDDEV_SAMP FILTER (WHERE condition)*
> *VAR_POP FILTER (WHERE condition)* 
> *VAR_SAMP 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)