Description:
When a SELECT statement, used as part of a IN clause in a DELETE statement, is invalid it results in all rows being deleted from a table. I have included a script that duplicates the problem, but the jist of it is this:
I ran the following statement:
delete from notes where list_id in (select list_id from lists where owner_id = 3);
In my example the lists table does not include a list_id column, it has an id column. If you just run the SELECT portion of the above statement you receive an error, but if you run the entire statement it just deletes everything from the notes table.
In the "how to repeat" section I have included the table creation, population, and the delete statement. This should result in the entire notes table being wiped when only 2 of the 3 records should be deleted (one has owner_id of 4).
How to repeat:
CREATE TABLE `lists` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` varchar(50) NOT NULL,
`url` varchar(255) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`checkable` tinyint(4) DEFAULT '0',
`content_type` int(11) DEFAULT '1',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`owner_id` int(11) DEFAULT NULL,
`vendor_id` int(11) DEFAULT NULL,
`username` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
`scrape_url` varchar(50) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=317 DEFAULT CHARSET=utf8;
CREATE TABLE `notes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(150) DEFAULT NULL,
`body` text,
`position` int(11) DEFAULT NULL,
`checked` int(11) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`content_type` int(11) DEFAULT '1',
`url` text,
`icon_url` text,
`user_id` int(11) DEFAULT NULL,
`list_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=285 DEFAULT CHARSET=utf8;
INSERT INTO `lists` (`id`,`type`,`url`,`name`,`checkable`,`content_type`,`created_at`,`updated_at`,`owner_id`,`vendor_id`,`username`,`password`,`scrape_url`,`user_id`)
VALUES
(310, 'List', NULL, 'sweetness', 0, 1, '2009-08-26 16:24:28', '2009-08-26 16:24:28', 3, NULL, NULL, NULL, NULL, NULL),
(316, 'List', NULL, 'awesomeness', 0, 1, '2009-08-27 18:18:02', '2009-08-27 18:18:02', 3, NULL, NULL, NULL, NULL, NULL);
INSERT INTO `notes` (`id`,`title`,`body`,`position`,`checked`,`created_at`,`updated_at`,`content_type`,`url`,`icon_url`,`user_id`,`list_id`)
VALUES
(285, 'note 1', 'asdf', NULL, NULL, '2009-08-28 11:05:42', '2009-08-28 11:05:42', 1, NULL, NULL, 3, 317),
(286, 'note 2', 'another asdf', NULL, NULL, '2009-08-28 11:05:58', '2009-08-28 11:05:58', 1, NULL, NULL, 3, 317),
(287, 'note 3', 'look, another asdf', NULL, NULL, '2009-08-28 11:06:29', '2009-08-28 11:06:29', 1, NULL, NULL, 3, 317),
(288, 'keeper', 'This one should not be deleted', NULL, NULL, '2009-08-28 11:06:29', '2009-08-28 11:06:29', 1, NULL, NULL, 4, 1);
delete from notes where list_id in (select list_id from lists where owner_id = 3);
Suggested fix:
Throw an error.
