MIME-Version: 1.0
References: <19534-9cdf4693c42033da@postgresql.org>
 <CAHewXNk8xt1pNepcEiCx_e6_SW_=bm1GNnU7+bPczoZJuLC5qQ@mail.gmail.com>
 <544F3673-0EC9-4440-9FC5-4BB4F0AA3037@outlook.com>
In-Reply-To: <544F3673-0EC9-4440-9FC5-4BB4F0AA3037@outlook.com>
From: Tender Wang <tndrwang@gmail.com>
Date: Mon, 29 Jun 2026 09:22:49 +0800
Message-ID: 
 <CAHewXN=xpqkC5FKc3st1zXrixgR-8Y_NEw_08vCCoPE-dcZccA@mail.gmail.com>
Subject: Re: BUG #19534: Qual pushdown across a window subquery is unsafe with
 nondeterministic partition collations
To: Chengpeng Yan <chengpeng_yan@outlook.com>
Cc: "imchifan@163.com" <imchifan@163.com>,
	"pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAHewXN%3DxpqkC5FKc3st1zXrixgR-8Y_NEw_08vCCoPE-dcZccA%40mail.gmail.com>
Precedence: bulk

Chengpeng Yan <chengpeng_yan@outlook.com> =E4=BA=8E2026=E5=B9=B46=E6=9C=882=
8=E6=97=A5=E5=91=A8=E6=97=A5 21:43=E5=86=99=E9=81=93=EF=BC=9A
>
> Hi
>
> ```
> CREATE COLLATION case_insensitive
> (provider =3D icu, locale =3D '@colStrength=3Dsecondary',
> deterministic =3D false);
>
> CREATE TABLE t (x text COLLATE case_insensitive);
> INSERT INTO t VALUES ('abc'), ('ABC');
>
> SELECT x, c
> FROM (
> SELECT x, count(*) OVER (PARTITION BY x) AS c
> FROM t
> ) s
> WHERE ascii(x) =3D 97;
> ```
>
> Without the patch, the planner can push the `ascii(x) =3D 97` filter belo=
w
> `WindowAgg`, so the result is:
>
> ```
> x | c
> -----+---
> abc | 1
> ```
>
> The correct result is:
>
> ```
> x | c
> -----+---
> abc | 2
> ```

Good catch.
I didn't take this query into account.

> Postgres only knows at this point that the column appears in every
> window `PARTITION BY` list; it does not know that an arbitrary qual on
> that column is constant over the partition equality class. The attached
> v2 patch therefore takes a conservative approach: if the matching
> partition key uses a nondeterministic collation, ordinary qual pushdown
> is disabled for that column.
>
> The tradeoff is that we may miss some pushdown opportunities for quals
> that really are constant over the partition. But for a wrong-result
> case, that is safer than pushing a qual whose behavior over the
> partition is not proven. Deterministic partition keys keep the existing
> behavior.
>
> I ran `make check`; all tests passed.
>
> Any thoughts?

The correct result is the most important thing. So I agree with you.
The fix seems obvious: we should refuse quals pushed down to the
subquery if the quals contain a nondeterministic partition clause var.


--=20
Thanks,
Tender Wang