EVOLUTION-MANAGER

Edit File: innodb_mrr_cpk.result

drop table if exists t0,t1,t2,t3; set @innodb_mrr_cpk_tmp=@@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set @save_join_cache_level=@@join_cache_level; set join_cache_level=6; set @save_storage_engine=@@storage_engine; set storage_engine=innodb; create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(a char(8), b char(8), filler char(100), primary key(a)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(8) NOT NULL, `b` char(8) DEFAULT NULL, `filler` char(100) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 insert into t1 select concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), concat('b-', 1000 + A.a + B.a*10 + C.a*100, '=B'), 'filler' from t0 A, t0 B, t0 C; create table t2 (a char(8)); insert into t2 values ('a-1010=A'), ('a-1030=A'), ('a-1020=A'); This should use join buffer: explain select * from t1, t2 where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 8 test.t2.a 1 Using join buffer (flat, BKA join); Key-ordered scan This output must be sorted by value of t1.a: select * from t1, t2 where t1.a=t2.a; a b filler a a-1010=A b-1010=B filler a-1010=A a-1020=A b-1020=B filler a-1020=A a-1030=A b-1030=B filler a-1030=A drop table t1, t2; create table t1( a char(8) character set utf8, b int, filler char(100), primary key(a,b) ); insert into t1 select concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), 1000 + A.a + B.a*10 + C.a*100, 'filler' from t0 A, t0 B, t0 C; create table t2 (a char(8) character set utf8, b int); insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020); explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 28 test.t2.a,test.t2.b 1 Using join buffer (flat, BKA join); Key-ordered scan select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; a b filler a b a-1010=A 1010 filler a-1010=A 1010 a-1020=A 1020 filler a-1020=A 1020 a-1030=A 1030 filler a-1030=A 1030 insert into t2 values ('a-1030=A', 1030), ('a-1020=A', 1020); explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 28 test.t2.a,test.t2.b 1 Using join buffer (flat, BKA join); Key-ordered scan select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; a b filler a b a-1010=A 1010 filler a-1010=A 1010 a-1020=A 1020 filler a-1020=A 1020 a-1020=A 1020 filler a-1020=A 1020 a-1030=A 1030 filler a-1030=A 1030 a-1030=A 1030 filler a-1030=A 1030 drop table t1, t2; create table t1( a varchar(8) character set utf8, b int, filler char(100), primary key(a,b) ); insert into t1 select concat('a-', 1000 + A.a + B.a*10 + C.a*100, '=A'), 1000 + A.a + B.a*10 + C.a*100, 'filler' from t0 A, t0 B, t0 C; create table t2 (a char(8) character set utf8, b int); insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020); explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 30 test.t2.a,test.t2.b 1 Using where; Using join buffer (flat, BKA join); Key-ordered scan select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; a b filler a b a-1010=A 1010 filler a-1010=A 1010 a-1020=A 1020 filler a-1020=A 1020 a-1030=A 1030 filler a-1030=A 1030 explain select * from t1, t2 where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where 1 SIMPLE t1 ref PRIMARY PRIMARY 26 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered scan select * from t1, t2 where t1.a=t2.a; a b filler a b a-1010=A 1010 filler a-1010=A 1010 a-1020=A 1020 filler a-1020=A 1020 a-1030=A 1030 filler a-1030=A 1030 drop table t1, t2; create table t1 (a int, b int, c int, filler char(100), primary key(a,b,c)); insert into t1 select A.a, B.a, C.a, 'filler' from t0 A, t0 B, t0 C; insert into t1 values (11, 11, 11, 'filler'); insert into t1 values (11, 11, 12, 'filler'); insert into t1 values (11, 11, 13, 'filler'); insert into t1 values (11, 22, 1234, 'filler'); insert into t1 values (11, 33, 124, 'filler'); insert into t1 values (11, 33, 125, 'filler'); create table t2 (a int, b int); insert into t2 values (11,33), (11,22), (11,11); explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where 1 SIMPLE t1 ref PRIMARY PRIMARY 8 test.t2.a,test.t2.b 1 Using join buffer (flat, BKA join); Key-ordered scan select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; a b c filler a b 11 11 11 filler 11 11 11 11 12 filler 11 11 11 11 13 filler 11 11 11 22 1234 filler 11 22 11 33 124 filler 11 33 11 33 125 filler 11 33 set join_cache_level=0; select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; a b c filler a b 11 33 124 filler 11 33 11 33 125 filler 11 33 11 22 1234 filler 11 22 11 11 11 filler 11 11 11 11 12 filler 11 11 11 11 13 filler 11 11 set join_cache_level=6; explain select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where 1 SIMPLE t1 ref PRIMARY PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered scan select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; a b c filler a b set optimizer_switch='index_condition_pushdown=off'; explain select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where 1 SIMPLE t1 ref PRIMARY PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered scan select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; a b c filler a b set optimizer_switch='index_condition_pushdown=on'; drop table t1,t2; drop table t0; # # MDEV-6878: Use of uninitialized saved_primary_key in Mrr_ordered_index_reader::resume_read() # create table t0(a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 ( pk varchar(32) character set utf8 primary key, kp1 char(32) not null, col1 varchar(32), key (kp1) ) engine=innodb; insert into t1 select concat('pk-', 1000 +A.a), concat('kp1-', 1000 +A.a), concat('val-', 1000 +A.a) from test.t0 A ; create table t2 as select kp1 as a from t1; set join_cache_level=8; set optimizer_switch='mrr=on,mrr_sort_keys=on'; explain select * from t2 straight_join t1 force index(kp1) where t1.kp1=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 10 1 SIMPLE t1 ref kp1 kp1 32 test.t2.a 1 Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan select * from t2 straight_join t1 force index(kp1) where t1.kp1=t2.a; a pk kp1 col1 kp1-1000 pk-1000 kp1-1000 val-1000 kp1-1001 pk-1001 kp1-1001 val-1001 kp1-1002 pk-1002 kp1-1002 val-1002 kp1-1003 pk-1003 kp1-1003 val-1003 kp1-1004 pk-1004 kp1-1004 val-1004 kp1-1005 pk-1005 kp1-1005 val-1005 kp1-1006 pk-1006 kp1-1006 val-1006 kp1-1007 pk-1007 kp1-1007 val-1007 kp1-1008 pk-1008 kp1-1008 val-1008 kp1-1009 pk-1009 kp1-1009 val-1009 drop table t0,t1,t2; # # # MDEV-3817: Wrong result with index_merge+index_merge_intersection, InnoDB table, join, AND and OR conditions # set @tmp_mdev3817=@@optimizer_switch; SET optimizer_switch='index_merge=on,index_merge_intersection=on'; CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, c VARCHAR(1024) CHARACTER SET utf8, d INT, KEY (b) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, 9, 'one', 11), (2, 6, 'two', 12), (3, 2, 'three', 13), (4, 5, 'four', 14); CREATE TABLE t2 (e INT, g INT) ENGINE=InnoDB; INSERT INTO t2 VALUES (1,9), (2,6) ; SELECT * FROM t1, t2 WHERE g = b AND ( a < 7 OR a > e ); a b c d e g 1 9 one 11 1 9 2 6 two 12 2 6 DROP TABLE t1, t2; set optimizer_switch=@tmp_mdev3817; # # MDEV-5037: Server crash on a JOIN on a derived table with join_cache_level > 2 # create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); CREATE TABLE t1 ( id char(8) CHARACTER SET utf8 NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE t2 ( id char(8) CHARACTER SET utf8 DEFAULT NULL, url text CHARACTER SET utf8 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into t1 select '03b2ca8c' from t0 A, t0 B limit 80; insert into t2 select '03b2ca8c','' from t0 A, t0 B, t0 C; set @tmp_mdev5037=@@join_cache_level; set join_cache_level=3; explain SELECT 1 FROM (SELECT url, id FROM t2 LIMIT 1 OFFSET 20) derived RIGHT JOIN t1 ON t1.id = derived.id; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL # 1 PRIMARY <derived2> ref key0 key0 25 test.t1.id # 2 DERIVED t2 ALL NULL NULL NULL NULL # set join_cache_level= @tmp_mdev5037; drop table t0,t1,t2; # # This must be at the end: # set @@join_cache_level= @save_join_cache_level; set storage_engine=@save_storage_engine; set optimizer_switch=@innodb_mrr_cpk_tmp;