Etienne Pelissier created CALCITE-7779:
------------------------------------------
Summary: Optimization rule FilterAggregateTransposeRule rewrites
queries to semantically non-equivalent ones
Key: CALCITE-7779
URL: https://issues.apache.org/jira/browse/CALCITE-7779
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Etienne Pelissier
Assignee: Etienne Pelissier
This only concerns non-simple aggregates (aggregates with multiple grouping
sets).
In the simple aggregate case, FilterAggregateTransposeRule pushes a predicate
below an aggregate when the predicate's referenced columns all belong to the
aggregate’s grouping set, because aggregate-function results are unavailable
before aggregation.
In the non-simple aggregate case, FilterAggregateTransposeRule pushes a
predicate below an aggregate when the predicate's referenced columns all belong
to every grouping set, because a referenced column is replaced with NULL in
rows produced by a grouping set that omits it, and evaluating the predicate
before aggregation can change the result.
However, the pushability check in the non-simple case currently compares
filter-input column positions (= aggregate-output column positions) with
aggregate-input column positions without remapping. The same position can
identify different columns in these two row layouts, so this check does not
establish that the predicate’s referenced columns belong to every grouping set.
Consequently, the rule can incorrectly push a predicate and change the query
result, or fail to push a predicate when doing so would be valid.
The existing dedicated grouping-set tests use aggregates whose grouping-column
input and output positions coincide. They do not cover grouping sets where
those positions differ, as in the following reproducer.
{code:java}
SELECT a, b
FROM (VALUES (0, 1, 2)) AS t(unused, a, b)
GROUP BY GROUPING SETS ((a), (a, b))
HAVING b IS NULL
{code}
After applying AGGREGATE_PROJECT_MERGE followed by FILTER_AGGREGATE_TRANSPOSE,
the resulting plan is equivalent to:
{code:java}
SELECT a, b
FROM (VALUES (0, 1, 2)) AS t(unused, a, b)
WHERE b IS NULL
GROUP BY GROUPING SETS ((a), (a, b))
{code}
which isn't semantically equivalent to the first query.
The original query returns:
{code:java}
A | B
--+-----
1 | NULL
(1 row)
{code}
The rewritten query returns no rows.
The fix is straightforward: remap each grouping set from aggregate-input column
positions to aggregate-output column positions before comparing it with the
predicate’s referenced columns (around 10 lines of code change).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
