Horton
Menu

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

Tom Lane <tgl@sss.pgh.pa.us>

View thread
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
Date: 2025-05-27T14:54:12Z
Lists: 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

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Adjust regex for test with opening parenthesis in character classes

  2. Fix conversion of SIMILAR TO regexes for character classes