MySQL Bugs: #68901: Search doesn't work using LIKE %% on a simple digit backslash

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

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

Thank you for the bug report. Could you please provide the output of:

SHOW COLLATION LIKE "utf8_unicode%";

Thanks.

SHOW COLLATION LIKE 'utf8_unicode'

Results

collation : utf8_unicode_ci
Charset : utf8
Id : 192
Default :
Compiled : Yes
Sortlen : 8

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.

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'

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.

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)

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)

Thanks for your answer

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

probably duplicate/subissue of bug #84118