pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19800: Wrong results: self-join elimination drops an ORDER BY sort key with GROUPING SETS / CUBE / ROLLUP
5+ messages / 3 participants
[nested] [flat]

* BUG #19800: Wrong results: self-join elimination drops an ORDER BY sort key with GROUPING SETS / CUBE / ROLLUP
@ 2026-10-27 07:36  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 5+ messages in thread

From: PG Bug reporting form @ 2026-10-27 07:36 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: 303677365@qq.com

The following bug has been logged on the website:

Bug reference:      19800
Logged by:          chunling qin
Email address:      303677365@qq.com
PostgreSQL version: 18.6
Operating system:   Linux x86_64
Description:        

Self-join elimination (SJE, new in v18) rewrites the Vars of the removed
relation to the kept one. When both sides of a grouping-sets query are
joined on a unique key, the two output columns end up as the same Var, so
their two ORDER BY keys land in the same equivalence class and the second
key is discarded as redundant (pathkey_is_redundant()).

That reasoning assumes EC members are always equal. After grouping sets it
is false: grouping sets emit NULLs per set, so two columns that share an
expression do not share values. Losing the second key leaves rows that tie
on the first key in an arbitrary order, which violates ORDER BY.

This is not a "tie is unspecified" case: in the example the tied rows are
distinguishable on the remaining sort key ((NULL,3) vs (NULL,8)), so the
required order is fully determined.

Reproduction (N = NULL):

CREATE TABLE m2 (a int UNIQUE);
INSERT INTO m2 VALUES (1), (2);
ANALYZE m2;

SET enable_self_join_elimination = on;   -- default
SELECT s1.a, s2.a FROM m2 s1 JOIN m2 s2 ON s1.a = s2.a
 GROUP BY CUBE (s1.a, s2.a) ORDER BY 1, 2;

SET enable_self_join_elimination = off;
SELECT s1.a, s2.a FROM m2 s1 JOIN m2 s2 ON s1.a = s2.a
 GROUP BY CUBE (s1.a, s2.a) ORDER BY 1, 2;

Actual output:
SJE on : (1,N) (1,1) (2,2) (2,N) (N,N) (N,1) (N,2)
SJE off: (1,1) (1,N) (2,2) (2,N) (N,1) (N,2) (N,N)

Expected: the SJE-off ordering. With ORDER BY 1, 2 (ASC, NULLS LAST) the
pair (1,1) must precede (1,N), and (N,N) must come after (N,1) and (N,2).
The SJE-on output inverts both.

Plans (EXPLAIN (COSTS OFF, VERBOSE)):
SJE on : Sort Key: s2.a        Output: s2.a, s2.a    <- one key; both
columns are the same Var
SJE off: Sort Key: s1.a, s2.a  Output: s1.a, s2.a

With 10 rows the effect is unambiguous: the group whose first column is NULL
comes out as
3 8 10 9 7 1 5 4 2 6 with SJE on, and 1 2 3 4 5 6 7 8 9 10 with SJE off. The
sequence is deterministic - identical across sessions and across all three
builds tested.

Impact is not limited to cosmetic ordering: with LIMIT 1 OFFSET 4 on the
2-row example, SJE on returns (NULL,NULL) and SJE off returns (NULL,1) -
different rows, so paging and any INSERT ... SELECT ... ORDER BY ... LIMIT
can materialise wrong data.

Source: remove_self_join_rel() in analyzejoins.c rewrites root->parse via
ChangeVarNodes() without awareness of grouping-set semantics; the key is
then dropped by make_pathkeys_for_sortclauses() -> pathkey_is_redundant() in
pathkeys.c.

Related: the same underlying hazard was reported in 2021 ("Sort keys are
omitted incorrectly after grouping sets", Richard Guo, pgsql-hackers), where
the hand-written form
SELECT a, b FROM foo WHERE a = b GROUP BY CUBE (a,b) ORDER BY a, b
lost a sort key. That form is fixed today - I verified it produces Sort Key:
a, b and correct output on all three builds. The grouping-sets special case
in pathkeys.c does not appear to cover the shape SJE produces, so v18 can
silently transform a safe query into the unsafe one.

Tested on:
- PostgreSQL 18.6, official Docker image postgres:latest (Debian
18.6-1.pgdg13+2)
- PostgreSQL 18.6, build of REL_18_STABLE tip (commit 606f3db)
- PostgreSQL 20devel, build of master
All three reproduce. enable_hashagg=off does not change the result.








^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #19800: Wrong results: self-join elimination drops an ORDER BY sort key with GROUPING SETS / CUBE / ROLLUP
@ 2026-10-27 09:28  Tender Wang <tndrwang@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 5+ messages in thread

From: Tender Wang @ 2026-10-27 09:28 UTC (permalink / raw)
  To: 303677365@qq.com; pgsql-bugs@lists.postgresql.org

PG Bug reporting form <noreply@postgresql.org> 于2026年10月27日周二 17:04写道:
>
> Tested on:
> - PostgreSQL 18.6, official Docker image postgres:latest (Debian
> 18.6-1.pgdg13+2)
> - PostgreSQL 18.6, build of REL_18_STABLE tip (commit 606f3db)
> - PostgreSQL 20devel, build of master
> All three reproduce. enable_hashagg=off does not change the result.
>

Thanks for the report. I agree that this is a correctness issue.

The special property of grouping sets here is that two grouping items
whose underlying expressions are equal do not necessarily produce equal
output values. Each grouping set can independently replace a grouping
item with NULL.

Before SJE, s1.a and s2.a are distinct grouping items:

GROUP BY CUBE (s1.a, s2.a)

In particular, the expanded grouping sets include both (s1.a) and
(s2.a), so the output can contain (value, NULL) and (NULL, value).

SJE removes one side of the join and rewrites references to the removed
relation to reference the kept relation. After that rewrite, the two
grouping items appear to contain the same Var. Consequently, their
ORDER BY keys can be considered equivalent and the second one is
discarded as redundant. That reasoning would be valid for ordinary
rows, but it is not valid after grouping sets have independently
nullified the two grouping items.

The simplest fix I can think of for now is to skip SJE when the current
query level contains grouping sets. For example, in
remove_useless_self_joins():

if (!enable_self_join_elimination ||
    root->parse->groupingSets != NIL ||
    joinlist == NIL ||
    (list_length(joinlist) == 1 && !IsA(linitial(joinlist), List)))
    return joinlist;

This is conservative, since some queries containing grouping sets could
still safely use SJE. However, it avoids changing the grouping-item
identity, and the affected combination of grouping sets and a removable
self join seems uncommon. It may therefore be a reasonable quick fix,
particularly for back-patching to v18.

A more selective fix would need to determine whether the SJE
substitution makes two distinct grouping items identical while some
grouping set contains one but not the other. That seems considerably
more complicated, so I think the conservative check is a useful
starting point.


-- 
Thanks,
Tender Wang






^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #19800: Wrong results: self-join elimination drops an ORDER BY sort key with GROUPING SETS / CUBE / ROLLUP
@ 2026-10-29 06:41  Narayanan Venkateswaran <narayananvpostgres@gmail.com>
  parent: Tender Wang <tndrwang@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Narayanan Venkateswaran @ 2026-10-29 06:41 UTC (permalink / raw)
  To: Tender Wang <tndrwang@gmail.com>; +Cc: 303677365@qq.com; pgsql-bugs@lists.postgresql.org

Hi,


On Tue, Oct 27, 2026 at 2:58 PM Tender Wang <tndrwang@gmail.com> wrote:
>
> PG Bug reporting form <noreply@postgresql.org> 于2026年10月27日周二 17:04写道:
> >
> > Tested on:
> > - PostgreSQL 18.6, official Docker image postgres:latest (Debian
> > 18.6-1.pgdg13+2)
> > - PostgreSQL 18.6, build of REL_18_STABLE tip (commit 606f3db)
> > - PostgreSQL 20devel, build of master
> > All three reproduce. enable_hashagg=off does not change the result.
> >
>
> Thanks for the report. I agree that this is a correctness issue.
>
> The special property of grouping sets here is that two grouping items
> whose underlying expressions are equal do not necessarily produce equal
> output values. Each grouping set can independently replace a grouping
> item with NULL.
>
> Before SJE, s1.a and s2.a are distinct grouping items:
>
> GROUP BY CUBE (s1.a, s2.a)
>
> In particular, the expanded grouping sets include both (s1.a) and
> (s2.a), so the output can contain (value, NULL) and (NULL, value).
>
> SJE removes one side of the join and rewrites references to the removed
> relation to reference the kept relation. After that rewrite, the two
> grouping items appear to contain the same Var. Consequently, their
> ORDER BY keys can be considered equivalent and the second one is
> discarded as redundant. That reasoning would be valid for ordinary
> rows, but it is not valid after grouping sets have independently
> nullified the two grouping items.
>

Your analysis is spot on.

When self-join elimination (SJE) removes one side of a self-join on a
unique key,
references to the removed relation are rewritten via ChangeVarNodes().

When both sides of the self-join are referenced in grouping items
under grouping sets
(CUBE, ROLLUP, or GROUPING SETS), the rewrite causes two distinct grouping items
to become the exact same Var expression. Downstream in
make_pathkeys_for_sortclauses(),
pathkey_is_redundant() assumes members of the same Equivalence Class cannot
distinguish tied rows, and silently drops the second ORDER BY key,
resulting in arbitrary row
ordering.

> The simplest fix I can think of for now is to skip SJE when the current
> query level contains grouping sets. For example, in
> remove_useless_self_joins():
>
> if (!enable_self_join_elimination ||
>     root->parse->groupingSets != NIL ||
>     joinlist == NIL ||
>     (list_length(joinlist) == 1 && !IsA(linitial(joinlist), List)))
>     return joinlist;

I have implemented and tested the Approach you suggested above. I have
also added comprehensive
unit tests covering different permutations.
>
> This is conservative, since some queries containing grouping sets could
> still safely use SJE. However, it avoids changing the grouping-item
> identity, and the affected combination of grouping sets and a removable
> self join seems uncommon. It may therefore be a reasonable quick fix,
> particularly for back-patching to v18.

I agree with you that this approach looks conservative, although I
think this is the best approach due to
its simplicity and safety.

>
> A more selective fix would need to determine whether the SJE
> substitution makes two distinct grouping items identical while some
> grouping set contains one but not the other. That seems considerably
> more complicated, so I think the conservative check is a useful
> starting point.

I also want to explore the following two approaches and present a
comparative evaluation of alternatives

Approach 2 (Selective SJE check): Instead of skipping SJE query-wide,
check if the candidate relations
(toRemove / toKeep) appear in root->parse->groupClause. If neither
relation is referenced in the grouping
items, SJE can still safely eliminate the join.

Approach 3 (Pathkey level): Relaxing the redundancy assumption in
pathkey_is_redundant() when two
sort keys originate from distinct grouping items (tleSortGroupRef) in
the presence of grouping sets.

I need to deep-dive into whether the above two will have any broader
implications I am missing.

>
>
> --
> Thanks,
> Tender Wang
>

Thank you,
Narayanan

>

Attachments:

v1-0001-Skip-self-join-elimination-for-queries-with-grouping.patch (application/octet-stream, 12.1K) download From 56158eb6efc37e4136b5a499d54ee0afa44a9cf5 Mon Sep 17 00:00:00 2001 From: Narayanan Venkateswaran <narayananvenk@google.com> Date: Wed, 28 Oct 2026 10:39:50 +0000 Subject: [PATCH] Skip self-join elimination for queries with grouping sets. Self-join elimination (SJE) removes one side of a self-join on a unique key and rewrites references to the removed relation to point to the kept relation via ChangeVarNodes(). When grouping sets (GROUPING SETS, CUBE, or ROLLUP) are present, each grouping set can independently nullify grouping items that are omitted from that set. When both sides of a self-join appear in the grouping items, SJE's rewrite causes two distinct grouping items to become the exact same Var expression. Consequently, make_pathkeys_for_sortclauses() treats the corresponding ORDER BY sort keys as redundant and drops the second key. This leads to wrong result ordering for rows that tie on the first key, and can return incorrect rows when combined with LIMIT and OFFSET. To prevent this, skip self-join elimination when the current query level contains grouping sets (root->parse->groupingSets != NIL). --- src/backend/optimizer/plan/analyzejoins.c | 15 +- src/test/regress/expected/join.out | 226 ++++++++++++++++++++++ src/test/regress/sql/join.sql | 63 ++++++ 3 files changed, 302 insertions(+), 2 deletions(-) diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index 7aad91fd34e..a8d4dd63d0e 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -2146,8 +2146,19 @@ self_join_candidates_cmp(const void *a, const void *b) bool remove_useless_self_joins(PlannerInfo *root, List *joinlist) { - /* Skip if SJE is disabled, or if the joinlist has less than 2 members. */ - if (!enable_self_join_elimination || joinlist == NIL || + /* + * Skip if SJE is disabled, if the query uses grouping sets, or if the + * joinlist has less than 2 members. + * + * When grouping sets are present, each grouping set can independently + * replace grouping items with NULLs. Replacing references to a removed + * relation with references to the kept relation can cause distinct + * grouping items to appear identical, leading downstream pathkey + * deduplication to drop valid ORDER BY keys as redundant. + */ + if (!enable_self_join_elimination || + root->parse->groupingSets != NIL || + joinlist == NIL || (list_length(joinlist) == 1 && !IsA(linitial(joinlist), List))) return false; diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 1e6aa380257..2096abc4553 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -9197,6 +9197,232 @@ where t1.a = s.a; Filter: (a IS NOT NULL) (2 rows) +-- Ensure that SJE is not performed when grouping sets are present, since +-- grouping sets can independently nullify columns that would otherwise be +-- considered equivalent, causing ORDER BY sort keys to be dropped. +-- 1. CUBE with aggregates +explain (costs off) +select t1.a, t2.a, count(*), sum(t1.b) from sj t1 join sj t2 on t1.a = t2.a +group by cube (t1.a, t2.a) order by 1, 2; + QUERY PLAN +------------------------------------------- + Sort + Sort Key: t1.a, t2.a + -> MixedAggregate + Hash Key: t1.a, t2.a + Hash Key: t1.a + Hash Key: t2.a + Group Key: () + -> Nested Loop + Join Filter: (t1.a = t2.a) + -> Seq Scan on sj t1 + -> Materialize + -> Seq Scan on sj t2 +(12 rows) + +select t1.a, t2.a, count(*), sum(t1.b) from sj t1 join sj t2 on t1.a = t2.a +group by cube (t1.a, t2.a) order by 1, 2; + a | a | count | sum +---+---+-------+----- + 1 | 1 | 1 | + 1 | | 1 | + 2 | 2 | 1 | 1 + 2 | | 1 | 1 + | 1 | 1 | + | 2 | 1 | 1 + | | 2 | 1 +(7 rows) + +-- 2. ROLLUP +explain (costs off) +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by rollup (t1.a, t2.a) order by 1, 2; + QUERY PLAN +------------------------------------------------- + Sort + Sort Key: t1.a, t2.a + -> GroupAggregate + Group Key: t1.a, t2.a + Group Key: t1.a + Group Key: () + -> Sort + Sort Key: t1.a + -> Nested Loop + Join Filter: (t1.a = t2.a) + -> Seq Scan on sj t1 + -> Materialize + -> Seq Scan on sj t2 +(13 rows) + +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by rollup (t1.a, t2.a) order by 1, 2; + a | a | count +---+---+------- + 1 | 1 | 1 + 1 | | 1 + 2 | 2 | 1 + 2 | | 1 + | | 2 +(5 rows) + +-- 3. Explicit GROUPING SETS +explain (costs off) +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by grouping sets ((t1.a, t2.a), (t1.a), (t2.a), ()) order by 1, 2; + QUERY PLAN +------------------------------------------- + Sort + Sort Key: t1.a, t2.a + -> MixedAggregate + Hash Key: t1.a, t2.a + Hash Key: t1.a + Hash Key: t2.a + Group Key: () + -> Nested Loop + Join Filter: (t1.a = t2.a) + -> Seq Scan on sj t1 + -> Materialize + -> Seq Scan on sj t2 +(12 rows) + +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by grouping sets ((t1.a, t2.a), (t1.a), (t2.a), ()) order by 1, 2; + a | a | count +---+---+------- + 1 | 1 | 1 + 1 | | 1 + 2 | 2 | 1 + 2 | | 1 + | 1 | 1 + | 2 | 1 + | | 2 +(7 rows) + +-- 4. Asymmetric GROUPING SETS +explain (costs off) +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by grouping sets ((t1.a), (t2.a)) order by 1, 2; + QUERY PLAN +------------------------------------------- + Sort + Sort Key: t1.a, t2.a + -> HashAggregate + Hash Key: t1.a + Hash Key: t2.a + -> Nested Loop + Join Filter: (t1.a = t2.a) + -> Seq Scan on sj t1 + -> Materialize + -> Seq Scan on sj t2 +(10 rows) + +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by grouping sets ((t1.a), (t2.a)) order by 1, 2; + a | a | count +---+---+------- + 1 | | 1 + 2 | | 1 + | 1 | 1 + | 2 | 1 +(4 rows) + +-- 5. Multiple unique keys with CUBE +explain (costs off) +select t1.a, t2.c, count(*) from sj t1 join sj t2 on t1.a = t2.a and t1.c = t2.c +group by cube (t1.a, t2.c) order by 1, 2; + QUERY PLAN +-------------------------------------------------------------- + Sort + Sort Key: t1.a, t2.c + -> MixedAggregate + Hash Key: t1.a, t2.c + Hash Key: t1.a + Hash Key: t2.c + Group Key: () + -> Nested Loop + Join Filter: ((t2.a = t1.a) AND (t1.c = t2.c)) + -> Seq Scan on sj t1 + -> Materialize + -> Seq Scan on sj t2 +(12 rows) + +select t1.a, t2.c, count(*) from sj t1 join sj t2 on t1.a = t2.a and t1.c = t2.c +group by cube (t1.a, t2.c) order by 1, 2; + a | c | count +---+---+------- + 1 | 2 | 1 + 1 | | 1 + 2 | 1 | 1 + 2 | | 1 + | 1 | 1 + | 2 | 1 + | | 2 +(7 rows) + +-- 6. Pulled-up subquery with self-join under grouping sets +explain (costs off) +select x, y, count(*) +from (select t1.a as x, t2.a as y from sj t1 join sj t2 on t1.a = t2.a) s +group by cube (x, y) order by 1, 2; + QUERY PLAN +------------------------------------------- + Sort + Sort Key: t1.a, t2.a + -> MixedAggregate + Hash Key: t1.a, t2.a + Hash Key: t1.a + Hash Key: t2.a + Group Key: () + -> Nested Loop + Join Filter: (t1.a = t2.a) + -> Seq Scan on sj t1 + -> Materialize + -> Seq Scan on sj t2 +(12 rows) + +select x, y, count(*) +from (select t1.a as x, t2.a as y from sj t1 join sj t2 on t1.a = t2.a) s +group by cube (x, y) order by 1, 2; + x | y | count +---+---+------- + 1 | 1 | 1 + 1 | | 1 + 2 | 2 | 1 + 2 | | 1 + | 1 | 1 + | 2 | 1 + | | 2 +(7 rows) + +-- 7. Outer self-join with inner subquery containing grouping sets (SJE should still apply) +explain (costs off) +select t1.a, t2.a, sub.ct from sj t1 join sj t2 on t1.a = t2.a +join (select a, count(*) as ct from sj group by rollup(a)) sub on t1.a = sub.a +order by 1, 2; + QUERY PLAN +--------------------------------------------- + Sort + Sort Key: t2.a + -> Nested Loop + Join Filter: (t2.a = sj.a) + -> MixedAggregate + Hash Key: sj.a + Group Key: () + -> Seq Scan on sj + -> Materialize + -> Seq Scan on sj t2 + Filter: (a IS NOT NULL) +(11 rows) + +select t1.a, t2.a, sub.ct from sj t1 join sj t2 on t1.a = t2.a +join (select a, count(*) as ct from sj group by rollup(a)) sub on t1.a = sub.a +order by 1, 2; + a | a | ct +---+---+---- + 1 | 1 | 1 + 2 | 2 | 1 +(2 rows) + -- Degenerated case. explain (costs off) select * from diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 6264f23e081..7917407193f 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -3517,6 +3517,69 @@ select * from sj t1 on true where t1.a = s.a; +-- Ensure that SJE is not performed when grouping sets are present, since +-- grouping sets can independently nullify columns that would otherwise be +-- considered equivalent, causing ORDER BY sort keys to be dropped. +-- 1. CUBE with aggregates +explain (costs off) +select t1.a, t2.a, count(*), sum(t1.b) from sj t1 join sj t2 on t1.a = t2.a +group by cube (t1.a, t2.a) order by 1, 2; + +select t1.a, t2.a, count(*), sum(t1.b) from sj t1 join sj t2 on t1.a = t2.a +group by cube (t1.a, t2.a) order by 1, 2; + +-- 2. ROLLUP +explain (costs off) +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by rollup (t1.a, t2.a) order by 1, 2; + +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by rollup (t1.a, t2.a) order by 1, 2; + +-- 3. Explicit GROUPING SETS +explain (costs off) +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by grouping sets ((t1.a, t2.a), (t1.a), (t2.a), ()) order by 1, 2; + +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by grouping sets ((t1.a, t2.a), (t1.a), (t2.a), ()) order by 1, 2; + +-- 4. Asymmetric GROUPING SETS +explain (costs off) +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by grouping sets ((t1.a), (t2.a)) order by 1, 2; + +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by grouping sets ((t1.a), (t2.a)) order by 1, 2; + +-- 5. Multiple unique keys with CUBE +explain (costs off) +select t1.a, t2.c, count(*) from sj t1 join sj t2 on t1.a = t2.a and t1.c = t2.c +group by cube (t1.a, t2.c) order by 1, 2; + +select t1.a, t2.c, count(*) from sj t1 join sj t2 on t1.a = t2.a and t1.c = t2.c +group by cube (t1.a, t2.c) order by 1, 2; + +-- 6. Pulled-up subquery with self-join under grouping sets +explain (costs off) +select x, y, count(*) +from (select t1.a as x, t2.a as y from sj t1 join sj t2 on t1.a = t2.a) s +group by cube (x, y) order by 1, 2; + +select x, y, count(*) +from (select t1.a as x, t2.a as y from sj t1 join sj t2 on t1.a = t2.a) s +group by cube (x, y) order by 1, 2; + +-- 7. Outer self-join with inner subquery containing grouping sets (SJE should still apply) +explain (costs off) +select t1.a, t2.a, sub.ct from sj t1 join sj t2 on t1.a = t2.a +join (select a, count(*) as ct from sj group by rollup(a)) sub on t1.a = sub.a +order by 1, 2; + +select t1.a, t2.a, sub.ct from sj t1 join sj t2 on t1.a = t2.a +join (select a, count(*) as ct from sj group by rollup(a)) sub on t1.a = sub.a +order by 1, 2; + -- Degenerated case. explain (costs off) select * from -- 2.56.0.317.g454f8302e1-goog
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: BUG #19800: Wrong results: self-join elimination drops an ORDER BY sort key with GROUPING SETS / CUBE / ROLLUP
@ 2026-11-11 06:38  Tender Wang <tndrwang@gmail.com>
  parent: Narayanan Venkateswaran <narayananvpostgres@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Tender Wang @ 2026-11-11 06:38 UTC (permalink / raw)
  To: Narayanan Venkateswaran <narayananvpostgres@gmail.com>; +Cc: 303677365@qq.com; pgsql-bugs@lists.postgresql.org

Narayanan Venkateswaran <narayananvpostgres@gmail.com> 于2026年10月29日周四 14:41写道:
>
> Approach 2 (Selective SJE check): Instead of skipping SJE query-wide,
> check if the candidate relations
> (toRemove / toKeep) appear in root->parse->groupClause. If neither
> relation is referenced in the grouping
> items, SJE can still safely eliminate the join.
>

Skipping SJE query-wide seems too strict; I'm also thinking about
approach 2. As in the attached v2.

In the attached v2, I collect the relids referenced by the grouping
expressions and skip only an SJE candidate whose toRemove or toKeep
relation overlaps that set.  The relid set is computed once in
remove_useless_self_joins(), so the per-candidate check only requires
bitmap overlap tests.

-- 
Thanks,
Tender Wang

Attachments:

v2-0001-Skip-sje.patch (text/plain, 4.1K) download From d97189567f387347a592c1b55884d7a50edbff48 Mon Sep 17 00:00:00 2001 From: Tender Wang <tndrwang@gmail.com> Date: Wed, 11 Nov 2026 14:31:41 +0800 Subject: [PATCH v2] Skip sje: --- src/backend/optimizer/plan/analyzejoins.c | 51 ++++++++++++++++++++--- 1 file changed, 45 insertions(+), 6 deletions(-) diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index 25a863efbde..a2ed215939b 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -1775,7 +1775,7 @@ match_unique_clauses(RelOptInfo *outer, List *uclauses, Index relid) * cannot make one that isn't justified. */ static bool -remove_self_joins_one_group(PlannerInfo *root, Relids relids) +remove_self_joins_one_group(PlannerInfo *root, Relids relids, Relids grouping_relids) { bool removed = false; int k; /* Index of kept relation */ @@ -1804,6 +1804,11 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids) Assert(root->simple_rte_array[k]->relid == root->simple_rte_array[r]->relid); + /* SJE must not rewrite expressions used as grouping items. */ + if (bms_overlap(grouping_relids, rrel->relids) || + bms_overlap(grouping_relids, krel->relids)) + continue; + /* * It is impossible to eliminate the join of two relations if they * are not on the same side of every outer join. Otherwise, the @@ -1943,7 +1948,7 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids) * Return true if we removed any joins. */ static bool -remove_self_joins_recurse(PlannerInfo *root, List *joinlist) +remove_self_joins_recurse(PlannerInfo *root, List *joinlist, Relids grouping_relids) { bool removed = false; ListCell *jl; @@ -2000,7 +2005,7 @@ remove_self_joins_recurse(PlannerInfo *root, List *joinlist) else if (IsA(jlnode, List)) { /* Recursively perform SJE within the sub-joinlist */ - removed |= remove_self_joins_recurse(root, (List *) jlnode); + removed |= remove_self_joins_recurse(root, (List *) jlnode, grouping_relids); } else elog(ERROR, "unrecognized joinlist node type: %d", @@ -2068,7 +2073,7 @@ remove_self_joins_recurse(PlannerInfo *root, List *joinlist) relids = bms_del_members(relids, group); /* Try to remove self-joins from the group */ - removed |= remove_self_joins_one_group(root, group); + removed |= remove_self_joins_one_group(root, group, grouping_relids); bms_free(group); } else @@ -2144,11 +2149,45 @@ self_join_candidates_cmp(const void *a, const void *b) bool remove_useless_self_joins(PlannerInfo *root, List *joinlist) { + Relids grouping_relids = NULL; + bool removed; + /* Skip if SJE is disabled, or if the joinlist has less than 2 members. */ if (!enable_self_join_elimination || joinlist == NIL || (list_length(joinlist) == 1 && !IsA(linitial(joinlist), List))) return false; - /* Try to merge pairs of self-joined relations. */ - return remove_self_joins_recurse(root, joinlist); + /* + * With grouping sets, grouping items that become the same expression + * after SJE are not necessarily interchangeable: either item may be + * replaced with NULL independently. Collect the base relids referenced + * by grouping expressions, so that only SJE candidates that could affect + * those expressions are rejected. + * + * GroupingSet nodes contain sortgrouprefs rather than expressions, so use + * groupClause to locate the corresponding target-list expressions. + */ + if (root->parse->groupingSets != NIL) + { + ListCell *lc; + + foreach(lc, root->parse->groupClause) + { + SortGroupClause *sgc = lfirst_node(SortGroupClause, lc); + TargetEntry *tle; + Relids expr_relids; + + tle = get_sortgroupclause_tle(sgc, root->parse->targetList); + expr_relids = pull_varnos(root, (Node *) tle->expr); + grouping_relids = bms_add_members(grouping_relids, expr_relids); + bms_free(expr_relids); + } + } + + /* Try to merge one pair of relations participating in a self-join. */ + removed = remove_self_joins_recurse(root, joinlist, grouping_relids); + + bms_free(grouping_relids); + + return removed; } -- 2.43.0
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: BUG #19800: Wrong results: self-join elimination drops an ORDER BY sort key with GROUPING SETS / CUBE / ROLLUP
@ 2026-11-12 21:34  Narayanan Venkateswaran <narayananvpostgres@gmail.com>
  parent: Tender Wang <tndrwang@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Narayanan Venkateswaran @ 2026-11-12 21:34 UTC (permalink / raw)
  To: Tender Wang <tndrwang@gmail.com>; +Cc: 303677365@qq.com; pgsql-bugs@lists.postgresql.org

Hi Tender,

On Wed, Nov 11, 2026 at 12:08 PM Tender Wang <tndrwang@gmail.com> wrote:
>
> Skipping SJE query-wide seems too strict; I'm also thinking about
> approach 2. As in the attached v2.
>
> In the attached v2, I collect the relids referenced by the grouping
> expressions and skip only an SJE candidate whose toRemove or toKeep
> relation overlaps that set.  The relid set is computed once in
> remove_useless_self_joins(), so the per-candidate check only requires
> bitmap overlap tests.

Thanks for the v2 patch! I like this approach much better than skipping
SJE for the entire query.

While testing it further, I noticed a couple of small things,

1. In remove_useless_self_joins(), I passed NULL as the root to
pull_varnos(NULL, (Node *) tle->expr). When a grouping set contains a
constant (like GROUP BY ROLLUP(1)), mark_nullable_by_grouping() wraps the
constant in a PlaceHolderVar whose phrels includes all relations in the
jointree. Passing NULL tells pull_varnos() to look inside the
PlaceHolderVar's expression rather than returning ph_eval_at, so SJE can
still optimize self-joins in those queries.

2. In remove_self_joins_one_group(), since r and k are base relation IDs,
I moved the check for r to the outer loop using bms_is_member(r,
grouping_relids) and used bms_is_member(k, grouping_relids) in the inner
loop.

3. I also noticed that the underlying issue can cause wrong results even
without ORDER BY: if the second grouping column is used in HAVING (e.g.,
HAVING t2.a IS NULL) or in an expression like COALESCE(t2.a, -1), SJE
rewrites t1.a and t2.a to the same Var and setrefs.c ends up binding both
to the first column. Skipping SJE for relations in grouping_relids fixes
this case as well.

4. Added the commit message and regression tests in join.sql covering
both cases where SJE should be skipped and cases where SJE should still
apply.

Please find the patch with the above changes attached.

Thank you,
Narayanan

Attachments:

v3-0001-Skip-self-join-elimination-for-relations-in-group.patch (application/octet-stream, 19.0K) download From ba1f1c56cace43757f57420dbdf15b93474e16be Mon Sep 17 00:00:00 2001 From: Narayanan Venkateswaran <narayananvenk@google.com> Date: Thu, 12 Nov 2026 21:28:18 +0000 Subject: [PATCH v3] Skip self-join elimination for relations in grouping sets. Self-join elimination (SJE) removes one side of a self-join on a unique key and rewrites references to the removed relation to point to the kept relation via ChangeVarNodes(). When grouping sets (GROUPING SETS, CUBE, or ROLLUP) are present, each grouping set can independently nullify grouping items that are omitted from that set. When both sides of a self-join appear in the grouping items, SJE's rewrite causes two distinct grouping items to become the exact same Var expression. This causes make_pathkeys_for_sortclauses() to drop the second ORDER BY or DISTINCT key as redundant, and causes setrefs.c to bind HAVING clauses and upper targetlist expressions referencing the second column to the first column. To prevent this while still allowing SJE on other relations in the query, collect the base relids referenced by groupClause when grouping sets are present, and skip SJE for any candidate relation pair that overlaps those relids. --- src/backend/optimizer/plan/analyzejoins.c | 58 +++- src/test/regress/expected/join.out | 325 ++++++++++++++++++++++ src/test/regress/sql/join.sql | 99 +++++++ 3 files changed, 477 insertions(+), 5 deletions(-) diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index 25a863efbde..521569174f7 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -1775,7 +1775,8 @@ match_unique_clauses(RelOptInfo *outer, List *uclauses, Index relid) * cannot make one that isn't justified. */ static bool -remove_self_joins_one_group(PlannerInfo *root, Relids relids) +remove_self_joins_one_group(PlannerInfo *root, Relids relids, + Relids grouping_relids) { bool removed = false; int k; /* Index of kept relation */ @@ -1785,6 +1786,10 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids) { RelOptInfo *rrel = root->simple_rel_array[r]; + /* SJE must not rewrite expressions used as grouping items. */ + if (bms_is_member(r, grouping_relids)) + continue; + /* k iterates over the relids after r */ k = r; while ((k = bms_next_member(relids, k)) > 0) @@ -1804,6 +1809,10 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids) Assert(root->simple_rte_array[k]->relid == root->simple_rte_array[r]->relid); + /* SJE must not rewrite expressions used as grouping items. */ + if (bms_is_member(k, grouping_relids)) + continue; + /* * It is impossible to eliminate the join of two relations if they * are not on the same side of every outer join. Otherwise, the @@ -1943,7 +1952,8 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids) * Return true if we removed any joins. */ static bool -remove_self_joins_recurse(PlannerInfo *root, List *joinlist) +remove_self_joins_recurse(PlannerInfo *root, List *joinlist, + Relids grouping_relids) { bool removed = false; ListCell *jl; @@ -2000,7 +2010,8 @@ remove_self_joins_recurse(PlannerInfo *root, List *joinlist) else if (IsA(jlnode, List)) { /* Recursively perform SJE within the sub-joinlist */ - removed |= remove_self_joins_recurse(root, (List *) jlnode); + removed |= remove_self_joins_recurse(root, (List *) jlnode, + grouping_relids); } else elog(ERROR, "unrecognized joinlist node type: %d", @@ -2068,7 +2079,8 @@ remove_self_joins_recurse(PlannerInfo *root, List *joinlist) relids = bms_del_members(relids, group); /* Try to remove self-joins from the group */ - removed |= remove_self_joins_one_group(root, group); + removed |= remove_self_joins_one_group(root, group, + grouping_relids); bms_free(group); } else @@ -2144,11 +2156,47 @@ self_join_candidates_cmp(const void *a, const void *b) bool remove_useless_self_joins(PlannerInfo *root, List *joinlist) { + Relids grouping_relids = NULL; + bool removed; + /* Skip if SJE is disabled, or if the joinlist has less than 2 members. */ if (!enable_self_join_elimination || joinlist == NIL || (list_length(joinlist) == 1 && !IsA(linitial(joinlist), List))) return false; + /* + * With grouping sets, grouping items that become the same expression + * after SJE are not necessarily interchangeable: either item may be + * replaced with NULL independently. Collect the base relids referenced + * by grouping expressions, so that only SJE candidates that could affect + * those expressions are rejected. + * + * GroupingSet nodes contain sortgrouprefs rather than expressions, so use + * groupClause to locate the corresponding target-list expressions. Pass + * NULL as the root to pull_varnos() so that it recurses into any + * PlaceHolderVars rather than using their ph_eval_at relids. + */ + if (root->parse->groupingSets != NIL) + { + ListCell *lc; + + foreach(lc, root->parse->groupClause) + { + SortGroupClause *sgc = lfirst_node(SortGroupClause, lc); + TargetEntry *tle; + Relids expr_relids; + + tle = get_sortgroupclause_tle(sgc, root->parse->targetList); + expr_relids = pull_varnos(NULL, (Node *) tle->expr); + grouping_relids = bms_add_members(grouping_relids, expr_relids); + bms_free(expr_relids); + } + } + /* Try to merge pairs of self-joined relations. */ - return remove_self_joins_recurse(root, joinlist); + removed = remove_self_joins_recurse(root, joinlist, grouping_relids); + + bms_free(grouping_relids); + + return removed; } diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 526430a5ea0..932f3afa6c8 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -9597,6 +9597,331 @@ where t1.a = s.a; Filter: (a IS NOT NULL) (2 rows) +-- Ensure that SJE is not performed on relations referenced in grouping sets, +-- since grouping sets can independently nullify columns that would otherwise +-- be considered equivalent. +-- 1. CUBE with aggregates +explain (costs off) +select t1.a, t2.a, count(*), sum(t1.b) from sj t1 join sj t2 on t1.a = t2.a +group by cube (t1.a, t2.a) order by 1, 2; + QUERY PLAN +------------------------------------------- + Sort + Sort Key: t1.a, t2.a + -> MixedAggregate + Hash Key: t1.a, t2.a + Hash Key: t1.a + Hash Key: t2.a + Group Key: () + -> Nested Loop + Join Filter: (t1.a = t2.a) + -> Seq Scan on sj t1 + -> Materialize + -> Seq Scan on sj t2 +(12 rows) + +select t1.a, t2.a, count(*), sum(t1.b) from sj t1 join sj t2 on t1.a = t2.a +group by cube (t1.a, t2.a) order by 1, 2; + a | a | count | sum +---+---+-------+----- + 1 | 1 | 1 | + 1 | | 1 | + 2 | 2 | 1 | 1 + 2 | | 1 | 1 + | 1 | 1 | + | 2 | 1 | 1 + | | 2 | 1 +(7 rows) + +-- 2. ROLLUP +explain (costs off) +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by rollup (t1.a, t2.a) order by 1, 2; + QUERY PLAN +------------------------------------------------- + Sort + Sort Key: t1.a, t2.a + -> GroupAggregate + Group Key: t1.a, t2.a + Group Key: t1.a + Group Key: () + -> Sort + Sort Key: t1.a + -> Nested Loop + Join Filter: (t1.a = t2.a) + -> Seq Scan on sj t1 + -> Materialize + -> Seq Scan on sj t2 +(13 rows) + +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by rollup (t1.a, t2.a) order by 1, 2; + a | a | count +---+---+------- + 1 | 1 | 1 + 1 | | 1 + 2 | 2 | 1 + 2 | | 1 + | | 2 +(5 rows) + +-- 3. Explicit GROUPING SETS +explain (costs off) +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by grouping sets ((t1.a, t2.a), (t1.a), (t2.a), ()) order by 1, 2; + QUERY PLAN +------------------------------------------- + Sort + Sort Key: t1.a, t2.a + -> MixedAggregate + Hash Key: t1.a, t2.a + Hash Key: t1.a + Hash Key: t2.a + Group Key: () + -> Nested Loop + Join Filter: (t1.a = t2.a) + -> Seq Scan on sj t1 + -> Materialize + -> Seq Scan on sj t2 +(12 rows) + +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by grouping sets ((t1.a, t2.a), (t1.a), (t2.a), ()) order by 1, 2; + a | a | count +---+---+------- + 1 | 1 | 1 + 1 | | 1 + 2 | 2 | 1 + 2 | | 1 + | 1 | 1 + | 2 | 1 + | | 2 +(7 rows) + +-- 4. Asymmetric GROUPING SETS +explain (costs off) +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by grouping sets ((t1.a), (t2.a)) order by 1, 2; + QUERY PLAN +------------------------------------------- + Sort + Sort Key: t1.a, t2.a + -> HashAggregate + Hash Key: t1.a + Hash Key: t2.a + -> Nested Loop + Join Filter: (t1.a = t2.a) + -> Seq Scan on sj t1 + -> Materialize + -> Seq Scan on sj t2 +(10 rows) + +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by grouping sets ((t1.a), (t2.a)) order by 1, 2; + a | a | count +---+---+------- + 1 | | 1 + 2 | | 1 + | 1 | 1 + | 2 | 1 +(4 rows) + +-- 5. Multiple unique keys with CUBE +explain (costs off) +select t1.a, t2.c, count(*) from sj t1 join sj t2 on t1.a = t2.a and t1.c = t2.c +group by cube (t1.a, t2.c) order by 1, 2; + QUERY PLAN +-------------------------------------------------------------- + Sort + Sort Key: t1.a, t2.c + -> MixedAggregate + Hash Key: t1.a, t2.c + Hash Key: t1.a + Hash Key: t2.c + Group Key: () + -> Nested Loop + Join Filter: ((t2.a = t1.a) AND (t1.c = t2.c)) + -> Seq Scan on sj t1 + -> Materialize + -> Seq Scan on sj t2 +(12 rows) + +select t1.a, t2.c, count(*) from sj t1 join sj t2 on t1.a = t2.a and t1.c = t2.c +group by cube (t1.a, t2.c) order by 1, 2; + a | c | count +---+---+------- + 1 | 2 | 1 + 1 | | 1 + 2 | 1 | 1 + 2 | | 1 + | 1 | 1 + | 2 | 1 + | | 2 +(7 rows) + +-- 6. Pulled-up subquery with self-join under grouping sets +explain (costs off) +select x, y, count(*) +from (select t1.a as x, t2.a as y from sj t1 join sj t2 on t1.a = t2.a) s +group by cube (x, y) order by 1, 2; + QUERY PLAN +------------------------------------------- + Sort + Sort Key: t1.a, t2.a + -> MixedAggregate + Hash Key: t1.a, t2.a + Hash Key: t1.a + Hash Key: t2.a + Group Key: () + -> Nested Loop + Join Filter: (t1.a = t2.a) + -> Seq Scan on sj t1 + -> Materialize + -> Seq Scan on sj t2 +(12 rows) + +select x, y, count(*) +from (select t1.a as x, t2.a as y from sj t1 join sj t2 on t1.a = t2.a) s +group by cube (x, y) order by 1, 2; + x | y | count +---+---+------- + 1 | 1 | 1 + 1 | | 1 + 2 | 2 | 1 + 2 | | 1 + | 1 | 1 + | 2 | 1 + | | 2 +(7 rows) + +-- 7. CUBE with HAVING and non-Var targetlist expression +select t1.a, t2.a, coalesce(t2.a, -1) +from sj t1 join sj t2 on t1.a = t2.a +group by cube (t1.a, t2.a) +having t2.a is null +order by 1, 2; + a | a | coalesce +---+---+---------- + 1 | | -1 + 2 | | -1 + | | -1 +(3 rows) + +-- 8. 3-way self-join where t1 and t3 are in GROUP BY (SJE must not +-- transitively merge t1 and t3 via t2) +explain (costs off) +select t1.a, t3.a, count(*) +from sj t1 join sj t2 on t1.a = t2.a join sj t3 on t2.a = t3.a +group by cube (t1.a, t3.a) order by 1, 2; + QUERY PLAN +------------------------------------------------------- + Sort + Sort Key: t1.a, t3.a + -> MixedAggregate + Hash Key: t1.a, t3.a + Hash Key: t1.a + Hash Key: t3.a + Group Key: () + -> Nested Loop + Join Filter: (t1.a = t3.a) + -> Seq Scan on sj t3 + -> Materialize + -> Nested Loop + Join Filter: (t1.a = t2.a) + -> Seq Scan on sj t1 + -> Materialize + -> Seq Scan on sj t2 +(16 rows) + +select t1.a, t3.a, count(*) +from sj t1 join sj t2 on t1.a = t2.a join sj t3 on t2.a = t3.a +group by cube (t1.a, t3.a) order by 1, 2; + a | a | count +---+---+------- + 1 | 1 | 1 + 1 | | 1 + 2 | 2 | 1 + 2 | | 1 + | 1 | 1 + | 2 | 1 + | | 2 +(7 rows) + +-- 9. Grouping sets not referencing the self-joined relations (SJE should +-- still apply to t1 and t2) +explain (costs off) +select t3.b, count(*) +from sj t1 join sj t2 on t1.a = t2.a join sj t3 on t1.a = t3.a +group by rollup (t3.b) order by 1, 2; + QUERY PLAN +--------------------------------------------------- + Sort + Sort Key: t3.b, (count(*)) + -> MixedAggregate + Hash Key: t3.b + Group Key: () + -> Nested Loop + Join Filter: (t2.a = t3.a) + -> Seq Scan on sj t3 + -> Materialize + -> Seq Scan on sj t2 + Filter: (a IS NOT NULL) +(11 rows) + +select t3.b, count(*) +from sj t1 join sj t2 on t1.a = t2.a join sj t3 on t1.a = t3.a +group by rollup (t3.b) order by 1, 2; + b | count +---+------- + 1 | 1 + | 1 + | 2 +(3 rows) + +-- 10. Constant in GROUPING SETS (SJE should still apply) +explain (costs off) +select 1, count(*) +from sj t1 join sj t2 on t1.a = t2.a +group by rollup (1); + QUERY PLAN +--------------------------------- + MixedAggregate + Hash Key: 1 + Group Key: () + -> Seq Scan on sj t2 + Filter: (a IS NOT NULL) +(5 rows) + +-- 11. Outer self-join with inner subquery containing grouping sets (SJE should +-- still apply) +explain (costs off) +select t1.a, t2.a, sub.ct from sj t1 join sj t2 on t1.a = t2.a +join (select a, count(*) as ct from sj group by rollup(a)) sub on t1.a = sub.a +order by 1, 2; + QUERY PLAN +--------------------------------------------- + Sort + Sort Key: t2.a + -> Nested Loop + Join Filter: (t2.a = sj.a) + -> MixedAggregate + Hash Key: sj.a + Group Key: () + -> Seq Scan on sj + -> Materialize + -> Seq Scan on sj t2 + Filter: (a IS NOT NULL) +(11 rows) + +select t1.a, t2.a, sub.ct from sj t1 join sj t2 on t1.a = t2.a +join (select a, count(*) as ct from sj group by rollup(a)) sub on t1.a = sub.a +order by 1, 2; + a | a | ct +---+---+---- + 1 | 1 | 1 + 2 | 2 | 1 +(2 rows) + -- Degenerated case. explain (costs off) select * from diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 27b44016717..5062ca59ace 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -3674,6 +3674,105 @@ select * from sj t1 on true where t1.a = s.a; +-- Ensure that SJE is not performed on relations referenced in grouping sets, +-- since grouping sets can independently nullify columns that would otherwise +-- be considered equivalent. +-- 1. CUBE with aggregates +explain (costs off) +select t1.a, t2.a, count(*), sum(t1.b) from sj t1 join sj t2 on t1.a = t2.a +group by cube (t1.a, t2.a) order by 1, 2; + +select t1.a, t2.a, count(*), sum(t1.b) from sj t1 join sj t2 on t1.a = t2.a +group by cube (t1.a, t2.a) order by 1, 2; + +-- 2. ROLLUP +explain (costs off) +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by rollup (t1.a, t2.a) order by 1, 2; + +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by rollup (t1.a, t2.a) order by 1, 2; + +-- 3. Explicit GROUPING SETS +explain (costs off) +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by grouping sets ((t1.a, t2.a), (t1.a), (t2.a), ()) order by 1, 2; + +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by grouping sets ((t1.a, t2.a), (t1.a), (t2.a), ()) order by 1, 2; + +-- 4. Asymmetric GROUPING SETS +explain (costs off) +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by grouping sets ((t1.a), (t2.a)) order by 1, 2; + +select t1.a, t2.a, count(*) from sj t1 join sj t2 on t1.a = t2.a +group by grouping sets ((t1.a), (t2.a)) order by 1, 2; + +-- 5. Multiple unique keys with CUBE +explain (costs off) +select t1.a, t2.c, count(*) from sj t1 join sj t2 on t1.a = t2.a and t1.c = t2.c +group by cube (t1.a, t2.c) order by 1, 2; + +select t1.a, t2.c, count(*) from sj t1 join sj t2 on t1.a = t2.a and t1.c = t2.c +group by cube (t1.a, t2.c) order by 1, 2; + +-- 6. Pulled-up subquery with self-join under grouping sets +explain (costs off) +select x, y, count(*) +from (select t1.a as x, t2.a as y from sj t1 join sj t2 on t1.a = t2.a) s +group by cube (x, y) order by 1, 2; + +select x, y, count(*) +from (select t1.a as x, t2.a as y from sj t1 join sj t2 on t1.a = t2.a) s +group by cube (x, y) order by 1, 2; + +-- 7. CUBE with HAVING and non-Var targetlist expression +select t1.a, t2.a, coalesce(t2.a, -1) +from sj t1 join sj t2 on t1.a = t2.a +group by cube (t1.a, t2.a) +having t2.a is null +order by 1, 2; + +-- 8. 3-way self-join where t1 and t3 are in GROUP BY (SJE must not +-- transitively merge t1 and t3 via t2) +explain (costs off) +select t1.a, t3.a, count(*) +from sj t1 join sj t2 on t1.a = t2.a join sj t3 on t2.a = t3.a +group by cube (t1.a, t3.a) order by 1, 2; + +select t1.a, t3.a, count(*) +from sj t1 join sj t2 on t1.a = t2.a join sj t3 on t2.a = t3.a +group by cube (t1.a, t3.a) order by 1, 2; + +-- 9. Grouping sets not referencing the self-joined relations (SJE should +-- still apply to t1 and t2) +explain (costs off) +select t3.b, count(*) +from sj t1 join sj t2 on t1.a = t2.a join sj t3 on t1.a = t3.a +group by rollup (t3.b) order by 1, 2; + +select t3.b, count(*) +from sj t1 join sj t2 on t1.a = t2.a join sj t3 on t1.a = t3.a +group by rollup (t3.b) order by 1, 2; + +-- 10. Constant in GROUPING SETS (SJE should still apply) +explain (costs off) +select 1, count(*) +from sj t1 join sj t2 on t1.a = t2.a +group by rollup (1); + +-- 11. Outer self-join with inner subquery containing grouping sets (SJE should +-- still apply) +explain (costs off) +select t1.a, t2.a, sub.ct from sj t1 join sj t2 on t1.a = t2.a +join (select a, count(*) as ct from sj group by rollup(a)) sub on t1.a = sub.a +order by 1, 2; + +select t1.a, t2.a, sub.ct from sj t1 join sj t2 on t1.a = t2.a +join (select a, count(*) as ct from sj group by rollup(a)) sub on t1.a = sub.a +order by 1, 2; + -- Degenerated case. explain (costs off) select * from -- 2.56.0.611.g1030f0b940-goog
^ permalink raw reply [nested|flat] 5+ messages in thread


end of thread, other threads:[~2026-11-12 21:34 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-27 07:36 BUG #19800: Wrong results: self-join elimination drops an ORDER BY sort key with GROUPING SETS / CUBE / ROLLUP PG Bug reporting form <noreply@postgresql.org>
2026-10-27 09:28 ` Tender Wang <tndrwang@gmail.com>
2026-10-29 06:41   ` Narayanan Venkateswaran <narayananvpostgres@gmail.com>
2026-11-11 06:38     ` Tender Wang <tndrwang@gmail.com>
2026-11-12 21:34       ` Narayanan Venkateswaran <narayananvpostgres@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox