1
# Test routine for range queries using BlitzDB
4
drop table if exists t1;
10
create table t1 (a int, index(a)) engine = blitzdb;
12
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
13
insert into t1 values (11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
14
insert into t1 values (21),(22),(23),(24),(25),(26),(27),(28),(29),(30);
15
insert into t1 values (31),(32),(33),(34),(35),(36),(37),(38),(39),(40);
16
insert into t1 values (41),(42),(43),(44),(45),(46),(47),(48),(49),(50);
17
insert into t1 values (51),(52),(53),(54),(55),(56),(57),(58),(59),(60);
19
select count(*) from t1;
20
select max(a) from t1;
21
select min(a) from t1;
23
explain select * from t1 where a <= 30;
24
select * from t1 where a <= 30;
26
explain select * from t1 where a > 30;
27
select * from t1 where a > 30;
29
explain select * from t1 where a <= 10 limit 1;
30
select * from t1 where a <= 10 limit 1;
32
explain select * from t1 where a < 20 order by a desc;
33
select * from t1 where a < 20 order by a desc;
35
explain select * from t1 where a <= 10 order by a desc limit 1;
36
select * from t1 where a <= 10 order by a desc limit 1;
38
explain select * from t1 where a between 30 and 40;
39
select * from t1 where a between 30 and 40;
41
explain select * from t1 where a in (10, 20, 30, 40, 50, 60);
42
select * from t1 where a in (10, 20, 30, 40, 50, 60);
45
select * from t1 where a > 60;
46
select * from t1 where a < 0;
47
select * from t1 where a > 20 and a < 20;
48
select * from t1 where a is NULL;
51
# +--------------------+
52
# | INDEX TYPE: BIGINT |
53
# +--------------------+
54
create table t1 (a bigint, index(a)) engine = blitzdb;
56
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
57
insert into t1 values (11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
58
insert into t1 values (21),(22),(23),(24),(25),(26),(27),(28),(29),(30);
59
insert into t1 values (31),(32),(33),(34),(35),(36),(37),(38),(39),(40);
60
insert into t1 values (41),(42),(43),(44),(45),(46),(47),(48),(49),(50);
61
insert into t1 values (51),(52),(53),(54),(55),(56),(57),(58),(59),(60);
63
explain select * from t1 where a <= 30;
64
select * from t1 where a <= 30;
66
explain select * from t1 where a > 30;
67
select * from t1 where a > 30;
69
explain select * from t1 where a <= 10 limit 1;
70
select * from t1 where a <= 10 limit 1;
72
explain select * from t1 where a < 20 order by a desc;
73
select * from t1 where a < 20 order by a desc;
74
select * from t1 where a < 5 order by a desc;
76
explain select * from t1 where a <= 10 order by a desc limit 1;
77
select * from t1 where a <= 10 order by a desc limit 1;
79
explain select * from t1 where a between 30 and 40;
80
select * from t1 where a between 30 and 40;
82
explain select * from t1 where a in (10, 20, 30, 40, 50, 60);
83
select * from t1 where a in (10, 20, 30, 40, 50, 60);
86
select * from t1 where a > 60;
87
select * from t1 where a < 0;
88
select * from t1 where a > 20 and a < 20;
89
select * from t1 where a is NULL;
92
# +--------------------+
93
# | INDEX TYPE: DOUBLE |
94
# +--------------------+
95
create table t1 (a double, index(a)) engine = blitzdb;
97
insert into t1 values (1.4), (1.3), (0.9), (1.0), (1.2), (1.1);
101
explain select * from t1;
104
select * from t1 where a < 2.0;
105
select * from t1 where a > 0.01;
108
select * from t1 where a <= 1.1;
109
select * from t1 where a > 1.1;
112
select * from t1 where a in (0.9, 1.4, 1.1);
115
select * from t1 where a < 1.3 order by a;
116
select * from t1 where a < 1.3 order by a desc;
119
select * from t1 where a > 2.0;
120
select * from t1 where a < 0.01;
121
select * from t1 where a > 1.0 and a < 1.0;
122
select * from t1 where a is NULL;
126
# +------------------+
127
# | INDEX TYPE: DATE |
128
# +------------------+
129
create table t1 (a date, index(a)) engine = blitzdb;
131
insert into t1 values ('2000-07-10'), ('2000-07-11'), ('2000-07-12');
132
insert into t1 values ('2000-08-13'), ('2000-08-14'), ('2000-08-15');
133
insert into t1 values ('2001-07-10'), ('2001-07-11'), ('2001-07-12');
134
insert into t1 values ('2001-08-13'), ('2001-08-14'), ('2001-08-15');
135
insert into t1 values ('2002-07-10'), ('2002-07-11'), ('2002-07-12');
136
insert into t1 values ('2002-08-13'), ('2002-08-14'), ('2002-08-15');
137
insert into t1 values ('2003-07-10'), ('2003-07-11'), ('2003-07-12');
138
insert into t1 values ('2003-08-13'), ('2003-08-14'), ('2003-08-15');
139
insert into t1 values ('2004-07-10'), ('2004-07-11'), ('2004-07-12');
140
insert into t1 values ('2004-08-13'), ('2004-08-14'), ('2004-08-15');
143
explain select * from t1 where a < '2002-01-01';
144
select * from t1 where a < '2002-01-01';
147
explain select * from t1 where a between '2002-01-01' and '2002-12-31';
148
select * from t1 where a between '2002-01-01' and '2002-12-31';
151
explain select * from t1 where a > '2000-01-01' and a < '2004-12-31';
152
select * from t1 where a > '2000-01-01' and a < '2004-12-31';
153
select count(*) from t1 where a > '2000-01-01' and a < '2004-12-31';
156
explain select * from t1 where a < '2002-07-10' order by a;
157
select * from t1 where a < '2002-07-10' order by a;
158
explain select * from t1 where a < '2002-07-10' order by a desc;
159
select * from t1 where a < '2002-07-10' order by a desc;
162
select * from t1 where a > '2022-01-01';
163
select * from t1 where a < '2000-01-01';
166
select count(*) from t1;