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 1xTbBk-00000000QeT-2N71 for pgsql-bugs@arkaria.postgresql.org; Wed, 18 Nov 2026 08:38:45 +0000 Received: from localhost ([127.0.0.1]:40002 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xTbBj-0000000ANGk-48fO for pgsql-bugs@arkaria.postgresql.org; Wed, 18 Nov 2026 08:38:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:35796) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xTUrF-000000088T7-2Lq1 for pgsql-bugs@lists.postgresql.org; Wed, 18 Nov 2026 01:53:09 +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 1xTUrC-00000000EiP-3hy6 for pgsql-bugs@lists.postgresql.org; Wed, 18 Nov 2026 01:53:08 +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=IyGvqEBLXFp7zh82huFJwYwlDUsDRGcZV/XBaUKJS5E=; b=BLwFoiwRVMjlOMSWFzH1WGN0se 9rlqXTh7QRKfT439HWzRdm4JoioJFBp6/ylOP4FeZOX6SbvLY/33xHt8+jltHCZU6HFbHvubPABvx Yv1vQBWr6sQ9VB/VzHuOqpo6ihlMO7D+axI7CkGQnVoNF66zUieqSSUC9LQ1n49E7/+6XTYs72qDK q6QUIb4/xmpakZ+9KGlttCMXPKWn1ttp8eFi8GdLfMbS9pJE099Cwts/maccl2+0Y4fPmIeqMhcg1 AhgFYZ4JRWmU9ATt5fBJhZuGOS/yOUe24bnjeGOA6fS+1g2oftnh8aqPsXBRQwyETtmDpIFBgY6Tn jCP9cV4w==; 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 1xTUrA-00000000gdy-31fy for pgsql-bugs@lists.postgresql.org; Wed, 18 Nov 2026 01:53:06 +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 1xTUr9-00000001gqq-0Fyu for pgsql-bugs@lists.postgresql.org; Wed, 18 Nov 2026 01:53:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19915: Qual over function-wrapped grouping column pushed past DISTINCT ON returns wrong rows 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: Wed, 18 Nov 2026 01:53:01 +0000 Message-ID: <19915-7166b5b1b377ad97@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: 19915 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: Unsupported/Unknown Operating system: Linux x86_64; master build 81b2179b492 (20devel) Description: =20 **PostgreSQL version:** 20devel master build (commit 81b2179b492, built 2026-10-27). The grouping/distinct pushdown machinery involved has been evolving through 2026 (see Related); the fix committed on 2026-07-06 is present in this build, and the problem below still occurs, so current released versions are likely affected as well; I have not verified on a release build. **Operating system:** Linux x86_64 **Description:** An outer qual that wraps a grouping column in a function is pushed down past a DISTINCT ON / grouping layer, when the corresponding qual over the bare column is (correctly) not pushed. The pushed-down qual filters rows before the grouping step, changing which rows the grouping sees, and the query returns rows that the subquery, on its own, never produces. **Reproduction (single session):** ```sql DROP TABLE IF EXISTS m, m_sub CASCADE; CREATE TABLE m (id int, a numeric); INSERT INTO m VALUES (1, 1.00), (2, 1.0), (3, 2); ANALYZE m; -- Ground truth: the DISTINCT ON subquery output, materialized. CREATE TABLE m_sub AS SELECT DISTINCT ON (a) id, a FROM m ORDER BY a, id; SELECT * FROM m_sub ORDER BY id; ``` ```text id | a ----+------ 1 | 1.00 3 | 2 (2 rows) ``` Rows (1, 1.00) and (2, 1.0) are equal under numeric equality (the eqop used for grouping/DISTINCT), so DISTINCT ON keeps the first per ORDER BY, id=3D1. Filtering the materialized output gives the correct answer =C3=A2=C2=80=C2= =94 no rows (scale(1.00)=3D2, scale(2)=3D0): ```sql SELECT * FROM m_sub WHERE scale(a) =3D 1; -- 0 rows ``` But in the single query, the qual is pushed below the Unique node (EXPLAIN shows the filter on the scan), the grouping only sees row (2, 1.0), and the query returns a row the subquery never produces: ```sql EXPLAIN (COSTS OFF) SELECT * FROM (SELECT DISTINCT ON (a) id, a FROM m ORDER BY a, id) s WHERE scale(s.a) =3D 1; ``` ```text QUERY PLAN ------------------------------ Unique -> Sort Sort Key: m.a, m.id -> Seq Scan on m Filter: (scale(a) =3D 1) (5 rows) ``` ```sql SELECT * FROM (SELECT DISTINCT ON (a) id, a FROM m ORDER BY a, id) s WHERE scale(s.a) =3D 1 ORDER BY id; -- returns (2, 1.0) ``` Proof that the returned row is not a row of the subquery output: ```sql SELECT s.* FROM (SELECT DISTINCT ON (a) id, a FROM m ORDER BY a, id) s WHERE scale(s.a) =3D 1 AND NOT EXISTS (SELECT 1 FROM m_sub o WHERE o.id =3D s.id AND o.a =3D s= .a); ``` ```text id | a ----+----- 2 | 1.0 (1 row) ``` Control: an OFFSET 0 barrier (which blocks pushdown) returns 0 rows =C3=A2= =C2=80=C2=94 so the query, written over the subquery output, is expected to return nothing. ```sql SELECT * FROM (SELECT DISTINCT ON (a) id, a FROM m ORDER BY a, id OFFSET 0) s WHERE scale(s.a) =3D 1; -- 0 rows ``` The mechanism: scale(1.0) =3D 1 but scale(1.00) =3D 2, while 1.0 =3D 1.00 u= nder numeric equality. Filtering before grouping keeps the row the grouping would discard (2, 1.0) instead of the row it would keep (1, 1.00). A qual over the bare grouping column (a =3D 1) is not pushed (the planner knows the grouping column's value is not stable across equal-but-differently-scaled members); wrapping the column in an IMMUTABLE function defeats that check, because the function's output is assumed derivable from the grouping key while it actually depends on the presentation (scale) of the value, not its semantic value. **Root cause (analysis):** The pushdown check for quals over grouped outputs (expression_has_grouping_conflict / the equivalence-class based checks in clauses.c) does not treat a function applied to a grouping column as conflicting: it only rejects quals whose operands directly reference the grouping column, relying on the function being IMMUTABLE to conclude its value is determined by the group. For a collatable/deterministic type like numeric, equal group members can have different function results (different typmod/scale), so the pushed qual changes which member survives the grouping.