[
https://issues.apache.org/jira/browse/CALCITE-7643?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhen Chen updated CALCITE-7643:
-------------------------------
Description:
{{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}
was:
{{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}
> 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)
