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: | |
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 |
[26 Jan 2011 23:32]
Erica Moss
[27 Jan 2011 12:27]
Peter Laursen
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!
[27 Jan 2011 16:39]
Peter Laursen
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?).
[27 Jan 2011 19:31]
Peter Laursen
-- 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.
[2 Mar 2011 0:00]
Bugs System
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".
[29 Aug 2011 3:37]
Ai Ling Tan
having this issue on the 5.5.15 MySQL server in windows. Will there be any fixes ?
[13 Nov 2011 17:52]
Valeriy Kravchuk
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.