[
https://issues.apache.org/jira/browse/CALCITE-7643?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhen Chen updated CALCITE-7643:
-------------------------------
Summary: AggregateMinMaxToLimitRule drops FILTER condition for filtered
MIN/MAX aggregates (was: `AggregateMinMaxToLimitRule drops FILTER condition
for filtered MIN/MAX aggregates)
> 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
> 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)
