summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/maria_mrr.result337
-rw-r--r--mysql-test/t/maria_mrr.test57
-rw-r--r--storage/maria/ma_rnext_same.c2
3 files changed, 395 insertions, 1 deletions
diff --git a/mysql-test/r/maria_mrr.result b/mysql-test/r/maria_mrr.result
new file mode 100644
index 00000000000..e7c958943d8
--- /dev/null
+++ b/mysql-test/r/maria_mrr.result
@@ -0,0 +1,337 @@
+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;
+CREATE TABLE t1(
+pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL,
+PRIMARY KEY (pk), INDEX idx (v, i)
+) ENGINE=ARIA;
+INSERT INTO t1 VALUES
+(1,9,'x'), (2,5,'g'), (3,1,'o'), (4,0,'g'), (5,1,'v'),
+(6,190,'m'), (7,6,'x'), (8,3,'c'), (9,4,'z'), (10,3,'i'),
+(11,186,'x'), (12,1,'g'), (13,8,'q'), (14,226,'m'), (15,133,'p');
+CREATE TABLE t2(
+pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL,
+PRIMARY KEY (pk), INDEX idx (v, i)
+) ENGINE=ARIA;
+INSERT INTO t2 SELECT * FROM t1;
+INSERT INTO t2 VALUES (77, 333, 'z');
+CREATE TABLE t3(
+pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL,
+PRIMARY KEY (pk), INDEX idx (v, i)
+) ENGINE=ARIA;
+INSERT INTO t3 SELECT * FROM t1;
+INSERT INTO t3 VALUES (88, 442, 'y'), (99, 445, 'w') ;
+SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx)
+WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
+COUNT(t1.v)
+120
+EXPLAIN
+SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx)
+WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL idx 7 NULL 15 Using index
+1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 16 Using where; Using join buffer
+1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 17 Using where; Using join buffer
+SELECT COUNT(t1.v) FROM t1, t2, t3
+WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
+COUNT(t1.v)
+120
+EXPLAIN
+SELECT COUNT(t1.v) FROM t1, t2, t3
+WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL idx 7 NULL 15 Using index
+1 SIMPLE t2 ALL PRIMARY,idx NULL NULL NULL 16 Using where; Using join buffer
+1 SIMPLE t3 ref PRIMARY,idx idx 3 test.t2.v 2 Using index condition; Using where
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/t/maria_mrr.test b/mysql-test/t/maria_mrr.test
new file mode 100644
index 00000000000..a4bb8eb9f9e
--- /dev/null
+++ b/mysql-test/t/maria_mrr.test
@@ -0,0 +1,57 @@
+-- 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;
+
+#
+# Bug #665049: index condition pushdown with Maria
+#
+
+CREATE TABLE t1(
+ pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL,
+ PRIMARY KEY (pk), INDEX idx (v, i)
+) ENGINE=ARIA;
+INSERT INTO t1 VALUES
+ (1,9,'x'), (2,5,'g'), (3,1,'o'), (4,0,'g'), (5,1,'v'),
+ (6,190,'m'), (7,6,'x'), (8,3,'c'), (9,4,'z'), (10,3,'i'),
+ (11,186,'x'), (12,1,'g'), (13,8,'q'), (14,226,'m'), (15,133,'p');
+
+CREATE TABLE t2(
+ pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL,
+ PRIMARY KEY (pk), INDEX idx (v, i)
+) ENGINE=ARIA;
+INSERT INTO t2 SELECT * FROM t1;
+INSERT INTO t2 VALUES (77, 333, 'z');
+
+CREATE TABLE t3(
+ pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL,
+ PRIMARY KEY (pk), INDEX idx (v, i)
+) ENGINE=ARIA;
+INSERT INTO t3 SELECT * FROM t1;
+INSERT INTO t3 VALUES (88, 442, 'y'), (99, 445, 'w') ;
+
+SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx)
+ WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
+EXPLAIN
+SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx)
+ WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
+
+SELECT COUNT(t1.v) FROM t1, t2, t3
+ WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
+EXPLAIN
+ SELECT COUNT(t1.v) FROM t1, t2, t3
+ WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0;
+
+DROP TABLE t1,t2,t3;
+
+
+
+
diff --git a/storage/maria/ma_rnext_same.c b/storage/maria/ma_rnext_same.c
index 597d97fc78a..f67a76a366f 100644
--- a/storage/maria/ma_rnext_same.c
+++ b/storage/maria/ma_rnext_same.c
@@ -81,7 +81,7 @@ int maria_rnext_same(MARIA_HA *info, uchar *buf)
break;
}
/* Skip rows that are inserted by other threads since we got a lock */
- if ((info->s->row_is_visible)(info) ||
+ if ((info->s->row_is_visible)(info) &&
((icp_res= ma_check_index_cond(info, inx, buf)) != 0))
break;
}
View Lorry Controller Status