MySQL Bugs: #10489: Backslash in LIKE clause handled inconsistently

Bug #10489	Backslash in LIKE clause handled inconsistently
Submitted:	9 May 2005 18:53	Modified:	16 Aug 2005 14:43
Reporter:	Matthew Schultz	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.11	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Backslashes in LIKE clause is handled inconsistently.  I am aware of Bug #412 and I did read this in the docs:  

----------------------------------------------------------------
Note: Because MySQL uses the C escape syntax in strings (for example, '\n' to represent newline), you must double any '\' that you use in your LIKE  strings. For example, to search for '\n', specify it as '\\n'. To search for '\', specify it as '\\\\' (the backslashes are stripped once by the parser and another time when the pattern match is done, leaving a single backslash to be matched).
----------------------------------------------------------------

This does not work on 1 occurrence of a backslash, but it works on the second occurrence of a backslash after the first. 

How to repeat:
Create a Table and insert a record to look like this:

+------------+--------+
| categoryID | cTitle |
+------------+--------+
| home       | Home\ |
+------------+--------+

Now, according to your docs, you should be able to perform a query like this and it should return 1 row:

SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\\';

Result: Empty set (0.00 sec)

Now try: 

SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\\\\';

Result: 1 row in set (0.01 sec)

Even better is when you modify the data in cTitle to have two backslashes:

+------------+--------+
| categoryid | cTitle |
+------------+--------+
| home       | Home\\ |
+------------+--------+

Again according to the docs, this would be: 

SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\\\\\\';

Result: Empty set (0.00 sec)

Now try: 

SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\\\\\\\\';

Result: 1 row in set (0.00 sec)

There is a somewhat different pattern to single quotes except for one occurence which returns as expected:

+------------+--------+
| categoryid | cTitle |
+------------+--------+
| home       | Home\' |
+------------+--------+

SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\'';
1 row in set (0.00 sec)

But in this layout: 

+------------+----------+
| categoryid | cTitle   |
+------------+----------+
| home       | Home\'\' |
+------------+----------+

Again the same query returns fine: 

SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\'';
1 row in set (0.00 sec)

But to get the last two occurences according to the docs, it should be:

SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\'\\\'';
Empty set (0.00 sec)

Again like the backslash pattern, it requires two more backslashes than the first: 

SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\'\\\\\'';
1 row in set (0.00 sec)

Just to show the pattern further here is the same table with yet another backslash quote:

+------------+------------+
| categoryid | cTitle     |
+------------+------------+
| home       | Home\'\'\' |
+------------+------------+

SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\'\\\'\\\'';
Empty set (0.00 sec)

Just for another test, I also tried this:
SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\\\'\\\\\'\\\\\'';
Empty set (0.00 sec)

This works:
SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\'\\\\\'\\\\\'';
1 row in set (0.00 sec)

Suggested fix:
This is what is going when doing the search:

1 backslash search => 6 backslashes in LIKE
2 backslash search => 10 backslashes in LIKE
3 backslash search => 14 backslashes in LIKE
4 backslash search => 18 backslashes in LIKE

1 backslash quote search => 3 backslashes and quote
2 backslash quote search => 3 backslashes and quote, 5 backslashes and quote
3 backslash quote search => 
               3 backslashes and quote, 
               5 backslashes and quote,
               5 backslashes and quote
4 backslash quote search => 
               3 backslashes and quote, 
               5 backslashes and quote,
               5 backslashes and quote,
               5 backslashes and quote

So either your documentation is wrong or this is a bug and if this is a bug then can you make the number of backslashes needed consistent for escaping and not give the first occurence a different escaping rule than each subsequent occurence?

I was unable to repeat:

mysql> select * from categories;
+------------+--------+
| categoryID | cTitle |
+------------+--------+
| home       | Home\  |
+------------+--------+
1 row in set (0.04 sec)

mysql> SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\\';
+--------+
| cTitle |
+--------+
| Home\  |
+--------+
1 row in set (0.03 sec)

mysql> SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\\\\';
Empty set (0.00 sec)

Could you please provide the test case with your table schema ?

thanks in advance.

Sure here's the test: 

CREATE DATABASE test;
USE test;
CREATE TABLE categories (categoryID varchar(64) not null, primary key (categoryID), cTitle varchar(255) null);
INSERT INTO categories (categoryID, cTitle) VALUES ('home','Home\\');
SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\\';

result: Empty set (0.00 sec)

SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\\\\';

result: 1 row in set (0.00 sec)

Also, in case it might help, here are some specs on my system:

Linux 2.6.10-gentoo-r6 #2 SMP Tue Feb 8 16:18:16 PST 2005 i686 Intel(R) Pentium(R) III CPU family      1400MHz GenuineIntel GNU/Linux

I compiled the source code with these flags:

./configure --prefix=/usr/lib/mysql --enable-thread-safe-client --enable-local-infile --with-unix-socket-path=/var/run/mysqld/mysql.sock --with-mysqld-user=mysql --localstatedir=/home/mysql

As well as to actually be able to build it on the 2.6 kernel:

echo '/* LinuxThreads */' >> /usr/include/pthread.h

miguel@hegel:~/dbs/4.1$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.11-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop database test;
Query OK, 2 rows affected (0.12 sec)

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> USE test;
Database changed
mysql> CREATE TABLE categories (categoryID varchar(64) not null, primary key
    -> (categoryID), cTitle varchar(255) null);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO categories (categoryID, cTitle) VALUES ('home','Home\\');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\\';
+--------+
| cTitle |
+--------+
| Home\  |
+--------+
1 row in set (0.01 sec)

mysql>

Thank you for the feedback but still with your test case I was unable to repeat.

You must have some kind of special test environment because I just tested this on a different server running Slackware 9.0 on Kernel 2.4.22 running MySQL 4.1.9.  I ran through the same test on that server and I got:

select * from categories
+------------+--------+
| categoryID | cTitle |
+------------+--------+
| home       | Home\  |
+------------+--------+
1 row in set (0.00 sec)

SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\\';
Empty set (0.00 sec)

SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\\\\';
+--------+
| cTitle |
+--------+
| Home\  |
+--------+
1 row in set (0.00 sec)

The slackware server's mysql compile flags were:

./configure --prefix=/usr/lib/mysql --enable-thread-safe-client --enable-local-infile --with-unix-socket-path=/var/run/mysql/mysql.sock --with-mysqld-user=mysql --localstatedir=/home/mysql

Now I do notice that your version also says: 4.1.11-debug-log which is different than what I am using.  I compiled the tarball from source and my version on my gentoo server says 4.1.11-log and on the slackware server it is 4.1.9-log

http://dev.mysql.com/downloads/mysql/4.1.html
Tarball (tar.gz)		4.1.11	21.8M	Pick a mirror
MD5: 0b99001b07cad53f161ec629a6bb24ea | Signature

I tested it on Slackware 10.0 and compiled it with:

BUILD/compile-pentium-debug-max --prefix=/home/miguel/dbs/4.1

I didn't compile with this: BUILD/compile-pentium-debug-max

Both slackware and gentoo mysql compiles were the usual.  I did not use anything in the BUILD directory:

./configure --prefix=/usr/lib/mysql --enable-thread-safe-client
--enable-local-infile --with-unix-socket-path=/var/run/mysql/mysql.sock
--with-mysqld-user=mysql --localstatedir=/home/mysql

make

make install

Ok I will test it with your compile options and I will back with the
result.
Thanks for the feedback.

What is the status of this bug?  It still exists in 4.1.12 as well.

I was unable to repeat the issue also on Suse 9.3:

miguel@hegel:~/dbs/4.1> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.14-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> USE test;
Database changed
mysql> CREATE TABLE categories (categoryID varchar(64) not null, primary key
    -> (categoryID), cTitle varchar(255) null);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO categories (categoryID, cTitle) VALUES ('home','Home\\');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\\';
+--------+
| cTitle |
+--------+
| Home\  |
+--------+
1 row in set (0.01 sec)

mysql>

I'm not making this up.  I can still reproduce this bug on 4.1.13.  I did the same thing to install it as I previously did with 4.1.11.  I did _not_ use the builds directory to install mysql.  I did the configure, make, make install.  I can still reproduce this bug on Slackware and Gentoo both running 4.1.13.  I have a test file I run through to check it on every version increase:

CREATE DATABASE test;
USE test;
CREATE TABLE categories (categoryID varchar(64) not null, primary key (categoryID), cTitle varchar(255) null);
INSERT INTO categories (categoryID, cTitle) VALUES ('home','Home\\');
SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\\';

result: Empty set (0.00 sec)

SELECT cTitle FROM categories WHERE cTitle LIKE '%\\\\\\';

result: 1 row in set (0.00 sec)

Are you installing Mysql using configure, make, make install?  As I said before, this is how I install Mysql.

I don't how else to demonstrate this.  Perhaps I can give you access to one of my servers to demonstrate this bug?

I did some more testing and I found out that this bug has to do with the utf-8 character set on the database, table and columns.  It seems that as soon as I changed them all to latin1, the LIKE statement worked as it should.  Could this be related to bug #11754?