From 2a16e7674b9462586ef883e5678b21c87ca5f045 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Mon, 2 Apr 2012 21:41:54 +0400 Subject: BUG#913030: Optimizer chooses a suboptimal excution plan for Q18 from DBT-3 - When doing join optimization, pre-sort the tables so that they mimic the execution order we've had with 'semijoin=off'. - That way, we will not get regressions when there are two query plans (the old and the new) that have indentical costs but different execution times (because of factors that the optimizer was not able to take into account). --- mysql-test/r/join_cache.result | 12 ++++----- mysql-test/r/subselect.result | 4 +-- mysql-test/r/subselect3.result | 14 +++++------ mysql-test/r/subselect3_jcl6.result | 16 ++++++------ mysql-test/r/subselect_no_mat.result | 6 ++--- mysql-test/r/subselect_no_scache.result | 4 +-- mysql-test/r/subselect_sj.result | 20 +++++++-------- mysql-test/r/subselect_sj2.result | 37 +++++++++++++-------------- mysql-test/r/subselect_sj2_jcl6.result | 40 ++++++++++++++++-------------- mysql-test/r/subselect_sj2_mat.result | 37 +++++++++++++-------------- mysql-test/r/subselect_sj_jcl6.result | 32 ++++++++++++------------ mysql-test/r/subselect_sj_mat.result | 10 ++++---- mysql-test/r/subselect_sj_nonmerged.result | 4 +-- 13 files changed, 120 insertions(+), 116 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index f8db2c8cf72..380e6dd0e46 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5258,8 +5258,8 @@ SET join_cache_level=0; EXPLAIN SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 2 -1 PRIMARY ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY ALL NULL NULL NULL NULL 4 +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 2 DERIVED t1 ALL NULL NULL NULL NULL 2 2 DERIVED t2 ALL NULL NULL NULL NULL 2 @@ -5477,8 +5477,8 @@ EXPLAIN SELECT * FROM t1 WHERE t1.i IN (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 Using where SELECT * FROM t1 WHERE t1.i IN @@ -5491,8 +5491,8 @@ EXPLAIN SELECT * FROM t1 WHERE t1.i IN (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE t1.i IN diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 4eec1729fe3..95d82396bf0 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -5196,8 +5196,8 @@ WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a FROM it2,it3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 -1 PRIMARY ALL distinct_key NULL NULL NULL 24 Using where -1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using join buffer (flat, BNL join) +1 PRIMARY eq_ref distinct_key distinct_key 8 func,func 1 2 MATERIALIZED it2 ALL NULL NULL NULL NULL 4 2 MATERIALIZED it3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) DROP TABLE IF EXISTS ot1, ot4, it2, it3; diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 3f83ac59fed..f4ff52babdc 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1146,9 +1146,9 @@ insert into t4 select a from t3; explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; LooseScan -1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1) -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using where +1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t3) drop table t1, t3, t4; set @@optimizer_switch=@save_optimizer_switch; create table t1 (a int) as select * from t0 where a < 5; @@ -1157,11 +1157,11 @@ set @@optimizer_switch='firstmatch=off,materialization=off'; set @@max_heap_table_size= 16384; explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY E ALL NULL NULL NULL NULL 5 Start temporary -1 PRIMARY A ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +1 PRIMARY A ALL NULL NULL NULL NULL 10 1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) -1 PRIMARY C ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) -1 PRIMARY D ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY E ALL NULL NULL NULL NULL 5 Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY D ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +1 PRIMARY C ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (flat, BNL join) flush status; select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a); count(*) diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index 77b8b5faebc..c0bf5302e64 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -1156,9 +1156,9 @@ insert into t4 select a from t3; explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; LooseScan -1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1) -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using where +1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t3) drop table t1, t3, t4; set @@optimizer_switch=@save_optimizer_switch; create table t1 (a int) as select * from t0 where a < 5; @@ -1167,11 +1167,11 @@ set @@optimizer_switch='firstmatch=off,materialization=off'; set @@max_heap_table_size= 16384; explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY E ALL NULL NULL NULL NULL 5 Using where; Start temporary -1 PRIMARY A ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) -1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer (incremental, BNL join) -1 PRIMARY C ALL NULL NULL NULL NULL 10 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY D hash_ALL NULL #hash#$hj 5 test.E.a 10 Using where; End temporary; Using join buffer (incremental, BNLH join) +1 PRIMARY A ALL NULL NULL NULL NULL 10 +1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) +1 PRIMARY E ALL NULL NULL NULL NULL 5 Using where; Start temporary; Using join buffer (incremental, BNL join) +1 PRIMARY D hash_ALL NULL #hash#$hj 5 test.E.a 10 Using where; Using join buffer (incremental, BNLH join) +1 PRIMARY C ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (incremental, BNL join) flush status; select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a); count(*) diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 0962894bc19..46c7b48a918 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -5198,9 +5198,9 @@ WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a FROM it2,it3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 -1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Start temporary; Using join buffer (flat, BNL join) -1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) -1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using join buffer (flat, BNL join) +1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(ot4) DROP TABLE IF EXISTS ot1, ot4, it2, it3; # # Bug#729039: NULL keys used to evaluate subquery diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index d97b95ad314..cccf910842b 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -5202,8 +5202,8 @@ WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a FROM it2,it3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 -1 PRIMARY ALL distinct_key NULL NULL NULL 24 Using where -1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using join buffer (flat, BNL join) +1 PRIMARY eq_ref distinct_key distinct_key 8 func,func 1 2 MATERIALIZED it2 ALL NULL NULL NULL NULL 4 2 MATERIALIZED it3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join) DROP TABLE IF EXISTS ot1, ot4, it2, it3; diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index d17af2ae092..8a2f1f5eaeb 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -760,16 +760,16 @@ explain extended select a from t1 where a in (select c from t2 where d >= some(select e from t3 where b=e)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Start temporary -1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where; FirstMatch(t1) 3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and (<`test`.`t2`.`d`,`test`.`t1`.`b`>((`test`.`t2`.`d`,(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and ((`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (<`test`.`t2`.`d`,`test`.`t1`.`b`>((`test`.`t2`.`d`,(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and ((`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) show warnings; Level Code Message Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and (<`test`.`t2`.`d`,`test`.`t1`.`b`>((`test`.`t2`.`d`,(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and ((`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (<`test`.`t2`.`d`,`test`.`t1`.`b`>((`test`.`t2`.`d`,(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and ((`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) select a from t1 where a in (select c from t2 where d >= some(select e from t3 where b=e)); a @@ -2158,10 +2158,10 @@ INSERT INTO t5 VALUES (7,0),(9,0); explain SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t5 index a a 10 NULL 2 Using where; Using index; LooseScan +1 PRIMARY t5 index a a 10 NULL 2 Using index; Start temporary 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 -1 PRIMARY t2 ref b b 5 test.t5.b 2 Using where; FirstMatch(t5) -1 PRIMARY t3 ALL NULL NULL NULL NULL 15 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL b NULL NULL NULL 10 Using where +1 PRIMARY t3 ALL NULL NULL NULL NULL 15 Using where; End temporary; Using join buffer (flat, BNL join) SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b); a 0 @@ -2240,11 +2240,11 @@ alias1.c IN (SELECT SQ3_alias1.b FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2) LIMIT 100; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL NULL NULL NULL NULL 20 +1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Start temporary +1 PRIMARY ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) +1 PRIMARY SQ3_alias2 index NULL PRIMARY 4 NULL 20 Using index; End temporary 1 PRIMARY alias2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) 1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) -1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Using where; Start temporary -1 PRIMARY SQ3_alias2 index NULL PRIMARY 4 NULL 20 Using index; End temporary 2 DERIVED t2 ALL NULL NULL NULL NULL 20 create table t3 as SELECT diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index 8a9946c404e..b54d4e8db56 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -49,9 +49,9 @@ a b 19 14 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t2 ref b b 5 test.t1.a 2 -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t2 ALL b NULL NULL NULL 20 +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 select * from t2 where b in (select a from t1); a b 1 1 @@ -69,9 +69,9 @@ insert into t3 select a,a, a,a,a from t0; insert into t3 select a,a, a+100,a+100,a+100 from t0; explain select * from t3 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t3 ref b b 5 test.t1.a 1 -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t3 ALL b NULL NULL NULL 20 +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 select * from t3 where b in (select a from t1); a b pk1 pk2 pk3 1 1 1 1 1 @@ -95,15 +95,19 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 10 -1 PRIMARY t3 ref b b 5 test.t0.a 1 -2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY t3 ALL b NULL NULL NULL 56 +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 set @save_ecp= @@engine_condition_pushdown; set engine_condition_pushdown=0; select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); a b pk1 pk2 0 0 0 0 1 1 1 1 +10 10 10 10 +11 11 11 11 +12 12 12 12 +13 13 13 13 2 2 2 2 3 3 3 3 4 4 4 4 @@ -112,10 +116,6 @@ a b pk1 pk2 7 7 7 7 8 8 8 8 9 9 9 9 -10 10 10 10 -11 11 11 11 -12 12 12 12 -13 13 13 13 set engine_condition_pushdown=@save_ecp; set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; @@ -748,8 +748,8 @@ c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t2) 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) drop table t2, t3; # @@ -931,9 +931,10 @@ SELECT d FROM t2, t1 WHERE a = d AND ( pk < 2 OR d = 'z' ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index PRIMARY,d d 9 NULL 17 Using where; Using index; LooseScan -1 PRIMARY t1 ref a a 5 test.t2.d 1 Using where; Using index; FirstMatch(t2) +1 PRIMARY ALL distinct_key NULL NULL NULL 2 1 PRIMARY t1 ref b b 4 test.t2.d 1 +2 MATERIALIZED t2 index_merge PRIMARY,d d,PRIMARY 4,4 NULL 2 Using sort_union(d,PRIMARY); Using where +2 MATERIALIZED t1 ref a a 5 test.t2.d 1 Using where; Using index SELECT * FROM t1 WHERE b IN ( SELECT d FROM t2, t1 WHERE a = d AND ( pk < 2 OR d = 'z' ) @@ -993,8 +994,8 @@ WHERE alias2.b = alias1.a AND (alias1.b >= alias1.a OR alias2.b = 'z')); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 19 -1 PRIMARY t2 ref a a 4 test.alias1.a 1 +1 PRIMARY t2 ALL a NULL NULL NULL 38 +1 PRIMARY eq_ref distinct_key distinct_key 8 func,func 1 2 MATERIALIZED alias1 ALL a NULL NULL NULL 19 Using where 2 MATERIALIZED alias2 ref a a 4 test.alias1.a 1 Using where SELECT * FROM t2 diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index c2cfbb44d86..172a4a40f4c 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -60,9 +60,9 @@ a b 19 14 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t2 ref b b 5 test.t1.a 2 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t2 ALL b NULL NULL NULL 20 +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 select * from t2 where b in (select a from t1); a b 1 1 @@ -80,9 +80,9 @@ insert into t3 select a,a, a,a,a from t0; insert into t3 select a,a, a+100,a+100,a+100 from t0; explain select * from t3 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t3 ref b b 5 test.t1.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t3 ALL b NULL NULL NULL 20 +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 select * from t3 where b in (select a from t1); a b pk1 pk2 pk3 1 1 1 1 1 @@ -106,15 +106,19 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 10 -1 PRIMARY t3 ref b b 5 test.t0.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY t3 ALL b NULL NULL NULL 56 +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 set @save_ecp= @@engine_condition_pushdown; set engine_condition_pushdown=0; select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); a b pk1 pk2 0 0 0 0 1 1 1 1 +10 10 10 10 +11 11 11 11 +12 12 12 12 +13 13 13 13 2 2 2 2 3 3 3 3 4 4 4 4 @@ -123,10 +127,6 @@ a b pk1 pk2 7 7 7 7 8 8 8 8 9 9 9 9 -10 10 10 10 -11 11 11 11 -12 12 12 12 -13 13 13 13 set engine_condition_pushdown=@save_ecp; set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; @@ -762,9 +762,10 @@ c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2); Using join buffer (incremental, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch((sj-nest)); Using join buffer (incremental, BNL join) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 drop table t2, t3; # # BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3 @@ -945,9 +946,10 @@ SELECT d FROM t2, t1 WHERE a = d AND ( pk < 2 OR d = 'z' ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index PRIMARY,d d 9 NULL 17 Using where; Using index; LooseScan -1 PRIMARY t1 ref a a 5 test.t2.d 1 Using where; Using index; FirstMatch(t2) +1 PRIMARY ALL distinct_key NULL NULL NULL 2 1 PRIMARY t1 ref b b 4 test.t2.d 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +2 MATERIALIZED t2 index_merge PRIMARY,d d,PRIMARY 4,4 NULL 2 Using sort_union(d,PRIMARY); Using where +2 MATERIALIZED t1 ref a a 5 test.t2.d 1 Using where; Using index SELECT * FROM t1 WHERE b IN ( SELECT d FROM t2, t1 WHERE a = d AND ( pk < 2 OR d = 'z' ) @@ -1007,8 +1009,8 @@ WHERE alias2.b = alias1.a AND (alias1.b >= alias1.a OR alias2.b = 'z')); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 19 -1 PRIMARY t2 ref a a 4 test.alias1.a 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY t2 ALL a NULL NULL NULL 38 +1 PRIMARY eq_ref distinct_key distinct_key 8 func,func 1 2 MATERIALIZED alias1 ALL a NULL NULL NULL 19 Using where 2 MATERIALIZED alias2 ref a a 4 test.alias1.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT * FROM t2 diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 378bf1d8844..8acdbab9a12 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -51,9 +51,9 @@ a b 19 14 explain select * from t2 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t2 ref b b 5 test.t1.a 2 -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t2 ALL b NULL NULL NULL 20 +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 select * from t2 where b in (select a from t1); a b 1 1 @@ -71,9 +71,9 @@ insert into t3 select a,a, a,a,a from t0; insert into t3 select a,a, a+100,a+100,a+100 from t0; explain select * from t3 where b in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t3 ref b b 5 test.t1.a 1 -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t3 ALL b NULL NULL NULL 20 +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 select * from t3 where b in (select a from t1); a b pk1 pk2 pk3 1 1 1 1 1 @@ -97,15 +97,19 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 10 -1 PRIMARY t3 ref b b 5 test.t0.a 1 -2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY t3 ALL b NULL NULL NULL 46 +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 set @save_ecp= @@engine_condition_pushdown; set engine_condition_pushdown=0; select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); a b pk1 pk2 0 0 0 0 1 1 1 1 +10 10 10 10 +11 11 11 11 +12 12 12 12 +13 13 13 13 2 2 2 2 3 3 3 3 4 4 4 4 @@ -114,10 +118,6 @@ a b pk1 pk2 7 7 7 7 8 8 8 8 9 9 9 9 -10 10 10 10 -11 11 11 11 -12 12 12 12 -13 13 13 13 set engine_condition_pushdown=@save_ecp; set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; @@ -750,8 +750,8 @@ c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t2) 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) drop table t2, t3; # @@ -933,9 +933,10 @@ SELECT d FROM t2, t1 WHERE a = d AND ( pk < 2 OR d = 'z' ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index PRIMARY,d d 9 NULL 17 Using where; Using index; LooseScan -1 PRIMARY t1 ref a a 5 test.t2.d 1 Using where; Using index; FirstMatch(t2) +1 PRIMARY ALL distinct_key NULL NULL NULL 2 1 PRIMARY t1 ref b b 4 test.t2.d 1 +2 MATERIALIZED t2 index_merge PRIMARY,d d,PRIMARY 4,4 NULL 2 Using sort_union(d,PRIMARY); Using where +2 MATERIALIZED t1 ref a a 5 test.t2.d 1 Using where; Using index SELECT * FROM t1 WHERE b IN ( SELECT d FROM t2, t1 WHERE a = d AND ( pk < 2 OR d = 'z' ) @@ -995,8 +996,8 @@ WHERE alias2.b = alias1.a AND (alias1.b >= alias1.a OR alias2.b = 'z')); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 19 -1 PRIMARY t2 ref a a 4 test.alias1.a 1 +1 PRIMARY t2 ALL a NULL NULL NULL 38 +1 PRIMARY eq_ref distinct_key distinct_key 8 func,func 1 2 MATERIALIZED alias1 ALL a NULL NULL NULL 19 Using where 2 MATERIALIZED alias2 ref a a 4 test.alias1.a 1 Using where SELECT * FROM t2 diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 0ac1c51ee3d..704cde2afd2 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -773,16 +773,16 @@ explain extended select a from t1 where a in (select c from t2 where d >= some(select e from t3 where b=e)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Start temporary -1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and (<`test`.`t2`.`d`,`test`.`t1`.`b`>((`test`.`t2`.`d`,(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and ((`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (<`test`.`t2`.`d`,`test`.`t1`.`b`>((`test`.`t2`.`d`,(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and ((`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) show warnings; Level Code Message Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and (<`test`.`t2`.`d`,`test`.`t1`.`b`>((`test`.`t2`.`d`,(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and ((`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (<`test`.`t2`.`d`,`test`.`t1`.`b`>((`test`.`t2`.`d`,(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and ((`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))) select a from t1 where a in (select c from t2 where d >= some(select e from t3 where b=e)); a @@ -2172,10 +2172,10 @@ INSERT INTO t5 VALUES (7,0),(9,0); explain SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t5 index a a 10 NULL 2 Using where; Using index; LooseScan -1 PRIMARY t4 ALL NULL NULL NULL NULL 3 -1 PRIMARY t2 ref b b 5 test.t5.b 2 Using where; FirstMatch(t5) -1 PRIMARY t3 ALL NULL NULL NULL NULL 15 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t5 index a a 10 NULL 2 Using index; Start temporary +1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL b NULL NULL NULL 10 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 15 Using where; End temporary; Using join buffer (incremental, BNL join) SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b); a 0 @@ -2254,11 +2254,11 @@ alias1.c IN (SELECT SQ3_alias1.b FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2) LIMIT 100; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL NULL NULL NULL NULL 20 -1 PRIMARY alias2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) -1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (incremental, BNL join) -1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Using where; Start temporary; Using join buffer (incremental, BNL join) +1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Start temporary +1 PRIMARY ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) 1 PRIMARY SQ3_alias2 index NULL PRIMARY 4 NULL 20 Using index; End temporary; Using join buffer (incremental, BNL join) +1 PRIMARY alias2 ALL NULL NULL NULL NULL 20 Using join buffer (incremental, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (incremental, BNL join) 2 DERIVED t2 ALL NULL NULL NULL NULL 20 create table t3 as SELECT @@ -2882,8 +2882,8 @@ EXPLAIN SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) GROUP BY a HAVING a != 'z'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 2 Using temporary; Using filesort -1 PRIMARY t ref idx_a idx_a 4 test.t2.b 2 Using index +1 PRIMARY t index idx_a idx_a 4 NULL 3 Using index; Using temporary; Using filesort +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) @@ -2896,8 +2896,8 @@ EXPLAIN SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) GROUP BY a HAVING a != 'z'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 2 Using temporary; Using filesort -1 PRIMARY t ref idx_a idx_a 4 test.t2.b 2 Using index +1 PRIMARY t index idx_a idx_a 4 NULL 3 Using index; Using temporary; Using filesort +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index e60851775c0..1f96932309d 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -505,15 +505,15 @@ b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2)); 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 t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t2i ref it2i1,it2i2,it2i3 it2i3 18 test.t1.a1,test.t1.a2 2 100.00 Using index; Start temporary 1 PRIMARY t3c ALL NULL NULL NULL NULL 4 100.00 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) 4 MATERIALIZED t3b ALL NULL NULL NULL NULL 4 100.00 Using where 3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1 Note 1276 Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1 -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` `t3c`) where ((`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3c`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3c`.`c1` = `test`.`t1`.`a1`) and (<`test`.`t2`.`b2`,`test`.`t1`.`a1`>((`test`.`t2`.`b2`,(select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and ((`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))))) or <`test`.`t2`.`b2`>((`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), (`test`.`t2`.`b2` in on distinct_key where ((`test`.`t2`.`b2` = ``.`c2`)))))))) +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` `t3c`) where ((`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3c`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3c`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (<`test`.`t2`.`b2`,`test`.`t1`.`a1`>((`test`.`t2`.`b2`,(select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and ((`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))))) or <`test`.`t2`.`b2`>((`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), (`test`.`t2`.`b2` in on distinct_key where ((`test`.`t2`.`b2` = ``.`c2`)))))))) explain extended select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01'); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -1087,11 +1087,11 @@ create index it1a on t1(a); explain extended select a from t1 where a in (select c from t2 where d >= 20); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 6 100.00 -1 PRIMARY t1 ref it1a it1a 4 test.t2.c 2 100.00 Using index +1 PRIMARY t1 index it1a it1a 4 NULL 7 100.00 Using index +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`d` >= 20)) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` >= 20)) select a from t1 where a in (select c from t2 where d >= 20); a 2 diff --git a/mysql-test/r/subselect_sj_nonmerged.result b/mysql-test/r/subselect_sj_nonmerged.result index 2a3768c8c50..c7e04225ffe 100644 --- a/mysql-test/r/subselect_sj_nonmerged.result +++ b/mysql-test/r/subselect_sj_nonmerged.result @@ -77,8 +77,8 @@ explain select * from t4 where t4.a in (select max(t2.a) from t1, t2 group by t2.b) and t4.b in (select max(t2.a) from t1, t2 group by t2.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL distinct_key NULL NULL NULL 5 -1 PRIMARY ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join) +1 PRIMARY ALL distinct_key NULL NULL NULL 5 +1 PRIMARY ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join) 1 PRIMARY t4 ref a a 10 .max(t2.a),.max(t2.a) 12 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 3 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) -- cgit v1.2.1 From a3bee835eea847b6d5b68897aee63ab9a1a5d6ba Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Wed, 4 Apr 2012 00:14:07 +0300 Subject: Fixed lp:970528 "Server crashes in my_strnncollsp_simple on LEFT JOIN with CSV table, TEXT field" The main problem was a bug in CSV where it provided wrong statistics (it claimed the table was empty when it wasn't) I also fixed wrong freeing of blob's in the CSV handler. (Any call to handler::read_first_row() on a CSV table with blobs would fail) mysql-test/r/csv.result: Added new test case mysql-test/r/partition_innodb.result: Updated test results after fixing bug with impossible partitions and const tables mysql-test/t/csv.test: Added new test case sql/sql_select.cc: Cleaned up code for handling of partitions. Fixed also a bug where we didn't threat a table with impossible partitions as a const table. storage/csv/ha_tina.cc: Allocate blobroot onces. --- mysql-test/r/csv.result | 13 +++++++++++++ mysql-test/r/partition_innodb.result | 14 +++++++------- mysql-test/t/csv.test | 15 +++++++++++++++ 3 files changed, 35 insertions(+), 7 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/csv.result b/mysql-test/r/csv.result index 4c3529fb227..76a8bc06f6d 100644 --- a/mysql-test/r/csv.result +++ b/mysql-test/r/csv.result @@ -5415,4 +5415,17 @@ foo DROP TABLE t1; +CREATE TABLE t1 ( b TEXT NOT NULL ) ENGINE=MYISAM; +INSERT INTO t1 VALUES ('x'),('y'); +CREATE TABLE t2 ( a VARCHAR(1) NOT NULL ) ENGINE=CSV; +INSERT INTO t2 VALUES ('r'),('t'); +SELECT * FROM t2 ORDER BY a; +a +r +t +SELECT * FROM t1 LEFT JOIN t2 ON ( b = a ); +b a +x NULL +y NULL +drop table t1,t2; End of 5.1 tests diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index 5fcb0e796b1..1afffd3a037 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -48,13 +48,13 @@ insert INTO t1 VALUES (110); ERROR HY000: Table has no partition for value 110 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 90; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 90; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 90; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 89; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 3 Using where @@ -63,16 +63,16 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p90 ALL NULL NULL NULL NULL 3 Using where EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 89; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 100; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 100; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 100; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1; # # Bug#50104: Partitioned table with just 1 partion works with fk diff --git a/mysql-test/t/csv.test b/mysql-test/t/csv.test index fd08f7c5a40..974b6a65c39 100644 --- a/mysql-test/t/csv.test +++ b/mysql-test/t/csv.test @@ -1820,5 +1820,20 @@ INSERT INTO t1 VALUES(-1); SELECT * FROM t1; DROP TABLE t1; +# +# Bug#970528 +# Server crashes in my_strnncollsp_simple on LEFT JOIN with CSV table, +# TEXT field +# + +CREATE TABLE t1 ( b TEXT NOT NULL ) ENGINE=MYISAM; +INSERT INTO t1 VALUES ('x'),('y'); + +CREATE TABLE t2 ( a VARCHAR(1) NOT NULL ) ENGINE=CSV; +INSERT INTO t2 VALUES ('r'),('t'); + +SELECT * FROM t2 ORDER BY a; +SELECT * FROM t1 LEFT JOIN t2 ON ( b = a ); +drop table t1,t2; --echo End of 5.1 tests -- cgit v1.2.1 From 6a0c2e8b2158cc3a84fd21079d7474f8d14a7972 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Wed, 4 Apr 2012 13:20:06 +0300 Subject: Fixed test cases that changed as part of fixing bugs with record count and partitioning --- mysql-test/suite/pbxt/r/join_nested.result | 2 +- mysql-test/suite/pbxt/r/partition_pruning.result | 38 ++++++++++++------------ mysql-test/suite/pbxt/r/partition_range.result | 4 +-- mysql-test/suite/pbxt/r/select.result | 6 ++-- mysql-test/suite/pbxt/r/select_found.result | 20 ++++++------- 5 files changed, 35 insertions(+), 35 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/pbxt/r/join_nested.result b/mysql-test/suite/pbxt/r/join_nested.result index 5495ba2a55d..99a260e0e6a 100644 --- a/mysql-test/suite/pbxt/r/join_nested.result +++ b/mysql-test/suite/pbxt/r/join_nested.result @@ -1316,8 +1316,8 @@ c11 c21 5 NULL EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON c11=c21; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 0 SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21; c11 c21 c31 4 NULL NULL diff --git a/mysql-test/suite/pbxt/r/partition_pruning.result b/mysql-test/suite/pbxt/r/partition_pruning.result index 7f96e6d06d9..64f2283fa3a 100644 --- a/mysql-test/suite/pbxt/r/partition_pruning.result +++ b/mysql-test/suite/pbxt/r/partition_pruning.result @@ -48,10 +48,10 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t3 p1 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t3 where a=20; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t3 where a=30; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables create table t4 (a int not null, b int not null) partition by LIST (a+b) ( partition p0 values in (12), partition p1 values in (14) @@ -118,13 +118,13 @@ partition p9 values in (9) insert into t6 values (1),(3),(5); explain partitions select * from t6 where a < 1; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t6 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t6 where a <= 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p1 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t6 where a > 9; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t6 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t6 where a >= 9; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p9 ALL NULL NULL NULL NULL 0 Using where @@ -148,7 +148,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p3,p5,p7 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t6 where a > 3 and a < 5; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t6 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables drop table t6; create table t6 (a int unsigned not null) partition by LIST(a) ( partition p1 values in (1), @@ -160,13 +160,13 @@ partition p9 values in (9) insert into t6 values (1),(3),(5); explain partitions select * from t6 where a < 1; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t6 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t6 where a <= 1; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p1 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t6 where a > 9; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t6 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t6 where a >= 9; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p9 ALL NULL NULL NULL NULL 0 Using where @@ -190,7 +190,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t6 p3,p5,p7 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t6 where a > 3 and a < 5; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t6 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables create table t7 (a int not null) partition by RANGE(a) ( partition p10 values less than (10), partition p30 values less than (30), @@ -216,13 +216,13 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a = 90; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t7 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a > 90; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t7 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a >= 90; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t7 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a > 11 and a < 29; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p30 ALL NULL NULL NULL NULL 2 Using where @@ -252,13 +252,13 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p10,p30,p50,p70,p90 ALL NULL NULL NULL NULL 3 Using where explain partitions select * from t7 where a = 90; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t7 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a > 90; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t7 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a >= 90; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t7 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t7 where a > 11 and a < 29; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t7 p30 ALL NULL NULL NULL NULL 2 Using where @@ -301,7 +301,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p2 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t1 where a1 < 3 and a1 > 3; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables drop table t1; create table t3 (a int, b int) partition by list(a) subpartition by hash(b) subpartitions 4 ( @@ -829,7 +829,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t1 where a = 18446744073709551614; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables drop table t1; create table t1 (a int) partition by range(a) ( @@ -859,10 +859,10 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t2 p2 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t1 where a > 0xFE AND a <= 0xFF; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t2 where a > 0xFE AND a <= 0xFF; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t1 where a >= 0xFE AND a <= 0xFF; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p2 ALL NULL NULL NULL NULL 2 Using where @@ -898,7 +898,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra explain partitions select * from t1 where a > 0xFFFFFFFFFFFFFFEC and a < 0xFFFFFFFFFFFFFFEE; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain partitions select * from t1 where a>=0 and a <= 0xFFFFFFFFFFFFFFFF; id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p1,p2,p3,p4 ALL NULL NULL NULL NULL 8 Using where diff --git a/mysql-test/suite/pbxt/r/partition_range.result b/mysql-test/suite/pbxt/r/partition_range.result index a8c4e36cbc2..013bc87d779 100644 --- a/mysql-test/suite/pbxt/r/partition_range.result +++ b/mysql-test/suite/pbxt/r/partition_range.result @@ -33,7 +33,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pnull,p0 ALL NULL NULL NULL NULL 2 Using where explain partitions select * from t1 where a > 1; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables drop table t1; create table t1 (a int unsigned, b int unsigned) partition by range (a) @@ -75,7 +75,7 @@ id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1,p0_p0sp0,p0_p0sp1 ALL NULL NULL NULL NULL 4 Using where explain partitions select * from t1 where a > 1; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables drop table t1; CREATE TABLE t1 ( a int not null, diff --git a/mysql-test/suite/pbxt/r/select.result b/mysql-test/suite/pbxt/r/select.result index c06dd06ea3e..7ba4e3e5ed8 100644 --- a/mysql-test/suite/pbxt/r/select.result +++ b/mysql-test/suite/pbxt/r/select.result @@ -2333,10 +2333,10 @@ insert into t4 values (1,1); explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 left join t4 on id3 = id4 where id2 = 1 or id4 = 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 system NULL NULL NULL NULL 0 const row not found -1 SIMPLE t4 const id4 NULL NULL NULL 1 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 1 +1 SIMPLE t3 ALL NULL NULL NULL NULL 0 +1 SIMPLE t4 ref id4 id4 4 test.t3.id3 1 Using where select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 left join t4 on id3 = id4 where id2 = 1 or id4 = 1; id1 id2 id3 id4 id44 diff --git a/mysql-test/suite/pbxt/r/select_found.result b/mysql-test/suite/pbxt/r/select_found.result index 7896f8a9f4e..f707f703098 100644 --- a/mysql-test/suite/pbxt/r/select_found.result +++ b/mysql-test/suite/pbxt/r/select_found.result @@ -83,20 +83,20 @@ UNIQUE KEY e_n (email,name) ); EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY,kid NULL NULL NULL 0 const row not found -1 SIMPLE t2 index NULL e_n 104 NULL 10 +1 SIMPLE t2 ALL NULL NULL NULL NULL 200 Using temporary +1 SIMPLE t1 ref kid kid 2 test.t2.id 1 Using where; Not exists; Distinct SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; email email1 +email2 +email3 +email4 +email5 +email6 +email7 +email8 +email9 email10 -email100 -email101 -email102 -email103 -email104 -email105 -email106 -email107 SELECT FOUND_ROWS(); FOUND_ROWS() 200 -- cgit v1.2.1 From cc2298ebb7e1225d6a293281bc949d746040d547 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 4 Apr 2012 21:35:34 +0400 Subject: Make test results stable. --- mysql-test/r/subselect_sj2.result | 6 +++--- mysql-test/r/subselect_sj2_jcl6.result | 6 +++--- mysql-test/r/subselect_sj2_mat.result | 6 +++--- mysql-test/t/subselect_sj2.test | 1 + 4 files changed, 10 insertions(+), 9 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index b54d4e8db56..be3e05c7a50 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -95,9 +95,9 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL b NULL NULL NULL 56 -1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 +1 PRIMARY t3 ALL b NULL NULL NULL # +1 PRIMARY eq_ref distinct_key distinct_key 4 func # +2 MATERIALIZED t0 ALL NULL NULL NULL NULL # set @save_ecp= @@engine_condition_pushdown; set engine_condition_pushdown=0; select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 172a4a40f4c..44cdb69e132 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -106,9 +106,9 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL b NULL NULL NULL 56 -1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 +1 PRIMARY t3 ALL b NULL NULL NULL # +1 PRIMARY eq_ref distinct_key distinct_key 4 func # +2 MATERIALIZED t0 ALL NULL NULL NULL NULL # set @save_ecp= @@engine_condition_pushdown; set engine_condition_pushdown=0; select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 8acdbab9a12..4bce431a771 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -97,9 +97,9 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; explain select * from t3 where b in (select a from t0); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 ALL b NULL NULL NULL 46 -1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t0 ALL NULL NULL NULL NULL 10 +1 PRIMARY t3 ALL b NULL NULL NULL # +1 PRIMARY eq_ref distinct_key distinct_key 4 func # +2 MATERIALIZED t0 ALL NULL NULL NULL NULL # set @save_ecp= @@engine_condition_pushdown; set engine_condition_pushdown=0; select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test index b9a1b91771e..1afc39264ea 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -78,6 +78,7 @@ insert into t3 select A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B where B.a <5; +--replace_column 9 # explain select * from t3 where b in (select a from t0); # Because of BUG#40154, run the next select w/o index condition pushdown: set @save_ecp= @@engine_condition_pushdown; -- cgit v1.2.1 From 1a48919036a7746a15ff6bd19cb52c25fc6a57c1 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 5 Apr 2012 23:32:57 +0300 Subject: Fix of LP bug#968720. When a view/derived table is converted from merged to materialized the items from the used_item lists are substituted for items referring to the fields of the result of the materialization. The problem appeared with queries employing natural joins. Since the resolution of a natural join was performed only once the used_item list formed at the second execution of the query lacked the references to the fields that were used only in the equality predicates generated for the natural join. --- mysql-test/r/derived_view.result | 39 +++++++++++++++++++++++++++++++++++++++ mysql-test/t/derived_view.test | 37 +++++++++++++++++++++++++++++++++++++ 2 files changed, 76 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 28c4c362a9d..3151bb14657 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1997,5 +1997,44 @@ a b gc SET SESSION optimizer_switch= @save_optimizer_switch; DROP VIEW v; DROP TABLE t1,t2; +# +# LP BUG#968720 crash due to converting to materialized and +# natural join made only once +# +SET @save968720_optimizer_switch=@@optimizer_switch; +SET optimizer_switch = 'derived_merge=on'; +CREATE TABLE t1 (a int, INDEX(a)); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (a int, INDEX(a)); +INSERT INTO t2 VALUES (1), (2); +INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN +t2 AS s2; +SELECT * FROM t1; +a +1 +1 +DELETE FROM t1; +INSERT INTO t1 VALUES (1); +PREPARE stmt FROM " +INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN +t2 AS s2; +"; +EXECUTE stmt; +SELECT * FROM t1; +a +1 +1 +EXECUTE stmt; +SELECT * FROM t1; +a +1 +1 +1 +1 +drop table t1,t2; +set optimizer_switch=@save968720_optimizer_switch; +# +# end of 5.3 tests +# set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index d1ed2ff5ba6..03d308b6c45 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1380,6 +1380,43 @@ SET SESSION optimizer_switch= @save_optimizer_switch; DROP VIEW v; DROP TABLE t1,t2; +--echo # +--echo # LP BUG#968720 crash due to converting to materialized and +--echo # natural join made only once +--echo # + +SET @save968720_optimizer_switch=@@optimizer_switch; +SET optimizer_switch = 'derived_merge=on'; + +CREATE TABLE t1 (a int, INDEX(a)); +INSERT INTO t1 VALUES (1); + +CREATE TABLE t2 (a int, INDEX(a)); +INSERT INTO t2 VALUES (1), (2); + +INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN +t2 AS s2; +SELECT * FROM t1; + +DELETE FROM t1; +INSERT INTO t1 VALUES (1); + +PREPARE stmt FROM " +INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN +t2 AS s2; +"; +EXECUTE stmt; +SELECT * FROM t1; +EXECUTE stmt; +SELECT * FROM t1; + +drop table t1,t2; +set optimizer_switch=@save968720_optimizer_switch; + +--echo # +--echo # end of 5.3 tests +--echo # + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; -- cgit v1.2.1 From 4ca9b8eb3af0fbfabd493bf72a0cc0e57f51d935 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 6 Apr 2012 15:08:09 -0700 Subject: Fixed bug #915222. This bug happened because the function find_field_in_view formed autogenerated names of view columns without a possibility to roll them back. In some situation it could cause memory misuses reported by valgrind or even crashes. --- mysql-test/r/view.result | 20 ++++++++++++++++++++ mysql-test/t/view.test | 29 +++++++++++++++++++++++++++++ 2 files changed, 49 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 15ef0c088b1..b4e6f194058 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4433,6 +4433,26 @@ NULL NULL 1 0 NULL NULL 1 0 DROP VIEW v2; DROP TABLE t1, t2, t3; +# +# BUG#915222: Valgrind complains or crashes with INSERT SELECT +# within a trigger that uses a view +# +CREATE TABLE t1 (a char(1)); +CREATE TABLE t2 (d int, e char(1)); +INSERT INTO t2 VALUES (13,'z'); +CREATE TRIGGER tr AFTER UPDATE ON t2 +FOR EACH ROW +REPLACE INTO t3 +SELECT f, a AS alias FROM t3, v; +CREATE TABLE t3 (f int, g char(8)); +CREATE VIEW v AS SELECT a, e FROM t2, t1; +UPDATE t2 SET d=7; +UPDATE t2 SET d=7; +UPDATE t2 SET d=7; +UPDATE t2 SET d=7; +DROP TRIGGER tr; +DROP VIEW v; +DROP TABLE t1,t2,t3; # ----------------------------------------------------------------- # -- End of 5.3 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 2a9bfd89f3b..4820e0ac173 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -4365,6 +4365,35 @@ SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM DROP VIEW v2; DROP TABLE t1, t2, t3; +--echo # +--echo # BUG#915222: Valgrind complains or crashes with INSERT SELECT +--echo # within a trigger that uses a view +--echo # + +CREATE TABLE t1 (a char(1)); + +CREATE TABLE t2 (d int, e char(1)); + +INSERT INTO t2 VALUES (13,'z'); + +CREATE TRIGGER tr AFTER UPDATE ON t2 + FOR EACH ROW + REPLACE INTO t3 + SELECT f, a AS alias FROM t3, v; + +CREATE TABLE t3 (f int, g char(8)); + +CREATE VIEW v AS SELECT a, e FROM t2, t1; + +UPDATE t2 SET d=7; +UPDATE t2 SET d=7; +UPDATE t2 SET d=7; +UPDATE t2 SET d=7; + +DROP TRIGGER tr; +DROP VIEW v; +DROP TABLE t1,t2,t3; + --echo # ----------------------------------------------------------------- --echo # -- End of 5.3 tests. --echo # ----------------------------------------------------------------- -- cgit v1.2.1 From b95ae56b9f47cc19d3498d4be3142b2449a04600 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 7 Apr 2012 02:29:04 -0700 Subject: Fixed LP bug #972973. When the function free_tmp_table deletes the handler object for a temporary table the field TABLE::file for this table should be set to NULL. Otherwise an assertion failure may occur. --- mysql-test/r/view.result | 19 +++++++++++++++++++ mysql-test/t/view.test | 26 ++++++++++++++++++++++++++ 2 files changed, 45 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index b4e6f194058..a1b36c323c7 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4453,6 +4453,25 @@ UPDATE t2 SET d=7; DROP TRIGGER tr; DROP VIEW v; DROP TABLE t1,t2,t3; +# +# BUG#972943: Assertion failure with INSERT SELECT within a trigger +# that uses derived table and materialized view +# +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (1,0), (2,8); +CREATE ALGORITHM=TEMPTABLE VIEW v1 +AS SELECT * FROM t1; +CREATE TABLE t2 (c int); +CREATE TABLE t3 (d int, e int); +CREATE TRIGGER tr BEFORE INSERT ON t2 FOR EACH ROW +INSERT INTO t3 +SELECT t1.* +FROM (SELECT * FROM t1 WHERE b IN (SELECT b FROM v1)) AS alias1, t1 +WHERE t1.a = 3 OR t1.a > 5; +INSERT INTO t2 VALUES (1); +DROP TRIGGER tr; +DROP VIEW v1; +DROP TABLE t1,t2,t3; # ----------------------------------------------------------------- # -- End of 5.3 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 4820e0ac173..93e0cce8a5d 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -4394,6 +4394,32 @@ DROP TRIGGER tr; DROP VIEW v; DROP TABLE t1,t2,t3; +--echo # +--echo # BUG#972943: Assertion failure with INSERT SELECT within a trigger +--echo # that uses derived table and materialized view +--echo # + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (1,0), (2,8); + +CREATE ALGORITHM=TEMPTABLE VIEW v1 + AS SELECT * FROM t1; + +CREATE TABLE t2 (c int); +CREATE TABLE t3 (d int, e int); + +CREATE TRIGGER tr BEFORE INSERT ON t2 FOR EACH ROW + INSERT INTO t3 + SELECT t1.* + FROM (SELECT * FROM t1 WHERE b IN (SELECT b FROM v1)) AS alias1, t1 + WHERE t1.a = 3 OR t1.a > 5; + +INSERT INTO t2 VALUES (1); + +DROP TRIGGER tr; +DROP VIEW v1; +DROP TABLE t1,t2,t3; + --echo # ----------------------------------------------------------------- --echo # -- End of 5.3 tests. --echo # ----------------------------------------------------------------- -- cgit v1.2.1