[
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)
