#At file:///home/rl136806/mysql/repo/mysql-work5/ based on revid:jorgen.loland@stripped
3346 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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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 08:27:44 +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-02-16 09:31:18 +0000
+++ b/sql/sql_select.cc 2011-03-03 08:27:44 +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-20110303082744-28baszi4q1u7s2ra.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk branch (roy.lyseng:3346) Bug#11766739 | Roy Lyseng | 3 Mar |
