MySQL Bugs: #44633: Automatic search depth and nested join's results in server crash

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:	None
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

Description:
followup to bug #38795

mysqld-debug.exe!get_best_combination()[sql_select.cc:5172]
mysqld-debug.exe!make_join_statistics()[sql_select.cc:2783]
mysqld-debug.exe!JOIN::optimize()[sql_select.cc:913]
mysqld-debug.exe!mysql_select()[sql_select.cc:2271]
mysqld-debug.exe!handle_select()[sql_select.cc:257]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2789]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:6267]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1940]
mysqld-debug.exe!do_command()[sql_parse.cc:1628]
mysqld-debug.exe!handle_one_connection()[sql_parse.cc:1234]
mysqld-debug.exe!pthread_start()[my_winthread.c:85]
mysqld-debug.exe!_callthreadstart()[thread.c:295]
mysqld-debug.exe!_threadstart()[thread.c:277]
kernel32.dll!BaseThreadStart()

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;

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

This bug is fixed by the fix for bug #42116.