Re: SIMILAR TO expressions translate wildcards where they shouldn't - Mailing list pgsql-bugs

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