Re: Notes about behaviour of SIMILAR TO operator

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Adam Buraczewski <adamb(at)nor(dot)pl>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Notes about behaviour of SIMILAR TO operator
Date: 2003-11-20 22:57:27
Message-ID: 7420.1069369047@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Adam Buraczewski <adamb(at)nor(dot)pl> writes:
> ... for example the pattern 'a|z' (which should match single 'a' or 'z'
> characters only, according to SQL spec) is converted into POSIX
> regular expression in the form of '^a|z$' which matches all strings
> beginning with 'a' ('abcdef' for example) and all strings ending with
> 'z' ('xyz' for example). So the meaning of the pattern is changed,
> which is not good.

Hm, that's a mistake, it should probably translate to ^(a|z)$ instead.

> The behaviour above is also caused by similar_escape(), which converts
> '[_]' to '^[.]$' and '[%]' to '^[.*]$', not noticing the simple fact
> that these characters are inside brackets.

As near as I can tell, the SQL spec requires special characters to be
escaped when they are inside a bracket construct. So indeed the above
are invalid SQL regexes.

> Talking about square brackets, it should be noticed that there is a
> slight difference between SIMILAR TO and POSIX way of describing named
> character classes.

Mmm, yeah, that looks like a mess.

> This at least could be avoided simply by prepending regular expression
> returned by similar_escape() with a magic sequence '***:' which
> switches regexp engine into ARE mode.

Good point. Actually, do we want to force ARE mode, or something simpler?
Perhaps ERE or even BRE would be a better match to the SQL spec.

> I think I am able to write such a patch in my spare time,

Go to it ...

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message GreyGeek 2003-11-21 01:11:00 Re: IpcSemaphoreCreate: semget(...) failed: No space left on device
Previous Message Tom Lane 2003-11-20 21:10:47 Re: [BUGS] 7.4: CHAR padding inconsistency