[
https://issues.apache.org/jira/browse/CALCITE-7192?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18022448#comment-18022448
]
lincoln lee commented on CALCITE-7192:
--------------------------------------
[~jensen] I have no idea why can't be searched and usually only the project's
committers have the permission to assign Jiras.
If assignment isn't possible, would it be acceptable to simply close this Jira
issue instead?
> 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)
