MySQL Bugs: #59759: pattern matching in utf8 isn't working right when sql_mode=NO_BACKSLASH

Bug #59759	pattern matching in utf8 isn't working right when sql_mode=NO_BACKSLASH_ESCAPES
Submitted:	26 Jan 2011 23:32	Modified:	13 Nov 2011 17:52
Reporter:	Erica Moss	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.5.9, 5.5.15, 5.1.60	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	NO_BACKSLASH_ESCAPES

Description:
when the server character set is utf8 and sql_mode=NO_BACKSLASH_ESCAPES, it appears that a LIKE clause is not able to match strings that contain "\\"

If however the server character set is latin1 and sql_mode=NO_BACKSLASH_ESCAPES, LIKE will match the same strings.

How to repeat:
SET  character_set_server=utf8;
SET  character_set_client=latin1;
USE test;
SET sql_mode=NO_BACKSLASH_ESCAPES;
DROP TABLE IF EXISTS likeTest;
CREATE TABLE likeTest (userName varchar(32));
INSERT INTO likeTest VALUES ("c:\\jetson");
SELECT * FROM likeTest WHERE userName LIKE '%c:\\j%';

Returns... 
Empty set (0.00 sec)

SET  character_set_server=latin1;
SET  character_set_client=latin1;
USE test;
SET sql_mode=NO_BACKSLASH_ESCAPES;
DROP TABLE IF EXISTS likeTest;
CREATE TABLE likeTest (userName varchar(32));
INSERT INTO likeTest VALUES ("c:\\jetson");
SELECT * FROM likeTest WHERE userName LIKE '%c:\\j%';

returns...
+------------+
| userName   |
+------------+
| c:\\jetson |
+------------+
1 row in set (0.00 sec)

I posted this: http://bugs.mysql.com/bug.php?id=59441 a few weeks ago.

What Erica does here is to "SET (*SESSION*) 'character_set_server'" variable. 

"SET 'character_set_server' = utf8"

is same as "SET @@session.character_set_server = utf8"

and not "SET @@global.character_set_server = utf8"

As I wrote in my report I do not see that 'character_set_server' makes sense at all as a SESSION variable. But I am surprised that it has an effect at all to set it!

In my understanding it fails with latin1 rather than utf8 - See 

SET  character_set_server=utf8;
SET  character_set_client=latin1;
USE test;
SET SQL_MODE=NO_BACKSLASH_ESCAPES;
DROP TABLE IF EXISTS likeTest;
CREATE TABLE likeTest (userName VARCHAR(32));
INSERT INTO likeTest VALUES ("c:\\jetson");
SELECT * FROM likeTest;
SELECT * FROM likeTest WHERE userName LIKE '%c:\\\\j%';
/*
userName  
----------
c:\\jetson
*/

SET  character_set_server=latin1;
SET  character_set_client=latin1;
USE test;
SET SQL_MODE=NO_BACKSLASH_ESCAPES;
DROP TABLE IF EXISTS likeTest;
CREATE TABLE likeTest (userName VARCHAR(32));
INSERT INTO likeTest VALUES ("c:\\jetson");
SELECT * FROM likeTest; -- same as above
SELECT * FROM likeTest WHERE userName LIKE '%c:\\\\j%'; -- empty set

The points are 
1) in this mode you insert "c:\\jetson" and not "c:\jetson" with the INSERT statement. No escape during INSERT either.
2) when matching with LIKE you will have to double the number of backslashes in the string in the LIKE clause. 

(I have utf8 as default character set for the server in my settings - changing SESSION value causes this?).

-- with *ANY* (SESSION) client and server charset:
SET SQL_MODE = 'NO_BACKSLASH_ESCAPES';
SELECT "c:\\jetson" LIKE '%c:\\\\j%'; -- 1
SELECT "c:\\jetson" LIKE '%c:\\j%'; -- 0
SELECT "c:\jetson" LIKE '%c:\\j%'; -- 1

So the issue discovered (inconsistent result with different SESSION server charsets - whatever that means??) by Erica seems only to happen when working with *stored data* - not when data are given as strings in the SELECT statement itself.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

having this issue on the 5.5.15 MySQL server in windows. Will there be any fixes ?

This happens with 5.1.60 on Mac also:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.60 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET  character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> SET  character_set_client=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> USE test;
Database changed
mysql> SET sql_mode=NO_BACKSLASH_ESCAPES;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS likeTest;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE likeTest (userName varchar(32));
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO likeTest VALUES ("c:\\jetson");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM likeTest WHERE userName LIKE '%c:\\j%';
Empty set (0.00 sec)

mysql> 
mysql> SELECT * FROM likeTest;
+------------+
| userName   |
+------------+
| c:\\jetson |
+------------+
1 row in set (0.00 sec)

Does not look like a recent regression in 5.5.