delete on derived table succeeds erroneously

[Monty Taylor]

CREATE TABLE `t1` (
  `N` int NOT NULL default '0',
  `M` int default '0'
) ENGINE=MyISAM;

delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;

Succeeds. This isn't such a good thing.

[Jay Pipes (jaypipes)]

Not only is this a critical bug, it apparently introduces behaviour (feature?) that MySQL doesn't have..the ability to use a self-join for a DELETE.

This is odd that this succeeds...

[Jay Pipes (jaypipes)]

Confirmed, and doesn't depend on table not having any rows. Indeed, the derived table *maps* to the actual table and issues deletes on all affected rows in the original!

drizzle> use test
Database changed
drizzle> CREATE TABLE `t1` ( `N` int NOT NULL default '0', `M` int default '0' ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

drizzle> delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
Query OK, 0 rows affected (0.00 sec)

drizzle> insert into t1 values (1,1);
Query OK, 1 row affected (0.00 sec)

drizzle> delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
Query OK, 0 rows affected (0.00 sec)

drizzle> insert into t1 values (1,2);
Query OK, 1 row affected (0.00 sec)

drizzle> insert into t1 values (2,2);
Query OK, 1 row affected (0.00 sec)

drizzle> insert into t1 values (2,3);
Query OK, 1 row affected (0.00 sec)

drizzle> insert into t1 values (3,3);
Query OK, 1 row affected (0.00 sec)

drizzle> delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
Query OK, 6 rows affected (0.00 sec)

drizzle> select * from t1;
+---+------+
| N | M |
+---+------+
| 3 | 3 |
+---+------+
1 row in set (0.00 sec)

[Lee Bieber (kalebral-deactivatedaccount)]

Still exists as of build 1103 (using InnoDB instead of MyISAM)