Re: 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(at)postgresql(dot)org>:

> The following bug has been logged on the website:
>
> Bug reference: 19649
> Logged by: chunling qin
> Email address: 303677365(at)qq(dot)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