[
https://issues.apache.org/jira/browse/CALCITE-7912?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mihai Budiu updated CALCITE-7912:
---------------------------------
Description:
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:
{code:java}
SELECT a, SUM AS m, CAST(NULL AS INTEGER) AS b FROM t1 GROUP BY a
UNION ALL
SELECT a, CAST(NULL AS INTEGER), SUM FROM t2 GROUP BY a{code}
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:
{code:java}
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 {code}
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.
was:
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.
> 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:
> {code:java}
> SELECT a, SUM AS m, CAST(NULL AS INTEGER) AS b FROM t1 GROUP BY a
> UNION ALL
> SELECT a, CAST(NULL AS INTEGER), SUM FROM t2 GROUP BY a{code}
> 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:
> {code:java}
> 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
> {code}
> 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)
