| 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: | |
| 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 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.
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';