Message-ID: <2c58dcdba889f5b28df5ad9d21b5ea2d0ac63a9a.camel@cybertec.at>
Subject: Re: Latest patches break one of our unit-test, related to RLS
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Tom Lane <tgl@sss.pgh.pa.us>, Dominique Devienne <ddevienne@gmail.com>
Cc: pgsql-general@postgresql.org
Date: Sat, 13 Sep 2025 01:34:08 +0200
In-Reply-To: <2109533.1757686026@sss.pgh.pa.us>
References: 
	<CAFCRh-8NwJd0jq6P=R3qhHyqU7hw0BTor3W0SvUcii24et+zAw@mail.gmail.com>
	 <CAFCRh-93hsRvoJqQCwXq06=AJJJ_s783gVsGHKc-QjgOO6jGVg@mail.gmail.com>
	 <77a63548783dd4007ee479a4c5ed300629aaa776.camel@cybertec.at>
	 <CAFCRh--DPWEHpRmdr-Jfw4guYqjc9TM_QyVH7SR4B=Le5gGe3Q@mail.gmail.com>
	 <CAFCRh--M1y2YpJJMb6undzvyvhd4geKtRWFkDyr1shHvvoOkpQ@mail.gmail.com>
	 <CAFCRh-_OBKtabe8MO0vUocNSq_O3_b852AXknPiNhL0tNUBGQg@mail.gmail.com>
	 <CAFCRh-_U+8fmRoc-sYM5ahp=9dNjs=Dri1LFLcHCgEJX8Zq_oA@mail.gmail.com>
	 <2109533.1757686026@sss.pgh.pa.us>
Content-Type: multipart/mixed; boundary="=-8/q/9WuncXWbNi2+vA3X"
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/2c58dcdba889f5b28df5ad9d21b5ea2d0ac63a9a.camel%40cybertec.at>
Precedence: bulk

--=-8/q/9WuncXWbNi2+vA3X
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, 2025-09-12 at 10:07 -0400, Tom Lane wrote:
> Dominique Devienne <ddevienne@gmail.com> writes:
> > > This DOES look like a bug, no? I've done regexes for a long time,
> > > and these two forms should be equivalent IMHO. --DD
>=20
> Yeah, I agree it's busted.=C2=A0 You can use EXPLAIN VERBOSE to see the
> translated-to-POSIX pattern, and it's wrong:
>=20
> regression=3D# explain verbose with t(v) as (values ('foo:bar'), ('foo/ba=
r'), ('foo0bar'))=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=20
> select v from t where v similar to 'foo[\d\w]_%';
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0 QUERY PLAN=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=20
> --------------------------------------------------------------
> =C2=A0Values Scan on "*VALUES*"=C2=A0 (cost=3D0.00..0.05 rows=3D1 width=
=3D32)
> =C2=A0=C2=A0 Output: "*VALUES*".column1
> =C2=A0=C2=A0 Filter: ("*VALUES*".column1 ~ '^(?:foo[\d\w]_%)$'::text)
> (3 rows)
>=20
> The _ and % are not getting converted to their POSIX equivalents
> ("." and ".*").

Indeed, and I have to take the blame for introducing a bug in a minor
release :^(

The attached patch should fix the problem.

Yours,
Laurenz Albe

--=-8/q/9WuncXWbNi2+vA3X
Content-Disposition: attachment;
	filename*0=v1-0001-Amend-recent-fix-for-SIMILAR-TO-regex-conversion.patc;
	filename*1=h
Content-Transfer-Encoding: base64
Content-Type: text/x-patch;
	name="v1-0001-Amend-recent-fix-for-SIMILAR-TO-regex-conversion.patch";
	charset="UTF-8"

RnJvbSA4ZDBmOGViYWM2YzQyZmU3ZGEzNmVjOGMzMGVlMDkxZDIwMjcwMDY4IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5h
dD4KRGF0ZTogU2F0LCAxMyBTZXAgMjAyNSAwMTozMjo1NSArMDIwMApTdWJqZWN0OiBbUEFUQ0gg
djFdIEFtZW5kIHJlY2VudCBmaXggZm9yIFNJTUlMQVIgVE8gcmVnZXggY29udmVyc2lvbgoKQ29t
bWl0IGUzZmZjM2U5MWQgZml4ZWQgdGhlIHRyYW5zbGF0aW9uIG9mIGNoYXJhY3RlciBjbGFzc2Vz
IGluClNJTUlMQVIgVE8gcmVndWxhciBleHByZXNzaW9ucy4gIFVuZm9ydHVuYXRlbHkgdGhlIGZp
eCBicm9rZSBhIGNvcm5lcgpjYXNlOiBpZiB0aGVyZSBpcyBhbiBlc2NhcGUgY2hhcmFjdGVyIHJp
Z2h0IGFmdGVyIHRoZSBvcGVuaW5nIGJyYWNrZXQsCihmb3IgZXhhbXBsZSBpbiAiW1xxXSIpIGEg
Y2xvc2luZyBicmFja2V0IHJpZ2h0IGFmdGVyIHRoZSBlc2NhcGUKc2VxdWVuY2Ugd291bGQgbm90
IGJlIHNlZW4gYXMgY2xvc2luZyB0aGUgY2hhcmFjdGVyIGNsYXNzLgoKQXV0aG9yOiBMYXVyZW56
IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5hdD4KUmVwb3J0ZWQtQnk6IERvbWluaXF1ZSBE
ZXZpZW5uZSA8ZGRldmllbm5lQGdtYWlsLmNvbT4KUmVwb3J0ZWQtQnk6IFN0ZXBoYW4gU3ByaW5n
bCA8c3ByaW5nbC1wc3FsQGJmdy1vbmxpbmUuZGU+CkJhY2twYXRjaC10aHJvdWdoOiAxMwotLS0K
IHNyYy9iYWNrZW5kL3V0aWxzL2FkdC9yZWdleHAuYyAgICAgICAgfCA2ICsrKysrKwogc3JjL3Rl
c3QvcmVncmVzcy9leHBlY3RlZC9zdHJpbmdzLm91dCB8IDkgKysrKysrKysrCiBzcmMvdGVzdC9y
ZWdyZXNzL3NxbC9zdHJpbmdzLnNxbCAgICAgIHwgMyArKysKIDMgZmlsZXMgY2hhbmdlZCwgMTgg
aW5zZXJ0aW9ucygrKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9yZWdleHAu
YyBiL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9yZWdleHAuYwppbmRleCA2ZTI4NjRjYmJkYS4uMjk2
OTJmYjFhOWQgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9yZWdleHAuYworKysg
Yi9zcmMvYmFja2VuZC91dGlscy9hZHQvcmVnZXhwLmMKQEAgLTk0OCw2ICs5NDgsMTIgQEAgc2lt
aWxhcl9lc2NhcGVfaW50ZXJuYWwodGV4dCAqcGF0X3RleHQsIHRleHQgKmVzY190ZXh0KQogCQkJ
CSAqLwogCQkJCSpyKysgPSAnXFwnOwogCQkJCSpyKysgPSBwY2hhcjsKKwkJCQkvKgorCQkJCSAq
IElmIHdlIGVuY291bnRlciBhbiBlc2NhcGVkIGNoYXJhY3RlciBpbiBhIGNoYXJhY3RlciBjbGFz
cywKKwkJCQkgKiB3ZSBtdXN0IGJlIHBhc3QgYW4gaW5pdGlhbCBeIG9yIF0uCisJCQkJICovCisJ
CQkJaWYgKGNoYXJjbGFzc19kZXB0aCA+IDApCisJCQkJCWNoYXJjbGFzc19zdGFydCA9IDM7CiAJ
CQl9CiAJCQlhZnRlcmVzY2FwZSA9IGZhbHNlOwogCQl9CmRpZmYgLS1naXQgYS9zcmMvdGVzdC9y
ZWdyZXNzL2V4cGVjdGVkL3N0cmluZ3Mub3V0IGIvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9z
dHJpbmdzLm91dAppbmRleCBiYTMwMmRhNTFlNy4uMmQ2Y2IwMmFkNjAgMTAwNjQ0Ci0tLSBhL3Ny
Yy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvc3RyaW5ncy5vdXQKKysrIGIvc3JjL3Rlc3QvcmVncmVz
cy9leHBlY3RlZC9zdHJpbmdzLm91dApAQCAtNjkzLDYgKzY5MywxNSBAQCBFWFBMQUlOIChDT1NU
UyBPRkYpIFNFTEVDVCAqIEZST00gVEVYVF9UQkwgV0hFUkUgZjEgU0lNSUxBUiBUTyAnW15eXV4n
OwogICAgRmlsdGVyOiAoZjEgfiAnXig/OlteXl1cXikkJzo6dGV4dCkKICgyIHJvd3MpCiAKKy0t
IENsb3Npbmcgc3F1YXJlIGJyYWNrZXQgYWZ0ZXIgYW4gZXNjYXBlIHNlcXVlbmNlIGF0IHRoZSBi
ZWdpbm5pbmcgb2YKKy0tIGEgY2hhcmFjdGVyIGNsb3NlcyB0aGUgY2hhcmFjdGVyIGNsYXNzCitF
WFBMQUlOIChDT1NUUyBPRkYpIFNFTEVDVCAqIEZST00gVEVYVF9UQkwgV0hFUkUgZjEgU0lNSUxB
UiBUTyAnW3xhXSUnIEVTQ0FQRSAnfCc7CisgICAgICAgICAgICAgIFFVRVJZIFBMQU4gICAgICAg
ICAgICAgICAKKy0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQorIFNlcSBT
Y2FuIG9uIHRleHRfdGJsCisgICBGaWx0ZXI6IChmMSB+ICdeKD86W1xhXS4qKSQnOjp0ZXh0KQor
KDIgcm93cykKKwogLS0gVGVzdCBiYWNrc2xhc2ggZXNjYXBlcyBpbiByZWdleHBfcmVwbGFjZSdz
IHJlcGxhY2VtZW50IHN0cmluZwogU0VMRUNUIHJlZ2V4cF9yZXBsYWNlKCcxMTEyMjIzMzMzJywg
RScoXFxkezN9KShcXGR7M30pKFxcZHs0fSknLCBFJyhcXDEpIFxcMi1cXDMnKTsKICByZWdleHBf
cmVwbGFjZSAKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL3N0cmluZ3Muc3FsIGIv
c3JjL3Rlc3QvcmVncmVzcy9zcWwvc3RyaW5ncy5zcWwKaW5kZXggYjk0MDA0Y2MwOGMuLjVlZDQy
MWQ2MjA1IDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC9zdHJpbmdzLnNxbAorKysg
Yi9zcmMvdGVzdC9yZWdyZXNzL3NxbC9zdHJpbmdzLnNxbApAQCAtMjE4LDYgKzIxOCw5IEBAIEVY
UExBSU4gKENPU1RTIE9GRikgU0VMRUNUICogRlJPTSBURVhUX1RCTCBXSEVSRSBmMSBTSU1JTEFS
IFRPICdbXSVdW15dJV1bXiVdJSc7CiAtLSBDbG9zaW5nIHNxdWFyZSBicmFja2V0IGVmZmVjdGl2
ZSBhZnRlciB0d28gY2FyZXRzIGF0IHRoZSBiZWdpbm5pbmcKIC0tIG9mIGNoYXJhY3RlciBjbGFz
cy4KIEVYUExBSU4gKENPU1RTIE9GRikgU0VMRUNUICogRlJPTSBURVhUX1RCTCBXSEVSRSBmMSBT
SU1JTEFSIFRPICdbXl5dXic7CistLSBDbG9zaW5nIHNxdWFyZSBicmFja2V0IGFmdGVyIGFuIGVz
Y2FwZSBzZXF1ZW5jZSBhdCB0aGUgYmVnaW5uaW5nIG9mCistLSBhIGNoYXJhY3RlciBjbG9zZXMg
dGhlIGNoYXJhY3RlciBjbGFzcworRVhQTEFJTiAoQ09TVFMgT0ZGKSBTRUxFQ1QgKiBGUk9NIFRF
WFRfVEJMIFdIRVJFIGYxIFNJTUlMQVIgVE8gJ1t8YV0lJyBFU0NBUEUgJ3wnOwogCiAtLSBUZXN0
IGJhY2tzbGFzaCBlc2NhcGVzIGluIHJlZ2V4cF9yZXBsYWNlJ3MgcmVwbGFjZW1lbnQgc3RyaW5n
CiBTRUxFQ1QgcmVnZXhwX3JlcGxhY2UoJzExMTIyMjMzMzMnLCBFJyhcXGR7M30pKFxcZHszfSko
XFxkezR9KScsIEUnKFxcMSkgXFwyLVxcMycpOwotLSAKMi41MS4wCgo=


--=-8/q/9WuncXWbNi2+vA3X--