Yet another LIKE operator issue

[monetdb-team]

Date: 2015-10-13 15:55:00 +0200
From: Guillaume Savary <<guillaume.savary>>
To: SQL devs <>
Version: 11.21.5 (Jul2015)
CC: @njnes

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

Comment 21326

Date: 2015-10-13 15:55:00 +0200
From: Guillaume Savary <<guillaume.savary>>

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

LIKE operator used with '\' should match the '' char in any cases.

This works fine:

sql> select '' like '\';
+-------------------+
| like_single_value |
+===================+
| true |
+-------------------+

But matching selected objects does not work as expected:

sql> select * from (select '' as foo) AS t0 WHERE foo LIKE '\';
+-----+
| foo |
+=====+
+-----+

We discovered this bug by querying the catalog to find table like "\_%" which did not return any value despite we have plenty of tables which should match.

Reproducible: Always

Steps to Reproduce:

1. Just launch the SQL query described

Not a minor bug because it's not that easy to find a workaround and sometimes it's impossible in a single query.

Maybe there is a relation with the bug we reports some week ago about the LIKE operator and was fixed recently: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3811

Comment 21329

Date: 2015-10-14 11:23:30 +0200
From: @sjoerdmullender

I'm not sure this is a bug. What you can do to get the result you want is to add an ESCAPE clause:

sql> select * from (select '' as foo) AS t0 WHERE foo LIKE '\' ESCAPE '\';
+------+
| foo |
+======+
| _ |
+------+

Comment 21340

Date: 2015-10-14 19:39:19 +0200
From: @njnes

The like expression by default should not use \ as the escape character (the default is empty, ie no escaping). The first like is wrong as it should match \ followed by any second character.

The correct usage should be

select * from (select '' as foo) AS t0 WHERE foo LIKE '\' escape '\';

Comment 21342

Date: 2015-10-16 09:15:23 +0200
From: MonetDB Mercurial Repository <>

Changeset 4aee2a208fac 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=4aee2a208fac

Changeset description:

Fix LIKE operator to default to empty ESCAPE.
This fixes bug #3822 (although perhaps not in the way the reported had
originally envisioned).

Comment 21438

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

Jul2015 SP1 has been released.