Bug #374 Delete using multiple table references and indexes fails
Submitted: 5 May 2003 18:39 Modified: 13 May 2003 11:11
Reporter: Lance Walton Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:3.23.51 OS:MacOS (Mac OS X)
Assigned to: Michael Widenius CPU Architecture:Any

[5 May 2003 18:39] Lance Walton
Description:
I am attempting to do a 'delete using' with two table references to the same table. The table has an 
index on a column I'm using in the where clause of the delete.

I get an error message 'Got error 134 from table handler'

If I remove the index, then the error doesn't happen. The error also seems to be data sensitive (the 
data I've given below reliably causes the error for me).

Regards,

Lance

How to repeat:
create table MODES_F3 (F1 VARCHAR(30), F2 VARCHAR(30), F3 VARCHAR(30), cnt int, groupid int, 
KEY groupid_index (groupid));

insert into modes_f3 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6), ('0','1','2',1,5), ('0','2','0',1,3), 
('1','0','1',1,2), ('1','2','1',1,1), ('1','2','2',1,1), ('2','0','1',2,4), ('2','2','0',1,7);

delete from MODES_F3 using MODES_F3 m1,MODES_F3 m2 where m1.groupid=m2.groupid and 
(m1.cnt < m2.cnt or m1.cnt=m2.cnt and m1.F3>m2.F3);
[13 May 2003 11:11] Michael Widenius
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

3.23.51 doesn't have multi table deletes, so I tried the code in the latest MySQL 4.0 tree.  As I couldn't repeat the problem, I have closed this bug.

I did however add your test case to our test system to get this verified on all our build platforms.