Horton
Menu

SIMILAR TO expressions translate wildcards where they shouldn't

Laurenz Albe <laurenz.albe@cybertec.at>

View thread
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: pgsql-bugs@lists.postgresql.org
Date: 2025-05-22T21:18:44Z
Lists: pgsql-bugs

Attachments

The following surprising result

  SELECT 'a_b' SIMILAR TO '[_[:alpha:]]*',
         'a_b' SIMILAR TO '[[:alpha:]_]*';

   ?column? │ ?column? 
  ══════════╪══════════
   t        │ f
  (1 row)

becomes clear when we look how the expressions are translated to
regular expressions:

  EXPLAIN (VERBOSE, GENERIC_PLAN, COSTS OFF)
  SELECT $1 SIMILAR TO '[_[:alpha:]]*',
         $1 SIMILAR TO '[[:alpha:]_]*';

                                      QUERY PLAN                                    
  ══════════════════════════════════════════════════════════════════════════════════
   Result
     Output: ($1 ~ '^(?:[_[:alpha:]]*)$'::text), ($1 ~ '^(?:[[:alpha:].]*)$'::text)
  (2 rows)

The underscore before the [:alpha:] is left alone, but the one after
it gets translated to a period.  Now the underscore is a wildcard
that corresponds to the period in regular expressions, but characters
in square brackets should lose their special meaning.  The code in
utils/adt/regexp.c doesn't expect that square brackets can be nested.

The attached patch fixes the bug.

Yours,
Laurenz Albe

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