MySQL Bugs: #43977: Nested queries / Subqueries with outer joins produce errors

Bug #43977	Nested queries / Subqueries with outer joins produce errors
Submitted:	31 Mar 2009 8:44	Modified:	13 Nov 2009 11:39
Reporter:	Jonathan Frazier	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	6.0.10,6.0-bzr	OS:	Any (FreeBSD, Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	optimizer_switch, outerjoin, semijoin, subquery

Description:
Large subqueries produce random error codes:

#2013 - Lost connection to MySQL server during query
#2003 - Can't connect to MySQL server on '127.0.0.1' (61)
#2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)

090331  4:18:43 - mysqld got signal 11 ;
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=33554432
read_buffer_size=524288
max_used_connections=8
max_threads=151
thread_count=7
connection_count=7
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 265681 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

090331 04:18:43 mysqld_safe mysqld restarted
InnoDB: The log sequence number in ibdata files does not match  
InnoDB: the log sequence number in the ib_logfiles!
090331  4:18:44  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite  
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 107279888, file name ./mysql-bin.000014
090331  4:18:44  InnoDB: Started; log sequence number 0 36549503
090331  4:18:44 [Note] Event Scheduler: Loaded 0 events
090331  4:18:44 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '6.0.10-alpha'  socket: '/tmp/mysql.sock'  port: 3306  FreeBSD port: mysql-server-6.0.10

How to repeat:
SELECT q.questionid, q.title, q.answer, q.workflowstatus FROM ikm_questions q WHERE q.questionid in ( 

SELECT distinct q.questionid FROM ikm_categories c, ikm_categoryassociations a, ikm_catassoc_history ca, (ikm_question_auth qa RIGHT JOIN ikm_questions q ON qa.questionid = q.questionid) WHERE a.categoryid='2' AND a.questionid = q.questionid AND c.categoryid = a.categoryid AND a.questionid = ca.questionid AND ca.categoryid = a.categoryid AND q.visible=1 AND ( ((q.startdate < '2009-03-31 02:00:07') AND ((q.enableexpiry = 1) AND q.expirydate > '2009-03-31 02:00:07')) OR ((q.startdate < '2009-03-31 02:00:07') AND (q.enableexpiry = 0)) ) AND a.categoryid IN ('2', '3', '5', '4', '6', '7') AND ((qa.groupid IS NULL)) 
) 

ORDER BY q.sortorder DESC,q.lastupdated DESC, q.title ASC LIMIT 0, 20

Both queries work on their own.  When nested, fails with above error codes.  This query does not fail in 5.x - 6.0.9.

Suggested fix:
Unknown

Table structures for query as displayed for reproduction purposes can be found here:  http://rafb.net/p/Zktoz128.html

Thank you for the report.

Verified as described.

Backtrace:

Thread 1 (process 8693):
#0  0x002ce402 in __kernel_vsyscall ()
#1  0x0046264f in pthread_kill () from /lib/libpthread.so.0
#2  0x08825bd7 in my_write_core (sig=11) at stacktrace.c:309
#3  0x082bf4ee in handle_segfault (sig=11) at mysqld.cc:2693
#4  <signal handler called>
#5  0x0834e061 in optimize_semijoin_nests (join=0xb3b11d8, all_table_map=63) at sql_select.cc:4542
#6  0x08359f4e in make_join_statistics (join=0xb3b11d8, tables_arg=0xb28e6d8, conds=0xb3bb6d0, keyuse_array=0xb3b5f04) at sql_select.cc:4429
#7  0x08367ba0 in JOIN::optimize (this=0xb3b11d8) at sql_select.cc:1617
#8  0x0836f764 in mysql_select (thd=0xb291a68, rref_pointer_array=0xb292da4, tables=0xb28e6d8, wild_num=0, fields=@0xb292d34, conds=0xb28cfa0, og_num=3, order=0xb28d140, group=0x0, having=0x0, 
    proc_param=0x0, select_options=2147764736, result=0xb28d400, unit=0xb292a08, select_lex=0xb292ca0) at sql_select.cc:3048
#9  0x0836fb12 in handle_select (thd=0xb291a68, lex=0xb2929ac, result=0xb28d400, setup_tables_done_option=0) at sql_select.cc:314
#10 0x082ce8af in execute_sqlcom_select (thd=0xb291a68, all_tables=0xb28e6d8) at sql_parse.cc:4828
#11 0x082d4495 in mysql_execute_command (thd=0xb291a68) at sql_parse.cc:2148
#12 0x082dd1a5 in mysql_parse (thd=0xb291a68, 
    inBuf=0xb28db30 "SELECT q.questionid, q.title, q.answer, q.workflowstatus FROM ikm_questions q WHERE\nq.questionid in ( \nSELECT distinct q.questionid FROM ikm_categories c, ikm_categoryassociations a,\nikm_catassoc_hist"..., length=794, found_semicolon=0xad086f20) at sql_parse.cc:5843
#13 0x082ddbe7 in dispatch_command (command=COM_QUERY, thd=0xb291a68, 
    packet=0xb2aa471 "SELECT q.questionid, q.title, q.answer, q.workflowstatus FROM ikm_questions q WHERE\nq.questionid in ( \nSELECT distinct q.questionid FROM ikm_categories c, ikm_categoryassociations a,\nikm_catassoc_hist"..., packet_length=795) at sql_parse.cc:1057
#14 0x082def4b in do_command (thd=0xb291a68) at sql_parse.cc:739
#15 0x082ccc5f in handle_one_connection (arg=0xb291a68) at sql_connect.cc:1146
#16 0x0045fbd4 in start_thread () from /lib/libpthread.so.0
#17 0x003b74fe in clone () from /lib/libc.so.6

test case

Attachment: bug43977.test (application/octet-stream, text), 3.96 KiB.

Changed synopsis to more accurately reflect the bug nature

Optimizer_switch, optimizer_use_mrr, and engine_condition_pushdown values did not have an affect on the presence of this bug in my testing.

Problem goes away when I run with:

set optimizer_switch='default,semijoin=off';

The other indication of semijoin being the culprit, is that the core dump occurs inside optimize_semijoin_nests()

I am specializing in core dumps in optimize_semijoin_nests ;-)

Bug#42353 is likely a duplicate of this bug.

Bug#45219 is likely a duplicate of this bug.

Bug#45933 is likely a duplicate of this bug.

This is a duplicate of Bug#46692.  It has been verified that a fix for that bug fixes this issue too.