Date: 2013-08-21 15:22:01 +0200
From: @swingbit
To: SQL devs <>
Version: 11.15.3 (Feb2013-SP1)
CC: @mlkersten, @njnes

Last updated: 2013-09-27 13:47:15 +0200

Comment 19043

Date: 2013-08-21 15:22:01 +0200
From: @swingbit

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/29.0.1547.57 Safari/537.36
Build Identifier:

create table x (a CLOB);
insert into x VALUES ('aapX');
insert into x VALUES ('abc|aapX');
insert into x VALUES ('abc|aap_beer');

select * from x where a like 'abc%'; -- 2 results, as expected
select * from x where a like 'abc|aap%'; -- 2 results, as expected
select * from x where a like 'abc|aap_%'; -- 3 results, expected 1 result

Reproducible: Always

MonetDB 5 server v11.15.12 (64-bit, 64-bit oids)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 15.6GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.32 2012-11-30 (compiled with 8.31)
openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with OpenSSL 1.0.1e-fips 11 Feb 2013)
libxml2: 2.9.1 (compiled with 2.9.1)
Compiled by: roberto@photon.spinque.com (x86_64-unknown-linux-gnu)
Compilation: gcc -g -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code
Linking : /usr/bin/ld -m elf_x86_64

Comment 19044

Date: 2013-08-21 15:44:53 +0200
From: @mlkersten

Dont forget the '_' is a placeholder for a single arbitrary character

Comment 19045

Date: 2013-08-21 15:48:38 +0200
From: @swingbit

Oops. Sorry, didn't know that!

Comment 19046

Date: 2013-08-21 16:11:10 +0200
From: @swingbit

Hold on.

If the way to escape the '_' is as below (which I couldn't find in the documentation, but only in bits of sql code on the web - this should be a bug by itself), then I still don't get the expected result:

create table x (a CLOB);
insert into x VALUES ('aapX');
insert into x VALUES ('abc|aapX');
insert into x VALUES ('abc|aap_beer');

select * from x where a like 'abc|aap_%' escape '_';
+--------------+
| a |
+==============+
| abc|aapX |
| abc|aap_beer |
+--------------+
2 tuples (0.456ms)

The first tuple is wrong.

Comment 19050

Date: 2013-08-22 11:00:47 +0200
From: @njnes

I think the problem is the handling of the | (or) in this case. I'll have to check sql standard if the | should really behave as such.

Comment 19052

Date: 2013-08-23 09:31:57 +0200
From: @njnes

fixed, ie the bug was in the handling of '|'. Also the added a test which shows the use of the escape character.