Bug #21310 Trees in SQL causing a "crashed" table with MyISAM storage engine

Description:
When trying to adapt to MySQL the article on Trees & Hierarchies in SQL (http://www.sqlteam.com/item.asp?ItemID=8866), I found something that looks like a bug :

    ERROR 1194 (HY000): La table 'P' est marquée 'crashed' et devrait être réparée

See the attached code to reproduce the bug.

I noticed some things that might help in understanding the source of the bug :

- When using InnoDB storage engine, everything looks fine, the bug is only appearing when using MyISAM storage engine

- When removing the 'id' field, and using 'node' as PRIMARY KEY, the bug is no more appearing with MyISAM storage engine :

CREATE TABLE `foo` (
  `node` tinyint(3) unsigned NOT NULL,
  `parent_node` tinyint(3) unsigned NULL,
  `depth` tinyint(4) unsigned NULL,
  `lineage` varchar(255) NULL,
  PRIMARY KEY  (`node`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `foo` VALUES (100,NULL, NULL, NULL);
INSERT INTO `foo` VALUES (101, 100, NULL, NULL);
INSERT INTO `foo` VALUES (102, 100, NULL, NULL);
INSERT INTO `foo` VALUES (103, 100, NULL, NULL);
INSERT INTO `foo` VALUES (104, 101, NULL, NULL);
INSERT INTO `foo` VALUES (105, 101, NULL, NULL);
INSERT INTO `foo` VALUES (106, 104, NULL, NULL);
INSERT INTO `foo` VALUES (107, 102, NULL, NULL);

My version is 5.0.22-Debian_1.dotdeb.1-log

Regards.

How to repeat:
DROP TABLE IF EXISTS `foo`;
CREATE TABLE `foo` (
  `id` smallint(5) unsigned NOT NULL default '0',
  `node` tinyint(3) unsigned NOT NULL,
  `parent_node` tinyint(3) unsigned NULL,
  `depth` tinyint(4) unsigned NULL,
  `lineage` varchar(255) NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `foo` VALUES (1, 100,NULL, NULL, NULL);
INSERT INTO `foo` VALUES (2, 101, 100, NULL, NULL);
INSERT INTO `foo` VALUES (3, 102, 100, NULL, NULL);
INSERT INTO `foo` VALUES (4, 103, 100, NULL, NULL);
INSERT INTO `foo` VALUES (5, 104, 101, NULL, NULL);
INSERT INTO `foo` VALUES (6, 105, 101, NULL, NULL);
INSERT INTO `foo` VALUES (7, 106, 104, NULL, NULL);
INSERT INTO `foo` VALUES (8, 107, 102, NULL, NULL);

UPDATE foo SET depth = 0,    lineage = '/'  WHERE ISNULL(parent_node);
UPDATE foo SET depth = NULL, lineage = NULL WHERE NOT ISNULL(parent_node);

#-- Following UPDATE x 3 times --
UPDATE
    foo AS T
        INNER JOIN foo AS P ON (T.parent_node = P.node)
SET
    T.depth = P.depth + 1,
    T.lineage = LTRIM(CONCAT(P.lineage, T.parent_node, '/'))
WHERE
    P.depth >= 0 
    AND NOT ISNULL(P.lineage)
    AND ISNULL(T.depth);

SELECT * FROM foo;

Thank you for a bug report. Verified just as described with 5.0.25-BK on Linux:

openxs@suse:~/dbs/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 8 to server version: 5.0.25

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

mysql> DROP TABLE IF EXISTS `foo`;
Query OK, 0 rows affected (0.01 sec)

CRmysql> CREATE TABLE `foo` (
    ->   `id` smallint(5) unsigned NOT NULL default '0',
    ->   `node` tinyint(3) unsigned NOT NULL,
    ->   `parent_node` tinyint(3) unsigned NULL,
    ->   `depth` tinyint(4) unsigned NULL,
    ->   `lineage` varchar(255) NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `foo` VALUES (1, 100,NULL, NULL, NULL);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `foo` VALUES (2, 101, 100, NULL, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `foo` VALUES (3, 102, 100, NULL, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `foo` VALUES (4, 103, 100, NULL, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `foo` VALUES (5, 104, 101, NULL, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `foo` VALUES (6, 105, 101, NULL, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `foo` VALUES (7, 106, 104, NULL, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `foo` VALUES (8, 107, 102, NULL, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE foo SET depth = 0,    lineage = '/'  WHERE ISNULL(parent_node);
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE foo SET depth = NULL, lineage = NULL WHERE NOT ISNULL(parent_node
);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 7  Changed: 0  Warnings: 0

mysql> UPDATE
    ->     foo AS T
    ->         INNER JOIN foo AS P ON (T.parent_node = P.node)
    -> SET
    ->     T.depth = P.depth + 1,
    ->     T.lineage = LTRIM(CONCAT(P.lineage, T.parent_node, '/'))
    -> WHERE
    ->     P.depth >= 0
    ->     AND NOT ISNULL(P.lineage)
    ->     AND ISNULL(T.depth);
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> UPDATE     foo AS T         INNER JOIN foo AS P ON (T.parent_node = P.no
de) SET     T.depth = P.depth + 1,     T.lineage = LTRIM(CONCAT(P.lineage, T.pa
rent_node, '/')) WHERE     P.depth >= 0      AND NOT ISNULL(P.lineage)     AND
ISNULL(T.depth);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> UPDATE     foo AS T         INNER JOIN foo AS P ON (T.parent_node = P.no
de) SET     T.depth = P.depth + 1,     T.lineage = LTRIM(CONCAT(P.lineage, T.pa
rent_node, '/')) WHERE     P.depth >= 0      AND NOT ISNULL(P.lineage)     AND
ISNULL(T.depth);
ERROR 1194 (HY000): Table 'P' is marked as crashed and should be repaired

mysql> select * from foo;
+----+------+-------------+-------+---------------+
| id | node | parent_node | depth | lineage       |
+----+------+-------------+-------+---------------+
|  1 |  100 |        NULL |     0 | /             |
|  2 |  101 |         100 |     1 | /100/         |
|  3 |  102 |         100 |     1 | /100/         |
|  4 |  103 |         100 |     1 | /100/         |
|  5 |  104 |         101 |     2 | /100/101/     |
|  6 |  105 |         101 |     2 | /100/101/     |
|  7 |  106 |         104 |     3 | /100/101/104/ |
|  8 |  107 |         102 |     2 | /100/102/     |
+----+------+-------------+-------+---------------+
8 rows in set (0.00 sec)

mysql> analyze table foo;
+----------+---------+----------+----------+
| Table    | Op      | Msg_type | Msg_text |
+----------+---------+----------+----------+
| test.foo | analyze | status   | OK       |
+----------+---------+----------+----------+
1 row in set (0.01 sec)

This problem was introduced with fix for bug#18036. I was able to create simplier test case:
CREATE TABLE t1(a VARCHAR(16));
INSERT INTO t1 VALUES('aaaaaaaa'),(NULL);
UPDATE t1 AS ta1, t1 AS ta2 SET ta1.a='aaaaaaaaaaaaaaaa';

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/14499

ChangeSet@1.2534, 2006-10-27 22:23:10+05:00, svoj@mysql.com +3 -0
  BUG#21310 - Trees in SQL causing a "crashed" table with MyISAM storage engine
  
  An update that used a join of a table to itself and modified the
  table on one side of the join reported the table as crashed.
  
  This problem was introduced with fix for BUG#18036.
  
  Do not rely on (tables_to_update & table->map) while determining whether to
use
  record cache for self-joined table updates. It must be disabled for both:
  table that is opened for reading and table that is opened for writing.

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/15767

ChangeSet@1.2534, 2006-11-23 19:50:37+04:00, svoj@mysql.com +3 -0
  BUG#21310 - Trees in SQL causing a "crashed" table with MyISAM storage engine
  
  An update that used a join of a table to itself and modified the
  table on one side of the join reported the table as crashed or
  updated wrong rows.
  
  Fixed by creating temporary table for self-joined multi update statement.

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/17222

ChangeSet@1.2534, 2006-12-20 19:05:35+04:00, svoj@mysql.com +4 -0
  BUG#21310 - Trees in SQL causing a "crashed" table with MyISAM storage engine
  
  An update that used a join of a table to itself and modified the
  table on one side of the join reported the table as crashed or
  updated wrong rows.
  
  Fixed by creating temporary table for self-joined multi update statement.

Pushed to trees currently marked as 5.0.36 and 5.1.15.

Pushed to tree currently marked as 4.1.23.

A note has been added to the 4.1.23, 5.0.36 and 5.1.15 changelogs.