pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19837: ORDER BY/DISTINCT ON use wrong column with grouping set duplicates
3+ messages / 2 participants
[nested] [flat]
* BUG #19837: ORDER BY/DISTINCT ON use wrong column with grouping set duplicates
@ 2026-11-06 19:23 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2026-11-06 19:23 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 125atishprak@gmail.com
The following bug has been logged on the website:
Bug reference: 19837
Logged by: Atish Prakash
Email address: 125atishprak@gmail.com
PostgreSQL version: 19rc1
Operating system: Linux x86_64 (kernel 7.0.0, glibc 2.43)
Description:
When the same expression is selected twice and the two outputs are separate
grouping-set members, they can hold different values, because each grouping
set
nulls only one of them. ORDER BY and DISTINCT ON on the second output still
act
on the first. The result is wrongly ordered, LIMIT returns the wrong row,
and
DISTINCT ON loses a row. No error is raised.
Minimal example:
CREATE TABLE t (a int);
INSERT INTO t VALUES (1), (2);
SELECT a AS x, a AS y FROM t GROUP BY GROUPING SETS ((1), (2)) ORDER BY
2;
Actual output, with y not sorted:
x | y
------+------
1 | NULL
2 | NULL
NULL | 2
NULL | 1
Expected output, which is what the same query returns behind an OFFSET 0
barrier:
x | y
------+------
NULL | 1
NULL | 2
2 | NULL
1 | NULL
The EXPLAIN output shows one sort key, t.a. I have not traced the cause. My
guess is that both outputs are the same expression with the same
nullingrels,
so they share an EquivalenceClass and the sort uses the first matching
targetlist entry.
Related:
- The query in this 2022 thread still returns the wrong order (case 4
below):
https://www.postgresql.org/message-id/3642474.1666568956@sss.pgh.pa.us
This may be the known issue described there, but I could not find a report
or
fix for the current behaviour.
- BUG #19800 and #19805 look similar but need self-join elimination. This
query
has no join.
- I reported a related deparse problem separately: "view with GROUP BY
ROLLUP
over duplicate columns changes results after dump/restore".
I have no patch for this. I found it with a SQL fuzzer.
Versions tested, all with identical output:
- 18.6 (REL_18_STABLE at 813a74bc5a)
- 19rc1 (REL_19_STABLE at 0b1eb8b917)
- master at 72b9498df9, plus my unrelated uncommitted patch to ruleutils.c
Platform: Linux 7.0.0-42-generic x86_64, glibc 2.43, Intel i9-13900HX, 14
GiB RAM
Client and server: psql and postgres from the same build, Unix socket, psql
-X
Build: clang 21.1.8, CFLAGS=-O1 -g, --enable-cassert --enable-debug
--without-icu --without-readline --without-libxml --without-openssl
Non-default server settings: fsync=off, shared_buffers=48MB, lc_messages=C
Install: built from git with no other deviations from the instructions
Reproducer, run with psql -X -f in a new database:
\pset null NULL
CREATE TABLE t (a int);
INSERT INTO t VALUES (1), (2);
\echo 1. ORDER BY y: y is not sorted
SELECT a AS x, a AS y FROM t GROUP BY GROUPING SETS ((1), (2)) ORDER BY 2;
EXPLAIN (COSTS OFF, VERBOSE) SELECT a AS x, a AS y FROM t GROUP BY GROUPING
SETS ((1), (2)) ORDER BY 2;
\echo Same rows behind a subquery barrier, correctly sorted
SELECT * FROM (SELECT a AS x, a AS y FROM t GROUP BY GROUPING SETS ((1),
(2)) OFFSET 0) s ORDER BY y;
\echo 2. ORDER BY y LIMIT 1: wrong row
SELECT a AS x, a AS y FROM t GROUP BY GROUPING SETS ((1), (2)) ORDER BY 2
LIMIT 1;
SELECT * FROM (SELECT a AS x, a AS y FROM t GROUP BY GROUPING SETS ((1),
(2)) OFFSET 0) s ORDER BY y LIMIT 1;
\echo 3. DISTINCT ON (y): y = NULL twice, y = 1 lost
SELECT DISTINCT ON (2) a AS x, a AS y FROM t GROUP BY GROUPING SETS ((1),
(2)) ORDER BY 2;
SELECT DISTINCT ON (y) x, y FROM (SELECT a AS x, a AS y FROM t GROUP BY
GROUPING SETS ((1), (2)) OFFSET 0) s ORDER BY y;
\echo 4. Query from the 2022 thread: ai1 is not sorted, NULLs are
interleaved
SELECT i AS ai1, i AS ai2 FROM generate_series(1,3) i GROUP BY ai2,
ROLLUP(ai1) ORDER BY ai1, ai2;
Full output:
Null display is "NULL".
CREATE TABLE
INSERT 0 2
1. ORDER BY y: y is not sorted
x | y
------+------
1 | NULL
2 | NULL
NULL | 2
NULL | 1
(4 rows)
QUERY PLAN
----------------------------------
Sort
Output: a, a
Sort Key: t.a
-> HashAggregate
Output: a, a
Hash Key: t.a
Hash Key: t.a
-> Seq Scan on public.t
Output: a, a
(9 rows)
Same rows behind a subquery barrier, correctly sorted
x | y
------+------
NULL | 1
NULL | 2
2 | NULL
1 | NULL
(4 rows)
2. ORDER BY y LIMIT 1: wrong row
x | y
---+------
1 | NULL
(1 row)
x | y
------+---
NULL | 1
(1 row)
3. DISTINCT ON (y): y = NULL twice, y = 1 lost
x | y
------+------
1 | NULL
2 | NULL
NULL | 2
(3 rows)
x | y
------+------
NULL | 1
NULL | 2
2 | NULL
(3 rows)
4. Query from the 2022 thread: ai1 is not sorted, NULLs are interleaved
ai1 | ai2
------+-----
1 | 1
NULL | 1
2 | 2
NULL | 2
3 | 3
NULL | 3
(6 rows)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19837: ORDER BY/DISTINCT ON use wrong column with grouping set duplicates
@ 2026-11-13 21:17 Andrey Rachitskiy <pl0h0yp1@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Andrey Rachitskiy @ 2026-11-13 21:17 UTC (permalink / raw)
To: 125atishprak@gmail.com; pgsql-bugs@lists.postgresql.org; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
сб, 7 нояб. 2026 г. в 20:54, PG Bug reporting form <noreply@postgresql.org>:
> The following bug has been logged on the website:
>
> Bug reference: 19837
> Logged by: Atish Prakash
> Email address: 125atishprak@gmail.com
> PostgreSQL version: 19rc1
> Operating system: Linux x86_64 (kernel 7.0.0, glibc 2.43)
> Description:
>
> When the same expression is selected twice and the two outputs are separate
> grouping-set members, they can hold different values, because each grouping
> set
> nulls only one of them. ORDER BY and DISTINCT ON on the second output still
> act
> on the first. The result is wrongly ordered, LIMIT returns the wrong row,
> and
> DISTINCT ON loses a row. No error is raised.
>
> Minimal example:
>
> CREATE TABLE t (a int);
> INSERT INTO t VALUES (1), (2);
> SELECT a AS x, a AS y FROM t GROUP BY GROUPING SETS ((1), (2)) ORDER BY
> 2;
>
> Actual output, with y not sorted:
>
> x | y
> ------+------
> 1 | NULL
> 2 | NULL
> NULL | 2
> NULL | 1
>
> Expected output, which is what the same query returns behind an OFFSET 0
> barrier:
>
> x | y
> ------+------
> NULL | 1
> NULL | 2
> 2 | NULL
> 1 | NULL
>
> The EXPLAIN output shows one sort key, t.a. I have not traced the cause. My
> guess is that both outputs are the same expression with the same
> nullingrels,
> so they share an EquivalenceClass and the sort uses the first matching
> targetlist entry.
>
> Related:
>
> - The query in this 2022 thread still returns the wrong order (case 4
> below):
> https://www.postgresql.org/message-id/3642474.1666568956@sss.pgh.pa.us
> This may be the known issue described there, but I could not find a
> report
> or
> fix for the current behaviour.
> - BUG #19800 and #19805 look similar but need self-join elimination. This
> query
> has no join.
> - I reported a related deparse problem separately: "view with GROUP BY
> ROLLUP
> over duplicate columns changes results after dump/restore".
>
> I have no patch for this. I found it with a SQL fuzzer.
>
>
> Hi, Atish!
Thanks for the report.
While investigating this bug, I also found a ROLLUP bug.
SELECT i AS ai1, i AS ai2
FROM generate_series(1, 3) i
GROUP BY ai2, ROLLUP(ai1)
ORDER BY ai1, ai2;
With a sorted aggregate the ORDER BY Sort is not added. The rows come
out in grouping order, by ai2.
0001 fixes the ROLLUP query. The nulling bit from f5050f795ae was
attached to ai2, which is in every grouping set, so ai1 never got it.
0002 fixes GROUPING SETS ((x), (y)). A column that only some grouping
sets null gets its own EquivalenceClass and is matched by sortref.
Volatile ORDER BY keys are already matched that way.
Attachments: download all (2 files, .tar.gz)
0002-Keep-separate-equivalence-classes-for-grouping-set-o.patch (text/x-patch, 25.2K) download
From 44edd0a127af28d4b71316259da65c233c28e69f Mon Sep 17 00:00:00 2001
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Date: Sat, 14 Nov 2026 01:14:47 +0500
Subject: [PATCH] Keep separate equivalence classes for grouping-set outputs.
Two outputs of one expression can hold different values when grouping
sets null them separately. equal() does not see that, so they shared
one EquivalenceClass. ORDER BY, LIMIT and DISTINCT ON used the first
target entry, and a sorted aggregate could skip the Sort.
A column that only some grouping sets null now gets its own
EquivalenceClass, matched by sortref, as volatile sort keys already are.
Pathkeys for the grouping input stay shared. Those values are equal
before nulling.
---
src/backend/nodes/outfuncs.c | 1 +
src/backend/optimizer/path/equivclass.c | 105 ++++++++++++++++-----
src/backend/optimizer/path/indxpath.c | 8 +-
src/backend/optimizer/path/pathkeys.c | 42 ++++++---
src/backend/optimizer/plan/createplan.c | 22 +++--
src/backend/optimizer/plan/initsplan.c | 2 +-
src/include/nodes/pathnodes.h | 17 ++++
src/include/optimizer/paths.h | 3 +-
src/test/regress/expected/groupingsets.out | 84 +++++++++++++++++
src/test/regress/sql/groupingsets.sql | 36 +++++++
10 files changed, 267 insertions(+), 53 deletions(-)
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 40990143927..ba06a3a19bd 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -477,6 +477,7 @@ _outEquivalenceClass(StringInfo str, const EquivalenceClass *node)
WRITE_BITMAPSET_FIELD(ec_relids);
WRITE_BOOL_FIELD(ec_has_const);
WRITE_BOOL_FIELD(ec_has_volatile);
+ WRITE_BOOL_FIELD(ec_sortref_specific);
WRITE_BOOL_FIELD(ec_broken);
WRITE_UINT_FIELD(ec_sortref);
WRITE_UINT_FIELD(ec_min_security);
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 439cb651c01..36e27cffc9f 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -308,8 +308,8 @@ process_equivalence(PlannerInfo *root,
EquivalenceClass *cur_ec = (EquivalenceClass *) lfirst(lc1);
ListCell *lc2;
- /* Never match to a volatile EC */
- if (cur_ec->ec_has_volatile)
+ /* Value is not identified by the expression alone */
+ if (EC_SORTREF_SPECIFIC(cur_ec))
continue;
/*
@@ -491,6 +491,7 @@ process_equivalence(PlannerInfo *root,
ec->ec_relids = NULL;
ec->ec_has_const = false;
ec->ec_has_volatile = false;
+ ec->ec_sortref_specific = false;
ec->ec_broken = false;
ec->ec_sortref = 0;
ec->ec_min_security = restrictinfo->security_level;
@@ -701,6 +702,36 @@ add_child_eq_member(PlannerInfo *root, EquivalenceClass *ec, int ec_index,
return em;
}
+/*
+ * sortref_nulled_by_grouping_sets
+ * True when some grouping sets include this sortref and others do not.
+ *
+ * The output column is then NULL in some sets and the underlying value in
+ * others. A column that is in every set is never nulled.
+ */
+static bool
+sortref_nulled_by_grouping_sets(PlannerInfo *root, Index sortref)
+{
+ ListCell *lc;
+ bool in_some = false;
+ bool missing_from_some = false;
+
+ if (sortref == 0 || root->parse->groupingSets == NIL)
+ return false;
+
+ foreach(lc, root->parse->groupingSets)
+ {
+ if (list_member_int((List *) lfirst(lc), (int) sortref))
+ in_some = true;
+ else
+ missing_from_some = true;
+
+ if (in_some && missing_from_some)
+ return true;
+ }
+
+ return false;
+}
/*
* get_eclass_for_sort_expr
@@ -711,6 +742,12 @@ add_child_eq_member(PlannerInfo *root, EquivalenceClass *ec, int ec_index,
* sortref is the SortGroupRef of the originating SortGroupClause, if any,
* or zero if not. (It should never be zero if the expression is volatile!)
*
+ * below_grouping is true when the expression is the grouping input, after
+ * the grouping step's nulling bit has been removed. Those values are equal
+ * across sortrefs that grouping sets will later null separately, so they
+ * share an ordinary EC. A post-grouping reference to a column that only
+ * some sets null does not.
+ *
* If rel is not NULL, it identifies a specific relation we're considering
* a path for, and indicates that child EC members for that relation can be
* considered. Otherwise child members are ignored. (Note: since child EC
@@ -741,7 +778,8 @@ get_eclass_for_sort_expr(PlannerInfo *root,
Oid collation,
Index sortref,
Relids relids,
- bool create_it)
+ bool create_it,
+ bool below_grouping)
{
JoinDomain *jdomain;
Relids expr_relids;
@@ -749,12 +787,16 @@ get_eclass_for_sort_expr(PlannerInfo *root,
EquivalenceMember *newem;
ListCell *lc1;
MemoryContext oldcontext;
+ bool sortref_specific;
/*
* Ensure the expression exposes the correct type and collation.
*/
expr = canonicalize_ec_expression(expr, opcintype, collation);
+ sortref_specific = (!below_grouping &&
+ sortref_nulled_by_grouping_sets(root, sortref));
+
/*
* Since SortGroupClause nodes are top-level expressions (GROUP BY, ORDER
* BY, etc), they can be presumed to belong to the top JoinDomain.
@@ -771,12 +813,20 @@ get_eclass_for_sort_expr(PlannerInfo *root,
EquivalenceMember *cur_em;
/*
- * Never match to a volatile EC, except when we are looking at another
- * reference to the same volatile SortGroupClause.
+ * A volatile EC matches only another reference to the same
+ * SortGroupClause. A grouping-set EC matches only that sortref, and
+ * not a below-grouping reference to the same expression. A
+ * post-grouping column that only some sets null must not join an
+ * ordinary EC for the same expr.
*/
if (cur_ec->ec_has_volatile &&
(sortref == 0 || sortref != cur_ec->ec_sortref))
continue;
+ if (cur_ec->ec_sortref_specific &&
+ (below_grouping || sortref != cur_ec->ec_sortref))
+ continue;
+ if (sortref_specific && !cur_ec->ec_sortref_specific)
+ continue;
if (collation != cur_ec->ec_collation)
continue;
@@ -829,14 +879,16 @@ get_eclass_for_sort_expr(PlannerInfo *root,
newec->ec_relids = NULL;
newec->ec_has_const = false;
newec->ec_has_volatile = contain_volatile_functions((Node *) expr);
+ newec->ec_sortref_specific = sortref_specific;
newec->ec_broken = false;
newec->ec_sortref = sortref;
newec->ec_min_security = UINT_MAX;
newec->ec_max_security = 0;
newec->ec_merged = NULL;
- if (newec->ec_has_volatile && sortref == 0) /* should not happen */
- elog(ERROR, "volatile EquivalenceClass has no sortref");
+ if ((newec->ec_has_volatile || newec->ec_sortref_specific) &&
+ sortref == 0) /* should not happen */
+ elog(ERROR, "sortref-specific EquivalenceClass has no sortref");
/*
* Get the precise set of relids appearing in the expression.
@@ -855,6 +907,7 @@ get_eclass_for_sort_expr(PlannerInfo *root,
if (newec->ec_has_const)
{
if (newec->ec_has_volatile ||
+ newec->ec_sortref_specific ||
expression_returns_set((Node *) expr) ||
contain_agg_clause((Node *) expr) ||
contain_window_function((Node *) expr))
@@ -1087,9 +1140,11 @@ relation_can_be_sorted_early(PlannerInfo *root, RelOptInfo *rel,
ListCell *lc;
/*
- * Reject volatile ECs immediately; such sorts must always be postponed.
+ * Reject ECs whose value is not the expression. A volatile sort must run
+ * at the right plan step. A grouping-set output that only some sets null
+ * cannot be produced by sorting the input relation.
*/
- if (ec->ec_has_volatile)
+ if (EC_SORTREF_SPECIFIC(ec))
return false;
/*
@@ -2353,8 +2408,8 @@ reconsider_outer_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo,
/* Ignore EC unless it contains pseudoconstants */
if (!cur_ec->ec_has_const)
continue;
- /* Never match to a volatile EC */
- if (cur_ec->ec_has_volatile)
+ /* Value is not identified by the expression alone */
+ if (EC_SORTREF_SPECIFIC(cur_ec))
continue;
/* It has to match the outer-join clause as to semantics, too */
if (collation != cur_ec->ec_collation)
@@ -2497,8 +2552,8 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
/* Ignore EC unless it contains pseudoconstants */
if (!cur_ec->ec_has_const)
continue;
- /* Never match to a volatile EC */
- if (cur_ec->ec_has_volatile)
+ /* Value is not identified by the expression alone */
+ if (EC_SORTREF_SPECIFIC(cur_ec))
continue;
/* It has to match the outer-join clause as to semantics, too */
if (collation != cur_ec->ec_collation)
@@ -2683,8 +2738,8 @@ exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2, Oid opfamily)
bool item2member = false;
ListCell *lc2;
- /* Never match to a volatile EC */
- if (ec->ec_has_volatile)
+ /* Value is not identified by the expression alone */
+ if (EC_SORTREF_SPECIFIC(ec))
continue;
/*
@@ -2765,8 +2820,8 @@ match_eclasses_to_foreign_key_col(PlannerInfo *root,
EquivalenceMember *item2_em = NULL;
ListCell *lc2;
- /* Never match to a volatile EC */
- if (ec->ec_has_volatile)
+ /* Value is not identified by the expression alone */
+ if (EC_SORTREF_SPECIFIC(ec))
continue;
/*
@@ -2879,11 +2934,10 @@ add_child_rel_equivalences(PlannerInfo *root,
EquivalenceClass *cur_ec = (EquivalenceClass *) list_nth(root->eq_classes, i);
/*
- * If this EC contains a volatile expression, then generating child
- * EMs would be downright dangerous, so skip it. We rely on a
- * volatile EC having only one EM.
+ * Skip an EC whose value is not the expression. It has one member,
+ * and a child copy would not be that same value.
*/
- if (cur_ec->ec_has_volatile)
+ if (EC_SORTREF_SPECIFIC(cur_ec))
continue;
/* Sanity check eclass_indexes only contain ECs for parent_rel */
@@ -2993,11 +3047,10 @@ add_child_join_rel_equivalences(PlannerInfo *root,
EquivalenceClass *cur_ec = (EquivalenceClass *) list_nth(root->eq_classes, i);
/*
- * If this EC contains a volatile expression, then generating child
- * EMs would be downright dangerous, so skip it. We rely on a
- * volatile EC having only one EM.
+ * Skip an EC whose value is not the expression. It has one member,
+ * and a child copy would not be that same value.
*/
- if (cur_ec->ec_has_volatile)
+ if (EC_SORTREF_SPECIFIC(cur_ec))
continue;
/* Sanity check on get_eclass_indexes_for_relids result */
@@ -3109,7 +3162,7 @@ add_child_rel_pathkey_equivalences(PlannerInfo *root, RelOptInfo *child_rel,
{
EquivalenceClass *ec = lfirst_node(PathKey, lc)->pk_eclass;
- if (ec->ec_has_volatile)
+ if (EC_SORTREF_SPECIFIC(ec))
continue;
foreach_node(EquivalenceMember, cur_em, ec->ec_members)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 3f5d4fa3182..a1b950111ea 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -3737,8 +3737,12 @@ match_pathkeys_to_index(IndexOptInfo *index, List *pathkeys,
if (pathkey->pk_cmptype != COMPARE_LT || pathkey->pk_nulls_first)
return;
- /* If eclass is volatile, no hope of using an indexscan */
- if (pathkey->pk_eclass->ec_has_volatile)
+ /*
+ * No index order matches an EC whose value is not the expression.
+ * That is a volatile key, or a grouping-set output that only some
+ * sets null.
+ */
+ if (EC_SORTREF_SPECIFIC(pathkey->pk_eclass))
return;
/*
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 50651813a11..70736f9aaac 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -193,6 +193,9 @@ pathkey_is_redundant(PathKey *new_pathkey, List *pathkeys)
* create_it is true if we should create any missing EquivalenceClass
* needed to represent the sort key. If it's false, we return NULL if the
* sort key isn't already present in any EquivalenceClass.
+ *
+ * below_grouping is true when expr is a grouping input whose grouping
+ * nulling bit has been removed. See get_eclass_for_sort_expr.
*/
static PathKey *
make_pathkey_from_sortinfo(PlannerInfo *root,
@@ -204,7 +207,8 @@ make_pathkey_from_sortinfo(PlannerInfo *root,
bool nulls_first,
Index sortref,
Relids rel,
- bool create_it)
+ bool create_it,
+ bool below_grouping)
{
CompareType cmptype;
Oid equality_op;
@@ -234,7 +238,8 @@ make_pathkey_from_sortinfo(PlannerInfo *root,
/* Now find or (optionally) create a matching EquivalenceClass */
eclass = get_eclass_for_sort_expr(root, expr,
opfamilies, opcintype, collation,
- sortref, rel, create_it);
+ sortref, rel, create_it,
+ below_grouping);
/* Fail if no EC and !create_it */
if (!eclass)
@@ -259,7 +264,8 @@ make_pathkey_from_sortop(PlannerInfo *root,
bool reverse_sort,
bool nulls_first,
Index sortref,
- bool create_it)
+ bool create_it,
+ bool below_grouping)
{
Oid opfamily,
opcintype,
@@ -284,7 +290,8 @@ make_pathkey_from_sortop(PlannerInfo *root,
nulls_first,
sortref,
NULL,
- create_it);
+ create_it,
+ below_grouping);
}
@@ -790,6 +797,7 @@ build_index_pathkeys(PlannerInfo *root,
nulls_first,
0,
index->rel->relids,
+ false,
false);
if (cpathkey)
@@ -949,6 +957,7 @@ build_partition_pathkeys(PlannerInfo *root, RelOptInfo *partrel,
ScanDirectionIsBackward(scandir),
0,
partrel->relids,
+ false,
false);
@@ -1024,7 +1033,8 @@ build_expression_pathkey(PlannerInfo *root,
(cmptype == COMPARE_GT),
0,
rel,
- create_it);
+ create_it,
+ false);
if (cpathkey)
pathkeys = list_make1(cpathkey);
@@ -1066,18 +1076,19 @@ convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
EquivalenceClass *sub_eclass = sub_pathkey->pk_eclass;
PathKey *best_pathkey = NULL;
- if (sub_eclass->ec_has_volatile)
+ if (EC_SORTREF_SPECIFIC(sub_eclass))
{
/*
- * If the sub_pathkey's EquivalenceClass is volatile, then it must
- * have come from an ORDER BY clause, and we have to match it to
- * that same targetlist entry.
+ * The sub_pathkey's EquivalenceClass does not identify a value by
+ * expression alone. Match it to the sortref's targetlist entry.
+ * Volatile ORDER BY keys are one case. A grouping-set output
+ * that only some sets null is the other.
*/
TargetEntry *tle;
Var *outer_var;
if (sub_eclass->ec_sortref == 0) /* can't happen */
- elog(ERROR, "volatile EquivalenceClass has no sortref");
+ elog(ERROR, "sortref-specific EquivalenceClass has no sortref");
tle = get_sortgroupref_tle(sub_eclass->ec_sortref, subquery_tlist);
Assert(tle);
/* Is TLE actually available to the outer query? */
@@ -1107,6 +1118,7 @@ convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
sub_eclass->ec_collation,
0,
rel->relids,
+ false,
false);
/*
@@ -1189,6 +1201,7 @@ convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
sub_expr_coll,
0,
rel->relids,
+ false,
false);
/*
@@ -1424,7 +1437,8 @@ make_pathkeys_for_sortclauses_extended(PlannerInfo *root,
sortcl->reverse_sort,
sortcl->nulls_first,
sortcl->tleSortGroupRef,
- true);
+ true,
+ remove_group_rtindex);
if (pathkey->pk_eclass->ec_sortref == 0 && set_ec_sortref)
{
/*
@@ -1492,7 +1506,8 @@ initialize_mergeclause_eclasses(PlannerInfo *root, RestrictInfo *restrictinfo)
((OpExpr *) clause)->inputcollid,
0,
NULL,
- true);
+ true,
+ false);
restrictinfo->right_ec =
get_eclass_for_sort_expr(root,
(Expr *) get_rightop(clause),
@@ -1501,7 +1516,8 @@ initialize_mergeclause_eclasses(PlannerInfo *root, RestrictInfo *restrictinfo)
((OpExpr *) clause)->inputcollid,
0,
NULL,
- true);
+ true,
+ false);
}
/*
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 9c1a93f19fb..66e94bb3793 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -6288,15 +6288,16 @@ prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
Oid sortop;
ListCell *j;
- if (ec->ec_has_volatile)
+ if (EC_SORTREF_SPECIFIC(ec))
{
/*
- * If the pathkey's EquivalenceClass is volatile, then it must
- * have come from an ORDER BY clause, and we have to match it to
- * that same targetlist entry.
+ * The pathkey's EquivalenceClass does not identify a value by
+ * expression alone. Match it to the sortref's targetlist entry.
+ * Volatile ORDER BY keys are one case. A grouping-set output
+ * that only some sets null is the other.
*/
if (ec->ec_sortref == 0) /* can't happen */
- elog(ERROR, "volatile EquivalenceClass has no sortref");
+ elog(ERROR, "sortref-specific EquivalenceClass has no sortref");
tle = get_sortgroupref_tle(ec->ec_sortref, tlist);
Assert(tle);
Assert(list_length(ec->ec_members) == 1);
@@ -6833,15 +6834,16 @@ make_unique_from_pathkeys(Plan *lefttree, List *pathkeys, int numCols,
if (keyno >= numCols)
break;
- if (ec->ec_has_volatile)
+ if (EC_SORTREF_SPECIFIC(ec))
{
/*
- * If the pathkey's EquivalenceClass is volatile, then it must
- * have come from an ORDER BY clause, and we have to match it to
- * that same targetlist entry.
+ * The pathkey's EquivalenceClass does not identify a value by
+ * expression alone. Match it to the sortref's targetlist entry.
+ * Volatile ORDER BY keys are one case. A grouping-set output
+ * that only some sets null is the other.
*/
if (ec->ec_sortref == 0) /* can't happen */
- elog(ERROR, "volatile EquivalenceClass has no sortref");
+ elog(ERROR, "sortref-specific EquivalenceClass has no sortref");
tle = get_sortgroupref_tle(ec->ec_sortref, plan->targetlist);
Assert(tle);
Assert(list_length(ec->ec_members) == 1);
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index d2dd1d68868..51f9bf123ab 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -1010,7 +1010,7 @@ get_eclass_for_sortgroupclause(PlannerInfo *root, SortGroupClause *sgc,
/* Now find a matching EquivalenceClass */
return get_eclass_for_sort_expr(root, expr, opfamilies, opcintype,
collation, sgc->tleSortGroupRef,
- NULL, false);
+ NULL, false, false);
}
/*****************************************************************************
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 2ba1dbfb67f..34358e3af35 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1614,6 +1614,14 @@ typedef struct JoinDomain
* entry: consider SELECT random() AS a, random() AS b ... ORDER BY b,a.
* So we record the SortGroupRef of the originating sort clause.
*
+ * Grouping sets have the same hazard. SELECT a AS x, a AS y GROUP BY
+ * GROUPING SETS ((x), (y)) ORDER BY y puts different values in x and y,
+ * because each set nulls only one of them. equal() does not see that.
+ * A shared EC sorts by the first target entry and can treat the aggregate
+ * output as already ordered. A column that some sets null gets an EC with
+ * ec_sortref_specific set, matched only by ec_sortref. Pathkeys for the
+ * grouping input are not marked. Before nulling, those values are equal.
+ *
* Derived equality clauses are stored in ec_derives_list. For small queries,
* this list is scanned directly during lookup. For larger queries -- e.g.,
* with many partitions or joins -- a hash table (ec_derives_hash) is built
@@ -1673,6 +1681,7 @@ typedef struct EquivalenceClass
* for child members (see below) */
bool ec_has_const; /* any pseudoconstants in ec_members? */
bool ec_has_volatile; /* the (sole) member is a volatile expr */
+ bool ec_sortref_specific; /* match this EC only by ec_sortref */
bool ec_broken; /* failed to generate needed clauses? */
Index ec_sortref; /* originating sortclause label, or 0 */
Index ec_min_security; /* minimum security_level in ec_sources */
@@ -1687,6 +1696,14 @@ typedef struct EquivalenceClass
#define EC_MUST_BE_REDUNDANT(eclass) \
((eclass)->ec_has_const)
+/*
+ * True when the expression does not identify the value. Match the EC to
+ * ec_sortref. Volatile sort keys are one case. A grouping-set output that
+ * only some sets null is the other.
+ */
+#define EC_SORTREF_SPECIFIC(eclass) \
+ ((eclass)->ec_has_volatile || (eclass)->ec_sortref_specific)
+
/*
* EquivalenceMember - one member expression of an EquivalenceClass
*
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 64f87da1674..addc9a7e392 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -144,7 +144,8 @@ extern EquivalenceClass *get_eclass_for_sort_expr(PlannerInfo *root,
Oid collation,
Index sortref,
Relids relids,
- bool create_it);
+ bool create_it,
+ bool below_grouping);
extern EquivalenceMember *find_ec_member_matching_expr(EquivalenceClass *ec,
Expr *expr,
Relids relids);
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index 4c4e9c887a2..bc6bf041af6 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -2817,4 +2817,88 @@ select i as ai1, i as ai2 from generate_series(1,3) i
| 3
(6 rows)
+-- Two outputs of one expression still compare equal when both carry the
+-- same grouping nulling bit. ORDER BY, LIMIT and DISTINCT ON must follow
+-- the requested column. y, x breaks ties.
+-- A sorted aggregate can treat its output as already ordered.
+create temp table gstest_dup (a int);
+insert into gstest_dup values (1), (2);
+set enable_hashagg = false;
+select a as x, a as y from gstest_dup
+ group by grouping sets ((x), (y)) order by y, x;
+ x | y
+---+---
+ | 1
+ | 2
+ 1 |
+ 2 |
+(4 rows)
+
+select a as x, a as y from gstest_dup
+ group by grouping sets ((x), (y)) order by x, y;
+ x | y
+---+---
+ 1 |
+ 2 |
+ | 1
+ | 2
+(4 rows)
+
+select a as x, a as y from gstest_dup
+ group by grouping sets ((x), (y)) order by y, x limit 1;
+ x | y
+---+---
+ | 1
+(1 row)
+
+select distinct on (y) a as x, a as y from gstest_dup
+ group by grouping sets ((x), (y)) order by y, x;
+ x | y
+---+---
+ | 1
+ | 2
+ 1 |
+(3 rows)
+
+select a + 1 as x, a + 1 as y from gstest_dup
+ group by grouping sets ((x), (y)) order by y, x;
+ x | y
+---+---
+ | 2
+ | 3
+ 2 |
+ 3 |
+(4 rows)
+
+-- Both outputs are nulled in the same sets, so the values stay equal.
+select a as x, a as y from gstest_dup
+ group by grouping sets ((x, y), ()) order by y, x;
+ x | y
+---+---
+ 1 | 1
+ 2 | 2
+ |
+(3 rows)
+
+-- A constant is not a redundant sort key once a grouping set can null it.
+select 1 as x, 1 as y
+ group by grouping sets ((x), (y)) order by y, x;
+ x | y
+---+---
+ | 1
+ 1 |
+(2 rows)
+
+set enable_hashagg = true;
+select a as x, a as y from gstest_dup
+ group by grouping sets ((x), (y)) order by y, x;
+ x | y
+---+---
+ | 1
+ | 2
+ 1 |
+ 2 |
+(4 rows)
+
+drop table gstest_dup;
-- end
diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql
index cb323a58a8c..535f59ef87d 100644
--- a/src/test/regress/sql/groupingsets.sql
+++ b/src/test/regress/sql/groupingsets.sql
@@ -827,4 +827,40 @@ set enable_hashagg = true;
select i as ai1, i as ai2 from generate_series(1,3) i
group by ai2, rollup(ai1) order by ai2, ai1;
+
+-- Two outputs of one expression still compare equal when both carry the
+-- same grouping nulling bit. ORDER BY, LIMIT and DISTINCT ON must follow
+-- the requested column. y, x breaks ties.
+-- A sorted aggregate can treat its output as already ordered.
+create temp table gstest_dup (a int);
+insert into gstest_dup values (1), (2);
+
+set enable_hashagg = false;
+
+select a as x, a as y from gstest_dup
+ group by grouping sets ((x), (y)) order by y, x;
+select a as x, a as y from gstest_dup
+ group by grouping sets ((x), (y)) order by x, y;
+select a as x, a as y from gstest_dup
+ group by grouping sets ((x), (y)) order by y, x limit 1;
+select distinct on (y) a as x, a as y from gstest_dup
+ group by grouping sets ((x), (y)) order by y, x;
+select a + 1 as x, a + 1 as y from gstest_dup
+ group by grouping sets ((x), (y)) order by y, x;
+
+-- Both outputs are nulled in the same sets, so the values stay equal.
+select a as x, a as y from gstest_dup
+ group by grouping sets ((x, y), ()) order by y, x;
+
+-- A constant is not a redundant sort key once a grouping set can null it.
+select 1 as x, 1 as y
+ group by grouping sets ((x), (y)) order by y, x;
+
+set enable_hashagg = true;
+
+select a as x, a as y from gstest_dup
+ group by grouping sets ((x), (y)) order by y, x;
+
+drop table gstest_dup;
+
-- end
--
2.53.0
0001-Bind-repeated-grouping-expressions-to-their-own-sort.patch (text/x-patch, 8.7K) download
From 46379eb2e574dc82bb961db903a8295ccf0e6503 Mon Sep 17 00:00:00 2001
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Date: Sat, 14 Nov 2026 01:14:19 +0500
Subject: [PATCH] Bind repeated grouping expressions to their own sortref.
substitute_grouped_columns() matched a repeated Var or expression to
the first group clause with the same value. When that clause is in
every grouping set, another output of the same expression lost the
nulling bit. ORDER BY then treated the two outputs as one value.
Each targetlist entry now binds to its own sortref. The first match is
used only when the entry is not itself a grouping column.
---
src/backend/parser/parse_agg.c | 135 +++++++++++++++------
src/test/regress/expected/groupingsets.out | 41 +++++++
src/test/regress/sql/groupingsets.sql | 15 +++
3 files changed, 157 insertions(+), 34 deletions(-)
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index a5424c9d24c..c26992e2157 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -56,6 +56,7 @@ typedef struct
List **func_grouped_rels;
int sublevels_up;
bool in_agg_direct_args;
+ Index preferred_sortref; /* this tlist entry's sortref, or 0 */
} substitute_grouped_columns_context;
static int check_agg_arguments(ParseState *pstate,
@@ -72,6 +73,9 @@ static Node *substitute_grouped_columns(Node *node, ParseState *pstate, Query *q
List **func_grouped_rels);
static Node *substitute_grouped_columns_mutator(Node *node,
substitute_grouped_columns_context *context);
+static Node *match_grouped_column(Node *node, List *groupClauses,
+ substitute_grouped_columns_context *context,
+ bool var_match);
static void finalize_grouping_exprs(Node *node, ParseState *pstate, Query *qry,
List *groupClauses, bool hasJoinRTEs,
bool have_non_var_grouping);
@@ -1366,18 +1370,103 @@ substitute_grouped_columns(Node *node, ParseState *pstate, Query *qry,
context.func_grouped_rels = func_grouped_rels;
context.sublevels_up = 0;
context.in_agg_direct_args = false;
+ context.preferred_sortref = 0;
return substitute_grouped_columns_mutator(node, &context);
}
+/*
+ * If node matches a GROUP BY item, return a Var for that grouping column.
+ * Otherwise return NULL.
+ *
+ * Several items can match one expression. Use the item whose sortref is
+ * context->preferred_sortref. Otherwise use the first match.
+ *
+ * var_match is true when every group item is a Var. Two Vars then match on
+ * varno and varattno. Otherwise the expressions must be equal().
+ */
static Node *
-substitute_grouped_columns_mutator(Node *node,
- substitute_grouped_columns_context *context)
+match_grouped_column(Node *node, List *groupClauses,
+ substitute_grouped_columns_context *context,
+ bool var_match)
{
ListCell *gl;
+ int attnum = 0;
+ int match_attnum = 0;
+ Index match_ref = 0;
+ bool matched = false;
+
+ foreach(gl, groupClauses)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(gl);
+
+ attnum++;
+
+ if (var_match)
+ {
+ Var *var;
+ Var *gvar;
+
+ Assert(IsA(node, Var));
+ var = (Var *) node;
+ gvar = (Var *) tle->expr;
+ if (!(IsA(gvar, Var) &&
+ gvar->varno == var->varno &&
+ gvar->varattno == var->varattno &&
+ gvar->varlevelsup == 0))
+ continue;
+ }
+ else if (!equal(node, tle->expr))
+ continue;
+
+ if (context->preferred_sortref != 0 &&
+ tle->ressortgroupref == context->preferred_sortref)
+ {
+ match_attnum = attnum;
+ match_ref = tle->ressortgroupref;
+ matched = true;
+ break;
+ }
+ if (!matched)
+ {
+ match_attnum = attnum;
+ match_ref = tle->ressortgroupref;
+ matched = true;
+ }
+ }
+
+ if (!matched)
+ return NULL;
+
+ return (Node *) buildGroupedVar(match_attnum, match_ref, context);
+}
+
+static Node *
+substitute_grouped_columns_mutator(Node *node,
+ substitute_grouped_columns_context *context)
+{
if (node == NULL)
return NULL;
+ /*
+ * Bind a repeated grouping expression to this targetlist entry's own
+ * sortref. Two outputs of one expression can be nulled by different
+ * grouping sets. The first matching group clause can be the other
+ * column, and then this column loses its grouping nulling bit.
+ */
+ if (IsA(node, TargetEntry) && context->sublevels_up == 0)
+ {
+ TargetEntry *tle = (TargetEntry *) node;
+ TargetEntry *newtle = flatCopyTargetEntry(tle);
+ Index save_sortref = context->preferred_sortref;
+
+ context->preferred_sortref = tle->ressortgroupref;
+ newtle->expr = (Expr *)
+ substitute_grouped_columns_mutator((Node *) tle->expr, context);
+ context->preferred_sortref = save_sortref;
+ return (Node *) newtle;
+ }
+
if (IsA(node, Aggref))
{
Aggref *agg = (Aggref *) node;
@@ -1434,7 +1523,7 @@ substitute_grouped_columns_mutator(Node *node,
if (context->have_non_var_grouping)
{
List *groupClauses;
- int attnum = 0;
+ Node *match;
/* Within a subquery, we need a mutated version of the groupClauses */
if (context->sublevels_up == 0)
@@ -1443,19 +1532,9 @@ substitute_grouped_columns_mutator(Node *node,
groupClauses = list_nth(context->groupClauseSubLevels,
context->sublevels_up - 1);
- foreach(gl, groupClauses)
- {
- TargetEntry *tle = (TargetEntry *) lfirst(gl);
-
- attnum++;
- if (equal(node, tle->expr))
- {
- /* acceptable, replace it with a GROUP Var */
- return (Node *) buildGroupedVar(attnum,
- tle->ressortgroupref,
- context);
- }
- }
+ match = match_grouped_column(node, groupClauses, context, false);
+ if (match)
+ return match;
}
/*
@@ -1488,25 +1567,12 @@ substitute_grouped_columns_mutator(Node *node,
*/
if (!context->have_non_var_grouping)
{
- int attnum = 0;
+ Node *match;
- foreach(gl, context->groupClauses)
- {
- TargetEntry *tle = (TargetEntry *) lfirst(gl);
- Var *gvar = (Var *) tle->expr;
-
- attnum++;
- if (IsA(gvar, Var) &&
- gvar->varno == var->varno &&
- gvar->varattno == var->varattno &&
- gvar->varlevelsup == 0)
- {
- /* acceptable, replace it with a GROUP Var */
- return (Node *) buildGroupedVar(attnum,
- tle->ressortgroupref,
- context);
- }
- }
+ match = match_grouped_column(node, context->groupClauses,
+ context, true);
+ if (match)
+ return match;
}
/*
@@ -1627,6 +1693,7 @@ finalize_grouping_exprs(Node *node, ParseState *pstate, Query *qry,
context.func_grouped_rels = NULL;
context.sublevels_up = 0;
context.in_agg_direct_args = false;
+ context.preferred_sortref = 0;
finalize_grouping_exprs_walker(node, &context);
}
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index 393d63cfb0c..4c4e9c887a2 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -2776,4 +2776,45 @@ order by 1, 2;
2 |
(6 rows)
+-- substitute_grouped_columns() bound a repeated grouping expression to the
+-- first group clause. GROUP BY ai2, ROLLUP(ai1) then left ai1 without a
+-- nulling bit, and ORDER BY treated the two outputs as one value.
+set enable_hashagg = false;
+select i as ai1, i as ai2 from generate_series(1,3) i
+ group by ai2, rollup(ai1) order by ai1, ai2;
+ ai1 | ai2
+-----+-----
+ 1 | 1
+ 2 | 2
+ 3 | 3
+ | 1
+ | 2
+ | 3
+(6 rows)
+
+select i as ai1, i as ai2 from generate_series(1,3) i
+ group by ai2, rollup(ai1) order by ai2, ai1;
+ ai1 | ai2
+-----+-----
+ 1 | 1
+ | 1
+ 2 | 2
+ | 2
+ 3 | 3
+ | 3
+(6 rows)
+
+set enable_hashagg = true;
+select i as ai1, i as ai2 from generate_series(1,3) i
+ group by ai2, rollup(ai1) order by ai2, ai1;
+ ai1 | ai2
+-----+-----
+ 1 | 1
+ | 1
+ 2 | 2
+ | 2
+ 3 | 3
+ | 3
+(6 rows)
+
-- end
diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql
index 91c05adf432..cb323a58a8c 100644
--- a/src/test/regress/sql/groupingsets.sql
+++ b/src/test/regress/sql/groupingsets.sql
@@ -812,4 +812,19 @@ from (values (1, 1), (2, 2)) as t (a, b)
group by rollup(a, ab)
order by 1, 2;
+-- substitute_grouped_columns() bound a repeated grouping expression to the
+-- first group clause. GROUP BY ai2, ROLLUP(ai1) then left ai1 without a
+-- nulling bit, and ORDER BY treated the two outputs as one value.
+set enable_hashagg = false;
+
+select i as ai1, i as ai2 from generate_series(1,3) i
+ group by ai2, rollup(ai1) order by ai1, ai2;
+select i as ai1, i as ai2 from generate_series(1,3) i
+ group by ai2, rollup(ai1) order by ai2, ai1;
+
+set enable_hashagg = true;
+
+select i as ai1, i as ai2 from generate_series(1,3) i
+ group by ai2, rollup(ai1) order by ai2, ai1;
+
-- end
--
2.53.0
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19837: ORDER BY/DISTINCT ON use wrong column with grouping set duplicates
@ 2026-11-14 10:50 Andrey Rachitskiy <pl0h0yp1@gmail.com>
parent: Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Andrey Rachitskiy @ 2026-11-14 10:50 UTC (permalink / raw)
To: 125atishprak@gmail.com; pgsql-bugs@lists.postgresql.org; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
сб, 14 нояб. 2026 г. в 02:17, Andrey Rachitskiy <pl0h0yp1@gmail.com>:
>
> 0001 fixes the ROLLUP query. The nulling bit from f5050f795ae was
> attached to ai2, which is in every grouping set, so ai1 never got it.
>
>
Attached v2 for 0001 — code unchanged, only the test was fixed.
Attachments:
v2-0001-Bind-repeated-grouping-expressions-to-their-own-sort.patch (text/x-patch, 8.4K) download
From f391539703686918c963a15507952bf2e26dfb57 Mon Sep 17 00:00:00 2001
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Date: Sat, 14 Nov 2026 15:33:56 +0500
Subject: [PATCH] Bind repeated grouping expressions to their own sortref.
substitute_grouped_columns() matched a repeated Var or expression to
the first group clause with the same value. When that clause is in
every grouping set, another output of the same expression lost the
nulling bit. ORDER BY then treated the two outputs as one value.
Each targetlist entry now binds to its own sortref. The first match is
used only when the entry is not itself a grouping column.
---
src/backend/parser/parse_agg.c | 135 +++++++++++++++------
src/test/regress/expected/groupingsets.out | 30 +++++
src/test/regress/sql/groupingsets.sql | 11 ++
3 files changed, 142 insertions(+), 34 deletions(-)
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index a5424c9d24c..c26992e2157 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -56,6 +56,7 @@ typedef struct
List **func_grouped_rels;
int sublevels_up;
bool in_agg_direct_args;
+ Index preferred_sortref; /* this tlist entry's sortref, or 0 */
} substitute_grouped_columns_context;
static int check_agg_arguments(ParseState *pstate,
@@ -72,6 +73,9 @@ static Node *substitute_grouped_columns(Node *node, ParseState *pstate, Query *q
List **func_grouped_rels);
static Node *substitute_grouped_columns_mutator(Node *node,
substitute_grouped_columns_context *context);
+static Node *match_grouped_column(Node *node, List *groupClauses,
+ substitute_grouped_columns_context *context,
+ bool var_match);
static void finalize_grouping_exprs(Node *node, ParseState *pstate, Query *qry,
List *groupClauses, bool hasJoinRTEs,
bool have_non_var_grouping);
@@ -1366,18 +1370,103 @@ substitute_grouped_columns(Node *node, ParseState *pstate, Query *qry,
context.func_grouped_rels = func_grouped_rels;
context.sublevels_up = 0;
context.in_agg_direct_args = false;
+ context.preferred_sortref = 0;
return substitute_grouped_columns_mutator(node, &context);
}
+/*
+ * If node matches a GROUP BY item, return a Var for that grouping column.
+ * Otherwise return NULL.
+ *
+ * Several items can match one expression. Use the item whose sortref is
+ * context->preferred_sortref. Otherwise use the first match.
+ *
+ * var_match is true when every group item is a Var. Two Vars then match on
+ * varno and varattno. Otherwise the expressions must be equal().
+ */
static Node *
-substitute_grouped_columns_mutator(Node *node,
- substitute_grouped_columns_context *context)
+match_grouped_column(Node *node, List *groupClauses,
+ substitute_grouped_columns_context *context,
+ bool var_match)
{
ListCell *gl;
+ int attnum = 0;
+ int match_attnum = 0;
+ Index match_ref = 0;
+ bool matched = false;
+
+ foreach(gl, groupClauses)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(gl);
+
+ attnum++;
+
+ if (var_match)
+ {
+ Var *var;
+ Var *gvar;
+
+ Assert(IsA(node, Var));
+ var = (Var *) node;
+ gvar = (Var *) tle->expr;
+ if (!(IsA(gvar, Var) &&
+ gvar->varno == var->varno &&
+ gvar->varattno == var->varattno &&
+ gvar->varlevelsup == 0))
+ continue;
+ }
+ else if (!equal(node, tle->expr))
+ continue;
+
+ if (context->preferred_sortref != 0 &&
+ tle->ressortgroupref == context->preferred_sortref)
+ {
+ match_attnum = attnum;
+ match_ref = tle->ressortgroupref;
+ matched = true;
+ break;
+ }
+ if (!matched)
+ {
+ match_attnum = attnum;
+ match_ref = tle->ressortgroupref;
+ matched = true;
+ }
+ }
+
+ if (!matched)
+ return NULL;
+
+ return (Node *) buildGroupedVar(match_attnum, match_ref, context);
+}
+
+static Node *
+substitute_grouped_columns_mutator(Node *node,
+ substitute_grouped_columns_context *context)
+{
if (node == NULL)
return NULL;
+ /*
+ * Bind a repeated grouping expression to this targetlist entry's own
+ * sortref. Two outputs of one expression can be nulled by different
+ * grouping sets. The first matching group clause can be the other
+ * column, and then this column loses its grouping nulling bit.
+ */
+ if (IsA(node, TargetEntry) && context->sublevels_up == 0)
+ {
+ TargetEntry *tle = (TargetEntry *) node;
+ TargetEntry *newtle = flatCopyTargetEntry(tle);
+ Index save_sortref = context->preferred_sortref;
+
+ context->preferred_sortref = tle->ressortgroupref;
+ newtle->expr = (Expr *)
+ substitute_grouped_columns_mutator((Node *) tle->expr, context);
+ context->preferred_sortref = save_sortref;
+ return (Node *) newtle;
+ }
+
if (IsA(node, Aggref))
{
Aggref *agg = (Aggref *) node;
@@ -1434,7 +1523,7 @@ substitute_grouped_columns_mutator(Node *node,
if (context->have_non_var_grouping)
{
List *groupClauses;
- int attnum = 0;
+ Node *match;
/* Within a subquery, we need a mutated version of the groupClauses */
if (context->sublevels_up == 0)
@@ -1443,19 +1532,9 @@ substitute_grouped_columns_mutator(Node *node,
groupClauses = list_nth(context->groupClauseSubLevels,
context->sublevels_up - 1);
- foreach(gl, groupClauses)
- {
- TargetEntry *tle = (TargetEntry *) lfirst(gl);
-
- attnum++;
- if (equal(node, tle->expr))
- {
- /* acceptable, replace it with a GROUP Var */
- return (Node *) buildGroupedVar(attnum,
- tle->ressortgroupref,
- context);
- }
- }
+ match = match_grouped_column(node, groupClauses, context, false);
+ if (match)
+ return match;
}
/*
@@ -1488,25 +1567,12 @@ substitute_grouped_columns_mutator(Node *node,
*/
if (!context->have_non_var_grouping)
{
- int attnum = 0;
+ Node *match;
- foreach(gl, context->groupClauses)
- {
- TargetEntry *tle = (TargetEntry *) lfirst(gl);
- Var *gvar = (Var *) tle->expr;
-
- attnum++;
- if (IsA(gvar, Var) &&
- gvar->varno == var->varno &&
- gvar->varattno == var->varattno &&
- gvar->varlevelsup == 0)
- {
- /* acceptable, replace it with a GROUP Var */
- return (Node *) buildGroupedVar(attnum,
- tle->ressortgroupref,
- context);
- }
- }
+ match = match_grouped_column(node, context->groupClauses,
+ context, true);
+ if (match)
+ return match;
}
/*
@@ -1627,6 +1693,7 @@ finalize_grouping_exprs(Node *node, ParseState *pstate, Query *qry,
context.func_grouped_rels = NULL;
context.sublevels_up = 0;
context.in_agg_direct_args = false;
+ context.preferred_sortref = 0;
finalize_grouping_exprs_walker(node, &context);
}
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index 393d63cfb0c..ac302b7e3d8 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -2776,4 +2776,34 @@ order by 1, 2;
2 |
(6 rows)
+-- check that a repeated expression in ROLLUP sorts by the output
+-- that only some grouping sets null
+set enable_hashagg = false;
+explain (costs off)
+select i as ai1, i as ai2 from generate_series(1,3) i
+ group by ai2, rollup(ai1) order by ai1, ai2;
+ QUERY PLAN
+------------------------------------------------------
+ Sort
+ Sort Key: i, i
+ -> GroupAggregate
+ Group Key: i, i
+ Group Key: i
+ -> Sort
+ Sort Key: i
+ -> Function Scan on generate_series i
+(8 rows)
+
+select i as ai1, i as ai2 from generate_series(1,3) i
+ group by ai2, rollup(ai1) order by ai1, ai2;
+ ai1 | ai2
+-----+-----
+ 1 | 1
+ 2 | 2
+ 3 | 3
+ | 1
+ | 2
+ | 3
+(6 rows)
+
-- end
diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql
index 91c05adf432..d9509b22986 100644
--- a/src/test/regress/sql/groupingsets.sql
+++ b/src/test/regress/sql/groupingsets.sql
@@ -812,4 +812,15 @@ from (values (1, 1), (2, 2)) as t (a, b)
group by rollup(a, ab)
order by 1, 2;
+-- check that a repeated expression in ROLLUP sorts by the output
+-- that only some grouping sets null
+set enable_hashagg = false;
+
+explain (costs off)
+select i as ai1, i as ai2 from generate_series(1,3) i
+ group by ai2, rollup(ai1) order by ai1, ai2;
+
+select i as ai1, i as ai2 from generate_series(1,3) i
+ group by ai2, rollup(ai1) order by ai1, ai2;
+
-- end
--
2.53.0
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-11-14 10:50 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-06 19:23 BUG #19837: ORDER BY/DISTINCT ON use wrong column with grouping set duplicates PG Bug reporting form <noreply@postgresql.org>
2026-11-13 21:17 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-11-14 10:50 ` Andrey Rachitskiy <pl0h0yp1@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