[
https://issues.apache.org/jira/browse/CALCITE-7779?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Etienne Pelissier updated CALCITE-7779:
---------------------------------------
Description: For non simple-aggregates, FilterAggregateTransposeRule's
pushability check compare column indices without taking into account column
remapping from the aggregate. (was: 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:sql}
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:sql}
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}
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).)
> 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
> Priority: Major
>
> For non simple-aggregates, FilterAggregateTransposeRule's pushability check
> compare column indices without taking into account column remapping from the
> aggregate.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
