diff options
Diffstat (limited to 'mysql-test')
-rw-r--r-- | mysql-test/r/subselect_extra_no_semijoin.result | 8 | ||||
-rw-r--r-- | mysql-test/r/subselect_sj.result | 26 | ||||
-rw-r--r-- | mysql-test/r/subselect_sj_jcl6.result | 26 | ||||
-rw-r--r-- | mysql-test/t/subselect_sj.test | 25 |
4 files changed, 81 insertions, 4 deletions
diff --git a/mysql-test/r/subselect_extra_no_semijoin.result b/mysql-test/r/subselect_extra_no_semijoin.result index 525b80e60ac..b8f1ba7cc5e 100644 --- a/mysql-test/r/subselect_extra_no_semijoin.result +++ b/mysql-test/r/subselect_extra_no_semijoin.result @@ -349,9 +349,9 @@ WHERE t.a IN (SELECT b FROM t1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -3 MATERIALIZED t1 system NULL NULL NULL NULL 1 100.00 +3 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00 Warnings: -Note 1003 select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t2` where <expr_cache><0>(<in_optimizer>(0,0 in ( <materialize> (select 0 from dual ), <primary_index_lookup>(0 in <temporary table> on distinct_key where ((0 = `<subquery3>`.`b`)))))) +Note 1003 select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t2` where <expr_cache><0>(<in_optimizer>(0,<exists>(select 0 from dual where (<cache>(0) = 0)))) SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0 WHERE t.a IN (SELECT b FROM t1); a a b @@ -362,9 +362,9 @@ WHERE t.a IN (SELECT b FROM t1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where -2 MATERIALIZED t1 system NULL NULL NULL NULL 1 100.00 +2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00 Warnings: -Note 1003 select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t2` where <expr_cache><0>(<in_optimizer>(0,0 in ( <materialize> (select 0 from dual ), <primary_index_lookup>(0 in <temporary table> on distinct_key where ((0 = `<subquery2>`.`b`)))))) +Note 1003 select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t2` where <expr_cache><0>(<in_optimizer>(0,<exists>(select 0 from dual where (<cache>(0) = 0)))) DROP VIEW v1; DROP TABLE t1,t2; # diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 21acd463b59..cef13df63a8 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -2995,4 +2995,30 @@ explain select 1 from t1 where _cp932 "1" in (select '1' from t1); ERROR HY000: Illegal mix of collations (cp932_japanese_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '=' drop table t1; +# +# MDEV-7823: Server crashes in next_depth_first_tab on nested IN clauses with SQ inside +# +set @tmp_mdev7823=@@optimizer_switch; +set optimizer_switch=default; +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (f2 INT, KEY(f2)); +INSERT INTO t2 VALUES (8),(0); +CREATE TABLE t3 (f3 INT); +INSERT INTO t3 VALUES (1),(2); +CREATE TABLE t4 (f4 INT); +INSERT INTO t4 VALUES (0),(5); +explain +SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ref f2 f2 5 const 0 Using where; Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) ); +f1 f2 f3 +1 0 1 +1 0 2 +drop table t1,t2,t3,t4; +set optimizer_switch= @tmp_mdev7823; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index d9aa187e717..e3c46d28ec8 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -3009,6 +3009,32 @@ explain select 1 from t1 where _cp932 "1" in (select '1' from t1); ERROR HY000: Illegal mix of collations (cp932_japanese_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '=' drop table t1; +# +# MDEV-7823: Server crashes in next_depth_first_tab on nested IN clauses with SQ inside +# +set @tmp_mdev7823=@@optimizer_switch; +set optimizer_switch=default; +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (f2 INT, KEY(f2)); +INSERT INTO t2 VALUES (8),(0); +CREATE TABLE t3 (f3 INT); +INSERT INTO t3 VALUES (1),(2); +CREATE TABLE t4 (f4 INT); +INSERT INTO t4 VALUES (0),(5); +explain +SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ref f2 f2 5 const 0 Using where; Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) ); +f1 f2 f3 +1 0 1 +1 0 2 +drop table t1,t2,t3,t4; +set optimizer_switch= @tmp_mdev7823; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 9d7ea17bbe2..7b09cdc5f1c 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -2700,5 +2700,30 @@ explain select 1 from t1 where _cp932 "1" in (select '1' from t1); drop table t1; +--echo # +--echo # MDEV-7823: Server crashes in next_depth_first_tab on nested IN clauses with SQ inside +--echo # +set @tmp_mdev7823=@@optimizer_switch; +set optimizer_switch=default; +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (1); + +CREATE TABLE t2 (f2 INT, KEY(f2)); +INSERT INTO t2 VALUES (8),(0); + +CREATE TABLE t3 (f3 INT); +INSERT INTO t3 VALUES (1),(2); + +CREATE TABLE t4 (f4 INT); +INSERT INTO t4 VALUES (0),(5); + +explain +SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) ); +SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) ); + +drop table t1,t2,t3,t4; +set optimizer_switch= @tmp_mdev7823; + + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; |