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;