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 1x937m-00000001iIV-3Xzu for pgsql-bugs@arkaria.postgresql.org; Tue, 22 Sep 2026 16:13:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1x937m-0000000HLxP-0foh for pgsql-bugs@arkaria.postgresql.org; Tue, 22 Sep 2026 16:13:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1x92wl-0000000HC7D-2g0t for pgsql-bugs@lists.postgresql.org; Tue, 22 Sep 2026 16:02:19 +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 1x92wc-00000000j4y-2qVn for pgsql-bugs@lists.postgresql.org; Tue, 22 Sep 2026 16:02:19 +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=8aQcfx6cdqnrGilWlt9eLXxMnDeRLQiTHPxgEF2FxLk=; b=SIq2G/ZC4pBH3JGyf63g2bCZjk UyS3nnculcfiEW6dHarOPgNVCDNRY+RfnwXDOweGDDBsxMyMVXZQRyJv+p4GW7VHjCOizbWsZzX+H crGwtt+PFZJ+gLjZgUjuSCkiEf04xrap++5SwR9NMLjZI2U0ARIKiHj/EGxQgFzfEKhSp3q1R9yEz VAb3CmVeJTWZAkunZbWsFUEECcNBxClqJwnQNFmYk9cKh281ZWbM6W8LqS8DH1qj8aWjo6V3YGbXP dL53rZzN2L0iKFxRT9wwu3ZIEcWguCfcQej7RXYky/HquVqbE7lGPlvoWldoECs5se/kM1d0TIvAw /tLPlqGA==; 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.96) (envelope-from ) id 1x92wX-0027w7-17 for pgsql-bugs@lists.postgresql.org; Tue, 22 Sep 2026 16:02: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 1x92wU-00000005x51-3jla for pgsql-bugs@lists.postgresql.org; Tue, 22 Sep 2026 16:02:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19713: WindowAgg qual pushdown gives wrong partition count when scale(numeric) distinguishes equal values To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: imchifan@163.com Reply-To: imchifan@163.com, pgsql-bugs@lists.postgresql.org Date: Tue, 22 Sep 2026 16:01:34 +0000 Message-ID: <19713-635b8656d07464df@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: 19713 Logged by: Qifan Liu Email address: imchifan@163.com PostgreSQL version: 18.6 Operating system: Linux/amd64 Description: =20 An outer scale(numeric) predicate is pushed below WindowAgg even though numeric values 1.0 and 1.00 are equal under PARTITION BY semantics while scale() distinguishes them. The pushed predicate removes one partition member and produces an incorrect window count of 1 instead of 2. Steps to reproduce ------------------ Run the following input with psql: \set ON_ERROR_STOP on BEGIN; CREATE TEMP TABLE bugseer_postgres_00010_window_scale (x numeric); INSERT INTO bugseer_postgres_00010_window_scale VALUES (1.0), (1.00); CREATE FUNCTION bugseer_postgres_00010_identity_numeric_window(numeric) RETURNS numeric LANGUAGE plpgsql VOLATILE STRICT AS 'BEGIN RETURN $1; END'; EXPLAIN (COSTS OFF) SELECT n FROM ( SELECT x, count(*) OVER (PARTITION BY x) AS n FROM bugseer_postgres_00010_window_scale ) s WHERE scale(x) =3D 1; EXPLAIN (COSTS OFF) SELECT n FROM ( SELECT x, count(*) OVER (PARTITION BY x) AS n FROM bugseer_postgres_00010_window_scale ) s WHERE scale(bugseer_postgres_00010_identity_numeric_window(x)) =3D 1; WITH optimized AS ( SELECT n FROM ( SELECT x, count(*) OVER (PARTITION BY x) AS n FROM bugseer_postgres_00010_window_scale ) s WHERE scale(x) =3D 1 ), baseline AS ( SELECT n FROM ( SELECT x, count(*) OVER (PARTITION BY x) AS n FROM bugseer_postgres_00010_window_scale ) s WHERE scale(bugseer_postgres_00010_identity_numeric_window(x)) =3D 1 ) SELECT (SELECT min(n) FROM optimized) AS optimized_n, (SELECT min(n) FROM baseline) AS baseline_n, (SELECT min(n) FROM optimized) =3D (SELECT min(n) FROM baseline) AS invariant_holds; ROLLBACK; Actual result ------------- The plain predicate appears as a sequential-scan filter below WindowAgg, while the value-preserving volatile form remains above WindowAgg: optimized_n | baseline_n | invariant_holds -------------+------------+----------------- 1 | 2 | f (1 row) Expected result --------------- Filtering must preserve every member equal under the window partition semantics. Both forms should report partition count 2, and invariant_holds should be true. Additional information ---------------------- The issue was reproduced on PostgreSQL 20devel, PostgreSQL 18.6, and PostgreSQL 17.11. This issue may be similar to the three reports below [1=E2=80=932], but I a= m unsure whether they share the same root cause. [1] [Fujii Masao=E2=80=99s scale(numeric) report](https://www.postgresql.org/message-id/CAHGQGwGnPNX=3DnK+nXL0ihh+8WO= bQnFWjnPi5uvxBFvbtqN9WmQ@mail.gmail.com) [2] [Richard Guo=E2=80=99s explanation of the known limitation](https://www.postgresql.org/message-id/CAMbWs4_=3D1kH=3DJ2qDmdQm= 1NrM0oVPXO8dETOSaUfMUYatfbDbRA@mail.gmail.com)