Bug #4458 multi-table delete deletes too much
Submitted: 8 Jul 2004 9:56 Modified: 12 Aug 2004 9:03
Reporter: Sergei Golubchik Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.21 OS:
Assigned to: Sergei Glukhov CPU Architecture:Any

[8 Jul 2004 9:56] Sergei Golubchik
Description:
multi-table delete deletes a row it shoud not (the row that does not match WHERE condition)

How to repeat:
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (a int(11) NOT NULL PRIMARY KEY, b int(11));
INSERT  INTO  t1 VALUES (1, 1), (3, 5), (4, 5);
CREATE TABLE t2 (c int(11) NOT NULL default '1', d int(1), KEY (d));
INSERT INTO t2 VALUES (1, 1), (1, NULL), (1, 1), (1, NULL), (1, 1),
(3, 1), (3, NULL), (1, 1), (5, 1), (6, NULL), (1, NULL), (1, 1);

select * from t1, t2 WHERE a=c AND b=5; -- two rows with d=1, d=NULL
DELETE t1, t2 FROM t1, t2 WHERE a=c AND t1.b=5 AND d IS NULL;
select * from t1, t2 WHERE a=c AND b=5; -- the row with d=1 is gone too
[13 Jul 2004 11:00] Hartmut Holzgraefe
same in 4.1.3 and 5.0-bk
[12 Aug 2004 9:03] Sergei Glukhov
use "select * from t2 where c=3" to see that the row is not deleted