Bug #28837 MyISAM storage engine error (134) doing delete with self-join

Description:
If I run the follwing SQL on either 5.0.42 (or the latest snapshot) or 5.1.19-beta, I repeatably get "ERROR 1030 (HY000): Got error 134 from storage engine".

DROP TABLE IF EXISTS delete_self_join_bug_test;
CREATE TABLE delete_self_join_bug_test (
  id int NOT NULL,
  ref int NOT NULL,
  INDEX (id)
);
CREATE TEMPORARY TABLE loader LIKE delete_self_join_bug_test;
INSERT INTO loader (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4);
INSERT INTO delete_self_join_bug_test SELECT * FROM loader;
DELETE FROM a USING      delete_self_join_bug_test AS a
              INNER JOIN delete_self_join_bug_test AS b USING (id)
       WHERE a.ref < b.ref;

Note:
  * the error doesn't occur if you INSERT the rows directly rather than copying
    them via the "loader" table;

  * the error also doesn't occur if the index is removed;

  * the order of the rows appears to be important;

  * this occurs only with MyISAM tables, not with InnoDB.

How to repeat:
Run the above SQL on one of the affected versions of the MySQL server.

Thank you for the bug report.

[miguel@light 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.44-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS delete_self_join_bug_test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE delete_self_join_bug_test (
    ->   id int NOT NULL,
    ->   ref int NOT NULL,
    ->   INDEX (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TEMPORARY TABLE loader LIKE delete_self_join_bug_test;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO loader (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO delete_self_join_bug_test SELECT * FROM loader;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> DELETE FROM a USING      delete_self_join_bug_test AS a
    ->               INNER JOIN delete_self_join_bug_test AS b USING (id)
    ->        WHERE a.ref < b.ref;
ERROR 1030 (HY000): Got error 134 from storage engine
mysql> 
-----------------without index----------------------
mysql> DROP TABLE IF EXISTS delete_self_join_bug_test;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE delete_self_join_bug_test (
    ->   id int NOT NULL,
    ->   ref int NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TEMPORARY TABLE loader LIKE delete_self_join_bug_test;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO loader (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO delete_self_join_bug_test SELECT * FROM loader;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> DELETE FROM a USING      delete_self_join_bug_test AS a
    ->               INNER JOIN delete_self_join_bug_test AS b USING (id)
    ->        WHERE a.ref < b.ref;
Query OK, 1 row affected (0.00 sec)

mysql>

Bug #30290 can be related

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36993

ChangeSet@1.2552, 2007-11-02 18:51:11+04:00, gshchepa@gleb.loc +3 -0
  Fixed bug #28837.
  The MyISAM storage engine reports error 134 doing DELETE with a self-join.
  
  Error 134 (HA_ERR_RECORD_DELETED) is an internal engine code to report
  deleted records during table scans. 
  The join_read_next_same function has been modified to skip
  deleted rows silently.

The same problem applies to MERGE tables. Test case:

CREATE TABLE t1 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MyISAM;
CREATE TABLE t2 LIKE t1;

INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4);
INSERT INTO t1 SELECT * FROM t2;
INSERT INTO t1 SELECT * FROM t2;

CREATE TABLE t3 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MERGE
                                                                UNION(t1);

SELECT * FROM t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref;
SELECT * FROM t3;
DELETE FROM a USING t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref;
SELECT * FROM t3;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38133

ChangeSet@1.2550, 2007-11-20 16:59:10+03:00, kaa@polly.(none) +6 -0
  Fix for bug #28837: MyISAM storage engine error (134) doing delete with
  self-join
  
  When doing DELETE with self-join on a MyISAM or MERGE table, it could
  happen that a record being retrieved in join_read_next_same() has
  already been deleted by previous iterations. That caused the engine's
  index_next_same() method to fail with HA_ERR_RECORD_DELETED error and
  the whole DELETE query to be aborted with an error.
  
  Fixed by suppressing the HA_ERR_RECORD_DELETED error in
  hy_myisam::index_next_same() and ha_myisammrg::index_next_same(). Since
  HA_ERR_RECORD_DELETED can only be returned by MyISAM, there is no point
  in filtering this error in the SQL layer.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38531

ChangeSet@1.2550, 2007-11-26 18:58:54+03:00, kaa@polly.(none) +6 -0
  Fix for bug #28837: MyISAM storage engine error (134) doing delete with
  self-join
  
  When doing DELETE with self-join on a MyISAM or MERGE table, it could
  happen that a record being retrieved in join_read_next_same() has
  already been deleted by previous iterations. That caused the engine's
  index_next_same() method to fail with HA_ERR_RECORD_DELETED error and
  the whole DELETE query to be aborted with an error.
  
  Fixed by suppressing the HA_ERR_RECORD_DELETED error in
  hy_myisam::index_next_same() and ha_myisammrg::index_next_same(). Since
  HA_ERR_RECORD_DELETED can only be returned by MyISAM, there is no point
  in filtering this error in the SQL layer.

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

A note has been added to the 5.0.54, 5.1.23 and 6.0.5 changelogs: 

When doing a DELETE on table that involved a JOIN with MyISAM or MERGE tables and the JOIN referred to the same table, the operation could fail reporting ERROR 1030 (HY000): Got error 134 from storage engine. This was because scans on the table contents would change because of rows that had already been deleted

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44728

ChangeSet@1.2623, 2008-04-01 15:36:32+03:00, monty@mysql.com +21 -0
  Merge of changes in MyISAM since December 16 -> April 1
  Fixes bugs:
  Bug#28837  MyISAM storage engine error (134) doing delete with self-join
  Bug#31277  myisamchk --unpack corrupts table
  Bug#4692   DISABLE/ENABLE KEYS waste a space
  Bug#31305  myisam tables crash when they are near capacity

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44737

ChangeSet@1.2623, 2008-04-01 17:57:30+03:00, monty@mysql.com +21 -0
  Merge of changes in MyISAM since December 16 -> April 1
  Fixes bugs:
  Bug#28837  MyISAM storage engine error (134) doing delete with self-join
  Bug#31277  myisamchk --unpack corrupts table
  Bug#4692   DISABLE/ENABLE KEYS waste a space
  Bug#31305  myisam tables crash when they are near capacity