| Bug #11853 | DELETE ... NOT LIKE doesn't seem to work properly when index present | ||
|---|---|---|---|
| Submitted: | 11 Jul 2005 0:52 | Modified: | 18 Jul 2005 19:13 |
| Reporter: | Paul Keenan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.7-beta | OS: | Linux (Linux) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[11 Jul 2005 4:06]
Aleksey Kishkin
Well. I would say it also presents on linux and on innodb handler. So I changed category to 'MySQL Server'
[13 Jul 2005 15:02]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/27007
[13 Jul 2005 15:04]
Evgeny Potemkin
Function get_func_mm_tree() was ingoring NOT when building SEL_TREE for NOT LIKE , which results in creation SEL_TREE same as for LIKE predicate.
[13 Jul 2005 21:05]
Sergey Petrunya
The problem affects not only "x NOT LIKE y" (which is internally converted to "NOT (x LIKE y"): mysql> select * from tn where NOT (a <=> 4); +------+ | a | +------+ | NULL | | 1 | | 2 | | 3 | +------+ 4 rows in set (2.56 sec) mysql> delete from tn where NOT (a <=> 4); Query OK, 0 rows affected (5.63 sec) mysql> select * from tn where NOT (a <=> 4); +------+ | a | +------+ | NULL | | 1 | | 2 | | 3 | +------+ 4 rows in set (3.25 sec)
[13 Jul 2005 21:12]
Sergey Petrunya
The bug was created by a new 5.0 optimization.
[16 Jul 2005 15:19]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/27218
[16 Jul 2005 15:52]
Igor Babaev
The bug was due to some incorrect code in the range optimization for NOT IN and NOT BETWEEN added in 5.0.
ChangeSet
1.1949 05/07/16 08:19:20 igor@rurik.mysql.com +3 -0
opt_range.cc:
Fixed bug #11853.
Corrected the code of the range optimization for
NOT IN and NOT BETWEEN.
range.test, range.result:
Fixed bug #11853.
The fix will appear in 5.0.10
[18 Jul 2005 19:13]
Paul DuBois
Noted in 5.0.10 changelog.
Description: The construct DELETE ... NOT LIKE ... doesn't seem to work when there's an index on the field. I don't know of a workaround. How to repeat: set up a test table (snipped from mysqldump) : -------------------------------------------------------------------- DROP TABLE IF EXISTS `people`; CREATE TABLE `people` ( `name` varchar(5) NOT NULL, KEY `name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `people` VALUES ('Bob'); -------------------------------------------------------------------- and the following commands illustrate the issue : -------------------------------------------------------------------- mysql> select name from people where name not like 'A%'; +------+ | name | +------+ | Bob | +------+ 1 row in set (0.00 sec) mysql> delete from people where name not like 'A%'; Query OK, 0 rows affected (0.00 sec) mysql> alter table people drop key name; Query OK, 1 row affected (0.10 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> delete from people where name not like 'A%'; Query OK, 1 row affected (0.00 sec) -------------------------------------------------------------------- As you can hopefully see, the select statement verifies that the where clause matches the row - since "Bob" does not start with "A". The first delete statement should have deleted this row . After deleting the index, the row is deleted by the same statement which failed to previously. If the NOT LIKE is replaced by a LIKE, the problem is not manifested : -------------------------------------------------------------------- <set up table as before> mysql> delete from people where name not like 'A%'; Query OK, 0 rows affected (0.00 sec) mysql> delete from people where name like 'B%'; Query OK, 1 row affected (0.00 sec) -------------------------------------------------------------------- This behavior was exhibited on a brand new install of 5.0.7-beta. Hope that helps. Cheers, Paul Suggested fix: Out of my league, I'm afraid.