Bug #31445 Delete only deletes first matching record
Submitted: 8 Oct 2007 12:12 Modified: 22 Oct 2007 16:04
Reporter: Johan Svensson Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Memory storage engine Severity:S2 (Serious)
Version:5.0.45 OS:Windows (Linux)
Assigned to: Assigned Account CPU Architecture:Any

[8 Oct 2007 12:12] Johan Svensson
Description:
Doing a delete from x where list='b' will only delete the first matching record.

How to repeat:
CREATE TABLE x (
  `List` varchar(30) NOT NULL,
  `Id` varchar(24) NOT NULL,
  `ListOrder` int(11) NOT NULL,
  `Name` varchar(60) default NULL,
  KEY `idxIdList` (`Id`,`List`),
  KEY `idx_views_List` USING BTREE (`List`,`ListOrder`),
  KEY `idx_views_Name` USING BTREE (`Name`),
  KEY `idx_views_id` (`Id`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

insert into x values ('a','1',1,'');
insert into x values ('a','2',2,'');
insert into x values ('a','3',3,'');
insert into x values ('b','1',1,'');
insert into x values ('b','2',2,'');
insert into x values ('b','2',3,'');
insert into x values ('c','1',1,'');
insert into x values ('c','2',2,'');
insert into x values ('c','2',3,'');

delete from x where list='b';
[8 Oct 2007 15:38] MySQL Verification Team
Thank you for the bug report.

[miguel@skybr 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.52-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE x (
    ->   `List` varchar(30) NOT NULL,
    ->   `Id` varchar(24) NOT NULL,
    ->   `ListOrder` int(11) NOT NULL,
    ->   `Name` varchar(60) default NULL,
    ->   KEY `idxIdList` (`Id`,`List`),
    ->   KEY `idx_views_List` USING BTREE (`List`,`ListOrder`),
    ->   KEY `idx_views_Name` USING BTREE (`Name`),
    ->   KEY `idx_views_id` (`Id`)
    -> ) ENGINE=MEMORY DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> insert into x values ('a','1',1,'');
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values ('a','2',2,'');
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values ('a','3',3,'');
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values ('b','1',1,'');
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values ('b','2',2,'');
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values ('b','2',3,'');
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values ('c','1',1,'');
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values ('c','2',2,'');
Query OK, 1 row affected (0.00 sec)

mysql> insert into x values ('c','2',3,'');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> delete from x where list='b';
Query OK, 1 row affected (0.03 sec)

mysql> select * from x;
+------+----+-----------+------+
| List | Id | ListOrder | Name |
+------+----+-----------+------+
| a    | 1  |         1 |      | 
| a    | 2  |         2 |      | 
| a    | 3  |         3 |      | 
| b    | 2  |         2 |      | 
| b    | 2  |         3 |      | 
| c    | 1  |         1 |      | 
| c    | 2  |         2 |      | 
| c    | 2  |         3 |      | 
+------+----+-----------+------+
8 rows in set (0.00 sec)

mysql> delete from x where list='b';
Query OK, 1 row affected (0.00 sec)

mysql> select * from x;
+------+----+-----------+------+
| List | Id | ListOrder | Name |
+------+----+-----------+------+
| a    | 1  |         1 |      | 
| a    | 2  |         2 |      | 
| a    | 3  |         3 |      | 
| b    | 2  |         3 |      | 
| c    | 1  |         1 |      | 
| c    | 2  |         2 |      | 
| c    | 2  |         3 |      | 
+------+----+-----------+------+
7 rows in set (0.00 sec)

mysql>
[8 Oct 2007 16:26] Sergey Vojtovich
Likely a dup of BUG#30590.
[10 Oct 2007 9:19] Konstantin Osipov
Bad data, upgrading.
[22 Oct 2007 16:04] Alexey Kopytov
Yes, this is a duplicate of bug #30590.