From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
cc: Michael Paquier <michael@paquier.xyz>, pgsql-bugs@lists.postgresql.org
Subject: Re: SIMILAR TO expressions translate wildcards where they shouldn't
In-reply-to: <37584c1321b984a0c5c05d213ef70b869d95a12a.camel@cybertec.at>
References: <16ab039d1af455652bdf4173402ddda145f2c73b.camel@cybertec.at>
 <aC_K7JPXF0AdPZqB@paquier.xyz> <aC_p7b5H6pLICU_e@paquier.xyz>
 <5498224d1d5a6a3be7f29ae7725f76162d48372f.camel@cybertec.at>
 <aDVUWaoZg8cW3B9x@paquier.xyz>
 <37584c1321b984a0c5c05d213ef70b869d95a12a.camel@cybertec.at>
Comments: In-reply-to Laurenz Albe <laurenz.albe@cybertec.at>
	message dated "Tue, 27 May 2025 12:24:33 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1101507.1748357652.1@sss.pgh.pa.us>
Date: Tue, 27 May 2025 10:54:12 -0400
Message-ID: <1101508.1748357652@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1101508.1748357652%40sss.pgh.pa.us>
Precedence: bulk

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Tue, 2025-05-27 at 14:57 +0900, Michael Paquier wrote:
>> With some tweaks and the tests reworked, I am finishing with the
>> reviewed version attached.  What do you think?

> Thank you; I think that is good to go.

Code changes look good, but I think the test cases are too cute:

+EXPLAIN (VERBOSE, COSTS OFF) SELECT (SELECT '') SIMILAR TO '_[_[:alpha:]_]_';
+                          QUERY PLAN                           
+---------------------------------------------------------------
+ Result
+   Output: ((InitPlan 1).col1 ~ '^(?:.[_[:alpha:]_].)$'::text)
+   InitPlan 1
+     ->  Result
+           Output: ''::text
+(5 rows)

This will break whenever somebody decides it's worth optimizing
a sub-select that looks like that.  I'd suggest following the
pattern

explain (costs off) select * from text_tbl where f1 similar to 'z';
            QUERY PLAN            
----------------------------------
 Seq Scan on text_tbl
   Filter: (f1 ~ '^(?:z)$'::text)
(2 rows)

which is both less noisy and less likely to change in future.

			regards, tom lane