MySQL Bugs: #30590: delete from memory table with composite btree primary key

Bug #30590	delete from memory table with composite btree primary key
Submitted:	23 Aug 2007 10:26	Modified:	22 Oct 2007 22:08
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S1 (Critical)
Version:	5.0.48,5.1.22	OS:	Any
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
deleting from a memory table where the PK is defined as a composite key over two columns results in wrong matches.  select works fine.

mysql> drop table if exists `t`;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `t` (`a` int,`b` int,`c` int,primary key using btree (`a`,`b`))engine=memory;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into `t` values (1,1,1),(2,1,1),(2,2,1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from `t`;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 |    1 |
| 2 | 1 |    1 |
| 2 | 2 |    1 |
+---+---+------+
3 rows in set (0.00 sec)

mysql> delete from `t` where `a`=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from `t`;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 |    1 |
| 2 | 2 |    1 |<---- we expect this to be deleted!!
+---+---+------+
2 rows in set (0.00 sec)

How to repeat:
drop table if exists `t`;
create table `t` (`a` int,`b` int,`c` int,primary key using btree (`a`,`b`))engine=memory;
insert into `t` values (1,1,1),(2,1,1),(2,2,1);
select * from `t`;
delete from `t` where `a`=2;
select * from `t`;

Suggested fix:
as a workaround, using hash indexes seems to work

this leads to wrong data!  related might be bug #23764

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/commits/33471

ChangeSet@1.2680, 2007-08-30 23:47:04+05:00, svoj@april.(none) +4 -0
  BUG#30590 - delete from memory table with composite btree primary key
  
  DELETE query against memory table with btree index may remove
  not all matching rows. This happens only when DELETE uses
  index read method to find matching rows. E.g. for queries
  like DELETE FROM t1 WHERE a=1.
  
  Fixed by reverting fix for BUG9719 and applying proper solution.

IMHO a second reviewer is not absolutely required. May Calvin decide.

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/commits/34168

ChangeSet@1.2680, 2007-09-13 15:39:16+05:00, svoj@mysql.com +5 -0
  BUG#30590 - delete from memory table with composite btree primary key
  
  DELETE query against memory table with btree index may remove
  not all matching rows. This happens only when DELETE uses
  index read method to find matching rows. E.g. for queries
  like DELETE FROM t1 WHERE a=1.
  
  Fixed by reverting fix for BUG9719 and applying proper solution.

Pushed into 5.1.23-beta

Pushed into 5.0.52

Pushed into 4.1.24

Noted in 4.1.24, 5.0.52, 5.1.23 changelogs.

For MEMORY tables, DELETE statements that remove rows based on an
index read could fail to remove all matching rows.

I have this issue always on memory table with unique composite btree key. But 
also SOMETIMES I have similar issue on memory table with simple btree key:
...
KEY `key` USING BTREE (`key`)
...

SELECT COUNT(*) AS rows_count FROM `table` WHERE `key` = <SOME_VALUE>;
+------------+
| rows_count |
+------------+
|<ROWS_COUNT>|
+------------+
1 row in set (0.00 sec)

DELETE FROM `table` WHERE `key` = <SOME_VALUE>;
Query OK, <X> rows affected (0.00 sec)

0 <= X <= ROWS_COUNT