Yu Xu created CALCITE-7703:
------------------------------
Summary: 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
*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)
