You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Yu Xu (Jira)" <ji...@apache.org> on 2026/08/10 03:28:00 UTC
[jira] [Updated] (CALCITE-7703) AggregateFilterToCaseRule produces wrong results for multi-argument COUNT with FILTER
[ https://issues.apache.org/jira/browse/CALCITE-7703?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Yu Xu updated CALCITE-7703:
---------------------------
Description:
*AggregateFilterToCaseRule* rewrites a filtered aggregate such as SUM x FILTER (WHERE c) into SUM(CASE WHEN c THEN x END).
For COUNT it also handles the zero-argument and single-argument cases by wrapping the (possibly dummy) argument in a CASE expression.
However, COUNT also accepts multiple arguments (SqlCountAggFunction uses OperandTypes.ONE_OR_MORE), where COUNT(a, b) counts the rows in which every argument is non-null. In the COUNT branch the rule only read getArgList().get(0) and built a single-argument CASE, silently dropping the remaining arguments. This multi-argument COUNT also bypassed the existing guard that only allows single-argument aggregates to be transformed, because the COUNT branch ran before that guard.
sql
{code:java}
COUNT(DISTINCT empno, sal) FILTER (WHERE deptno = 20){code}
was incorrectly rewritten from:
{code:java}
LogicalAggregate(COUNT_DISTINCT_MATCH=[COUNT(DISTINCT $0, $1) FILTER $2])
LogicalProject(EMPNO=[$0], SAL=[$5], $f2=[=($7, 20)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]]){code}
to
{code:java}
LogicalAggregate(COUNT_DISTINCT_MATCH=[COUNT(DISTINCT $0)])
LogicalProject($f3=[CASE(=($7, 20), $0, null:INTEGER)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]]){code}
was:
*AggregateFilterToCaseRule* rewrites a filtered aggregate such as SUM(x) FILTER (WHERE c) into SUM(CASE WHEN c THEN x END).
For COUNT it also handles the zero-argument and single-argument cases by wrapping the (possibly dummy) argument in a CASE expression.
However, COUNT also accepts multiple arguments (SqlCountAggFunction uses OperandTypes.ONE_OR_MORE), where COUNT(a, b) counts the rows in which every argument is non-null. In the COUNT branch the rule only read getArgList().get(0) and built a single-argument CASE, silently dropping the remaining arguments. This multi-argument COUNT also bypassed the existing guard that only allows single-argument aggregates to be transformed, because the COUNT branch ran before that guard.
sql
{code:java}
COUNT(DISTINCT empno, sal) FILTER (WHERE deptno = 20){code}
was incorrectly rewritten from:
{code:java}
LogicalAggregate(COUNT_DISTINCT_MATCH=[COUNT(DISTINCT $0, $1) FILTER $2])
LogicalProject(EMPNO=[$0], SAL=[$5], $f2=[=($7, 20)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]]){code}
to
{code:java}
LogicalAggregate(COUNT_DISTINCT_MATCH=[COUNT(DISTINCT $0)])
LogicalProject($f3=[CASE(=($7, 20), $0, null:INTEGER)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]]){code}
> AggregateFilterToCaseRule produces wrong results for multi-argument COUNT with FILTER
> -------------------------------------------------------------------------------------
>
> Key: CALCITE-7703
> URL: https://issues.apache.org/jira/browse/CALCITE-7703
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.42.0
> Reporter: Yu Xu
> Assignee: Yu Xu
> Priority: Major
>
> *AggregateFilterToCaseRule* rewrites a filtered aggregate such as SUM x FILTER (WHERE c) into SUM(CASE WHEN c THEN x END).
> For COUNT it also handles the zero-argument and single-argument cases by wrapping the (possibly dummy) argument in a CASE expression.
> However, COUNT also accepts multiple arguments (SqlCountAggFunction uses OperandTypes.ONE_OR_MORE), where COUNT(a, b) counts the rows in which every argument is non-null. In the COUNT branch the rule only read getArgList().get(0) and built a single-argument CASE, silently dropping the remaining arguments. This multi-argument COUNT also bypassed the existing guard that only allows single-argument aggregates to be transformed, because the COUNT branch ran before that guard.
> sql
> {code:java}
> COUNT(DISTINCT empno, sal) FILTER (WHERE deptno = 20){code}
> was incorrectly rewritten from:
> {code:java}
> LogicalAggregate(COUNT_DISTINCT_MATCH=[COUNT(DISTINCT $0, $1) FILTER $2])
> LogicalProject(EMPNO=[$0], SAL=[$5], $f2=[=($7, 20)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]]){code}
> to
> {code:java}
> LogicalAggregate(COUNT_DISTINCT_MATCH=[COUNT(DISTINCT $0)])
> LogicalProject($f3=[CASE(=($7, 20), $0, null:INTEGER)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]]){code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)