| Bug #45469 | DELETE doesn't see specified row on InnoDB, while SELECT does | ||
|---|---|---|---|
| Submitted: | 12 Jun 2009 12:53 | Modified: | 10 Feb 2018 17:37 |
| Reporter: | Vojtech Kurka | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S2 (Serious) |
| Version: | 5.1.35, 5.0.77 | OS: | Linux (CentOS 5.3 64bit) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | collation, delete, innodb, primary key, utf8_bin, utf8_czech_ci | ||
[12 Jun 2009 12:59]
Vojtech Kurka
SHOW VARIABLES output (was run immediately after the testcase)
Attachment: output.txt (text/plain), 38.60 KiB.
[13 Jun 2009 11:07]
Peter Laursen
I tried with a Danish 'ø' .. DELETE FROM `test` WHERE `value` = 'ø' COLLATE utf8_danish_ci AND `value` != 'ø' COLLATE utf8_bin; -- (0 row(s) affected) SELECT * FROM `test` WHERE `value` = 'ø' COLLATE utf8_danish_ci AND `value` != 'ø' COLLATE utf8_bin; -- (0 row(s) returned) So no problem here. Also I find no issue with 'ö' with collations for languages where this character exists.
[13 Jun 2009 11:14]
Peter Laursen
but for me the original Czech case is also reproducable with MyISAM
[13 Jun 2009 11:25]
Vojtech Kurka
I'm able now to reproduce it on MyISAM and MEMORY, sorry for the misinformation in the original post. It's definitely not storage engine-dependent.
[13 Jul 2009 9:21]
Susanne Ebrecht
Verified as described
[24 Jun 2010 1:05]
Vojtech Kurka
Any updates?
[30 Jun 2010 6:30]
Alexander Barkov
The same problem can be demonstrated with a simpler test case:
mysql> select version(); SET NAMES utf8; DROP TABLE IF EXISTS `test`; CREATE TABLE test (value VARCHAR(70) COLLATE utf8_czech_ci NOT NULL, PRIMARY KEY(value)); INSERT INTO test VALUES ('o'); DELETE FROM test WHERE value ='ó' COLLATE utf8_czech_ci AND value != 'ó' COLLATE utf8_bin; SELECT * FROM test WHERE value='ó' COLLATE utf8_czech_ci AND value !='ó' COLLATE utf8_bin;
+-----------+
| version() |
+-----------+
| 5.0.27 |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+-------+
| value |
+-------+
| o |
+-------+
1 row in set (0.00 sec)
[30 Jun 2010 6:31]
Alexander Barkov
Works correctly in 5.5.4:
mysql> select version(); SET NAMES utf8; DROP TABLE IF EXISTS `test`; CREATE TABLE test (value VARCHAR(70) COLLATE utf8_czech_ci NOT NULL, PRIMARY KEY(value)); INSERT INTO test VALUES ('o'); DELETE FROM test WHERE value ='ó' COLLATE utf8_czech_ci AND value != 'ó' COLLATE utf8_bin; SELECT * FROM test WHERE value='ó' COLLATE utf8_czech_ci AND value !='ó' COLLATE utf8_bin;
+----------------+
| version() |
+----------------+
| 5.5.4-m3-debug |
+----------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Empty set (0.00 sec)
[30 Jun 2010 6:45]
Alexander Barkov
Seems to be related to: http://bugs.mysql.com/bug.php?id=48447 which was fixed by this patch in 6.0 (and later backported to 5.5): http://lists.mysql.com/commits/89682 === modified file 'sql/opt_range.cc' --- a/sql/opt_range.cc 2009-10-29 17:04:23 +0000 +++ b/sql/opt_range.cc 2009-11-05 14:42:03 +0000 @@ -5882,7 +5882,8 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND value->result_type() == STRING_RESULT && key_part->image_type == Field::itRAW && ((Field_str*)field)->charset() != conf_func->compare_collation() && - !(conf_func->compare_collation()->state & MY_CS_BINSORT)) + !(conf_func->compare_collation()->state & MY_CS_BINSORT && + (type == Item_func::EQUAL_FUNC || type == Item_func::EQ_FUNC))) goto end;
[10 Feb 2018 17:37]
Roy Lyseng
Posted by developer: Closing, confirmed fixed in 5.5.15
Description: When I SELECT from the innodb table, select returns one row. When I run DELETE with identical WHERE clause, the DELETE doesn't see the row and returns 0 rows affected. The WHERE consists of two conditions, each with different collation. Only InnoDB ENGINE is affected (MyISAM is OK). After dropping the PK, delete works fine. Tested with clients: SQLyog 8.1, standard mysql CLI, mysqli(php) Tested on CPUs AMD(5.1.35) and Intel(5.0.77) Output from mysql cli client: -------------- SET NAMES utf8 -------------- Query OK, 0 rows affected (0.00 sec) -------------- DROP TABLE IF EXISTS `test` -------------- Query OK, 0 rows affected (1.41 sec) -------------- CREATE TABLE `test` ( `id` MEDIUMINT(8) UNSIGNED NOT NULL, `value` VARCHAR(70) COLLATE utf8_czech_ci NOT NULL, PRIMARY KEY (`id`,`value`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci -------------- Query OK, 0 rows affected (0.00 sec) -------------- INSERT INTO `test` (`id`, `value`) VALUES('2533','o') -------------- Query OK, 1 row affected (0.00 sec) -------------- DELETE FROM `test` WHERE `value` = 'ó' COLLATE utf8_czech_ci AND `value` != 'ó' COLLATE utf8_bin -------------- Query OK, 0 rows affected (0.00 sec) -------------- SELECT * FROM `test` WHERE `value` = 'ó' COLLATE utf8_czech_ci AND `value` != 'ó' COLLATE utf8_bin -------------- +------+-------+ | id | value | +------+-------+ | 2533 | o | +------+-------+ 1 row in set (0.00 sec) Bye How to repeat: /* UTF-8 charset is used */ SET NAMES utf8; DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `id` MEDIUMINT(8) UNSIGNED NOT NULL, `value` VARCHAR(70) COLLATE utf8_czech_ci NOT NULL, PRIMARY KEY (`id`,`value`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci; INSERT INTO `test` (`id`, `value`) VALUES('2533','o'); DELETE FROM `test` WHERE `value` = 'ó' COLLATE utf8_czech_ci AND `value` != 'ó' COLLATE utf8_bin; SELECT * FROM `test` WHERE `value` = 'ó' COLLATE utf8_czech_ci AND `value` != 'ó' COLLATE utf8_bin;