From: Chengpeng Yan <chengpeng_yan@outlook.com>
To: "imchifan@163.com" <imchifan@163.com>, "pgsql-bugs@lists.postgresql.org"
	<pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19532: Window run-condition optimization can produce wrong
 results for count() window aggregates
Thread-Topic: BUG #19532: Window run-condition optimization can produce wrong
 results for count() window aggregates
Thread-Index: AQHdBi80AI6KTaf3IUSE00Vpmq6wpbZSoHOA
Date: Sat, 27 Jun 2026 17:00:20 +0000
Message-ID: <7802C3D0-8769-4E72-A408-F08B07138F0B@outlook.com>
References: <19532-a8893c9d2a630a30@postgresql.org>
In-Reply-To: <19532-a8893c9d2a630a30@postgresql.org>
Accept-Language: zh-CN, en-US
Content-Language: en-US
x-ms-exchange-messagesentrepresentingtype: 1
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: TYYPR01MB14116:EE_|OSZPR01MB8188:EE_
x-ms-office365-filtering-correlation-id: d71c0db4-200e-4a9c-02ed-08ded46d9252
x-microsoft-antispam: 
 BCL:0;ARA:14566002|41001999006|8062599012|19110799012|8060799015|51005399006|31061999003|24021099003|15080799012|25010399006|24121999003|22091999003|10035399007|40105399003|102099032|440099028|3412199025;
x-microsoft-antispam-message-info: 
 =?us-ascii?Q?W15DhJ/zMcMKyAcpUvY9r1NOaHLobddbJLlcbQ0rtNxc3kOUvO8ffUDdB+ej?=
 =?us-ascii?Q?kTs8R64uMzzQQZj0m8u4WIxcBscnWGjTPCnYzw5tkylTBijqHcakaTWTSLAN?=
 =?us-ascii?Q?E6812Ot80QfoGsgVzoTOO0vnex2kMu0y/P6dkteIltidEVPeg80ZLuAOOYIM?=
 =?us-ascii?Q?SyPgqQPjkLqhyCTnx/Unwf4JzlcojeCtYjekdkUMmYiV+QmdNIGnv3IA4zeD?=
 =?us-ascii?Q?+2/x1XEHm7uAP8K8SCCK3B963RyRNrVF8HqlnS5FnyslpXlyGFcEWsWhKCXE?=
 =?us-ascii?Q?yNLVsVqvHsClqldIy5Adr45XvkIXrxBCdduwy6Q4ya/iZ5U8PlE4nt7fMSpZ?=
 =?us-ascii?Q?6adfZVgsCrQk0O6chDETuAQ7Ts8AwE7SWTuztXvAIaj9is3R1JRFf6oMkjf8?=
 =?us-ascii?Q?+HIoRUWNs+0+oC6kz6Wnc47xjcfyZqdA02uvy6UNzfasOQ++31eRMwsU7nmz?=
 =?us-ascii?Q?hT1Qdri3Y5cUB9rTYzaaQTK+MLNzsmpDIY0SxGxbXhPQgQoCy2Yp4rSzF4yj?=
 =?us-ascii?Q?NEhCuYsccSIPppZECy8IXdyEy0v3ekImxIq89VJdOaFwk44b644dllwiWUc6?=
 =?us-ascii?Q?NN5lNyFVfkfu5enMAbH7AvoBq/z+Qps/HYZ3XiY85sddY3yPFna89vgXsiMh?=
 =?us-ascii?Q?0UoaPODetuNn9SoZQusejDWc+R3liQ+SfH+T16dTltSHoBVygHKFQMOF28nW?=
 =?us-ascii?Q?nD3HUmhRJQwuJ6iBsKVo99nKzV1Zyvlp8/8zBIeyj8KoEe07LpkniGe9lYF0?=
 =?us-ascii?Q?lnb6xTrY4oXJ24OMxC0fx5tfQtwMfUbSaETeG7wR1It31S9qpRf6dgMeTxJy?=
 =?us-ascii?Q?58Nmj9UlOMRstPql/Xuhld+f82K5houeqCpKQ28y0UuNfe/u8SybZOFe1bz1?=
 =?us-ascii?Q?2UYudEZgCLKYrP055v3Xl0Gqrx+t4vSkwlnr/jpEsU+lBa+3fCGHYewq6plJ?=
 =?us-ascii?Q?wGcmlVHmwvdgDtA4d5xT0yW8e+csI2+4WWHaHp3+/bb3wvTwMU0BTZ0P2lMq?=
 =?us-ascii?Q?zQmbDUBhNgwMgjTg9o1AZynt3RBYEXj9voZGNbLGF2GmE4uAifMvmEhcnTLI?=
 =?us-ascii?Q?jc7UCoIu?=
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?w2MglWLCqptxAtnU+R1qwrXCnk0M8e9fjs8f8seEl6N660WSrurm3QsArc52?=
 =?us-ascii?Q?7GIHGJLTsKzl8ITRxouRxzTqFn4/piD13JtycvmGHN8IXTtt9D95J3AP6fiG?=
 =?us-ascii?Q?IlW7WGxzgeXLsbHhjPijGDV8VY1VGehex1fVO8l8LKPL9LgZtecxBB3Xqj3h?=
 =?us-ascii?Q?5mJ+u9PIm5IqCwPMLV8Q+FDX+5YJtWuq3LZc9gMxpiq5obVrZtWsVRtWfhke?=
 =?us-ascii?Q?MBeDW+EXmffMJyDY5/Mp8JE164e2uvPjkuTjp1lTTZneEK/seqJYGCB4jNrj?=
 =?us-ascii?Q?JuoR7XkyFYOlQLbTiCK9uGrLVF/HTfm/7wlCYvCus3cp7+ar8CcZ5+IwvRLq?=
 =?us-ascii?Q?u5Kq+P/WvYyGaktTJS5VTC+DR9QC+qTPwNrebevAFONsfX0BnVfQ47TTxYoB?=
 =?us-ascii?Q?/p6sGfenwcat0HhhAxJOOqwVFANt3WnSD0X0E9foLLdoZFuiuEQsDHOfDCdd?=
 =?us-ascii?Q?K0yh+ETAXUq+ShArdP/A2rZdccQKefIjI4FzbqnRy9JdgJkJoLBHrEYhMiB3?=
 =?us-ascii?Q?cnxwgkWayq3Yyb3eiEEoedDbOgYW7iDgf45k3xB08N6TPGcaQlRSDcDNsHQf?=
 =?us-ascii?Q?bPrTzlu1ahBYd9ftxHHYAnFzwXzxzCzprLT1Fw7TCZbH/2NXvdbl0mvXY4lV?=
 =?us-ascii?Q?QFMwmTP24adu4TBegiF3o3ZLxoWUmjqyl5+DyNHvPM/v/CU1a39fpd9th4UT?=
 =?us-ascii?Q?QoNel2Lck/5L1q8b1yE41dNVV9XA8AJblPKK7A6IY8ZfZuJdS3D3qOcIjhAd?=
 =?us-ascii?Q?efM8N/Uo6VCKNCggIBBHG+YvPEfsWYSaSSAcdAwJMga4MxYEywYrG/e8zkF2?=
 =?us-ascii?Q?gqexuuALwAizcEA4rE0E2lv1NAHPyp+4z1l3DHdnfhTWP1s4WY6JOr9PDAY5?=
 =?us-ascii?Q?U13auHJNukYwyLjmFR9dujJveaUMBZk1beKEr+2hwFrUTKkVkyG9dVGQv1J1?=
 =?us-ascii?Q?Pgh29WElLUDCHHsrzANvOgREq/XQKXnkh5QNNKEL7lkdVOEYxCMc4g6VbL6j?=
 =?us-ascii?Q?O5f9tSHMWXp0TFl8FQ9kzyuteOHR6deUH20etCHJKsS3VD9qgGBVAu4z1OII?=
 =?us-ascii?Q?o0jtt9M42Rw2mv7ZmkadSp+P9PvdQiluFXqgrWvwLTNzzwdKIkSK562MKa6Q?=
 =?us-ascii?Q?6z0dTlbVbKnwMl1k3vsHIvR+c2rKfS6t1PVdzoYkGalGt5gKKPgU6mHrSMyC?=
 =?us-ascii?Q?3x8q26qt3NT9w8eiDG66r9IqqsHeQI19W0ElDPam5w9V9P+2gR5P+OFYiUyR?=
 =?us-ascii?Q?ujqpmoIgnbKYNpvavAsVr/KK4ojvEQeBpttn8qpUohrKg0QbfwMS3Jwm9YJz?=
 =?us-ascii?Q?stYGF67rbcTxW2YqS9Y2xu1qN3GYj0iUYTUNbfdB/LhSNMEaWEVgRMQEj+de?=
 =?us-ascii?Q?sdsLd/BgOO6I8sTSQPyYM6OFPN4xq3eTy6OkndUQ+NIeJH2xyw=3D=3D?=
Content-Type: text/plain; charset="us-ascii"
Content-ID: <0AE60ACB4BB01A4588B731AF75BF0236@jpnprd01.prod.outlook.com>
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: TYYPR01MB14116.jpnprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 
 00000000-0000-0000-0000-000000000000
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 d71c0db4-200e-4a9c-02ed-08ded46d9252
X-MS-Exchange-CrossTenant-originalarrivaltime: 27 Jun 2026 17:00:20.4084
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 
 00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: OSZPR01MB8188
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/7802C3D0-8769-4E72-A408-F08B07138F0B%40outlook.com>
Precedence: bulk

Hi,

> On Jun 24, 2026, at 22:34, PG Bug reporting form <noreply@postgresql.org>=
 wrote:
>=20
> The following bug has been logged on the website:
>=20
> Bug reference:      19532
> Logged by:          Qifan Liu
> Email address:      imchifan@163.com
> PostgreSQL version: 18.4
> Operating system:   Ubuntu 20.04 x86-64, docker image postgres:18.4
> Description:       =20
>=20
> ## PoC
>=20
> ```sql
> DROP TABLE IF EXISTS t;
>=20
> CREATE TABLE t (
>    id int PRIMARY KEY,
>    k  int NOT NULL,
>    v  int
> );
>=20
> INSERT INTO t(id, k, v) VALUES
>    (1, 1, 1),
>    (2, 1, NULL),
>    (3, 1, 1),
>    (4, 2, 1);
>=20
> WITH
> source_rows AS (
>    SELECT id
>    FROM (
>        SELECT id,
>               count(v) OVER (
>                   ORDER BY k
>                   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
>                   EXCLUDE CURRENT ROW
>               ) AS c
>        FROM t
>    ) s
>    WHERE c <=3D 1
> ),
> reference_rows AS (
>    SELECT t1.id
>    FROM t AS t1
>    WHERE (
>        SELECT count(t2.v)
>        FROM t AS t2
>        WHERE t2.k <=3D t1.k
>          AND t2.id <> t1.id
>    ) <=3D 1
> ),
> metamorphic_rows AS (
>    SELECT id
>    FROM (
>        SELECT id,
>               count(v) OVER (
>                   ORDER BY k
>                   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
>                   EXCLUDE CURRENT ROW
>               ) AS c
>        FROM t
>    ) s
>    WHERE c + 0 <=3D 1
> )
> SELECT 'source' AS label, coalesce(array_agg(id ORDER BY id), '{}'::int[]=
)
> AS ids
> FROM source_rows
> UNION ALL
> SELECT 'reference', coalesce(array_agg(id ORDER BY id), '{}'::int[])
> FROM reference_rows
> UNION ALL
> SELECT 'metamorphic_no_pushdown', coalesce(array_agg(id ORDER BY id),
> '{}'::int[])
> FROM metamorphic_rows;
>=20
> EXPLAIN (COSTS OFF)
> SELECT id
> FROM (
>    SELECT id,
>           count(v) OVER (
>               ORDER BY k
>               RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
>               EXCLUDE CURRENT ROW
>           ) AS c
>    FROM t
> ) s
> WHERE c <=3D 1;
> ```
>=20
> ## Expected Behavior
>=20
> The source query should return the same rows as the reference query and t=
he
> semantically equivalent metamorphic_no_pushdown query.
>=20
> ## Actual Behavior
>=20
> The optimized query returns only `{1}`, while the reference and no-pushdo=
wn
> forms return `{1,3}`. `EXPLAIN` shows a `Run Condition` on the window
> aggregate, indicating that the optimization changed query semantics.


This appears to be a duplicate of BUG #19533, so I have posted a
proposed fix in that thread:
https://www.postgresql.org/message-id/flat/19533-413a1014e5d0e766%40postgre=
sql.org

--
Best regards,
Chengpeng Yan