MySQL Bugs: #42740: crash in optimize_semijoin

Bug #42740	crash in optimize_semijoin_nests
Submitted:	10 Feb 2009 21:06	Modified:	20 Nov 2010 23:12
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	6.0.10-debug	OS:	Any (MS Windows, Linux)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
006E60E0    mysqld.exe!optimize_semijoin_nests()[sql_select.cc:4527]
006E5AC5    mysqld.exe!make_join_statistics()[sql_select.cc:4425]
006DDBBA    mysqld.exe!JOIN::optimize()[sql_select.cc:1619]
006E2653    mysqld.exe!mysql_select()[sql_select.cc:3044]
0070E914    mysqld.exe!mysql_explain_union()[sql_select.cc:21792]
0069CB9C    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4734]
00695AC6    mysqld.exe!mysql_execute_command()[sql_parse.cc:2063]
0069ED33    mysqld.exe!mysql_parse()[sql_parse.cc:5751]
00693E48    mysqld.exe!dispatch_command()[sql_parse.cc:1009]
006936C9    mysqld.exe!do_command()[sql_parse.cc:691]
007BC4E9    mysqld.exe!handle_one_connection()[sql_connect.cc:1146]
008A9C48    mysqld.exe!pthread_start()[my_winthread.c:61]
00C10891    mysqld.exe!_callthreadstartex()[threadex.c:348]
00C10832    mysqld.exe!_threadstartex()[threadex.c:331]
7D4DFFF1    kernel32.dll!FlsSetValue()

How to repeat:
will make testcase later.

testcase:

drop table if exists `t1`,`t2`;
create table `t1` (`c6` timestamp,key (`c6`)) engine=innodb;
create table `t2` (`c2` double) engine=innodb;
explain select 1 from `t2`
where `c2` = any (select log10(null) from `t1` where `c6` <null)  ;

Thank you for the report.

Verified as described.

Backtrace from Linux:

Thread 1 (process 4050):
#0  0x002ce402 in __kernel_vsyscall ()
#1  0x0046264f in pthread_kill () from /lib/libpthread.so.0
#2  0x0880e6e3 in my_write_core (sig=11) at stacktrace.c:309
#3  0x082ba638 in handle_segfault (sig=11) at mysqld.cc:2685
#4  <signal handler called>
#5  0x083482c7 in optimize_semijoin_nests (join=0xa9accd8, all_table_map=3) at sql_select.cc:4527
#6  0x08354258 in make_join_statistics (join=0xa9accd8, tables=0x0, conds=0xa9b6988, keyuse_array=0xa9b1a04) at sql_select.cc:4425
#7  0x08361e6c in JOIN::optimize (this=0xa9accd8) at sql_select.cc:1618
#8  0x08369a0c in mysql_select (thd=0xa916800, rref_pointer_array=0xa917b24, tables=0xa97cd48, wild_num=0, fields=@0xa917ab4, conds=0xa97de10, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2147764740, result=0xa97df60, unit=0xa917788, select_lex=0xa917a20) at sql_select.cc:3044
#9  0x0836458e in mysql_explain_union (thd=0xa916800, unit=0xa917788, result=0xa97df60) at sql_select.cc:21792
#10 0x082c9605 in execute_sqlcom_select (thd=0xa916800, all_tables=0xa97cd48) at sql_parse.cc:4734
#11 0x082cf2ba in mysql_execute_command (thd=0xa916800) at sql_parse.cc:2063
#12 0x082d80b5 in mysql_parse (thd=0xa916800, inBuf=0xa97c978 "explain select 1 from `t2`\nwhere `c2` = any (select log10(null) from `t1` where `c6` <null)", length=91, found_semicolon=0xa92e1f20)
    at sql_parse.cc:5751
#13 0x082d8af7 in dispatch_command (command=COM_QUERY, thd=0xa916800, packet=0xa970769 "", packet_length=93) at sql_parse.cc:1009
#14 0x082d9e5b in do_command (thd=0xa916800) at sql_parse.cc:691
#15 0x082c7d6b in handle_one_connection (arg=0xa916800) at sql_connect.cc:1146
#16 0x0045fbd4 in start_thread () from /lib/libpthread.so.0
#17 0x003b74fe in clone () from /lib/libc.so.6

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/69966

2730 Sergey Petrunia	2009-03-21
      BUG#42740: crash in optimize_semijoin_nests
      - Run pull_out_semijoin_tables() after range analysis, not before it. We need to
        do it in this order because range analysis may mark tables as constant, and then
        pull_out_semijoin_nests() will remove semi-join nests that contain only constant 
        tables. This removal operation is crucial as setup_sj_materialization() cannot deal 
        with semi-join nests that have only constant tables.

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/71886

2734 Sergey Petrunia	2009-04-11
      BUG#42740: Pushbuild fixes

Pushed into 6.0.11-alpha (revid:sergefp@mysql.com-20090417211236-fy28y9o1w8p4ic9m) (version source revid:igor@mysql.com-20090411171436-xmgkfgimdkzukzzm) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 6.0.11 changelog.

Use of semijoin optimization could cause a server crash.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

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)

Noted in 5.6.1 changelog.

Noted in 5.6.1 changelog.

Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.