List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:March 3 2011 9:43am
Subject:bzr commit into mysql-trunk branch (roy.lyseng:3347) Bug#11766739
View as plain text  
#At file:///home/rl136806/mysql/repo/mysql-work5/ based on revid:roy.lyseng@stripped

 3347 Roy Lyseng	2011-03-03
      Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
      
      Used to be bug#59919.
      
      A crash occurs when a certain subquery is transformed with a semi join
      and a semi join materialization operation is attempted.
      The subquery contains a nested join structure (an outer join where
      the inner part is itself an inner join).
      Function setup_sj_materialization() tries to lookup the embedding
      semi join nest for an inner table of the subquery. However, it uses
      the pointer tab->table->pos_in_table_list->embedding, pointing to
      an outer join nest, instead of the correct pointer tab->emb_sj_nest.
      When dereferencing this pointer, a crash occurs.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#11766739
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
        Added test results for bug#11766739
      
      sql/sql_select.cc
        In setup_sj_materialization(), replaced use of pointer
        tab->table->pos_in_table_list->embedding with
        tab->emb_sj_nest.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
      sql/sql_select.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2011-01-27 11:38:22 +0000
+++ b/mysql-test/include/subquery_sj.inc	2011-03-03 09:43:14 +0000
@@ -3504,3 +3504,24 @@ deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 
 --echo # End of the test for bug#57623.
+
+--echo #
+--echo # Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+--echo #
+
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+
+let $query=
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+             FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1, t2;
+
+--echo # End of the test for bug#11766739.

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2011-03-03 09:43:14 +0000
@@ -5393,4 +5393,27 @@ a	a	a
 deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
+#
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End materialize
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result	2011-03-03 09:43:14 +0000
@@ -5397,5 +5397,28 @@ a	a	a
 deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
+#
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End materialize
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result	2011-03-03 09:43:14 +0000
@@ -5397,5 +5397,28 @@ a	a	a
 deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
+#
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End materialize
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2011-03-03 09:43:14 +0000
@@ -5392,4 +5392,27 @@ a	a	a
 deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
+#
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End temporary; Using join buffer (BNL, incremental buffers)
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2011-03-03 09:43:14 +0000
@@ -5396,5 +5396,28 @@ a	a	a
 deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
+#
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End temporary; Using join buffer (BNL, incremental buffers)
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2011-03-03 09:43:14 +0000
@@ -5396,5 +5396,28 @@ a	a	a
 deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
+#
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End temporary
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2011-03-03 09:43:14 +0000
@@ -5394,6 +5394,29 @@ deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
 #
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2011-03-03 09:43:14 +0000
@@ -5398,6 +5398,29 @@ deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
 #
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2011-03-03 09:43:14 +0000
@@ -5398,6 +5398,29 @@ deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
 #
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2011-03-03 09:43:14 +0000
@@ -5393,4 +5393,27 @@ a	a	a
 deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
+#
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End temporary; Using join buffer (BNL, incremental buffers)
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result	2011-03-03 09:43:14 +0000
@@ -5397,5 +5397,28 @@ a	a	a
 deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
+#
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End temporary; Using join buffer (BNL, incremental buffers)
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result	2011-03-03 09:43:14 +0000
@@ -5397,5 +5397,28 @@ a	a	a
 deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
+#
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End temporary
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2011-03-03 09:43:14 +0000
@@ -5393,4 +5393,27 @@ a	a	a
 deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
+#
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End materialize
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result	2011-03-03 09:43:14 +0000
@@ -5397,5 +5397,28 @@ a	a	a
 deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
+#
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End materialize
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result	2011-03-03 09:43:14 +0000
@@ -5397,5 +5397,28 @@ a	a	a
 deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
+#
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start materialize
+1	PRIMARY	b2	ALL	NULL	NULL	NULL	NULL	2	End materialize
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2011-03-03 09:43:14 +0000
@@ -5615,4 +5615,27 @@ a	a	a
 deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
+#
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
+2	SUBQUERY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	b2	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2011-03-03 09:43:14 +0000
@@ -5538,4 +5538,27 @@ a	a	a
 deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
+#
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	b2	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result	2011-03-03 09:43:14 +0000
@@ -5542,5 +5542,28 @@ a	a	a
 deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
+#
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	b2	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result	2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result	2011-03-03 09:43:14 +0000
@@ -5542,5 +5542,28 @@ a	a	a
 deallocate prepare s;
 DROP TABLE ot1, ot2, ot3, it1;
 # End of the test for bug#57623.
+#
+# Bug#11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	b1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	b2	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t2
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+DROP TABLE t1, t2;
+# End of the test for bug#11766739.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-03-01 14:57:53 +0000
+++ b/sql/sql_select.cc	2011-03-03 09:43:14 +0000
@@ -10929,7 +10929,7 @@ bool setup_sj_materialization(JOIN_TAB *
 {
   uint i;
   DBUG_ENTER("setup_sj_materialization");
-  TABLE_LIST *emb_sj_nest= tab->table->pos_in_table_list->embedding;
+  TABLE_LIST *emb_sj_nest= tab->emb_sj_nest;
   Semijoin_mat_exec *sjm= emb_sj_nest->sj_mat_exec;
   THD *thd= tab->join->thd;
   /* First the calls come to the materialization function */


Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20110303094314-13ilk7l18hcompvx.bundle
Thread
bzr commit into mysql-trunk branch (roy.lyseng:3347) Bug#11766739Roy Lyseng3 Mar