MySQL Bugs: #22172: multi table delete does not work correct

Bug #22172	multi table delete does not work correct
Submitted:	9 Sep 2006 10:19	Modified:	20 Oct 2006 7:54
Reporter:	Georg Richter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	5.2	OS:	Any (all)
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
In case of multi table deletes Falcon does not
delete all affected records. 

How to repeat:
CREATE TABLE t1 (
number bigint(20) NOT NULL default '0',
cname char(15) NOT NULL default '',
carrier_id smallint(6) NOT NULL default '0'
);
INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90);
INSERT INTO t1 VALUES (650,'San Francisco',0);
INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90);
INSERT INTO t1 VALUES (333,'tubs',99);
CREATE TABLE t2 (
number bigint(20) NOT NULL default '0',
cname char(15) NOT NULL default '',
carrier_id smallint(6) NOT NULL default '0'
);
INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0);
INSERT INTO t2 VALUES (650,'San Francisco',90);
INSERT INTO t2 VALUES (333,'tubs',99);
select number, cname, carrier_id from t1 ORDER BY number, cname, carrier_id;
number cname carrier_id
333 tubs 99
650 San Francisco 0
302467 Sue's Subshop 90
4077711111 SeanWheeler 90
select number, cname, carrier_id from t2 ORDER BY number, cname, carrier_id;
number cname carrier_id
333 tubs 99
650 San Francisco 90
4077711111 SeanWheeler 0
select *
from t1 left join t2 on t1.number=t2.number
where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or (t1.carrier_id=90 and t2.number is null)
ORDER BY t1.number, t1.cname, t1.carrier_id;
number cname carrier_id number cname carrier_id
650 San Francisco 0 650 San Francisco 90
302467 Sue's Subshop 90 NULL NULL NULL
4077711111 SeanWheeler 90 4077711111 SeanWheeler 0
delete t1, t2 from t1 left join t2 on t1.number=t2.number
where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or (t1.carrier_id=90 and t2.number is null);
select number, cname, carrier_id from t1 ORDER BY number, cname, carrier_id;
number cname carrier_id
333 tubs 99
650 San Francisco 0 <------ These records must
302467 Sue's Subshop 90 <------ no more exist
select number, cname, carrier_id from t2 ORDER BY number, cname, carrier_id;
number cname carrier_id
333 tubs 99

mantis bug#215.

Bug #22172 - falcon_bug_215.test failed to delete the second and third records 
from t1 because the table->status code was left with the value 64 (STATUS_DELETED).
Apparently, it is up to the storage engine to set this back to zero on the next
call, which is a call to handler::rnd_next().  I found this by tracing the same 
test against innodb to see what it does.

Verified on Linux 32-bit and Linux 64-bit change set 1.2333, 2006-10-19 23:00:34-05:00

hakan@lsmy005:~/work/mysql/mysql-5.1-falcon/mysql-test> ./mysql-test-run.pl --enable-disabled --skip-ndb falcon_bug_215

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

falcon_bug_215                 [ pass ]            877
-------------------------------------------------------
Stopping All Servers
Shutting-down Instance Manager
All 1 tests were successful.

Regards, Hakan