Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

LIKE is broken for many patterns #3800

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

LIKE is broken for many patterns #3800

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
bug Something isn't working major SQL

Comments

@monetdb-team
Copy link

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.

@monetdb-team monetdb-team added bug Something isn't working major SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working major SQL
Projects
None yet
Development

No branches or pull requests

2 participants