List:Commits« Previous MessageNext Message »
From:Tor Didriksen Date:May 12 2010 2:12pm
Subject:bzr push into mysql-next-mr-bugfixing branch (tor.didriksen:3168 to 3169)
Bug#48868
View as plain text  
 3169 Tor Didriksen	2010-05-12
      Bug #48868 Left outer join in subquery causes segmentation fault in make_join_select
      
      Backport of tor.didriksen@stripped
      
      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().

    modified:
      mysql-test/r/subselect_sj.result
      mysql-test/r/subselect_sj_jcl6.result
      mysql-test/t/subselect_sj.test
      sql/sql_select.cc
 3168 Sergey Glukhov	2010-05-12
      Bug#21317 SHOW CREATE DATABASE does not obey to lower_case_table_names
      preserve lettercase for DB name as it specified in 'show create'
      (backport to next-mr)
     @ mysql-test/r/lowercase_table.result
        test case
     @ mysql-test/t/lowercase_table.test
        test case
     @ sql/mysql_priv.h
        renamed check_db_name() func to check_and_convert_db_name,
        added new parameter 'preserve_lettercase' to this func.
     @ sql/sql_db.cc
        renamed check_db_name() func to check_and_convert_db_name
     @ sql/sql_parse.cc
        renamed check_db_name() func to check_and_convert_db_name
     @ sql/sql_show.cc
        preserve lettercase for DB name as it specified in 'show create'
     @ sql/sql_yacc.yy
        renamed check_db_name() func to check_and_convert_db_name
     @ sql/table.cc
        renamed check_db_name() func to check_and_convert_db_name

    modified:
      mysql-test/r/lowercase_table.result
      mysql-test/t/lowercase_table.test
      sql/mysql_priv.h
      sql/sql_db.cc
      sql/sql_parse.cc
      sql/sql_show.cc
      sql/sql_yacc.yy
      sql/table.cc
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2010-05-07 17:44:52 +0000
+++ b/mysql-test/r/subselect_sj.result	2010-05-12 14:12:01 +0000
@@ -206,6 +206,14 @@ drop table t10, t11, t12;
 CREATE TABLE t1 (i INTEGER);
 CREATE TABLE t2 (i INTEGER);
 CREATE TABLE t3 (i INTEGER);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	0	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -215,6 +223,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	0	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -224,6 +240,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	0	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -235,6 +259,14 @@ i
 INSERT INTO t3 VALUES (0);
 DELETE FROM t3;
 INSERT INTO t2 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	1	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -244,6 +276,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	1	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -253,6 +293,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	1	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -264,6 +312,14 @@ i
 INSERT INTO t3 VALUES (0);
 DELETE FROM t3;
 INSERT INTO t2 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	2	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -273,6 +329,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	2	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -282,6 +346,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	2	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -295,6 +367,14 @@ DELETE FROM t3;
 INSERT INTO t2 VALUES (0);
 DELETE FROM t2;
 INSERT INTO t1 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	0	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -304,6 +384,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	0	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -313,6 +401,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	0	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -324,6 +420,14 @@ i
 INSERT INTO t3 VALUES (0);
 DELETE FROM t3;
 INSERT INTO t2 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	1	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -333,6 +437,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	1	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -342,6 +454,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	1	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -353,6 +473,14 @@ i
 INSERT INTO t3 VALUES (0);
 DELETE FROM t3;
 INSERT INTO t2 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	2	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -362,6 +490,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	2	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -371,6 +507,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	2	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -384,6 +528,14 @@ DELETE FROM t3;
 INSERT INTO t2 VALUES (0);
 DELETE FROM t2;
 INSERT INTO t1 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	0	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -393,6 +545,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	0	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -402,6 +562,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	0	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -413,6 +581,14 @@ i
 INSERT INTO t3 VALUES (0);
 DELETE FROM t3;
 INSERT INTO t2 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	1	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -422,6 +598,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	1	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -431,6 +615,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	1	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -442,6 +634,14 @@ i
 INSERT INTO t3 VALUES (0);
 DELETE FROM t3;
 INSERT INTO t2 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	2	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -451,6 +651,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	2	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -460,6 +668,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	2	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -474,6 +690,23 @@ INSERT INTO t2 VALUES (0);
 DELETE FROM t2;
 INSERT INTO t1 VALUES (0);
 DROP TABLE t1, t2, t3;
+#
+# Bug#48868: Left outer join in subquery causes segmentation fault in
+#            make_join_select.
+#
+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);
+i
+1
+DROP TABLE t1, t2, t3;
 
 BUG#37120 optimizer_switch allowable values not according to specification
 

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2010-05-07 17:44:52 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2010-05-12 14:12:01 +0000
@@ -210,6 +210,14 @@ drop table t10, t11, t12;
 CREATE TABLE t1 (i INTEGER);
 CREATE TABLE t2 (i INTEGER);
 CREATE TABLE t3 (i INTEGER);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	0	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -219,6 +227,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	0	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -228,6 +244,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	0	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -239,6 +263,14 @@ i
 INSERT INTO t3 VALUES (0);
 DELETE FROM t3;
 INSERT INTO t2 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	1	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -248,6 +280,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	1	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -257,6 +297,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	1	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -268,6 +316,14 @@ i
 INSERT INTO t3 VALUES (0);
 DELETE FROM t3;
 INSERT INTO t2 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	2	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -277,6 +333,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	2	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -286,6 +350,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+0	2	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -299,6 +371,14 @@ DELETE FROM t3;
 INSERT INTO t2 VALUES (0);
 DELETE FROM t2;
 INSERT INTO t1 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	0	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -308,6 +388,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	0	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -317,6 +405,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	0	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -328,6 +424,14 @@ i
 INSERT INTO t3 VALUES (0);
 DELETE FROM t3;
 INSERT INTO t2 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	1	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -337,6 +441,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	1	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -346,6 +458,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	1	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -357,6 +477,14 @@ i
 INSERT INTO t3 VALUES (0);
 DELETE FROM t3;
 INSERT INTO t2 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	2	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -366,6 +494,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	2	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -375,6 +511,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+1	2	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -388,6 +532,14 @@ DELETE FROM t3;
 INSERT INTO t2 VALUES (0);
 DELETE FROM t2;
 INSERT INTO t1 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	0	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -397,6 +549,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	0	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -406,6 +566,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	0	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -417,6 +585,14 @@ i
 INSERT INTO t3 VALUES (0);
 DELETE FROM t3;
 INSERT INTO t2 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	1	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -426,6 +602,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	1	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -435,6 +619,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	1	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -446,6 +638,14 @@ i
 INSERT INTO t3 VALUES (0);
 DELETE FROM t3;
 INSERT INTO t2 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	2	0
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -455,6 +655,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (2);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	2	1
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -464,6 +672,14 @@ i
 SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
 i
 INSERT INTO t3 VALUES (1);
+SELECT (SELECT COUNT(*) from t1),
+(SELECT COUNT(*) from t2),
+(SELECT COUNT(*) from t3);
+(SELECT COUNT(*) from t1)	(SELECT COUNT(*) from t2)	(SELECT COUNT(*) from t3)
+2	2	2
+SELECT * FROM t1 WHERE (11) IN 
+(SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+i
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -478,6 +694,23 @@ INSERT INTO t2 VALUES (0);
 DELETE FROM t2;
 INSERT INTO t1 VALUES (0);
 DROP TABLE t1, t2, t3;
+#
+# Bug#48868: Left outer join in subquery causes segmentation fault in
+#            make_join_select.
+#
+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);
+i
+1
+DROP TABLE t1, t2, t3;
 
 BUG#37120 optimizer_switch allowable values not according to specification
 

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2010-05-07 12:19:46 +0000
+++ b/mysql-test/t/subselect_sj.test	2010-05-12 14:12:01 +0000
@@ -111,11 +111,16 @@ while ($i)
        let $k=3;
        while ($k)
        {
-#        TODO: LEFT JOIN queries are disabled due to Bug#48868
-#        SELECT * FROM t1 WHERE (11) IN 
+         SELECT (SELECT COUNT(*) from t1),
+                (SELECT COUNT(*) from t2),
+                (SELECT COUNT(*) from t3);
+#        TODO: This still crashes when t1 has two rows, t2 and t3 are empty.
+#        SELECT * FROM t1 WHERE (t1.i) IN 
 #          (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
          SELECT * FROM t1 WHERE (11) IN 
-          (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
+           (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+         SELECT * FROM t1 WHERE (11) IN 
+           (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
          SELECT * FROM t1 WHERE (11) IN 
            (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
          SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
@@ -132,6 +137,24 @@ while ($i)
 }
 DROP TABLE t1, t2, t3;
 
+--echo #
+--echo # Bug#48868: Left outer join in subquery causes segmentation fault in
+--echo #            make_join_select.
+--echo #
+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);
+
+DROP TABLE t1, t2, t3;
+
 --echo
 --echo BUG#37120 optimizer_switch allowable values not according to specification
 --echo

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-05-11 16:56:38 +0000
+++ b/sql/sql_select.cc	2010-05-12 14:12:01 +0000
@@ -8549,7 +8549,7 @@ JOIN::make_simple_join(JOIN *parent, TAB
   join_tab->cache_select= 0;
   join_tab->table=tmp_table;
   join_tab->select=0;
-  join_tab->select_cond=0;
+  join_tab->select_cond= NULL;
   join_tab->quick=0;
   join_tab->type= JT_ALL;			/* Map through all records */
   join_tab->keys.init();
@@ -8822,6 +8822,111 @@ make_outerjoin_info(JOIN *join)
   DBUG_VOID_RETURN;
 }
 
+static bool extend_select_cond(JOIN_TAB *cond_tab, COND *tmp_cond)
+{
+  DBUG_ENTER("extend_select_cond");
+
+  COND *new_cond= !cond_tab->select_cond ? tmp_cond :
+    new Item_cond_and(cond_tab->select_cond, tmp_cond);
+  cond_tab->set_select_cond(new_cond, __LINE__);
+  if (!cond_tab->select_cond)
+    DBUG_RETURN(1);
+  cond_tab->select_cond->update_used_tables();
+  cond_tab->select_cond->quick_fix_field();
+  if (cond_tab->select)
+    cond_tab->select->cond= cond_tab->select_cond; 
+
+  DBUG_RETURN(0);
+}
+
+
+/**
+   Local helper function for make_join_select().
+
+   Push down conditions from all on expressions.
+   Each of these conditions are guarded by a variable
+   that turns if off just before null complemented row for
+   outer joins is formed. Thus, the condition from an
+   'on expression' are guaranteed not to be checked for
+   the null complemented row.
+*/ 
+static bool pushdown_on_conditions(JOIN* join, JOIN_TAB *last_tab)
+{
+  DBUG_ENTER("pushdown_on_conditions");
+
+  /* First push down constant conditions from on expressions */
+  for (JOIN_TAB *join_tab= join->join_tab+join->const_tables;
+       join_tab < join->join_tab+join->tables ; join_tab++)
+  {
+    if (*join_tab->on_expr_ref)
+    {
+      JOIN_TAB *cond_tab= join_tab->first_inner;
+      COND *tmp_cond= make_cond_for_table(*join_tab->on_expr_ref,
+                                          join->const_table_map,
+                                          (table_map) 0, 0);
+      if (!tmp_cond)
+        continue;
+      tmp_cond= new Item_func_trig_cond(tmp_cond, &cond_tab->not_null_compl);
+      if (!tmp_cond)
+        DBUG_RETURN(1);
+      tmp_cond->quick_fix_field();
+
+      if (extend_select_cond(cond_tab, tmp_cond))
+        DBUG_RETURN(1);
+    }       
+  }
+
+  JOIN_TAB *first_inner_tab= last_tab->first_inner; 
+
+  /* Push down non-constant conditions from on expressions */
+  while (first_inner_tab && first_inner_tab->last_inner == last_tab)
+  {  
+    /* 
+       Table last_tab is the last inner table of an outer join.
+       An on expression is always attached to it.
+    */     
+    COND *on_expr= *first_inner_tab->on_expr_ref;
+
+    table_map used_tables= (join->const_table_map |
+                            OUTER_REF_TABLE_BIT | RAND_TABLE_BIT);
+    for (JOIN_TAB *join_tab= join->join_tab+join->const_tables;
+         join_tab <= last_tab ; join_tab++)
+    {
+      table_map current_map= join_tab->table->map;
+      used_tables|= current_map;
+      COND *tmp_cond= make_cond_for_table(on_expr, used_tables, current_map, 0);
+      if (!tmp_cond)
+        continue;
+
+      JOIN_TAB *cond_tab=
+        join_tab < first_inner_tab ? first_inner_tab : join_tab;
+      /*
+        First add the guards for match variables of
+        all embedding outer join operations.
+      */
+      if (!(tmp_cond= add_found_match_trig_cond(cond_tab->first_inner,
+                                                tmp_cond,
+                                                first_inner_tab)))
+        DBUG_RETURN(1);
+      /* 
+         Now add the guard turning the predicate off for 
+         the null complemented row.
+      */ 
+      tmp_cond= new Item_func_trig_cond(tmp_cond,
+                                        &first_inner_tab->not_null_compl);
+      if (tmp_cond)
+        tmp_cond->quick_fix_field();
+
+      /* Add the predicate to other pushed down predicates */
+      if (extend_select_cond(cond_tab, tmp_cond))
+        DBUG_RETURN(1);
+    }
+    first_inner_tab= first_inner_tab->first_upper;       
+  }
+  DBUG_RETURN(0);
+}
+
+
 static bool make_join_select(JOIN *join, Item *cond)
 {
   THD *thd= join->thd;
@@ -9148,100 +9253,8 @@ static bool make_join_select(JOIN *join,
 	}
       }
       
-      /* 
-        Push down conditions from all on expressions.
-        Each of these conditions are guarded by a variable
-        that turns if off just before null complemented row for
-        outer joins is formed. Thus, the condition from an
-        'on expression' are guaranteed not to be checked for
-        the null complemented row.
-      */ 
-      JOIN_TAB *last_tab;
-      /* First push down constant conditions from on expressions */
-      for (JOIN_TAB *join_tab= join->join_tab+join->const_tables;
-           join_tab < join->join_tab+join->tables ; join_tab++)
-      {
-        if (*join_tab->on_expr_ref)
-        {
-          JOIN_TAB *cond_tab= join_tab->first_inner;
-          COND *tmp= make_cond_for_table(*join_tab->on_expr_ref,
-                                         join->const_table_map,
-                                         (table_map) 0, 0);
-          if (!tmp)
-            continue;
-            //goto end;
-          tmp= new Item_func_trig_cond(tmp, &cond_tab->not_null_compl);
-          if (!tmp)
-            DBUG_RETURN(1);
-          tmp->quick_fix_field();
-          COND *new_cond= !cond_tab->select_cond ? tmp :
-            new Item_cond_and(cond_tab->select_cond, tmp);
-          cond_tab->set_select_cond(new_cond, __LINE__);
-          if (!cond_tab->select_cond)
-	    DBUG_RETURN(1);
-          cond_tab->select_cond->update_used_tables();
-          cond_tab->select_cond->quick_fix_field();
-          if (cond_tab->select)
-            cond_tab->select->cond= cond_tab->select_cond; 
-        }       
-      }
-
-      /* Push down non-constant conditions from on expressions */
-      last_tab= tab;
-      while (first_inner_tab && first_inner_tab->last_inner == last_tab)
-      {  
-        /* 
-          Table tab is the last inner table of an outer join.
-          An on expression is always attached to it.
-	*/     
-        COND *on_expr= *first_inner_tab->on_expr_ref;
-
-        table_map used_tables2= (join->const_table_map |
-                                 OUTER_REF_TABLE_BIT | RAND_TABLE_BIT);
-	for (tab= join->join_tab+join->const_tables; tab <= last_tab ; tab++)
-        {
-          current_map= tab->table->map;
-          used_tables2|= current_map;
-          COND *tmp_cond= make_cond_for_table(on_expr, used_tables2,
-                                              current_map, 0);
-          if (tmp_cond)
-          {
-            JOIN_TAB *cond_tab= tab < first_inner_tab ? first_inner_tab : tab;
-            /*
-              First add the guards for match variables of
-              all embedding outer join operations.
-	    */
-            if (!(tmp_cond= add_found_match_trig_cond(cond_tab->first_inner,
-                                                     tmp_cond,
-                                                     first_inner_tab)))
-              DBUG_RETURN(1);
-            /* 
-              Now add the guard turning the predicate off for 
-              the null complemented row.
-	    */ 
-            DBUG_PRINT("info", ("Item_func_trig_cond"));
-            tmp_cond= new Item_func_trig_cond(tmp_cond,
-                                              &first_inner_tab->
-                                              not_null_compl);
-            DBUG_PRINT("info", ("Item_func_trig_cond 0x%lx",
-                                (ulong) tmp_cond));
-            if (tmp_cond)
-              tmp_cond->quick_fix_field();
-	    /* Add the predicate to other pushed down predicates */
-            DBUG_PRINT("info", ("Item_cond_and"));
-            COND *new_cond= !cond_tab->select_cond ? tmp_cond :
-              new Item_cond_and(cond_tab->select_cond, tmp_cond);
-            cond_tab->set_select_cond(new_cond, __LINE__);
-            if (!cond_tab->select_cond)
-	      DBUG_RETURN(1);
-            cond_tab->select_cond->update_used_tables();
-            cond_tab->select_cond->quick_fix_field();
-            if (cond_tab->select)
-              cond_tab->select->cond= cond_tab->select_cond; 
-          }              
-        }
-        first_inner_tab= first_inner_tab->first_upper;       
-      }
+      if (pushdown_on_conditions(join, tab))
+        DBUG_RETURN(1);
 
       if (save_used_tables && !(used_tables & 
                                 ~(tab->emb_sj_nest->sj_inner_tables |


Attachment: [text/bzr-bundle] bzr/tor.didriksen@sun.com-20100512141201-1dmgd4slpa6fuzjq.bundle
Thread
bzr push into mysql-next-mr-bugfixing branch (tor.didriksen:3168 to 3169)Bug#48868Tor Didriksen12 May