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

 3343 Roy Lyseng	2011-02-14
      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-02-14 15:01:33 +0000
@@ -3504,3 +3504,25 @@ 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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+
+let $query=
+SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+             FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE a, b;
+
+--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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	b	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 b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	b	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 b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	b	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 b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	b	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 b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	b	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 b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	b	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 b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	b	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(b)
+SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	b	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(b)
+SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	b	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(b)
+SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	b	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 b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	b	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 b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	b	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 b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	b	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 b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	b	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 b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	b	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 b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	b	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	a	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 b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	b	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	a	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 b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	b	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	a	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 b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 15:01:33 +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 a (f1 INTEGER NOT NULL) ENGINE=MyISAM;
+CREATE TABLE b (f1 INTEGER NOT NULL, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO a VALUES (1);
+INSERT INTO b VALUES (1,1), (2,1);
+EXPLAIN SELECT * FROM b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	b	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	a	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 b
+WHERE f2 IN (SELECT a.f1
+FROM a LEFT OUTER JOIN (b AS b1 JOIN b AS b2 ON 1=1) ON 1=1);
+f1	f2
+1	1
+2	1
+DROP TABLE a, b;
+# 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-02-14 11:21:26 +0000
+++ b/sql/sql_select.cc	2011-02-14 15:01:33 +0000
@@ -10920,7 +10920,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-20110214150133-1qivlvud44x53b8h.bundle
Thread
bzr commit into mysql-trunk branch (roy.lyseng:3343) Bug#11766739Roy Lyseng14 Feb
  • Re: bzr commit into mysql-trunk branch (roy.lyseng:3343) Bug#11766739Øystein Grøvlen22 Feb
    • Re: bzr commit into mysql-trunk branch (roy.lyseng:3343) Bug#11766739Roy Lyseng2 Mar