diff options
42 files changed, 2666 insertions, 305 deletions
diff --git a/mysql-test/r/innodb_mrr.result b/mysql-test/r/innodb_mrr.result index 7b1c18d2523..1e50a8b84ed 100644 --- a/mysql-test/r/innodb_mrr.result +++ b/mysql-test/r/innodb_mrr.result @@ -402,3 +402,33 @@ SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; id parent_id name 60 40 F drop table t1; +# +# BUG#628785: multi_range_read.cc:430: int DsMrr_impl::dsmrr_init(): Assertion `do_sort_keys || do_rowid_fetch' failed +# +set @save_join_cache_level= @@join_cache_level; +set @save_optimizer_switch= @@optimizer_switch; +SET SESSION join_cache_level=9; +Warnings: +Warning 1292 Truncated incorrect join_cache_level value: '9' +SET SESSION optimizer_switch='mrr_sort_keys=off'; +CREATE TABLE `t1` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`col_int_nokey` int(11) DEFAULT NULL, +`col_int_key` int(11) DEFAULT NULL, +`col_varchar_key` varchar(1) DEFAULT NULL, +`col_varchar_nokey` varchar(1) DEFAULT NULL, +PRIMARY KEY (`pk`), +KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1; +INSERT INTO `t1` VALUES (1,6,NULL,'r','r'); +INSERT INTO `t1` VALUES (2,8,0,'c','c'); +INSERT INTO `t1` VALUES (97,7,0,'z','z'); +INSERT INTO `t1` VALUES (98,1,1,'j','j'); +INSERT INTO `t1` VALUES (99,7,8,'c','c'); +INSERT INTO `t1` VALUES (100,2,5,'f','f'); +SELECT table1 .`col_varchar_key` +FROM t1 table1 STRAIGHT_JOIN ( t1 table3 JOIN t1 table4 ON table4 .`pk` = table3 .`col_int_nokey` ) ON table4 .`col_varchar_nokey` ; +col_varchar_key +DROP TABLE t1; +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/innodb_mrr_cpk.result b/mysql-test/r/innodb_mrr_cpk.result new file mode 100644 index 00000000000..469d78e8e45 --- /dev/null +++ b/mysql-test/r/innodb_mrr_cpk.result @@ -0,0 +1,148 @@ +drop table if exists t0,t1,t2,t3; +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 DEFAULT '', + `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 +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 8 test.t2.a 1 Using join buffer +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 +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 28 test.t2.a,test.t2.b 1 Using join buffer +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 +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 28 test.t2.a,test.t2.b 1 Using join buffer +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 +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 30 test.t2.a,test.t2.b 1 Using index condition(BKA); Using join buffer +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 +1 SIMPLE t1 ref PRIMARY PRIMARY 26 test.t2.a 1 Using index condition(BKA); Using join buffer +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 +1 SIMPLE t1 ref PRIMARY PRIMARY 8 test.t2.a,test.t2.b 1 Using join buffer +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 +1 SIMPLE t1 ref PRIMARY PRIMARY 4 test.t2.a 1 Using index condition(BKA); Using join buffer +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 +1 SIMPLE t1 ref PRIMARY PRIMARY 4 test.t2.a 1 Using where; Using join buffer +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; +set @@join_cache_level= @save_join_cache_level; +set storage_engine=@save_storage_engine; +drop table t0; diff --git a/mysql-test/r/maria_mrr.result b/mysql-test/r/maria_mrr.result index e7c958943d8..ac1045eb0cb 100644 --- a/mysql-test/r/maria_mrr.result +++ b/mysql-test/r/maria_mrr.result @@ -1,4 +1,5 @@ drop table if exists t1,t2,t3,t4; +set @mrr_buffer_size_save= @@mrr_buffer_size; set @save_storage_engine= @@storage_engine; set storage_engine=aria; create table t1(a int); @@ -292,6 +293,35 @@ NULL 9 0 NULL 9 0 drop table t1, t2; set storage_engine= @save_storage_engine; +set @@mrr_buffer_size= @mrr_buffer_size_save; +# +# Crash in quick_range_seq_next() in maria-5.3-dsmrr-cpk with join_cache_level = {8,1} +# +set @save_join_cache_level= @@join_cache_level; +SET SESSION join_cache_level = 8; +CREATE TABLE `t1` ( +`col_int_key` int(11) DEFAULT NULL, +`col_datetime_key` datetime DEFAULT NULL, +`col_varchar_key` varchar(1) DEFAULT NULL, +`col_varchar_nokey` varchar(1) DEFAULT NULL, +KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; +INSERT INTO `t1` VALUES (6,'2005-10-07 00:00:00','e','e'); +INSERT INTO `t1` VALUES (51,'2000-07-15 05:00:34','f','f'); +CREATE TABLE `t2` ( +`col_int_key` int(11) DEFAULT NULL, +`col_datetime_key` datetime DEFAULT NULL, +`col_varchar_key` varchar(1) DEFAULT NULL, +`col_varchar_nokey` varchar(1) DEFAULT NULL, +KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; +INSERT INTO `t2` VALUES (2,'2004-10-11 18:13:16','w','w'); +INSERT INTO `t2` VALUES (2,'1900-01-01 00:00:00','d','d'); +SELECT table2 .`col_datetime_key` +FROM t2 JOIN ( t1 table2 JOIN t2 table3 ON table3 .`col_varchar_key` < table2 .`col_varchar_key` ) ON table3 .`col_varchar_nokey` ; +col_datetime_key +drop table t1, t2; +set join_cache_level=@save_join_cache_level; CREATE TABLE t1( pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, PRIMARY KEY (pk), INDEX idx (v, i) diff --git a/mysql-test/r/maria_mrr.result.moved b/mysql-test/r/maria_mrr.result.moved new file mode 100644 index 00000000000..621c221e732 --- /dev/null +++ b/mysql-test/r/maria_mrr.result.moved @@ -0,0 +1,294 @@ +drop table if exists t1,t2,t3,t4; +set @save_storage_engine= @@storage_engine; +set storage_engine=aria; +create table t1(a int); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2(a int); +insert into t2 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C; +create table t3 ( +a char(8) not null, b char(8) not null, filler char(200), +key(a) +); +insert into t3 select @a:=concat('c-', 1000+ A.a, '=w'), @a, 'filler' from t2 A; +insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 2000+A.a, '=w'), +'filler-1' from t2 A; +insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 3000+A.a, '=w'), +'filler-2' from t2 A; +select a,filler from t3 where a >= 'c-9011=w'; +a filler +select a,filler from t3 where a >= 'c-1011=w' and a <= 'c-1015=w'; +a filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1011=w filler-1 +c-1012=w filler-1 +c-1013=w filler-1 +c-1014=w filler-1 +c-1015=w filler-1 +c-1011=w filler-2 +c-1012=w filler-2 +c-1013=w filler-2 +c-1014=w filler-2 +c-1015=w filler-2 +select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or +(a>='c-1014=w' and a <= 'c-1015=w'); +a filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1011=w filler-1 +c-1012=w filler-1 +c-1013=w filler-1 +c-1014=w filler-1 +c-1015=w filler-1 +c-1011=w filler-2 +c-1012=w filler-2 +c-1013=w filler-2 +c-1014=w filler-2 +c-1015=w filler-2 +insert into t3 values ('c-1013=z', 'c-1013=z', 'err'); +insert into t3 values ('a-1014=w', 'a-1014=w', 'err'); +select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or +(a>='c-1014=w' and a <= 'c-1015=w'); +a filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1011=w filler-1 +c-1012=w filler-1 +c-1013=w filler-1 +c-1014=w filler-1 +c-1015=w filler-1 +c-1011=w filler-2 +c-1012=w filler-2 +c-1013=w filler-2 +c-1014=w filler-2 +c-1015=w filler-2 +delete from t3 where b in ('c-1013=z', 'a-1014=w'); +select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or +a='c-1014=w' or a='c-1015=w'; +a filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1011=w filler-1 +c-1012=w filler-1 +c-1013=w filler-1 +c-1014=w filler-1 +c-1015=w filler-1 +c-1011=w filler-2 +c-1012=w filler-2 +c-1013=w filler-2 +c-1014=w filler-2 +c-1015=w filler-2 +insert into t3 values ('c-1013=w', 'del-me', 'inserted'); +select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or +a='c-1014=w' or a='c-1015=w'; +a filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1011=w filler-1 +c-1012=w filler-1 +c-1013=w filler-1 +c-1014=w filler-1 +c-1015=w filler-1 +c-1011=w filler-2 +c-1012=w filler-2 +c-1013=w filler-2 +c-1014=w filler-2 +c-1015=w filler-2 +c-1013=w inserted +delete from t3 where b='del-me'; +alter table t3 add primary key(b); +select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or +b IN ('c-1019=w', 'c-1020=w', 'c-1021=w', +'c-1022=w', 'c-1023=w', 'c-1024=w'); +b filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1016=w filler +c-1017=w filler +c-1018=w filler +c-1019=w filler +c-1020=w filler +c-1021=w filler +c-1022=w filler +c-1023=w filler +c-1024=w filler +select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1020=w') or +b IN ('c-1021=w', 'c-1022=w', 'c-1023=w'); +b filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1016=w filler +c-1017=w filler +c-1018=w filler +c-1019=w filler +c-1020=w filler +c-1021=w filler +c-1022=w filler +c-1023=w filler +select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or +b IN ('c-1019=w', 'c-1020=w') or +(b>='c-1021=w' and b<= 'c-1023=w'); +b filler +c-1011=w filler +c-1012=w filler +c-1013=w filler +c-1014=w filler +c-1015=w filler +c-1016=w filler +c-1017=w filler +c-1018=w filler +c-1019=w filler +c-1020=w filler +c-1021=w filler +c-1022=w filler +c-1023=w filler +create table t4 (a varchar(10), b int, c char(10), filler char(200), +key idx1 (a, b, c)); +insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15; +insert into t4 (a,b,c,filler) +select 'b-1',NULL,'c-1', concat('NULL-', 15-a) from t2 order by a limit 15; +insert into t4 (a,b,c,filler) +select 'b-1',NULL,'c-222', concat('NULL-', 15-a) from t2 order by a limit 15; +insert into t4 (a,b,c,filler) +select 'bb-1',NULL,'cc-2', concat('NULL-', 15-a) from t2 order by a limit 15; +insert into t4 (a,b,c,filler) +select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500; +explain +select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' + or c='no-such-row2'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Using MRR +select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' + or c='no-such-row2'); +a b c filler +NULL NULL NULL NULL-15 +NULL NULL NULL NULL-14 +NULL NULL NULL NULL-13 +NULL NULL NULL NULL-12 +NULL NULL NULL NULL-11 +NULL NULL NULL NULL-10 +NULL NULL NULL NULL-9 +NULL NULL NULL NULL-8 +NULL NULL NULL NULL-7 +NULL NULL NULL NULL-6 +NULL NULL NULL NULL-5 +NULL NULL NULL NULL-4 +NULL NULL NULL NULL-3 +NULL NULL NULL NULL-2 +NULL NULL NULL NULL-1 +explain +select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Using MRR +select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); +a b c filler +b-1 NULL c-1 NULL-15 +b-1 NULL c-1 NULL-14 +b-1 NULL c-1 NULL-13 +b-1 NULL c-1 NULL-12 +b-1 NULL c-1 NULL-11 +b-1 NULL c-1 NULL-10 +b-1 NULL c-1 NULL-9 +b-1 NULL c-1 NULL-8 +b-1 NULL c-1 NULL-7 +b-1 NULL c-1 NULL-6 +b-1 NULL c-1 NULL-5 +b-1 NULL c-1 NULL-4 +b-1 NULL c-1 NULL-3 +b-1 NULL c-1 NULL-2 +b-1 NULL c-1 NULL-1 +bb-1 NULL cc-2 NULL-15 +bb-1 NULL cc-2 NULL-14 +bb-1 NULL cc-2 NULL-13 +bb-1 NULL cc-2 NULL-12 +bb-1 NULL cc-2 NULL-11 +bb-1 NULL cc-2 NULL-10 +bb-1 NULL cc-2 NULL-9 +bb-1 NULL cc-2 NULL-8 +bb-1 NULL cc-2 NULL-7 +bb-1 NULL cc-2 NULL-6 +bb-1 NULL cc-2 NULL-5 +bb-1 NULL cc-2 NULL-4 +bb-1 NULL cc-2 NULL-3 +bb-1 NULL cc-2 NULL-2 +bb-1 NULL cc-2 NULL-1 +select * from t4 ignore index(idx1) where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); +a b c filler +b-1 NULL c-1 NULL-15 +b-1 NULL c-1 NULL-14 +b-1 NULL c-1 NULL-13 +b-1 NULL c-1 NULL-12 +b-1 NULL c-1 NULL-11 +b-1 NULL c-1 NULL-10 +b-1 NULL c-1 NULL-9 +b-1 NULL c-1 NULL-8 +b-1 NULL c-1 NULL-7 +b-1 NULL c-1 NULL-6 +b-1 NULL c-1 NULL-5 +b-1 NULL c-1 NULL-4 +b-1 NULL c-1 NULL-3 +b-1 NULL c-1 NULL-2 +b-1 NULL c-1 NULL-1 +bb-1 NULL cc-2 NULL-15 +bb-1 NULL cc-2 NULL-14 +bb-1 NULL cc-2 NULL-13 +bb-1 NULL cc-2 NULL-12 +bb-1 NULL cc-2 NULL-11 +bb-1 NULL cc-2 NULL-10 +bb-1 NULL cc-2 NULL-9 +bb-1 NULL cc-2 NULL-8 +bb-1 NULL cc-2 NULL-7 +bb-1 NULL cc-2 NULL-6 +bb-1 NULL cc-2 NULL-5 +bb-1 NULL cc-2 NULL-4 +bb-1 NULL cc-2 NULL-3 +bb-1 NULL cc-2 NULL-2 +bb-1 NULL cc-2 NULL-1 +drop table t1, t2, t3, t4; +create table t1 (a int, b int not null,unique key (a,b),index(b)); +insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); +create table t2 like t1; +insert into t2 select * from t1; +alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); +select * from t1 where a is null; +a b c +NULL 7 0 +NULL 9 0 +NULL 9 0 +select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; +a b c +NULL 9 0 +NULL 9 0 +select * from t1 where a is null and b=9 or a is null and b=7 limit 3; +a b c +NULL 7 0 +NULL 9 0 +NULL 9 0 +drop table t1, t2; +set storage_engine= @save_storage_engine; diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result index 5db03db85ac..5b3e966f83d 100644 --- a/mysql-test/r/myisam_mrr.result +++ b/mysql-test/r/myisam_mrr.result @@ -413,4 +413,52 @@ explain select * from t1 where a < 20; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 20 Using index condition; Using MRR set optimizer_switch=@save_optimizer_switch; +# +# BUG#629684: Unreachable code in multi_range_read.cc in maria-5.3-dsmrr-cpk +# +delete from t0 where a > 2; +insert into t0 values (NULL),(NULL); +insert into t1 values (NULL, 1234), (NULL, 5678); +set @save_join_cache_level=@@join_cache_level; +set @@join_cache_level=6; +explain +select * from t0, t1 where t0.a<=>t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 5 +1 SIMPLE t1 ref a a 5 test.t0.a 1 Using index condition(BKA); Using join buffer +select * from t0, t1 where t0.a<=>t1.a; +a a b +0 0 0 +1 1 1 +2 2 2 +NULL NULL 1234 +NULL NULL 1234 +NULL NULL 5678 +NULL NULL 5678 +set @@join_cache_level=@save_join_cache_level; drop table t0, t1; +# +# BUG#625841: Assertion `!table || (!table->read_set || bitmap_is_set +# (table->read_set, field_index))' on REPLACE ... SELECT with MRR +# +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 ( +key1 varchar(10), +col1 char(255), col2 char(255), +col3 char(244), col4 char(255), +key(key1) +); +create table t2 like t1; +insert into t1 +select +1000+A.a+100*B.a + 10*C.a, +'col1val', 'col2val', +'col3val', 'col4val' +from t0 A, t0 B, t0 C; +REPLACE INTO t2(col2,col3,col4) +SELECT col2,col3,col4 +FROM t1 +WHERE `key1` LIKE CONCAT( LEFT( '1' , 7 ) , '%' ) +ORDER BY col1 LIMIT 7; +drop table t0, t1, t2; diff --git a/mysql-test/r/optimizer_switch.result b/mysql-test/r/optimizer_switch.result index 6bccefe54be..13a5f1ab232 100644 --- a/mysql-test/r/optimizer_switch.result +++ b/mysql-test/r/optimizer_switch.result @@ -4,19 +4,19 @@ # select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on set optimizer_switch='index_merge=off,index_merge_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on set optimizer_switch='index_merge_union=on'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on set optimizer_switch='default,index_merge_sort_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on set optimizer_switch=4; ERROR 42000: Variable 'optimizer_switch' can't be set to the value of '4' set optimizer_switch=NULL; @@ -43,57 +43,57 @@ set optimizer_switch=default; set optimizer_switch='index_merge=off,index_merge_union=off,default'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on set optimizer_switch=default; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on set @@global.optimizer_switch=default; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on # # Check index_merge's @@optimizer_switch flags # select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on BUG#37120 optimizer_switch allowable values not according to specification select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on set optimizer_switch='default,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on set optimizer_switch='default,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on set optimizer_switch='default,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on set optimizer_switch='default,semijoin=off,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on set optimizer_switch='default,materialization=off,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on set optimizer_switch='default,semijoin=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on set optimizer_switch='default,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on set optimizer_switch=default; diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index dcd40f66365..fc1cd57dd28 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1489,7 +1489,7 @@ SELECT d FROM t1, t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using temporary; Using filesort +1 SIMPLE t1 ref a,b b 4 const 4 Using index condition; Using where; Using temporary; Using filesort 1 SIMPLE t2 ref a,b,c a 40 test.t1.a,const 11 Using index condition SELECT d FROM t1, t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 7722fbe5dcc..c1d87645fff 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3562,19 +3562,19 @@ EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk < 'c' AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using MRR +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using where; Using MRR 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Using MRR 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Using MRR 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where DROP TABLE t1,t2; CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a)); @@ -3608,7 +3608,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using MRR +1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where; Using MRR 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where EXPLAIN SELECT t3.a FROM t1,t2,t3 @@ -3616,7 +3616,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND t3.a=t2.a AND t3.c IN ('bb','ee') ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using MRR +1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where; Using MRR 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where EXPLAIN SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 @@ -3624,7 +3624,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using MRR +1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where; Using MRR 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where EXPLAIN SELECT t3.a FROM t1,t2,t3 @@ -3632,7 +3632,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using MRR +1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where; Using MRR 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where DROP TABLE t1,t2,t3; CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index fb09a62eb73..ec511396f79 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -3566,19 +3566,19 @@ EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk < 'c' AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using MRR +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using where; Using MRR 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where; Using join buffer EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Using MRR 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where; Using join buffer EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Using MRR 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where; Using join buffer DROP TABLE t1,t2; CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a)); @@ -3612,7 +3612,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using MRR +1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where; Using MRR 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer EXPLAIN SELECT t3.a FROM t1,t2,t3 @@ -3620,7 +3620,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND t3.a=t2.a AND t3.c IN ('bb','ee') ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using MRR +1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where; Using MRR 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer EXPLAIN SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 @@ -3628,7 +3628,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using MRR +1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where; Using MRR 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer EXPLAIN SELECT t3.a FROM t1,t2,t3 @@ -3636,7 +3636,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using MRR +1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where; Using MRR 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where; Using join buffer DROP TABLE t1,t2,t3; CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index 7722fbe5dcc..c1d87645fff 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -3562,19 +3562,19 @@ EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk < 'c' AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using MRR +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 3 Using index condition; Using where; Using MRR 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Using MRR 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where EXPLAIN SELECT t2.* FROM t1 JOIN t2 ON t2.fk=t1.pk WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using MRR +1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using index condition; Using where; Using MRR 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where DROP TABLE t1,t2; CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a)); @@ -3608,7 +3608,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using MRR +1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where; Using MRR 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where EXPLAIN SELECT t3.a FROM t1,t2,t3 @@ -3616,7 +3616,7 @@ WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND t3.a=t2.a AND t3.c IN ('bb','ee') ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using MRR +1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where; Using MRR 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where EXPLAIN SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 @@ -3624,7 +3624,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using MRR +1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using where; Using MRR 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where EXPLAIN SELECT t3.a FROM t1,t2,t3 @@ -3632,7 +3632,7 @@ WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using MRR +1 SIMPLE t2 range si,ai si 5 NULL 2 Using index condition; Using where; Using MRR 1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where DROP TABLE t1,t2,t3; CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int); diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index efb7a417bb0..f861ac34e87 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1130,7 +1130,7 @@ 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 MRR; LooseScan +1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR; 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 drop table t1, t3, t4; diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index d24c1baa814..a49d2954b5e 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -1135,7 +1135,7 @@ 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 MRR; LooseScan +1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using where; Using MRR; 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 drop table t1, t3, t4; diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result index 6da1e716efa..3abedbc10fe 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -128,7 +128,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3 This should use facts and a1 tables: explain extended select id from v1 where attr1 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition; Using MRR +1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition; Using where; Using MRR 1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #1 @@ -156,7 +156,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3 This should use facts and a1 tables: explain extended select id from v2 where attr1 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition; Using MRR +1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition; Using where; Using MRR 1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #1 @@ -164,7 +164,7 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` whe This should use facts, a2 and its subquery: explain extended select id from v2 where attr2 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using MRR +1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using where; Using MRR 1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using where; Using index 3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.f.id 2 100.00 Using index Warnings: diff --git a/mysql-test/suite/vcol/r/vcol_misc.result.moved b/mysql-test/suite/vcol/r/vcol_misc.result.moved index 57460b1d669..f72373c6d3d 100644 --- a/mysql-test/suite/vcol/r/vcol_misc.result.moved +++ b/mysql-test/suite/vcol/r/vcol_misc.result.moved @@ -13,8 +13,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref idx idx 5 test.t1.b 2 Using where; Using join buffer select * from t1,t2 where t1.b=t2.c and d <= 100; a b c d v -4 20 20 100 101 1 20 20 100 101 3 30 30 100 101 +4 20 20 100 101 set join_cache_level=default; drop table t1, t2; diff --git a/mysql-test/suite/vcol/t/vcol_misc.test.moved b/mysql-test/suite/vcol/t/vcol_misc.test.moved index afe6f838268..5031a23aa49 100644 --- a/mysql-test/suite/vcol/t/vcol_misc.test.moved +++ b/mysql-test/suite/vcol/t/vcol_misc.test.moved @@ -17,7 +17,8 @@ set join_cache_level=6; explain select * from t1,t2 where t1.b=t2.c and d <= 100; +--sorted_result select * from t1,t2 where t1.b=t2.c and d <= 100; set join_cache_level=default; -drop table t1, t2;
\ No newline at end of file +drop table t1, t2; diff --git a/mysql-test/t/innodb_mrr.test b/mysql-test/t/innodb_mrr.test index 0f5b41cef27..f2c7a83e068 100644 --- a/mysql-test/t/innodb_mrr.test +++ b/mysql-test/t/innodb_mrr.test @@ -123,3 +123,34 @@ SELECT id FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; explain SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1; drop table t1; + + +-- echo # +-- echo # BUG#628785: multi_range_read.cc:430: int DsMrr_impl::dsmrr_init(): Assertion `do_sort_keys || do_rowid_fetch' failed +-- echo # +set @save_join_cache_level= @@join_cache_level; +set @save_optimizer_switch= @@optimizer_switch; +SET SESSION join_cache_level=9; +SET SESSION optimizer_switch='mrr_sort_keys=off'; + +CREATE TABLE `t1` ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `col_int_nokey` int(11) DEFAULT NULL, + `col_int_key` int(11) DEFAULT NULL, + `col_varchar_key` varchar(1) DEFAULT NULL, + `col_varchar_nokey` varchar(1) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1; +INSERT INTO `t1` VALUES (1,6,NULL,'r','r'); +INSERT INTO `t1` VALUES (2,8,0,'c','c'); +INSERT INTO `t1` VALUES (97,7,0,'z','z'); +INSERT INTO `t1` VALUES (98,1,1,'j','j'); +INSERT INTO `t1` VALUES (99,7,8,'c','c'); +INSERT INTO `t1` VALUES (100,2,5,'f','f'); +SELECT table1 .`col_varchar_key` +FROM t1 table1 STRAIGHT_JOIN ( t1 table3 JOIN t1 table4 ON table4 .`pk` = table3 .`col_int_nokey` ) ON table4 .`col_varchar_nokey` ; +DROP TABLE t1; +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; + diff --git a/mysql-test/t/innodb_mrr_cpk.test b/mysql-test/t/innodb_mrr_cpk.test new file mode 100644 index 00000000000..69eeef9618f --- /dev/null +++ b/mysql-test/t/innodb_mrr_cpk.test @@ -0,0 +1,137 @@ +# +# Tests for DS-MRR over clustered primary key. The only engine that supports +# this is InnoDB/XtraDB. +# +# Basic idea about testing +# - DS-MRR/CPK works only with BKA +# - Should also test index condition pushdown +# - Should also test whatever uses RANGE_SEQ_IF::skip_record() for filtering +# - Also test access using prefix of primary key +# +# - Forget about cost model, BKA's multi_range_read_info() call passes 10 for +# #rows, the call is there at all only for applicability check +# +-- source include/have_innodb.inc + +--disable_warnings +drop table if exists t0,t1,t2,t3; +--enable_warnings + +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; + +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'); + +--echo This should use join buffer: +explain select * from t1, t2 where t1.a=t2.a; + +--echo This output must be sorted by value of t1.a: +select * from t1, t2 where t1.a=t2.a; +drop table t1, t2; + +# Try multi-column indexes +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; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; + +# Try with dataset that causes identical lookup keys: +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; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; + +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; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; + +# +# Try scanning on a CPK prefix +# +explain select * from t1, t2 where t1.a=t2.a; +select * from t1, t2 where t1.a=t2.a; +drop table t1, t2; + +# +# The above example is not very interesting, as CPK prefix has +# only one match. Create a dataset where scan on CPK prefix +# would produce multiple matches: +# +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; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; + +# Check a real resultset for comaprison: +set join_cache_level=0; +select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; +set join_cache_level=6; + + +# +# Check that Index Condition Pushdown (BKA) actually works: +# +explain select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; +select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; + +set optimizer_switch='index_condition_pushdown=off'; +explain select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; +select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; +set optimizer_switch='index_condition_pushdown=on'; + +drop table t1,t2; + +set @@join_cache_level= @save_join_cache_level; +set storage_engine=@save_storage_engine; +drop table t0; + diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test index 166aab99ccd..1665d1a2032 100644 --- a/mysql-test/t/join_nested.test +++ b/mysql-test/t/join_nested.test @@ -462,7 +462,6 @@ SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b LEFT JOIN (t1,t2) ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b; - SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b FROM (t3,t4) LEFT JOIN diff --git a/mysql-test/t/maria_mrr.test b/mysql-test/t/maria_mrr.test index a4bb8eb9f9e..b8b975ade9f 100644 --- a/mysql-test/t/maria_mrr.test +++ b/mysql-test/t/maria_mrr.test @@ -1,16 +1,51 @@ -- source include/have_maria.inc +# +# MRR/Maria tests. +# --disable_warnings drop table if exists t1,t2,t3,t4; --enable_warnings +set @mrr_buffer_size_save= @@mrr_buffer_size; + set @save_storage_engine= @@storage_engine; set storage_engine=aria; --source include/mrr_tests.inc - set storage_engine= @save_storage_engine; +set @@mrr_buffer_size= @mrr_buffer_size_save; + +--echo # +--echo # Crash in quick_range_seq_next() in maria-5.3-dsmrr-cpk with join_cache_level = {8,1} +--echo # +set @save_join_cache_level= @@join_cache_level; +SET SESSION join_cache_level = 8; +CREATE TABLE `t1` ( + `col_int_key` int(11) DEFAULT NULL, + `col_datetime_key` datetime DEFAULT NULL, + `col_varchar_key` varchar(1) DEFAULT NULL, + `col_varchar_nokey` varchar(1) DEFAULT NULL, + KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; +INSERT INTO `t1` VALUES (6,'2005-10-07 00:00:00','e','e'); +INSERT INTO `t1` VALUES (51,'2000-07-15 05:00:34','f','f'); +CREATE TABLE `t2` ( + `col_int_key` int(11) DEFAULT NULL, + `col_datetime_key` datetime DEFAULT NULL, + `col_varchar_key` varchar(1) DEFAULT NULL, + `col_varchar_nokey` varchar(1) DEFAULT NULL, + KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) +) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; +INSERT INTO `t2` VALUES (2,'2004-10-11 18:13:16','w','w'); +INSERT INTO `t2` VALUES (2,'1900-01-01 00:00:00','d','d'); +SELECT table2 .`col_datetime_key` +FROM t2 JOIN ( t1 table2 JOIN t2 table3 ON table3 .`col_varchar_key` < table2 .`col_varchar_key` ) ON table3 .`col_varchar_nokey` ; + +drop table t1, t2; +set join_cache_level=@save_join_cache_level; + # # Bug #665049: index condition pushdown with Maria # @@ -55,3 +90,4 @@ DROP TABLE t1,t2,t3; + diff --git a/mysql-test/t/maria_mrr.test.moved b/mysql-test/t/maria_mrr.test.moved new file mode 100644 index 00000000000..1b46eb03083 --- /dev/null +++ b/mysql-test/t/maria_mrr.test.moved @@ -0,0 +1,13 @@ +-- source include/have_maria.inc + +--disable_warnings +drop table if exists t1,t2,t3,t4; +--enable_warnings + +set @save_storage_engine= @@storage_engine; +set storage_engine=aria; + +--source include/mrr_tests.inc + +set storage_engine= @save_storage_engine; + diff --git a/mysql-test/t/myisam_mrr.test b/mysql-test/t/myisam_mrr.test index d9afdf3140d..a9433337a10 100644 --- a/mysql-test/t/myisam_mrr.test +++ b/mysql-test/t/myisam_mrr.test @@ -123,4 +123,49 @@ explain select * from t1 where a < 20; set optimizer_switch=@save_optimizer_switch; + +--echo # +--echo # BUG#629684: Unreachable code in multi_range_read.cc in maria-5.3-dsmrr-cpk +--echo # + +delete from t0 where a > 2; +insert into t0 values (NULL),(NULL); +insert into t1 values (NULL, 1234), (NULL, 5678); + +set @save_join_cache_level=@@join_cache_level; +set @@join_cache_level=6; +explain +select * from t0, t1 where t0.a<=>t1.a; +select * from t0, t1 where t0.a<=>t1.a; + +set @@join_cache_level=@save_join_cache_level; drop table t0, t1; + +--echo # +--echo # BUG#625841: Assertion `!table || (!table->read_set || bitmap_is_set +--echo # (table->read_set, field_index))' on REPLACE ... SELECT with MRR +--echo # +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 ( + key1 varchar(10), + col1 char(255), col2 char(255), + col3 char(244), col4 char(255), + key(key1) +); +create table t2 like t1; + +insert into t1 +select + 1000+A.a+100*B.a + 10*C.a, + 'col1val', 'col2val', + 'col3val', 'col4val' +from t0 A, t0 B, t0 C; + +REPLACE INTO t2(col2,col3,col4) +SELECT col2,col3,col4 +FROM t1 +WHERE `key1` LIKE CONCAT( LEFT( '1' , 7 ) , '%' ) +ORDER BY col1 LIMIT 7; +drop table t0, t1, t2; diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test index e73e7cfade2..67452b5a69e 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -586,6 +586,7 @@ if (`select @@join_cache_level=6`) --echo # Not anymore: --echo # The following query gives wrong result due to Bug#49129 } + select * from t0 where t0.a in (select t1.a from t1, t2 where t2.a=t0.a and t1.b=t2.b); diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt index 26957c5ea43..8fc32f1e0d1 100755 --- a/sql/CMakeLists.txt +++ b/sql/CMakeLists.txt @@ -63,6 +63,7 @@ SET (SQL_SOURCE sql_cache.cc sql_class.cc sql_client.cc sql_crypt.cc sql_crypt.h sql_cursor.cc sql_db.cc sql_delete.cc sql_derived.cc sql_do.cc sql_error.cc sql_handler.cc sql_help.cc sql_insert.cc + sql_lifo_buffer.h sql_join_cache.cc sql_lex.cc sql_list.cc sql_load.cc sql_manager.cc sql_map.cc sql_parse.cc sql_partition.cc sql_plugin.cc sql_prepare.cc sql_rename.cc diff --git a/sql/Makefile.am b/sql/Makefile.am index f7a3945311f..53d27300f74 100644 --- a/sql/Makefile.am +++ b/sql/Makefile.am @@ -66,6 +66,7 @@ noinst_HEADERS = item.h item_func.h item_sum.h item_cmpfunc.h \ log.h log_slow.h sql_show.h rpl_rli.h rpl_mi.h \ sql_select.h structs.h table.h sql_udf.h hash_filo.h \ lex.h lex_symbol.h sql_acl.h sql_crypt.h \ + sql_lifo_buffer.h \ sql_repl.h slave.h rpl_filter.h rpl_injector.h \ log_event.h rpl_record.h \ log_event_old.h rpl_record_old.h \ diff --git a/sql/filesort.cc b/sql/filesort.cc index aa808a5e6b5..1ee3972c4fd 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -543,11 +543,6 @@ static ha_rows find_all_keys(SORTPARAM *param, SQL_SELECT *select, current_thd->variables.read_buff_size); } - if (quick_select) - { - if (select->quick->reset()) - DBUG_RETURN(HA_POS_ERROR); - } /* Remember original bitmaps */ save_read_set= sort_form->read_set; @@ -561,9 +556,19 @@ static ha_rows find_all_keys(SORTPARAM *param, SQL_SELECT *select, if (select && select->cond) select->cond->walk(&Item::register_field_in_read_map, 1, (uchar*) sort_form); + if (select && select->pre_idx_push_select_cond) + select->pre_idx_push_select_cond->walk(&Item::register_field_in_read_map, + 1, (uchar*) sort_form); sort_form->column_bitmaps_set(&sort_form->tmp_set, &sort_form->tmp_set, &sort_form->tmp_set); + + if (quick_select) + { + if (select->quick->reset()) + DBUG_RETURN(HA_POS_ERROR); + } + for (;;) { if (quick_select) diff --git a/sql/handler.h b/sql/handler.h index f76580b7082..d05709bbf3d 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -1281,9 +1281,9 @@ void get_sweep_read_cost(TABLE *table, ha_rows nrows, bool interrupted, COST_VECT *cost); /* - The below two are not used (and not handled) in this milestone of this WL - entry because there seems to be no use for them at this stage of - implementation. + Indicates that all scanned ranges will be singlepoint (aka equality) ranges. + The ranges may not use the full key but all of them will use the same number + of key parts. */ #define HA_MRR_SINGLE_POINT 1 #define HA_MRR_FIXED_KEY 2 @@ -1325,6 +1325,16 @@ void get_sweep_read_cost(TABLE *table, ha_rows nrows, bool interrupted, */ #define HA_MRR_NO_NULL_ENDPOINTS 128 +/* + The MRR user has materialized range keys somewhere in the user's buffer. + This can be used for optimization of the procedure that sorts these keys + since in this case key values don't have to be copied into the MRR buffer. + + In other words, it is guaranteed that after RANGE_SEQ_IF::next() call the + pointer in range->start_key.key will point to a key value that will remain + there until the end of the MRR scan. +*/ +#define HA_MRR_MATERIALIZED_KEYS 256 /* @@ -1815,14 +1825,19 @@ public: inline int ha_index_first(uchar * buf); inline int ha_index_last(uchar * buf); inline int ha_index_next_same(uchar *buf, const uchar *key, uint keylen); + /* + TODO: should we make for those functions non-virtual ha_func_name wrappers, + too? + */ virtual ha_rows multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, void *seq_init_param, uint n_ranges, uint *bufsz, uint *flags, COST_VECT *cost); virtual ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys, - uint *bufsz, uint *flags, COST_VECT *cost); + uint key_parts, uint *bufsz, + uint *flags, COST_VECT *cost); virtual int multi_range_read_init(RANGE_SEQ_IF *seq, void *seq_init_param, - uint n_ranges, uint mode, + uint n_ranges, uint mode, HANDLER_BUFFER *buf); virtual int multi_range_read_next(char **range_info); virtual int read_range_first(const key_range *start_key, @@ -2181,7 +2196,8 @@ public: TRUE if the engine supports virtual columns */ virtual bool check_if_supported_virtual_columns(void) { return FALSE;} - + + TABLE* get_table() { return table; } protected: /* deprecated, don't use in new engines */ inline void ha_statistic_increment(ulong SSV::*offset) const { } @@ -2374,7 +2390,6 @@ private: virtual int rename_partitions(const char *path) { return HA_ERR_WRONG_COMMAND; } friend class ha_partition; - friend class DsMrr_impl; public: /* XXX to be removed, see ha_partition::partition_ht() */ virtual handlerton *partition_ht() const diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc index 53699c47688..f577d4598e3 100644 --- a/sql/multi_range_read.cc +++ b/sql/multi_range_read.cc @@ -1,4 +1,5 @@ #include "mysql_priv.h" +#include <my_bit.h> #include "sql_select.h" /**************************************************************************** @@ -136,10 +137,16 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, */ ha_rows handler::multi_range_read_info(uint keyno, uint n_ranges, uint n_rows, - uint *bufsz, uint *flags, COST_VECT *cost) + uint key_parts, uint *bufsz, + uint *flags, COST_VECT *cost) { - *bufsz= 0; /* Default implementation doesn't need a buffer */ + /* + Currently we expect this function to be called only in preparation of scan + with HA_MRR_SINGLE_POINT property. + */ + DBUG_ASSERT(*flags | HA_MRR_SINGLE_POINT); + *bufsz= 0; /* Default implementation doesn't need a buffer */ *flags |= HA_MRR_USE_DEFAULT_IMPL; cost->zero(); @@ -207,7 +214,6 @@ handler::multi_range_read_init(RANGE_SEQ_IF *seq_funcs, void *seq_init_param, DBUG_RETURN(0); } - /** Get next record in MRR scan @@ -223,7 +229,7 @@ handler::multi_range_read_init(RANGE_SEQ_IF *seq_funcs, void *seq_init_param, int handler::multi_range_read_next(char **range_info) { - int UNINIT_VAR(result); + int result= HA_ERR_END_OF_FILE; int range_res; DBUG_ENTER("handler::multi_range_read_next"); @@ -276,6 +282,452 @@ scan_it_again: DBUG_RETURN(result); } + +/***** MRR_impl classes ****************************************************/ + +int Mrr_simple_index_reader::get_next(char **range_info) +{ + while (!(res= h->handler::multi_range_read_next(range_info))) + { + KEY_MULTI_RANGE *curr_range= &h->handler::mrr_cur_range; + if (!h->mrr_funcs.skip_index_tuple || + !h->mrr_funcs.skip_index_tuple(h->mrr_iter, curr_range->ptr)) + break; + } + return res; +} + +int Mrr_simple_index_reader::init(handler *h_arg, RANGE_SEQ_IF *seq_funcs, + void *seq_init_param, uint n_ranges, + uint mode, Buffer_manager *buf_manager_arg) +{ + HANDLER_BUFFER no_buffer = {NULL, NULL, NULL}; + h= h_arg; + res= 0; + return h->handler::multi_range_read_init(seq_funcs, seq_init_param, n_ranges, + mode, &no_buffer); +} + +/** + DS-MRR/CPK: multi_range_read_next() function + + @param range_info OUT identifier of range that the returned record belongs to + + @note + This function walks over key buffer and does index reads, i.e. it produces + {current_record, range_id} pairs. + + The function has the same call contract like multi_range_read_next()'s. + + We actually iterate over nested sequences: + - a disjoint sequence of index ranges + - each range has multiple records + - each record goes into multiple identical ranges. + + @retval 0 OK, next record was successfully read + @retval HA_ERR_END_OF_FILE End of records + @retval Other Some other error +*/ + +int Mrr_ordered_index_reader::get_next(char **range_info_arg) +{ + DBUG_ENTER("Mrr_ordered_index_reader::get_next"); + + if (!know_key_tuple_params) /* We're in startup phase */ + DBUG_RETURN(HA_ERR_END_OF_FILE); + + while (1) + { + bool have_record= FALSE; + if (scanning_key_val_iter) + { + if (kv_it.get_next()) + { + kv_it.close(); + scanning_key_val_iter= FALSE; + } + else + have_record= TRUE; + } + else + { + while (kv_it.init(this)) + { + if (key_buffer->is_empty()) + { + /*if (auto_refill) + { + int res; + if ((res= refill_buffer())) + DBUG_RETURN(res); + if (key_buffer->is_empty()) + { + index_scan_eof= TRUE; + DBUG_RETURN(HA_ERR_END_OF_FILE); + } + } + else + */ + { + /* Buffer refills are managed by somebody else for us */ + index_scan_eof= TRUE; + DBUG_RETURN(HA_ERR_END_OF_FILE); + } + } + } + scanning_key_val_iter= TRUE; + } + + if (have_record && + (!mrr_funcs.skip_index_tuple || + !mrr_funcs.skip_index_tuple(mrr_iter, *(char**)cur_range_info)) + && + (!mrr_funcs.skip_record || + !mrr_funcs.skip_record(mrr_iter, *(char**)cur_range_info, NULL))) + { + break; + } + /* Go get another (record, range_id) combination */ + } /* while */ + + memcpy(range_info_arg, cur_range_info, sizeof(void*)); + DBUG_RETURN(0); +} + + +/** + DS-MRR/CPK: Fill the buffer with (lookup_tuple, range_id) pairs and sort + + Enumerate the input range (=key) sequence, fill the key buffer with + (lookup_key, range_id) pairs and sort it. + + When this function returns, either + - key buffer is non-empty, or + - key buffer is empty and source range sequence is exhausted + + @note + dsmrr_eof is set to indicate whether we've exhausted the list of ranges + we're scanning. +*/ + +int Mrr_ordered_index_reader::refill_buffer() +{ + int res; + KEY_MULTI_RANGE cur_range; + uchar **range_info_ptr= (uchar**)&cur_range.ptr; + uchar *key_ptr; + DBUG_ENTER("Mrr_ordered_index_reader::refill_buffer"); + + DBUG_ASSERT(!know_key_tuple_params || key_buffer->is_empty()); + if (know_key_tuple_params) + { + buf_manager->reset_buffer_sizes(); + key_buffer->reset(); + key_buffer->setup_writing(&key_ptr, keypar.key_size_in_keybuf, + is_mrr_assoc? (uchar**)&range_info_ptr : NULL, + sizeof(uchar*)); + } +#if 0 + + if (know_key_tuple_params) + { + if (do_rndpos_scan && rowid_buffer.is_empty()) + { + /* + We're using two buffers and both of them are empty now. Restore the + original sizes + */ + rowid_buffer.set_buffer_space(full_buf, rowid_buffer_end); + key_buffer= &backward_key_buf; + key_buffer->set_buffer_space(rowid_buffer_end, full_buf_end); + } + } + is all of the ifdef-ed stuff is handled above? +#endif + while ((!know_key_tuple_params || key_buffer->can_write()) && + !(res= mrr_funcs.next(mrr_iter, &cur_range))) + { + DBUG_ASSERT(cur_range.range_flag & EQ_RANGE); + + if (!know_key_tuple_params) + { + /* This only happens when we've just started filling the buffer */ + key_range *sample_key= &cur_range.start_key; + know_key_tuple_params= TRUE; + keypar.key_tuple_length= sample_key->length; + keypar.key_tuple_map= sample_key->keypart_map; + keypar.key_size_in_keybuf= keypar.use_key_pointers ? sizeof(char*) : keypar.key_tuple_length; + KEY *key_info= &h->get_table()->key_info[h->active_index]; + keypar.index_ranges_unique= test(key_info->flags & HA_NOSAME && + key_info->key_parts == + my_count_bits(sample_key->keypart_map)); + buf_manager->setup_buffer_sizes(keypar.key_size_in_keybuf, keypar.key_tuple_map); + key_buffer= buf_manager->get_key_buffer(); + key_buffer->setup_writing(&key_ptr, keypar.key_size_in_keybuf, + is_mrr_assoc? (uchar**)&range_info_ptr : NULL, + sizeof(uchar*)); + DBUG_ASSERT(key_buffer->can_write()); + } + + /* Put key, or {key, range_id} pair into the buffer */ + if (keypar.use_key_pointers) + key_ptr=(uchar*) &cur_range.start_key.key; + else + key_ptr=(uchar*) cur_range.start_key.key; + + key_buffer->write(); + } + + no_more_keys= test(res); + + key_buffer->sort((key_buffer->type() == Lifo_buffer::FORWARD)? + (qsort2_cmp)Mrr_ordered_index_reader::key_tuple_cmp_reverse : + (qsort2_cmp)Mrr_ordered_index_reader::key_tuple_cmp, + (void*)this); + + scanning_key_val_iter= FALSE; + index_scan_eof= FALSE; + + DBUG_RETURN((no_more_keys && key_buffer->is_empty())? HA_ERR_END_OF_FILE:0); +} + + +int Mrr_ordered_index_reader::init(handler *h_arg, RANGE_SEQ_IF *seq_funcs, + void *seq_init_param, uint n_ranges, + uint mode, Buffer_manager *buf_manager_arg) +{ + h= h_arg; + mrr_iter= seq_funcs->init(seq_init_param, n_ranges, mode); + keypar.use_key_pointers= test(mode & HA_MRR_MATERIALIZED_KEYS); + is_mrr_assoc= !test(mode & HA_MRR_NO_ASSOCIATION); + mrr_funcs= *seq_funcs; + know_key_tuple_params= FALSE; + buf_manager= buf_manager_arg; + return 0; +} + + +static int rowid_cmp_reverse(void *h, uchar *a, uchar *b) +{ + return - ((handler*)h)->cmp_ref(a, b); +} + + +int Mrr_ordered_rndpos_reader::init(handler *h_arg, + Mrr_index_reader *index_reader_arg, + uint mode, + Lifo_buffer *buf) +{ + h= h_arg; + index_reader= index_reader_arg; + rowid_buffer= buf; + is_mrr_assoc= !test(mode & HA_MRR_NO_ASSOCIATION); + //rowid_buff_elem_size= h->ref_length; + //if (!(mode & HA_MRR_NO_ASSOCIATION)) + // rowid_buff_elem_size += sizeof(char*); + + index_reader_exhausted= FALSE; + ///int res= index_reader->refill_buffer(); + ///if (res && res!=HA_ERR_END_OF_FILE) + /// return res; + return 0; +} + + +/** + DS-MRR: Fill and sort the rowid buffer + + Scan the MRR ranges and collect ROWIDs (or {ROWID, range_id} pairs) into + buffer. When the buffer is full or scan is completed, sort the buffer by + rowid and return. + + When this function returns, either rowid buffer is not empty, or the source + of lookup keys (i.e. ranges) is exhaused. + + dsmrr_eof is set to indicate whether we've exhausted the list of ranges we're + scanning. This function never returns HA_ERR_END_OF_FILE. + + @retval 0 OK, the next portion of rowids is in the buffer, + properly ordered + @retval other Error +*/ + + +int Mrr_ordered_rndpos_reader::refill_buffer() +{ + int res; + DBUG_ENTER("Mrr_ordered_rndpos_reader::refill_buffer"); + + if (index_reader_exhausted) + DBUG_RETURN(HA_ERR_END_OF_FILE); + + while ((res= refill2() == HA_ERR_END_OF_FILE)) + { + if ((res= index_reader->refill_buffer())) + { + if (res == HA_ERR_END_OF_FILE) + index_reader_exhausted= TRUE; + break; + } + } + DBUG_RETURN(res); +} + + +/* This one refills without calling index_reader->refill_buffer(). */ +int Mrr_ordered_rndpos_reader::refill2() +{ + char *range_info; + uchar **range_info_ptr= (uchar**)&range_info; + int res; + DBUG_ENTER("Mrr_ordered_rndpos_reader::refill2"); + + DBUG_ASSERT(rowid_buffer->is_empty()); + index_rowid= index_reader->get_rowid_ptr(); + rowid_buffer->reset(); + rowid_buffer->setup_writing(&index_rowid, h->ref_length, + is_mrr_assoc? (uchar**)&range_info_ptr: NULL, + sizeof(void*)); + + last_identical_rowid= NULL; + + while (rowid_buffer->can_write()) + { + res= index_reader->get_next(&range_info); + + if (res) + break; + + /* Put rowid, or {rowid, range_id} pair into the buffer */ + index_reader->h->position(index_reader->h->get_table()->record[0]); + + rowid_buffer->write(); + } + + /* Sort the buffer contents by rowid */ + rowid_buffer->sort((qsort2_cmp)rowid_cmp_reverse, (void*)h); + + rowid_buffer->setup_reading(&rowid, h->ref_length, + is_mrr_assoc? (uchar**)&rowids_range_id: NULL, + sizeof(void*)); + DBUG_RETURN(rowid_buffer->is_empty()? HA_ERR_END_OF_FILE : 0); +} + + +/** + DS-MRR implementation: multi_range_read_next() function. + + Calling convention is like multi_range_read_next() has. +*/ + +int Mrr_ordered_rndpos_reader::get_next(char **range_info) +{ + int res; + + while (last_identical_rowid) + { + /* + Current record (the one we've returned in previous call) was obtained + from a rowid that matched multiple range_ids. Return this record again, + with next matching range_id. + */ + bool UNINIT_VAR(bres); + bres= rowid_buffer->read(); + DBUG_ASSERT(!bres); + + if (is_mrr_assoc) + memcpy(range_info, rowids_range_id, sizeof(uchar*)); + + if (rowid == last_identical_rowid) + { + last_identical_rowid= NULL; /* reached the last of identical rowids */ + } + + if (!index_reader->skip_record((char*)*range_info, rowid)) + { + return 0; + } + } + + while (1) + { +#if 0 + if (rowid_buffer->is_empty()) /* We're out of rowids */ + { + /* First, finish off the sorted keys we have */ + if (!index_reader->eof()) + { + res= refill_buffer(); + if (res && res != HA_ERR_END_OF_FILE) + return res; + } + + if (rowid_buffer->is_empty()) + { + /* + Ok neither index_reader nor us have any records. Refill index + reader, then refill us. + */ + // TODO: if key buffer is empty, too, redistribute the buffer space. + if ((res= index_reader->refill_buffer()) || + (res= refill_buffer())) + return res; + } + } +#endif + + last_identical_rowid= NULL; + + /* Return eof if there are no rowids in the buffer after re-fill attempt */ + if (rowid_buffer->read()) + return HA_ERR_END_OF_FILE; + + if (is_mrr_assoc) + { + memcpy(range_info, rowids_range_id, sizeof(uchar*)); + } + + if (index_reader->skip_record(*range_info, rowid)) + continue; + + res= h->ha_rnd_pos(h->get_table()->record[0], rowid); + + if (res == HA_ERR_RECORD_DELETED) + continue; + + /* + Check if subsequent buffer elements have the same rowid value as this + one. If yes, remember this fact so that we don't make any more rnd_pos() + calls with this value. + */ + if (!res) + { + uchar *cur_rowid= rowid; + /* + Note: this implies that SQL layer doesn't touch table->record[0] + between calls. + */ + Lifo_buffer_iterator it; + it.init(rowid_buffer); + while (!it.read()) // reads to (rowid, ...) + { + if (h->cmp_ref(rowid, cur_rowid)) + break; + last_identical_rowid= rowid; + } + } + return 0; + } + + return res; +} + + + + +/************ MRR_impl classes end *********************************************/ + + /**************************************************************************** * DS-MRR implementation ***************************************************************************/ @@ -302,9 +754,8 @@ int DsMrr_impl::dsmrr_init(handler *h_arg, RANGE_SEQ_IF *seq_funcs, void *seq_init_param, uint n_ranges, uint mode, HANDLER_BUFFER *buf) { - uint elem_size; - Item *pushed_cond= NULL; - handler *new_h2= 0; + THD *thd= current_thd; + int res; DBUG_ENTER("DsMrr_impl::dsmrr_init"); /* @@ -312,50 +763,140 @@ int DsMrr_impl::dsmrr_init(handler *h_arg, RANGE_SEQ_IF *seq_funcs, has not been called, so set the owner handler here as well. */ h= h_arg; - if (mode & HA_MRR_USE_DEFAULT_IMPL || mode & HA_MRR_SORTED) + is_mrr_assoc= !test(mode & HA_MRR_NO_ASSOCIATION); + + if ((mode & HA_MRR_USE_DEFAULT_IMPL) || (mode & HA_MRR_SORTED)) + { + DBUG_ASSERT(h->inited == handler::INDEX); + Mrr_simple_index_reader *s= &strategy_factory.simple_index_reader; + res= s->init(h, seq_funcs, seq_init_param, n_ranges, mode, this); + strategy= s; + DBUG_RETURN(res); + } + + /* Neither of strategies used below can handle sorting */ + DBUG_ASSERT(!(mode & HA_MRR_SORTED)); + + /* + Determine whether we'll need to do key sorting and/or rnd_pos() scan + */ + index_strategy= NULL; + Mrr_ordered_index_reader *ordered_idx_reader= NULL; + if ((mode & HA_MRR_SINGLE_POINT) && + optimizer_flag(thd, OPTIMIZER_SWITCH_MRR_SORT_KEYS)) { - use_default_impl= TRUE; - const int retval= - h->handler::multi_range_read_init(seq_funcs, seq_init_param, - n_ranges, mode, buf); - DBUG_RETURN(retval); + index_strategy= ordered_idx_reader= &strategy_factory.ordered_index_reader; } - rowids_buf= buf->buffer; + else + index_strategy= &strategy_factory.simple_index_reader; + + strategy= index_strategy; + /* + We don't need a rowid-to-rndpos step if + - We're doing a scan on clustered primary key + - [In the future] We're doing an index_only read + */ + DBUG_ASSERT(h->inited == handler::INDEX || + (h->inited == handler::RND && h2 && + h2->inited == handler::INDEX)); - is_mrr_assoc= !test(mode & HA_MRR_NO_ASSOCIATION); + handler *h_idx= (h->inited == handler::INDEX)? h: h2; + keyno= h_idx->active_index; + + Mrr_ordered_rndpos_reader *disk_strategy= NULL; + if (!(keyno == table->s->primary_key && h_idx->primary_key_is_clustered())) + { + strategy= disk_strategy= &strategy_factory.ordered_rndpos_reader; + } if (is_mrr_assoc) - status_var_increment(table->in_use->status_var.ha_multi_range_read_init_count); - - rowids_buf_end= buf->buffer_end; - elem_size= h->ref_length + (int)is_mrr_assoc * sizeof(void*); - rowids_buf_last= rowids_buf + - ((rowids_buf_end - rowids_buf)/ elem_size)* - elem_size; - rowids_buf_end= rowids_buf_last; + status_var_increment(thd->status_var.ha_multi_range_read_init_count); + full_buf= buf->buffer; + full_buf_end= buf->buffer_end; + + if (strategy == index_strategy) + { + ///if (ordered_idx_reader) + // ordered_idx_reader->auto_refill= TRUE; + /* Index strategy serves it all. We don't need two handlers, etc */ + /* Give the buffer to index strategy */ + if ((res= index_strategy->init(h, seq_funcs, seq_init_param, n_ranges, + mode, this))) + goto error; + } + else + { /* - There can be two cases: - - This is the first call since index_init(), h2==NULL - Need to setup h2 then. - - This is not the first call, h2 is initalized and set up appropriately. - The caller might have called h->index_init(), need to switch h to - rnd_pos calls. + If we got here the request is served by both index and rndpos strategies + working together. + + */ + rowid_buffer.set_buffer_space(buf->buffer, buf->buffer_end); + + if ((res= setup_two_handlers())) + DBUG_RETURN(res); + + ///if (ordered_idx_reader) + /// ordered_idx_reader->auto_refill= FALSE; + + if ((res= index_strategy->init(h2, seq_funcs, seq_init_param, n_ranges, + mode, this)) || + (res= disk_strategy->init(h, index_strategy, mode, &rowid_buffer))) + { + goto error; + } + } + + res= strategy->refill_buffer(); + if (res && res != HA_ERR_END_OF_FILE) //psergey-todo: remove EOF check here + goto error; + + /* + If we have scanned through all intervals in *seq, then adjust *buf to + indicate that the remaining buffer space will not be used. */ +// if (dsmrr_eof) +// buf->end_of_used_area= rowid_buffer.end_of_space(); + + + DBUG_RETURN(0); +error: + close_second_handler(); + strategy= NULL; + DBUG_RETURN(1); +} + + +/* + Whatever the current state is, make it so that we have two handler objects: + - h (the primary) - initialized for rnd_pos() scan + - h2 (the secondary) - initialized for scanning the index specified in + this->keyno + RETURN + 0 OK + HA_XXX Error code +*/ + +int DsMrr_impl::setup_two_handlers() +{ + int res; + THD *thd= current_thd; + DBUG_ENTER("DsMrr_impl::setup_two_handlers"); if (!h2) { - /* Create a separate handler object to do rndpos() calls. */ - THD *thd= current_thd; + handler *new_h2; + Item *pushed_cond= NULL; + DBUG_ASSERT(h->inited == handler::INDEX); + /* Create a separate handler object to do rnd_pos() calls. */ /* ::clone() takes up a lot of stack, especially on 64 bit platforms. The constant 5 is an empiric result. */ if (check_stack_overrun(thd, 5*STACK_MIN_SIZE, (uchar*) &new_h2)) DBUG_RETURN(1); - DBUG_ASSERT(h->active_index != MAX_KEY); - uint mrr_keyno= h->active_index; - /* Create a separate handler object to do rndpos() calls. */ + /* Create a separate handler object to do rnd_pos() calls. */ if (!(new_h2= h->clone(thd->mem_root)) || new_h2->ha_external_lock(thd, F_RDLCK)) { @@ -363,88 +904,69 @@ int DsMrr_impl::dsmrr_init(handler *h_arg, RANGE_SEQ_IF *seq_funcs, DBUG_RETURN(1); } - if (mrr_keyno == h->pushed_idx_cond_keyno) + if (keyno == h->pushed_idx_cond_keyno) pushed_cond= h->pushed_idx_cond; - + + Mrr_reader *save_strategy= strategy; + strategy= NULL; /* Caution: this call will invoke this->dsmrr_close(). Do not put the - created secondary table handler into this->h2 or it will delete it. + created secondary table handler new_h2 into this->h2 or it will delete + it. Also, save the picked strategy */ - if (h->ha_index_end()) - { - h2=new_h2; - goto error; - } + res= h->ha_index_end(); + strategy= save_strategy; h2= new_h2; /* Ok, now can put it into h2 */ + + if (res || (res= (h->ha_rnd_init(FALSE)))) + goto error; + table->prepare_for_position(); h2->extra(HA_EXTRA_KEYREAD); - - if (h2->ha_index_init(mrr_keyno, FALSE)) + h2->mrr_iter= h->mrr_iter; + + if ((res= h2->ha_index_init(keyno, FALSE))) goto error; - use_default_impl= FALSE; if (pushed_cond) - h2->idx_cond_push(mrr_keyno, pushed_cond); + h2->idx_cond_push(keyno, pushed_cond); } else { + DBUG_ASSERT(h2 && h2->inited==handler::INDEX); /* We get here when the access alternates betwen MRR scan(s) and non-MRR scans. Calling h->index_end() will invoke dsmrr_close() for this object, - which will delete h2. We need to keep it, so save put it away and dont + which will delete h2. We need to keep it, so put it away and dont let it be deleted: */ - handler *save_h2= h2; - h2= NULL; - int res= (h->inited == handler::INDEX && h->ha_index_end()); - h2= save_h2; - use_default_impl= FALSE; - if (res) + if (h->inited == handler::INDEX) + { + handler *save_h2= h2; + Mrr_reader *save_strategy= strategy; + h2= NULL; + strategy= NULL; + res= h->ha_index_end(); + h2= save_h2; + strategy= save_strategy; + if (res) + goto error; + } + if ((h->inited != handler::RND) && h->ha_rnd_init(FALSE)) goto error; } - - if (h2->handler::multi_range_read_init(seq_funcs, seq_init_param, n_ranges, - mode, buf) || - dsmrr_fill_buffer()) - { - goto error; - } - /* - If the above call has scanned through all intervals in *seq, then - adjust *buf to indicate that the remaining buffer space will not be used. - */ - if (dsmrr_eof) - buf->end_of_used_area= rowids_buf_last; - - /* - h->inited == INDEX may occur when 'range checked for each record' is - used. - */ - if ((h->inited != handler::RND) && - ((h->inited==handler::INDEX? h->ha_index_end(): FALSE) || - (h->ha_rnd_init(FALSE)))) - goto error; - - use_default_impl= FALSE; - h->mrr_funcs= *seq_funcs; - DBUG_RETURN(0); error: - h2->ha_index_or_rnd_end(); - h2->ha_external_lock(current_thd, F_UNLCK); - h2->close(); - delete h2; - h2= NULL; - DBUG_RETURN(1); + //close_second_handler(); -- caller does that + DBUG_RETURN(res); } -void DsMrr_impl::dsmrr_close() +void DsMrr_impl::close_second_handler() { - DBUG_ENTER("DsMrr_impl::dsmrr_close"); if (h2) { h2->ha_index_or_rnd_end(); @@ -453,136 +975,284 @@ void DsMrr_impl::dsmrr_close() delete h2; h2= NULL; } - use_default_impl= TRUE; - DBUG_VOID_RETURN; } -static int rowid_cmp(void *h, uchar *a, uchar *b) +void DsMrr_impl::dsmrr_close() { - return ((handler*)h)->cmp_ref(a, b); + DBUG_ENTER("DsMrr_impl::dsmrr_close"); + close_second_handler(); + strategy= NULL; + DBUG_VOID_RETURN; } -/** - DS-MRR: Fill the buffer with rowids and sort it by rowid - - {This is an internal function of DiskSweep MRR implementation} - Scan the MRR ranges and collect ROWIDs (or {ROWID, range_id} pairs) into - buffer. When the buffer is full or scan is completed, sort the buffer by - rowid and return. - - The function assumes that rowids buffer is empty when it is invoked. - - @param h Table handler - - @retval 0 OK, the next portion of rowids is in the buffer, - properly ordered - @retval other Error +/* + my_qsort2-compatible function to compare key tuples */ -int DsMrr_impl::dsmrr_fill_buffer() +int Mrr_ordered_index_reader::key_tuple_cmp(void* arg, uchar* key1, uchar* key2) { - char *range_info; + Mrr_ordered_index_reader *this_= (Mrr_ordered_index_reader*)arg; + TABLE *table= this_->h->get_table(); int res; - DBUG_ENTER("DsMrr_impl::dsmrr_fill_buffer"); - - rowids_buf_cur= rowids_buf; - while ((rowids_buf_cur < rowids_buf_end) && - !(res= h2->handler::multi_range_read_next(&range_info))) + KEY_PART_INFO *part= table->key_info[this_->h->active_index].key_part; + + if (this_->keypar.use_key_pointers) { - KEY_MULTI_RANGE *curr_range= &h2->handler::mrr_cur_range; - if (h2->mrr_funcs.skip_index_tuple && - h2->mrr_funcs.skip_index_tuple(h2->mrr_iter, curr_range->ptr)) - continue; - - /* Put rowid, or {rowid, range_id} pair into the buffer */ - h2->position(table->record[0]); - memcpy(rowids_buf_cur, h2->ref, h2->ref_length); - rowids_buf_cur += h2->ref_length; + /* the buffer stores pointers to keys, get to the keys */ + key1= *((uchar**)key1); + key2= *((uchar**)key2); // todo is this alignment-safe? + } - if (is_mrr_assoc) + uchar *key1_end= key1 + this_->keypar.key_tuple_length; + + while (key1 < key1_end) + { + Field* f = part->field; + int len = part->store_length; + if (part->null_bit) { - memcpy(rowids_buf_cur, &range_info, sizeof(void*)); - rowids_buf_cur += sizeof(void*); + if (*key1) // key1 == NULL + { + if (!*key2) // key1(NULL) < key2(notNULL) + return -1; + goto equals; + } + else if (*key2) // key1(notNULL) > key2 (NULL) + return 1; + // Step over NULL byte for f->cmp(). + key1++; + key2++; + len--; } + + if ((res= f->key_cmp(key1, key2))) + return res; +equals: + key1 += len; + key2 += len; + part++; + } + return 0; +} + + +int Mrr_ordered_index_reader::key_tuple_cmp_reverse(void* arg, uchar* key1, uchar* key2) +{ + return -key_tuple_cmp(arg, key1, key2); +} + + +/** + Setup key/rowid buffer sizes based on sample_key and its length. + + @param + sample_key A lookup key to use as a sample. It is assumed that + all other keys will have the same length/etc. + @note + This function must be called when all buffers are empty +*/ + +void DsMrr_impl::setup_buffer_sizes(uint key_size_in_keybuf, + key_part_map key_tuple_map) +{ + uint key_buff_elem_size= key_size_in_keybuf + + (int)is_mrr_assoc * sizeof(void*); + + KEY *key_info= &h->get_table()->key_info[keyno]; + if (strategy == index_strategy) + { + /* Give all space to the key buffer, key buffer must be forward */ + key_buffer= &forward_key_buf; + key_buffer->set_buffer_space(full_buf, full_buf_end); + + /* Just in case, tell rowid buffer that it has zero size: */ + rowid_buffer.set_buffer_space(full_buf_end, full_buf_end); + return; + } + + /* + Ok if we got here we need to allocate one part of the buffer + for keys and another part for rowids. + */ + uint rowid_buf_elem_size= h->ref_length + + (int)is_mrr_assoc * sizeof(char*); + + /* + Use rec_per_key statistics as a basis to find out how many rowids + we'll get for each key value. + TODO: are we guaranteed to get r_p_c==1 for unique keys? + TODO: what should be the default value to use when there is no + statistics? + */ + uint parts= my_count_bits(key_tuple_map); + ulong rpc; + if ((rpc= key_info->rec_per_key[parts - 1])) + { + rowid_buf_elem_size *= rpc; } - if (res && res != HA_ERR_END_OF_FILE) - DBUG_RETURN(res); - dsmrr_eof= test(res == HA_ERR_END_OF_FILE); + double fraction_for_rowids= + ((double) rowid_buf_elem_size / + ((double)rowid_buf_elem_size + key_buff_elem_size)); - /* Sort the buffer contents by rowid */ - uint elem_size= h->ref_length + (int)is_mrr_assoc * sizeof(void*); - uint n_rowids= (rowids_buf_cur - rowids_buf) / elem_size; + size_t bytes_for_rowids= + round(fraction_for_rowids * (full_buf_end - full_buf)); - my_qsort2(rowids_buf, n_rowids, elem_size, (qsort2_cmp)rowid_cmp, - (void*)h); - rowids_buf_last= rowids_buf_cur; - rowids_buf_cur= rowids_buf; - DBUG_RETURN(0); + uint bytes_for_keys= (full_buf_end - full_buf) - bytes_for_rowids; + + if (bytes_for_keys < key_buff_elem_size + 1) + { + uint add= key_buff_elem_size + 1 - bytes_for_keys; + bytes_for_rowids -= add; + DBUG_ASSERT(bytes_for_rowids >= + (h->ref_length + (int)is_mrr_assoc * sizeof(char*) + 1)); + } + + rowid_buffer_end= full_buf + bytes_for_rowids; + rowid_buffer.set_buffer_space(full_buf, rowid_buffer_end); + key_buffer= &backward_key_buf; + key_buffer->set_buffer_space(rowid_buffer_end, full_buf_end); } +void DsMrr_impl::reset_buffer_sizes() +{ + if (strategy != index_strategy) + { + /* + Ok we have both ordered index reader and there is a disk rearder. + Redistribute the buffer space. + */ + rowid_buffer.set_buffer_space(full_buf, rowid_buffer_end); + key_buffer= &backward_key_buf; + key_buffer->set_buffer_space(rowid_buffer_end, full_buf_end); + } +} + /** - DS-MRR implementation: multi_range_read_next() function + Take unused space from the key buffer and give it to the rowid buffer */ +//psergey-todo: do invoke this function. +void DsMrr_impl::reallocate_buffer_space() +{ + uchar *unused_start, *unused_end; + key_buffer->remove_unused_space(&unused_start, &unused_end); + rowid_buffer.grow(unused_start, unused_end); +} -int DsMrr_impl::dsmrr_next(char **range_info) + +////////////////////////////////////////////////////////////////////////////// +////////////////////////////////////////////////////////////////////////////// + +bool Key_value_records_iterator::init(Mrr_ordered_index_reader *owner_arg) { int res; - uchar *cur_range_info= 0; - uchar *rowid; + //h= h_arg; + //param= param_arg; + owner= owner_arg; + + identical_key_it.init(owner->key_buffer); + /* Get the first pair into (cur_index_tuple, cur_range_info) */ + owner->key_buffer->setup_reading(&cur_index_tuple, owner->keypar.key_size_in_keybuf, + owner->is_mrr_assoc? (uchar**)&owner->cur_range_info: NULL, + sizeof(void*)); + + if (identical_key_it.read()) + return TRUE; - if (use_default_impl) - return h->handler::multi_range_read_next(range_info); + uchar *key_in_buf= cur_index_tuple; + + last_identical_key_ptr= cur_index_tuple; + if (owner->keypar.use_key_pointers) + cur_index_tuple= *((uchar**)cur_index_tuple); - do + /* Check out how many more identical keys are following */ + uchar *save_cur_index_tuple= cur_index_tuple; + while (!identical_key_it.read()) { - if (rowids_buf_cur == rowids_buf_last) - { - if (dsmrr_eof) - { - res= HA_ERR_END_OF_FILE; - goto end; - } - res= dsmrr_fill_buffer(); - if (res) - goto end; - } - - /* return eof if there are no rowids in the buffer after re-fill attempt */ - if (rowids_buf_cur == rowids_buf_last) + if (Mrr_ordered_index_reader::key_tuple_cmp(owner, key_in_buf, cur_index_tuple)) + break; + last_identical_key_ptr= cur_index_tuple; + } + identical_key_it.init(owner->key_buffer); + cur_index_tuple= save_cur_index_tuple; + res= owner->h->ha_index_read_map(owner->h->get_table()->record[0], + cur_index_tuple, + owner->keypar.key_tuple_map, + HA_READ_KEY_EXACT); + + if (res) + { + close(); + return res; /* Fatal error */ + } + get_next_row= FALSE; + return 0; +} + + +int Key_value_records_iterator::get_next() +{ + int res; + + if (get_next_row) + { + if (owner->keypar.index_ranges_unique) + return HA_ERR_END_OF_FILE; /* Max one match */ + + handler *h= owner->h; + if ((res= h->ha_index_next_same(h->get_table()->record[0], + cur_index_tuple, + owner->keypar.key_tuple_length))) { - res= HA_ERR_END_OF_FILE; - goto end; + /* EOF is EOF for iterator, also, any error means EOF on the iterator */ + return res; } - rowid= rowids_buf_cur; + identical_key_it.init(owner->key_buffer); + get_next_row= FALSE; + } - if (is_mrr_assoc) - memcpy(&cur_range_info, rowids_buf_cur + h->ref_length, sizeof(uchar**)); + identical_key_it.read(); // This gets us next range_id. + if (!last_identical_key_ptr || (cur_index_tuple == last_identical_key_ptr)) + { + get_next_row= TRUE; + } + return 0; +} - rowids_buf_cur += h->ref_length + sizeof(void*) * test(is_mrr_assoc); - if (h2->mrr_funcs.skip_record && - h2->mrr_funcs.skip_record(h2->mrr_iter, (char *) cur_range_info, rowid)) - continue; - res= h->ha_rnd_pos(table->record[0], rowid); - break; - } while (true); - - if (is_mrr_assoc) +void Key_value_records_iterator::close() +{ + while (!owner->key_buffer->read() && + (cur_index_tuple != last_identical_key_ptr)) {} +} + + +/** + DS-MRR implementation: multi_range_read_next() function. + + Calling convention is like multi_range_read_next() has. +*/ + +int DsMrr_impl::dsmrr_next(char **range_info) +{ + int res; + while ((res= strategy->get_next(range_info)) == HA_ERR_END_OF_FILE) { - memcpy(range_info, rowid + h->ref_length, sizeof(void*)); + if ((res= strategy->refill_buffer())) + break; /* EOF or error */ } -end: return res; + //return strategy->get_next(range_info); } /** DS-MRR implementation: multi_range_read_info() function */ -ha_rows DsMrr_impl::dsmrr_info(uint keyno, uint n_ranges, uint rows, +ha_rows DsMrr_impl::dsmrr_info(uint keyno, uint n_ranges, uint rows, + uint key_parts, uint *bufsz, uint *flags, COST_VECT *cost) { ha_rows res; @@ -590,8 +1260,8 @@ ha_rows DsMrr_impl::dsmrr_info(uint keyno, uint n_ranges, uint rows, uint def_bufsz= *bufsz; /* Get cost/flags/mem_usage of default MRR implementation */ - res= h->handler::multi_range_read_info(keyno, n_ranges, rows, &def_bufsz, - &def_flags, cost); + res= h->handler::multi_range_read_info(keyno, n_ranges, rows, key_parts, + &def_bufsz, &def_flags, cost); DBUG_ASSERT(!res); if ((*flags & HA_MRR_USE_DEFAULT_IMPL) || @@ -683,7 +1353,29 @@ bool key_uses_partial_cols(TABLE *table, uint keyno) return FALSE; } -/** + +/* + Check if key/flags allow DS-MRR/CPK strategy to be used + + @param thd + @param keyno Index that will be used + @param mrr_flags + + @retval TRUE DS-MRR/CPK should be used + @retval FALSE Otherwise +*/ + +bool DsMrr_impl::check_cpk_scan(THD *thd, uint keyno, uint mrr_flags) +{ + return test((mrr_flags & HA_MRR_SINGLE_POINT) && // check + // !(mrr_flags & HA_MRR_SORTED) && + keyno == table->s->primary_key && + h->primary_key_is_clustered() && + optimizer_flag(thd, OPTIMIZER_SWITCH_MRR_SORT_KEYS)); //check +} + + +/* DS-MRR Internals: Choose between Default MRR implementation and DS-MRR Make the choice between using Default MRR implementation and DS-MRR. @@ -706,21 +1398,25 @@ bool key_uses_partial_cols(TABLE *table, uint keyno) @retval FALSE DS-MRR implementation should be used */ + bool DsMrr_impl::choose_mrr_impl(uint keyno, ha_rows rows, uint *flags, uint *bufsz, COST_VECT *cost) { COST_VECT dsmrr_cost; bool res; THD *thd= current_thd; + + bool doing_cpk_scan= check_cpk_scan(thd, keyno, *flags); + bool using_cpk= test(keyno == table->s->primary_key && + h->primary_key_is_clustered()); if (thd->variables.optimizer_use_mrr == 2 || *flags & HA_MRR_INDEX_ONLY || - (keyno == table->s->primary_key && h->primary_key_is_clustered()) || - key_uses_partial_cols(table, keyno)) + (using_cpk && !doing_cpk_scan) || key_uses_partial_cols(table, keyno)) { /* Use the default implementation */ *flags |= HA_MRR_USE_DEFAULT_IMPL; return TRUE; } - + uint add_len= table->key_info[keyno].key_length + h->ref_length; *bufsz -= add_len; if (get_disk_sweep_mrr_cost(keyno, rows, *flags, bufsz, &dsmrr_cost)) @@ -744,6 +1440,10 @@ bool DsMrr_impl::choose_mrr_impl(uint keyno, ha_rows rows, uint *flags, *flags &= ~HA_MRR_SORTED; /* We will return unordered output */ *cost= dsmrr_cost; res= FALSE; + + if ((*flags & HA_MRR_SINGLE_POINT) && + optimizer_flag(thd, OPTIMIZER_SWITCH_MRR_SORT_KEYS)) + *flags |= HA_MRR_MATERIALIZED_KEYS; } else { @@ -828,17 +1528,14 @@ bool DsMrr_impl::get_disk_sweep_mrr_cost(uint keynr, ha_rows rows, uint flags, /* Get cost of one sort-and-sweep step + + It consists of two parts: + - sort an array of #nrows ROWIDs using qsort + - read #nrows records from table in a sweep. - SYNOPSIS - get_sort_and_sweep_cost() - table Table being accessed - nrows Number of rows to be sorted and retrieved - cost OUT The cost - - DESCRIPTION - Get cost of these operations: - - sort an array of #nrows ROWIDs using qsort - - read #nrows records from table in a sweep. + @param table Table being accessed + @param nrows Number of rows to be sorted and retrieved + @param cost OUT The cost of scan */ static diff --git a/sql/multi_range_read.h b/sql/multi_range_read.h index 90e2e4c93d6..14bcdd5de6c 100644 --- a/sql/multi_range_read.h +++ b/sql/multi_range_read.h @@ -1,49 +1,430 @@ -/* - This file contains declarations for - - Disk-Sweep MultiRangeRead (DS-MRR) implementation +/** + @defgroup DS-MRR declarations + @{ */ /** - A Disk-Sweep MRR interface implementation + A Disk-Sweep implementation of MRR Interface (DS-MRR for short) - This implementation makes range (and, in the future, 'ref') scans to read - table rows in disk sweeps. - - Currently it is used by MyISAM and InnoDB. Potentially it can be used with - any table handler that has non-clustered indexes and on-disk rows. + This is a "plugin"(*) for storage engines that allows to + 1. When doing index scans, read table rows in rowid order; + 2. when making many index lookups, do them in key order and don't + lookup the same key value multiple times; + 3. Do both #1 and #2, when applicable. + These changes are expected to speed up query execution for disk-based + storage engines running io-bound loads and "big" queries (ie. queries that + do joins and enumerate lots of records). + + (*) - only conceptually. No dynamic loading or binary compatibility of any + kind. + + General scheme of things: + + SQL Layer code + | | | + v v v + -|---|---|---- handler->multi_range_read_XXX() function calls + | | | + _____________________________________ + / DS-MRR module \ + | (order/de-duplicate lookup keys, | + | scan indexes in key order, | + | order/de-duplicate rowids, | + | retrieve full record reads in rowid | + | order) | + \_____________________________________/ + | | | + -|---|---|----- handler->read_range_first()/read_range_next(), + | | | handler->index_read(), handler->rnd_pos() calls. + | | | + v v v + Storage engine internals + + + Currently DS-MRR is used by MyISAM, InnoDB/XtraDB and Maria storage engines. + Potentially it can be used with any table handler that has disk-based data + storage and has better performance when reading data in rowid order. */ -class DsMrr_impl +#include "sql_lifo_buffer.h" + +class DsMrr_impl; + +class Key_parameters { public: - typedef void (handler::*range_check_toggle_func_t)(bool on); + /* TRUE <=> We can get at most one index tuple for a lookup key */ + bool index_ranges_unique; - DsMrr_impl() - : h2(NULL) {}; + uint key_tuple_length; /* Length of index lookup tuple, in bytes */ + key_part_map key_tuple_map; /* keyparts used in index lookup tuples */ + + /* + This is + = key_tuple_length if we copy keys to buffer + = sizeof(void*) if we're using pointers to materialized keys. + */ + uint key_size_in_keybuf; + + /* TRUE <=> don't copy key values, use pointers to them instead. */ + bool use_key_pointers; +}; + +/** + Iterator over (record, range_id) pairs that match given key value. + + We may need to scan multiple (key_val, range_id) pairs with the same + key value. A key value may have multiple matching records, so we'll need to + produce a cross-product of sets of matching records and range_id-s. +*/ +class Mrr_ordered_index_reader; +class Key_value_records_iterator +{ + /* Scan parameters */ + Key_parameters *param; + Lifo_buffer_iterator identical_key_it; + uchar *last_identical_key_ptr; + bool get_next_row; + //handler *h; + /* TRUE <=> We can get at most one index tuple for a lookup key */ + //bool index_ranges_unique; + Mrr_ordered_index_reader *owner; + /* key_buffer.read() reads to here */ + uchar *cur_index_tuple; +public: + bool init(Mrr_ordered_index_reader *owner_arg); + /* - The "owner" handler object (the one that calls dsmrr_XXX functions. - It is used to retrieve full table rows by calling rnd_pos(). + Get next (key_val, range_id) pair. */ + int get_next(); + + void close(); + friend class Mrr_ordered_index_reader; +}; + + +/* + Something that will manage buffers for those that call it +*/ +class Buffer_manager +{ +public: + virtual void reset_buffer_sizes()= 0; + virtual void setup_buffer_sizes(uint key_size_in_keybuf, + key_part_map key_tuple_map)=0; + virtual Lifo_buffer* get_key_buffer()= 0; + virtual ~Buffer_manager(){} +}; + + +/* + Abstract MRR execution strategy + + An object of this class produces (R, range_info) pairs where R can be an + index tuple or a table record. + + Getting HA_ERR_END_OF_FILE from get_next() means that the source should be + re-filled. + + Was: + if eof() returns true after refill attempt, then the end of + stream has been reached and get_next() must not be called anymore. + + Now: + if refill_buffer() returns HA_ERR_END_OF_FILE that means the stream is + really exhausted. + +*/ + +class Mrr_reader +{ +public: + virtual int get_next(char **range_info) = 0; + virtual int refill_buffer()=0; + + virtual ~Mrr_reader() {}; /* just to remove compiler warning */ +}; + + +/* A common base for strategies that do index scans and produce index tuples */ +class Mrr_index_reader : public Mrr_reader +{ +public: handler *h; - TABLE *table; /* Always equal to h->table */ + + virtual int init(handler *h_arg, RANGE_SEQ_IF *seq_funcs, + void *seq_init_param, uint n_ranges, + uint mode, Buffer_manager *buf_manager_arg) = 0; + virtual bool eof() = 0; + virtual uchar *get_rowid_ptr()= 0; + virtual bool skip_record(char *range_id, uchar *rowid)=0; +}; + + +/* + A "bypass" strategy that uses default MRR implementation (i.e. + handler::multi_range_read_XXX() calls) to produce rows. +*/ + +class Mrr_simple_index_reader : public Mrr_index_reader +{ + int res; +public: + int init(handler *h_arg, RANGE_SEQ_IF *seq_funcs, + void *seq_init_param, uint n_ranges, + uint mode, Buffer_manager *buf_manager_arg); + int get_next(char **range_info); + int refill_buffer() { return HA_ERR_END_OF_FILE; } + bool eof() { return test(res); } + uchar *get_rowid_ptr() { return h->ref; } + bool skip_record(char *range_id, uchar *rowid) + { + return (h->mrr_funcs.skip_record && + h->mrr_funcs.skip_record(h->mrr_iter, range_id, rowid)); + } +}; + + + +/* + A strategy that sorts index lookup keys before scanning the index +*/ + +class Mrr_ordered_index_reader : public Mrr_index_reader +{ +public: + int init(handler *h_arg, RANGE_SEQ_IF *seq_funcs, + void *seq_init_param, uint n_ranges, + uint mode, Buffer_manager *buf_manager_arg); + int get_next(char **range_info); + int refill_buffer(); + bool eof() { return index_scan_eof; } + uchar *get_rowid_ptr() { return h->ref; } + + bool skip_record(char *range_info, uchar *rowid) + { + return (mrr_funcs.skip_record && + mrr_funcs.skip_record(mrr_iter, range_info, rowid)); + } private: - /* Secondary handler object. It is used for scanning the index */ - handler *h2; + Key_value_records_iterator kv_it; + + bool scanning_key_val_iter; + + char *cur_range_info; - /* Buffer to store rowids, or (rowid, range_id) pairs */ - uchar *rowids_buf; - uchar *rowids_buf_cur; /* Current position when reading/writing */ - uchar *rowids_buf_last; /* When reading: end of used buffer space */ - uchar *rowids_buf_end; /* End of the buffer */ + /* Buffer to store (key, range_id) pairs */ + Lifo_buffer *key_buffer; - bool dsmrr_eof; /* TRUE <=> We have reached EOF when reading index tuples */ + Buffer_manager *buf_manager; - /* TRUE <=> need range association, buffer holds {rowid, range_id} pairs */ + /* Initially FALSE, becomes TRUE when we've set key_tuple_xxx members */ + bool know_key_tuple_params; + + // bool use_key_pointers; + + Key_parameters keypar; + /* TRUE <=> need range association, buffers hold {rowid, range_id} pairs */ bool is_mrr_assoc; - bool use_default_impl; /* TRUE <=> shortcut all calls to default MRR impl */ + bool no_more_keys; + RANGE_SEQ_IF mrr_funcs; + range_seq_t mrr_iter; + + //bool auto_refill; + + bool index_scan_eof; + + static int key_tuple_cmp(void* arg, uchar* key1, uchar* key2); + static int key_tuple_cmp_reverse(void* arg, uchar* key1, uchar* key2); + //void cleanup(); + + friend class Key_value_records_iterator; + friend class DsMrr_impl; + friend class Mrr_ordered_rndpos_reader; +}; + + +/* MRR strategy that fetches rowids */ + +class Mrr_ordered_rndpos_reader : public Mrr_reader +{ +public: + int init(handler *h, Mrr_index_reader *index_reader, uint mode, + Lifo_buffer *buf); + int get_next(char **range_info); + int refill_buffer(); + int refill2(); + void cleanup(); +private: + handler *h; + + DsMrr_impl *dsmrr; + /* This what we get (rowid, range_info) pairs from */ + Mrr_index_reader *index_reader; + uchar *index_rowid; + bool index_reader_exhausted; + + /* TRUE <=> need range association, buffers hold {rowid, range_id} pairs */ + bool is_mrr_assoc; + + uchar *last_identical_rowid; + Lifo_buffer *rowid_buffer; + + /* = h->ref_length [ + sizeof(range_assoc_info) ] */ + //uint rowid_buff_elem_size; + + /* rowid_buffer.read() will set the following: */ + uchar *rowid; + uchar *rowids_range_id; +}; + +class Mrr_reader_factory +{ +public: + Mrr_ordered_rndpos_reader ordered_rndpos_reader; + Mrr_ordered_index_reader ordered_index_reader; + Mrr_simple_index_reader simple_index_reader; +}; + +/* + DS-MRR implementation for one table. Create/use one object of this class for + each ha_{myisam/innobase/etc} object. That object will be further referred to + as "the handler" + + DsMrr_impl supports has the following execution strategies: + + - Bypass DS-MRR, pass all calls to default MRR implementation, which is + an MRR-to-non-MRR call converter. + - Key-Ordered Retrieval + - Rowid-Ordered Retrieval + + DsMrr_impl will use one of the above strategies, or a combination of them, + according to the following diagram: + + (mrr function calls) + | + +----------------->-----------------+ + | | + ___________v______________ _______________v________________ + / default: use lookup keys \ / KEY-ORDERED RETRIEVAL: \ + | (or ranges) in whatever | | sort lookup keys and then make | + | order they are supplied | | index lookups in index order | + \__________________________/ \________________________________/ + | | | | | + +---<---+ | +--------------->-----------|----+ + | | | | + | | +---------------+ | + | ______v___ ______ | _______________v_______________ + | / default: read \ | / ROWID-ORDERED RETRIEVAL: \ + | | table records | | | Before reading table records, | + v | in random order | v | sort their rowids and then | + | \_________________/ | | read them in rowid order | + | | | \_______________________________/ + | | | | + | | | | + +-->---+ | +----<------+-----------<--------+ + | | | + v v v + (table records and range_ids) + + The choice of strategy depends on MRR scan properties, table properties + (whether we're scanning clustered primary key), and @@optimizer_switch + settings. + + Key-Ordered Retrieval + --------------------- + The idea is: if MRR scan is essentially a series of lookups on + + tbl.key=value1 OR tbl.key=value2 OR ... OR tbl.key=valueN + + then it makes sense to collect and order the set of lookup values, i.e. + + sort(value1, value2, .. valueN) + + and then do index lookups in index order. This results in fewer index page + fetch operations, and we also can avoid making multiple index lookups for the + same value. That is, if value1=valueN we can easily discover that after + sorting and make one index lookup for them instead of two. + + Rowid-Ordered Retrieval + ----------------------- + If we do a regular index scan or a series of index lookups, we'll be hitting + table records at random. For disk-based engines, this is much slower than + reading the same records in disk order. We assume that disk ordering of + rows is the same as ordering of their rowids (which is provided by + handler::cmp_ref()) + In order to retrieve records in different order, we must separate index + scanning and record fetching, that is, MRR scan uses the following steps: + + 1. Scan the index (and only index, that is, with HA_EXTRA_KEYREAD on) and + fill a buffer with {rowid, range_id} pairs + 2. Sort the buffer by rowid value + 3. for each {rowid, range_id} pair in the buffer + get record by rowid and return the {record, range_id} pair + 4. Repeat the above steps until we've exhausted the list of ranges we're + scanning. + + Buffer space management considerations + -------------------------------------- + With regards to buffer/memory management, MRR interface specifies that + - SQL layer provides multi_range_read_init() with buffer of certain size. + - MRR implementation may use (i.e. have at its disposal till the end of + the MRR scan) all of the buffer, or return the unused end of the buffer + to SQL layer. + + DS-MRR needs buffer in order to accumulate and sort rowids and/or keys. When + we need to accumulate/sort only keys (or only rowids), it is fairly trivial. + + When we need to accumulate/sort both keys and rowids, efficient buffer use + gets complicated. We need to: + - First, accumulate keys and sort them + - Then use the keys (smaller values go first) to obtain rowids. A key is not + needed after we've got matching rowids for it. + - Make sure that rowids are accumulated at the front of the buffer, so that we + can return the end part of the buffer to SQL layer, should there be too + few rowid values to occupy the buffer. + + All of these goals are achieved by using the following scheme: + + | | We get an empty buffer from SQL layer. + + | *-| + | *----| First, we fill the buffer with keys. Key_buffer + | *-------| part grows from end of the buffer space to start + | *----------| (In this picture, the buffer is big enough to + | *-------------| accomodate all keys and even have some space left) + + | *=============| We want to do key-ordered index scan, so we sort + the keys + + |-x *===========| Then we use the keys get rowids. Rowids are + |----x *========| stored from start of buffer space towards the end. + |--------x *=====| The part of the buffer occupied with keys + |------------x *===| gradually frees up space for rowids. In this + |--------------x *=| picture we run out of keys before we've ran out + |----------------x | of buffer space (it can be other way as well). + + |================x | Then we sort the rowids. + + | |~~~| The unused part of the buffer is at the end, so + we can return it to the SQL layer. + + |================* Sorted rowids are then used to read table records + in disk order + +*/ + +class DsMrr_impl : public Buffer_manager +{ public: + typedef void (handler::*range_check_toggle_func_t)(bool on); + + DsMrr_impl() + : h2(NULL) {}; + void init(handler *h_arg, TABLE *table_arg) { h= h_arg; @@ -52,19 +433,102 @@ public: int dsmrr_init(handler *h, RANGE_SEQ_IF *seq_funcs, void *seq_init_param, uint n_ranges, uint mode, HANDLER_BUFFER *buf); void dsmrr_close(); - int dsmrr_fill_buffer(); int dsmrr_next(char **range_info); - ha_rows dsmrr_info(uint keyno, uint n_ranges, uint keys, uint *bufsz, - uint *flags, COST_VECT *cost); + ha_rows dsmrr_info(uint keyno, uint n_ranges, uint keys, uint key_parts, + uint *bufsz, uint *flags, COST_VECT *cost); ha_rows dsmrr_info_const(uint keyno, RANGE_SEQ_IF *seq, void *seq_init_param, uint n_ranges, uint *bufsz, uint *flags, COST_VECT *cost); private: + /* Buffer to store (key, range_id) pairs */ + Lifo_buffer *key_buffer; + + /* + The "owner" handler object (the one that is expected to "own" this object + and call its functions). + */ + handler *h; + TABLE *table; /* Always equal to h->table */ + + /* + Secondary handler object. (created when needed, we need it when we need + to run both index scan and rnd_pos() scan at the same time) + */ + handler *h2; + + /** Properties of current MRR scan **/ + + uint keyno; /* index we're running the scan on */ + /* TRUE <=> need range association, buffers hold {rowid, range_id} pairs */ + bool is_mrr_assoc; + /* TRUE <=> sort the keys before making index lookups */ + //bool do_sort_keys; + /* TRUE <=> sort rowids and use rnd_pos() to get and return full records */ + //bool do_rndpos_scan; + + Mrr_reader_factory strategy_factory; + Mrr_reader *strategy; + Mrr_index_reader *index_strategy; + + /* The whole buffer space that we're using */ + uchar *full_buf; + uchar *full_buf_end; + + /* + When using both rowid and key buffers: the boundary between key and rowid + parts of the buffer. This is the "original" value, actual memory ranges + used by key and rowid parts may be different because of dynamic space + reallocation between them. + */ + uchar *rowid_buffer_end; + + /** Index scaning and key buffer-related members **/ + + /* + One of the following two is used for key buffer: forward is used when + we only need key buffer, backward is used when we need both key and rowid + buffers. + */ + Forward_lifo_buffer forward_key_buf; + Backward_lifo_buffer backward_key_buf; + + Forward_lifo_buffer rowid_buffer; + + /* = key_size_in_keybuf [ + sizeof(range_assoc_info) ] */ + //uint key_buff_elem_size_; + + /** rnd_pos() scan and rowid buffer-related members **/ + + /* + Buffer to store (rowid, range_id) pairs, or just rowids if + is_mrr_assoc==FALSE + */ + //Forward_lifo_buffer rowid_buffer; + bool choose_mrr_impl(uint keyno, ha_rows rows, uint *flags, uint *bufsz, COST_VECT *cost); bool get_disk_sweep_mrr_cost(uint keynr, ha_rows rows, uint flags, uint *buffer_size, COST_VECT *cost); + bool check_cpk_scan(THD *thd, uint keyno, uint mrr_flags); + + void reallocate_buffer_space(); + + /* Buffer_manager implementation */ + void setup_buffer_sizes(uint key_size_in_keybuf, key_part_map key_tuple_map); + void reset_buffer_sizes(); + Lifo_buffer* get_key_buffer() { return key_buffer; } + + friend class Key_value_records_iterator; + friend class Mrr_ordered_index_reader; + friend class Mrr_ordered_rndpos_reader; + + int setup_two_handlers(); + void close_second_handler(); }; +/** + @} (end of group DS-MRR declarations) +*/ + diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 84c07dbf2b3..8bf609ff5de 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -571,12 +571,13 @@ protected: #define OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE 512 #define OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN 1024 #define OPTIMIZER_SWITCH_SUBQUERY_CACHE (1<<11) +#define OPTIMIZER_SWITCH_MRR_SORT_KEYS (1<<12) #ifdef DBUG_OFF -# define OPTIMIZER_SWITCH_LAST (1<<12) -#else -# define OPTIMIZER_SWITCH_TABLE_ELIMINATION (1<<12) # define OPTIMIZER_SWITCH_LAST (1<<13) +#else +# define OPTIMIZER_SWITCH_TABLE_ELIMINATION (1<<13) +# define OPTIMIZER_SWITCH_LAST (1<<14) #endif #ifdef DBUG_OFF @@ -592,7 +593,8 @@ protected: OPTIMIZER_SWITCH_SEMIJOIN | \ OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\ OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\ - OPTIMIZER_SWITCH_SUBQUERY_CACHE) + OPTIMIZER_SWITCH_SUBQUERY_CACHE|\ + OPTIMIZER_SWITCH_MRR_SORT_KEYS) #else # define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \ OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \ @@ -606,7 +608,8 @@ protected: OPTIMIZER_SWITCH_SEMIJOIN | \ OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\ OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\ - OPTIMIZER_SWITCH_SUBQUERY_CACHE) + OPTIMIZER_SWITCH_SUBQUERY_CACHE|\ + OPTIMIZER_SWITCH_MRR_SORT_KEYS) #endif /* diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 763515c9e9c..83646858400 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -345,6 +345,7 @@ static const char *optimizer_switch_names[]= "partial_match_rowid_merge", "partial_match_table_scan", "subquery_cache", + "mrr_sort_keys", #ifndef DBUG_OFF "table_elimination", #endif @@ -366,6 +367,7 @@ static const unsigned int optimizer_switch_names_len[]= sizeof("partial_match_rowid_merge") - 1, sizeof("partial_match_table_scan") - 1, sizeof("subquery_cache") - 1, + sizeof("mrr_sort_keys") - 1, #ifndef DBUG_OFF sizeof("table_elimination") - 1, #endif @@ -464,7 +466,8 @@ static const char *optimizer_switch_str="index_merge=on,index_merge_union=on," "semijoin=on," "partial_match_rowid_merge=on," "partial_match_table_scan=on," - "subquery_cache=on" + "subquery_cache=on," + "mrr_sort_keys=on" #ifndef DBUG_OFF ",table_elimination=on"; #else diff --git a/sql/opt_index_cond_pushdown.cc b/sql/opt_index_cond_pushdown.cc index 277343b81a5..2e5ad795668 100644 --- a/sql/opt_index_cond_pushdown.cc +++ b/sql/opt_index_cond_pushdown.cc @@ -378,6 +378,7 @@ void push_index_cond(JOIN_TAB *tab, uint keyno, bool other_tbls_ok) QT_ORDINARY);); tab->select->cond= tab->select_cond; + tab->select->pre_idx_push_select_cond= tab->pre_idx_push_select_cond; } } } diff --git a/sql/opt_range.cc b/sql/opt_range.cc index d77893bdb1c..e139839cf56 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -1119,7 +1119,7 @@ SQL_SELECT *make_select(TABLE *head, table_map const_tables, } -SQL_SELECT::SQL_SELECT() :quick(0),cond(0),free_cond(0) +SQL_SELECT::SQL_SELECT() :quick(0),cond(0),pre_idx_push_select_cond(NULL),free_cond(0) { quick_keys.clear_all(); needed_reg.clear_all(); my_b_clear(&file); @@ -8006,6 +8006,7 @@ QUICK_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table, quick->mrr_buf_size= thd->variables.mrr_buff_size; if (table->file->multi_range_read_info(quick->index, 1, (uint)records, + uint(-1), &quick->mrr_buf_size, &quick->mrr_flags, &cost)) goto err; diff --git a/sql/opt_range.h b/sql/opt_range.h index 1f2df1d7d55..072b83d4fbf 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -818,6 +818,13 @@ class SQL_SELECT :public Sql_alloc { public: QUICK_SELECT_I *quick; // If quick-select used COND *cond; // where condition + + /* + When using Index Condition Pushdown: condition that we've had before + extracting and pushing index condition. + In other cases, NULL. + */ + Item *pre_idx_push_select_cond; TABLE *head; IO_CACHE file; // Positions to used records ha_rows records; // Records in use if read from file diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc index 10064590a75..e0df7dfec88 100644 --- a/sql/sql_join_cache.cc +++ b/sql/sql_join_cache.cc @@ -651,6 +651,9 @@ int JOIN_CACHE_BKA::init() use_emb_key= check_emb_key_usage(); + if (use_emb_key) + mrr_mode|= HA_MRR_MATERIALIZED_KEYS; + create_remaining_fields(FALSE); set_constants(); @@ -2386,8 +2389,8 @@ JOIN_CACHE_BKA::init_join_matching_records(RANGE_SEQ_IF *seq_funcs, uint ranges) */ if (!file->inited) file->ha_index_init(join_tab->ref.key, 1); - if ((error= file->multi_range_read_init(seq_funcs, (void*) this, ranges, - mrr_mode, &mrr_buff))) + if ((error= file->multi_range_read_init(seq_funcs, (void*) this, ranges, + mrr_mode, &mrr_buff))) rc= error < 0 ? NESTED_LOOP_NO_MORE_ROWS: NESTED_LOOP_ERROR; return rc; @@ -2627,6 +2630,8 @@ int JOIN_CACHE_BKA_UNIQUE::init() data_fields_offset+= copy->length; } + mrr_mode|= HA_MRR_MATERIALIZED_KEYS; + DBUG_RETURN(rc); } diff --git a/sql/sql_lifo_buffer.h b/sql/sql_lifo_buffer.h new file mode 100644 index 00000000000..89b520484e4 --- /dev/null +++ b/sql/sql_lifo_buffer.h @@ -0,0 +1,339 @@ +/** + @defgroup Bi-directional LIFO buffers used by DS-MRR implementation + @{ +*/ + +class Forward_lifo_buffer; +class Backward_lifo_buffer; + + +/* + A base class for in-memory buffer used by DS-MRR implementation. Common + properties: + - The buffer is last-in-first-out, i.e. elements that are written last are + read first. + - The buffer contains fixed-size elements. The elements are either atomic + byte sequences or pairs of them. + - The buffer resides in the memory provided by the user. It is possible to + = dynamically (ie. between write operations) add ajacent memory space to + the buffer + = dynamically remove unused space from the buffer. + The intent of this is to allow to have two buffers on adjacent memory + space, one is being read from (and so its space shrinks), while the other + is being written to (and so it needs more and more space). + + There are two concrete classes, Forward_lifo_buffer and Backward_lifo_buffer. +*/ + +class Lifo_buffer +{ +protected: + /** + Pointers to data to be written. write() call will assume that + (*write_ptr1) points to size1 bytes of data to be written. + If write_ptr2 != NULL then the buffer stores pairs, and (*write_ptr2) + points to size2 bytes of data that form the second component. + */ + uchar **write_ptr1; + size_t size1; + uchar **write_ptr2; + size_t size2; + + /** + read() will do reading by storing pointer to read data into *read_ptr1 (if + the buffer stores atomic elements), or into {*read_ptr1, *read_ptr2} (if + the buffer stores pairs). + */ + uchar **read_ptr1; + uchar **read_ptr2; + + uchar *start; /**< points to start of buffer space */ + uchar *end; /**< points to just beyond the end of buffer space */ +public: + + enum enum_direction { + BACKWARD=-1, /**< buffer is filled/read from bigger to smaller memory addresses */ + FORWARD=1 /**< buffer is filled/read from smaller to bigger memory addresses */ + }; + + virtual enum_direction type() = 0; + + /* Buffer space control functions */ + + /** Let the buffer store data in the given space. */ + void set_buffer_space(uchar *start_arg, uchar *end_arg) + { + start= start_arg; + end= end_arg; + TRASH(start, end - start); + reset(); + } + + /** + Specify where write() should get the source data from, as well as source + data size. + */ + void setup_writing(uchar **data1, size_t len1, uchar **data2, size_t len2) + { + write_ptr1= data1; + size1= len1; + write_ptr2= data2; + size2= len2; + } + + /** + Specify where read() should store pointers to read data, as well as read + data size. The sizes must match those passed to setup_writing(). + */ + void setup_reading(uchar **data1, size_t len1, uchar **data2, size_t len2) + { + read_ptr1= data1; + DBUG_ASSERT(len1 == size1); + read_ptr2= data2; + DBUG_ASSERT(len2 == size2); + } + + bool can_write() + { + return have_space_for(size1 + (write_ptr2 ? size2 : 0)); + } + virtual void write() = 0; + + bool is_empty() { return used_size() == 0; } + virtual bool read() = 0; + + void sort(qsort2_cmp cmp_func, void *cmp_func_arg) + { + uint elem_size= size1 + (write_ptr2 ? size2 : 0); + uint n_elements= used_size() / elem_size; + my_qsort2(used_area(), n_elements, elem_size, cmp_func, cmp_func_arg); + } + + virtual void reset() = 0; + virtual uchar *end_of_space() = 0; +protected: + virtual bool have_space_for(size_t bytes) = 0; + virtual size_t used_size() = 0; + + /* To be used only by iterator class: */ + virtual uchar *get_pos()= 0; + virtual bool read(uchar **position)= 0; + friend class Lifo_buffer_iterator; +public: + virtual void remove_unused_space(uchar **unused_start, uchar **unused_end)=0; + virtual uchar *used_area() = 0; + virtual ~Lifo_buffer() {}; +}; + + +/** + Forward LIFO buffer + + The buffer that is being written to from start to end and read in the + reverse. 'pos' points to just beyond the end of used space. + + It is possible to grow/shink the buffer at the end bound + + used space unused space + *==============*-----------------* + ^ ^ ^ + | | +--- end + | +---- pos + +--- start +*/ + +class Forward_lifo_buffer: public Lifo_buffer +{ + uchar *pos; +public: + enum_direction type() { return FORWARD; } + size_t used_size() + { + return pos - start; + } + void reset() + { + pos= start; + } + uchar *end_of_space() { return pos; } + bool have_space_for(size_t bytes) + { + return (pos + bytes < end); + } + + void write() + { + write_bytes(*write_ptr1, size1); + if (write_ptr2) + write_bytes(*write_ptr2, size2); + } + void write_bytes(const uchar *data, size_t bytes) + { + DBUG_ASSERT(have_space_for(bytes)); + memcpy(pos, data, bytes); + pos += bytes; + } + bool have_data(uchar *position, size_t bytes) + { + return ((position - start) >= (ptrdiff_t)bytes); + } + uchar *read_bytes(uchar **position, size_t bytes) + { + DBUG_ASSERT(have_data(*position, bytes)); + *position= (*position) - bytes; + return *position; + } + bool read() { return read(&pos); } + bool read(uchar **position) + { + if (!have_data(*position, size1 + (read_ptr2 ? size2 : 0))) + return TRUE; + if (read_ptr2) + *read_ptr2= read_bytes(position, size2); + *read_ptr1= read_bytes(position, size1); + return FALSE; + } + void remove_unused_space(uchar **unused_start, uchar **unused_end) + { + DBUG_ASSERT(0); /* Don't need this yet */ + } + /** + Add more space to the buffer. The caller is responsible that the space + being added is adjacent to the end of the buffer. + + @param unused_start Start of space + @param unused_end End of space + */ + void grow(uchar *unused_start, uchar *unused_end) + { + DBUG_ASSERT(unused_end >= unused_start); + DBUG_ASSERT(end == unused_start); + TRASH(unused_start, unused_end - unused_start); + end= unused_end; + } + /* Return pointer to start of the memory area that is occupied by the data */ + uchar *used_area() { return start; } + friend class Lifo_buffer_iterator; + uchar *get_pos() { return pos; } +}; + + + +/** + Backward LIFO buffer + + The buffer that is being written to from start to end and read in the + reverse. 'pos' points to the start of used space. + + It is possible to grow/shink the buffer at the start. + + unused space used space + *--------------*=================* + ^ ^ ^ + | | +--- end + | +---- pos + +--- start +*/ +class Backward_lifo_buffer: public Lifo_buffer +{ + uchar *pos; +public: + enum_direction type() { return BACKWARD; } + + size_t used_size() + { + return end - pos; + } + void reset() + { + pos= end; + } + uchar *end_of_space() { return end; } + bool have_space_for(size_t bytes) + { + return (pos - bytes >= start); + } + void write() + { + if (write_ptr2) + write_bytes(*write_ptr2, size2); + write_bytes(*write_ptr1, size1); + } + void write_bytes(const uchar *data, size_t bytes) + { + DBUG_ASSERT(have_space_for(bytes)); + pos -= bytes; + memcpy(pos, data, bytes); + } + bool read() + { + return read(&pos); + } + bool read(uchar **position) + { + if (!have_data(*position, size1 + (read_ptr2 ? size2 : 0))) + return TRUE; + *read_ptr1= read_bytes(position, size1); + if (read_ptr2) + *read_ptr2= read_bytes(position, size2); + return FALSE; + } + bool have_data(uchar *position, size_t bytes) + { + return ((end - position) >= (ptrdiff_t)bytes); + } + uchar *read_bytes(uchar **position, size_t bytes) + { + DBUG_ASSERT(have_data(*position, bytes)); + uchar *ret= *position; + *position= *position + bytes; + return ret; + } + /** + Stop using/return the unused part of the space + @param unused_start OUT Start of the unused space + @param unused_end OUT End of the unused space + */ + void remove_unused_space(uchar **unused_start, uchar **unused_end) + { + *unused_start= start; + *unused_end= pos; + start= pos; + } + void grow(uchar *unused_start, uchar *unused_end) + { + DBUG_ASSERT(0); /* Not used for backward buffers */ + } + /* Return pointer to start of the memory area that is occupied by the data */ + uchar *used_area() { return pos; } + friend class Lifo_buffer_iterator; + uchar *get_pos() { return pos; } +}; + + + +/** Iterator to walk over contents of the buffer without reading it. */ +class Lifo_buffer_iterator +{ + uchar *pos; + Lifo_buffer *buf; +public: + void init(Lifo_buffer *buf_arg) + { + buf= buf_arg; + pos= buf->get_pos(); + } + /* + Read the next value. The calling convention is the same as buf->read() + has. + + @retval FALSE - ok + @retval TRUE - EOF, reached the end of the buffer + */ + bool read() + { + return buf->read(&pos); + } +}; + + diff --git a/sql/sql_select.cc b/sql/sql_select.cc index debd1b07fe5..aae5828c678 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -6623,7 +6623,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) DBUG_ENTER("make_join_select"); if (select) { - add_not_null_conds(join); table_map used_tables; /* Step #1: Extract constant condition @@ -7068,6 +7067,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) } } + add_not_null_conds(join); } DBUG_RETURN(0); } @@ -7494,10 +7494,11 @@ uint check_join_cache_usage(JOIN_TAB *tab, case JT_EQ_REF: if (cache_level <= 4) return 0; - flags= HA_MRR_NO_NULL_ENDPOINTS; + flags= HA_MRR_NO_NULL_ENDPOINTS | HA_MRR_SINGLE_POINT; if (tab->table->covering_keys.is_set(tab->ref.key)) flags|= HA_MRR_INDEX_ONLY; rows= tab->table->file->multi_range_read_info(tab->ref.key, 10, 20, + tab->ref.key_parts, &bufsz, &flags, &cost); if ((rows != HA_POS_ERROR) && !(flags & HA_MRR_USE_DEFAULT_IMPL) && (!(flags & HA_MRR_NO_ASSOCIATION) || cache_level > 6) && diff --git a/storage/maria/ha_maria.cc b/storage/maria/ha_maria.cc index 0ad414e300e..74c3f8f40ff 100644 --- a/storage/maria/ha_maria.cc +++ b/storage/maria/ha_maria.cc @@ -3609,8 +3609,8 @@ static struct st_mysql_show_var aria_status_variables[]= { ***************************************************************************/ int ha_maria::multi_range_read_init(RANGE_SEQ_IF *seq, void *seq_init_param, - uint n_ranges, uint mode, - HANDLER_BUFFER *buf) + uint n_ranges, uint mode, + HANDLER_BUFFER *buf) { return ds_mrr.dsmrr_init(this, seq, seq_init_param, n_ranges, mode, buf); } @@ -3636,11 +3636,11 @@ ha_rows ha_maria::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, } ha_rows ha_maria::multi_range_read_info(uint keyno, uint n_ranges, uint keys, - uint *bufsz, uint *flags, - COST_VECT *cost) + uint key_parts, uint *bufsz, + uint *flags, COST_VECT *cost) { ds_mrr.init(this, table); - return ds_mrr.dsmrr_info(keyno, n_ranges, keys, bufsz, flags, cost); + return ds_mrr.dsmrr_info(keyno, n_ranges, keys, key_parts, bufsz, flags, cost); } /* MyISAM MRR implementation ends */ diff --git a/storage/maria/ha_maria.h b/storage/maria/ha_maria.h index 605ad1d3a20..0433134275c 100644 --- a/storage/maria/ha_maria.h +++ b/storage/maria/ha_maria.h @@ -185,7 +185,8 @@ public: uint n_ranges, uint *bufsz, uint *flags, COST_VECT *cost); ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys, - uint *bufsz, uint *flags, COST_VECT *cost); + uint key_parts, uint *bufsz, + uint *flags, COST_VECT *cost); /* Index condition pushdown implementation */ Item *idx_cond_push(uint keyno, Item* idx_cond); diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc index 368bb0bd4e3..85012b196f5 100644 --- a/storage/myisam/ha_myisam.cc +++ b/storage/myisam/ha_myisam.cc @@ -2224,11 +2224,11 @@ ha_rows ha_myisam::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, } ha_rows ha_myisam::multi_range_read_info(uint keyno, uint n_ranges, uint keys, - uint *bufsz, uint *flags, - COST_VECT *cost) + uint key_parts, uint *bufsz, + uint *flags, COST_VECT *cost) { ds_mrr.init(this, table); - return ds_mrr.dsmrr_info(keyno, n_ranges, keys, bufsz, flags, cost); + return ds_mrr.dsmrr_info(keyno, n_ranges, keys, key_parts, bufsz, flags, cost); } /* MyISAM MRR implementation ends */ diff --git a/storage/myisam/ha_myisam.h b/storage/myisam/ha_myisam.h index 3fed26632fa..4326c0e4e7e 100644 --- a/storage/myisam/ha_myisam.h +++ b/storage/myisam/ha_myisam.h @@ -168,7 +168,8 @@ public: uint n_ranges, uint *bufsz, uint *flags, COST_VECT *cost); ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys, - uint *bufsz, uint *flags, COST_VECT *cost); + uint key_parts, uint *bufsz, + uint *flags, COST_VECT *cost); /* Index condition pushdown implementation */ Item *idx_cond_push(uint keyno, Item* idx_cond); diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc index f511918e845..b4f9712dd97 100644 --- a/storage/xtradb/handler/ha_innodb.cc +++ b/storage/xtradb/handler/ha_innodb.cc @@ -12032,7 +12032,8 @@ test_innobase_convert_name() */ int ha_innobase::multi_range_read_init(RANGE_SEQ_IF *seq, void *seq_init_param, - uint n_ranges, uint mode, HANDLER_BUFFER *buf) + uint n_ranges, uint mode, + HANDLER_BUFFER *buf) { return ds_mrr.dsmrr_init(this, seq, seq_init_param, n_ranges, mode, buf); } @@ -12059,12 +12060,13 @@ ha_rows ha_innobase::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, return res; } -ha_rows ha_innobase::multi_range_read_info(uint keyno, uint n_ranges, - uint keys, uint *bufsz, +ha_rows ha_innobase::multi_range_read_info(uint keyno, uint n_ranges, uint keys, + uint key_parts, uint *bufsz, uint *flags, COST_VECT *cost) { ds_mrr.init(this, table); - ha_rows res= ds_mrr.dsmrr_info(keyno, n_ranges, keys, bufsz, flags, cost); + ha_rows res= ds_mrr.dsmrr_info(keyno, n_ranges, keys, key_parts, bufsz, + flags, cost); return res; } diff --git a/storage/xtradb/handler/ha_innodb.h b/storage/xtradb/handler/ha_innodb.h index 50a43aaebed..761ea55bad9 100644 --- a/storage/xtradb/handler/ha_innodb.h +++ b/storage/xtradb/handler/ha_innodb.h @@ -234,7 +234,8 @@ public: uint n_ranges, uint *bufsz, uint *flags, COST_VECT *cost); ha_rows multi_range_read_info(uint keyno, uint n_ranges, uint keys, - uint *bufsz, uint *flags, COST_VECT *cost); + uint key_parts, uint *bufsz, + uint *flags, COST_VECT *cost); DsMrr_impl ds_mrr; Item *idx_cond_push(uint keyno, Item* idx_cond); |