agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19649: Qual pushdown into GROUP BY subqueries ignores non-equivalence-preserving references to grouping col
Date: Thu, 03 Sep 2026 06:41:49 +0000
Message-ID: <19649-2cabf1440793cc71@postgresql.org> (raw)
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:
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 —
but whose text representations differ — 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 —
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 = 1.0, so the table has exactly ONE jsonb group with count = 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 =
'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 =
'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 =
'1'::jsonb;
-- 1 | 2 (control: same-eqop comparison is
correct)
```
hunt@(null)=# CREATE TABLE t(id int primary key, j jsonb);
INSERT INTO t VALUES (1,'1'),(2,'1.0');
-- jsonb 1 = 1.0, so the table has exactly ONE jsonb group with count = 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 =
'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 =
'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 =
'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)=# select version();
version
---------------------------------------------------------------------------------------------
------------------------------------------
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)
```
view thread (9+ messages) latest in thread
Message-ID: <19649-2cabf1440793cc71@postgresql.org>
Permalink: ../19649-2cabf1440793cc71@postgresql.org/
Also on: postgresql.org/message-id/19649-2cabf1440793cc71@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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, 303677365@qq.com
Subject: Re: BUG #19649: Qual pushdown into GROUP BY subqueries ignores non-equivalence-preserving references to grouping col
In-Reply-To: <19649-2cabf1440793cc71@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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox