Bug #4458 multi-table delete deletes too much

• Submitted: 8 Jul 2004 9:56
• Modified: 12 Aug 2004 9:03
• Reporter: Sergei Golubchik
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.21
• 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