MIME-Version: 1.0
References: <19534-9cdf4693c42033da@postgresql.org>
In-Reply-To: <19534-9cdf4693c42033da@postgresql.org>
From: Tender Wang <tndrwang@gmail.com>
Date: Sun, 28 Jun 2026 19:33:47 +0800
Message-ID: 
 <CAHewXNk8xt1pNepcEiCx_e6_SW_=bm1GNnU7+bPczoZJuLC5qQ@mail.gmail.com>
Subject: Re: BUG #19534: Qual pushdown across a window subquery is unsafe with
 nondeterministic partition collations
To: imchifan@163.com, pgsql-bugs@lists.postgresql.org
Content-Type: multipart/mixed; boundary="000000000000ec173906554eb755"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHewXNk8xt1pNepcEiCx_e6_SW_%3Dbm1GNnU7%2BbPczoZJuLC5qQ%40mail.gmail.com>
Precedence: bulk

--000000000000ec173906554eb755
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

PG Bug reporting form <noreply@postgresql.org> =E4=BA=8E2026=E5=B9=B46=E6=
=9C=8827=E6=97=A5=E5=91=A8=E5=85=AD 20:55=E5=86=99=E9=81=93=EF=BC=9A
>
> The following bug has been logged on the website:
>
> Bug reference:      19534
> 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:
>
> ## Expected Behavior
>
> The window query should return the same result as the reference query. Si=
nce
> `'abc'` and `'ABC'` are in the same nondeterministic partition, the
> partition sum for row `'abc'` should be `3`.
>
> ## Actual Behavior
>
> The window query returns `abc | 1 | 1`, while the reference query returns
> `abc | 1 | 3`. `EXPLAIN` shows that the strict-collation filter is pushed
> below `WindowAgg`, which changes the partition before the window sum is
> computed.
>

Yes, I can reproduce this on HEAD.
The whereClause was pushed down to the scan level of table t_window_ci.
So the row with 'ABC' was ignored by the filter.

In current logic, we have no logic to process a nondeterministic
partition clause when considering whether the
qual can be pushed down to the subquery.

I try to fix this with the attached patch.
I add a new flag UNSAFE_HAS_NONDETERMINITIC for
pushdown_safety_info.unsafeFlags.
If the var is in the partition clause and it is nondeterministic, we
set this flag.
In qual_is_pushdown_safe(), we check the qual's inputcollid; if it is
deterministic, but the var in
the partition clause is nondeterministic, it is PUSHDOWN_UNSAFE.

Any thoughts?
--=20
Thanks,
Tender Wang

--000000000000ec173906554eb755
Content-Type: application/octet-stream;
	name="0001-Fix-qual-pushdown-with-nondeterministic-partition-co.patch"
Content-Disposition: attachment;
	filename="0001-Fix-qual-pushdown-with-nondeterministic-partition-co.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mqxpeadq0>
X-Attachment-Id: f_mqxpeadq0

RnJvbSBjOTlkY2IzZGUzZDBlNDJkMDNhZjcxMDI3Y2E0YjFjZDZkMTMwMTI3IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBVYnVudHUgPHVidW50dUBsb2NhbGhvc3QubG9jYWxkb21haW4+
CkRhdGU6IFN1biwgMjggSnVuIDIwMjYgMTk6MjE6NDAgKzA4MDAKU3ViamVjdDogW1BBVENIXSBG
aXggcXVhbCBwdXNoZG93biB3aXRoIG5vbmRldGVybWluaXN0aWMgcGFydGl0aW9uIGNvbGxhdGlv
bnMKCi0tLQogc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvYWxscGF0aHMuYyB8IDM3ICsrKysr
KysrKysrKysrKysrKysrKysrKysrKwogMSBmaWxlIGNoYW5nZWQsIDM3IGluc2VydGlvbnMoKykK
CmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGF0aC9hbGxwYXRocy5jIGIvc3Jj
L2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvYWxscGF0aHMuYwppbmRleCBjMTM0NTk0YTIxYS4uOTk1
OTcyZTE1ZGMgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wYXRoL2FsbHBhdGhz
LmMKKysrIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvYWxscGF0aHMuYwpAQCAtNTcsNiAr
NTcsNyBAQAogI2RlZmluZSBVTlNBRkVfTk9USU5fRElTVElOQ1RPTl9DTEFVU0UJKDEgPDwgMikK
ICNkZWZpbmUgVU5TQUZFX05PVElOX1BBUlRJVElPTkJZX0NMQVVTRQkoMSA8PCAzKQogI2RlZmlu
ZSBVTlNBRkVfVFlQRV9NSVNNQVRDSAkJCSgxIDw8IDQpCisjZGVmaW5lIFVOU0FGRV9IQVNfTk9O
REVURVJNSU5JVElDCQkoMSA8PCA1KQogCiAvKiByZXN1bHRzIG9mIHN1YnF1ZXJ5X2lzX3B1c2hk
b3duX3NhZmUgKi8KIHR5cGVkZWYgc3RydWN0IHB1c2hkb3duX3NhZmV0eV9pbmZvCkBAIC0xNTks
NiArMTYwLDcgQEAgc3RhdGljIHZvaWQgY2hlY2tfb3V0cHV0X2V4cHJlc3Npb25zKFF1ZXJ5ICpz
dWJxdWVyeSwKIHN0YXRpYyB2b2lkIGNvbXBhcmVfdGxpc3RfZGF0YXR5cGVzKExpc3QgKnRsaXN0
LCBMaXN0ICpjb2xUeXBlcywKIAkJCQkJCQkJCXB1c2hkb3duX3NhZmV0eV9pbmZvICpzYWZldHlJ
bmZvKTsKIHN0YXRpYyBib29sIHRhcmdldElzSW5BbGxQYXJ0aXRpb25MaXN0cyhUYXJnZXRFbnRy
eSAqdGxlLCBRdWVyeSAqcXVlcnkpOworc3RhdGljIGJvb2wgY29udGFpbl9ub25kZXRlcm1pbmlz
dGljX3BhcnRpdGlvbl9jbGF1c2UoVGFyZ2V0RW50cnkgKnRsZSwgUXVlcnkgKnF1ZXJ5KTsKIHN0
YXRpYyBwdXNoZG93bl9zYWZlX3R5cGUgcXVhbF9pc19wdXNoZG93bl9zYWZlKFF1ZXJ5ICpzdWJx
dWVyeSwgSW5kZXggcnRpLAogCQkJCQkJCQkJCQkJUmVzdHJpY3RJbmZvICpyaW5mbywKIAkJCQkJ
CQkJCQkJCXB1c2hkb3duX3NhZmV0eV9pbmZvICpzYWZldHlJbmZvKTsKQEAgLTQzNDUsNiArNDM0
NywxNiBAQCBjaGVja19vdXRwdXRfZXhwcmVzc2lvbnMoUXVlcnkgKnN1YnF1ZXJ5LCBwdXNoZG93
bl9zYWZldHlfaW5mbyAqc2FmZXR5SW5mbykKIAkJCXNhZmV0eUluZm8tPnVuc2FmZUZsYWdzW3Rs
ZS0+cmVzbm9dIHw9IFVOU0FGRV9OT1RJTl9QQVJUSVRJT05CWV9DTEFVU0U7CiAJCQljb250aW51
ZTsKIAkJfQorCisJCWlmIChzdWJxdWVyeS0+aGFzV2luZG93RnVuY3MgJiYKKwkJCShzYWZldHlJ
bmZvLT51bnNhZmVGbGFnc1t0bGUtPnJlc25vXSAmCisJCQkgVU5TQUZFX0hBU19OT05ERVRFUk1J
TklUSUMpID09IDAgJiYKKwkJCWNvbnRhaW5fbm9uZGV0ZXJtaW5pc3RpY19wYXJ0aXRpb25fY2xh
dXNlKHRsZSwgc3VicXVlcnkpKQorCQl7CisJCQkvKiBoYXMgbm9uZGV0ZXJtaW5pc3RpYyBwYXJ0
aXRpb24gY29sbGF0aW9ucyAqLworCQkJc2FmZXR5SW5mby0+dW5zYWZlRmxhZ3NbdGxlLT5yZXNu
b10gfD0gVU5TQUZFX0hBU19OT05ERVRFUk1JTklUSUM7CisJCQljb250aW51ZTsKKwkJfQogCX0K
IH0KIApAQCAtNDQxMyw2ICs0NDI1LDIyIEBAIHRhcmdldElzSW5BbGxQYXJ0aXRpb25MaXN0cyhU
YXJnZXRFbnRyeSAqdGxlLCBRdWVyeSAqcXVlcnkpCiAJcmV0dXJuIHRydWU7CiB9CiAKK3N0YXRp
YyBib29sCitjb250YWluX25vbmRldGVybWluaXN0aWNfcGFydGl0aW9uX2NsYXVzZShUYXJnZXRF
bnRyeSAqdGxlLCBRdWVyeSAqcXVlcnkpCit7CisJT2lkIGNvbGxpZDsKKworCWlmICghdGFyZ2V0
SXNJbkFsbFBhcnRpdGlvbkxpc3RzKHRsZSwgcXVlcnkpKQorCQlyZXR1cm4gZmFsc2U7CisKKwlj
b2xsaWQgPSBleHByQ29sbGF0aW9uKChOb2RlICopIHRsZS0+ZXhwcik7CisKKwlpZiAoT2lkSXNW
YWxpZChjb2xsaWQpKQorCQlyZXR1cm4gIWdldF9jb2xsYXRpb25faXNkZXRlcm1pbmlzdGljKGNv
bGxpZCk7CisKKwlyZXR1cm4gZmFsc2U7Cit9CisKIC8qCiAgKiBxdWFsX2lzX3B1c2hkb3duX3Nh
ZmUgLSBpcyBhIHBhcnRpY3VsYXIgcmluZm8gc2FmZSB0byBwdXNoIGRvd24/CiAgKgpAQCAtNDUy
NSw2ICs0NTUzLDE1IEBAIHF1YWxfaXNfcHVzaGRvd25fc2FmZShRdWVyeSAqc3VicXVlcnksIElu
ZGV4IHJ0aSwgUmVzdHJpY3RJbmZvICpyaW5mbywKIAkJCQlzYWZlID0gUFVTSERPV05fVU5TQUZF
OwogCQkJCWJyZWFrOwogCQkJfQorCQkJZWxzZSBpZiAoc2FmZXR5SW5mby0+dW5zYWZlRmxhZ3Nb
dmFyLT52YXJhdHRub10gJgorCQkJCQkgVU5TQUZFX0hBU19OT05ERVRFUk1JTklUSUMpCisJCQl7
CisJCQkJaWYgKGdldF9jb2xsYXRpb25faXNkZXRlcm1pbmlzdGljKGV4cHJJbnB1dENvbGxhdGlv
bihxdWFsKSkpCisJCQkJeworCQkJCQlzYWZlID0gUFVTSERPV05fVU5TQUZFOworCQkJCQlicmVh
azsKKwkJCQl9CisJCQl9CiAJCQllbHNlCiAJCQl7CiAJCQkJLyogVU5TQUZFX05PVElOX1BBUlRJ
VElPTkJZX0NMQVVTRSBpcyBvayBmb3IgcnVuIGNvbmRpdGlvbnMgKi8KLS0gCjIuMzQuMQoK
--000000000000ec173906554eb755--