LIKE is broken for many patterns

[monetdb-team]

Date: 2015-08-31 16:14:18 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>
To: SQL devs <>
Version: 11.21.5 (Jul2015)

Last updated: 2015-11-03 10:18:13 +0100

Comment 21240

Date: 2015-08-31 16:14:18 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:40.0) Gecko/20100101 Firefox/40.0
Build Identifier:

Hi everyone,

We found that backslashes () and various others characters are not properly escaped when a LIKE pattern is translated to PCRE.

Reproducible: Always

Steps to Reproduce:

It seems that the sql2pcre function in monetdb5/modules/mal/pcre.c is missing several characters to escape.

The charaters to escape are:

. + ? * ( ) [ ] { } | ^ $ \

However, MonetDB currently only matches a subset of them!

So, we have the following broken pattern:

SELECT '\a' LIKE '\\a'; => False, but should be True
SELECT '\a' LIKE '\\\\a'; => True, but should be False
SELECT 'xa' LIKE '_a{1}'; => True, but should be False
SELECT 'xa$b' LIKE '_a$b'; => False, but should be True

Some raw examples:

CREATE FUNCTION sql2pcre(pat TEXT, esc TEXT) RETURNS TEXT EXTERNAL NAME pcre.sql2pcre;

  OK: SELECT sql2pcre('a', '\\');   => NULL
  OK: SELECT sql2pcre('_', '\\');   => '^.$'
  OK: SELECT sql2pcre('%', '\\');   => '^.*$'

WRONG: SELECT sql2pcre('??', '?'); => '^.?$' (correct: '^.?$')
WRONG: SELECT sql2pcre('{', '\'); => '^.{$' (correct: '^.{$')
WRONG: SELECT sql2pcre('%^%', '\'); => '^.^.$' (correct: '^.^.$')

Both strchr() in sql2pcre should rely on the same exception list, and this list should be:

 ".+?*()[]{}|^$\\"

Comment 21241

Date: 2015-08-31 16:42:59 +0200
From: MonetDB Mercurial Repository <>

Changeset adf405e7edb0 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=adf405e7edb0

Changeset description:

Properly escape all PCRE special characters.
This fixes bug #3800.

Comment 21242

Date: 2015-08-31 16:43:51 +0200
From: @sjoerdmullender

Good catch, and thanks for the fix.

Comment 21244

Date: 2015-09-01 11:15:48 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

Wonderful. Thanks for your reactivity. Much appreciated.

Comment 21437

Date: 2015-11-03 10:18:13 +0100
From: @sjoerdmullender

Jul2015 SP1 has been released.