Elena Stepanova added a comment - 2016-06-15 12:03 Thanks for the report and the test case. The problem was initially introduced in MariaDB 5.5 by a merge of MySQL 5.5.35. It is still present in MySQL 5.5, but it's been fixed (or never existed) in MySQL 5.6+. However, MariaDB has it in all active versions 5.5-10.2. The degenerate condition "1 AND" in WHERE is not required to get the wrong result, which makes it more generic and hence important problem: MariaDB [test]> DROP TABLE IF EXISTS `test`; Query OK, 0 rows affected (0.37 sec)   MariaDB [test]> CREATE TABLE `test` ( -> `key1varchar` varchar (14) NOT NULL , -> `key2int` int (11) NOT NULL DEFAULT '0' , -> PRIMARY KEY (`key1varchar`,`key2int`), -> KEY `key1varchar` (`key1varchar`), -> KEY `key2int` (`key2int`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.77 sec)   MariaDB [test]> insert into `test` values ( 'value1' ,0),( 'value1' ,1),( 'value1' ,1000685),( 'value1' ,1003560),( 'value1' ,1004807); Query OK, 5 rows affected (0.11 sec) Records: 5 Duplicates: 0 Warnings: 0   MariaDB [test]> delete from `test` where `key1varchar`= 'value1' AND ( key2int <=1 OR key2int > 1); Query OK, 1 row affected (0.11 sec)   MariaDB [test]> select * from `test`; + -------------+---------+ | key1varchar | key2int | + -------------+---------+ | value1 | 1 | | value1 | 1000685 | | value1 | 1003560 | | value1 | 1004807 | + -------------+---------+ 4 rows in set (0.00 sec) Test case CREATE TABLE `test` ( `key1varchar` varchar (14) NOT NULL , `key2int` int (11) NOT NULL DEFAULT '0' , PRIMARY KEY (`key1varchar`,`key2int`), KEY `key1varchar` (`key1varchar`), KEY `key2int` (`key2int`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;   insert into test values ( 'value1' ,0),( 'value1' ,1),( 'value1' ,1000685),( 'value1' ,1003560),( 'value1' ,1004807); delete from test where `key1varchar`= 'value1' AND ( key2int <=1 OR key2int > 1); select * from test;   DROP TABLE test;

Vicențiu Ciorbaru added a comment - 2016-07-26 00:38 The main difference between when using an InnoDB storage engine and when using a different storage engine, say MyISAM, is that we're using a different query plan. We're making use of an index scan with InnoDB and this leads us to the following selects: analyze DELETE FROM test WHERE `key1varchar`='value1' AND ( key2int <=1 OR key2int > 1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE test range PRIMARY,key1varchar,key2int PRIMARY 44 NULL 5 1.00 100.00 100.00 Using where analyze DELETE FROM test_isam WHERE `key1varchar`='value1' AND ( key2int <=1 OR key2int > 1); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE test_isam ALL PRIMARY,key1varchar,key2int NULL NULL NULL 5 5.00 100.00 100.00 Using where As we can see, for the InnoDB table, we're only looking at one row. A preliminary step through the code indicates a difference when running check_quick_select(). For InnoDB we return a valid option, while MyISAM does not.

Sergei Petrunia added a comment - 2016-07-26 20:50 SELECT is not affected because for SELECT we're using a query plan that uses ref access: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref PRIMARY PRIMARY 44 const 1 Using where; Using index I think the problem is reproducible when range access is not reducible to ref but that would require a bigger table.

Sergei Petrunia added a comment - 2016-07-27 23:43 Fix pushed into 5.5 tree (from there, it will be merged to 10.x versions). ebial@navista.fr , thanks for taking time to report this!