Bug #54488 crash when using explain and prepared statements with subqueries
Submitted: 14 Jun 2010 15:15 Modified: 20 Nov 2010 2:43
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.91, 5.0.92-bzr, 5.1.47, 5.1.49-bzr, 5.5.3 OS:Any
Assigned to: Sergey Glukhov
Triage: Triaged: D1 (Critical)

[14 Jun 2010 15:15] Shane Bester
Description:
maybe related to bug #54484
5.1.47 stack trace:

mysqld.exe!select_describe()[sql_select.cc:16404]
mysqld.exe!JOIN::exec()[sql_select.cc:2335]
mysqld.exe!mysql_select()[sql_select.cc:2511]
mysqld.exe!mysql_explain_union()[sql_select.cc:16753]
mysqld.exe!select_describe()[sql_select.cc:16694]
mysqld.exe!JOIN::exec()[sql_select.cc:1775]
mysqld.exe!mysql_select()[sql_select.cc:2511]
mysqld.exe!mysql_explain_union()[sql_select.cc:16753]
mysqld.exe!select_describe()[sql_select.cc:16694]
mysqld.exe!JOIN::exec()[sql_select.cc:2335]
mysqld.exe!mysql_select()[sql_select.cc:2511]
mysqld.exe!mysql_explain_union()[sql_select.cc:16753]
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5046]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2263]
mysqld.exe!Prepared_statement::execute()[sql_prepare.cc:3583]
mysqld.exe!Prepared_statement::execute_loop()[sql_prepare.cc:3258]
mysqld.exe!mysql_sql_stmt_execute()[sql_prepare.cc:2531]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2273]
mysqld.exe!mysql_parse()[sql_parse.cc:5990]
mysqld.exe!dispatch_command()[sql_parse.cc:1235]
mysqld.exe!do_command()[sql_parse.cc:874]
mysqld.exe!handle_one_connection()[sql_connect.cc:1134]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:295]
mysqld.exe!_threadstart()[thread.c:275]
kernel32.dll!BaseThreadStart()

How to repeat:
drop table if exists `t1`;
create table `t1`(`a` int)engine=myisam;
insert into `t1` values (1),(1);
prepare `stmt` from 'explain 
select 1 from `t1`
where(select(select 1 from `t1` group by `a`))'; 
execute `stmt` ;
[14 Jun 2010 15:22] Valerii Kravchuk
Verified just as described:

valeriy-kravchuks-macbook-pro:5.0 openxs$ 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 2
Server version: 5.0.92-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `t1`(`a` int)engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into `t1` values (1),(1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> prepare `stmt` from 'explain 
    '> select 1 from `t1`
    '> where(select(select 1 from `t1` group by `a`))'; 
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> execute `stmt` ;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 100614 18:20:42  mysqld restarted
[14 Jun 2010 15:24] Valerii Kravchuk
Stack trace is:

Version: '5.1.49-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
100614 18:23:25 - mysqld got signal 10 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8384512
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337725 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x101c618
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0xb026af64 thread_stack 0x30000
0   mysqld                              0x00580bde my_print_stacktrace + 44
1   mysqld                              0x00101ae8 handle_segfault + 836
2   libSystem.B.dylib                   0x940472bb _sigtramp + 43
3   ???                                 0xffffffff 0x0 + 4294967295
4   mysqld                              0x001ac1c7 _ZN4JOIN4execEv + 2459
5   mysqld                              0x001a8795 _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex + 879
6   mysqld                              0x001a8cef _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 997
7   mysqld                              0x001ab4e8 _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 11230
8   mysqld                              0x001abad5 _ZN4JOIN4execEv + 681
9   mysqld                              0x001a8795 _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex + 879
10  mysqld                              0x001a8cef _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 997
11  mysqld                              0x001ab4e8 _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 11230
12  mysqld                              0x001ac1c7 _ZN4JOIN4execEv + 2459
13  mysqld                              0x001a8795 _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex + 879
14  mysqld                              0x001a8cef _Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result + 997
15  mysqld                              0x0011342e _Z15update_precheckP3THDP10TABLE_LIST + 690
16  mysqld                              0x00115cb6 _Z21mysql_execute_commandP3THD + 2936
17  mysqld                              0x001c0834 _ZN18Prepared_statement7executeEP6Stringb + 1112
18  mysqld                              0x001c4f57 _ZN18Prepared_statement12execute_loopEP6StringbPhS2_ + 327
19  mysqld                              0x001c5299 _Z22mysql_sql_stmt_executeP3THD + 491
20  mysqld                              0x00115cdf _Z21mysql_execute_commandP3THD + 2977
21  mysqld                              0x0011f677 _Z11mysql_parseP3THDPKcjPS2_ + 625
22  mysqld                              0x0012044d _Z16dispatch_command19enum_server_commandP3THDPcj + 3079
23  mysqld                              0x00121878 _Z10do_commandP3THD + 666
24  mysqld                              0x0010c21a handle_one_connection + 372
25  libSystem.B.dylib                   0x9400c095 _pthread_start + 321
26  libSystem.B.dylib                   0x9400bf52 thread_start + 34
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x10b46e8 = explain 
select 1 from `t1`
where(select(select 1 from `t1` group by `a`))
thd->thread_id=1
[14 Jun 2010 18:44] Shane Bester
prevention of exploitation is to put "max_prepared_stmt_count=0" in my.cnf
[24 Jun 2010 10:11] Tatiana Azundris Nuernberg
(gdb) bt
#0  0x00000001001bf3d9 in select_describe (join=0x1018c6438, need_tmp_table=true, need_order=true, distinct=false, message=0x0) at sql_select.cc:16475
#1  0x00000001001c1a51 in JOIN::exec (this=0x1018c6438) at sql_select.cc:1879
#2  0x00000001001be082 in mysql_select (thd=0x101836428, rref_pointer_array=0x10402b730, tables=0x10402c060, wild_num=0, fields=@0x10402b668, conds=0x0, og_num=1, order=0x0, group=0x10402c390, having=0x0, proc_param=0x0, select_options=2147764740, result=0x104023570, unit=0x10402bc38, select_lex=0x10402b560) at sql_select.cc:2517
#3  0x00000001001be5c4 in mysql_explain_union (thd=0x101836428, unit=0x10402bc38, result=0x104023570) at sql_select.cc:16824
#4  0x00000001001c0d12 in select_describe (join=0x1018c2838, need_tmp_table=false, need_order=false, distinct=false, message=0x10066b7c6 "No tables used") at sql_select.cc:16765
#5  0x00000001001c12e9 in JOIN::exec (this=0x1018c2838) at sql_select.cc:1782
#6  0x00000001001be082 in mysql_select (thd=0x101836428, rref_pointer_array=0x10402b050, tables=0x0, wild_num=0, fields=@0x10402af88, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0x104023570, unit=0x10402b138, select_lex=0x10402ae80) at sql_select.cc:2517
#7  0x00000001001be5c4 in mysql_explain_union (thd=0x101836428, unit=0x10402b138, result=0x104023570) at sql_select.cc:16824
#8  0x00000001001c0d12 in select_describe (join=0x104023b90, need_tmp_table=false, need_order=false, distinct=false, message=0x0) at sql_select.cc:16765
#9  0x00000001001c1a51 in JOIN::exec (this=0x104023b90) at sql_select.cc:1879
#10 0x00000001001be082 in mysql_select (thd=0x101836428, rref_pointer_array=0x10402a0d8, tables=0x10402ab38, wild_num=0, fields=@0x10402a010, conds=0x10402c560, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200196, result=0x104023570, unit=0x104029ae0, select_lex=0x104029f08) at sql_select.cc:2517
#11 0x00000001001be5c4 in mysql_explain_union (thd=0x101836428, unit=0x104029ae0, result=0x104023570) at sql_select.cc:16824
#12 0x00000001001281c1 in execute_sqlcom_select (thd=0x101836428, all_tables=0x10402ab38) at sql_parse.cc:5067
#13 0x000000010012a8b1 in mysql_execute_command (thd=0x101836428) at sql_parse.cc:2287
#14 0x00000001001d66af in Prepared_statement::execute (this=0x103a078c8, expanded_query=0x1034c6ec0, open_cursor=false) at sql_prepare.cc:3590
#15 0x00000001001db094 in Prepared_statement::execute_loop (this=0x103a078c8, expanded_query=0x1034c6ec0, open_cursor=false, packet=0x0, packet_end=0x0) at sql_prepare.cc:3265
#16 0x00000001001db398 in mysql_sql_stmt_execute (thd=0x101836428) at sql_prepare.cc:2528
#17 0x000000010012a8dc in mysql_execute_command (thd=0x101836428) at sql_parse.cc:2296
#18 0x0000000100133b51 in mysql_parse (thd=0x101836428, inBuf=0x104023438 "execute `stmt`", length=14, found_semicolon=0x1034c89c0) at sql_parse.cc:6013
#19 0x0000000100134acd in dispatch_command (command=COM_QUERY, thd=0x101836428, packet=0x1018ba429 "", packet_length=14) at sql_parse.cc:1254
#20 0x0000000100136121 in do_command (thd=0x101836428) at sql_parse.cc:882

(gdb) print join->select_lex->type
$7 = 0x10066b57f "SUBQUERY"

sql_select.cc::select_describe()::475

	item_list.push_back(new Item_string(real_table->alias,
					    strlen(real_table->alias),
					    cs));
[20 Oct 2010 13:33] Paul Dubois
Noted in 5.1.52, 5.5.7 changelogs.

In prepared-statement mode, EXPLAIN for a SELECT from a derived table
caused a server crash.
[1 Nov 2010 19:01] Bugs System
Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)
[9 Nov 2010 19:47] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)
[13 Nov 2010 16:14] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:40] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)