Zhen Chen created CALCITE-7643:
----------------------------------
Summary: `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
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)
