Sean Broeder created CALCITE-7912:
-------------------------------------
Summary: 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
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)
