pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19806: Wrong results: HAVING pushed into WHERE for GROUP BY on composite column (nondeterministic field)
2+ messages / 2 participants
[nested] [flat]

* BUG #19806: Wrong results: HAVING pushed into WHERE for GROUP BY on composite column (nondeterministic field)
@ 2026-10-29 06:14  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2026-10-29 06:14 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: 303677365@qq.com

The following bug has been logged on the website:

Bug reference:      19806
Logged by:          chunling qin
Email address:      303677365@qq.com
PostgreSQL version: 18.6
Operating system:   Linux x86_64
Description:        

When the grouped expression is a composite (record) column whose field
carries a nondeterministic collation, a HAVING clause is pushed into WHERE
and evaluated below the aggregation. Grouping equality is nondeterministic
in that case, so the clause can remove members of a group before aggregation
and change count(*).

The pushdown safety check only inspects the top-level varcollid of the
grouping Var. A composite column has varcollid = InvalidOid (record is not
sortable), so the check is skipped - yet record_eq compares fields using
each field's declared collation, which may be nondeterministic.

Test case (self-contained, requires ICU):

CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2',
deterministic = false);
CREATE TYPE t_pair AS (f text COLLATE ci);
CREATE TABLE t_rec2 (r t_pair, n int);
INSERT INTO t_rec2 VALUES (row('foo')::t_pair, 1), (row('FOO')::t_pair, 2);
ANALYZE t_rec2;

-- reference: the group {foo, FOO} has size 2
SELECT r, count(*) FROM t_rec2 GROUP BY r;

-- pushed down -> wrong
SELECT r, count(*) FROM t_rec2 GROUP BY r HAVING (r).f = 'foo' COLLATE "C";

-- not pushable (contains an aggregate) -> correct
SELECT r, count(*) FROM t_rec2 GROUP BY r HAVING (r).f = 'foo' COLLATE "C"
OR count(*) = 99;

Actual output (18.6):

query 1 (reference):   (foo) | 2
query 2 (pushed down): (foo) | 1     <- wrong
query 3 (not pushable):(foo) | 2

Expected: query 2 returns 2, or no row at all. The group is {foo, FOO} under
the field collation, so its size is fixed at 2; evaluating the HAVING clause
against the group's representative row can only yield (representative, 2) or
nothing. 1 is not reachable.

Plan of query 2 on 18.6 (filter below the aggregate):

 HashAggregate
   Group Key: r
   ->  Seq Scan on t_rec2
         Filter: ((r).f = 'foo'::text COLLATE "C")

Source: in 18.4 this is having_collation_conflict_walker() in planner.c
(~line 1517): OidIsValid(var->varcollid) &&
!get_collation_isdeterministic(var->varcollid). On current REL_18_STABLE
that walker has been replaced by the shared
expression_has_grouping_conflict() (clauses.c), introduced by fe5d6295
(2026-07-06, "Fix qual pushdown past grouping with mismatched equivalence",
backpatched to 18), but the new walker keeps the same precondition
(clauses.c:5606-5608):

if (OidIsValid(ctx->get_eqop(var, ctx->cb_context)) &&
    OidIsValid(var->varcollid) &&
    !get_collation_isdeterministic(var->varcollid))

so a composite grouping column is still not recognised as having
nondeterministic equality.

Notes:

- fe5d6295 does fix the same class of problem for ordinary columns. I
confirmed on 18.6 that the related cases it targets now behave correctly:
HAVING length(x) = 2 over a ci grouping (returns count = 3, matching the
non-pushable form) and qual pushdown past window PARTITION BY / DISTINCT ON
(window count 2 instead of 1; DISTINCT ON 0 rows instead of one wrong row).
Composite columns appear to be the remaining gap.








^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: BUG #19806: Wrong results: HAVING pushed into WHERE for GROUP BY on composite column (nondeterministic field)
@ 2026-11-08 03:21  shihao zhong <zhong950419@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 0 replies; 2+ messages in thread

From: shihao zhong @ 2026-11-08 03:21 UTC (permalink / raw)
  To: 303677365@qq.com; pgsql-bugs@lists.postgresql.org

Hi,

This is the same as your 19797[1], we should discuss in that thread.

[1] https://postgr.es/m/19797-7054aa017fa9d791@postgresql.org

Thanks,
Shihao

^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2026-11-08 03:21 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-29 06:14 BUG #19806: Wrong results: HAVING pushed into WHERE for GROUP BY on composite column (nondeterministic field) PG Bug reporting form <noreply@postgresql.org>
2026-11-08 03:21 ` shihao zhong <zhong950419@gmail.com>

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