3481 Roy Lyseng 2011-11-08
Bug#13335319: Segmentation fault when analyzing FirstMatch semi-join strategy
There was a segmentation fault inside
semijoin_firstmatch_loosescan_access_paths() because it was called
with first_firstmatch_table = MAX_TABLES. The reason for this was that
first_firstmatch_table was copied from the previous position, then
the test (outer_corr_tables & pos->first_firstmatch_rtbl) caused
first_firstmatch_table to be set to MAX_TABLES, but we nevertheless
called the access path calculation function.
The fix is to always copy first_firstmatch_table from the previous
position (the old behaviour was using some heuristics that FirstMatch
would not be considered if FirstMatch was really selected for the
previous position, but it would be considered if a different strategy
was select), and to never call the access path calculation function
if the semi-join tables were dependent on outer tables in
remaining_tables.
mysql-test/include/subquery_sj.inc
Added test case for bug#13335319
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
Added test case results for bug#13335319
sql/sql_select.cc
Assigns pos->first_firstmatch_table unconditionally from
strategy decision from previous position.
Adds the tables of a semi-join nest to pos->firstmatch_need_tables
only once per position.
Assures that semijoin_firstmatch_loosescan_access_paths() is not
called when semi-join nest is dependent on outer table that is not
in the join prefix.
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
sql/sql_select.cc
3480 Jorgen Loland 2011-11-08
WL#5860: Make COST_VECT a properly encapsulated C++ class
This changeset:
* Changes name of COST_VECT to Cost_estimate
* Encapsulates variables
* Merges io_count and io_avg_cost into one variable: io_cost
* Modifies code that used COST_VECT to use Cost_estimate instead
* Adds unit tests for the Cost_estimate class
This changeset does NOT change any cost calculations. It is
purely a refactoring to an encapsulated C++ class following
our coding guidelines.
@ sql/handler.cc
Refactoring: Move from COST_VECT to Cost_estimate
@ sql/handler.h
Refactoring: Make COST_VECT a properly encapsulated C++ class
with name Cost_estimate
@ sql/opt_range.cc
Refactoring: Move from COST_VECT to Cost_estimate
@ sql/sql_select.cc
Refactoring: Move from COST_VECT to Cost_estimate
@ sql/sql_select.h
Refactoring: Move from COST_VECT to Cost_estimate
@ sql/table.h
Refactoring: Move from COST_VECT to Cost_estimate
@ storage/innobase/handler/ha_innodb.cc
Refactoring: Move from COST_VECT to Cost_estimate
@ storage/innobase/handler/ha_innodb.h
Refactoring: Move from COST_VECT to Cost_estimate
@ storage/myisam/ha_myisam.cc
Refactoring: Move from COST_VECT to Cost_estimate
@ storage/myisam/ha_myisam.h
Refactoring: Move from COST_VECT to Cost_estimate
@ unittest/gunit/CMakeLists.txt
Added gunit test file: cost_estimate
@ unittest/gunit/cost_estimate-t.cc
Unit tests for class Cost_estimate
added:
unittest/gunit/cost_estimate-t.cc
modified:
sql/handler.cc
sql/handler.h
sql/opt_range.cc
sql/sql_select.cc
sql/sql_select.h
sql/table.h
storage/innobase/handler/ha_innodb.cc
storage/innobase/handler/ha_innodb.h
storage/myisam/ha_myisam.cc
storage/myisam/ha_myisam.h
unittest/gunit/CMakeLists.txt
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2011-11-07 14:00:01 +0000
+++ b/mysql-test/include/subquery_sj.inc 2011-11-08 12:06:01 +0000
@@ -4338,4 +4338,37 @@ DROP TABLE t1, t2;
--echo # End of test for bug#13340270.
+--echo #
+--echo # Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+--echo #
+
+CREATE TABLE ot1(a INTEGER);
+
+INSERT INTO ot1 VALUES(1), (2), (3);
+
+CREATE TABLE ot2(a INTEGER);
+
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+
+CREATE TABLE it1(a INTEGER);
+
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+
+CREATE TABLE it2(a INTEGER);
+
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+
+let $query=
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ ot2.a IN (SELECT a FROM it2);
+
+eval explain $query;
+eval $query;
+
+DROP TABLE ot1, ot2, it1, it2;
+
+--echo # End of test for bug#13335319.
+
--echo # End of 5.6 tests
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2011-11-08 12:06:01 +0000
@@ -7006,5 +7006,34 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(ot1); Using join buffer (Block Nested Loop)
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Materialize; Scan
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2011-11-08 12:06:01 +0000
@@ -7007,6 +7007,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(ot1); Using join buffer (Block Nested Loop)
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Materialize; Scan
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2011-11-08 12:06:01 +0000
@@ -7007,6 +7007,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Materialize
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Materialize; Scan
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2011-11-08 12:06:01 +0000
@@ -7008,6 +7008,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(ot1); Using join buffer (Block Nested Loop)
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Materialize; Scan
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2011-11-08 12:06:01 +0000
@@ -7006,5 +7006,34 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3 Start temporary
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Using join buffer (Block Nested Loop)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2011-11-08 12:06:01 +0000
@@ -7007,6 +7007,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3 Start temporary
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Using join buffer (Block Nested Loop)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2011-11-08 12:06:01 +0000
@@ -7007,6 +7007,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Using where; Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; End temporary
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2011-11-08 12:06:01 +0000
@@ -7008,6 +7008,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3 Start temporary
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Using join buffer (Block Nested Loop)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2011-11-08 12:06:01 +0000
@@ -7007,6 +7007,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3 Start temporary
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Using join buffer (Block Nested Loop)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2011-11-08 12:06:01 +0000
@@ -7008,6 +7008,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3 Start temporary
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Using join buffer (Block Nested Loop)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2011-11-08 12:06:01 +0000
@@ -7008,6 +7008,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Using where; Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; End temporary
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2011-11-08 12:06:01 +0000
@@ -7009,6 +7009,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3 Start temporary
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Using join buffer (Block Nested Loop)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2011-11-08 12:06:01 +0000
@@ -7007,5 +7007,34 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3 Start temporary
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Using join buffer (Block Nested Loop)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2011-11-08 12:06:01 +0000
@@ -7008,6 +7008,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3 Start temporary
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Using join buffer (Block Nested Loop)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2011-11-08 12:06:01 +0000
@@ -7008,6 +7008,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Using where; Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; End temporary
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2011-11-08 12:06:01 +0000
@@ -7009,6 +7009,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3 Start temporary
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Using join buffer (Block Nested Loop)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2011-11-08 12:06:01 +0000
@@ -7006,5 +7006,34 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using join buffer (Block Nested Loop)
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Materialize
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Materialize
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2011-11-08 12:06:01 +0000
@@ -7007,6 +7007,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using join buffer (Block Nested Loop)
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Materialize
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Materialize
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2011-11-08 12:06:01 +0000
@@ -7007,6 +7007,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Materialize
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Materialize; Scan
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2011-11-08 12:06:01 +0000
@@ -7008,6 +7008,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using join buffer (Block Nested Loop)
+1 PRIMARY it1 ALL NULL NULL NULL NULL 4 Materialize
+1 PRIMARY it2 ALL NULL NULL NULL NULL 5 Materialize
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2011-11-08 12:06:01 +0000
@@ -7083,5 +7083,34 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (Block Nested Loop)
+3 SUBQUERY it2 ALL NULL NULL NULL NULL 5
+2 SUBQUERY it1 ALL NULL NULL NULL NULL 4
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2011-11-08 12:06:01 +0000
@@ -7018,5 +7018,34 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (Block Nested Loop)
+3 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 5 Using where
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 4 Using where
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2011-11-08 12:06:01 +0000
@@ -7019,6 +7019,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (Block Nested Loop)
+3 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 5 Using where
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 4 Using where
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2011-11-08 12:06:01 +0000
@@ -7019,6 +7019,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where
+3 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 5 Using where
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 4 Using where
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result 2011-11-07 14:00:01 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2011-11-08 12:06:01 +0000
@@ -7020,6 +7020,35 @@ o
o
DROP TABLE t1, t2;
# End of test for bug#13340270.
+#
+# Bug#13335319: Seg fault when analyzing FirstMatch semi-join strategy
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(1), (2), (3);
+CREATE TABLE ot2(a INTEGER);
+INSERT INTO ot2 VALUES(1), (2), (4), (6), (8), (10);
+CREATE TABLE it1(a INTEGER);
+INSERT INTO it1 VALUES(1), (3), (5), (7);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(1), (3), (5), (7), (9);
+explain SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (Block Nested Loop)
+3 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 5 Using where
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 4 Using where
+SELECT ot1.a, ot2.a
+FROM ot1, ot2
+WHERE ot1.a IN (SELECT a FROM it1) AND
+ot2.a IN (SELECT a FROM it2);
+a a
+1 1
+3 1
+DROP TABLE ot1, ot2, it1, it2;
+# End of test for bug#13335319.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-11-08 11:37:54 +0000
+++ b/sql/sql_select.cc 2011-11-08 12:06:01 +0000
@@ -15874,9 +15874,7 @@ void Optimize_table_order::advance_sj_st
pos->dups_producing_tables= pos[-1].dups_producing_tables;
// FirstMatch
- pos->first_firstmatch_table=
- (pos[-1].sj_strategy == SJ_OPT_FIRST_MATCH) ?
- MAX_TABLES : pos[-1].first_firstmatch_table;
+ pos->first_firstmatch_table= pos[-1].first_firstmatch_table;
pos->first_firstmatch_rtbl= pos[-1].first_firstmatch_rtbl;
pos->firstmatch_need_tables= pos[-1].firstmatch_need_tables;
@@ -15898,7 +15896,25 @@ void Optimize_table_order::advance_sj_st
}
table_map handled_by_fm_or_ls= 0;
- /* FirstMatch Strategy */
+ /*
+ FirstMatch Strategy
+ ===================
+
+ FirstMatch requires that all dependent outer tables are in the join prefix.
+ (see "FirstMatch strategy" above setup_semijoin_dups_elimination()).
+ The execution strategy will handle multiple semi-join nests correctly,
+ and the optimizer will pick execution strategy according to these rules:
+ - If tables from multiple semi-join nests are intertwined, they will
+ be processed as one FirstMatch evaluation.
+ - If tables from each semi-join nest are grouped together, each semi-join
+ nest is processed as one FirstMatch evaluation.
+
+ Example: Let's say we have an outer table ot and two semi-join nests with
+ two tables each: it11 and it12, and it21 and it22.
+
+ Intertwined tables: ot - FM(it11 - it21 - it12 - it22)
+ Grouped tables: ot - FM(it11 - it12) - FM(it21 - it22)
+ */
if (emb_sj_nest &&
thd->optimizer_switch_flag(OPTIMIZER_SWITCH_FIRSTMATCH))
{
@@ -15922,12 +15938,15 @@ void Optimize_table_order::advance_sj_st
{
/* Start tracking potential FirstMatch range */
pos->first_firstmatch_table= idx;
- pos->firstmatch_need_tables= sj_inner_tables;
+ pos->firstmatch_need_tables= 0;
pos->first_firstmatch_rtbl= remaining_tables;
}
if (pos->first_firstmatch_table != MAX_TABLES)
{
+ /* Record that we need all of this semi-join's inner tables */
+ pos->firstmatch_need_tables|= sj_inner_tables;
+
if (outer_corr_tables & pos->first_firstmatch_rtbl)
{
/*
@@ -15936,13 +15955,7 @@ void Optimize_table_order::advance_sj_st
*/
pos->first_firstmatch_table= MAX_TABLES;
}
- else
- {
- /* Record that we need all of this semi-join's inner tables, too */
- pos->firstmatch_need_tables|= sj_inner_tables;
- }
-
- if (!(pos->firstmatch_need_tables & remaining_tables))
+ else if (!(pos->firstmatch_need_tables & remaining_tables))
{
// Got a complete FirstMatch range. Calculate access paths and cost
double cost, rowcount;
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3480 to 3481) Bug#13335319 | Roy Lyseng | 11 Nov |
