Bug #44633 Automatic search depth and nested join's results in server crash - v2
Submitted: 4 May 2009 7:39 Modified: 22 May 2009 13:28
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.79, 5.1.33, 6.0 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any

[4 May 2009 7:39] Shane Bester
followup to bug #38795


How to repeat:
set session optimizer_search_depth=0;
drop table if exists `t1`;
create table `t1` (`a` int) engine=innodb;
insert into `t1` values (1);

select t1.a
from t1 t1
left join t1 t2 on t2.a = t1.a
left join t1 t3 on t3.a = t1.a
left join t1 t4 on t4.a = t1.a
left join (t1 t5
left join (t1 t1_2 
left join t1 t6_2 on t6_2.a = t1_2.a) 
on t1_2.a = t5.a) on t5.a = t1.a
left join t1 t6 on t6.a = t1.a;
[4 May 2009 8:15] Sveta Smirnova
Thank you for the report.

Verified as described.

Backtrace in my Linux env:

Thread 1 (process 24479):
#0  0x002ce402 in __kernel_vsyscall ()
#1  0x0046264f in pthread_kill () from /lib/libpthread.so.0
#2  0x085a6171 in my_write_core (sig=11) at stacktrace.c:310
#3  0x0824cf72 in handle_segfault (sig=11) at mysqld.cc:2536
#4  <signal handler called>
#5  0x003588ac in memcpy () from /lib/libc.so.6
#6  0x082ccf9a in get_best_combination (join=0xa44ef90) at sql_select.cc:5451
#7  0x082da152 in make_join_statistics (join=0xa44ef90, tables_arg=0xa462c78, conds=0x0, keyuse_array=0xa450094) at sql_select.cc:2889
#8  0x082e7846 in JOIN::optimize (this=0xa44ef90) at sql_select.cc:954
#9  0x082ef237 in mysql_select (thd=0xa3e7ca0, rref_pointer_array=0xa3e9138, tables=0xa462c78, wild_num=0, fields=@0xa3e90d4, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2147764736, result=0xa43e328, unit=0xa3e8dd0, select_lex=0xa3e9040) at sql_select.cc:2364
#10 0x082ef615 in handle_select (thd=0xa3e7ca0, lex=0xa3e8d74, result=0xa43e328, setup_tables_done_option=0) at sql_select.cc:268
#11 0x0825c337 in execute_sqlcom_select (thd=0xa3e7ca0, all_tables=0xa462c78) at sql_parse.cc:4982
#12 0x082625f9 in mysql_execute_command (thd=0xa3e7ca0) at sql_parse.cc:2204
#13 0x0826bc10 in mysql_parse (thd=0xa3e7ca0, 
    inBuf=0xa462958 "select t1.a\nfrom t1 t1\nleft join t1 t2 on t2.a = t1.a\nleft join t1 t3 on t3.a = t1.a\nleft join t1 t4 on t4.a = t1.a\nleft join (t1 t5\nleft join (t1 t1_2 \nleft join t1 t6_2 on t6_2.a = t1_2.a) \non t1_2."..., length=255, found_semicolon=0xb2b582fc) at sql_parse.cc:5902
#14 0x0826c84c in dispatch_command (command=COM_QUERY, thd=0xa3e7ca0, 
    packet=0xa42cda9 "select t1.a\nfrom t1 t1\nleft join t1 t2 on t2.a = t1.a\nleft join t1 t3 on t3.a = t1.a\nleft join t1 t4 on t4.a = t1.a\nleft join (t1 t5\nleft join (t1 t1_2 \nleft join t1 t6_2 on t6_2.a = t1_2.a) \non t1_2."..., packet_length=255) at sql_parse.cc:1216
#15 0x0826da6a in do_command (thd=0xa3e7ca0) at sql_parse.cc:857
#16 0x0825a411 in handle_one_connection (arg=0xa3e7ca0) at sql_connect.cc:1115
#17 0x0045fbd4 in start_thread () from /lib/libpthread.so.0
#18 0x003b74fe in clone () from /lib/libc.so.6
[22 May 2009 13:28] Georgi Kodinov
This bug is fixed by the fix for bug #42116.