MySQL Bugs: #48868: Left outer join in subquery causes segmentation fault in make_join

Bug #48868	Left outer join in subquery causes segmentation fault in make_join_select
Submitted:	18 Nov 2009 15:42	Modified:	22 Nov 2010 0:52
Reporter:	Øystein Grøvlen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Tor Didriksen	CPU Architecture:	Any
Tags:	crash, materialization, optimizer_switch, outerjoin, semijoin, subquery

Description:
NOTE:  This issue can only observed when the proposed fix to Bug#46692 is applied.  Without this fix another seg fault will occur for a broader range of queries.

The following query causes the seg fault:

SELECT * FROM t1 WHERE (11) IN 
  (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);

Note that this problem only occur if all of the following conditions are satisfied:
  1. t1 is not empty.
  2. t2 has exactly 1 row.
  3. t3 is empty.

At least for all other permutations of 0-2 rows in each table, the query succeeds.  Right outer join works in all cases.  

Workaround: Set optimizer_switch='materialization=off' 

Call stack:
#0  0x009cc422 in __kernel_vsyscall ()
#1  0x003c2e93 in __pthread_kill (threadid=3070011248, signo=11)
    at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:64
#2  0x086322b4 in my_write_core (sig=11) at stacktrace.c:309
#3  0x0829afc1 in handle_segfault (sig=11) at mysqld.cc:2765
#4  <signal handler called>
#5  0x0832c382 in make_join_select (join=0x9a54e30, cond=0x9a601e8)
    at sql_select.cc:9132
#6  0x0831a20b in JOIN::optimize (this=0x9a54e30) at sql_select.cc:1720
#7  0x0831e7ae in mysql_select (thd=0x99b28a8, rref_pointer_array=0x99b3e0c, 
    tables=0x997b6b8, wild_num=1, fields=..., conds=0x997d1f0, og_num=0, 
    order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2147748352, result=0x997d348, unit=0x99b3850, 
    select_lex=0x99b3d08) at sql_select.cc:3126
#8  0x08316cf7 in handle_select (thd=0x99b28a8, lex=0x99b37f4, 
    result=0x997d348, setup_tables_done_option=0) at sql_select.cc:308
#9  0x082b566f in execute_sqlcom_select (thd=0x99b28a8, all_tables=0x997b6b8)
    at sql_parse.cc:4961
#10 0x082ac83a in mysql_execute_command (thd=0x99b28a8) at sql_parse.cc:2156
#11 0x082b7671 in mysql_parse (thd=0x99b28a8, 
    inBuf=0x997b508 "SELECT * FROM t1 WHERE (11) IN \n(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i)", length=79, found_semicolon=0xb6fc998c)
    at sql_parse.cc:5975
#12 0x082aa170 in dispatch_command (command=COM_QUERY, thd=0x99b28a8, 
    packet=0x9981789 "SELECT * FROM t1 WHERE (11) IN \n(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i)", packet_length=79) at sql_parse.cc:1076
#13 0x082a9564 in do_command (thd=0x99b28a8) at sql_parse.cc:758
#14 0x082a7d9e in handle_one_connection (arg=0x99b28a8) at sql_connect.cc:1164
#15 0x003bd80e in start_thread (arg=0xb6fca770) at pthread_create.c:300
#16 0x008217ee in clone () at ../sysdeps/unix/sysv/linux/i386/clone.S:130

How to repeat:
CREATE TABLE t1 (i INT);
CREATE TABLE t2 (i INT);
CREATE TABLE t3 (i INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);

SELECT * FROM t1 WHERE (11) IN 
  (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);

DROP TABLE t1, t2, t3;

This issue is also reproducible with the test case below.  It seems the requirement for reproducing the bug with the below queyr is that table t2 has one row.  Sizes of t1 and t3 is irrelevant, it seems.

CREATE TABLE t1 (i INTEGER);
INSERT INTO t1 VALUES (1);	
INSERT INTO t1 VALUES (2);	
CREATE TABLE t2 (i INTEGER);
INSERT INTO t2 VALUES(1);
CREATE TABLE t3 (i INTEGER);
INSERT INTO t3 VALUES (1);	
INSERT INTO t3 VALUES (2);	

SELECT * FROM t1 WHERE (t1.i) IN 
  (SELECT t2.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);

This problem with left outer join in IN-subqueries that surfaces when the left table pulled out because it is constant (one row).  The problem is related to a loop at the end of make_join_select

  for (tab= join->join_tab+join->const_tables; tab <= last_tab ; tab++)

The problem is that the 'tab' loop variable is also the loop variable of an 
outer loop.  There is code below this inner loop that uses tab that gets into 
trouble because of this.  The inner loop is only used for inner tables of outer joins.  Hence the problem only surfaces for outer joins.

This bug is now visible in mysql-6.0-codebase-bugfixing since bug#46692 has now been fixed.

Requesting retriage since previous triage was based on that it was only visible in private branch.

Reassigning this to Tor since he has a fix ready.

A test case in subselect_sj.test has been disabled due to this but.
Please, enable it as part of fixing this bug.

I also suggest adding the query from the above test case to the same loop of subselect_sj.test.  That is,

SELECT * FROM t1 WHERE (t1.i) IN 
  (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);

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

3807 Tor Didriksen	2010-01-05
      Bug #48868 Left outer join in subquery causes segmentation fault in make_join_select
      
      This problem with left outer join in IN-subqueries that surfaced
      when the left table was pulled out because it was constant (one row).
      The problem was related to a loop at the end of make_join_select
      
        for (tab= join->join_tab+join->const_tables; tab <= last_tab ; tab++)
      
      The problem was that the 'tab' loop variable was also the loop
      variable of an outer loop.  There was code below this inner loop
      that used tab that gets into trouble because of this.  The inner
      loop was only used for inner tables of outer joins.  Hence the
      problem only surfaced for outer joins.
      
      Solution: split make_join_select() in two, and put the
      condition-push-down code into a separate utility function.
     @ mysql-test/r/subselect_sj.result
        Add test case result.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case result.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/sql_select.cc
        Split make_join_select() in two, and put the
        condition-push-down code into a separate utility function
        pushdown_conditions().

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

3808 Tor Didriksen	2010-01-05
      Bug #48868 Left outer join in subquery causes segmentation fault in make_join_select
      
      This problem with left outer join in IN-subqueries that surfaced
      when the left table was pulled out because it was constant (one row).
      The problem was related to a loop at the end of make_join_select
      
        for (tab= join->join_tab+join->const_tables; tab <= last_tab ; tab++)
      
      The problem was that the 'tab' loop variable was also the loop
      variable of an outer loop.  There was code below this inner loop
      that used tab that gets into trouble because of this.  The inner
      loop was only used for inner tables of outer joins.  Hence the
      problem only surfaced for outer joins.
      
      Solution: split make_join_select() in two, and put the
      condition-push-down code into a separate utility function.
     @ mysql-test/r/subselect_sj.result
        Add test case result.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case result.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/sql_select.cc
        Split make_join_select() in two, and put the
        condition-push-down code into a separate utility function
        pushdown_conditions().

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

3815 Tor Didriksen	2010-01-07
      Bug #48868 Left outer join in subquery causes segmentation fault in make_join_select
      
      This problem with left outer join in IN-subqueries that surfaced
      when the left table was pulled out because it was constant (one row).
      The problem was related to a loop at the end of make_join_select
      
        for (tab= join->join_tab+join->const_tables; tab <= last_tab ; tab++)
      
      The problem was that the 'tab' loop variable was also the loop
      variable of an outer loop.  There was code below this inner loop
      that used tab that gets into trouble because of this.  The inner
      loop was only used for inner tables of outer joins.  Hence the
      problem only surfaced for outer joins.
      
      Solution: split make_join_select() in two, and put the
      condition-push-down code into a separate utility function.
     @ mysql-test/r/subselect_sj.result
        Add test case result.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case result.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/sql_select.cc
        Split make_join_select() in two, and put the
        condition-push-down code into a separate utility function
        pushdown_conditions().

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

3815 Tor Didriksen	2010-01-08
      Bug #48868 Left outer join in subquery causes segmentation fault in make_join_select
      
      This problem with left outer join in IN-subqueries that surfaced
      when the left table was pulled out because it was constant (one row).
      The problem was related to a loop at the end of make_join_select
      
        for (tab= join->join_tab+join->const_tables; tab <= last_tab ; tab++)
      
      The problem was that the 'tab' loop variable was also the loop
      variable of an outer loop.  There was code below this inner loop
      that used tab that gets into trouble because of this.  The inner
      loop was only used for inner tables of outer joins.  Hence the
      problem only surfaced for outer joins.
      
      Solution: split make_join_select() in two, and put the
      condition-push-down code into a separate utility function.
     @ mysql-test/r/subselect_sj.result
        Add test case result.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case result.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/sql_select.cc
        Split make_join_select() in two, and put the
        condition-push-down code into a separate utility function
        pushdown_on_conditions().

pushed to
bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-codebase-bugfixing/

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100112162328-2sblcul1kl08bbib) (version source revid:guilhem@mysql.com-20100108092756-k0zzf4kvx9b7bh38) (merge vers: 6.0.14-alpha) (pib:15)

Appears only when fix for Bug#46692 is applied, which is in the same release tree. Hence, this bug does not appear in any released version and no changelog entry is needed.

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

3169 Tor Didriksen 2010-05-12
Bug #48868 Left outer join in subquery causes segmentation fault in make_join_select

Backport of tor.didriksen@sun.com-20100108093836-405e9flc3x1egvbt

This problem with left outer join in IN-subqueries that surfaced
when the left table was pulled out because it was constant (one row).
The problem was related to a loop at the end of make_join_select

for (tab= join->join_tab+join->const_tables; tab <= last_tab ; tab++)

The problem was that the 'tab' loop variable was also the loop
variable of an outer loop. There was code below this inner loop
that used tab that gets into trouble because of this. The inner
loop was only used for inner tables of outer joins. Hence the
problem only surfaced for outer joins.

Solution: split make_join_select() in two, and put the
condition-push-down code into a separate utility function.
@ mysql-test/r/subselect_sj.result
Add test case result.
@ mysql-test/r/subselect_sj_jcl6.result
Add test case result.
@ mysql-test/t/subselect_sj.test
Add test case.
@ sql/sql_select.cc
Split make_join_select() in two, and put the
condition-push-down code into a separate utility function
pushdown_on_conditions().

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)

Not in any released 5.6.x version. No changelog entry needed.