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:
None 
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 0:52] Paul Keenan
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.
[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.