3856 Jorgen Loland 2012-03-26
Bug#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
Consider a query of the form
SELECT ... HAVING (SELECT ...)
in which there is a row in the outer query that satisfies
all conditions except those in the HAVING clause. In this
case, JOIN::exec() used to evaluate the HAVING clause and
then call return_zero_rows(). return_zero_rows() would, in
turn, call join->join_free(). Since the subquery in the HAVING
clause had already been evaluated, join_free() would assume
that it would not be evaluated over again and would clean it
up. However, return_zero_rows() also evaluated the HAVING
clause, and this causes a crash.
The fix is to not call return_zero_rows() if the reason for
no matching rows is that the HAVING clause evaluates to false.
@ mysql-test/include/subquery_sj.inc
Add test for BUG#13848789
@ sql/sql_executor.cc
Don't call return_zero_rows() if the reason for an empty result
is that HAVING evaluates to false.
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_executor.cc
3855 Nuno Carvalho 2012-03-26
BUG#11938382 - SLAVE GETS GOT FATAL ERROR 1236: ERROR READING LOG ENTRY MESSAGE
BUG#13779291 - RACE CONDITION AROUND ROTATE RELAY LOGS & FLUSH LOGS
BUG#13813811 - SPORADIC FAILURES IN RPL_GTID_STRESS_FAILOVER TEST
Making rpl_gtid_stress_failover test experimental.
modified:
mysql-test/collections/default.experimental
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2012-03-22 08:17:51 +0000
+++ b/mysql-test/include/subquery_sj.inc 2012-03-26 10:29:07 +0000
@@ -4916,4 +4916,39 @@ eval $query;
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+--echo #
+--echo # BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+--echo # SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+--echo #
+
+CREATE TABLE t1 (
+ col_int_key INT,
+ col_varchar_key VARCHAR(1),
+ KEY col_int_key (col_int_key),
+ KEY col_varchar_key (col_varchar_key)
+);
+
+INSERT INTO t1 VALUES (8,'x');
+
+CREATE TABLE t2 (
+ col_varchar_key VARCHAR(1),
+ KEY col_varchar_key (col_varchar_key)
+);
+
+INSERT INTO t2 VALUES ('x'), ('y');
+
+let $query= SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+ SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+ FROM t1 as t1_inner JOIN t2
+ ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+
+
+--eval explain $query
+--eval $query
+
+DROP TABLE t1,t2;
+
--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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2012-03-26 10:29:07 +0000
@@ -8060,5 +8060,41 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2012-03-26 10:29:07 +0000
@@ -8065,6 +8065,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-03-26 10:29:07 +0000
@@ -8066,6 +8066,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2012-03-26 10:29:07 +0000
@@ -8066,6 +8066,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2012-03-26 10:29:07 +0000
@@ -8044,5 +8044,41 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 DEPENDENT SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-03-26 10:29:07 +0000
@@ -8045,6 +8045,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 DEPENDENT SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-03-26 10:29:07 +0000
@@ -8053,6 +8053,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 DEPENDENT SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-03-26 10:29:07 +0000
@@ -8046,6 +8046,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 DEPENDENT SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2012-03-26 10:29:07 +0000
@@ -8043,6 +8043,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 DEPENDENT SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-03-26 10:29:07 +0000
@@ -8044,6 +8044,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 DEPENDENT SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-03-26 10:29:07 +0000
@@ -8052,6 +8052,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 DEPENDENT SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-03-26 10:29:07 +0000
@@ -8045,6 +8045,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 DEPENDENT SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2012-03-26 10:29:07 +0000
@@ -8045,5 +8045,41 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 DEPENDENT SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2012-03-26 10:29:07 +0000
@@ -8046,6 +8046,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 DEPENDENT SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-03-26 10:29:07 +0000
@@ -8054,6 +8054,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 DEPENDENT SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-03-26 10:29:07 +0000
@@ -8047,6 +8047,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 DEPENDENT SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2012-03-26 10:29:07 +0000
@@ -8058,5 +8058,41 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2012-03-26 10:29:07 +0000
@@ -8059,6 +8059,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-03-26 10:29:07 +0000
@@ -8060,6 +8060,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-03-26 10:29:07 +0000
@@ -8060,6 +8060,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2012-03-26 10:29:07 +0000
@@ -8126,5 +8126,41 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2012-03-26 10:29:07 +0000
@@ -8041,5 +8041,41 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 DEPENDENT SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2012-03-26 10:29:07 +0000
@@ -8042,6 +8042,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 DEPENDENT SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-03-26 10:29:07 +0000
@@ -8042,6 +8042,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 DEPENDENT SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# 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-22 08:17:51 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2012-03-26 10:29:07 +0000
@@ -8043,6 +8043,42 @@ col_datetime_key
2008-09-27 00:34:58
DROP TABLE t1,t2;
SET @@optimizer_search_depth=@old_depth;
+#
+# BUG#13848789: SEGFAULT IN JOIN_READ_NEXT_SAME AT
+# SQL/SQL_EXECUTOR.CC ON HAVING...IN...JOIN
+#
+CREATE TABLE t1 (
+col_int_key INT,
+col_varchar_key VARCHAR(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES (8,'x');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES ('x'), ('y');
+explain SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t1_inner system col_varchar_key NULL NULL NULL 1 NULL
+2 DEPENDENT SUBQUERY t2 ref col_varchar_key col_varchar_key 4 const 1 Using index
+SELECT MIN(col_int_key)
+FROM t1 as t1_outer
+HAVING (1, 2) IN (
+SELECT t1_inner.col_int_key, MAX(t1_inner.col_int_key)
+FROM t1 as t1_inner JOIN t2
+ON t2.col_varchar_key = t1_inner.col_varchar_key
+);
+MIN(col_int_key)
+DROP TABLE t1,t2;
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc 2012-03-21 21:12:45 +0000
+++ b/sql/sql_executor.cc 2012-03-26 10:29:07 +0000
@@ -145,15 +145,13 @@ JOIN::exec()
We have to test for 'conds' here as the WHERE may not be constant
even if we don't have any tables for prepared statements or if
conds uses something like 'rand()'.
- If the HAVING clause is either impossible or always true, then
- JOIN::having is set to NULL by optimize_cond.
- In this case JOIN::exec must check for JOIN::having_value, in the
- same way it checks for JOIN::cond_value.
+
+ Don't evaluate the having clause here. return_zero_rows() should
+ be called only for cases where there are no matching rows after
+ evaluating all conditions except the HAVING clause.
*/
if (select_lex->cond_value != Item::COND_FALSE &&
- select_lex->having_value != Item::COND_FALSE &&
- (!conds || conds->val_int()) &&
- (!having || having->val_int()))
+ (!conds || conds->val_int()))
{
if (result->send_result_set_metadata(*columns_list,
Protocol::SEND_NUM_ROWS |
@@ -161,7 +159,16 @@ JOIN::exec()
{
DBUG_VOID_RETURN;
}
- if (do_send_rows &&
+
+ /*
+ If the HAVING clause is either impossible or always true, then
+ JOIN::having is set to NULL by optimize_cond.
+ In this case JOIN::exec must check for JOIN::having_value, in the
+ same way it checks for JOIN::cond_value.
+ */
+ if (((select_lex->having_value != Item::COND_FALSE) &&
+ (!having || having->val_int()))
+ && do_send_rows &&
(procedure ? (procedure->send_row(procedure_fields_list) ||
procedure->end_of_records()) : result->send_data(fields_list)))
error= 1;
@@ -1422,13 +1429,17 @@ static void update_const_equal_items(Ite
}
/**
- For some reason (impossible WHERE clause etc), the tables cannot
+ For some reason, e.g. due to an impossible WHERE clause, the tables cannot
possibly contain any rows that will be in the result. This function
is used to return with a result based on no matching rows (i.e., an
empty result or one row with aggregates calculated without using
rows in the case of implicit grouping) before the execution of
nested loop join.
+ This function may evaluate the HAVING clause and is only meant for
+ result sets that are empty due to an impossible HAVING clause. Do
+ not use it if HAVING has already been evaluated.
+
@param join The join that does not produce a row
@param fields Fields in result
*/
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (jorgen.loland:3855 to 3856) Bug#13848789 | Jorgen Loland | 26 Mar |
