diff options
Diffstat (limited to 'mysql-test/r/subselect_mat_cost_bugs.result')
| -rw-r--r-- | mysql-test/r/subselect_mat_cost_bugs.result | 43 |
1 files changed, 43 insertions, 0 deletions
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index b4ddd5e5849..f5d5be8f726 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -379,6 +379,7 @@ drop table t3, t4, t5; # # LP BUG#858038 The result of a query with NOT IN subquery depends on the state of the optimizer switch # +set @optimizer_switch_save= @@optimizer_switch; create table t1 (c1 char(2) not null, c2 char(2)); create table t2 (c3 char(2), c4 char(2)); insert into t1 values ('a1', 'b1'); @@ -400,6 +401,7 @@ id select_type table type possible_keys key key_len ref rows Extra select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); c1 c2 drop table t1, t2; +set optimizer_switch= @optimizer_switch_save; # # MDEV-12673: cost-based choice between materialization and in-to-exists # @@ -442,3 +444,44 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t3 const PRIMARY PRIMARY 4 const 1 2 DEPENDENT SUBQUERY t2 index NULL i2 11 NULL 2 Using where; Using index DROP TABLE t1,t2,t3; +# +# MDEV-7599: in-to-exists chosen after min/max optimization +# +set @optimizer_switch_save= @@optimizer_switch; +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,6),(2,4), (8,9); +SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b); +b c +EXPLAIN EXTENDED SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +2 MATERIALIZED t1 index NULL a 5 NULL 2 100.00 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,`test`.`t2`.`b` in ( <materialize> (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` where `test`.`t2`.`c` = `test`.`t2`.`b` ), <primary_index_lookup>(`test`.`t2`.`b` in <temporary table> on distinct_key where `test`.`t2`.`b` = `<subquery2>`.`MIN(a)`)))) +set optimizer_switch= 'materialization=off'; +SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b); +b c +EXPLAIN EXTENDED SELECT * FROM t2 WHERE b != ALL (SELECT MIN(a) FROM t1, t2 WHERE t2.c = t2.b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +2 DEPENDENT SUBQUERY t1 index NULL a 5 NULL 2 100.00 Using index +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` where `test`.`t2`.`c` = `test`.`t2`.`b` having trigcond(<cache>(`test`.`t2`.`b`) = <ref_null_helper>(min(`test`.`t1`.`a`)))))) +set optimizer_switch= @optimizer_switch_save; +DROP TABLE t1,t2; +CREATE TABLE t1 (f1 varchar(10)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('foo'),('bar'); +CREATE TABLE t2 (f2 varchar(10), key(f2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('baz'),('qux'); +CREATE TABLE t3 (f3 varchar(10)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('abc'),('def'); +SELECT * FROM t1 +WHERE f1 = ALL( SELECT MAX(t2a.f2) +FROM t2 AS t2a INNER JOIN t2 t2b INNER JOIN t3 +ON (f3 = t2b.f2) ); +f1 +DROP TABLE t1,t2,t3; |