Bug #33509 Server crashes with number of recursive subqueries=61
Submitted: 25 Dec 2007 18:27 Modified: 20 Nov 2010 23:32
Reporter: Alexey Stroganov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:6.0.4-alpha OS:Linux (x86_64)
Assigned to: Sergey Petrunya CPU Architecture:Any
Tags: regression
Triage: D1 (Critical) / R2 (Low) / E3 (Medium)

[25 Dec 2007 18:27] Alexey Stroganov
Description:
I ran crash-me against the latest 6.0.4 and found out that server crashes on test that detects max number of nested subqueries. Further investigation showed that critical number of nested subqueries = 61. I attached simplified test case that can be run with mysql-test-run.

bt:

(gdb) bt
#0  0x00002b8eb1b694c5 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000650892 in handle_segfault (sig=11) at mysqld.cc:2313
#2  <signal handler called>
#3  0x00002b8eb2470955 in free () from /lib64/libc.so.6
#4  0x0000000000a67113 in my_no_flags_free (ptr=0x300000004) at my_malloc.c:59
#5  0x0000000000730947 in filesort_free_buffers (table=0x248e6f8, full=true) at filesort.cc:331
#6  0x00000000006b3fd7 in JOIN::cleanup (this=0x2409840, full=true) at sql_select.cc:8491
#7  0x00000000006b3e24 in JOIN::join_free (this=0x2409840) at sql_select.cc:8420
#8  0x00000000006bd5d6 in do_select (join=0x2409840, fields=0x2276280, table=0x0, procedure=0x0)
    at sql_select.cc:13059
#9  0x00000000006a8252 in JOIN::exec (this=0x2409840) at sql_select.cc:2713
#10 0x00000000006a87d4 in mysql_select (thd=0x2274790, rref_pointer_array=0x2276360, tables=0x201b848,
    wild_num=0, fields=@0x2276200, conds=0x1be7b00, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
    select_options=2147764736, result=0x1be7ef0, unit=0x2275d38, select_lex=0x2276178) at sql_select.cc:2901
#11 0x00000000006a2cf2 in handle_select (thd=0x2274790, lex=0x2275c98, result=0x1be7ef0,
    setup_tables_done_option=0) at sql_select.cc:270
#12 0x0000000000661419 in execute_sqlcom_select (thd=0x2274790, all_tables=0x201b848) at sql_parse.cc:4517
#13 0x000000000065c932 in mysql_execute_command (thd=0x2274790) at sql_parse.cc:1845
#14 0x0000000000662bfe in mysql_parse (thd=0x2274790,
    inBuf=0x201a4f0 "select a from crash_me where a in (select a from crash_me where a in (select a from crash_me where a in (select a from crash_me where a in (select a from crash_me where a in (select a from crash_me wh"..., length=2254, found_semicolon=0x4463d620) at sql_parse.cc:5410
#15 0x000000000065b315 in dispatch_command (command=COM_QUERY, thd=0x2274790, packet=0x201adbe "",
    packet_length=2254) at sql_parse.cc:948
#16 0x000000000065ab12 in do_command (thd=0x2274790) at sql_parse.cc:697
#17 0x00000000006598cf in handle_one_connection (arg=0x300000004) at sql_connect.cc:1146
#18 0x00002b8eb1b65193 in start_thread () from /lib64/libpthread.so.0
#19 0x00002b8eb24c345d in clone () from /lib64/libc.so.6
#20 0x0000000000000000 in ?? ()

How to repeat:
Run attached test case

./mysql-test-run <test-case>
[25 Dec 2007 18:30] Alexey Stroganov
Test case for issue

Attachment: bug33509.test (application/octet-stream, text), 2.40 KiB.

[26 Dec 2007 7:06] Valeriy Kravchuk
Thank you for a problem report. Sorry, but I can not repeat the behaviour described neither in older 6.0.3 (MySQL binaries) nor in latest 6.0.5 built from BK:

openxs@linux:~/dbs/6.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.5-alpha Source distribution

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

mysql> create table crash_me (a int not null);
roQuery OK, 0 rows affected (0.01 sec)

mysql>
mysql> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in (
    -> select a from crash_me where a in ( select a from crash_me)
    -> )))))))))))))))))))))))))))))))))))))))))))))))))))))))))))));
ERROR 1473 (HY000): Too high level of nesting for select

Do you use -debug build?
[8 Jan 2008 18:40] Omer Barnir
triage: not setting priority - 6.0
[3 May 2008 2:17] Bugs System
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/46321

ChangeSet@1.2635, 2008-05-03 06:16:48+04:00, sergefp@mysql.com +3 -0
  BUG#33509: Server crashes with number of recursive subqueries=61
  - Fix the code that makes JOIN::flatten_subqueries() not to flatten them
    if the total number of tables will exceed MAX_TABLES
  - When replacing the subquery predicate, replace the select_lex->where if 
    it is a complete WHERE clause.
[28 May 2008 10:02] Bugs System
Pushed into 6.0.6-alpha
[30 May 2008 19:06] Paul Dubois
Noted in 6.0.6 changelog.

Use of 61 nested subqueries caused a server crash.
[16 Aug 2010 6:38] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[23 Aug 2010 8:46] Catherine Woo
•For queries with a sufficient number of subqueries in the FROM clause of this form: 
SELECT * FROM (SELECT 1) AS t1,
              (SELECT 2) AS t2,
              (SELECT 3) AS t3, ...
The query failed with a Too high level of nesting for select error, as though the query had this form: 
SELECT * FROM (SELECT 1 FROM (SELECT 2 FROM (SELECT 3 FROM ...
(Bug#41156)
[13 Nov 2010 16:09] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[20 Nov 2010 23:32] Paul Dubois
Noted in 5.6.1 changelog.
[23 Nov 2010 2:24] Paul Dubois
Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.