Bug #9623 Server can't handle 33-table join

• Submitted: 4 Apr 2005 19:41
• Modified: 5 Apr 2005 17:57
• Reporter: Peter Gulutzan
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.4-beta-debug
• OS: Linux (SUSE 9.2)
• Assigned to: Michael Widenius
• CPU Architecture: Any

[4 Apr 2005 19:41] Peter Gulutzan

Description:
I have a one-column table with two rows. If I do a self-join on the table 33 times, the server goes away until it runs out of memory, or until I terminate the process after 15 minutes.
It doesn't matter whether or not there is an index on the table. Response is quick if there is only one row in the table.

How to repeat:
mysql> create table t (s1 int);
Query OK, 0 rows affected (0.31 sec)

mysql> insert into t values (null);
Query OK, 1 row affected (0.00 sec)

mysql> create index i on t (s1);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t as t1, t as t2, t as t3, t as t4, t as t5,
    ->               t as t6, t as t7, t as t8, t as t9, t as t10,
    ->               t as t11, t as t12, t as t13, t as t14, t as t15,
    ->               t as t17, t as t18, t as t19, t as t20, t as t21,
    ->               t as t22, t as t23, t as t24, t as t25, t as t26,
    ->               t as t27, t as t28, t as t29, t as t30, t as t31,
    ->               t as t32, t as t33;
+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   | s1   |
+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
1 row in set (0.02 sec)

mysql> insert into t values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t as t1, t as t2, t as t3, t as t4, t as t5,               t as t6, t as t7, t as t8, t as t9, t as t10,          t as t11, t as t12, t as t13, t as t14, t as t15,               t as t17, t as t18, t as t19, t as t20, t as t21,        t as t22, t as t23, t as t24, t as t25, t as t26,               t as t27, t as t28, t as t29, t as t30, t as t31,      t as t32, t as t33;
Terminated

[5 Apr 2005 17:57] Michael Widenius

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

There is no problem with the join optimizer.

The problem here is that you are doing a select that returns 4294967296 rows of NULLS, which the mysql client will try to cache in memory.   You are running out of memory in the mysql client as you don't have enough memory to hold the whole result.

Fix:

Start mysql with the --i-am-a-dummy option, which will add automatic detection of queries like this

or

Replace SELECT * with SELECT COUNT(*)

or

add LIMIT 1 to the end of the query