Re: SIMILAR TO expressions translate wildcards where they shouldn't

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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