319 lines
10 KiB
Plaintext
319 lines
10 KiB
Plaintext
select * from information_schema.engines where engine = 'xengine';
|
|
ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS
|
|
XENGINE DEFAULT X-Engine storage engine YES YES YES
|
|
drop table if exists t0,t1,t2,t3,t4,t5;
|
|
create table t0 (a int);
|
|
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
create table t1(a int);
|
|
insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
|
|
create table t2 (
|
|
pk int not null,
|
|
a int not null,
|
|
b int not null,
|
|
primary key(pk),
|
|
key(a) comment 'rev:cf1'
|
|
) engine=xengine;
|
|
insert into t2 select A.a, FLOOR(A.a/10), A.a from t1 A;
|
|
#
|
|
# HA_READ_KEY_EXACT tests
|
|
#
|
|
# Original failure was here:
|
|
explain
|
|
select * from t2 force index (a) where a=0;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL ref a a 4 const # 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` FORCE INDEX (`a`) where (`test`.`t2`.`a` = 0)
|
|
select * from t2 force index (a) where a=0;
|
|
pk a b
|
|
0 0 0
|
|
1 0 1
|
|
2 0 2
|
|
3 0 3
|
|
4 0 4
|
|
5 0 5
|
|
6 0 6
|
|
7 0 7
|
|
8 0 8
|
|
9 0 9
|
|
# The rest are for code coverage:
|
|
explain
|
|
select * from t2 force index (a) where a=2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL ref a a 4 const # 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` FORCE INDEX (`a`) where (`test`.`t2`.`a` = 2)
|
|
select * from t2 force index (a) where a=2;
|
|
pk a b
|
|
20 2 20
|
|
21 2 21
|
|
22 2 22
|
|
23 2 23
|
|
24 2 24
|
|
25 2 25
|
|
26 2 26
|
|
27 2 27
|
|
28 2 28
|
|
29 2 29
|
|
explain
|
|
select * from t2 force index (a) where a=3 and pk=33;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL const a a 8 const,const # 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` FORCE INDEX (`a`) where ((`test`.`t2`.`pk` = 33) and (`test`.`t2`.`a` = 3))
|
|
select * from t2 force index (a) where a=3 and pk=33;
|
|
pk a b
|
|
33 3 33
|
|
select * from t2 force index (a) where a=99 and pk=99;
|
|
pk a b
|
|
select * from t2 force index (a) where a=0 and pk=0;
|
|
pk a b
|
|
0 0 0
|
|
select * from t2 force index (a) where a=-1;
|
|
pk a b
|
|
select * from t2 force index (a) where a=-1 and pk in (101,102);
|
|
pk a b
|
|
select * from t2 force index (a) where a=100 and pk in (101,102);
|
|
pk a b
|
|
#
|
|
# #36: Range in form tbl.key >= const doesn't work in reverse column family
|
|
#
|
|
explain
|
|
select count(*) from t2 force index (a) where a>=0 and a <=1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL range a a 4 NULL # 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t2` FORCE INDEX (`a`) where ((`test`.`t2`.`a` >= 0) and (`test`.`t2`.`a` <= 1))
|
|
select count(*) from t2 force index (a) where a>=0 and a <=1;
|
|
count(*)
|
|
20
|
|
explain
|
|
select count(*) from t2 force index (a) where a>=-1 and a <=1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL range a a 4 NULL # 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t2` FORCE INDEX (`a`) where ((`test`.`t2`.`a` >= <cache>(-(1))) and (`test`.`t2`.`a` <= 1))
|
|
select count(*) from t2 force index (a) where a>=-1 and a <=1;
|
|
count(*)
|
|
20
|
|
explain
|
|
select * from t2 force index (a) where a=0 and pk>=3;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL range a a 8 NULL # 100.00 Using index condition
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` FORCE INDEX (`a`) where ((`test`.`t2`.`a` = 0) and (`test`.`t2`.`pk` >= 3))
|
|
select * from t2 force index (a) where a=0 and pk>=3;
|
|
pk a b
|
|
3 0 3
|
|
4 0 4
|
|
5 0 5
|
|
6 0 6
|
|
7 0 7
|
|
8 0 8
|
|
9 0 9
|
|
# Try edge cases where we fall over the end of the table
|
|
create table t3 like t2;
|
|
insert into t3 select * from t2;
|
|
select * from t3 where pk>=1000000;
|
|
pk a b
|
|
select * from t2 where pk>=1000000;
|
|
pk a b
|
|
#
|
|
# #42: Range in form tbl.key > const doesn't work in reverse column family
|
|
#
|
|
explain
|
|
select count(*) from t2 force index (a) where a>0;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL range a a 4 NULL # 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t2` FORCE INDEX (`a`) where (`test`.`t2`.`a` > 0)
|
|
select count(*) from t2 force index (a) where a>0;
|
|
count(*)
|
|
990
|
|
explain
|
|
select count(*) from t2 force index (a) where a>99;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL range a a 4 NULL # 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t2` FORCE INDEX (`a`) where (`test`.`t2`.`a` > 99)
|
|
select count(*) from t2 force index (a) where a>99;
|
|
count(*)
|
|
0
|
|
select * from t2 where pk>1000000;
|
|
pk a b
|
|
select * from t3 where pk>1000000;
|
|
pk a b
|
|
explain
|
|
select count(*) from t2 force index (a) where a=2 and pk>25;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL range a a 8 NULL # 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t2` FORCE INDEX (`a`) where ((`test`.`t2`.`a` = 2) and (`test`.`t2`.`pk` > 25))
|
|
select count(*) from t2 force index (a) where a=2 and pk>25;
|
|
count(*)
|
|
4
|
|
select * from t2 force index (a) where a>-10 and a < 1;
|
|
pk a b
|
|
0 0 0
|
|
1 0 1
|
|
2 0 2
|
|
3 0 3
|
|
4 0 4
|
|
5 0 5
|
|
6 0 6
|
|
7 0 7
|
|
8 0 8
|
|
9 0 9
|
|
select * from t3 force index (a) where a>-10 and a < 1;
|
|
pk a b
|
|
0 0 0
|
|
1 0 1
|
|
2 0 2
|
|
3 0 3
|
|
4 0 4
|
|
5 0 5
|
|
6 0 6
|
|
7 0 7
|
|
8 0 8
|
|
9 0 9
|
|
#
|
|
# #46: index_read_map(HA_READ_BEFORE_KEY) does not work in reverse column family
|
|
#
|
|
select max(a) from t2 where a < 2;
|
|
max(a)
|
|
1
|
|
select max(a) from t2 where a < -1;
|
|
max(a)
|
|
NULL
|
|
select max(pk) from t2 where a=3 and pk < 6;
|
|
max(pk)
|
|
NULL
|
|
select max(pk) from t2 where pk < 200000;
|
|
max(pk)
|
|
999
|
|
select max(pk) from t2 where pk < 20;
|
|
max(pk)
|
|
19
|
|
select max(a) from t3 where a < 2;
|
|
max(a)
|
|
1
|
|
select max(a) from t3 where a < -1;
|
|
max(a)
|
|
NULL
|
|
select max(pk) from t3 where pk < 200000;
|
|
max(pk)
|
|
999
|
|
select max(pk) from t3 where pk < 20;
|
|
max(pk)
|
|
19
|
|
select max(pk) from t2 where a=3 and pk < 33;
|
|
max(pk)
|
|
32
|
|
select max(pk) from t3 where a=3 and pk < 33;
|
|
max(pk)
|
|
32
|
|
#
|
|
# #48: index_read_map(HA_READ_PREFIX_LAST) does not work in reverse CF
|
|
#
|
|
# Tests for search_flag=HA_READ_PREFIX_LAST_OR_PREV
|
|
explain
|
|
select * from t2 where a between 99 and 2000 order by a desc;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t2 NULL range a a 4 NULL # 100.00 Using index condition; Backward index scan
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`pk` AS `pk`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` between 99 and 2000) order by `test`.`t2`.`a` desc
|
|
select * from t2 where a between 99 and 2000 order by a desc;
|
|
pk a b
|
|
999 99 999
|
|
998 99 998
|
|
997 99 997
|
|
996 99 996
|
|
995 99 995
|
|
994 99 994
|
|
993 99 993
|
|
992 99 992
|
|
991 99 991
|
|
990 99 990
|
|
select max(a) from t2 where a <=10;
|
|
max(a)
|
|
10
|
|
select max(a) from t2 where a <=-4;
|
|
max(a)
|
|
NULL
|
|
select max(pk) from t2 where a=5 and pk <=55;
|
|
max(pk)
|
|
55
|
|
select max(pk) from t2 where a=5 and pk <=55555;
|
|
max(pk)
|
|
59
|
|
select max(pk) from t2 where a=5 and pk <=0;
|
|
max(pk)
|
|
NULL
|
|
select max(pk) from t2 where pk <=-1;
|
|
max(pk)
|
|
NULL
|
|
select max(pk) from t2 where pk <=999999;
|
|
max(pk)
|
|
999
|
|
select max(pk) from t3 where pk <=-1;
|
|
max(pk)
|
|
NULL
|
|
select max(pk) from t3 where pk <=999999;
|
|
max(pk)
|
|
999
|
|
#
|
|
# Tests for search_flag=HA_READ_PREFIX_LAST
|
|
#
|
|
create table t4 (
|
|
pk int primary key,
|
|
a int,
|
|
b int,
|
|
c int,
|
|
key(a,b,c)
|
|
) engine=xengine;
|
|
insert into t4 select pk,pk,pk,pk from t2 where pk < 100;
|
|
explain
|
|
select * from t4 where a=1 and b in (1) order by c desc;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t4 NULL ref a a 10 const,const # 100.00 Backward index scan; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t4`.`pk` AS `pk`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t4` where ((`test`.`t4`.`b` = 1) and (`test`.`t4`.`a` = 1)) order by `test`.`t4`.`c` desc
|
|
select * from t4 where a=1 and b in (1) order by c desc;
|
|
pk a b c
|
|
1 1 1 1
|
|
explain
|
|
select * from t4 where a=5 and b in (4) order by c desc;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t4 NULL ref a a 10 const,const # 100.00 Backward index scan; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t4`.`pk` AS `pk`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t4` where ((`test`.`t4`.`b` = 4) and (`test`.`t4`.`a` = 5)) order by `test`.`t4`.`c` desc
|
|
select * from t4 where a=5 and b in (4) order by c desc;
|
|
pk a b c
|
|
# HA_READ_PREFIX_LAST for reverse-ordered CF
|
|
create table t5 (
|
|
pk int primary key,
|
|
a int,
|
|
b int,
|
|
c int,
|
|
key(a,b,c) comment 'rev:cf2'
|
|
) engine=xengine;
|
|
insert into t5 select pk,pk,pk,pk from t2 where pk < 100;
|
|
explain
|
|
select * from t5 where a=1 and b in (1) order by c desc;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t5 NULL ref a a 10 const,const # 100.00 Backward index scan; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t5`.`pk` AS `pk`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t5`.`c` AS `c` from `test`.`t5` where ((`test`.`t5`.`b` = 1) and (`test`.`t5`.`a` = 1)) order by `test`.`t5`.`c` desc
|
|
select * from t5 where a=1 and b in (1) order by c desc;
|
|
pk a b c
|
|
1 1 1 1
|
|
explain
|
|
select * from t5 where a=5 and b in (4) order by c desc;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t5 NULL ref a a 10 const,const # 100.00 Backward index scan; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t5`.`pk` AS `pk`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t5`.`c` AS `c` from `test`.`t5` where ((`test`.`t5`.`b` = 4) and (`test`.`t5`.`a` = 5)) order by `test`.`t5`.`c` desc
|
|
select * from t5 where a=5 and b in (4) order by c desc;
|
|
pk a b c
|
|
drop table t0,t1,t2,t3,t4,t5;
|