| Bug #374 | Delete using multiple table references and indexes fails | ||
|---|---|---|---|
| Submitted: | 5 May 2003 20:39 | Modified: | 13 May 2003 13:11 |
| Reporter: | Lance Walton | ||
| Status: | Can't repeat | ||
| Category: | Server | Severity: | S2 (Serious) |
| Version: | 3.23.51 | OS: | Mac OS X (Mac OS X) |
| Assigned to: | Bugs System | Target Version: | |
[13 May 2003 13: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.
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);