From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dominique Devienne <ddevienne@gmail.com>
cc: Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-general@postgresql.org
Subject: Re: Latest patches break one of our unit-test, related to RLS
In-reply-to: <CAFCRh-_U+8fmRoc-sYM5ahp=9dNjs=Dri1LFLcHCgEJX8Zq_oA@mail.gmail.com>
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>
Comments: In-reply-to Dominique Devienne <ddevienne@gmail.com>
	message dated "Fri, 12 Sep 2025 15:34:58 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2109532.1757686026.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 12 Sep 2025 10:07:06 -0400
Message-ID: <2109533.1757686026@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2109533.1757686026%40sss.pgh.pa.us>
Precedence: bulk

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

Yeah, I agree it's busted.  You can use EXPLAIN VERBOSE to see the
translated-to-POSIX pattern, and it's wrong:

regression=3D# explain verbose with t(v) as (values ('foo:bar'), ('foo/bar=
'), ('foo0bar'))                                         =

select v from t where v similar to 'foo[\d\w]_%';
                          QUERY PLAN                          =

--------------------------------------------------------------
 Values Scan on "*VALUES*"  (cost=3D0.00..0.05 rows=3D1 width=3D32)
   Output: "*VALUES*".column1
   Filter: ("*VALUES*".column1 ~ '^(?:foo[\d\w]_%)$'::text)
(3 rows)

The _ and % are not getting converted to their POSIX equivalents
("." and ".*").  Your other example still does that correctly:

regression=3D# explain verbose with t(v) as (values ('foo:bar'), ('foo/bar=
'), ('foo0bar'))
select v from t where v similar to 'foo[0-9a-zA-Z]_%';
                            QUERY PLAN                            =

------------------------------------------------------------------
 Values Scan on "*VALUES*"  (cost=3D0.00..0.05 rows=3D1 width=3D32)
   Output: "*VALUES*".column1
   Filter: ("*VALUES*".column1 ~ '^(?:foo[0-9a-zA-Z]..*)$'::text)
(3 rows)

So e3ffc3e91 was at least one brick shy of a load.

			regards, tom lane