[
https://issues.apache.org/jira/browse/CALCITE-7192?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18022363#comment-18022363
]
Zhen Chen commented on CALCITE-7192:
------------------------------------
Hi [~lincoln.86xy], please assign this JIRA to you, I can not find your name,
thanks!
> AggregateReduceFunctionsRule lost FILTER condition in STDDEV/VAR function
> decomposition
> ---------------------------------------------------------------------------------------
>
> 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
> Labels: pull-request-available
> 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:
> {code:java}
> // Line 554-555: Bug - passing -1 instead of oldCall.filterArg
> final AggregateCall sumArgSquaredAggCall =
> createAggregateCallWithBinding(typeFactory, SqlStdOperatorTable.SUM,
> argSquared.getType(), oldAggRel, oldCall, argSquaredOrdinal, -1);
> {code}
> 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
> {code:java}
> SELECT STDDEV_POP(salary) FILTER (WHERE salary > 1000) FROM employees{code}
> 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)
