Description:
Using \ in LIKE to escape \ itself and the wildcard % causes odd results when matching against fields containing \ and %.
'\\%' acts as if it was '\\\%'
'\\\%' acts as if it was '\\%'
'\\\\%' acts as if it was '\\%'
'\\\\\%' acts as if it was '\\\%'
The latter two look like double-unescaping but I can find no rhyme nor reason in the first two at all :-)
How to repeat:
-- in any old database
drop table if exists foo;
create table foo
(
id varchar(255)
);
insert into foo values('bar%');
insert into foo values('bar%1');
insert into foo values('bart');
insert into foo values('bar\\t');
insert into foo values('bar\\%');
-- Wildcard % - fetches all
select id from foo where id like 'bar%';
-- OK
-- Straight escaped % - just bar%
select id from foo where id like 'bar\%';
-- OK
-- Escaped \ then wildcard % - should fetch bar\t and bar\%
select id from foo where id like 'bar\\%';
-- ERROR: Only fetches bar\%
-- Escaped \ then escaped % - should fetch bar\%
select id from foo where id like 'bar\\\%';
-- ERROR: Fetches both bar\t and bar\% as one before should have done
-- Two escaped \ then wildcard % - shouldn't fetch anything
select id from foo where id like 'bar\\\\%';
-- ERROR: Fetches both bar\t and bar\%
-- Two escaped \ then escaped % - shouldn't fetch anything
select id from foo where id like 'bar\\\\\%';
-- ERROR: Fetches bar\%
Description: Using \ in LIKE to escape \ itself and the wildcard % causes odd results when matching against fields containing \ and %. '\\%' acts as if it was '\\\%' '\\\%' acts as if it was '\\%' '\\\\%' acts as if it was '\\%' '\\\\\%' acts as if it was '\\\%' The latter two look like double-unescaping but I can find no rhyme nor reason in the first two at all :-) How to repeat: -- in any old database drop table if exists foo; create table foo ( id varchar(255) ); insert into foo values('bar%'); insert into foo values('bar%1'); insert into foo values('bart'); insert into foo values('bar\\t'); insert into foo values('bar\\%'); -- Wildcard % - fetches all select id from foo where id like 'bar%'; -- OK -- Straight escaped % - just bar% select id from foo where id like 'bar\%'; -- OK -- Escaped \ then wildcard % - should fetch bar\t and bar\% select id from foo where id like 'bar\\%'; -- ERROR: Only fetches bar\% -- Escaped \ then escaped % - should fetch bar\% select id from foo where id like 'bar\\\%'; -- ERROR: Fetches both bar\t and bar\% as one before should have done -- Two escaped \ then wildcard % - shouldn't fetch anything select id from foo where id like 'bar\\\\%'; -- ERROR: Fetches both bar\t and bar\% -- Two escaped \ then escaped % - shouldn't fetch anything select id from foo where id like 'bar\\\\\%'; -- ERROR: Fetches bar\%