You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Etienne Pelissier (Jira)" <ji...@apache.org> on 2026/09/13 01:53:00 UTC
[jira] [Updated] (CALCITE-7779) Optimization rule FilterAggregateTransposeRule rewrites queries to semantically non-equivalent ones
[ https://issues.apache.org/jira/browse/CALCITE-7779?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Etienne Pelissier updated CALCITE-7779:
---------------------------------------
Description:
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).
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: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).
> 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
>
> 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).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)