[MDEV-10228] Delete missing rows with OR conditions Created: 2016-06-14  Updated: 2016-09-19  Resolved: 2016-07-27

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Delete
Affects Version/s: 10.0.25, 5.5, 10.0, 10.1, 10.2
Fix Version/s: 5.5.51, 10.1.17, 10.0.27

Type: Bug Priority: Critical
Reporter: ebial@navista.fr Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None
Environment:

mariaDB 10.0.25

Issue Links:
Duplicate
duplicates MDEV-10795 not match update rows Closed
Sprint: 10.2.2-3

 Description   

On mariaDB 10.0.25. I've got a problem when i delete some rows with a primary key VARCHAR + INT and a request with AND and OR conditions.

mysql> select version();
 
+--------------------------+
 
| version()                |
 
+--------------------------+
 
| 10.0.25-MariaDB-0+deb8u1 |
 
+--------------------------+
 
1 row in set (0.00 sec)


 
 
mysql> set autocommit=0;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> SHOW CREATE TABLE test;
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                                                                                                 |
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| 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 |
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)


mysql> SELECT * FROM test;
 
+-------------+---------+
 
| key1varchar | key2int |
 
+-------------+---------+
 
| value1      |       0 |
 
| value1      |       1 |
 
| value1      | 1000685 |
 
| value1      | 1003560 |
 
| value1      | 1004807 |
 
+-------------+---------+
 
5 rows in set (0.00 sec)


mysql> DELETE FROM test  
    -> WHERE 1 
    -> AND `key1varchar`='value1' 
    -> AND ( key2int <=1 OR  key2int > 1);
 
Query OK, 1 row affected (0.00 sec)


mysql> SELECT * FROM test;
 
+-------------+---------+
 
| key1varchar | key2int |
 
+-------------+---------+
 
| value1      |       1 |
 
| value1      | 1000685 |
 
| value1      | 1003560 |
 
| value1      | 1004807 |
 
+-------------+---------+
 
4 rows in set (0.00 sec)



 Comments   
Comment by Elena Stepanova [ 2016-06-15 ]

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;

Comment by Vicențiu Ciorbaru [ 2016-07-26 ]

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.

Comment by Sergei Petrunia [ 2016-07-26 ]

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.

Comment by Sergei Petrunia [ 2016-07-27 ]

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!

Generated at Thu Feb 08 07:40:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.