1
# Test Routine for Multi Index Tables in BlitzDB
4
drop table if exists t1;
7
# Two Keys: Primary Key + Index
8
create table t1 (a int, b int, c int, primary key(a), index(b)) engine = blitzdb;
9
insert into t1 values (1, 1, 100), (2, 2, 200), (3, 3, 300), (4, 4, 400);
10
insert into t1 values (5, 5, 500), (6, 6, 600), (7, 7, 700), (8, 8, 800);
11
select * from t1 order by (a);
12
select * from t1 order by (a) desc;
15
explain select * from t1 where a <= 4;
16
select * from t1 where a <= 4;
17
explain select * from t1 where a > 2 and a < 6;
18
select * from t1 where a > 2 and a < 6;
21
explain select * from t1 where b <= 4;
22
select * from t1 where b <= 4;
23
explain select * from t1 where b > 2 and b < 6;
24
select * from t1 where b > 2 and b < 6;
26
# Needle in a Haystack
27
explain select c from t1 where a = 8;
28
select c from t1 where a = 8;
29
explain select c from t1 where b = 3;
30
select c from t1 where b = 3;
32
# Delete rows by PK and make sure it can't be fetched with
33
# the secondary index. Reason: Row(s) should not exist.
34
select * from t1 where a = 1;
35
select * from t1 where b = 1;
36
delete from t1 where a = 1;
37
select * from t1 where a = 1;
38
select * from t1 where b = 1;
40
select * from t1 where a >= 2 and a <= 4;
41
select * from t1 where b >= 2 and b <= 4;
42
delete from t1 where a >= 2 and a <= 4;
43
select * from t1 where a >= 2 and a <= 4;
44
select * from t1 where b >= 2 and b <= 4;