Bug #68901 Search doesn't work using LIKE %% on a simple digit backslash
Submitted: 9 Apr 2013 10:26 Modified: 12 Apr 2013 8:52
Reporter: sebastien Roux Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.5.30/5.6 OS:Linux (Debian 2.6.32-5-686)
Assigned to: CPU Architecture:Any
Tags: like, protected value, search

[9 Apr 2013 10:26] sebastien Roux
Description:
I try to replay a part of slq from page
http://dev.mysql.com/doc/refman/5.6/en/string-comparison-functions.html
mysql> SELECT filename, filename LIKE '%\\' FROM t1;
+--------------+---------------------+
| filename     | filename LIKE '%\\' |
+--------------+---------------------+
| C:\          |                   1 |
+--------------+---------------------+

But MySQL return me a zero

How to repeat:
CREATE TABLE `t1` ( `filename` varchar(255) COLLATE utf8_unicode NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `t1` (`filename`) VALUES ('C:\\');

SELECT filename, filename LIKE '%\\' FROM t1;
return no row
[9 Apr 2013 11:22] MySQL Verification Team
Thank you for the bug report. Could you please provide the output of:

SHOW COLLATION LIKE "utf8_unicode%";

Thanks.
[9 Apr 2013 12:22] sebastien Roux
SHOW COLLATION LIKE 'utf8_unicode'

Results

collation : utf8_unicode_ci
Charset : utf8
Id : 192
Default :
Compiled : Yes
Sortlen : 8
[10 Apr 2013 2:09] MySQL Verification Team
Could you please print here the output of your test case:

CREATE TABLE `t1` ( `filename` varchar(255) COLLATE utf8_unicode NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `t1` (`filename`) VALUES ('C:\\');

SELECT filename, filename LIKE '%\\' FROM t1;

Notice: varchar(255) COLLATE utf8_unicode<<<<<

Thanks.
[10 Apr 2013 3:41] sebastien Roux
Sorry, i can't do that table creation because MySQL return an error

mysql> CREATE TABLE `t1` ( `filename` varchar(255) COLLATE utf8_unicode NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ERROR 1273 (HY000): Unknown collation: 'utf8_unicode'
[10 Apr 2013 11:43] MySQL Verification Team
Thank you for the feedback. Please change your create table from:

CREATE TABLE `t1` ( `filename` varchar(255) COLLATE utf8_unicode NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

to

CREATE TABLE `t1` ( `filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

and run your test case again. Thanks.
[10 Apr 2013 12:06] sebastien Roux
Same result for me
Here below the terminal sequence
------------------------------------------------------------------------
mysql> use zzzz;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE `t1` ( `filename` varchar(255) COLLATE utf8_unicode_ci NOT
    -> NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `t1` (`filename`) VALUES ('C:\\');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT filename, filename LIKE '%\\' FROM t1;
+----------+---------------------+
| filename | filename LIKE '%\\' |
+----------+---------------------+
| C:\      |                   0 |
+----------+---------------------+
1 row in set (0.00 sec)
[10 Apr 2013 13:46] MySQL Verification Team
Thank you for the feedback.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> USE test
Database changed
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `t1` ( `filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=ut
f8_unicode_ci;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO `t1` (`filename`) VALUES ('C:\\');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT filename, filename LIKE '%\\' FROM t1;
+----------+---------------------+
| filename | filename LIKE '%\\' |
+----------+---------------------+
| C:\      |                   0 |
+----------+---------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 ENGINE INNODB;
Query OK, 1 row affected (0.58 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT filename, filename LIKE '%\\' FROM t1;
+----------+---------------------+
| filename | filename LIKE '%\\' |
+----------+---------------------+
| C:\      |                   0 |
+----------+---------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE `t1` ( `filename` varchar(255) COLLATE latin1_swedish_ci NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLAT
E=latin1_swedish_ci;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO `t1` (`filename`) VALUES ('C:\\');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT filename, filename LIKE '%\\' FROM t1;
+----------+---------------------+
| filename | filename LIKE '%\\' |
+----------+---------------------+
| C:\      |                   1 |
+----------+---------------------+
1 row in set (0.00 sec)
[12 Apr 2013 8:52] sebastien Roux
Thanks for your answer

It is normal that utf8_unicode_ci have a different behaviour than latin1_swedish_ci ?