List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:November 8 2011 12:29pm
Subject:bzr push into mysql-trunk branch (roy.lyseng:3480 to 3481) Bug#13335319
View as plain text  
 3481 Roy Lyseng	2011-11-08
      Bug#13335319: Segmentation fault when analyzing FirstMatch semi-join strategy
      
      There was a segmentation fault inside
      semijoin_firstmatch_loosescan_access_paths() because it was called
      with first_firstmatch_table = MAX_TABLES. The reason for this was that
      first_firstmatch_table was copied from the previous position, then
      the test (outer_corr_tables & pos->first_firstmatch_rtbl) caused
      first_firstmatch_table to be set to MAX_TABLES, but we nevertheless
      called the access path calculation function.
      
      The fix is to always copy first_firstmatch_table from the previous
      position (the old behaviour was using some heuristics that FirstMatch
      would not be considered if FirstMatch was really selected for the
      previous position, but it would be considered if a different strategy
      was select), and to never call the access path calculation function
      if the semi-join tables were dependent on outer tables in
      remaining_tables.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#13335319
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
        Added test case results for bug#13335319
      
      sql/sql_select.cc
        Assigns pos->first_firstmatch_table unconditionally from
        strategy decision from previous position.
        Adds the tables of a semi-join nest to pos->firstmatch_need_tables
        only once per position.
        Assures that semijoin_firstmatch_loosescan_access_paths() is not
        called when semi-join nest is dependent on outer table that is not
        in the join prefix. 

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
      sql/sql_select.cc
 3480 Jorgen Loland	2011-11-08
      WL#5860: Make COST_VECT a properly encapsulated C++ class
            
      This changeset:
       * Changes name of COST_VECT to Cost_estimate
       * Encapsulates variables
       * Merges io_count and io_avg_cost into one variable: io_cost
       * Modifies code that used COST_VECT to use Cost_estimate instead
       * Adds unit tests for the Cost_estimate class
            
      This changeset does NOT change any cost calculations. It is 
      purely a refactoring to an encapsulated C++ class following
      our coding guidelines.
     @ sql/handler.cc
        Refactoring: Move from COST_VECT to Cost_estimate
     @ sql/handler.h
        Refactoring: Make COST_VECT a properly encapsulated C++ class
        with name Cost_estimate
     @ sql/opt_range.cc
        Refactoring: Move from COST_VECT to Cost_estimate
     @ sql/sql_select.cc
        Refactoring: Move from COST_VECT to Cost_estimate
     @ sql/sql_select.h
        Refactoring: Move from COST_VECT to Cost_estimate
     @ sql/table.h
        Refactoring: Move from COST_VECT to Cost_estimate
     @ storage/innobase/handler/ha_innodb.cc
        Refactoring: Move from COST_VECT to Cost_estimate
     @ storage/innobase/handler/ha_innodb.h
        Refactoring: Move from COST_VECT to Cost_estimate
     @ storage/myisam/ha_myisam.cc
        Refactoring: Move from COST_VECT to Cost_estimate
     @ storage/myisam/ha_myisam.h
        Refactoring: Move from COST_VECT to Cost_estimate
     @ unittest/gunit/CMakeLists.txt
        Added gunit test file: cost_estimate
     @ unittest/gunit/cost_estimate-t.cc
        Unit tests for class Cost_estimate

    added:
      unittest/gunit/cost_estimate-t.cc
    modified:
      sql/handler.cc
      sql/handler.h
      sql/opt_range.cc
      sql/sql_select.cc
      sql/sql_select.h
      sql/table.h
      storage/innobase/handler/ha_innodb.cc
      storage/innobase/handler/ha_innodb.h
      storage/myisam/ha_myisam.cc
      storage/myisam/ha_myisam.h
      unittest/gunit/CMakeLists.txt
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2011-11-07 14:00:01 +0000
+++ b/mysql-test/include/subquery_sj.inc	2011-11-08 12:06:01 +0000
@@ -4338,4 +4338,37 @@ DROP TABLE t1, t2;
 
 --echo # End of test for bug#13340270.
 
+--echo #
+--echo # Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+--echo #
+
+CREATE TABLE ot1(a INTEGER);
+
+INSERT INTO ot1 VALUES(1), (2), (3);
+
+CREATE TABLE ot2(a INTEGER);
+
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+
+CREATE TABLE it1(a INTEGER);
+
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+
+CREATE TABLE it2(a INTEGER);
+
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+
+let $query=
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+      ot2.a IN (SELECT a FROM it2);
+
+eval explain $query;
+eval $query;
+
+DROP TABLE ot1, ot2, it1, it2;
+
+--echo # End of test for bug#13335319.
+
 --echo # End of 5.6 tests

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2011-11-08 12:06:01 +0000
@@ -7006,5 +7006,34 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where; FirstMatch(ot1); Using join buffer (Block Nested Loop)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Materialize; Scan
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result	2011-11-08 12:06:01 +0000
@@ -7007,6 +7007,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where; FirstMatch(ot1); Using join buffer (Block Nested Loop)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Materialize; Scan
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2011-11-08 12:06:01 +0000
@@ -7007,6 +7007,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Materialize
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Materialize; Scan
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result	2011-11-08 12:06:01 +0000
@@ -7008,6 +7008,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where; FirstMatch(ot1); Using join buffer (Block Nested Loop)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Materialize; Scan
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2011-11-08 12:06:01 +0000
@@ -7006,5 +7006,34 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	Start temporary
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (Block Nested Loop)
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result	2011-11-08 12:06:01 +0000
@@ -7007,6 +7007,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	Start temporary
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (Block Nested Loop)
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2011-11-08 12:06:01 +0000
@@ -7007,6 +7007,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where; Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2011-11-08 12:06:01 +0000
@@ -7008,6 +7008,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	Start temporary
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (Block Nested Loop)
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2011-11-08 12:06:01 +0000
@@ -7007,6 +7007,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	Start temporary
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (Block Nested Loop)
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result	2011-11-08 12:06:01 +0000
@@ -7008,6 +7008,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	Start temporary
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (Block Nested Loop)
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2011-11-08 12:06:01 +0000
@@ -7008,6 +7008,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where; Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2011-11-08 12:06:01 +0000
@@ -7009,6 +7009,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	Start temporary
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (Block Nested Loop)
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2011-11-08 12:06:01 +0000
@@ -7007,5 +7007,34 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	Start temporary
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (Block Nested Loop)
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result	2011-11-08 12:06:01 +0000
@@ -7008,6 +7008,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	Start temporary
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (Block Nested Loop)
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2011-11-08 12:06:01 +0000
@@ -7008,6 +7008,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where; Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2011-11-08 12:06:01 +0000
@@ -7009,6 +7009,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	Start temporary
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (Block Nested Loop)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (Block Nested Loop)
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2011-11-08 12:06:01 +0000
@@ -7006,5 +7006,34 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (Block Nested Loop)
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Materialize
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Materialize
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result	2011-11-08 12:06:01 +0000
@@ -7007,6 +7007,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (Block Nested Loop)
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Materialize
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Materialize
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2011-11-08 12:06:01 +0000
@@ -7007,6 +7007,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Materialize
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Materialize; Scan
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result	2011-11-08 12:06:01 +0000
@@ -7008,6 +7008,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (Block Nested Loop)
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	4	Materialize
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	5	Materialize
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2011-11-08 12:06:01 +0000
@@ -7083,5 +7083,34 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (Block Nested Loop)
+3	SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	5	
+2	SUBQUERY	it1	ALL	NULL	NULL	NULL	NULL	4	
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2011-11-08 12:06:01 +0000
@@ -7018,5 +7018,34 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (Block Nested Loop)
+3	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	5	Using where
+2	DEPENDENT SUBQUERY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result	2011-11-08 12:06:01 +0000
@@ -7019,6 +7019,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (Block Nested Loop)
+3	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	5	Using where
+2	DEPENDENT SUBQUERY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2011-11-08 12:06:01 +0000
@@ -7019,6 +7019,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where
+3	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	5	Using where
+2	DEPENDENT SUBQUERY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result	2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result	2011-11-08 12:06:01 +0000
@@ -7020,6 +7020,35 @@ o
 o
 DROP TABLE t1, t2;
 # End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (Block Nested Loop)
+3	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	5	Using where
+2	DEPENDENT SUBQUERY	it1	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a	a
+1	1
+3	1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-11-08 11:37:54 +0000
+++ b/sql/sql_select.cc	2011-11-08 12:06:01 +0000
@@ -15874,9 +15874,7 @@ void Optimize_table_order::advance_sj_st
     pos->dups_producing_tables= pos[-1].dups_producing_tables;
 
     // FirstMatch
-    pos->first_firstmatch_table=
-      (pos[-1].sj_strategy == SJ_OPT_FIRST_MATCH) ?
-      MAX_TABLES : pos[-1].first_firstmatch_table;
+    pos->first_firstmatch_table= pos[-1].first_firstmatch_table;
     pos->first_firstmatch_rtbl= pos[-1].first_firstmatch_rtbl;
     pos->firstmatch_need_tables= pos[-1].firstmatch_need_tables;
 
@@ -15898,7 +15896,25 @@ void Optimize_table_order::advance_sj_st
   }
   
   table_map handled_by_fm_or_ls= 0;
-  /* FirstMatch Strategy */
+  /*
+    FirstMatch Strategy
+    ===================
+
+    FirstMatch requires that all dependent outer tables are in the join prefix.
+    (see "FirstMatch strategy" above setup_semijoin_dups_elimination()).
+    The execution strategy will handle multiple semi-join nests correctly,
+    and the optimizer will pick execution strategy according to these rules:
+    - If tables from multiple semi-join nests are intertwined, they will
+      be processed as one FirstMatch evaluation.
+    - If tables from each semi-join nest are grouped together, each semi-join
+      nest is processed as one FirstMatch evaluation.
+
+    Example: Let's say we have an outer table ot and two semi-join nests with
+    two tables each: it11 and it12, and it21 and it22.
+
+    Intertwined tables: ot - FM(it11 - it21 - it12 - it22)
+    Grouped tables: ot - FM(it11 - it12) - FM(it21 - it22)
+  */
   if (emb_sj_nest &&
       thd->optimizer_switch_flag(OPTIMIZER_SWITCH_FIRSTMATCH))
   {
@@ -15922,12 +15938,15 @@ void Optimize_table_order::advance_sj_st
     {
       /* Start tracking potential FirstMatch range */
       pos->first_firstmatch_table= idx;
-      pos->firstmatch_need_tables= sj_inner_tables;
+      pos->firstmatch_need_tables= 0;
       pos->first_firstmatch_rtbl= remaining_tables;
     }
 
     if (pos->first_firstmatch_table != MAX_TABLES)
     {
+      /* Record that we need all of this semi-join's inner tables */
+      pos->firstmatch_need_tables|= sj_inner_tables;
+
       if (outer_corr_tables & pos->first_firstmatch_rtbl)
       {
         /*
@@ -15936,13 +15955,7 @@ void Optimize_table_order::advance_sj_st
         */
         pos->first_firstmatch_table= MAX_TABLES;
       }
-      else
-      {
-        /* Record that we need all of this semi-join's inner tables, too */
-        pos->firstmatch_need_tables|= sj_inner_tables;
-      }
-    
-      if (!(pos->firstmatch_need_tables & remaining_tables))
+      else if (!(pos->firstmatch_need_tables & remaining_tables))
       {
         // Got a complete FirstMatch range. Calculate access paths and cost
         double cost, rowcount;

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3480 to 3481) Bug#13335319Roy Lyseng11 Nov