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 1xLHMx-00000001Pr4-0fcC for pgsql-bugs@arkaria.postgresql.org; Mon, 26 Oct 2026 09:51:56 +0000 Received: from localhost ([127.0.0.1]:55844 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xLHMw-00000003eFu-2b4G for pgsql-bugs@arkaria.postgresql.org; Mon, 26 Oct 2026 09:51:54 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:60898) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xLH1y-00000003a1q-1wFf for pgsql-bugs@lists.postgresql.org; Mon, 26 Oct 2026 09:30:14 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xLH1s-000000012Bw-3kye for pgsql-bugs@lists.postgresql.org; Mon, 26 Oct 2026 09:30:14 +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=0L2CCDLeLce3CgozIQYQu7UI71latcWwYL6xmPOzcuM=; b=b9mdby1fx+423vUIfHC3gIbCA2 WyK7kAO/BTHINH+qtU+b/CqckVAaYWBw+U0ZUWvuKUS+8IqLvmmG0ovaeD7oiSDMVqnvG5gl+JHRo DVZf+NYR+YZMqybnxarr68NfbudDiPpSdeJJ38VUNarAuHSJclIAncENgJOnO3zei+s5vBw7cH+7i AAopjyvY5vjWvhYniCXUrVJWzOWLXXsnC78Zaos59y3HwY89ejNd1y63FaaEGxs8hHiWroM0iyNNQ rFVUdsJQlWuZ8ezBdViWGWimrVFP3TaMGcg8YwrsE57LLjGDw4/IJ6NAXvd64O3EiMiwCOW6wzp9n Ah63VNqg==; 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 1xLH1q-00000002uYu-0dxt for pgsql-bugs@lists.postgresql.org; Mon, 26 Oct 2026 09:30: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 1xLH1n-000000019j8-2kNa for pgsql-bugs@lists.postgresql.org; Mon, 26 Oct 2026 09:30:04 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19797: Wrong results: HAVING pushdown past GROUP BY on composite column with nondeterministic collation 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: Mon, 26 Oct 2026 09:30:01 +0000 Message-ID: <19797-7054aa017fa9d791@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: 19797 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: 18.6 Operating system: Linux x86_64 (TencentOS 4.4), self-compiled, --wit Description: =20 Hello, 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): 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): r | count -------+------- (foo) | 2 <- reference r | count -------+------- (foo) | 1 <- pushed down r | count -------+------- (foo) | 2 <- not pushable Expected: the second query 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 the second query 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 ove= r 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. - I have not checked branches 14-17, nor master. Thanks, chunling qin