Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMgsh-000000029Tv-0rlt for pgsql-bugs@arkaria.postgresql.org; Fri, 30 Oct 2026 07:18:31 +0000 Received: from localhost ([127.0.0.1]:51180 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xMgsf-00000008qJl-1nMk for pgsql-bugs@arkaria.postgresql.org; Fri, 30 Oct 2026 07:18:29 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:55244) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMJPr-00000002tLn-003R for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:15:11 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMJPo-00000001U3d-2T9X for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:15:10 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=nmLogiSgHEtQ3lxNFzhp76GeY+VlKW2xBplVaKdB280=; b=AKucyePPUs4dvEb8er9mG/N0xy cgDkl3KgepvnUC0DOR77fa0aJDwPPuECTGByqQ0xgpCOOOFrDI/sB4O6V52sybZiH2iB943WAVDQi BnnVt5JBVdT+tN1xegPMAITncvU58QV6DORXf2MVfriwYBrFFFl4ZkqoBrks+qUbJhdu5az+pkfTf dVMEjMQFgDnn6XcdCIcoMwqo9cc2FzCn6L+iqyhkYQrRFojYots3zNt4oAF980mWk9NoVZQlDaW3j wc45q/mYtzi0IhjrRDYY2676aHX3MQorIHfZGoiHfYetOI8OP92/PoqO1RlvvD3lXhKRJKYZBXnH1 bNA2gXVw==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMJPm-00000004HC5-0Kog for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:15:07 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xMJPj-00000005acm-2le8 for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:15:04 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19806: Wrong results: HAVING pushed into WHERE for GROUP BY on composite column (nondeterministic field) To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 303677365@qq.com Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org Date: Thu, 29 Oct 2026 06:14:36 +0000 Message-ID: <19806-c7976c3c16fe464b@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 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 =3D 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 =3D icu, locale =3D 'und-u-ks-level2', deterministic =3D 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 =3D 'foo' COLLATE "C= "; -- not pushable (contains an aggregate) -> correct SELECT r, count(*) FROM t_rec2 GROUP BY r HAVING (r).f =3D 'foo' COLLATE "C" OR count(*) =3D 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 =3D '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) =3D 2 over a ci grouping (returns count =3D 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.