mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
443 lines
14 KiB
Text
443 lines
14 KiB
Text
drop table if exists t1,t2,t3,t4;
|
|
set @maria_mrr_tmp=@@optimizer_switch;
|
|
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
|
|
set @mrr_buffer_size_save= @@mrr_buffer_size;
|
|
set @save_storage_engine= @@default_storage_engine;
|
|
set default_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 COLLATE=latin1_swedish_ci 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
|
|
drop table if exists t4;
|
|
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; Rowid-ordered scan
|
|
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; Rowid-ordered scan
|
|
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);
|
|
Warnings:
|
|
Warning 1062 Duplicate entry '6-6' for key 'a'
|
|
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 default_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
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DECIMAL value: 'd'
|
|
Warning 1292 Truncated incorrect DECIMAL value: 'd'
|
|
Warning 1292 Truncated incorrect DECIMAL value: 'd'
|
|
Warning 1292 Truncated incorrect DECIMAL value: 'd'
|
|
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)
|
|
) 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'), (87, 442, 'z'), (98, 445, 'v'), (86, 442, 'x'),
|
|
(97, 445, 't'), (85, 442, 'b'), (96, 445, 'l'), (84, 442, 'a'), (95, 445, 'k');
|
|
set @save_join_cache_level=@@join_cache_level;
|
|
set join_cache_level=1;
|
|
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 (flat, BNL join)
|
|
1 SIMPLE t3 ALL PRIMARY NULL NULL NULL 25 Using where; Using join buffer (flat, BNL join)
|
|
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 (flat, BNL join)
|
|
1 SIMPLE t3 ref PRIMARY,idx idx 3 test.t2.v 2 Using index condition; Using where
|
|
set join_cache_level=@save_join_cache_level;
|
|
DROP TABLE t1,t2,t3;
|
|
#
|
|
# BUG#671361: virtual int Mrr_ordered_index_reader::refill_buffer(): Assertion `!know_key_tuple_params
|
|
# (works only on Maria because we need 1024-byte long key)
|
|
#
|
|
SET SESSION join_cache_level = 6;
|
|
SET SESSION join_buffer_size = 1024;
|
|
CREATE TABLE t1 (
|
|
pk int(11) NOT NULL AUTO_INCREMENT,
|
|
col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL,
|
|
PRIMARY KEY (pk),
|
|
KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key)
|
|
) ENGINE=Aria;
|
|
INSERT INTO t1 VALUES
|
|
(1,'z'), (2,'abcdefjhjkl'), (3,'in'), (4,'abcdefjhjkl'), (6,'abcdefjhjkl'),
|
|
(11,'zx'), (12,'abcdefjhjm'), (13,'jn'), (14,'abcdefjhjp'), (16,'abcdefjhjr');
|
|
CREATE TABLE t2 (
|
|
col_varchar_10_latin1 varchar(10) DEFAULT NULL
|
|
) ENGINE=Aria;
|
|
INSERT INTO t2 VALUES ('foo'), ('foo');
|
|
EXPLAIN SELECT count(*)
|
|
FROM t1 AS table1, t2 AS table2
|
|
WHERE
|
|
table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE table2 ALL NULL NULL NULL NULL 2 Using where
|
|
1 SIMPLE table1 ref PRIMARY,col_varchar_1024_latin1_key col_varchar_1024_latin1_key 1027 test.table2.col_varchar_10_latin1 2 Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
|
|
SELECT count(*)
|
|
FROM t1 AS table1, t2 AS table2
|
|
WHERE
|
|
table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ;
|
|
count(*)
|
|
0
|
|
drop table t1, t2;
|
|
#
|
|
# BUG#693747: Assertion multi_range_read.cc:908: int DsMrr_impl::dsmrr_init(
|
|
#
|
|
set @_save_join_cache_level= @@join_cache_level;
|
|
set @_save_join_buffer_size= @@join_buffer_size;
|
|
set join_cache_level=8;
|
|
set join_buffer_size=10240;
|
|
CREATE TABLE t1 (
|
|
f2 varchar(32) COLLATE latin1_swedish_ci,
|
|
f3 int(11),
|
|
f4 varchar(4096) COLLATE utf8_bin,
|
|
f5 varchar(4096) COLLATE latin1_bin,
|
|
KEY (f5)
|
|
) ENGINE=Aria TRANSACTIONAL=0 ;
|
|
Warnings:
|
|
Note 1071 Specified key was too long; max key length is 2300 bytes
|
|
# Fill the table with some data
|
|
SELECT alias2.* , alias1.f2
|
|
FROM
|
|
t1 AS alias1
|
|
LEFT JOIN t1 AS alias2 ON alias1.f2 = alias2.f5
|
|
WHERE
|
|
alias2.f3 < 0;
|
|
f2 f3 f4 f5 f2
|
|
set join_cache_level=@_save_join_cache_level;
|
|
set join_buffer_size=@_save_join_buffer_size;
|
|
set optimizer_switch=@maria_mrr_tmp;
|
|
drop table t1;
|