BUG #19649: Qual pushdown into GROUP BY subqueries ignores non-equivalence-preserving references to grouping col

чт, 3 сент. 2026 г. в 18:02, PG Bug reporting form <noreply@postgresql.org>:

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)

```

Hi!

Thanks for the report!

Commit 44fb59fc605 added grouping conflict checks.
That check focused on direct grouping-Var operands.
A wrapper inside a comparison operand, such as CoerceViaIO or
jsonb text extraction, was still treated as pushdown-safe for
deterministic collations.

Proposal fix

In grouping_check_operand(), keep existing direct-operand compatibility
checks.
For comparison operands that are not direct Vars, recurse into the
operand tree and apply the same opfamily/collation compatibility checks
to grouping Vars found inside wrappers.
The implementation keeps direct-Var checks in one helper
(grouping_var_has_comparison_conflict) and reuses it from the wrapper
walker (grouping_operand_has_comparison_conflict_walker).

This blocks wrapper-based finer equivalence at pushdown boundaries while
preserving existing behavior for truly direct operands.

--
Regards,
Rachitskiy Andrey

On 03/09/2026 23:17, Andrey Rachitskiy wrote:

This blocks wrapper-based finer equivalence at pushdown boundaries while
preserving existing behavior for truly direct operands.

I don't like this fix. It causes regressions where we haven't had it before.
Let's see:

CREATE TABLE r(i int, s text, ts timestamptz);
EXPLAIN (COSTS OFF)
SELECT * FROM (SELECT i, count(*) c FROM r GROUP BY i) s WHERE i::text = '5';

Before:

GroupAggregate
Group Key: r.i
-> Sort
Sort Key: r.i
-> Seq Scan on r
Filter: ((i)::text = '5'::text)

With your fix:

HashAggregate
Group Key: r.i
Filter: ((r.i)::text = '5'::text)
-> Seq Scan on r

I think, filter should be pushed down to the scan.

--
regards, Andrei Lepikhov,
pgEdge

пт, 4 сент. 2026 г. в 20:12, Andrei Lepikhov <lepihov@gmail.com>:

On 03/09/2026 23:17, Andrey Rachitskiy wrote:

This blocks wrapper-based finer equivalence at pushdown boundaries while
preserving existing behavior for truly direct operands.

I don't like this fix. It causes regressions where we haven't had it
before.
Let's see:

CREATE TABLE r(i int, s text, ts timestamptz);
EXPLAIN (COSTS OFF)
SELECT * FROM (SELECT i, count(*) c FROM r GROUP BY i) s WHERE i::text =
'5';

Before:

GroupAggregate
Group Key: r.i
-> Sort
Sort Key: r.i
-> Seq Scan on r
Filter: ((i)::text = '5'::text)

With your fix:

HashAggregate
Group Key: r.i
Filter: ((r.i)::text = '5'::text)
-> Seq Scan on r

I think, filter should be pushed down to the scan.

--
regards, Andrei Lepikhov,
pgEdge

Dear Andrei,

Thanks for the review.

v2 with a correction in the attachment.
I also removed the duplicate logic.

--
Regards,
Rachitskiy Andrey

пт, 4 сент. 2026 г. в 21:10, Andrey Rachitskiy <pl0h0yp1@gmail.com>:

пт, 4 сент. 2026 г. в 20:12, Andrei Lepikhov <lepihov@gmail.com>:

On 03/09/2026 23:17, Andrey Rachitskiy wrote:

This blocks wrapper-based finer equivalence at pushdown boundaries while
preserving existing behavior for truly direct operands.

I don't like this fix. It causes regressions where we haven't had it
before.
Let's see:

CREATE TABLE r(i int, s text, ts timestamptz);
EXPLAIN (COSTS OFF)
SELECT * FROM (SELECT i, count(*) c FROM r GROUP BY i) s WHERE i::text =
'5';

Before:

GroupAggregate
Group Key: r.i
-> Sort
Sort Key: r.i
-> Seq Scan on r
Filter: ((i)::text = '5'::text)

With your fix:

HashAggregate
Group Key: r.i
Filter: ((r.i)::text = '5'::text)
-> Seq Scan on r

I think, filter should be pushed down to the scan.

--
regards, Andrei Lepikhov,
pgEdge

Dear Andrei,

Thanks for the review.

v2 with a correction in the attachment.
I also removed the duplicate logic.

In v2, I did not account for the GROUP BY case.
44fb59fc605 checks it later in
find_having_conflicts, after the qual is pushed into HAVING.
ReplaceVarsFromTargetList copies a GROUP Var from the tlist, so the
walker can keep a wrapped jsonb qual on the Agg node.
Without the wrapper check the qual moves to WHERE. The reporter's
j::text filters then turn count(*) from 2 into 1.
This update also looks at groupClause in qual_is_pushdown_safe, so the
qual is not pushed. The filter stays on Subquery Scan, as for
DISTINCT.

v3 in attachment.

P.S. I'm still learning plans and could be mistaken, so please don't judge
too harshly.

сб, 5 сент. 2026 г. в 12:52, Andrey Rachitskiy <pl0h0yp1@gmail.com>:

v3 in attachment.

P.S. I'm still learning plans and could be mistaken, so please don't judge
too harshly.

I looked more carefully at how the pushed qual is placed, and I had
misread the plan when I wrote v3. For a GROUP BY subquery the wrapped
qual is not pushed below the grouping. subquery_push_qual attaches it to
HAVING, and find_having_conflicts then keeps it there through the same
walker that the clauses.c fix changes. So v2 already produced correct
results for GROUP BY, by construction rather than by accident.
```
if (subquery->hasAggs || subquery->groupClause ||
subquery->groupingSets || subquery->havingQual)
subquery->havingQual = make_and_qual(subquery->havingQual, qual);
else
subquery->jointree->quals = make_and_qual(...);
```
So a wrapped jsonb qual such as j::text = '1' stays in HAVING and is not
lowered to WHERE. That is why the GroupAggregate and HashAggregate cases
already gave correct results under v2, with the filter on the Agg node.
DISTINCT, window PARTITION BY and set operations are different. They have
no HAVING, so subquery_push_qual routes the pushed qual to WHERE. That is
why point 6 in qual_is_pushdown_safe lists those three and omits GROUP BY.
The clauses.c fix covers both boundaries because both share the walker.
So the groupClause check I added to qual_is_pushdown_safe in v3 is
redundant for correctness. It only changes the plan shape: the filter
ends up on the Subquery Scan instead of on the Agg node. Both are
correct.

I attach v4, it is v2 with fixed comment.
Sorry for the noise, I'll be more attentive and take my time.

On 05/09/2026 12:45, Andrey Rachitskiy wrote:

I attach v4, it is v2 with fixed comment.
Sorry for the noise, I'll be more attentive and take my time. 

Thanks for the quick turnaround.

My concern is with the approach, not the code itself. The key change in v4 is a
single line:

if (getBaseType(var->vartype) != JSONBOID)
return false;

This only addresses the specific type mentioned in the report. However, the
reporter could have demonstrated the same bug using numeric, without involving
jsonb at all. The same goes for float8. In core, the default btree opclasses
that make no image-equality promise are numeric, float8, interval, jsonb, record
and tsvector, among others. Group by any of those and wrap the column in any
expression, and the qual moves. This means we will see this issue reported
again, just with a different type mentioned.
What's more, I think your solution not even full. Just check something like the
following:

SELECT j, count(*) FROM t GROUP BY j HAVING starts_with(j::text, '1.');

So, how can we address the broader issue rather than just this specific case?

The property we need is already in the catalogue. Peter and Anastasia added
equalimage support functions in 612a1ab7672 for btree deduplication, and the
documented contract is exactly what we need. If that holds, no wrapping
expression can distinguish values that the grouping merged, whatever the wrapper is.

Even this approach is not free from issues, quite narrow ones though. If I
understand correctly, image equality is not quite the same as bitwise equality
for varlena, since TOAST compression is not applied consistently, so functions
like pg_column_size() can still be inconsistent. The attachment is a LLM
generated patch that demonstrates the idea, maybe in too much detail. One way or
another, we end up with worse query plans, sometimes for nothing.

Postgres is not the only one facing this issue. Let's try to find ideas in
others' experience.

The mainstream solution is to remove the coarse equality from the type system.
DuckDB canonicalises -0.0 and gives DECIMAL a fixed per-column scale. ClickHouse
allows COLLATE only in ORDER BY and fixes DECIMAL scale as well. Equality
becomes image equality, and the optimiser needs no guard at all.

The second is to declare the result unspecified [1]https://sqlite.org/forum/info/6dc048f81303cb97. SQLite reproduces our bug
through type affinity, and their answer is that the affinity of such a column is
indeterminate and the group representative is arbitrary, so any result is legal.
Some discussions in the Internet give me an idea that SQL Server restrict clause
pushdown in such cases.

I personally prefer the second approach, possibly with an image equality check.
GROUP BY already hands back an arbitrary member of the group. Postgres does not
promise which one, and any expression that can distinguish members of the class
is therefore reading something we never guaranteed.

[1]: https://sqlite.org/forum/info/6dc048f81303cb97

--
regards, Andrei Lepikhov,
pgEdge

сб, 5 сент. 2026 г. в 21:23, Andrei Lepikhov <lepihov@gmail.com>:

My concern is with the approach, not the code itself. The key change in v4
is a
single line:

if (getBaseType(var->vartype) != JSONBOID)
return false;

This only addresses the specific type mentioned in the report. However, the
reporter could have demonstrated the same bug using numeric, without
involving
jsonb at all. The same goes for float8. In core, the default btree
opclasses
that make no image-equality promise are numeric, float8, interval, jsonb,
record
and tsvector, among others.

I agree, I deliberately didn't include them. I didn't like the code I ended
up with when I took them into account. And I couldn't figure out a better
way to do it, so I just stuck with jsonb.

The property we need is already in the catalogue. Peter and Anastasia added
equalimage support functions in 612a1ab7672 for btree deduplication, and
the
documented contract is exactly what we need. If that holds, no wrapping
expression can distinguish values that the grouping merged, whatever the
wrapper is.

Regarding BTEQUALIMAGE_PROC, I agree as well — I also considered it, but
rejected it because I thought it would degrade the execution plan, although
in our case it's actually a perfect fit.

The second is to declare the result unspecified [1]. SQLite reproduces our
bug
through type affinity, and their answer is that the affinity of such a
column is
indeterminate and the group representative is arbitrary, so any result is
legal.
Some discussions in the Internet give me an idea that SQL Server restrict
clause
pushdown in such cases.

I personally prefer the second approach, possibly with an image equality
check.
GROUP BY already hands back an arbitrary member of the group. Postgres
does not
promise which one, and any expression that can distinguish members of the
class
is therefore reading something we never guaranteed.

[1] https://sqlite.org/forum/info/6dc048f81303cb97

I'll take a look at the code from [0].

Perhaps we will be able to find some option in the discussion process and I
will try to implement it.

--
Regards,
Rachitskiy Andrey

сб, 5 сент. 2026 г. в 21:48, Andrey Rachitskiy <pl0h0yp1@gmail.com>:

I will try to implement it.

Hi, Tender, Andrei!

Please review v5. It uses BTEQUALIMAGE_PROC and updates btree.sgml /
select.sgml.A small refactoring is also included.