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
3346 Roy Lyseng 2011-03-01
Bug#11763382: Assertion 'inited=INDEX in sql/handler.h
Used to be bug#56080.
The problem occurs because some TABLE objects are associated with
the optimizer execution structures even after close_thread_tables()
has been called. In turn, this means that final cleanup of some
TABLE objects is done from within THD::cleanup_after_query().
At this point in time, the TABLE objects may already have been
given to another session, meaning that we may end another session's
index or table scan.
The safest solution seems to be to always perform a thorough cleanup
of all execution data structures, including releasing the TABLE
objects, before close_thread_tables() is called. In other words,
this will have to be done at the end of each execution.
However, this is currently not possible in the implementation of
subquery materialization. This part of the optimizer assigns some
objects, including a TABLE object, for the lifetime of the
statement object, and not for each execution. This gives an odd
situation, where some parts of the data structures are set up for
reuse and others are not. Combine this with the fact that the
user may decide to use another strategy for a subsequent
subquery execution, the "permanent" data structures may not even
be used later.
It is therefore reasonable to allocate materialization execution
structures for the lifetime of one execution. It means that the
functions init_permanent() and init_runtime() of class
subselect_hash_sj_engine are combined into one setup() function.
In addition, the function Item_subselect::cleanup() will be called
when the cleanup() function for the underlying query expression
is called, and this function will cleanup and destroy the
materialization data structures, so that the subquery item object
is ready for a new optimization process in the next round of
execution.
However, the EXPLAIN functionality relies on these data
structures being present when explaining a query containing
a materialized subquery. Thus, we have two conflicting
requirements for query cleanup:
- For maximum efficiency we should release resources as early as
possible after execution.
- We cannot release resources that are needed for EXPLAIN queries.
A partial solution to this problem is to split resource cleanup in two:
1. Delete JOIN objects as soon as possible - this makes sense
because TABLE objects are associated with them through JOIN_TAB.
JOIN::destroy() is extended so that associations to joined tables
are removed.
But notice that JOIN objects may be needed for EXPLAIN queries,
so sometimes deleting them must be delayed.
Setting free_join= 0 inside mysql_select() when SELECT_DESCRIBE
is part of select options is evidence of that.
2. Cleanup query expression objects (ie st_select_lex and
st_select_lex_unit) later (in practice at end of query execution).
One example: In mysql_explain_union() the call to unit->cleanup()
was deleted, so now it is done by the general cleanup.
The bug fix does only a limited amount of refactoring in this area.
mysql-test/r/optimizer_debug_sync.result
Results for new test case.
mysql-test/t/optimizer_debug_sync.test
Test case for bug. Requires the debug sync facility, and often fails
without the bug fix.
mysql-test/r/union.result
Change in EXPLAIN EXTENDED output that shows an optimized predicate
in the two query specifications within a UNION in a subquery.
AFAIK, this is similar to how a non-subquery is reported, so the
change is an improvement.
sql/ha_partition.cc
Comment change.
sql/item_subselect.cc
In Item_subselect::cleanup(), "new" engine is always deleted after
an execution.
Item_in_subselect::setup_engine() now builds the materialization
engine for one execution only. Specific arena is no longer needed.
In subselect_union_engine::cleanup(), call to
unit->reinit_exec_mechanism is deleted because it is redundant.
Implementation of cleanup() functions for subclasses of
subselect_engine has been refactored.
subselect_hash_sj_engine::setup() replaces init_permanent() and
init_runtime().
For class subselect_hash_sj_engine, freeing of the temporary
result table is moved from the destructor to ::cleanup().
Some changes necessary because of interface changes.
sql/item_subselect.h
Some cleanup of the subselect_engine classes:
- All virtual functions now marked virtual in all derived classes.
- Functions that returned bool result are now declared accordingly.
- Slight cleanup of private/protected/public performed.
- Improved constructors.
sql/sql_lex.h
Change in friend declaration.
sql/sql_select.cc
Function JOIN::reinit() is renamed to JOIN::reset().
JOIN::destroy() will now delete connection to associated tables.
The st_select_lex_unit object is no longer cleaned up as early as
before, but instead relying on the general cleanup of lex->unit
in mysql_execute_command().
sql/sql_select.h
A few small interface changes.
sql/sql_union.cc
Cleaned up some confusing error handling in st_select_lex_unit::exec()
and st_select_lex::cleanup().
added:
mysql-test/r/optimizer_debug_sync.result
mysql-test/t/optimizer_debug_sync.test
modified:
mysql-test/r/union.result
sql/ha_partition.cc
sql/item_subselect.cc
sql/item_subselect.h
sql/sql_lex.h
sql/sql_select.cc
sql/sql_select.h
sql/sql_union.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 */
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3346 to 3347) Bug#11766739 | Roy Lyseng | 3 Mar |
