pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 125atishprak@gmail.com
Subject: BUG #19837: ORDER BY/DISTINCT ON use wrong column with grouping set duplicates
Date: Fri, 06 Nov 2026 19:23:11 +0000
Message-ID: <19837-92a0d7bbd05b30d8@postgresql.org> (raw)

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)








view thread (3+ messages)  latest in thread

Message-ID: <19837-92a0d7bbd05b30d8@postgresql.org>
Permalink:  ../19837-92a0d7bbd05b30d8@postgresql.org/
Also on:    postgresql.org/message-id/19837-92a0d7bbd05b30d8@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, 125atishprak@gmail.com
  Subject: Re: BUG #19837: ORDER BY/DISTINCT ON use wrong column with grouping set duplicates
  In-Reply-To: <19837-92a0d7bbd05b30d8@postgresql.org>
  References: <19837-92a0d7bbd05b30d8@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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