diff options
Diffstat (limited to 'mysql-test/main/subselect4.result')
| -rw-r--r-- | mysql-test/main/subselect4.result | 79 |
1 files changed, 57 insertions, 22 deletions
diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index 6d1e0dac..1e65bfc8 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -3001,34 +3001,31 @@ where a >= any (select b from t2 group by (select c from t3 where c = 1)); 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 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t1`.`a`))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` group by (/* select#3 */ select `test`.`t3`.`c` from `test`.`t3` where `test`.`t3`.`c` = 1)) <= <cache>(`test`.`t1`.`a`))) select a from t1 where a >= any (select b from t2 group by (select c from t3 where c = 1)); a -3 -2 prepare stmt from "select a from t1 where a >= any (select b from t2 group by (select c from t3 where c = 1))"; execute stmt; a -3 -2 execute stmt; a -3 -2 deallocate prepare stmt; explain extended select a from t1 where a <= all (select b from t2 group by (select c from t3 where c = 1)); 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 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) < <cache>(`test`.`t1`.`a`))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` group by (/* select#3 */ select `test`.`t3`.`c` from `test`.`t3` where `test`.`t3`.`c` = 1)) < <cache>(`test`.`t1`.`a`))) select a from t1 where a <= all (select b from t2 group by (select c from t3 where c = 1)); a +3 1 2 explain extended select a from t1 @@ -3036,13 +3033,12 @@ where a >= any (select b from t2 group by 1 + (select c from t3 where c = 1)); 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 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,(/* select#2 */ select min(`test`.`t2`.`b`) from `test`.`t2`) <= <cache>(`test`.`t1`.`a`))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#2 */ select `test`.`t2`.`b` from `test`.`t2` group by 1 + (/* select#3 */ select `test`.`t3`.`c` from `test`.`t3` where `test`.`t3`.`c` = 1)) <= <cache>(`test`.`t1`.`a`))) select a from t1 where a >= any (select b from t2 group by 1 + (select c from t3 where c = 1)); a -3 -2 drop table t1,t2,t3; # # MDEV-29139: Redundant IN/ALL/ANY predicand in GROUP BY clause of @@ -3062,8 +3058,10 @@ group by (select a from t1 where a = 1) in (select d from t4)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +4 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where 1 +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where <in_optimizer>(1,exists(/* select#2 */ select `test`.`t3`.`c` from `test`.`t3` group by <in_optimizer>((/* select#3 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`a` = 1),<exists>(/* select#4 */ select `test`.`t4`.`d` from `test`.`t4` where trigcond(<cache>((/* select#3 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`a` = 1)) = `test`.`t4`.`d` or `test`.`t4`.`d` is null) having trigcond(`test`.`t4`.`d` is null))) limit 1)) select b from t2 where exists (select c from t3 group by (select a from t1 where a = 1) in (select d from t4)); @@ -3089,8 +3087,10 @@ any (select d from t4)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +4 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where 1 +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where <in_optimizer>(1,exists(/* select#2 */ select `test`.`t3`.`c` from `test`.`t3` group by <nop>(<expr_cache><(/* select#3 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`a` = 1)>(<in_optimizer>((/* select#3 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`a` = 1),<exists>(/* select#4 */ select `test`.`t4`.`d` from `test`.`t4` where trigcond(<cache>((/* select#3 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`a` = 1)) >= `test`.`t4`.`d` or `test`.`t4`.`d` is null) having trigcond(`test`.`t4`.`d` is null))))) limit 1)) select b from t2 where exists (select c from t3 group by (select a from t1 where a = 1) >= @@ -3105,8 +3105,10 @@ all (select d from t4)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +4 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where 1 +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where <in_optimizer>(1,exists(/* select#2 */ select `test`.`t3`.`c` from `test`.`t3` group by <not>(<expr_cache><(/* select#3 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`a` = 1)>(<in_optimizer>((/* select#3 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`a` = 1),<exists>(/* select#4 */ select `test`.`t4`.`d` from `test`.`t4` where trigcond(<cache>((/* select#3 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`a` = 1)) >= `test`.`t4`.`d` or `test`.`t4`.`d` is null) having trigcond(`test`.`t4`.`d` is null))))) limit 1)) select b from t2 where exists (select c from t3 group by (select a from t1 where a = 1) < @@ -3118,16 +3120,17 @@ explain extended select b from t2 where b in (select c from t3 group by (select a from t1 where a = 1) in (select d from t4)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t2.b 1 100.00 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 +4 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t2`.`b` AS `b` from `test`.`t2` semi join (`test`.`t3`) where 1 +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from <materialize> (/* select#2 */ select `test`.`t3`.`c` from `test`.`t3` group by <in_optimizer>((/* select#3 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`a` = 1),<exists>(/* select#4 */ select `test`.`t4`.`d` from `test`.`t4` where trigcond(<cache>((/* select#3 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`a` = 1)) = `test`.`t4`.`d` or `test`.`t4`.`d` is null) having trigcond(`test`.`t4`.`d` is null)))) join `test`.`t2` where `<subquery2>`.`c` = `test`.`t2`.`b` select b from t2 where b in (select c from t3 group by (select a from t1 where a = 1) in (select d from t4)); b -2 explain extended select b from t2 where b >= any (select c from t3 group by (select a from t1 where a = 1) in @@ -3135,15 +3138,15 @@ group by (select a from t1 where a = 1) in id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +4 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where <nop>(<in_optimizer>(`test`.`t2`.`b`,(/* select#2 */ select min(`test`.`t3`.`c`) from `test`.`t3`) <= <cache>(`test`.`t2`.`b`))) +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where <nop>(<in_optimizer>(`test`.`t2`.`b`,<min>(/* select#2 */ select `test`.`t3`.`c` from `test`.`t3` group by <in_optimizer>((/* select#3 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`a` = 1),<exists>(/* select#4 */ select `test`.`t4`.`d` from `test`.`t4` where trigcond(<cache>((/* select#3 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`a` = 1)) = `test`.`t4`.`d` or `test`.`t4`.`d` is null) having trigcond(`test`.`t4`.`d` is null)))) <= <cache>(`test`.`t2`.`b`))) select b from t2 where b >= any (select c from t3 group by (select a from t1 where a = 1) in (select d from t4)); b -3 -2 explain extended select b from t2 where b <= all (select c from t3 group by (select a from t1 where a = 1) in @@ -3151,13 +3154,16 @@ group by (select a from t1 where a = 1) in id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +4 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where <not>(<in_optimizer>(`test`.`t2`.`b`,<min>(/* select#2 */ select `test`.`t3`.`c` from `test`.`t3`) < <cache>(`test`.`t2`.`b`))) +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where <not>(<in_optimizer>(`test`.`t2`.`b`,<min>(/* select#2 */ select `test`.`t3`.`c` from `test`.`t3` group by <in_optimizer>((/* select#3 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`a` = 1),<exists>(/* select#4 */ select `test`.`t4`.`d` from `test`.`t4` where trigcond(<cache>((/* select#3 */ select `test`.`t1`.`a` from `test`.`t1` where `test`.`t1`.`a` = 1)) = `test`.`t4`.`d` or `test`.`t4`.`d` is null) having trigcond(`test`.`t4`.`d` is null)))) < <cache>(`test`.`t2`.`b`))) select b from t2 where b <= all (select c from t3 group by (select a from t1 where a = 1) in (select d from t4)); b +3 2 drop table t1,t2,t3,t4; # End of 10.3 tests @@ -3330,4 +3336,33 @@ a 2 DEALLOCATE PREPARE stmt; DROP TABLE t1,t2,t3; +# +# MDEV-33747: Optimization of (SELECT) IN (SELECT ...) executes subquery at prepare stage +# +create table t1 (a int, b int); +insert into t1 select seq, seq from seq_1_to_200; +create table t2 as select * from t1; +create table t3 as select * from t1; +analyze table t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +select @@expensive_subquery_limit < 200 as DEFAULTS_ARE_SUITABLE; +DEFAULTS_ARE_SUITABLE +1 +flush status; +explain select * from t1 where a<3 or (select max(a) from t2) in (select b from t3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 200 Using where +3 SUBQUERY t3 ALL NULL NULL NULL NULL 200 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 200 +# Must show 0. If this shows 200, this means subquery was executed and you have a bug: +show status like 'Handler_read_rnd_next%'; +Variable_name Value +Handler_read_rnd_next 0 +drop table t1,t2,t3; # End of 10.4 tests |