Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19649: Qual pushdown into GROUP BY subqueries ignores
 non-equivalence-preserving references to grouping col
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: 303677365@qq.com
Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org
Date: Thu, 03 Sep 2026 06:41:49 +0000
Message-ID: <19649-2cabf1440793cc71@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19649-2cabf1440793cc71%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19649
Logged by:          chunling qin
Email address:      303677365@qq.com
PostgreSQL version: 18.6
Operating system:   86_64
Description:       =20

When an outer WHERE/HAVING clause references a grouping column of a GROUP BY
(or DISTINCT) subquery through a type coercion (::text, CoerceViaIO) or a
function/operator wrapper (j->>0), the qual is pushed down below the
grouping node even though the reference applies a different equivalence
relation than the grouping does. Values that the grouping considers equal =
=E2=80=94
but whose text representations differ =E2=80=94 get separated by the pushed=
-down
qual, splitting one group into two halves. This produces silently wrong
results: count(*) values change, a group can emit different group keys
depending on the WHERE, and rows are lost.

The simplest proof that something is wrong: the same subquery group answers
with two different group keys under two different outer WHERE clauses =E2=
=80=94
impossible under SQL semantics, since WHERE may only select subquery output
rows, never alter them.

CREATE TABLE t(id int primary key, j jsonb);
INSERT INTO t VALUES (1,'1'),(2,'1.0');
-- jsonb 1 =3D 1.0, so the table has exactly ONE jsonb group with count =3D=
 2

SELECT j, c FROM (SELECT j, count(*) c FROM t GROUP BY j) s;
-- 1 | 2                              (baseline: one group)

SELECT j, c FROM (SELECT j, count(*) c FROM t GROUP BY j) s WHERE j::text =
=3D
'1';
-- 1 | 1                              (WRONG: count changed by WHERE)

SELECT j, c FROM (SELECT j, count(*) c FROM t GROUP BY j) s WHERE j::text =
=3D
'1.0';
-- 1.0 | 1                            (WRONG: the same group, different key)

SELECT j, c FROM (SELECT j, count(*) c FROM t GROUP BY j) s WHERE j =3D
'1'::jsonb;
-- 1 | 2                              (control: same-eqop comparison is
correct)


```
hunt@(null)=3D# CREATE TABLE t(id int primary key, j jsonb);
INSERT INTO t VALUES (1,'1'),(2,'1.0');
-- jsonb 1 =3D 1.0, so the table has exactly ONE jsonb group with count =3D=
 2

SELECT j, c FROM (SELECT j, count(*) c FROM t GROUP BY j) s;
-- 1 | 2                              (baseline: one group)

SELECT j, c FROM (SELECT j, count(*) c FROM t GROUP BY j) s WHERE j::text =
=3D
'1';
-- 1 | 1                              (WRONG: count changed by WHERE)

SELECT j, c FROM (SELECT j, count(*) c FROM t GROUP BY j) s WHERE j::text =
=3D
'1.0';
-- 1.0 | 1                            (WRONG: the same group, different key)

SELECT j, c FROM (SELECT j, count(*) c FROM t GROUP BY j) s WHERE j =3D
'1'::jsonb;
-- 1 | 2                              (control: same-eqop comparison is
correct)
CREATE TABLE
INSERT 0 2
 j | c
---+---
 1 | 2
(1 row)

 j | c
---+---
 1 | 1
(1 row)

  j  | c
-----+---
 1.0 | 1
(1 row)

 j | c
---+---
 1 | 2
(1 row)

hunt@(null)=3D# select version();
                                                                version
                                         =20
---------------------------------------------------------------------------=
------------------
------------------------------------------
 PostgreSQL 20devel on x86_64-pc-linux-gnu, compiled by gcc (Tencent
Compiler 12.3.1.8) 12.3.
1 20230912 (TencentOS 12.3.1.8-6), 64-bit
(1 row)

```