diff options
Diffstat (limited to 'mysql-test/main/subselect_mat_cost_bugs.result')
| -rw-r--r-- | mysql-test/main/subselect_mat_cost_bugs.result | 521 |
1 files changed, 521 insertions, 0 deletions
diff --git a/mysql-test/main/subselect_mat_cost_bugs.result b/mysql-test/main/subselect_mat_cost_bugs.result new file mode 100644 index 00000000000..125da8da517 --- /dev/null +++ b/mysql-test/main/subselect_mat_cost_bugs.result @@ -0,0 +1,521 @@ +# +# LP BUG#643424 valgrind warning in choose_subquery_plan() +# +CREATE TABLE t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +c1 int(11) DEFAULT NULL, +c2 int(11) DEFAULT NULL, +PRIMARY KEY (pk), +KEY c2 (c2)); +INSERT INTO t1 VALUES (1,NULL,2); +INSERT INTO t1 VALUES (2,7,9); +INSERT INTO t1 VALUES (9,NULL,8); +CREATE TABLE t2 ( +pk int(11) NOT NULL AUTO_INCREMENT, +c1 int(11) DEFAULT NULL, +c2 int(11) DEFAULT NULL, +PRIMARY KEY (pk), +KEY c2 (c2)); +INSERT INTO t2 VALUES (1,1,7); +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off'; +SELECT pk FROM t1 WHERE (c2, c1) IN (SELECT c2, c2 FROM t2); +pk +set session optimizer_switch=@save_optimizer_switch; +drop table t1, t2; +# +# LP BUG#652727 Crash in create_ref_for_key() +# +CREATE TABLE t2 ( +pk int(11) NOT NULL AUTO_INCREMENT, +c1 int(11) DEFAULT NULL, +PRIMARY KEY (pk)); +INSERT INTO t2 VALUES (10,7); +INSERT INTO t2 VALUES (11,1); +INSERT INTO t2 VALUES (17,NULL); +CREATE TABLE t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +c1 int(11) DEFAULT NULL, +PRIMARY KEY (pk)); +INSERT INTO t1 VALUES (15,1); +INSERT INTO t1 VALUES (19,NULL); +CREATE TABLE t3 (c2 int(11) DEFAULT NULL, KEY c2 (c2)); +INSERT INTO t3 VALUES (1); +set @save_optimizer_switch=@@optimizer_switch; +set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off'; +SELECT c2 +FROM t3 +WHERE (2, 6) IN (SELECT t1.c1, t1.c1 FROM t1 STRAIGHT_JOIN t2 ON t2.pk = t1.pk); +c2 +set session optimizer_switch=@save_optimizer_switch; +drop table t1, t2, t3; +# +# LP BUG#641245 Crash in Item_equal::contains +# +CREATE TABLE t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +c1 int(11) DEFAULT NULL, +c2 int(11) DEFAULT NULL, +c3 varchar(1) DEFAULT NULL, +c4 varchar(1) DEFAULT NULL, +PRIMARY KEY (pk), +KEY c2 (c2), +KEY c3 (c3,c2)); +INSERT INTO t1 VALUES (10,7,8,'v','v'); +INSERT INTO t1 VALUES (11,1,9,'r','r'); +INSERT INTO t1 VALUES (12,5,9,'a','a'); +INSERT INTO t1 VALUES (13,7,18,'v','v'); +INSERT INTO t1 VALUES (14,1,19,'r','r'); +INSERT INTO t1 VALUES (15,5,29,'a','a'); +INSERT INTO t1 VALUES (17,7,38,'v','v'); +INSERT INTO t1 VALUES (18,1,39,'r','r'); +INSERT INTO t1 VALUES (19,5,49,'a','a'); +create table t1a like t1; +insert into t1a select * from t1; +create table t1b like t1; +insert into t1b select * from t1; +CREATE TABLE t2 ( +pk int(11) NOT NULL AUTO_INCREMENT, +c1 int(11) DEFAULT NULL, +c2 int(11) DEFAULT NULL, +c3 varchar(1) DEFAULT NULL, +c4 varchar(1) DEFAULT NULL, +PRIMARY KEY (pk), +KEY c2 (c2), +KEY c3 (c3,c2)); +INSERT INTO t2 VALUES (1,NULL,2,'w','w'); +INSERT INTO t2 VALUES (2,7,9,'m','m'); +set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off'; +EXPLAIN EXTENDED SELECT pk +FROM t1 +WHERE c1 IN +(SELECT t1a.c1 +FROM (t1b JOIN t2 ON t2.c3 = t1b.c4) LEFT JOIN +t1a ON (t1a.c2 = t1b.pk AND 2) +WHERE t1.pk) ; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 9 100.00 Using where +2 DEPENDENT SUBQUERY t1b ALL NULL NULL NULL NULL 9 100.00 +2 DEPENDENT SUBQUERY t1a ref c2 c2 5 test.t1b.pk 1 100.00 Using where +2 DEPENDENT SUBQUERY t2 index c3 c3 9 NULL 2 100.00 Using where; Using index; Using join buffer (flat, BNL join) +Warnings: +Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where <expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t1a`.`c1` from `test`.`t1b` join `test`.`t2` left join `test`.`t1a` on(`test`.`t1a`.`c2` = `test`.`t1b`.`pk` and 2) where `test`.`t1`.`pk` <> 0 and <cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1` and `test`.`t2`.`c3` = `test`.`t1b`.`c4`))) +SELECT pk +FROM t1 +WHERE c1 IN +(SELECT t1a.c1 +FROM (t1b JOIN t2 ON t2.c3 = t1b.c4) LEFT JOIN +t1a ON (t1a.c2 = t1b.pk AND 2) +WHERE t1.pk) ; +pk +DROP TABLE t1, t1a, t1b, t2; +# +# LP BUG#714808 Assertion `outer_lookup_keys <= outer_record_count' +# failed with materialization +CREATE TABLE t1 ( pk int(11), PRIMARY KEY (pk)) ; +CREATE TABLE t2 ( f2 int(11)) ; +CREATE TABLE t3 ( f1 int(11), f3 varchar(1), KEY (f1)) ; +INSERT INTO t3 VALUES (7,'f'); +set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off'; +EXPLAIN +SELECT t1.* +FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1 +WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT t1.* +FROM t3 RIGHT JOIN t1 ON t1.pk = t3.f1 +WHERE t3.f3 OR ( 3 ) IN ( SELECT f2 FROM t2 ); +pk +drop table t1,t2,t3; +# +# LP BUG#714999 Second crash in select_describe() with nested subqueries +# +CREATE TABLE t1 ( pk int(11)) ; +INSERT INTO t1 VALUES (29); +CREATE TABLE t2 ( f1 varchar(1)) ; +INSERT INTO t2 VALUES ('f'),('d'); +CREATE TABLE t3 ( f2 varchar(1)) ; +EXPLAIN SELECT f2 FROM t3 WHERE ( +SELECT MAX( pk ) FROM t1 +WHERE EXISTS ( +SELECT max(f1) +FROM t2 GROUP BY f1 +) +) IS NULL ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t1 system NULL NULL NULL NULL 1 +3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +drop table t1, t2, t3; +# +# LP BUG#715034 Item_sum_distinct::clear(): Assertion `tree != 0' failed +# +CREATE TABLE t2 ( f2 int(11)) ; +CREATE TABLE t1 ( f3 int(11), KEY (f3)) ; +INSERT INTO t1 VALUES (6),(4); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; +EXPLAIN +SELECT * FROM (SELECT * FROM t2) AS a2 +WHERE (SELECT distinct SUM(distinct f3 ) FROM t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 Const row not found +3 SUBQUERY t1 index NULL f3 5 NULL 2 Using index +2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +insert into t2 values (1),(2); +EXPLAIN +SELECT * FROM (SELECT * FROM t2) AS a2 +WHERE (SELECT distinct SUM(distinct f3 ) FROM t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +3 SUBQUERY t1 index NULL f3 5 NULL 2 Using index +2 DERIVED t2 ALL NULL NULL NULL NULL 2 +set optimizer_switch=@tmp_optimizer_switch; +drop table t1,t2; +# +# LP BUG#715027 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed +# +CREATE TABLE t1 ( f1 int(11), PRIMARY KEY (f1)) ; +INSERT INTO t1 VALUES (28),(29); +CREATE TABLE t2 ( f2 int(11), f3 int(11), f10 varchar(1)) ; +INSERT INTO t2 VALUES (NULL,6,'f'),(4,2,'d'); +set @tmp_optimizer_switch=@@optimizer_switch; +set optimizer_switch='derived_merge=off,derived_with_keys=off'; +EXPLAIN +SELECT alias2.f2 AS field1 +FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 +WHERE ( +SELECT t2.f2 +FROM t2 JOIN t1 ON t1.f1 +WHERE t1.f1 AND alias2.f10 +) +ORDER BY field1 ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where; Using filesort +1 PRIMARY alias1 eq_ref PRIMARY PRIMARY 4 alias2.f3 1 Using index +3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 +3 DEPENDENT SUBQUERY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) +2 DERIVED t2 ALL NULL NULL NULL NULL 2 +SELECT alias2.f2 AS field1 +FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 +WHERE ( +SELECT t2.f2 +FROM t2 JOIN t1 ON t1.f1 +WHERE t1.f1 AND alias2.f10 +) +ORDER BY field1 ; +field1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'f' +Warning 1292 Truncated incorrect DOUBLE value: 'd' +set optimizer_switch=@tmp_optimizer_switch; +drop table t1,t2; +# +# LP BUG#718578 Yet another Assertion `!table || +# (!table->read_set || bitmap_is_set(table->read_set, field_index))' +CREATE TABLE t1 ( f1 int(11), f2 int(11), f3 int(11)) ; +INSERT IGNORE INTO t1 VALUES (28,5,6),(29,NULL,4); +CREATE TABLE t2 ( f10 varchar(1) ); +INSERT IGNORE INTO t2 VALUES (NULL); +SELECT f1 AS field1 +FROM ( SELECT * FROM t1 ) AS alias1 +WHERE (SELECT t1.f1 +FROM t2 JOIN t1 ON t1.f2 +WHERE alias1.f3 AND t1.f3) AND f2 +ORDER BY field1; +field1 +28 +drop table t1,t2; +# +# LP BUG#601124 Bug in eliminate_item_equal +# leads to crash in Item_func::Item_func +CREATE TABLE t1 ( f1 int(11), f3 varchar(1)) ; +INSERT INTO t1 VALUES (5,'m'),(NULL,'c'); +CREATE TABLE t2 ( f2 int(11), f3 varchar(1)) ; +INSERT INTO t2 VALUES (6,'f'),(2,'d'); +CREATE TABLE t3 ( f2 int(11), f3 varchar(1)) ; +INSERT INTO t3 VALUES (6,'f'),(2,'d'); +SELECT * FROM t3 +WHERE ( f2 ) IN (SELECT t1.f1 +FROM t1 STRAIGHT_JOIN t2 ON t2.f3 = t1.f3 +WHERE t2.f3 = 'c'); +f2 f3 +drop table t1,t2,t3; +# +# LP BUG#718593 Crash in substitute_for_best_equal_field -> eliminate_item_equal -> +# Item_field::find_item_equal -> Item_equal::contains +# +set @save_optimizer_switch=@@optimizer_switch; +SET @@optimizer_switch = 'semijoin=off'; +CREATE TABLE t1 ( f3 int(11), f10 varchar(1), f11 varchar(1)) ; +INSERT IGNORE INTO t1 VALUES (6,'f','f'),(2,'d','d'); +CREATE TABLE t2 ( f12 int(11), f13 int(11)) ; +insert into t2 values (1,2), (3,4); +EXPLAIN +SELECT * FROM t2 +WHERE ( f12 ) IN ( +SELECT alias2.f3 +FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11 +WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED alias1 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED alias2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +SELECT * FROM t2 +WHERE ( f12 ) IN ( +SELECT alias2.f3 +FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11 +WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10 +); +f12 f13 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'f' +Warning 1292 Truncated incorrect DOUBLE value: 'd' +EXPLAIN +SELECT * FROM t2 +WHERE ( f12 ) IN ( +SELECT alias2.f3 +FROM t1 AS alias1, t1 AS alias2 +WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED alias1 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED alias2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +SELECT * FROM t2 +WHERE ( f12 ) IN ( +SELECT alias2.f3 +FROM t1 AS alias1, t1 AS alias2 +WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); +f12 f13 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'f' +Warning 1292 Truncated incorrect DOUBLE value: 'd' +set @@optimizer_switch=@save_optimizer_switch; +drop table t1, t2; +# +# MWL#89: test introduced after Sergey Petrunia's review - test that +# keyparts wihtout index prefix are used with the IN-EXISTS strategy. +# +create table t1 (c1 int); +insert into t1 values (1), (2), (3); +create table t2 (kp1 int, kp2 int, c2 int, filler char(100)); +insert into t2 values (0,0,0,'filler'),(0,1,1,'filler'),(0,2,2,'filler'),(0,3,3,'filler'); +create index key1 on t2 (kp1, kp2); +create index key2 on t2 (kp1); +create index key3 on t2 (kp2); +SET @@optimizer_switch='materialization=off,semijoin=off,in_to_exists=on'; +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +explain +select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4) or c1 > 7; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index_subquery key1,key2,key3 key1 10 func,const 1 Using where +select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4) or c1 > 7; +c1 +set @@optimizer_switch='default'; +drop table t1, t2; +# +# LP BUG#800679: Assertion `outer_join->table_count > 0' failed in +# JOIN::choose_subquery_plan() with materialization=on,semijoin=off +# +CREATE TABLE t1 ( f1 int); +insert into t1 values (1),(2); +CREATE TABLE t2 ( f1 int); +insert into t2 values (1),(2); +SET @@optimizer_switch='materialization=on,semijoin=off'; +EXPLAIN +SELECT * FROM t1 +WHERE (f1) IN (SELECT f1 FROM t2) +LIMIT 0; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 +WHERE (f1) IN (SELECT f1 FROM t2) +LIMIT 0; +f1 +set @@optimizer_switch='default'; +drop table t1, t2; +# +# LP BUG#834492: Crash in fix_semijoin_strategies_for_picked_join_order +# with nested subqueries and LooseScan=ON +# +CREATE TABLE t3 (b int) ; +INSERT INTO t3 VALUES (0),(0); +CREATE TABLE t4 (a int, b int, c int, d int, PRIMARY KEY (a)) ; +INSERT INTO t4 VALUES (28,0,0,0),(29,3,0,0); +CREATE TABLE t5 (a int, b int, c int, d int, KEY (c,b)) ; +INSERT INTO t5 VALUES (28,0,0,0),(29,3,0,0); +SET @@optimizer_switch='semijoin=ON,loosescan=ON,firstmatch=OFF,materialization=OFF'; +EXPLAIN SELECT * +FROM t3 +WHERE t3.b > ALL ( +SELECT c +FROM t4 +WHERE t4.a >= t3.b +AND a = SOME (SELECT b FROM t5)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t5 index c c 10 NULL 2 Using where; Using index; Start temporary +2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t5.b 1 Using index condition; Using where; End temporary +SELECT * +FROM t3 +WHERE t3.b > ALL ( +SELECT c +FROM t4 +WHERE t4.a >= t3.b +AND a = SOME (SELECT b FROM t5)); +b +0 +0 +set @@optimizer_switch='default'; +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'); +insert into t1 values ('a2', 'b2'); +insert into t2 values ('x1', 'y1'); +insert into t2 values ('a2', null); +set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=off,partial_match_table_scan=on'; +explain select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); +c1 c2 +set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off'; +explain select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 +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 +# +CREATE TABLE t1 ( +pk1 int, a1 varchar(3), b1 varchar(3), PRIMARY KEY (pk1), KEY(a1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,'foo','bar'),(2,'bar','foo'); +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 VARCHAR(3), KEY(a2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,'abc'),(2,'xyz'),(3,'foo'); +SELECT 'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); +'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ) +0 +SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); +'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ) +1 +EXPLAIN +SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t2 const PRIMARY,a2 PRIMARY 4 const 1 +2 SUBQUERY t1 ref a1,b1 b1 6 const 1 Using where +DROP TABLE t1,t2; +CREATE TABLE t1 (i1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (i2 int, c2 varchar(3), KEY(i2,c2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,'abc'),(2,'foo'); +CREATE TABLE t3 (pk3 int PRIMARY KEY, c3 varchar(3)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (1,'foo'),(2,'bar'); +SELECT * FROM t1 WHERE i1 NOT IN ( +SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 +); +i1 +1 +EXPLAIN +SELECT * FROM t1 WHERE i1 NOT IN ( +SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +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; +# +# MDEV-12963: min/max optimization optimizing away all tables employed +# for uncorrelated IN subquery used in a disjunct of WHERE +# +create table t1 (a int, index idx(a)) engine=myisam; +insert into t1 values (4),(7),(1),(3),(9); +select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5; +a +3 +7 +9 +explain +select * from t1 where a in (select max(a) from t1 where a < 4) or a > 5; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index idx idx 5 NULL 5 Using where; Using index +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1; +# +# MDEV-13135: subquery with ON expression subject to +# semi-join optimizations +# +CREATE TABLE t1 (a INT); +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT a AS v_a FROM t1; +INSERT INTO t1 VALUES (1),(3); +CREATE TABLE t2 (b INT, KEY(b)); +INSERT INTO t2 VALUES (3),(4); +SELECT * FROM t1 WHERE a NOT IN ( +SELECT b FROM t2 INNER JOIN v1 ON (b IN ( SELECT a FROM t1 )) +WHERE v_a = b +); +a +1 +DROP VIEW v1; +DROP TABLE t1,t2; |