| Bug #60786 | For update:Trailing white space not ignored with index, is ignored without index | ||
|---|---|---|---|
| Submitted: | 7 Apr 2011 0:18 | Modified: | 28 Nov 2019 21:59 |
| Reporter: | Ariel S | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 5.1.49/5.5 | OS: | Linux (Debian Squeeze) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | index update whitespace | ||
[7 Apr 2011 0:42]
MySQL Verification Team
Thank you for the bug report.
C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.57-Win X64-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.1 >use test
Database changed
mysql 5.1 >CREATE TABLE `test_trailing` (
-> `last` varchar(20) NOT NULL,
-> `ord` tinyint(2) unsigned zerofill NOT NULL,
-> `deleted` timestamp NULL DEFAULT NULL,
-> UNIQUE KEY `last` (`last`,`ord`),
-> KEY `deleted` (`deleted`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.75 sec)
mysql 5.1 >INSERT INTO `test_trailing` VALUES ('RUPERD ',01,NULL);
Query OK, 1 row affected (0.45 sec)
mysql 5.1 >INSERT INTO `test_trailing` VALUES ('RUPERD ',02,NULL);
Query OK, 1 row affected (0.00 sec)
mysql 5.1 >INSERT INTO `test_trailing` VALUES ('RUPERD',03,NULL);
Query OK, 1 row affected (0.00 sec)
mysql 5.1 >INSERT INTO `test_trailing` VALUES ('RUPERD ',04,NULL);
Query OK, 1 row affected (0.00 sec)
mysql 5.1 >INSERT INTO `test_trailing` VALUES ('RUPERD',05,NULL);
Query OK, 1 row affected (0.00 sec)
mysql 5.1 >SELECT * FROM test_trailing WHERE last = 'RUPERD';
+---------+-----+---------+
| last | ord | deleted |
+---------+-----+---------+
| RUPERD | 01 | NULL |
| RUPERD | 02 | NULL |
| RUPERD | 03 | NULL |
| RUPERD | 04 | NULL |
| RUPERD | 05 | NULL |
+---------+-----+---------+
5 rows in set (0.07 sec)
mysql 5.1 >SELECT * FROM test_trailing FORCE INDEX (last) WHERE last = 'RUPERD';
+---------+-----+---------+
| last | ord | deleted |
+---------+-----+---------+
| RUPERD | 01 | NULL |
| RUPERD | 02 | NULL |
| RUPERD | 03 | NULL |
| RUPERD | 04 | NULL |
| RUPERD | 05 | NULL |
+---------+-----+---------+
5 rows in set (0.00 sec)
mysql 5.1 >UPDATE test_trailing SET deleted = NOW() WHERE last = 'RUPERD';
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql 5.1 >UPDATE test_trailing FORCE INDEX (last) SET deleted = NOW() WHERE last = 'RUPERD';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql 5.1 >exit
Bye
C:\DBS>55
C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.12 Source distribution
Copyright (c) 2000, 2010, 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 5.5 >use test
Database changed
mysql 5.5 >CREATE TABLE `test_trailing` (
-> `last` varchar(20) NOT NULL,
-> `ord` tinyint(2) unsigned zerofill NOT NULL,
-> `deleted` timestamp NULL DEFAULT NULL,
-> UNIQUE KEY `last` (`last`,`ord`),
-> KEY `deleted` (`deleted`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.93 sec)
mysql 5.5 >
mysql 5.5 >INSERT INTO `test_trailing` VALUES ('RUPERD ',01,NULL);
Query OK, 1 row affected (0.07 sec)
mysql 5.5 >INSERT INTO `test_trailing` VALUES ('RUPERD ',02,NULL);
Query OK, 1 row affected (0.00 sec)
mysql 5.5 >INSERT INTO `test_trailing` VALUES ('RUPERD',03,NULL);
Query OK, 1 row affected (0.00 sec)
mysql 5.5 >INSERT INTO `test_trailing` VALUES ('RUPERD ',04,NULL);
Query OK, 1 row affected (0.00 sec)
mysql 5.5 >INSERT INTO `test_trailing` VALUES ('RUPERD',05,NULL);
Query OK, 1 row affected (0.00 sec)
mysql 5.5 >SELECT * FROM test_trailing WHERE last = 'RUPERD';
+---------+-----+---------+
| last | ord | deleted |
+---------+-----+---------+
| RUPERD | 01 | NULL |
| RUPERD | 02 | NULL |
| RUPERD | 03 | NULL |
| RUPERD | 04 | NULL |
| RUPERD | 05 | NULL |
+---------+-----+---------+
5 rows in set (0.10 sec)
mysql 5.5 >SELECT * FROM test_trailing FORCE INDEX (last) WHERE last = 'RUPERD';
+---------+-----+---------+
| last | ord | deleted |
+---------+-----+---------+
| RUPERD | 01 | NULL |
| RUPERD | 02 | NULL |
| RUPERD | 03 | NULL |
| RUPERD | 04 | NULL |
| RUPERD | 05 | NULL |
+---------+-----+---------+
5 rows in set (0.00 sec)
mysql 5.5 >UPDATE test_trailing SET deleted = NOW() WHERE last = 'RUPERD';
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql 5.5 >UPDATE test_trailing FORCE INDEX (last) SET deleted = NOW() WHERE last = 'RUPERD';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql 5.5 >exit
Bye
[7 Apr 2011 2:05]
MySQL Verification Team
Just a side note, the testcase requires a sleep between the two updates otherwise it will not work as expected. Then, I'm surprised to see this bug at all! http://dev.mysql.com/doc/refman/5.5/en/index-hints.html says "Index hints are accepted but ignored for UPDATE statements."
[7 Apr 2011 5:11]
Ariel S
I encountered the bug without using index hints. When I ran a query that happened to use the index it occurred, and I couldn't understand why it didn't occur after deleting some rows. I finally figured out it was related to the index. Putting in the hint is just a way to test it, it's not actually necessary in order to trigger the bug.
[7 Apr 2011 8:13]
Peter Laursen
Another side-remark: Is this really correct behavior: SELECT 'RUPERD' = 'RUPERD '; -- returns 1 If so it surprises me! They are two different strings.
[7 Apr 2011 8:27]
Peter Laursen
In my understanding this leads to completely weird and inconsistent behavior when comparing using "=" and "LIKE" operators: SELECT 'a' = 'a'; -- 1 SELECT 'a' LIKE 'a'; -- 1 SELECT 'a' = 'a '; -- 1 SELECT 'a' LIKE 'a '; -- 0 New report?
[20 Nov 2015 8:59]
Roy Lyseng
This behavior: SELECT 'RUPERD' = 'RUPERD '; -- returns 1 is correct: Quoting from manual, section "The CHAR and VARCHAR Types" "All MySQL collations are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces. “Comparison” in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant. " PAD SPACE is a property defined by the SQL standard. Notice also that the equality operator '=' and LIKE are two different operators: The second operand to LIKE is a pattern string where every character is significant. Section "String Comparison Functions" in the manual explains this: "Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:" and "In particular, trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator"
[28 Nov 2019 21:59]
Roy Lyseng
Posted by developer: Fixed in 5.7.29. Notice that with the NOPAD collations introduced in 8.0, only two rows will be selected and updated.
Description: I have a column with some trailing white space. If, when I UPDATE the column, the database uses the index, then trailing white space is NOT ignored. But if the index isn't used, then trailing whitespace IS ignored. But for a SELECT, white space is ignored either way. How to repeat: CREATE TABLE `test_trailing` ( `last` varchar(20) NOT NULL, `ord` tinyint(2) unsigned zerofill NOT NULL, `deleted` timestamp NULL DEFAULT NULL, UNIQUE KEY `last` (`last`,`ord`), KEY `deleted` (`deleted`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `test_trailing` VALUES ('RUPERD ',01,NULL); INSERT INTO `test_trailing` VALUES ('RUPERD ',02,NULL); INSERT INTO `test_trailing` VALUES ('RUPERD',03,NULL); INSERT INTO `test_trailing` VALUES ('RUPERD ',04,NULL); INSERT INTO `test_trailing` VALUES ('RUPERD',05,NULL); The test: > SELECT * FROM test_trailing WHERE last = 'RUPERD'; +---------+-----+---------+ | last | ord | deleted | +---------+-----+---------+ | RUPERD | 01 | NULL | | RUPERD | 02 | NULL | | RUPERD | 03 | NULL | | RUPERD | 04 | NULL | | RUPERD | 05 | NULL | +---------+-----+---------+ 5 rows in set (0.00 sec) > SELECT * FROM test_trailing FORCE INDEX (last) WHERE last = 'RUPERD'; +---------+-----+---------+ | last | ord | deleted | +---------+-----+---------+ | RUPERD | 01 | NULL | | RUPERD | 02 | NULL | | RUPERD | 03 | NULL | | RUPERD | 04 | NULL | | RUPERD | 05 | NULL | +---------+-----+---------+ 5 rows in set (0.00 sec) > UPDATE test_trailing SET deleted = NOW() WHERE last = 'RUPERD'; Query OK, 5 rows affected (0.00 sec) Rows matched: 5 Changed: 5 Warnings: 0 > UPDATE test_trailing FORCE INDEX (last) SET deleted = NOW() WHERE last = 'RUPERD'; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 This last query is incorrect (only 3 rows were updated)! If you use the index in an update, trailing whitespace is not ignored. Suggested fix: I don't particularly care which way it chooses (ignore whitespace or not), but select and update need to match, and update certainly needs to do the same thing with an index and without.