3892 Roy Lyseng 2012-03-26
Bug#13845930: Segfault in st_join_table::and_with_condition
This problem occurs during second execution of a query involving
outer join and subquery to semi-join transformation. The outer join
is correctly converted to an inner join, but in make_join_select(),
the JOIN_TAB for the outer-joined table (alias3) has an on_expr_ref
that is non-NULL, even though the first_inner pointer is NULL.
The problem originates from convert_subquery_to_semijoin() where
a "wrap nest" is created that will contain the table alias3 and
the semi-joined tables sq1_alias1 and sq1_alias2. When creating
the wrap nest, the join condition is transferred from the inner table
(alias3) to the wrap nest. In this process, resetting the
prep_join_cond field of the inner table was forgotten.
mysql-test/include/subquery_sj.inc
Added test case for bug#13845930.
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#13845930.
sql/sql_optimizer.cc
In convert_subquery_to_semijoin(), when creating a wrap nest,
reset the prep_join_cond field for the table whose join condition
is copied to the wrap nest.
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_optimizer.cc
3891 Roy Lyseng 2012-03-22
WL#5561 - Enable semi-join transformation with outer join queries
Third worklog commit, after Guilhem's review.
sql/sql_optimizer.cc
Deleted some unused code, forgotten in previous commit.
sql/sql_planner.cc
Updated check_interleaving_with_nj() according to review comments.
sql/sql_select.cc
Typos fixed.
sql/table.h
Adjusted some comments.
modified:
sql/sql_optimizer.cc
sql/sql_planner.cc
sql/sql_select.cc
sql/table.h
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2012-03-21 11:22:13 +0000
+++ b/mysql-test/include/subquery_sj.inc 2012-03-26 10:45:48 +0000
@@ -5435,4 +5435,43 @@ eval $query;
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+--echo #
+--echo # Bug#13845930: Segfault in st_join_table::and_with_condition
+--echo #
+
+CREATE TABLE t1 (
+ col_int INTEGER
+);
+
+CREATE TABLE t2 (
+ col_varchar_1 VARCHAR(1),
+ col_varchar_2 VARCHAR(1)
+);
+
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+
+EXECUTE stmt;
+EXECUTE stmt;
+
+DEALLOCATE prepare stmt;
+
+DROP TABLE t1, t2;
+
+--echo # End of test for bug#13845930.
+
--echo # End of 5.6 tests
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2012-03-26 10:45:48 +0000
@@ -8542,5 +8542,37 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2012-03-26 10:45:48 +0000
@@ -8544,6 +8544,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2012-03-26 10:45:48 +0000
@@ -8541,5 +8541,37 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-03-26 10:45:48 +0000
@@ -8542,6 +8542,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-03-26 10:45:48 +0000
@@ -8542,6 +8542,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2012-03-26 10:45:48 +0000
@@ -8542,6 +8542,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-03-26 10:45:48 +0000
@@ -8544,6 +8544,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2012-03-26 10:45:48 +0000
@@ -8542,5 +8542,37 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-03-26 10:45:48 +0000
@@ -8544,6 +8544,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2012-03-26 10:45:48 +0000
@@ -8542,5 +8542,37 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-03-26 10:45:48 +0000
@@ -8544,6 +8544,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2012-03-26 10:45:48 +0000
@@ -8618,5 +8618,37 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2012-03-26 10:45:48 +0000
@@ -8553,5 +8553,37 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2012-03-26 10:45:48 +0000
@@ -8554,6 +8554,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-03-26 10:45:48 +0000
@@ -8554,6 +8554,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# 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 2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2012-03-26 10:45:48 +0000
@@ -8555,6 +8555,38 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+ JOIN
+ (t2 AS alias2
+ LEFT JOIN t2 AS alias3
+ ON (8, 92) IN
+ (SELECT sq1_alias1.col_int,
+ sq1_alias2.col_int
+ FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+ )
+ )
+ ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc 2012-03-22 11:53:41 +0000
+++ b/sql/sql_optimizer.cc 2012-03-26 10:45:48 +0000
@@ -6238,6 +6238,7 @@ static bool convert_subquery_to_semijoin
wrap_nest->set_join_cond(outer_tbl->join_cond());
outer_tbl->set_join_cond(NULL);
+ outer_tbl->prep_join_cond= NULL;
List_iterator<TABLE_LIST> li(*wrap_nest->join_list);
TABLE_LIST *tbl;
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3891 to 3892) Bug#13845930 | Roy Lyseng | 26 Mar |
