MySQL Bugs: #21007: NATURAL JOIN (any JOIN (2 x NATURAL JOIN)) crashes the server

Bug #21007	NATURAL JOIN (any JOIN (2 x NATURAL JOIN)) crashes the server
Submitted:	12 Jul 2006 16:34	Modified:	7 Aug 2006 6:25
Reporter:	Melun Arnaud	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.21-community/5.0BK/5.1BK	OS:	Windows (WINDOWS XP SP2/Suse Linux)
Assigned to:	Timour Katchaounov	CPU Architecture:	Any
Tags:	natural join; join; crash

Description:
When launching a request that contains the following structure in the FROM clause, the mysqld program crashes without any error in the logs.
NATURAL JOIN statement(
      any JOIN statement(
            ... NATURAL JOIN statement ...
            ... NATURAL JOIN statement ...
      )ON (...)
)

Windows throws a memory exception like :
"The instruction at "0x0051c745" attempts to use the memory address "0x0000003c". Memory cannot be read." (Translation from French)

The server can be relaunched after that.

The same bug has been spotted on an earlier version (5.0.19), if that can help.

How to repeat:
/* Table structure and data */
CREATE TABLE t1 (id1 INT, id2 INT);
INSERT INTO t1 VALUES(1,1);
CREATE TABLE t2 (id2 INT, id3 INT, id5 INT);
INSERT INTO t2 VALUES(1,1,1);
CREATE TABLE t3 (id3 INT, id4 INT);
INSERT INTO t3 VALUES(1,1);
CREATE TABLE t4 (id4 INT);
INSERT INTO t4 VALUES(1);
CREATE TABLE t5 (id5 INT, id6 INT);
INSERT INTO t5 VALUES(1,1);
CREATE TABLE t6 (id6 INT);
INSERT INTO t6 VALUES(1);

/* The request itself */
SELECT *
/* 1st level : 1 NATURAL JOIN statement */
FROM t1 NATURAL JOIN (
  /* 2nd level : any JOIN statement but NATURAL */
  t2 JOIN (
      /* 3rd level : 2 consecutive NATURAL JOIN statements */
      t3 NATURAL JOIN t4,
      t5 NATURAL JOIN t6)
  ON (t3.id3 = t2.id3
    AND t5.id5 = t2.id5)
);

Suggested fix:
I think it's an internal bug from the parser.
Since there's no output on the error log, I can't suggest more ...

Back Trace 5.0BK Suse 10 32-bit

Attachment: bt21007.txt (text/plain), 10.11 KiB.

Thank you for the bug report. Verified as described for 5.0/5.1BK on
Linux Suse 10 32-bit. Back trace attached.

mysql> SELECT *
    -> /* 1st level : 1 NATURAL JOIN statement */
    -> FROM t1 NATURAL JOIN (
    ->   /* 2nd level : any JOIN statement but NATURAL */
    ->   t2 JOIN (
    ->       /* 3rd level : 2 consecutive NATURAL JOIN statements */
    ->       t3 NATURAL JOIN t4,
    ->       t5 NATURAL JOIN t6)
    ->   ON (t3.id3 = t2.id3
    ->     AND t5.id5 = t2.id5)
    -> );
ERROR 2013 (HY000): Lost connection to MySQL server during query
------------------------------------------------------------------------------
mysql> SELECT *
    -> /* 1st level : 1 NATURAL JOIN statement */
    -> FROM t1 NATURAL JOIN (
    ->   /* 2nd level : any JOIN statement but NATURAL */
    ->   t2 JOIN (
    ->       /* 3rd level : 2 consecutive NATURAL JOIN statements */
    ->       t3 NATURAL JOIN t4,
    ->       t5 NATURAL JOIN t6)
    ->   ON (t3.id3 = t2.id3
    ->     AND t5.id5 = t2.id5)
    -> );
+------+------+------+------+------+------+------+------+
| id1  | id2  | id3  | id5  | id3  | id4  | id5  | id6  |
+------+------+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 |    1 |    1 |    1 |
+------+------+------+------+------+------+------+------+
1 row in set (0.02 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.21-debug |
+--------------+
1 row in set (0.00 sec)

mysql>

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

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

The problem was that store_top_level_join_columns() incorrectly assumed
that the left/right neighbor of a nested join table reference can be only
at the same level in the join tree.

Fixed in 5.0.25, 5.1.12

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html

Documented bugfix in 5.0.25/5.1.12 changelogs.