diff options
Diffstat (limited to 'mysql-test/main/subselect_sj2_mat.result')
| -rw-r--r-- | mysql-test/main/subselect_sj2_mat.result | 166 |
1 files changed, 85 insertions, 81 deletions
diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result index df67a0be..d022820e 100644 --- a/mysql-test/main/subselect_sj2_mat.result +++ b/mysql-test/main/subselect_sj2_mat.result @@ -27,11 +27,7 @@ key(b) ); insert into t2 select a, a/2 from t0; insert into t2 select a+10, a+10/2 from t0; -select * from t1; -a b -1 1 -1 1 -2 2 +insert into t1 values (1030,30),(1031,31),(1032,32),(1033,33); select * from t2; a b 0 0 @@ -58,7 +54,7 @@ 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 t2 ALL b NULL NULL NULL 20 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 7 select * from t2 where b in (select a from t1); a b 1 1 @@ -86,7 +82,7 @@ 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 t3 ALL b NULL NULL NULL 20 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 7 select * from t3 where b in (select a from t1); a b pk1 pk2 pk3 1 1 1 1 1 @@ -108,11 +104,14 @@ primary key(pk1, pk2) 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; +analyze table t3 persistent for all; +Table Op Msg_type Msg_text +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK 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 # -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func # -2 MATERIALIZED t0 ALL NULL NULL NULL NULL # +1 PRIMARY t0 ALL NULL NULL NULL NULL # Using where; Start temporary +1 PRIMARY t3 ref b b 5 test.t0.a # End temporary 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 @@ -133,20 +132,17 @@ set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b from t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t2 index b b 5 NULL 20 Using index -select * from t1; -a b -1 1 -1 1 -2 2 +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where +1 PRIMARY t2 ref b b 5 test.t1.a 1 Using index; FirstMatch(t1) select * from t1 where a in (select b from t2); a b 1 1 1 1 2 2 drop table t1, t2, t3; +# +# Test join buffering +# set @save_join_buffer_size = @@join_buffer_size; set join_buffer_size= 8192; create table t1 (a int, filler1 binary(200), filler2 binary(200)); @@ -158,7 +154,7 @@ insert into t1 values (2, 'duplicate ok', 'duplicate ok'); insert into t1 values (18, 'duplicate ok', 'duplicate ok'); insert into t2 values (3, 'duplicate ok', 'duplicate ok'); insert into t2 values (19, 'duplicate ok', 'duplicate ok'); -explain select +explain select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra @@ -346,9 +342,9 @@ t2.Code IN (SELECT Country FROM t3 WHERE Language='English' AND Percentage > 10 AND t2.Population > 100000); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using index condition; Rowid-ordered scan; Start temporary -1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary -1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where +1 PRIMARY t3 ALL PRIMARY,Percentage NULL NULL NULL 22 Using where +1 PRIMARY t1 ref Population,Country Country 3 test.t3.Country 0 Using where; LooseScan +1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t3.Country 1 Using where set optimizer_switch=@bug35674_save_optimizer_switch; DROP TABLE t1,t2,t3; CREATE TABLE t1 ( @@ -630,7 +626,7 @@ select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where -2 MATERIALIZED t3 index PRIMARY PRIMARY 4 NULL 10 Using index +2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY PRIMARY 4 func 1 Using index drop table t0, t1, t2, t3; create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -742,9 +738,8 @@ alter table t3 add primary key(id), add key(a); The following must use loose index scan over t3, key a: explain select count(a) from t2 where a in ( SELECT a FROM t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index a a 5 NULL 1000 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t3 index a a 5 NULL 30000 Using index +1 PRIMARY t2 index a a 5 NULL 1000 Using where; Using index +1 PRIMARY t3 ref a a 5 test.t2.a 30 Using index; FirstMatch(t2) select count(a) from t2 where a in ( SELECT a FROM t3); count(a) 1000 @@ -772,8 +767,7 @@ 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 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where -3 MATERIALIZED t2 ALL NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t3) select 1 from t2 where c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); @@ -841,9 +835,9 @@ explain SELECT * FROM t3 WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY alias1 const PRIMARY PRIMARY 4 const # Using index +1 PRIMARY alias1 const PRIMARY PRIMARY 4 const # 1 PRIMARY alias2 index f12 f12 7 NULL # Using index; LooseScan -1 PRIMARY t1 index NULL PRIMARY 4 NULL # Using index; FirstMatch(alias2) +1 PRIMARY t1 ALL NULL NULL NULL NULL # FirstMatch(alias2) 1 PRIMARY t3 ALL NULL NULL NULL NULL # Using where; Using join buffer (flat, BNL join) SELECT * FROM t3 WHERE f12 IN (SELECT alias2.f12 FROM t1 AS alias1, t2 AS alias2, t1 WHERE alias1.f13 = 24); @@ -913,9 +907,9 @@ SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a WHERE t3.b IN (SELECT b FROM t4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 1 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 2 3 DERIVED t1 ALL NULL NULL NULL NULL 1 SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a @@ -952,35 +946,47 @@ INSERT INTO t1 VALUES (11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4), (16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0); CREATE TABLE t2 ( -pk INT, d VARCHAR(1), e INT, +pk INT, d VARCHAR(1), e INT, f int, PRIMARY KEY(pk), KEY(d,e) ) ENGINE=InnoDB; -INSERT INTO t2 VALUES +INSERT INTO t2 (pk,d,e) VALUES (1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5), (6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1), (11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5), (15,'g',6),(16,'x',7),(17,'f',8); +update t2 set f=pk/2; analyze table t1,t2; 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 +# Original query, changed because of new optimizations explain SELECT * FROM t1 WHERE b IN ( 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 <subquery2> ALL distinct_key NULL NULL NULL 2 +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 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 Warnings: Note 1105 Cannot use key `d` part[0] for lookup: `test`.`t2`.`d` of type `varchar` = "`t1`.`a`" of type `int` +explain SELECT * FROM t1 WHERE b IN ( SELECT d FROM t2, t1 -WHERE a = d AND ( pk < 2 OR d = 'z' ) +WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index_merge PRIMARY,d d,PRIMARY 4,4 NULL 2 Using sort_union(d,PRIMARY); Using where; Start temporary +1 PRIMARY t1 ref a a 5 test.t2.d 1 Using where; Using index +1 PRIMARY t1 ref b b 4 test.t2.d 1 End temporary +Warnings: +Note 1105 Cannot use key `d` part[0] for lookup: `test`.`t2`.`d` of type `varchar` = "`t1`.`a`" of type `int` +SELECT * FROM t1 WHERE b IN ( +SELECT d FROM t2, t1 +WHERE a = d AND ( pk < 2 OR d = 'z' ) and f > 0 ); a b c Warnings: @@ -1228,10 +1234,10 @@ t1_pk1 t1_pk2 t3_i t3_c explain SELECT * FROM t1, t3 WHERE t3_c IN ( SELECT t1_pk2 FROM t4, t2 WHERE t2_c = t1_pk2 AND t2_i >= t3_i ) AND ( t1_pk1 = 'POL' ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref PRIMARY PRIMARY 5 const 1 Using where; Using index -1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Start temporary -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t4 index NULL PRIMARY 59 NULL 2 Using where; Using index; End temporary +1 PRIMARY t1 ref PRIMARY PRIMARY 5 const 1 Using where +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t3) DROP TABLE t1,t2,t3,t4; # # MDEV-6263: Wrong result when using IN subquery with order by @@ -1349,9 +1355,9 @@ WHERE T3_0_.t3idref= 1 ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY T3_0_ ref PRIMARY,FK_T3_T2Id PRIMARY 8 const 3 Using index; Start temporary +1 PRIMARY T3_0_ ref PRIMARY,FK_T3_T2Id PRIMARY 8 const 3 Start temporary 1 PRIMARY T2_1_ eq_ref PRIMARY,FK_T2_T1Id PRIMARY 8 test.T3_0_.t2idref 1 -1 PRIMARY T1_1_ eq_ref PRIMARY PRIMARY 8 test.T2_1_.t1idref 1 Using index +1 PRIMARY T1_1_ eq_ref PRIMARY PRIMARY 8 test.T2_1_.t1idref 1 1 PRIMARY T2_0_ ref FK_T2_T1Id FK_T2_T1Id 8 test.T2_1_.t1idref 1 Using index; End temporary drop table t3,t2,t1; set optimizer_search_depth=@tmp7474; @@ -1513,8 +1519,7 @@ t3.sack_id = 33479 AND t3.kit_id = 6; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 5 Using index 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.cat_id 1 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED t4 index cat_id cat_id 4 NULL 19 Using index +1 PRIMARY t4 ref cat_id cat_id 4 test.t3.cat_id 1 Using index; FirstMatch(t1) SELECT count(*) FROM t1, t3 WHERE t1.cat_id = t3.cat_id AND t3.cat_id IN (SELECT cat_id FROM t4) AND @@ -1529,8 +1534,7 @@ t3.sack_id = 33479 AND t3.kit_id = 6; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ref PRIMARY PRIMARY 5 const,const 5 Using index 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.cat_id 1 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where -2 MATERIALIZED t2 index cat_id cat_id 4 NULL 19 Using index +1 PRIMARY t2 ref cat_id cat_id 4 test.t3.cat_id 2 Using where; Using index; FirstMatch(t1) SELECT count(*) FROM t1, t3 WHERE t1.cat_id = t3.cat_id AND t3.cat_id IN (SELECT cat_id FROM t2) AND @@ -1559,7 +1563,7 @@ WHERE ( b1, b1 ) IN ( SELECT a4, b4 FROM t3, t4); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary 1 PRIMARY t1 ref idx idx 2 test.t4.a4 1 100.00 Using index -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 16.67 End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` semi join (`test`.`t3` join `test`.`t4`) left join `test`.`t2` on(`test`.`t2`.`a2` = `test`.`t4`.`a4`) where `test`.`t4`.`b4` = `test`.`t4`.`a4` and `test`.`t1`.`b1` = `test`.`t4`.`a4` @@ -1577,7 +1581,7 @@ WHERE ( b1, b1 ) IN ( SELECT a4, b4 FROM t3, t4); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t4 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary 1 PRIMARY t1 ref idx idx 2 test.t4.a4 1 100.00 Using index -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 16.67 End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` semi join (`test`.`t3` join `test`.`t4`) left join `test`.`t2` on(`test`.`t2`.`a2` = `test`.`t4`.`a4`) where `test`.`t4`.`b4` = `test`.`t4`.`a4` and `test`.`t1`.`b1` = `test`.`t4`.`a4` @@ -1612,7 +1616,7 @@ WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4); 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 t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary; Using join buffer (flat, BNL join) -1 PRIMARY t3 ALL NULL NULL NULL NULL 10 100.00 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 10 5.00 Using where; End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t3` join `test`.`t4`) left join `test`.`t2` on(`test`.`t2`.`c2` = `test`.`t1`.`c1` or `test`.`t1`.`c1` > 'z') where `test`.`t4`.`c4` = `test`.`t1`.`c1` and `test`.`t3`.`c3` = `test`.`t1`.`c1` @@ -1661,7 +1665,7 @@ SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 -2 DEPENDENT SUBQUERY t3 hash_ALL NULL #hash#$hj 5 func 3 100.00 Using where; Using join buffer (flat, BNLH join) +2 DEPENDENT SUBQUERY t3 hash_ALL NULL #hash#$hj 5 func 3 10.00 Using where; Using join buffer (flat, BNLH join) Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`i1` AS `i1` from `test`.`t1` where <expr_cache><`test`.`t1`.`i1`>(<in_optimizer>(`test`.`t1`.`i1`,<exists>(/* select#2 */ select `test`.`t3`.`i3` from `test`.`t2` join `test`.`t3` where `test`.`t3`.`i3` = `test`.`t2`.`i2` and <cache>(`test`.`t1`.`i1`) = `test`.`t3`.`i3`))) SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); @@ -1671,12 +1675,11 @@ set optimizer_switch='materialization=on,semijoin=on'; EXPLAIN EXTENDED SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Using where -2 MATERIALIZED t3 hash_ALL NULL #hash#$hj 5 test.t2.i2 3 100.00 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary +1 PRIMARY t3 hash_ALL NULL #hash#$hj 5 test.t2.i2 3 10.00 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t1 hash_ALL NULL #hash#$hj 5 test.t2.i2 5 10.00 Using where; End temporary; Using join buffer (flat, BNLH join) Warnings: -Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` +Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`i1` = `test`.`t2`.`i2` SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); i1 7 @@ -1684,12 +1687,11 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 Using where -2 MATERIALIZED t3 hash_ALL NULL #hash#$hj 5 test.t2.i2 3 100.00 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Start temporary +1 PRIMARY t3 hash_ALL NULL #hash#$hj 5 test.t2.i2 3 10.00 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t1 hash_ALL NULL #hash#$hj 5 test.t2.i2 5 10.00 Using where; End temporary; Using join buffer (flat, BNLH join) Warnings: -Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t3`.`i3` > 0 +Note 1003 select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where `test`.`t3`.`i3` = `test`.`t2`.`i2` and `test`.`t1`.`i1` = `test`.`t2`.`i2` and `test`.`t3`.`i3` > 0 SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2); i1 @@ -1716,11 +1718,10 @@ i explain extended select * from t1 where (rand() < 0) and i in (select i from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 10 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 10 10.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join (`test`.`t2`) where rand() < 0 +Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`i` = `test`.`t1`.`i` and rand() < 0 drop table t1,t2; set optimizer_switch=@save_optimizer_switch; # @@ -1729,7 +1730,13 @@ set optimizer_switch=@save_optimizer_switch; CREATE TABLE t1 (f1 varchar(8), KEY(f1)) ENGINE=InnoDB; INSERT INTO t1 VALUES ('qux'),('foo'); CREATE TABLE t2 (f2 varchar(8)) ENGINE=InnoDB; -INSERT INTO t2 VALUES ('bar'),('foo'),('qux'); +INSERT INTO t2 VALUES ('bar'),('foo'),('qux'),('qq1'),('qq2'); +analyze table t1,t2 persistent for all; +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 SELECT f1 FROM t1 WHERE f1 IN ( SELECT f2 FROM t2 WHERE f2 > 'bar' ) HAVING f1 != 'foo' @@ -1741,9 +1748,8 @@ WHERE f1 IN ( SELECT f2 FROM t2 WHERE f2 > 'bar' ) HAVING f1 != 'foo' ORDER BY f1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range f1 f1 11 NULL 2 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 11 func 1 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t1 range f1 f1 11 NULL 2 Using where; Using index; Using temporary; Using filesort +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) DROP TABLE t1,t2; # # MDEV-16225: wrong resultset from query with semijoin=on @@ -1781,7 +1787,7 @@ OR (t.id IN (0,4,12,13,1,10,3,11)) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t index PRIMARY PRIMARY 4 NULL 114 Using where; Using index +1 PRIMARY t ALL PRIMARY NULL NULL NULL 114 Using where 2 MATERIALIZED A ALL PRIMARY NULL NULL NULL 114 2 MATERIALIZED <subquery3> eq_ref distinct_key distinct_key 67 func 1 3 MATERIALIZED B range PRIMARY PRIMARY 4 NULL 8 Using where @@ -1840,16 +1846,15 @@ explain SELECT t2.id FROM t2,t1 WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 30 Using index -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where -2 MATERIALIZED t3 ALL NULL NULL NULL NULL 14 -2 MATERIALIZED t1 eq_ref PRIMARY PRIMARY 4 test.t3.id 1 Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 14 Using where; Start temporary +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.ref_id 1 Using where; Using index +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.id 1 Using index; End temporary +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.ref_id 1 Using where; Using index SELECT t2.id FROM t2,t1 WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; id -10 11 +10 set optimizer_switch='materialization=off'; SELECT t2.id FROM t2,t1 WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; @@ -1946,16 +1951,15 @@ AND t3.id_product IN (SELECT id_product FROM t2 t2_3 WHERE t2_3.id_t2 = 18 OR t2 AND t3.id_product IN (SELECT id_product FROM t2 t2_4 WHERE t2_4.id_t2 = 34 OR t2_4.id_t2 = 23) AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2_5.id_t2 = 28 OR t2_5.id_t2 = 26); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index PRIMARY PRIMARY 4 NULL 18 Using index -1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where -1 PRIMARY t5 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2_2 ref id_t2,id_product id_t2 5 const 12 Using where; Start temporary +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t2_2.id_product 1 Using where; Using index; End temporary 1 PRIMARY t4 eq_ref PRIMARY PRIMARY 8 test.t3.id_product,const 1 Using where; Using index 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where -1 PRIMARY t1 index NULL PRIMARY 8 NULL 73 Using where; Using index; Using join buffer (flat, BNL join) 1 PRIMARY <subquery6> eq_ref distinct_key distinct_key 4 func 1 Using where -3 MATERIALIZED t2_2 ref id_t2,id_product id_t2 5 const 12 +1 PRIMARY t5 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 index NULL PRIMARY 8 NULL 73 Using where; Using index; Using join buffer (flat, BNL join) 2 MATERIALIZED t2_1 ALL id_t2,id_product NULL NULL NULL 223 Using where 4 MATERIALIZED t2_3 range id_t2,id_product id_t2 5 NULL 33 Using index condition; Using where 5 MATERIALIZED t2_4 range id_t2,id_product id_t2 5 NULL 18 Using index condition; Using where |