[
https://issues.apache.org/jira/browse/CALCITE-7912?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-7912:
------------------------------------
Labels: pull-request-available (was: )
> Prune UNION ALL branches that cannot affect an enclosing aggregate
> ------------------------------------------------------------------
>
> Key: CALCITE-7912
> URL: https://issues.apache.org/jira/browse/CALCITE-7912
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.42.0
> Reporter: Sean Broeder
> Assignee: Sean Broeder
> Priority: Major
> Labels: pull-request-available
>
> A common pattern builds a view as a UNION ALL of branches that each fill only
> some of the columns and produce NULL for the rest. For example:
> SELECT a, SUM(x) AS m, CAST(NULL AS INTEGER) AS b FROM t1 GROUP BY a
> UNION ALL
> SELECT a, CAST(NULL AS INTEGER), SUM(y) FROM t2 GROUP BY a
> A query such as SELECT SUM(m) FROM view only needs the first branch. The
> second branch produces NULL for m, and SUM ignores NULL, so it cannot change
> the result. Calcite still plans, and the engine still executes, the whole
> second branch, including its scan of t2 and its aggregation.
> Proposal: Add AggregateUnionNullBranchRule as
> CoreRules.AGGREGATE_UNION_NULL_BRANCH. It matches a logical Aggregate that
> has at least one aggregate call over a logical UNION ALL. A branch is
> irrelevant to the aggregate and can be removed when:
> - every aggregate call ignores NULL inputs (SUM, SUM0, MIN, MAX, AVG,
> COUNT(col)), and
> - each call reads a column that the branch produces as a constant NULL.
> Then:
> - Without GROUP BY: the irrelevant branches are removed. At least one branch
> is always kept.
> - With GROUP BY, including ROLLUP and GROUPING SETS: the branch is kept
> because it still contributes group keys. A redundant pure GROUP BY that has
> no aggregate calls inside it is replaced by a projection of the grouping
> columns. Duplicate rows can't change the enclosing aggregate.
> DISTINCT and FILTER on the aggregate calls are preserved.
> Multi-argument COUNT: COUNT(a, b) skips a row if any argument is NULL. A
> branch therefore contributes nothing as soon as one argument is always NULL
> in that branch, and the rule treats it as irrelevant. For example:
> SELECT COUNT(k, m) FROM (
> SELECT mgr AS k, SUM(sal) AS m, CAST(NULL AS INTEGER) AS b FROM emp GROUP
> BY mgr
> UNION ALL
> SELECT mgr, CAST(NULL AS INTEGER), SUM(empno) FROM emp GROUP BY mgr) AS v
> The plan starts as COUNT($0, $1) over the union. The second branch is removed
> because its m is always NULL. The result is COUNT($0) over the first branch.
> RelBuilder drops arguments of a non-DISTINCT COUNT that cannot be NULL, and
> the rule re-creates the aggregate through it. On the surviving branch m is
> SUM(sal) over a NOT NULL column, so it is dropped. IIn the same way, COUNT(m)
> becomes COUNT() when m cannot be NULL.
> Not changed:
> - COUNT(*) is never treated as ignoring NULL, so such a branch is kept.
> - UNION distinct is not rewritten.
> - Branches that pad with a non-NULL constant, or with a nullable column that
> is not constant NULL, are kept.
> - Aggregates not known to ignore NULL, for example ANY_VALUE, keep the branch.
> - A ROLLUP or other non-simple pure GROUP BY inside a branch is not removed.
> - A pure GROUP BY or DISTINCT over a union, with no aggregate calls, is not
> touched.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
