- developers - lists.mariadb.org

[Maria-developers] Rev 2735: Backport into MariaDB-5.2 the following: in file:///home/psergey/dev/maria-5.2-dsmrr/
by Sergey Petrunya 15 Dec '09

15 Dec '09

At file:///home/psergey/dev/maria-5.2-dsmrr/ ------------------------------------------------------------ revno: 2735 revision-id: psergey(a)askmonty.org-20091215172355-m8gr6zzr3r8m399y parent: psergey(a)askmonty.org-20091215145330-oqv8jlxh36sl863m committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.2-dsmrr timestamp: Tue 2009-12-15 20:23:55 +0300 message: Backport into MariaDB-5.2 the following: WL#2474 "Multi Range Read: Change the default MRR implementation to … [View More]implement new MRR interface" WL#2475 "Batched range read functions for MyISAM/InnoDb" "Index condition pushdown for MyISAM/InnoDB" - Adjust test results (checked) - Code cleanup. === modified file 'mysql-test/r/myisam_mrr.result' --- a/mysql-test/r/myisam_mrr.result 2009-12-15 07:16:46 +0000 +++ b/mysql-test/r/myisam_mrr.result 2009-12-15 17:23:55 +0000 @@ -3,9 +3,6 @@ set read_rnd_buffer_size=79; Warnings: Warning 1292 Truncated incorrect read_rnd_buffer_size value: '79' -select @@read_rnd_buffer_size; -@@read_rnd_buffer_size -8228 create table t1(a int); show create table t1; Table Create Table === modified file 'mysql-test/suite/maria/r/maria.result' --- a/mysql-test/suite/maria/r/maria.result 2009-06-29 21:03:30 +0000 +++ b/mysql-test/suite/maria/r/maria.result 2009-12-15 17:23:55 +0000 @@ -382,20 +382,20 @@ explain select * from t1,t2 where t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL b NULL NULL NULL 2 -1 SIMPLE t1 ref b b 5 test.t2.b 1 Using where +1 SIMPLE t1 ref b b 5 test.t2.b 1 explain select * from t1,t2 force index(c) 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 2 1 SIMPLE t1 ref a a 4 test.t2.a 3 explain select * from t1 where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 4 NULL 4 Using where +1 SIMPLE t1 range a a 4 NULL 4 Using index condition; Using MRR explain select * from t1 force index (a) where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 4 NULL 4 Using where +1 SIMPLE t1 range a a 4 NULL 4 Using index condition; Using MRR explain select * from t1 where c=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c,c_2 c 5 const 1 Using where +1 SIMPLE t1 ref c,c_2 c 5 const 1 explain select * from t1 use index() where c=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where @@ -1131,7 +1131,7 @@ *a *a*a * explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v,v_2 # 13 const # Using where +1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition select v,count(*) from t1 group by v limit 10; v count(*) a 1 @@ -1307,7 +1307,7 @@ 1 SIMPLE t1 ref v v 303 const # Using where; Using index explain select * from t1 where v='a'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref v v 303 const # Using where +1 SIMPLE t1 ref v v 303 const # Using index condition select v,count(*) from t1 group by v limit 10; v count(*) a 1 === modified file 'mysql-test/suite/parts/r/partition_special_innodb.result' --- a/mysql-test/suite/parts/r/partition_special_innodb.result 2009-10-06 11:39:57 +0000 +++ b/mysql-test/suite/parts/r/partition_special_innodb.result 2009-12-15 17:23:55 +0000 @@ -31,9 +31,9 @@ 2000-06-15 jukg zikhuk m select * from t1 where a<19851231; a b c d +1975-01-01 abcde abcde m +1980-10-14 fgbbd dtzndtz w 1983-12-31 cdef srtbvsr w -1980-10-14 fgbbd dtzndtz w -1975-01-01 abcde abcde m drop table t1; create table t1 (a date not null, b varchar(50) not null, c varchar(50) not null, d enum('m', 'w') not null, e int not null, f decimal (18,2) not null, g bigint not null, h tinyint not null, i char(255), primary key(a,b,c,d,e,f,g,h)) engine='InnoDB' partition by key(a,b,c,d,e,f,g,h) ( @@ -73,9 +73,9 @@ 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m select * from t1 where a<19851231; a b c d e f g h i +1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f sÃ¶ierugsig msireg siug ei5ggth lrutluitgzeÃ¶jrtnb.rkjthuekuhzrkuthgjdnffjmbr 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi -1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh drop table t1; create table t1 (a date not null, b varchar(50) not null, c varchar(50) not null, d enum('m', 'w') not null, e int not null, f decimal (18,2) not null, g bigint not null, h tinyint not null, a1 date not null, b1 varchar(50) not null, c1 varchar(50) not null, d1 enum('m', 'w') not null, e1 int not null, f1 decimal (18,2) not null, g1 bigint not null, h1 tinyint not null, i char(255), primary key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1)) engine='InnoDB' partition by key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1) ( @@ -123,9 +123,9 @@ 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m select * from t1 where a<19851231; a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 i +1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh +1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f sÃ¶ierugsig msireg siug ei5ggth lrutluitgzeÃ¶jrtnb.rkjthuekuhzrkuthgjdnffjmbr 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi -1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f sÃ¶ierugsig msireg siug ei5ggth lrutluitgzeÃ¶jrtnb.rkjthuekuhzrkuthgjdnffjmbr -1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh drop table t1; create table t1 (a date not null, b varchar(50) not null, c varchar(50) not null, d enum('m', 'w') not null, e int not null, f decimal (18,2) not null, g bigint not null, h tinyint not null, a1 date not null, b1 varchar(50) not null, c1 varchar(50) not null, d1 enum('m', 'w') not null, e1 int not null, f1 decimal (18,2) not null, g1 bigint not null, h1 tinyint not null, a2 date not null, b2 varchar(50) not null, c2 varchar(50) not null, d2 enum('m', 'w') not null, e2 int not null, f2 decimal (18,2) not null, g2 bigint not null, h2 tinyint not null, a3 date not null, b3 varchar(50) not null, c3 varchar(50) not null, d3 enum('m', 'w') not null, e3 int not null, f3 decimal (18,2) not null, g3 bigint not null, h3 tinyint not null, i char(255), primary key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1,a2,b2,c2,d2,e2,f2,g2,h2,a3,b3,c3,d3,e3,f3,g3,h3,a4)) engine='InnoDB' partition by key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1,a2,b2,c2,d2,e2,f2,g2,h2,a3,b3,c3,d3,e3,f3,g3,h3,a4) ( @@ -203,9 +203,9 @@ 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m select * from t1 where a<19851231; a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 a2 b2 c2 d2 e2 f2 g2 h2 a3 b3 c3 d3 e3 f3 g3 h3 i +1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f sÃ¶ierugsig msireg siug ei5ggth lrutluitgzeÃ¶jrtnb.rkjthuekuhzrkuthgjdnffjmbr 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi -1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh drop table t1; # Bug#34604 - Assertion 'inited==RND' failed in handler::ha_rnd_end CREATE TABLE t1 ( === modified file 'mysql-test/suite/parts/r/partition_special_myisam.result' --- a/mysql-test/suite/parts/r/partition_special_myisam.result 2009-10-06 11:39:57 +0000 +++ b/mysql-test/suite/parts/r/partition_special_myisam.result 2009-12-15 17:23:55 +0000 @@ -73,9 +73,9 @@ 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m select * from t1 where a<19851231; a b c d e f g h i +1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f sÃ¶ierugsig msireg siug ei5ggth lrutluitgzeÃ¶jrtnb.rkjthuekuhzrkuthgjdnffjmbr 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi -1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh drop table t1; create table t1 (a date not null, b varchar(50) not null, c varchar(50) not null, d enum('m', 'w') not null, e int not null, f decimal (18,2) not null, g bigint not null, h tinyint not null, a1 date not null, b1 varchar(50) not null, c1 varchar(50) not null, d1 enum('m', 'w') not null, e1 int not null, f1 decimal (18,2) not null, g1 bigint not null, h1 tinyint not null, i char(255), primary key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1)) engine='MyISAM' partition by key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1) ( @@ -123,9 +123,9 @@ 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m select * from t1 where a<19851231; a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 i +1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh +1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f sÃ¶ierugsig msireg siug ei5ggth lrutluitgzeÃ¶jrtnb.rkjthuekuhzrkuthgjdnffjmbr 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi -1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f sÃ¶ierugsig msireg siug ei5ggth lrutluitgzeÃ¶jrtnb.rkjthuekuhzrkuthgjdnffjmbr -1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh drop table t1; create table t1 (a date not null, b varchar(50) not null, c varchar(50) not null, d enum('m', 'w') not null, e int not null, f decimal (18,2) not null, g bigint not null, h tinyint not null, a1 date not null, b1 varchar(50) not null, c1 varchar(50) not null, d1 enum('m', 'w') not null, e1 int not null, f1 decimal (18,2) not null, g1 bigint not null, h1 tinyint not null, a2 date not null, b2 varchar(50) not null, c2 varchar(50) not null, d2 enum('m', 'w') not null, e2 int not null, f2 decimal (18,2) not null, g2 bigint not null, h2 tinyint not null, a3 date not null, b3 varchar(50) not null, c3 varchar(50) not null, d3 enum('m', 'w') not null, e3 int not null, f3 decimal (18,2) not null, g3 bigint not null, h3 tinyint not null, i char(255), primary key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1,a2,b2,c2,d2,e2,f2,g2,h2,a3,b3,c3,d3,e3,f3,g3,h3,a4)) engine='MyISAM' partition by key(a,b,c,d,e,f,g,h,a1,b1,c1,d1,e1,f1,g1,h1,a2,b2,c2,d2,e2,f2,g2,h2,a3,b3,c3,d3,e3,f3,g3,h3,a4) ( @@ -203,7 +203,7 @@ 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 2000-06-15 jukg zikhuk m 45675 6465754.13 435242623462 18 pib mdotkbm.m select * from t1 where a<19851231; a b c d e f g h a1 b1 c1 d1 e1 f1 g1 h1 a2 b2 c2 d2 e2 f2 g2 h2 a3 b3 c3 d3 e3 f3 g3 h3 i +1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 1980-10-14 fgbbd dtzndtz w 67856 5463354.67 3567845333 124 d,f sÃ¶ierugsig msireg siug ei5ggth lrutluitgzeÃ¶jrtnb.rkjthuekuhzrkuthgjdnffjmbr 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 1983-12-31 cdef srtbvsr w 45634 13452.56 3452346456 127 liuugbzvdmrlti b itiortudirtfgtibm dfi -1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 1975-01-01 abcde abcde m 1234 123.45 32412341234 113 tbhth nrzh ztfghgfh fzh ftzhj fztjh drop table t1; === modified file 'mysql-test/suite/pbxt/r/distinct.result' --- a/mysql-test/suite/pbxt/r/distinct.result 2009-08-17 15:57:58 +0000 +++ b/mysql-test/suite/pbxt/r/distinct.result 2009-12-15 17:23:55 +0000 @@ -175,7 +175,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 4 Using temporary 1 SIMPLE t2 ref a a 4 test.t1.a 1 Using index -1 SIMPLE t3 ref a a 5 test.t1.b 1 Using where; Using index +1 SIMPLE t3 ref a a 5 test.t1.b 1 Using index SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a; a 1 @@ -190,7 +190,7 @@ explain select distinct t1.a from t1,t3 where t1.a=t3.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary -1 SIMPLE t3 ref a a 5 test.t1.a 1 Using where; Using index; Distinct +1 SIMPLE t3 ref a a 5 test.t1.a 1 Using index; Distinct select distinct t1.a from t1,t3 where t1.a=t3.a; a 1 === modified file 'mysql-test/suite/pbxt/r/explain.result' --- a/mysql-test/suite/pbxt/r/explain.result 2009-04-02 10:03:14 +0000 +++ b/mysql-test/suite/pbxt/r/explain.result 2009-12-15 17:23:55 +0000 @@ -50,7 +50,7 @@ insert into ÔÁÂ (ËÏÌ0) values (2); explain select ËÏÌ0 from ÔÁÂ where ËÏÌ0=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE ÔÁÂ ref ÉÎÄ0,ÉÎÄ01 ÉÎÄ0 5 const 1 Using where; Using index +1 SIMPLE ÔÁÂ ref ÉÎÄ0,ÉÎÄ01 ÉÎÄ0 5 const 1 Using index drop table ÔÁÂ; set names latin1; select 3 into @v1; === modified file 'mysql-test/suite/pbxt/r/heap.result' --- a/mysql-test/suite/pbxt/r/heap.result 2009-04-02 10:03:14 +0000 +++ b/mysql-test/suite/pbxt/r/heap.result 2009-12-15 17:23:55 +0000 @@ -204,7 +204,7 @@ INSERT INTO t1 VALUES (10), (10), (10); EXPLAIN SELECT * FROM t1 WHERE a=10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 5 const 3 Using where +1 SIMPLE t1 ref a a 5 const 3 SELECT * FROM t1 WHERE a=10; a 10 === modified file 'mysql-test/suite/pbxt/r/join.result' --- a/mysql-test/suite/pbxt/r/join.result 2009-08-17 15:57:58 +0000 +++ b/mysql-test/suite/pbxt/r/join.result 2009-12-15 17:23:55 +0000 @@ -774,7 +774,7 @@ explain select * from t2,t3 where t2.a < 200 and t2.b=t3.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range a,b a 5 NULL 1 Using where -1 SIMPLE t3 ref b b 5 test.t2.b 1 Using where +1 SIMPLE t3 ref b b 5 test.t2.b 1 drop table t1, t2, t3; create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); === modified file 'mysql-test/suite/pbxt/r/join_nested.result' --- a/mysql-test/suite/pbxt/r/join_nested.result 2009-08-17 15:57:58 +0000 +++ b/mysql-test/suite/pbxt/r/join_nested.result 2009-12-15 17:23:55 +0000 @@ -74,7 +74,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on((`test`.`t4`.`b` = `test`.`t2`.`b`)) where ((`test`.`t3`.`a` = 1) or isnull(`test`.`t3`.`c`)) SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b @@ -150,7 +150,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on((`test`.`t4`.`b` = `test`.`t2`.`b`)) where ((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`)) @@ -180,7 +180,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on((`test`.`t4`.`b` = `test`.`t2`.`b`)) where (((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`)) and ((`test`.`t5`.`a` < 3) or isnull(`test`.`t5`.`c`))) @@ -230,7 +230,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using join buffer -1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t7`.`b` = `test`.`t8`.`b`) and (`test`.`t6`.`b` < 10))) where 1 SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b @@ -546,12 +546,12 @@ 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 -1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t8 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`. `c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) where ((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`))) SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, @@ -837,8 +837,8 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 -1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) where (`test`.`t1`.`a` <= 2) CREATE INDEX idx_b ON t2(b); @@ -851,7 +851,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t4 ALL NULL NULL NULL NULL 2 100.00 Using join buffer -1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 1 100.00 +1 SIMPLE t2 ref idx_b idx_b 5 test.t3.b 1 100.00 Using where 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t3`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` = `test`.`t4`.`b`))) where 1 @@ -1054,8 +1054,8 @@ (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 Using where -1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 1 100.00 Using where +1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 +1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 1 100.00 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 100.00 @@ -1202,13 +1202,13 @@ EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON c < 3 and b = c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL 21 Using index -1 SIMPLE t3 index c c 5 NULL 6 Using index +1 SIMPLE t3 index c c 5 NULL 6 Using where; Using index 1 SIMPLE t2 ref b b 5 test.t3.c 1 Using index EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL # Using index 1 SIMPLE t3 index c c 5 NULL # Using index -1 SIMPLE t2 ref b b 5 test.t3.c # Using index +1 SIMPLE t2 ref b b 5 test.t3.c # Using where; Using index SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c NULL 0 0 @@ -1279,7 +1279,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 5 NULL # Using index 1 SIMPLE t3 index c c 5 NULL # Using index -1 SIMPLE t2 ref b b 5 test.t3.c # Using index +1 SIMPLE t2 ref b b 5 test.t3.c # Using where; Using index SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c; a b c NULL NULL NULL @@ -1323,8 +1323,8 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ALL NULL NULL NULL NULL 0 -1 SIMPLE t3 ALL NULL NULL NULL NULL 0 +1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where DROP TABLE t1,t2,t3; CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL); INSERT INTO t1 VALUES (23, 2340), (26, 9900); === modified file 'mysql-test/suite/pbxt/r/key_cache.result' --- a/mysql-test/suite/pbxt/r/key_cache.result 2009-08-17 15:57:58 +0000 +++ b/mysql-test/suite/pbxt/r/key_cache.result 2009-12-15 17:23:55 +0000 @@ -122,7 +122,7 @@ explain select count(*) from t1, t2 where t1.p = t2.i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index -1 SIMPLE t2 ref k1 k1 5 test.t1.p 1 Using where; Using index +1 SIMPLE t2 ref k1 k1 5 test.t1.p 1 Using index select count(*) from t1, t2 where t1.p = t2.i; count(*) 3 === modified file 'mysql-test/suite/pbxt/r/negation_elimination.result' --- a/mysql-test/suite/pbxt/r/negation_elimination.result 2009-04-02 10:03:14 +0000 +++ b/mysql-test/suite/pbxt/r/negation_elimination.result 2009-12-15 17:23:55 +0000 @@ -79,7 +79,7 @@ 19 explain select * from t1 where not(a != 10); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a a 5 const 1 Using where; Using index +1 SIMPLE t1 ref a a 5 const 1 Using index select * from t1 where not(a != 1); a 1 === modified file 'mysql-test/suite/pbxt/r/null.result' --- a/mysql-test/suite/pbxt/r/null.result 2009-08-17 15:57:58 +0000 +++ b/mysql-test/suite/pbxt/r/null.result 2009-12-15 17:23:55 +0000 @@ -170,7 +170,7 @@ insert into t1 values(null); explain select * from t1 where i=2 or i is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null i i 5 const # Using where; Using index +1 SIMPLE t1 ref_or_null i i 5 const # Using index select count(*) from t1 where i=2 or i is null; count(*) 10 === modified file 'mysql-test/suite/pbxt/r/null_key.result' --- a/mysql-test/suite/pbxt/r/null_key.result 2009-08-17 15:57:58 +0000 +++ b/mysql-test/suite/pbxt/r/null_key.result 2009-12-15 17:23:55 +0000 @@ -21,10 +21,10 @@ 1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index explain select * from t1 where (a is null or a = 7) and b=7; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using index explain select * from t1 where (a is null or a = 7) and b=7 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index; Using filesort +1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using index; Using filesort explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index @@ -151,7 +151,7 @@ insert into t1 values (7,null), (8,null), (8,7); explain select * from t1 where a = 7 and (b=7 or b is null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using where; Using index +1 SIMPLE t1 ref_or_null a,b a 10 const,const 2 Using index select * from t1 where a = 7 and (b=7 or b is null); a b 7 7 @@ -166,7 +166,7 @@ NULL 7 explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a a 5 const 5 Using where; Using index +1 SIMPLE t1 ref_or_null a a 5 const 5 Using index select * from t1 where (a = 7 or a is null) and (a = 7 or a is null); a b 7 NULL @@ -192,7 +192,7 @@ explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 -1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using index select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null); a a b 7 7 7 @@ -202,7 +202,7 @@ explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 -1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using where; Using index +1 SIMPLE t1 ref_or_null a a 10 test.t2.a,const 4 Using index select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7; a a b 7 7 7 @@ -226,7 +226,7 @@ explain select * from t2,t1 where t1.a=t2.a or t1.a is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 -1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using where; Using index +1 SIMPLE t1 ref_or_null a a 5 test.t2.a 4 Using index explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 @@ -258,7 +258,7 @@ INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL); explain select id from t1 where uniq_id is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1 idx1 5 const 5 Using where +1 SIMPLE t1 ref idx1 idx1 5 const 5 Using index condition explain select id from t1 where uniq_id =1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const idx1 idx1 5 const 1 === modified file 'mysql-test/t/myisam_mrr.test' --- a/mysql-test/t/myisam_mrr.test 2009-12-15 07:16:46 +0000 +++ b/mysql-test/t/myisam_mrr.test 2009-12-15 17:23:55 +0000 @@ -8,7 +8,6 @@ set @read_rnd_buffer_size_save= @@read_rnd_buffer_size; set read_rnd_buffer_size=79; -select @@read_rnd_buffer_size; -- source include/mrr_tests.inc === modified file 'sql/ds_mrr.cc' --- a/sql/ds_mrr.cc 2009-12-15 07:16:46 +0000 +++ b/sql/ds_mrr.cc 2009-12-15 17:23:55 +0000 @@ -950,8 +950,15 @@ ((tab->table->file->index_flags(keyno, 0, 1) & HA_DO_INDEX_COND_PUSHDOWN) && tab->join->thd->variables.engine_condition_pushdown); - // psergey: - // + + /* + Do not try index condition pushdown on indexes which have partially-covered + columns. Unpacking from a column prefix into index tuple is not a supported + operation in some engines, see e.g. MySQL BUG#42991. + TODO: a better solution would be not to consider partially-covered columns + as parts of the index and still produce/check index condition for + fully-covered index columns. + */ KEY *key_info= tab->table->key_info + keyno; for (uint kp= 0; kp < key_info->key_parts; kp++) { @@ -961,7 +968,7 @@ break; } } - // :psergey + /* When WL#5116 is done this DBUG statement must be removed. It's just a temporary hack to allow us to discriminate whether a test failure relates @@ -984,7 +991,8 @@ Item *idx_remainder_cond= 0; tab->pre_idx_push_select_cond= tab->select_cond; #if 0 - // The following is only needed for BKA: + /* + psergey: enable the below when we backport BKA: */ /* For BKA cache we store condition to special BKA cache field because evaluation of the condition requires additional operations === modified file 'sql/ds_mrr.h' --- a/sql/ds_mrr.h 2009-12-15 07:16:46 +0000 +++ b/sql/ds_mrr.h 2009-12-15 17:23:55 +0000 @@ -1,4 +1,8 @@ - +/* + This file contains declarations for + - Disk-Sweep MultiRangeRead (DS-MRR) implementation + - Index Condition Pushdown helper functions +*/ /** A Disk-Sweep MRR interface implementation @@ -65,4 +69,5 @@ uint *buffer_size, COST_VECT *cost); }; +void push_index_cond(JOIN_TAB *tab, uint keyno, bool other_tbls_ok); === modified file 'sql/ha_ndbcluster.cc' --- a/sql/ha_ndbcluster.cc 2009-09-07 20:50:10 +0000 +++ b/sql/ha_ndbcluster.cc 2009-12-15 17:23:55 +0000 @@ -8702,6 +8702,8 @@ DBUG_RETURN(FALSE); } +#if 0 +/* MRR/NDB is disabled, for details see method declarations in ha_ndbcluster.h */ int ha_ndbcluster::read_multi_range_first(KEY_MULTI_RANGE **found_range_p, KEY_MULTI_RANGE *ranges, @@ -9074,6 +9076,7 @@ m_multi_range_result_ptr += reclength; DBUG_RETURN(0); } +#endif int ha_ndbcluster::setup_recattr(const NdbRecAttr* curr) === modified file 'sql/ha_ndbcluster.h' --- a/sql/ha_ndbcluster.h 2008-05-29 18:39:25 +0000 +++ b/sql/ha_ndbcluster.h 2009-12-15 17:23:55 +0000 @@ -259,10 +259,20 @@ /** * Multi range stuff */ +#if 0 + /* + MRR/NDB is disabled in MariaDB. This is because in MariaDB, we've + backported + - the latest version of MRR interface (BKA needs this) + - the latest version of DS-MRR implementation + but didn't backport the latest version MRR/NDB implementation. + + */ int read_multi_range_first(KEY_MULTI_RANGE **found_range_p, KEY_MULTI_RANGE*ranges, uint range_count, bool sorted, HANDLER_BUFFER *buffer); int read_multi_range_next(KEY_MULTI_RANGE **found_range_p); +#endif bool null_value_index_search(KEY_MULTI_RANGE *ranges, KEY_MULTI_RANGE *end_range, HANDLER_BUFFER *buffer); === modified file 'sql/handler.cc' --- a/sql/handler.cc 2009-12-15 07:16:46 +0000 +++ b/sql/handler.cc 2009-12-15 17:23:55 +0000 @@ -4193,136 +4193,6 @@ return read_time; } -#if 0 -// psergey-mrr - -/** - Read the first row of a multi-range set. - - @param found_range_p Returns a pointer to the element in 'ranges' that - corresponds to the returned row. - @param ranges An array of KEY_MULTI_RANGE range descriptions. - @param range_count Number of ranges in 'ranges'. - @param sorted If result should be sorted per key. - @param buffer A HANDLER_BUFFER for internal handler usage. - - @note - - Record is read into table->record[0]. - - *found_range_p returns a valid value only if read_multi_range_first() - returns 0. - - Sorting is done within each range. If you want an overall sort, enter - 'ranges' with sorted ranges. - - @retval - 0 OK, found a row - @retval - HA_ERR_END_OF_FILE No rows in range - @retval - \# Error code -*/ -int handler::read_multi_range_first(KEY_MULTI_RANGE **found_range_p, - KEY_MULTI_RANGE *ranges, uint range_count, - bool sorted, HANDLER_BUFFER *buffer) -{ - int result= HA_ERR_END_OF_FILE; - DBUG_ENTER("handler::read_multi_range_first"); - multi_range_sorted= sorted; - multi_range_buffer= buffer; - - table->mark_columns_used_by_index_no_reset(active_index, table->read_set); - table->column_bitmaps_set(table->read_set, table->write_set); - - for (multi_range_curr= ranges, multi_range_end= ranges + range_count; - multi_range_curr < multi_range_end; - multi_range_curr++) - { - result= read_range_first(multi_range_curr->start_key.keypart_map ? - &multi_range_curr->start_key : 0, - multi_range_curr->end_key.keypart_map ? - &multi_range_curr->end_key : 0, - test(multi_range_curr->range_flag & EQ_RANGE), - multi_range_sorted); - if (result != HA_ERR_END_OF_FILE) - break; - } - - *found_range_p= multi_range_curr; - DBUG_PRINT("exit",("result %d", result)); - DBUG_RETURN(result); -} - - -/** - Read the next row of a multi-range set. - - @param found_range_p Returns a pointer to the element in 'ranges' that - corresponds to the returned row. - - @note - - Record is read into table->record[0]. - - *found_range_p returns a valid value only if read_multi_range_next() - returns 0. - - @retval - 0 OK, found a row - @retval - HA_ERR_END_OF_FILE No (more) rows in range - @retval - \# Error code -*/ -int handler::read_multi_range_next(KEY_MULTI_RANGE **found_range_p) -{ - int result; - DBUG_ENTER("handler::read_multi_range_next"); - - /* We should not be called after the last call returned EOF. */ - DBUG_ASSERT(multi_range_curr < multi_range_end); - - do - { - /* Save a call if there can be only one row in range. */ - if (multi_range_curr->range_flag != (UNIQUE_RANGE | EQ_RANGE)) - { - result= read_range_next(); - - /* On success or non-EOF errors jump to the end. */ - if (result != HA_ERR_END_OF_FILE) - break; - } - else - { - if (was_semi_consistent_read()) - goto scan_it_again; - /* - We need to set this for the last range only, but checking this - condition is more expensive than just setting the result code. - */ - result= HA_ERR_END_OF_FILE; - } - - multi_range_curr++; -scan_it_again: - /* Try the next range(s) until one matches a record. */ - for (; multi_range_curr < multi_range_end; multi_range_curr++) - { - result= read_range_first(multi_range_curr->start_key.keypart_map ? - &multi_range_curr->start_key : 0, - multi_range_curr->end_key.keypart_map ? - &multi_range_curr->end_key : 0, - test(multi_range_curr->range_flag & EQ_RANGE), - multi_range_sorted); - if (result != HA_ERR_END_OF_FILE) - break; - } - } - while ((result == HA_ERR_END_OF_FILE) && - (multi_range_curr < multi_range_end)); - - *found_range_p= multi_range_curr; - DBUG_PRINT("exit",("handler::read_multi_range_next: result %d", result)); - DBUG_RETURN(result); -} -#endif /** Read first row between two ranges. === modified file 'sql/handler.h' --- a/sql/handler.h 2009-12-15 14:53:30 +0000 +++ b/sql/handler.h 2009-12-15 17:23:55 +0000 @@ -1290,15 +1290,6 @@ ha_statistics stats; - /** The following are for read_multi_range */ -//////psergey: was: -#if 0 - bool multi_range_sorted; - KEY_MULTI_RANGE *multi_range_curr; - KEY_MULTI_RANGE *multi_range_end; - HANDLER_BUFFER *multi_range_buffer; -#endif -//////psergey /** MultiRangeRead-related members: */ range_seq_t mrr_iter; /* Interator to traverse the range sequence */ RANGE_SEQ_IF mrr_funcs; /* Range sequence traversal functions */ @@ -1311,7 +1302,6 @@ bool mrr_have_range; /** Current range (the one we're now returning rows from) */ KEY_MULTI_RANGE mrr_cur_range; -//////psergey /** The following are for read_range() */ key_range save_end_range, *end_range; @@ -1744,13 +1734,6 @@ update_index_statistics(); return error; } -#if 0 - virtual int read_multi_range_first(KEY_MULTI_RANGE **found_range_p, - KEY_MULTI_RANGE *ranges, uint range_count, - bool sorted, HANDLER_BUFFER *buffer); - virtual int read_multi_range_next(KEY_MULTI_RANGE **found_range_p); -#endif - //psergey-mrr: virtual ha_rows multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, void *seq_init_param, uint n_ranges, uint *bufsz, @@ -1761,7 +1744,6 @@ uint n_ranges, uint mode, HANDLER_BUFFER *buf); virtual int multi_range_read_next(char **range_info); - //psergey-mrr: ends virtual int read_range_first(const key_range *start_key, const key_range *end_key, bool eq_range, bool sorted); === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2009-12-15 07:16:46 +0000 +++ b/sql/sql_select.cc 2009-12-15 17:23:55 +0000 @@ -16734,11 +16734,12 @@ if (keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno && table->file->pushed_idx_cond) extra.append(STRING_WITH_LEN("; Using index condition")); - /** - psergey-mrr: + /* + psergey: enable the below when we backport BKA: + else if (tab->cache_idx_cond) extra.append(STRING_WITH_LEN("; Using index condition(BKA)")); - **/ + */ if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT || quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2009-12-15 07:16:46 +0000 +++ b/sql/sql_select.h 2009-12-15 17:23:55 +0000 @@ -780,7 +780,4 @@ void eliminate_tables(JOIN *join); -/// psergey-mrr: -void push_index_cond(JOIN_TAB *tab, uint keyno, bool other_tbls_ok); - [View Less]

1 0

[Maria-developers] Rev 2734: Backport into MariaDB-5.2 the following: in file:///home/psergey/dev/maria-5.2-dsmrr/
by Sergey Petrunya 15 Dec '09

15 Dec '09

At file:///home/psergey/dev/maria-5.2-dsmrr/ ------------------------------------------------------------ revno: 2734 revision-id: psergey(a)askmonty.org-20091215145330-oqv8jlxh36sl863m parent: psergey(a)askmonty.org-20091215071646-gbq7406d2xpopd82 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.2-dsmrr timestamp: Tue 2009-12-15 17:53:30 +0300 message: Backport into MariaDB-5.2 the following: WL#2474 "Multi Range Read: Change the default MRR implementation to … [View More]

1 0

[Maria-developers] Rev 2733: Backport into MariaDB-5.2 the following: in file:///home/psergey/dev/maria-5.2-dsmrr/
by Sergey Petrunya 15 Dec '09

15 Dec '09

At file:///home/psergey/dev/maria-5.2-dsmrr/ ------------------------------------------------------------ revno: 2733 revision-id: psergey(a)askmonty.org-20091215071646-gbq7406d2xpopd82 parent: igor(a)askmonty.org-20091112043128-yd6odg8zr5ribjal committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.2-dsmrr timestamp: Tue 2009-12-15 10:16:46 +0300 message: Backport into MariaDB-5.2 the following: WL#2474 "Multi Range Read: Change the default MRR implementation to … [View More]

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (knielsen:2776)
by knielsen＠knielsen-hq.org 14 Dec '09

14 Dec '09

#At lp:maria 2776 knielsen(a)knielsen-hq.org 2009-12-14 [merge] Merge latest PBXT fixes from Paul. modified: storage/pbxt/src/myxt_xt.cc === modified file 'storage/pbxt/src/myxt_xt.cc' --- a/storage/pbxt/src/myxt_xt.cc 2009-12-01 09:50:46 +0000 +++ b/storage/pbxt/src/myxt_xt.cc 2009-12-10 11:36:05 +0000 @@ -3041,14 +3041,6 @@ xtPublic MX_CHARSET_INFO *myxt_getcharse return (MX_CHARSET_INFO *)&my_charset_utf8_general_ci; } -#ifdef DBUG_OFF -//typedef struct … [View More]st_plugin_int *plugin_ref; -#define REF_MYSQL_PLUGIN(x) (x) -#else -//typedef struct st_plugin_int **plugin_ref; -#define REF_MYSQL_PLUGIN(x) (*(x)) -#endif - xtPublic void *myxt_create_thread() { #ifdef DRIZZLED @@ -3109,14 +3101,18 @@ xtPublic void *myxt_create_thread() /* * If PBXT is the default storage engine, then creating any THD objects will add extra - * references to the PBXT plugin object and will effectively deadlock the plugin so - * that server will have to force plugin shutdown. To avoid deadlocking and forced shutdown - * we must dereference the plugin after creating THD objects. + * references to the PBXT plugin object. because the threads are created but PBXT + * this creates a self reference, and the reference count does not go to zero + * on shutdown. + * + * The server then issues a message that it is forcing shutdown of the plugin. + * + * However, the engine reference is not required by the THDs used by PBXT, so + * I just remove them here. */ - LEX_STRING& plugin_name = REF_MYSQL_PLUGIN(new_thd->variables.table_plugin)->name; - if ((plugin_name.length == 4) && (strncmp(plugin_name.str, "PBXT", plugin_name.length) == 0)) { - REF_MYSQL_PLUGIN(new_thd->variables.table_plugin)->ref_count--; - } + plugin_unlock(NULL, new_thd->variables.table_plugin); + new_thd->variables.table_plugin = NULL; + new_thd->thread_stack = (char *) &new_thd; new_thd->store_globals(); lex_start(new_thd); @@ -3151,17 +3147,6 @@ xtPublic void myxt_destroy_thread(void * #else close_thread_tables(thd); #endif - - /* - * In myxt_create_thread we decremented plugin ref-count to avoid dead-locking. - * Here we need to increment ref-count to avoid assertion failures. - */ - if (thd->variables.table_plugin) { - LEX_STRING& plugin_name = REF_MYSQL_PLUGIN(thd->variables.table_plugin)->name; - if ((plugin_name.length == 4) && (strncmp(plugin_name.str, "PBXT", plugin_name.length) == 0)) { - REF_MYSQL_PLUGIN(thd->variables.table_plugin)->ref_count++; - } - } delete thd; [View Less]

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (knielsen:2784)
by knielsen＠knielsen-hq.org 13 Dec '09

13 Dec '09

#At lp:maria 2784 knielsen(a)knielsen-hq.org 2009-12-13 Fix source tarball name and 99 char path limitation. Fix obsolete automake usage in AM_INIT_AUTOMAKE so we can use the option to use tar version without 99 char pathname limit. modified: configure.in win/configure.js win/make_mariadb_win_dist per-file messages: configure.in Fix obsolete usage of AM_INIT_AUTOMAKE. Set option to use tar file format without 99 char lenght … [View More]

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (knielsen:2784)
by knielsen＠knielsen-hq.org 12 Dec '09

12 Dec '09

#At lp:maria 2784 knielsen(a)knielsen-hq.org 2009-12-12 Fix source tarball name and 99 char path limitation. Fix obsolete automake usage in AM_INIT_AUTOMAKE so we can use the option to use tar version without 99 char pathname limit. Change package name to get the source tarball to be named mariadb- not mysql-. Remove extra -MariaDB version string; we can instead change the package name and use --with-server-suffix when building packages.… [View More] modified: CMakeLists.txt Makefile.am configure.in include/config-netware.h include/config-win.h scripts/Makefile.am support-files/Makefile.am win/configure.js win/make_mariadb_win_dist per-file messages: CMakeLists.txt PACKAGE macro should not be needed. Makefile.am Fix tarball path name. configure.in Fix obsolete usage of AM_INIT_AUTOMAKE. Change package name to mariadb to get proper naming of source tarball. Set option to use tar file format without 99 char lenght limit. include/config-netware.h PACKAGE macro should not be needed. include/config-win.h PACKAGE macro should not be needed. scripts/Makefile.am The path should be /mysql not /mariadb for compatibility reasons. support-files/Makefile.am The path should be /mysql not /mariadb for compatibility reasons. win/configure.js Update parsing of configure.in win/make_mariadb_win_dist Update parsing of configure.in === modified file 'CMakeLists.txt' --- a/CMakeLists.txt 2009-12-03 11:19:05 +0000 +++ b/CMakeLists.txt 2009-12-11 23:26:09 +0000 @@ -36,7 +36,6 @@ ADD_DEFINITIONS(-DDEFAULT_MYSQL_HOME="c: ADD_DEFINITIONS(-DDEFAULT_BASEDIR="c:/Program Files/MySQL/") ADD_DEFINITIONS(-DMYSQL_DATADIR="c:/Program Files/MySQL/MySQL Server ${MYSQL_BASE_VERSION}/data") ADD_DEFINITIONS(-DDEFAULT_CHARSET_HOME="c:/Program Files/MySQL/MySQL Server ${MYSQL_BASE_VERSION}/") -ADD_DEFINITIONS(-DPACKAGE=mysql) ADD_DEFINITIONS(-DSHAREDIR="share") # Set debug options === modified file 'Makefile.am' --- a/Makefile.am 2009-12-03 11:19:05 +0000 +++ b/Makefile.am 2009-12-11 23:26:09 +0000 @@ -63,7 +63,7 @@ dist-hook: # Simple target to allow scripts etc. to get the name of the source # tarball easily. show-dist-name: - @echo "$(PACKAGE)-$(VERSION)" + @echo "$(PACKAGE_TARNAME)-$(VERSION)" all-local: @ABI_CHECK@ === modified file 'configure.in' --- a/configure.in 2009-12-03 11:34:11 +0000 +++ b/configure.in 2009-12-11 23:26:09 +0000 @@ -3,8 +3,6 @@ dnl Process this file with autoconf to p AC_PREREQ(2.52)dnl Minimum Autoconf version required. -AC_INIT(sql/mysqld.cc) -AC_CANONICAL_SYSTEM # The Docs Makefile.am parses this line! # remember to also update version.c in ndb # @@ -15,7 +13,12 @@ AC_CANONICAL_SYSTEM # MySQL version number. # # Note: the following line must be parseable by win/configure.js:GetVersion() -AM_INIT_AUTOMAKE(mysql, 5.1.41-MariaDB-beta) +# and by win/make_mariadb_win_dist +AC_INIT([MariaDB], [5.1.41-beta]) +AC_CONFIG_SRCDIR(sql/mysqld.cc) +AC_CANONICAL_SYSTEM + +AM_INIT_AUTOMAKE([tar-ustar]) AM_CONFIG_HEADER([include/config.h:config.h.in]) PROTOCOL_VERSION=10 === modified file 'include/config-netware.h' --- a/include/config-netware.h 2009-07-31 19:28:15 +0000 +++ b/include/config-netware.h 2009-12-11 23:26:09 +0000 @@ -127,7 +127,6 @@ extern "C" { /* default directory information */ #define DEFAULT_MYSQL_HOME "sys:/mysql" -#define PACKAGE "mysql" #define DEFAULT_BASEDIR "sys:/" #define SHAREDIR "share/" #define DEFAULT_CHARSET_HOME "sys:/mysql/" === modified file 'include/config-win.h' --- a/include/config-win.h 2009-09-07 20:50:10 +0000 +++ b/include/config-win.h 2009-12-11 23:26:09 +0000 @@ -348,7 +348,6 @@ inline ulonglong double2ulonglong(double #ifndef CMAKE_CONFIGD #define DEFAULT_MYSQL_HOME "c:\\mysql" #define MYSQL_DATADIR "c:\\mysql\\data" -#define PACKAGE "mysql" #define DEFAULT_BASEDIR "C:\\" #define SHAREDIR "share" #define DEFAULT_CHARSET_HOME "C:/mysql/" === modified file 'scripts/Makefile.am' --- a/scripts/Makefile.am 2008-12-10 09:02:25 +0000 +++ b/scripts/Makefile.am 2009-12-11 23:26:09 +0000 @@ -96,7 +96,7 @@ CLEANFILES = @server_scripts@ \ pkgplugindir = $(pkglibdir)/plugin # Default same as 'pkgdatadir', but we can override it -pkgsuppdir = $(datadir)/@PACKAGE@ +pkgsuppdir = $(datadir)/mysql # mysqlbug should be distributed built so that people can report build # failures with it. === modified file 'support-files/Makefile.am' --- a/support-files/Makefile.am 2009-10-23 16:48:54 +0000 +++ b/support-files/Makefile.am 2009-12-11 23:26:09 +0000 @@ -38,7 +38,7 @@ EXTRA_DIST = mysql.spec.sh \ SUBDIRS = MacOSX RHEL4-SElinux # Default same as 'pkgdatadir', but we can override it -pkgsuppdir = $(datadir)/@PACKAGE@ +pkgsuppdir = $(datadir)/mysql pkgsupp_DATA = my-small.cnf \ my-medium.cnf \ === modified file 'win/configure.js' --- a/win/configure.js 2009-10-07 21:00:29 +0000 +++ b/win/configure.js 2009-12-11 23:26:09 +0000 @@ -155,8 +155,8 @@ function GetValue(str, key) function GetVersion(str) { - var key = "AM_INIT_AUTOMAKE(mysql, "; - var key2 = "AM_INIT_AUTOMAKE(mariadb, "; + var key = "AC_INIT([mysql], ["; + var key2 = "AC_INIT([mariadb], ["; var key_len = key.length; var pos = str.indexOf(key); //5.0.6-beta) if (pos == -1) @@ -166,7 +166,7 @@ function GetVersion(str) } if (pos == -1) return null; pos += key_len; - var end = str.indexOf(")", pos); + var end = str.indexOf("]", pos); if (end == -1) return null; return str.substring(pos, end); } === modified file 'win/make_mariadb_win_dist' --- a/win/make_mariadb_win_dist 2009-10-30 10:50:48 +0000 +++ b/win/make_mariadb_win_dist 2009-12-11 23:26:09 +0000 @@ -25,7 +25,7 @@ devenv.com MySQL.sln /build Debug # TODO extract version number VER=`cat configure.in | - perl -e 'while (<>) { if (/^AM_INIT_AUTOMAKE\([a-z ]*, *([0-9a-z\.-]+)/) { print "$1\n"; exit(0)} } ; exit 1'` + perl -e 'while (<>) { if (/^AC_INIT\(\[[a-z ]*\], *\[([0-9a-z\.-]+)\]/) { print "$1\n"; exit(0)} } ; exit 1'` echo Version string: $VER. [View Less]

1 0

[Maria-developers] Updated (by Sanja): Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache (66)
by worklog-noreply＠askmonty.org 11 Dec '09

11 Dec '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache CREATION DATE..: Wed, 25 Nov 2009, 22:25 SUPERVISOR.....: Monty IMPLEMENTOR....: Sanja COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 66 (http://askmonty.org/worklog/?tid=… [View More]66) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Sanja - Fri, 11 Dec 2009, 15:09)=-=- High-Level Specification modified. --- /tmp/wklog.66.old.28164 2009-12-11 15:09:15.000000000 +0200 +++ /tmp/wklog.66.new.28164 2009-12-11 15:09:15.000000000 +0200 @@ -3,4 +3,4 @@ To check/discuss: -Are there sens to put subquery cache on all levels of subqueries of on highest. + To put subquery cache on all levels of subqueries or on highest level only. -=-=(Sanja - Fri, 11 Dec 2009, 15:08)=-=- Low Level Design modified. --- /tmp/wklog.66.old.28072 2009-12-11 15:08:16.000000000 +0200 +++ /tmp/wklog.66.new.28072 2009-12-11 15:08:16.000000000 +0200 @@ -1 +1,6 @@ +All items on which subquery depend could be collected in +st_select_lex::mark_as_dependent (direct of indirect reference?) + +Temporary table index should be created by all fields except result field +(TMP_TABLE_PARAM::keyinfo). -=-=(Sanja - Fri, 11 Dec 2009, 15:05)=-=- High-Level Specification modified. --- /tmp/wklog.66.old.27795 2009-12-11 15:05:04.000000000 +0200 +++ /tmp/wklog.66.new.27795 2009-12-11 15:05:04.000000000 +0200 @@ -1 +1,6 @@ +Attach subquery cache to each Item_subquery. Interface should allow to use hash +or temporary table inside. + +To check/discuss: +Are there sens to put subquery cache on all levels of subqueries of on highest. DESCRIPTION: Collect all outer items/references (left part of the subquiery and outer references inside the subquery) in key string. Compare the string (which represents certain value set of the references) against values in hash table and return cached result of subquery if the reference values combination has already been used. For example in the following subquery: (L1, L2) IN (SELECT A, B FROM T WHERE T.F1>OTER_FIELD) set of references to look into the subquery cache is (L1, L2, OTER_FIELD). The subquery cache should be implemented as simple LRU connected to the subquery. Size of the subquery cache (in number of results (but maybe in used memory amount)) is limited by session variable (query parameter?). HIGH-LEVEL SPECIFICATION: Attach subquery cache to each Item_subquery. Interface should allow to use hash or temporary table inside. To check/discuss: To put subquery cache on all levels of subqueries or on highest level only. LOW-LEVEL DESIGN: All items on which subquery depend could be collected in st_select_lex::mark_as_dependent (direct of indirect reference?) Temporary table index should be created by all fields except result field (TMP_TABLE_PARAM::keyinfo). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9) [View Less]

1 0

[Maria-developers] Updated (by Sanja): Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache (66)
by worklog-noreply＠askmonty.org 11 Dec '09

11 Dec '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache CREATION DATE..: Wed, 25 Nov 2009, 22:25 SUPERVISOR.....: Monty IMPLEMENTOR....: Sanja COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 66 (http://askmonty.org/worklog/?tid=… [View More]66) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Sanja - Fri, 11 Dec 2009, 15:09)=-=- High-Level Specification modified. --- /tmp/wklog.66.old.28164 2009-12-11 15:09:15.000000000 +0200 +++ /tmp/wklog.66.new.28164 2009-12-11 15:09:15.000000000 +0200 @@ -3,4 +3,4 @@ To check/discuss: -Are there sens to put subquery cache on all levels of subqueries of on highest. + To put subquery cache on all levels of subqueries or on highest level only. -=-=(Sanja - Fri, 11 Dec 2009, 15:08)=-=- Low Level Design modified. --- /tmp/wklog.66.old.28072 2009-12-11 15:08:16.000000000 +0200 +++ /tmp/wklog.66.new.28072 2009-12-11 15:08:16.000000000 +0200 @@ -1 +1,6 @@ +All items on which subquery depend could be collected in +st_select_lex::mark_as_dependent (direct of indirect reference?) + +Temporary table index should be created by all fields except result field +(TMP_TABLE_PARAM::keyinfo). -=-=(Sanja - Fri, 11 Dec 2009, 15:05)=-=- High-Level Specification modified. --- /tmp/wklog.66.old.27795 2009-12-11 15:05:04.000000000 +0200 +++ /tmp/wklog.66.new.27795 2009-12-11 15:05:04.000000000 +0200 @@ -1 +1,6 @@ +Attach subquery cache to each Item_subquery. Interface should allow to use hash +or temporary table inside. + +To check/discuss: +Are there sens to put subquery cache on all levels of subqueries of on highest. DESCRIPTION: Collect all outer items/references (left part of the subquiery and outer references inside the subquery) in key string. Compare the string (which represents certain value set of the references) against values in hash table and return cached result of subquery if the reference values combination has already been used. For example in the following subquery: (L1, L2) IN (SELECT A, B FROM T WHERE T.F1>OTER_FIELD) set of references to look into the subquery cache is (L1, L2, OTER_FIELD). The subquery cache should be implemented as simple LRU connected to the subquery. Size of the subquery cache (in number of results (but maybe in used memory amount)) is limited by session variable (query parameter?). HIGH-LEVEL SPECIFICATION: Attach subquery cache to each Item_subquery. Interface should allow to use hash or temporary table inside. To check/discuss: To put subquery cache on all levels of subqueries or on highest level only. LOW-LEVEL DESIGN: All items on which subquery depend could be collected in st_select_lex::mark_as_dependent (direct of indirect reference?) Temporary table index should be created by all fields except result field (TMP_TABLE_PARAM::keyinfo). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9) [View Less]

1 0

[Maria-developers] Updated (by Sanja): Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache (66)
by worklog-noreply＠askmonty.org 11 Dec '09

11 Dec '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache CREATION DATE..: Wed, 25 Nov 2009, 22:25 SUPERVISOR.....: Monty IMPLEMENTOR....: Sanja COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 66 (http://askmonty.org/worklog/?tid=… [View More]66) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Sanja - Fri, 11 Dec 2009, 15:08)=-=- Low Level Design modified. --- /tmp/wklog.66.old.28072 2009-12-11 15:08:16.000000000 +0200 +++ /tmp/wklog.66.new.28072 2009-12-11 15:08:16.000000000 +0200 @@ -1 +1,6 @@ +All items on which subquery depend could be collected in +st_select_lex::mark_as_dependent (direct of indirect reference?) + +Temporary table index should be created by all fields except result field +(TMP_TABLE_PARAM::keyinfo). -=-=(Sanja - Fri, 11 Dec 2009, 15:05)=-=- High-Level Specification modified. --- /tmp/wklog.66.old.27795 2009-12-11 15:05:04.000000000 +0200 +++ /tmp/wklog.66.new.27795 2009-12-11 15:05:04.000000000 +0200 @@ -1 +1,6 @@ +Attach subquery cache to each Item_subquery. Interface should allow to use hash +or temporary table inside. + +To check/discuss: +Are there sens to put subquery cache on all levels of subqueries of on highest. DESCRIPTION: Collect all outer items/references (left part of the subquiery and outer references inside the subquery) in key string. Compare the string (which represents certain value set of the references) against values in hash table and return cached result of subquery if the reference values combination has already been used. For example in the following subquery: (L1, L2) IN (SELECT A, B FROM T WHERE T.F1>OTER_FIELD) set of references to look into the subquery cache is (L1, L2, OTER_FIELD). The subquery cache should be implemented as simple LRU connected to the subquery. Size of the subquery cache (in number of results (but maybe in used memory amount)) is limited by session variable (query parameter?). HIGH-LEVEL SPECIFICATION: Attach subquery cache to each Item_subquery. Interface should allow to use hash or temporary table inside. To check/discuss: Are there sens to put subquery cache on all levels of subqueries of on highest. LOW-LEVEL DESIGN: All items on which subquery depend could be collected in st_select_lex::mark_as_dependent (direct of indirect reference?) Temporary table index should be created by all fields except result field (TMP_TABLE_PARAM::keyinfo). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9) [View Less]

1 0

[Maria-developers] Updated (by Sanja): Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache (66)
by worklog-noreply＠askmonty.org 11 Dec '09

11 Dec '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Subquery optimization: Avoid recalculating subquery if external fields values found in subquery cache CREATION DATE..: Wed, 25 Nov 2009, 22:25 SUPERVISOR.....: Monty IMPLEMENTOR....: Sanja COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 66 (http://askmonty.org/worklog/?tid=… [View More]66) VERSION........: Server-5.2 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Sanja - Fri, 11 Dec 2009, 15:08)=-=- Low Level Design modified. --- /tmp/wklog.66.old.28072 2009-12-11 15:08:16.000000000 +0200 +++ /tmp/wklog.66.new.28072 2009-12-11 15:08:16.000000000 +0200 @@ -1 +1,6 @@ +All items on which subquery depend could be collected in +st_select_lex::mark_as_dependent (direct of indirect reference?) + +Temporary table index should be created by all fields except result field +(TMP_TABLE_PARAM::keyinfo). -=-=(Sanja - Fri, 11 Dec 2009, 15:05)=-=- High-Level Specification modified. --- /tmp/wklog.66.old.27795 2009-12-11 15:05:04.000000000 +0200 +++ /tmp/wklog.66.new.27795 2009-12-11 15:05:04.000000000 +0200 @@ -1 +1,6 @@ +Attach subquery cache to each Item_subquery. Interface should allow to use hash +or temporary table inside. + +To check/discuss: +Are there sens to put subquery cache on all levels of subqueries of on highest. DESCRIPTION: Collect all outer items/references (left part of the subquiery and outer references inside the subquery) in key string. Compare the string (which represents certain value set of the references) against values in hash table and return cached result of subquery if the reference values combination has already been used. For example in the following subquery: (L1, L2) IN (SELECT A, B FROM T WHERE T.F1>OTER_FIELD) set of references to look into the subquery cache is (L1, L2, OTER_FIELD). The subquery cache should be implemented as simple LRU connected to the subquery. Size of the subquery cache (in number of results (but maybe in used memory amount)) is limited by session variable (query parameter?). HIGH-LEVEL SPECIFICATION: Attach subquery cache to each Item_subquery. Interface should allow to use hash or temporary table inside. To check/discuss: Are there sens to put subquery cache on all levels of subqueries of on highest. LOW-LEVEL DESIGN: All items on which subquery depend could be collected in st_select_lex::mark_as_dependent (direct of indirect reference?) Temporary table index should be created by all fields except result field (TMP_TABLE_PARAM::keyinfo). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9) [View Less]

1 0