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

Yet another LIKE operator issue #3822

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

Yet another LIKE operator issue #3822

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

Comments

@monetdb-team
Copy link

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.

@monetdb-team monetdb-team added bug Something isn't working normal 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 normal SQL
Projects
None yet
Development

No branches or pull requests

2 participants