diff options
26 files changed, 313 insertions, 215 deletions
diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc index a86e4818d15..65e488c538f 100644 --- a/mysql-test/include/mix1.inc +++ b/mysql-test/include/mix1.inc @@ -1184,9 +1184,9 @@ DROP TABLE t1; create table t1 (a bit(1) not null,b int) engine=myisam; create table t2 (c int) engine=innodb; set @save_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=off'; +set @@optimizer_switch='in_to_exists=on,materialization=off'; explain -select b from t1 where a not in (select b from t1,t2 group by a) group by a; +select b from t1 where a not in (select max(b) from t1,t2 group by a) group by a; set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2; diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index f3d2a567227..52c58f088a1 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -265,7 +265,7 @@ CREATE TABLE t1(f1 VARCHAR(6) NOT NULL, FULLTEXT KEY(f1),UNIQUE(f1)); INSERT INTO t1 VALUES ('test'); EXPLAIN SELECT 1 FROM t1 -WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) +WHERE 1 > ALL((SELECT t1.f1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) WHERE t1.f1 GROUP BY t1.f1)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 @@ -273,7 +273,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 - WHERE 1 > ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a + WHERE 1 > ALL((SELECT t1.f1 FROM t1 RIGHT OUTER JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) WHERE t1.f1 GROUP BY t1.f1))'; EXECUTE stmt; @@ -289,7 +289,7 @@ id select_type table type possible_keys key key_len ref rows Extra DEALLOCATE PREPARE stmt; PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 - WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a + WHERE 1 > ALL((SELECT t1.f1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) WHERE t1.f1 GROUP BY t1.f1))'; EXECUTE stmt; diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 33ec3d1f39c..5374d856343 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -684,10 +684,8 @@ PREPARE stmt FROM WHERE t1.f1 GROUP BY t1.f1))'; EXECUTE stmt; 1 -1 EXECUTE stmt; 1 -1 DEALLOCATE PREPARE stmt; DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 4c863d25257..858d31e05c8 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2963,7 +2963,7 @@ WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL b 5 NULL 10 Using index 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) -3 DEPENDENT SUBQUERY t1 index b b 5 NULL 10 Using where; Using index; Using temporary +3 DEPENDENT SUBQUERY t1 index b b 5 NULL 10 Using where; Using index SELECT alias1.* FROM t1, (SELECT * FROM t1) AS alias1 WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ; a b @@ -3073,7 +3073,7 @@ WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL b 5 NULL 10 Using index 1 PRIMARY alias1 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY t1 ALL b NULL NULL NULL 10 Range checked for each record (index map: 0x1); Using temporary +2 DEPENDENT SUBQUERY t1 index b b 5 NULL 10 Using where; Using index SELECT alias1.* FROM t1, t1 AS alias1 WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ; a b diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result index 5bb6b9db49e..4e1e4f01c07 100644 --- a/mysql-test/r/myisam_mrr.result +++ b/mysql-test/r/myisam_mrr.result @@ -350,9 +350,9 @@ GROUP BY t2.pk ); 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 -2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition; Using where; Using filesort +2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition Warnings: -Note 1003 select min(1) AS `MIN(t1.pk)` from `test`.`t1` where exists(select `test`.`t2`.`pk` from `test`.`t2` where isnull(`test`.`t2`.`int_key`) group by `test`.`t2`.`pk`) +Note 1003 select min(1) AS `MIN(t1.pk)` from `test`.`t1` where exists(select `test`.`t2`.`pk` from `test`.`t2` where isnull(`test`.`t2`.`int_key`)) DROP TABLE t1, t2; # # BUG#42048 Discrepancy between MyISAM and Maria's ICP implementation diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 8008d887823..f54106b5fa0 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -1662,7 +1662,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select NULL from `test`.`t2` group by 1) > <cache>(`test`.`t3`.`a`)))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`)))) select * from t3 where a >= some (select b from t2 group by 1); a explain extended select * from t3 where a >= some (select b from t2 group by 1); @@ -1670,7 +1670,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(<min>(select NULL from `test`.`t2` group by 1) <= <cache>(`test`.`t3`.`a`)))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`)))) select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); @@ -1686,7 +1686,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(<min>(select NULL from `test`.`t2` group by 1) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) select * from t3 where NULL >= some (select b from t2); a explain extended select * from t3 where NULL >= some (select b from t2); @@ -1702,7 +1702,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(<min>(select NULL from `test`.`t2` group by 1) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); a @@ -2980,9 +2980,9 @@ Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `tes explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; set optimizer_switch=@tmp11867_optimizer_switch; CREATE TABLE t1 (a char(5), b char(5)); @@ -4463,20 +4463,20 @@ a drop table t1, t2; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 -1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 +1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1 100.00 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary Warnings: -Note 1003 select 1 AS `1` from <materialize> (select 1 from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`1` = 1) -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); +Note 1003 select 1 AS `1` from <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`min(a)` = 1) +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 -1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 +1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1 100.00 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary Warnings: -Note 1003 select 1 AS `1` from <materialize> (select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`1` = 1) +Note 1003 select 1 AS `1` from <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`min(a)` = 1) DROP TABLE t1; # # Bug#45061: Incorrectly market field caused wrong result. @@ -4820,8 +4820,6 @@ SELECT * FROM t1 WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b); pk a 1 10 -3 30 -2 20 DROP TABLE t1,t2; CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY b (b)); INSERT INTO t1 VALUES (1,NULL), (9,NULL); @@ -5246,18 +5244,21 @@ DROP table t1,t2; # CREATE TABLE t1 (a int, b int) ; INSERT INTO t1 VALUES (0,0),(0,0); +set @optimizer_switch_save=@@optimizer_switch; +set @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=on'; EXPLAIN SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; b 0 +set @@optimizer_switch=@optimizer_switch_save; DROP TABLE t1; # # Bug #11765713 58705: @@ -5653,7 +5654,7 @@ WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY table1 ALL NULL NULL NULL NULL 2 1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY t2 ALL f1_key NULL NULL NULL 10 Range checked for each record (index map: 0x1); Using temporary +2 DEPENDENT SUBQUERY t2 index f1_key f1_key 4 NULL 10 Using where; Using index DROP TABLE t1,t2; # # LP bug #826279: assertion failure with GROUP BY a result of subquery @@ -5881,7 +5882,7 @@ SET @@optimizer_switch='semijoin=off,materialization=off,in_to_exists=on,subquer EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL a 4 NULL 2 Using where; Using index -2 DEPENDENT SUBQUERY t1 index NULL a 4 NULL 1 Using index +2 DEPENDENT SUBQUERY t1 index_subquery a a 4 func 2 Using index SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); a 2009-01-01 diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 51f89fd2a51..f9337ddc78a 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -706,36 +706,34 @@ INSERT INTO t1 VALUES ('28','6','m'),('29','4','c'); CREATE TABLE t2 (f11 varchar(1)) ; INSERT INTO t2 VALUES ('f'),('d'); SET @old_optimizer_switch = @@session.optimizer_switch; -SET SESSION optimizer_switch = 'materialization=on,in_to_exists=off,'; +SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off'; EXPLAIN SELECT * FROM t1 WHERE f3 = ( SELECT t1.f3 FROM t1 -WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 )); +WHERE ( t1.f10 ) IN ( SELECT max(f11) FROM t2 GROUP BY f11 )); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref f3 f3 5 const 0 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 5 test.t1.f10 1 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using temporary SELECT * FROM t1 WHERE f3 = ( SELECT t1.f3 FROM t1 -WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 )); +WHERE ( t1.f10 ) IN ( SELECT max(f11) FROM t2 GROUP BY f11 )); f1 f3 f10 EXPLAIN SELECT * FROM t1 WHERE f3 = ( SELECT f3 FROM t1 -WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 )); +WHERE ( f10, f10 ) IN ( SELECT max(f11), f11 FROM t2 GROUP BY f11 )); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref f3 f3 5 const 0 Using where 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY <subquery3> eq_ref distinct_key distinct_key 10 test.t1.f10,test.t1.f10 1 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using temporary SELECT * FROM t1 WHERE f3 = ( SELECT f3 FROM t1 -WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 )); +WHERE ( f10, f10 ) IN ( SELECT max(f11), f11 FROM t2 GROUP BY f11 )); f1 f3 f10 SET SESSION optimizer_switch = @old_optimizer_switch; drop table t1,t2; @@ -1331,7 +1329,7 @@ EXPLAIN SELECT * FROM t1 WHERE (SELECT f2 FROM t2 WHERE f4 <= ALL -(SELECT SQ1_t1.f4 +(SELECT max(SQ1_t1.f4) FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 GROUP BY SQ1_t1.f4)); id select_type table type possible_keys key key_len ref rows Extra @@ -1624,68 +1622,68 @@ INSERT INTO t2 VALUES (10,5,'d1d'); set @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch='outer_join_with_cache=off'; -set @@optimizer_switch = 'materialization=off'; +set @@optimizer_switch = 'in_to_exists=on,materialization=off,semijoin=off'; EXPLAIN SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using where 1 PRIMARY alias1 index NULL PRIMARY 4 NULL 2 Using where; Using index -2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 2 Using temporary SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); f1 f2 8 8 EXPLAIN -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); f1b f2b f3b 10 5 d1d EXPLAIN -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); f1b f2b f3b 10 5 d1d -SET @@optimizer_switch = 'materialization=on'; +set @@optimizer_switch = 'in_to_exists=off,materialization=on,semijoin=off'; EXPLAIN SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using where 1 PRIMARY alias1 index NULL PRIMARY 4 NULL 2 Using where; Using index -2 MATERIALIZED t0 ALL NULL NULL NULL NULL 2 +2 MATERIALIZED t0 ALL NULL NULL NULL NULL 2 Using temporary SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); f1 f2 8 8 EXPLAIN -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 Using temporary +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); f1b f2b f3b 10 5 d1d EXPLAIN -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 Using temporary +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); f1b f2b f3b 10 5 d1d set @@optimizer_switch=@save_optimizer_switch; @@ -1770,7 +1768,7 @@ FROM t1)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 -3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 drop table t1, t2, t3; # # LP BUG#802979 Assertion `table->key_read == 0' in close_thread_table @@ -1793,7 +1791,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref f1 f1 5 const 0 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 -3 SUBQUERY t4 index NULL f1 5 NULL 2 Using index; Using temporary +3 SUBQUERY t4 index NULL f1 5 NULL 2 Using index SELECT * FROM t1, t2 WHERE t2.f2 = (SELECT f2 FROM t3 @@ -1810,7 +1808,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ref f1 f1 5 const 0 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 -3 SUBQUERY t4 index NULL f1 5 NULL 2 Using index; Using temporary +3 SUBQUERY t4 index NULL f1 5 NULL 2 Using index SELECT * FROM t1, t2 WHERE t2.f2 = (SELECT f2 FROM t3 @@ -1838,7 +1836,7 @@ FROM t1)); 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 t4 ALL NULL NULL NULL NULL 2 -3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 SELECT * FROM t2, t3 WHERE t3.f1 = ( @@ -1884,7 +1882,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL col_int_key 5 NULL 2 Using index 2 SUBQUERY SUBQUERY2_t1 index NULL col_int_key 5 NULL 2 Using index 2 SUBQUERY SUBQUERY2_t2 ALL col_varchar_key NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 Using temporary +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 SELECT col_int_key FROM t2 WHERE (SELECT SUBQUERY2_t1.col_int_key @@ -1906,7 +1904,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index NULL col_int_key 5 NULL 2 Using index 2 SUBQUERY SUBQUERY2_t1 index NULL col_int_key 5 NULL 2 Using index 2 SUBQUERY SUBQUERY2_t2 ALL col_varchar_key NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where SELECT col_int_key FROM t2 WHERE (SELECT SUBQUERY2_t1.col_int_key diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index 402c18f2bbd..de0914e5f18 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -291,7 +291,7 @@ GROUP BY 1 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 ALL NULL NULL NULL NULL 1 -3 SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using temporary; Using filesort +3 SUBQUERY t3 ALL NULL NULL NULL NULL 1 SELECT MAX( f1 ) FROM t2 WHERE f2 >= ( SELECT SUM( f1 ) diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index f368f787407..7225cf7c825 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -59,9 +59,9 @@ explain extended select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select `test`.`t2`.`b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery2>`.`b1`)))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select `test`.`t2`.`b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery2>`.`b1`)))))) select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 @@ -70,9 +70,9 @@ explain extended select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery2>`.`b1`) and (`test`.`t1`.`a2` = `<subquery2>`.`b2`)))))) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery2>`.`b1`) and (`test`.`t1`.`a2` = `<subquery2>`.`b2`)))))) select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); a1 a2 1 - 01 2 - 01 @@ -100,13 +100,13 @@ a1 a2 1 - 01 2 - 01 1 - 02 2 - 02 explain extended -select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); +select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1i index NULL # 18 # 3 100.00 # -2 MATERIALIZED t2i range it2i1,it2i3 # 9 # 3 100.00 # +2 MATERIALIZED t2i index it2i1,it2i3 # 9 # 5 100.00 # Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a1`>(<in_optimizer>(`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( <materialize> (select `test`.`t2i`.`b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `<subquery2>`.`b1`)))))) -select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a1`>(<in_optimizer>(`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( <materialize> (select max(`test`.`t2i`.`b1`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `<subquery2>`.`max(b1)`)))))) +select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 1 - 02 2 - 02 @@ -122,13 +122,13 @@ a1 a2 1 - 01 2 - 01 1 - 02 2 - 02 explain extended -select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); +select * from t1i where (a1, a2) in (select b1, max(b2) from t2i where b1 > '0' group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1i index NULL # # # 3 100.00 # 2 MATERIALIZED t2i range it2i1,it2i3 # # # 3 100.00 # Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a1`,`test`.`t1i`.`a2`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `<subquery2>`.`b1`) and (`test`.`t1i`.`a2` = `<subquery2>`.`b2`)))))) -select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a1`,`test`.`t1i`.`a2`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `<subquery2>`.`b1`) and (`test`.`t1i`.`a2` = `<subquery2>`.`max(b2)`)))))) +select * from t1i where (a1, a2) in (select b1, max(b2) from t2i where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 1 - 02 2 - 02 @@ -394,7 +394,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 8 MATERIALIZED t2i index it2i1,it2i3 # # # 5 100.00 # NULL UNION RESULT <union1,7> ALL NULL # # # NULL NULL # Warnings: -Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery3>`.`c2`)))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery2>`.`b1`) and (`test`.`t1`.`a2` = `<subquery2>`.`b2`)))))) and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t3`.`c1`,`test`.`t3`.`c2` from `test`.`t3` where <expr_cache><`test`.`t3`.`c1`,`test`.`t3`.`c2`>(<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key where ((`test`.`t3`.`c1` = `<subquery6>`.`b1`) and (`test`.`t3`.`c2` = `<subquery6>`.`b2`)))))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery5>`.`c1`) and (`test`.`t1`.`a2` = `<subquery5>`.`c2`)))))))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (<expr_cache><`test`.`t1i`.`a1`,`test`.`t1i`.`a2`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `<subquery8>`.`b1`) and (`test`.`t1i`.`a2` = `<subquery8>`.`b2`)))))) and <expr_cache><`test`.`t1i`.`a1`,`test`.`t1i`.`a2`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t3i`.`c1`,`test`.`t3i`.`c2` from `test`.`t3i` where <expr_cache><`test`.`t3i`.`c1`,`test`.`t3i`.`c2`>(<in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3i`.`c1` in <temporary table> on distinct_key where ((`test`.`t3i`.`c1` = `<subquery10>`.`b1`) and (`test`.`t3i`.`c2` = `<subquery10>`.`b2`)))))) ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `<subquery9>`.`c1`) and (`test`.`t1i`.`a2` = `<subquery9>`.`c2`)))))))) +Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery3>`.`c2`)))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery2>`.`b1`) and (`test`.`t1`.`a2` = `<subquery2>`.`b2`)))))) and <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t3`.`c1`,`test`.`t3`.`c2` from `test`.`t3` where <expr_cache><`test`.`t3`.`c1`,`test`.`t3`.`c2`>(<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key where ((`test`.`t3`.`c1` = `<subquery6>`.`b1`) and (`test`.`t3`.`c2` = `<subquery6>`.`b2`)))))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `<subquery5>`.`c1`) and (`test`.`t1`.`a2` = `<subquery5>`.`c2`)))))))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (<expr_cache><`test`.`t1i`.`a1`,`test`.`t1i`.`a2`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `<subquery8>`.`b1`) and (`test`.`t1i`.`a2` = `<subquery8>`.`b2`)))))) and <expr_cache><`test`.`t1i`.`a1`,`test`.`t1i`.`a2`>(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t3i`.`c1`,`test`.`t3i`.`c2` from `test`.`t3i` where <expr_cache><`test`.`t3i`.`c1`,`test`.`t3i`.`c2`>(<in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3i`.`c1` in <temporary table> on distinct_key where ((`test`.`t3i`.`c1` = `<subquery10>`.`b1`) and (`test`.`t3i`.`c2` = `<subquery10>`.`b2`)))))) ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `<subquery9>`.`c1`) and (`test`.`t1i`.`a2` = `<subquery9>`.`c2`)))))))) (select * from t1 where (a1, a2) in (select b1, b2 from t2 where b2 in (select c2 from t3 where c2 LIKE '%02') or @@ -1150,21 +1150,21 @@ drop table t2; create table t1 (a1 int key); create table t2 (b1 int); insert into t1 values (5); -explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1); +explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 2 MATERIALIZED t2 system NULL NULL NULL NULL 0 const row not found -select min(a1) from t1 where 7 in (select b1 from t2 group by b1); +select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); min(a1) NULL set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch=@optimizer_switch_local_default; set @@optimizer_switch='materialization=off,in_to_exists=on'; -explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1); +explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); 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 t2 system NULL NULL NULL NULL 0 const row not found -select min(a1) from t1 where 7 in (select b1 from t2 group by b1); +select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); min(a1) NULL set @@optimizer_switch=@optimizer_switch_local_default; @@ -1231,7 +1231,7 @@ WHERE (t1i) IN ( SELECT t2i FROM t2 WHERE (t2i) IN ( -SELECT t3i +SELECT max(t3i) FROM t3 GROUP BY t3i ) diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index 87e1060aa87..a8fadfe43bd 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -141,14 +141,14 @@ CREATE TABLE t3 ( f2 varchar(1)) ; EXPLAIN SELECT f2 FROM t3 WHERE ( SELECT MAX( pk ) FROM t1 WHERE EXISTS ( -SELECT DISTINCT f1 -FROM t2 +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 +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 diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 59d108dc5c0..c55cb0a425a 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -1669,7 +1669,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select NULL from `test`.`t2` group by 1) > <cache>(`test`.`t3`.`a`)))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`)))) select * from t3 where a >= some (select b from t2 group by 1); a explain extended select * from t3 where a >= some (select b from t2 group by 1); @@ -1677,7 +1677,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(<min>(select NULL from `test`.`t2` group by 1) <= <cache>(`test`.`t3`.`a`)))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`)))) select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); @@ -1693,7 +1693,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(<min>(select NULL from `test`.`t2` group by 1) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) select * from t3 where NULL >= some (select b from t2); a explain extended select * from t3 where NULL >= some (select b from t2); @@ -1709,7 +1709,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(<min>(select NULL from `test`.`t2` group by 1) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); a @@ -2986,9 +2986,9 @@ Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `tes explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; set optimizer_switch=@tmp11867_optimizer_switch; CREATE TABLE t1 (a char(5), b char(5)); @@ -4467,18 +4467,18 @@ a drop table t1, t2; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,<exists>(select 1 from `test`.`t1` group by `test`.`t1`.`a` having (1 = <ref_null_helper>(1))))) -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); +Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` having (1 = <ref_null_helper>(min(`test`.`t1`.`a`)))))) +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,<exists>(select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (1 = <ref_null_helper>(1))))) +Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (1 = <ref_null_helper>(min(`test`.`t1`.`a`)))))) DROP TABLE t1; # # Bug#45061: Incorrectly market field caused wrong result. @@ -4822,8 +4822,6 @@ SELECT * FROM t1 WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b); pk a 1 10 -3 30 -2 20 DROP TABLE t1,t2; CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY b (b)); INSERT INTO t1 VALUES (1,NULL), (9,NULL); @@ -5247,18 +5245,21 @@ DROP table t1,t2; # CREATE TABLE t1 (a int, b int) ; INSERT INTO t1 VALUES (0,0),(0,0); +set @optimizer_switch_save=@@optimizer_switch; +set @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=on'; EXPLAIN SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; b 0 +set @@optimizer_switch=@optimizer_switch_save; DROP TABLE t1; # # Bug #11765713 58705: @@ -5654,7 +5655,7 @@ WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY table1 ALL NULL NULL NULL NULL 2 1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY t2 ALL f1_key NULL NULL NULL 10 Range checked for each record (index map: 0x1); Using temporary +2 DEPENDENT SUBQUERY t2 index f1_key f1_key 4 NULL 10 Using where; Using index DROP TABLE t1,t2; # # LP bug #826279: assertion failure with GROUP BY a result of subquery @@ -5882,7 +5883,7 @@ SET @@optimizer_switch='semijoin=off,materialization=off,in_to_exists=on,subquer EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL a 4 NULL 2 Using where; Using index -2 DEPENDENT SUBQUERY t1 index NULL a 4 NULL 1 Using index +2 DEPENDENT SUBQUERY t1 index_subquery a a 4 func 2 Using index SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); a 2009-01-01 diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index d4c3694f2c6..0b77366469c 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -1665,7 +1665,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select NULL from `test`.`t2` group by 1) > <cache>(`test`.`t3`.`a`)))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`)))) select * from t3 where a >= some (select b from t2 group by 1); a explain extended select * from t3 where a >= some (select b from t2 group by 1); @@ -1673,7 +1673,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(<min>(select NULL from `test`.`t2` group by 1) <= <cache>(`test`.`t3`.`a`)))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`)))) select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); @@ -1689,7 +1689,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(<min>(select NULL from `test`.`t2` group by 1) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) select * from t3 where NULL >= some (select b from t2); a explain extended select * from t3 where NULL >= some (select b from t2); @@ -1705,7 +1705,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(<min>(select NULL from `test`.`t2` group by 1) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); a @@ -2982,9 +2982,9 @@ Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `tes explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; set optimizer_switch=@tmp11867_optimizer_switch; CREATE TABLE t1 (a char(5), b char(5)); @@ -4463,18 +4463,18 @@ a drop table t1, t2; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 from `test`.`t1` group by `test`.`t1`.`a` having (1 = <ref_null_helper>(1)))) -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); +Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` having (1 = <ref_null_helper>(min(`test`.`t1`.`a`))))) +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (1 = <ref_null_helper>(1)))) +Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (1 = <ref_null_helper>(min(`test`.`t1`.`a`))))) DROP TABLE t1; # # Bug#45061: Incorrectly market field caused wrong result. @@ -4818,8 +4818,6 @@ SELECT * FROM t1 WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b); pk a 1 10 -3 30 -2 20 DROP TABLE t1,t2; CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY b (b)); INSERT INTO t1 VALUES (1,NULL), (9,NULL); @@ -5243,18 +5241,21 @@ DROP table t1,t2; # CREATE TABLE t1 (a int, b int) ; INSERT INTO t1 VALUES (0,0),(0,0); +set @optimizer_switch_save=@@optimizer_switch; +set @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=on'; EXPLAIN SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; b 0 +set @@optimizer_switch=@optimizer_switch_save; DROP TABLE t1; # # Bug #11765713 58705: @@ -5650,7 +5651,7 @@ WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY table1 ALL NULL NULL NULL NULL 2 1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY t2 ALL f1_key NULL NULL NULL 10 Range checked for each record (index map: 0x1); Using temporary +2 DEPENDENT SUBQUERY t2 index f1_key f1_key 4 NULL 10 Using where; Using index DROP TABLE t1,t2; # # LP bug #826279: assertion failure with GROUP BY a result of subquery @@ -5878,7 +5879,7 @@ SET @@optimizer_switch='semijoin=off,materialization=off,in_to_exists=on,subquer EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL a 4 NULL 2 Using where; Using index -2 DEPENDENT SUBQUERY t1 index NULL a 4 NULL 1 Using index +2 DEPENDENT SUBQUERY t1 index_subquery a a 4 func 2 Using index SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); a 2009-01-01 diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index aac4ce6e39d..fb9cda3c412 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -1668,7 +1668,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select NULL from `test`.`t2` group by 1) > <cache>(`test`.`t3`.`a`)))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`)))) select * from t3 where a >= some (select b from t2 group by 1); a explain extended select * from t3 where a >= some (select b from t2 group by 1); @@ -1676,7 +1676,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(<min>(select NULL from `test`.`t2` group by 1) <= <cache>(`test`.`t3`.`a`)))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`)))) select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); @@ -1692,7 +1692,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(<min>(select NULL from `test`.`t2` group by 1) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) select * from t3 where NULL >= some (select b from t2); a explain extended select * from t3 where NULL >= some (select b from t2); @@ -1708,7 +1708,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(<min>(select NULL from `test`.`t2` group by 1) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); a @@ -2986,9 +2986,9 @@ Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `tes explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; set optimizer_switch=@tmp11867_optimizer_switch; CREATE TABLE t1 (a char(5), b char(5)); @@ -4469,20 +4469,20 @@ a drop table t1, t2; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 -1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 +1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1 100.00 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary Warnings: -Note 1003 select 1 AS `1` from <materialize> (select 1 from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`1` = 1) -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); +Note 1003 select 1 AS `1` from <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`min(a)` = 1) +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 -1 PRIMARY <subquery2> const distinct_key distinct_key 4 const 1 100.00 +1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1 100.00 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary Warnings: -Note 1003 select 1 AS `1` from <materialize> (select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`1` = 1) +Note 1003 select 1 AS `1` from <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`<subquery2>`.`min(a)` = 1) DROP TABLE t1; # # Bug#45061: Incorrectly market field caused wrong result. @@ -4826,8 +4826,6 @@ SELECT * FROM t1 WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b); pk a 1 10 -3 30 -2 20 DROP TABLE t1,t2; CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY b (b)); INSERT INTO t1 VALUES (1,NULL), (9,NULL); @@ -5252,18 +5250,21 @@ DROP table t1,t2; # CREATE TABLE t1 (a int, b int) ; INSERT INTO t1 VALUES (0,0),(0,0); +set @optimizer_switch_save=@@optimizer_switch; +set @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=on'; EXPLAIN SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; b 0 +set @@optimizer_switch=@optimizer_switch_save; DROP TABLE t1; # # Bug #11765713 58705: @@ -5659,7 +5660,7 @@ WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY table1 ALL NULL NULL NULL NULL 2 1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY t2 ALL f1_key NULL NULL NULL 10 Range checked for each record (index map: 0x1); Using temporary +2 DEPENDENT SUBQUERY t2 index f1_key f1_key 4 NULL 10 Using where; Using index DROP TABLE t1,t2; # # LP bug #826279: assertion failure with GROUP BY a result of subquery @@ -5887,7 +5888,7 @@ SET @@optimizer_switch='semijoin=off,materialization=off,in_to_exists=on,subquer EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL a 4 NULL 2 Using where; Using index -2 DEPENDENT SUBQUERY t1 index NULL a 4 NULL 1 Using index +2 DEPENDENT SUBQUERY t1 index_subquery a a 4 func 2 Using index SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); a 2009-01-01 diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 3647e2fe2b3..381b2f12665 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -1665,7 +1665,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,(<max>(select NULL from `test`.`t2` group by 1) > <cache>(`test`.`t3`.`a`)))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <not>(<in_optimizer>(`test`.`t3`.`a`,((select max(NULL) from `test`.`t2`) > <cache>(`test`.`t3`.`a`)))) select * from t3 where a >= some (select b from t2 group by 1); a explain extended select * from t3 where a >= some (select b from t2 group by 1); @@ -1673,7 +1673,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 Using where 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,(<min>(select NULL from `test`.`t2` group by 1) <= <cache>(`test`.`t3`.`a`)))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(`test`.`t3`.`a`,((select min(NULL) from `test`.`t2`) <= <cache>(`test`.`t3`.`a`)))) select * from t3 where NULL >= any (select b from t2); a explain extended select * from t3 where NULL >= any (select b from t2); @@ -1689,7 +1689,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(<min>(select NULL from `test`.`t2` group by 1) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) select * from t3 where NULL >= some (select b from t2); a explain extended select * from t3 where NULL >= some (select b from t2); @@ -1705,7 +1705,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00 2 SUBQUERY t2 system NULL NULL NULL NULL 0 0.00 const row not found Warnings: -Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,(<min>(select NULL from `test`.`t2` group by 1) <= NULL))) +Note 1003 select `test`.`t3`.`a` AS `a` from `test`.`t3` where <nop>(<in_optimizer>(NULL,((select min(NULL) from `test`.`t2`) <= NULL))) insert into t2 values (2,2), (2,1), (3,3), (3,1); select * from t3 where a > all (select max(b) from t2 group by a); a @@ -2982,9 +2982,9 @@ Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `tes explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 9 100.00 Using where; Using temporary +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 9 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),(`test`.`t1`.`one`,`test`.`t1`.`two`) in ( <materialize> (select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` ), <primary_index_lookup>(`test`.`t1`.`one` in <temporary table> on distinct_key where ((`test`.`t1`.`one` = `<subquery2>`.`one`) and (`test`.`t1`.`two` = `<subquery2>`.`two`)))))) AS `test` from `test`.`t1` +Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),(`test`.`t1`.`one`,`test`.`t1`.`two`) in ( <materialize> (select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') ), <primary_index_lookup>(`test`.`t1`.`one` in <temporary table> on distinct_key where ((`test`.`t1`.`one` = `<subquery2>`.`one`) and (`test`.`t1`.`two` = `<subquery2>`.`two`)))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; set optimizer_switch=@tmp11867_optimizer_switch; CREATE TABLE t1 (a char(5), b char(5)); @@ -4463,18 +4463,18 @@ a drop table t1, t2; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,1 in ( <materialize> (select 1 from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery2>`.`1`)))))) -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); +Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,1 in ( <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery2>`.`min(a)`)))))) +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,1 in ( <materialize> (select 1 from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery2>`.`1`)))))) +Note 1003 select 1 AS `1` from `test`.`t1` where <expr_cache><1>(<in_optimizer>(1,1 in ( <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery2>`.`min(a)`)))))) DROP TABLE t1; # # Bug#45061: Incorrectly market field caused wrong result. @@ -4818,8 +4818,6 @@ SELECT * FROM t1 WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b); pk a 1 10 -3 30 -2 20 DROP TABLE t1,t2; CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY b (b)); INSERT INTO t1 VALUES (1,NULL), (9,NULL); @@ -5243,18 +5241,21 @@ DROP table t1,t2; # CREATE TABLE t1 (a int, b int) ; INSERT INTO t1 VALUES (0,0),(0,0); +set @optimizer_switch_save=@@optimizer_switch; +set @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=on'; EXPLAIN SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort -2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; b 0 +set @@optimizer_switch=@optimizer_switch_save; DROP TABLE t1; # # Bug #11765713 58705: @@ -5650,7 +5651,7 @@ WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY table1 ALL NULL NULL NULL NULL 2 1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY t2 ALL f1_key NULL NULL NULL 10 Range checked for each record (index map: 0x1); Using temporary +2 DEPENDENT SUBQUERY t2 index f1_key f1_key 4 NULL 10 Using where; Using index DROP TABLE t1,t2; # # LP bug #826279: assertion failure with GROUP BY a result of subquery @@ -5878,7 +5879,7 @@ SET @@optimizer_switch='semijoin=off,materialization=off,in_to_exists=on,subquer EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL a 4 NULL 2 Using where; Using index -2 DEPENDENT SUBQUERY t1 index NULL a 4 NULL 1 Using index +2 DEPENDENT SUBQUERY t1 index_subquery a a 4 func 2 Using index SELECT * FROM t1 WHERE a IN (SELECT a AS field1 FROM t1 GROUP BY field1); a 2009-01-01 diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 1f84da5433d..80c24652cbd 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -972,10 +972,18 @@ SET join_cache_level = 3; EXPLAIN SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a); +a b +v v +EXPLAIN +SELECT * FROM t1 WHERE b IN (SELECT max(a) FROM t2 GROUP BY a); +id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where 1 PRIMARY <subquery2> hash_ALL distinct_key #hash#distinct_key 5 test.t1.b 1 Using join buffer (flat, BNLH join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 Using temporary -SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a); +SELECT * FROM t1 WHERE b IN (SELECT max(a) FROM t2 GROUP BY a); a b v v set optimizer_switch=@tmp_optimizer_switch; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index f8d8505926c..82f012bcbf1 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -58,11 +58,11 @@ a1 a2 explain extended select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 9 test.t1.a1 1 100.00 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 9 func 1 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`) join `test`.`t1` where (`<subquery2>`.`b1` = `test`.`t1`.`a1`) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b1` > '0')) select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 @@ -70,11 +70,11 @@ a1 a2 explain extended select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 18 func,func 1 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`<subquery2>`.`b2` = `test`.`t1`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1`.`a1`)) +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b1` > '0')) select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2); a1 a2 1 - 01 2 - 01 @@ -103,14 +103,14 @@ a1 a2 1 - 01 2 - 01 1 - 02 2 - 02 explain extended -select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); +select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key # NULL # 3 100.00 # -1 PRIMARY t1i ref it1i1,it1i3 # 9 # 1 100.00 # -2 MATERIALIZED t2i range it2i1,it2i3 # 9 # 3 100.00 # +1 PRIMARY t1i index it1i1,it1i3 # 18 # 3 100.00 # +1 PRIMARY <subquery2> eq_ref distinct_key # 9 # 1 100.00 # +2 MATERIALIZED t2i index it2i1,it2i3 # 9 # 5 100.00 # Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (`test`.`t1i`.`a1` = `<subquery2>`.`b1`) -select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select max(`test`.`t2i`.`b1`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (`<subquery2>`.`max(b1)` = `test`.`t1i`.`a1`) +select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 1 - 02 2 - 02 @@ -126,14 +126,14 @@ a1 a2 1 - 01 2 - 01 1 - 02 2 - 02 explain extended -select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); +select * from t1i where (a1, a2) in (select b1, max(b2) from t2i where b1 > '0' group by b1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1i index it1i1,it1i2,it1i3 # # # 3 100.00 # 1 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # 2 MATERIALIZED t2i range it2i1,it2i3 # # # 3 100.00 # Warnings: -Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1`,`test`.`t2i`.`b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`,`test`.`t2i`.`b2`) join `test`.`t1i` where ((`<subquery2>`.`b2` = `test`.`t1i`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1i`.`a1`)) -select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); +Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where ((`<subquery2>`.`max(b2)` = `test`.`t1i`.`a2`) and (`<subquery2>`.`b1` = `test`.`t1i`.`a1`)) +select * from t1i where (a1, a2) in (select b1, max(b2) from t2i where b1 > '0' group by b1); a1 a2 1 - 01 2 - 01 1 - 02 2 - 02 @@ -399,10 +399,11 @@ where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and (a1, a2) in (select c1, c2 from t3i where (c1, c2) in (select b1, b2 from t2i where b2 > '0'))); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key # # # 5 0.00 # -1 PRIMARY t3 ALL NULL # # # 4 100.00 # 1 PRIMARY t1 ALL NULL # # # 3 100.00 # -1 PRIMARY t2i ref it2i1,it2i2,it2i3 # # # 2 100.00 # +1 PRIMARY <subquery5> eq_ref distinct_key # # # 1 100.00 # +1 PRIMARY <subquery2> eq_ref distinct_key # # # 1 100.00 # +5 MATERIALIZED t3 ALL NULL # # # 4 100.00 # +5 MATERIALIZED t2i index it2i1,it2i2,it2i3 # # # 5 80.00 # 2 MATERIALIZED t2 ALL NULL # # # 5 100.00 # 4 MATERIALIZED t3 ALL NULL # # # 4 100.00 # 3 MATERIALIZED t3 ALL NULL # # # 4 100.00 # @@ -412,7 +413,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 7 UNION t2i ref it2i1,it2i2,it2i3 # # # 2 100.00 # NULL UNION RESULT <union1,7> ALL NULL # # # NULL NULL # Warnings: -Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery3>`.`c2`)))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2`) semi join (`test`.`t2i` join `test`.`t3`) join `test`.`t1` where ((`test`.`t3`.`c2` = `<subquery2>`.`b2`) and (`test`.`t1`.`a2` = `<subquery2>`.`b2`) and (`test`.`t2i`.`b2` = `<subquery2>`.`b2`) and (`test`.`t3`.`c1` = `<subquery2>`.`b1`) and (`test`.`t1`.`a1` = `<subquery2>`.`b1`) and (`test`.`t2i`.`b1` = `<subquery2>`.`b1`) and (`<subquery2>`.`b2` > '0'))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0') and (`test`.`t2i`.`b2` > '0'))) +Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t2i`.`b2` = `test`.`t3`.`c2`) and (`test`.`t2i`.`b1` = `test`.`t3`.`c1`) and (<expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery3>`.`c2`)))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))) and (`test`.`t3`.`c2` > '0'))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0') and (`test`.`t2i`.`b2` > '0'))) (select * from t1 where (a1, a2) in (select b1, b2 from t2 where b2 in (select c2 from t3 where c2 LIKE '%02') or @@ -1183,22 +1184,22 @@ drop table t2; create table t1 (a1 int key); create table t2 (b1 int); insert into t1 values (5); -explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1); +explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY <subquery2> const distinct_key distinct_key 5 const 1 2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table -select min(a1) from t1 where 7 in (select b1 from t2 group by b1); +select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); min(a1) NULL set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch=@optimizer_switch_local_default; set @@optimizer_switch='materialization=off,in_to_exists=on'; -explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1); +explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); 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 t2 system NULL NULL NULL NULL 0 const row not found -select min(a1) from t1 where 7 in (select b1 from t2 group by b1); +select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); min(a1) NULL set @@optimizer_switch=@optimizer_switch_local_default; @@ -1265,7 +1266,7 @@ WHERE (t1i) IN ( SELECT t2i FROM t2 WHERE (t2i) IN ( -SELECT t3i +SELECT max(t3i) FROM t3 GROUP BY t3i ) diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index d4bc8bc662b..4dcce4d0373 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -1425,9 +1425,9 @@ DROP TABLE t1; create table t1 (a bit(1) not null,b int) engine=myisam; create table t2 (c int) engine=innodb; set @save_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=off'; +set @@optimizer_switch='in_to_exists=on,materialization=off'; explain -select b from t1 where a not in (select b from t1,t2 group by a) group by a; +select b from t1 where a not in (select max(b) from t1,t2 group by a) group by a; 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 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found diff --git a/mysql-test/suite/innodb_plugin/r/innodb_mysql.result b/mysql-test/suite/innodb_plugin/r/innodb_mysql.result index d42c61fe3ac..5a91335beeb 100644 --- a/mysql-test/suite/innodb_plugin/r/innodb_mysql.result +++ b/mysql-test/suite/innodb_plugin/r/innodb_mysql.result @@ -1425,9 +1425,9 @@ DROP TABLE t1; create table t1 (a bit(1) not null,b int) engine=myisam; create table t2 (c int) engine=innodb; set @save_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=off'; +set @@optimizer_switch='in_to_exists=on,materialization=off'; explain -select b from t1 where a not in (select b from t1,t2 group by a) group by a; +select b from t1 where a not in (select max(b) from t1,t2 group by a) group by a; 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 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test index 8700cd48c45..d07f36b37a4 100644 --- a/mysql-test/t/explain.test +++ b/mysql-test/t/explain.test @@ -243,12 +243,12 @@ FULLTEXT KEY(f1),UNIQUE(f1)); INSERT INTO t1 VALUES ('test'); EXPLAIN SELECT 1 FROM t1 -WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) +WHERE 1 > ALL((SELECT t1.f1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) WHERE t1.f1 GROUP BY t1.f1)); PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 - WHERE 1 > ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a + WHERE 1 > ALL((SELECT t1.f1 FROM t1 RIGHT OUTER JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) WHERE t1.f1 GROUP BY t1.f1))'; @@ -259,7 +259,7 @@ DEALLOCATE PREPARE stmt; PREPARE stmt FROM 'EXPLAIN SELECT 1 FROM t1 - WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a + WHERE 1 > ALL((SELECT t1.f1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) WHERE t1.f1 GROUP BY t1.f1))'; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index d53ba706388..2540232bd3b 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -3418,8 +3418,8 @@ drop table t1, t2; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a); -EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 GROUP BY a); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT min(a) FROM t1 WHERE a > 3 GROUP BY a); DROP TABLE t1; --echo # @@ -4442,7 +4442,8 @@ DROP table t1,t2; CREATE TABLE t1 (a int, b int) ; INSERT INTO t1 VALUES (0,0),(0,0); - +set @optimizer_switch_save=@@optimizer_switch; +set @@optimizer_switch='semijoin=off,materialization=on,in_to_exists=on'; EXPLAIN SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) @@ -4451,7 +4452,7 @@ SELECT b FROM t1 SELECT b FROM t1 WHERE ('0') IN ( SELECT a FROM t1 GROUP BY a ) GROUP BY b; - +set @@optimizer_switch=@optimizer_switch_save; DROP TABLE t1; --echo # diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index aa3cb30c6f3..0e51373fb01 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -642,27 +642,27 @@ CREATE TABLE t2 (f11 varchar(1)) ; INSERT INTO t2 VALUES ('f'),('d'); SET @old_optimizer_switch = @@session.optimizer_switch; -SET SESSION optimizer_switch = 'materialization=on,in_to_exists=off,'; +SET @@optimizer_switch = 'materialization=on,in_to_exists=off,semijoin=off'; EXPLAIN SELECT * FROM t1 WHERE f3 = ( SELECT t1.f3 FROM t1 - WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 )); + WHERE ( t1.f10 ) IN ( SELECT max(f11) FROM t2 GROUP BY f11 )); SELECT * FROM t1 WHERE f3 = ( SELECT t1.f3 FROM t1 - WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 )); + WHERE ( t1.f10 ) IN ( SELECT max(f11) FROM t2 GROUP BY f11 )); EXPLAIN SELECT * FROM t1 WHERE f3 = ( SELECT f3 FROM t1 - WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 )); + WHERE ( f10, f10 ) IN ( SELECT max(f11), f11 FROM t2 GROUP BY f11 )); SELECT * FROM t1 WHERE f3 = ( SELECT f3 FROM t1 - WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 )); + WHERE ( f10, f10 ) IN ( SELECT max(f11), f11 FROM t2 GROUP BY f11 )); SET SESSION optimizer_switch = @old_optimizer_switch; drop table t1,t2; @@ -1084,7 +1084,7 @@ EXPLAIN SELECT * FROM t1 WHERE (SELECT f2 FROM t2 WHERE f4 <= ALL - (SELECT SQ1_t1.f4 + (SELECT max(SQ1_t1.f4) FROM t3 AS SQ1_t1 JOIN t3 AS SQ1_t3 ON SQ1_t3.f4 GROUP BY SQ1_t1.f4)); @@ -1305,47 +1305,47 @@ INSERT INTO t2 VALUES set @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch='outer_join_with_cache=off'; -set @@optimizer_switch = 'materialization=off'; +set @@optimizer_switch = 'in_to_exists=on,materialization=off,semijoin=off'; EXPLAIN SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); EXPLAIN -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); EXPLAIN -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); -SET @@optimizer_switch = 'materialization=on'; +set @@optimizer_switch = 'in_to_exists=off,materialization=on,semijoin=off'; EXPLAIN SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); SELECT alias2.f1 , alias2.f2 FROM t0 AS alias1 RIGHT JOIN t0 AS alias2 ON alias2.f10 -WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t0 GROUP BY f2 , f1 ); +WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT max(f2) , f1 FROM t0 GROUP BY f2 , f1 ); EXPLAIN -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); -SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT max(f1a), f2a FROM t1 GROUP BY f1a, f2a); EXPLAIN -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); -SELECT * FROM t2 WHERE (f1b) IN (SELECT f1a FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); +SELECT * FROM t2 WHERE (f1b) IN (SELECT max(f1a) FROM t1 GROUP BY f1a, f2a); set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test index 463685bbdf0..8205e94b203 100644 --- a/mysql-test/t/subselect_mat_cost_bugs.test +++ b/mysql-test/t/subselect_mat_cost_bugs.test @@ -167,8 +167,8 @@ CREATE TABLE t3 ( f2 varchar(1)) ; EXPLAIN SELECT f2 FROM t3 WHERE ( SELECT MAX( pk ) FROM t1 WHERE EXISTS ( - SELECT DISTINCT f1 - FROM t2 + SELECT max(f1) + FROM t2 GROUP BY f1 ) ) IS NULL ; diff --git a/mysql-test/t/subselect_sj2_jcl6.test b/mysql-test/t/subselect_sj2_jcl6.test index 09212ba63d0..fbc474f7067 100644 --- a/mysql-test/t/subselect_sj2_jcl6.test +++ b/mysql-test/t/subselect_sj2_jcl6.test @@ -94,6 +94,10 @@ EXPLAIN SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a); SELECT * FROM t1 WHERE b IN (SELECT a FROM t2 GROUP BY a); +EXPLAIN +SELECT * FROM t1 WHERE b IN (SELECT max(a) FROM t2 GROUP BY a); +SELECT * FROM t1 WHERE b IN (SELECT max(a) FROM t2 GROUP BY a); + set optimizer_switch=@tmp_optimizer_switch; set join_cache_level=default; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index b9e0beaa5e3..2a5b0f56877 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -88,8 +88,8 @@ select * from t1i where a1 in (select b1 from t2i where b1 > '0'); --replace_column 6 # 8 # 11 # explain extended -select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); -select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1); +select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); +select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); --replace_column 7 # --replace_regex /it1.*/_it1_idx/ /test.t2i.*/_ref_/ /Using index$// /Using where$// @@ -99,8 +99,8 @@ select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); --replace_column 6 # 7 # 8 # 11 # explain extended -select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); -select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2); +select * from t1i where (a1, a2) in (select b1, max(b2) from t2i where b1 > '0' group by b1); +select * from t1i where (a1, a2) in (select b1, max(b2) from t2i where b1 > '0' group by b1); --replace_column 6 # 7 # 8 # 11 # explain extended @@ -835,14 +835,14 @@ create table t2 (b1 int); insert into t1 values (5); # Query with group by, executed via materialization -explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1); -select min(a1) from t1 where 7 in (select b1 from t2 group by b1); +explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); +select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); # Query with group by, executed via IN=>EXISTS set @save_optimizer_switch=@@optimizer_switch; set @@optimizer_switch=@optimizer_switch_local_default; set @@optimizer_switch='materialization=off,in_to_exists=on'; -explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1); -select min(a1) from t1 where 7 in (select b1 from t2 group by b1); +explain select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); +select min(a1) from t1 where 7 in (select max(b1) from t2 group by b1); # Executed with materialization set @@optimizer_switch=@optimizer_switch_local_default; @@ -900,7 +900,7 @@ WHERE (t1i) IN ( SELECT t2i FROM t2 WHERE (t2i) IN ( - SELECT t3i + SELECT max(t3i) FROM t3 GROUP BY t3i ) diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 00f5c7a948a..48522f8a70f 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -178,7 +178,7 @@ void Item_allany_subselect::cleanup() */ for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select()) - if (test_strategy(SUBS_MAXMIN_INJECTED)) + if (test_set_strategy(SUBS_MAXMIN_INJECTED)) sl->with_sum_func= false; Item_in_subselect::cleanup(); } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 18925f0f83f..9ad945a22b3 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -425,6 +425,73 @@ fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select, } /** + The following clauses are redundant for subqueries: + + DISTINCT + GROUP BY if there are no aggregate functions and no HAVING + clause + + Because redundant clauses are removed both from JOIN and + select_lex, the removal is permanent. Thus, it only makes sense to + call this function for normal queries and on first execution of + SP/PS + + @param subq_select_lex select_lex that is part of a subquery + predicate. This object and the associated + join is modified. +*/ + +static +void remove_redundant_subquery_clauses(st_select_lex *subq_select_lex) +{ + Item_subselect *subq_predicate= subq_select_lex->master_unit()->item; + /* + The removal should happen for IN, ALL, ANY and EXISTS subqueries, + which means all but single row subqueries. Example single row + subqueries: + a) SELECT * FROM t1 WHERE t1.a = (<single row subquery>) + b) SELECT a, (<single row subquery) FROM t1 + */ + if (subq_predicate->substype() == Item_subselect::SINGLEROW_SUBS) + return; + + /* A subquery that is not single row should be one of IN/ALL/ANY/EXISTS. */ + DBUG_ASSERT (subq_predicate->substype() == Item_subselect::EXISTS_SUBS || + subq_predicate->is_in_predicate()); + + if (subq_select_lex->options & SELECT_DISTINCT) + { + subq_select_lex->join->select_distinct= false; + subq_select_lex->options&= ~SELECT_DISTINCT; + } + + /* + Remove GROUP BY if there are no aggregate functions and no HAVING + clause + */ + if (subq_select_lex->group_list.elements && + !subq_select_lex->with_sum_func && !subq_select_lex->join->having) + { + subq_select_lex->join->group_list= NULL; + subq_select_lex->group_list.empty(); + } + + /* + TODO: This would prevent processing quries with ORDER BY ... LIMIT + therefore we disable this optimization for now. + Remove GROUP BY if there are no aggregate functions and no HAVING + clause + if (subq_select_lex->group_list.elements && + !subq_select_lex->with_sum_func && !subq_select_lex->join->having) + { + subq_select_lex->join->group_list= NULL; + subq_select_lex->group_list.empty(); + } + */ +} + + +/** Function to setup clauses without sum functions. */ inline int setup_without_group(THD *thd, Item **ref_pointer_array, @@ -521,6 +588,22 @@ JOIN::prepare(Item ***rref_pointer_array, tables_list, select_lex->leaf_tables, FALSE, SELECT_ACL, SELECT_ACL, FALSE)) DBUG_RETURN(-1); + + /* + Permanently remove redundant parts from the query if + 1) This is a subquery + 2) This is the first time this query is optimized (since the + transformation is permanent + 3) Not normalizing a view. Removal should take place when a + query involving a view is optimized, not when the view + is created + */ + if (select_lex->master_unit()->item && // 1) + select_lex->first_cond_optimization && // 2) + !(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW)) // 3) + { + remove_redundant_subquery_clauses(select_lex); + } /* TRUE if the SELECT list mixes elements with and without grouping, |