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

[23 Aug 2007 10:26] Shane Bester
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
[23 Aug 2007 10:26] Shane Bester
this leads to wrong data!  related might be bug #23764
[30 Aug 2007 19:59] 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/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.
[3 Sep 2007 16:28] Ingo Strüwing
IMHO a second reviewer is not absolutely required. May Calvin decide.
[13 Sep 2007 11:39] 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/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.
[22 Oct 2007 17:03] Bugs System
Pushed into 5.1.23-beta
[22 Oct 2007 17:05] Bugs System
Pushed into 5.0.52
[22 Oct 2007 17:08] Bugs System
Pushed into 4.1.24
[22 Oct 2007 22:08] Paul Dubois
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.
[20 Mar 2008 10:39] ronnie mist
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