public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tender Wang <[email protected]>
To: Chengpeng Yan <[email protected]>
Cc: [email protected] <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: BUG #19534: Qual pushdown across a window subquery is unsafe with nondeterministic partition collations
Date: Mon, 29 Jun 2026 09:22:49 +0800
Message-ID: <CAHewXN=xpqkC5FKc3st1zXrixgR-8Y_NEw_08vCCoPE-dcZccA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<CAHewXNk8xt1pNepcEiCx_e6_SW_=bm1GNnU7+bPczoZJuLC5qQ@mail.gmail.com>
	<[email protected]>

Chengpeng Yan <[email protected]> 于2026年6月28日周日 21:43写道：
>
> Hi
>
> ```
> CREATE COLLATION case_insensitive
> (provider = icu, locale = '@colStrength=secondary',
> deterministic = 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) = 97;
> ```
>
> Without the patch, the planner can push the `ascii(x) = 97` filter below
> `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.



-- 
Thanks,
Tender Wang

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: BUG #19534: Qual pushdown across a window subquery is unsafe with nondeterministic partition collations
  In-Reply-To: <CAHewXN=xpqkC5FKc3st1zXrixgR-8Y_NEw_08vCCoPE-dcZccA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox