[
https://issues.apache.org/jira/browse/CALCITE-7643?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-7643:
------------------------------------
Labels: pull-request-available (was: )
> AggregateMinMaxToLimitRule drops FILTER condition for filtered MIN/MAX
> aggregates
> ---------------------------------------------------------------------------------
>
> Key: CALCITE-7643
> URL: https://issues.apache.org/jira/browse/CALCITE-7643
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.42.0
> Reporter: Zhen Chen
> Assignee: Zhen Chen
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.43.0
>
>
> {{AggregateMinMaxToLimitRule}} matches no-group aggregates whose aggregate
> calls are all {{MIN}} or {{{}MAX{}}}, including aggregate calls with a
> {{FILTER}} clause.
> However, when rewriting each aggregate call into a scalar subquery with
> {{{}ORDER BY ... LIMIT 1{}}}, the rule only applies {{arg IS NOT NULL}} and
> does not preserve the aggregate filter condition. This can make rows that
> should be excluded by {{FILTER}} participate in the rewritten subquery,
> producing incorrect results.
> Example SQL:
> {code:java}
> select min(v) filter (where p), max(v) filter (where p)
> from (values
> (10, false),
> (100, true),
> (200, true),
> (300, false),
> (cast(null as integer), true)
> ) as t(v, p); {code}
> Expected result:
> {code:java}
> 100, 200 {code}
> For filtered aggregate calls, the generated subquery should apply both
> conditions:
> {code:java}
> WHERE p AND v IS NOT NULL
> ORDER BY v ASC/DESC
> LIMIT 1 {code}
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
