| blob | a223664ff32e31c46f0076bd15bf532a0317a95f |
1 # 2005 December 21
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #*************************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this script is descending indices.
13 #
14 # $Id: descidx1.test,v 1.10 2008/03/19 00:21:31 drh Exp $
15 #
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 # Do not use a codec for tests in this file, as the database file is
21 # manipulated directly using tcl scripts (using the [hexio_write] command).
22 #
23 do_not_use_codec
25 db eval {PRAGMA legacy_file_format=OFF}
27 # This procedure sets the value of the file-format in file 'test.db'
28 # to $newval. Also, the schema cookie is incremented.
29 #
30 proc set_file_format {newval} {
31 hexio_write test.db 44 [hexio_render_int32 $newval]
32 set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
33 incr schemacookie
34 hexio_write test.db 40 [hexio_render_int32 $schemacookie]
35 return {}
36 }
38 # This procedure returns the value of the file-format in file 'test.db'.
39 #
40 proc get_file_format {{fname test.db}} {
41 return [hexio_get_int [hexio_read $fname 44 4]]
42 }
45 # Verify that the file format starts as 4.
46 #
47 do_test descidx1-1.1 {
48 execsql {
49 CREATE TABLE t1(a,b);
50 CREATE INDEX i1 ON t1(b ASC);
51 }
52 get_file_format
53 } {4}
54 do_test descidx1-1.2 {
55 execsql {
56 CREATE INDEX i2 ON t1(a DESC);
57 }
58 get_file_format
59 } {4}
61 # Put some information in the table and verify that the descending
62 # index actually works.
63 #
64 do_test descidx1-2.1 {
65 execsql {
66 INSERT INTO t1 VALUES(1,1);
67 INSERT INTO t1 VALUES(2,2);
68 INSERT INTO t1 SELECT a+2, a+2 FROM t1;
69 INSERT INTO t1 SELECT a+4, a+4 FROM t1;
70 SELECT b FROM t1 WHERE a>3 AND a<7;
71 }
72 } {6 5 4}
73 do_test descidx1-2.2 {
74 execsql {
75 SELECT a FROM t1 WHERE b>3 AND b<7;
76 }
77 } {4 5 6}
78 do_test descidx1-2.3 {
79 execsql {
80 SELECT b FROM t1 WHERE a>=3 AND a<7;
81 }
82 } {6 5 4 3}
83 do_test descidx1-2.4 {
84 execsql {
85 SELECT b FROM t1 WHERE a>3 AND a<=7;
86 }
87 } {7 6 5 4}
88 do_test descidx1-2.5 {
89 execsql {
90 SELECT b FROM t1 WHERE a>=3 AND a<=7;
91 }
92 } {7 6 5 4 3}
93 do_test descidx1-2.6 {
94 execsql {
95 SELECT a FROM t1 WHERE b>=3 AND b<=7;
96 }
97 } {3 4 5 6 7}
99 # This procedure executes the SQL. Then it checks to see if the OP_Sort
100 # opcode was executed. If an OP_Sort did occur, then "sort" is appended
101 # to the result. If no OP_Sort happened, then "nosort" is appended.
102 #
103 # This procedure is used to check to make sure sorting is or is not
104 # occurring as expected.
105 #
106 proc cksort {sql} {
107 set ::sqlite_sort_count 0
108 set data [execsql $sql]
109 if {$::sqlite_sort_count} {set x sort} {set x nosort}
110 lappend data $x
111 return $data
112 }
114 # Test sorting using a descending index.
115 #
116 do_test descidx1-3.1 {
117 cksort {SELECT a FROM t1 ORDER BY a}
118 } {1 2 3 4 5 6 7 8 nosort}
119 do_test descidx1-3.2 {
120 cksort {SELECT a FROM t1 ORDER BY a ASC}
121 } {1 2 3 4 5 6 7 8 nosort}
122 do_test descidx1-3.3 {
123 cksort {SELECT a FROM t1 ORDER BY a DESC}
124 } {8 7 6 5 4 3 2 1 nosort}
125 do_test descidx1-3.4 {
126 cksort {SELECT b FROM t1 ORDER BY a}
127 } {1 2 3 4 5 6 7 8 nosort}
128 do_test descidx1-3.5 {
129 cksort {SELECT b FROM t1 ORDER BY a ASC}
130 } {1 2 3 4 5 6 7 8 nosort}
131 do_test descidx1-3.6 {
132 cksort {SELECT b FROM t1 ORDER BY a DESC}
133 } {8 7 6 5 4 3 2 1 nosort}
134 do_test descidx1-3.7 {
135 cksort {SELECT a FROM t1 ORDER BY b}
136 } {1 2 3 4 5 6 7 8 nosort}
137 do_test descidx1-3.8 {
138 cksort {SELECT a FROM t1 ORDER BY b ASC}
139 } {1 2 3 4 5 6 7 8 nosort}
140 do_test descidx1-3.9 {
141 cksort {SELECT a FROM t1 ORDER BY b DESC}
142 } {8 7 6 5 4 3 2 1 nosort}
143 do_test descidx1-3.10 {
144 cksort {SELECT b FROM t1 ORDER BY b}
145 } {1 2 3 4 5 6 7 8 nosort}
146 do_test descidx1-3.11 {
147 cksort {SELECT b FROM t1 ORDER BY b ASC}
148 } {1 2 3 4 5 6 7 8 nosort}
149 do_test descidx1-3.12 {
150 cksort {SELECT b FROM t1 ORDER BY b DESC}
151 } {8 7 6 5 4 3 2 1 nosort}
153 do_test descidx1-3.21 {
154 cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a}
155 } {4 5 6 7 nosort}
156 do_test descidx1-3.22 {
157 cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
158 } {4 5 6 7 nosort}
159 do_test descidx1-3.23 {
160 cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
161 } {7 6 5 4 nosort}
162 do_test descidx1-3.24 {
163 cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a}
164 } {4 5 6 7 nosort}
165 do_test descidx1-3.25 {
166 cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
167 } {4 5 6 7 nosort}
168 do_test descidx1-3.26 {
169 cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
170 } {7 6 5 4 nosort}
172 # Create a table with indices that are descending on some terms and
173 # ascending on others.
174 #
175 ifcapable bloblit {
176 do_test descidx1-4.1 {
177 execsql {
178 CREATE TABLE t2(a INT, b TEXT, c BLOB, d REAL);
179 CREATE INDEX i3 ON t2(a ASC, b DESC, c ASC);
180 CREATE INDEX i4 ON t2(b DESC, a ASC, d DESC);
181 INSERT INTO t2 VALUES(1,'one',x'31',1.0);
182 INSERT INTO t2 VALUES(2,'two',x'3232',2.0);
183 INSERT INTO t2 VALUES(3,'three',x'333333',3.0);
184 INSERT INTO t2 VALUES(4,'four',x'34343434',4.0);
185 INSERT INTO t2 VALUES(5,'five',x'3535353535',5.0);
186 INSERT INTO t2 VALUES(6,'six',x'363636363636',6.0);
187 INSERT INTO t2 VALUES(2,'two',x'323232',2.1);
188 INSERT INTO t2 VALUES(2,'zwei',x'3232',2.2);
189 INSERT INTO t2 VALUES(2,NULL,NULL,2.3);
190 SELECT count(*) FROM t2;
191 }
192 } {9}
193 do_test descidx1-4.2 {
194 execsql {
195 SELECT d FROM t2 ORDER BY a;
196 }
197 } {1.0 2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
198 do_test descidx1-4.3 {
199 execsql {
200 SELECT d FROM t2 WHERE a>=2 ORDER BY a;
201 }
202 } {2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
203 do_test descidx1-4.4 {
204 execsql {
205 SELECT d FROM t2 WHERE a>2 ORDER BY a;
206 }
207 } {3.0 4.0 5.0 6.0}
208 do_test descidx1-4.5 {
209 execsql {
210 SELECT d FROM t2 WHERE a=2 AND b>'two';
211 }
212 } {2.2}
213 do_test descidx1-4.6 {
214 execsql {
215 SELECT d FROM t2 WHERE a=2 AND b>='two';
216 }
217 } {2.2 2.0 2.1}
218 do_test descidx1-4.7 {
219 execsql {
220 SELECT d FROM t2 WHERE a=2 AND b<'two';
221 }
222 } {}
223 do_test descidx1-4.8 {
224 execsql {
225 SELECT d FROM t2 WHERE a=2 AND b<='two';
226 }
227 } {2.0 2.1}
228 }
230 do_test descidx1-5.1 {
231 execsql {
232 CREATE TABLE t3(a,b,c,d);
233 CREATE INDEX t3i1 ON t3(a DESC, b ASC, c DESC, d ASC);
234 INSERT INTO t3 VALUES(0,0,0,0);
235 INSERT INTO t3 VALUES(0,0,0,1);
236 INSERT INTO t3 VALUES(0,0,1,0);
237 INSERT INTO t3 VALUES(0,0,1,1);
238 INSERT INTO t3 VALUES(0,1,0,0);
239 INSERT INTO t3 VALUES(0,1,0,1);
240 INSERT INTO t3 VALUES(0,1,1,0);
241 INSERT INTO t3 VALUES(0,1,1,1);
242 INSERT INTO t3 VALUES(1,0,0,0);
243 INSERT INTO t3 VALUES(1,0,0,1);
244 INSERT INTO t3 VALUES(1,0,1,0);
245 INSERT INTO t3 VALUES(1,0,1,1);
246 INSERT INTO t3 VALUES(1,1,0,0);
247 INSERT INTO t3 VALUES(1,1,0,1);
248 INSERT INTO t3 VALUES(1,1,1,0);
249 INSERT INTO t3 VALUES(1,1,1,1);
250 SELECT count(*) FROM t3;
251 }
252 } {16}
253 do_test descidx1-5.2 {
254 cksort {
255 SELECT a||b||c||d FROM t3 ORDER BY a,b,c,d;
256 }
257 } {0000 0001 0010 0011 0100 0101 0110 0111 1000 1001 1010 1011 1100 1101 1110 1111 sort}
258 do_test descidx1-5.3 {
259 cksort {
260 SELECT a||b||c||d FROM t3 ORDER BY a DESC, b ASC, c DESC, d ASC;
261 }
262 } {1010 1011 1000 1001 1110 1111 1100 1101 0010 0011 0000 0001 0110 0111 0100 0101 nosort}
263 do_test descidx1-5.4 {
264 cksort {
265 SELECT a||b||c||d FROM t3 ORDER BY a ASC, b DESC, c ASC, d DESC;
266 }
267 } {0101 0100 0111 0110 0001 0000 0011 0010 1101 1100 1111 1110 1001 1000 1011 1010 nosort}
268 do_test descidx1-5.5 {
269 cksort {
270 SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b ASC, c DESC
271 }
272 } {101 100 111 110 001 000 011 010 nosort}
273 do_test descidx1-5.6 {
274 cksort {
275 SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c ASC
276 }
277 } {010 011 000 001 110 111 100 101 nosort}
278 do_test descidx1-5.7 {
279 cksort {
280 SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b DESC, c DESC
281 }
282 } {011 010 001 000 111 110 101 100 sort}
283 do_test descidx1-5.8 {
284 cksort {
285 SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a ASC, b ASC, c ASC
286 }
287 } {000 001 010 011 100 101 110 111 sort}
288 do_test descidx1-5.9 {
289 cksort {
290 SELECT a||b||c FROM t3 WHERE d=0 ORDER BY a DESC, b DESC, c ASC
291 }
292 } {110 111 100 101 010 011 000 001 sort}
294 # Test the legacy_file_format pragma here because we have access to
295 # the get_file_format command.
296 #
297 ifcapable legacyformat {
298 do_test descidx1-6.1 {
299 db close
300 forcedelete test.db test.db-journal
301 sqlite3 db test.db
302 execsql {PRAGMA legacy_file_format}
303 } {1}
304 } else {
305 do_test descidx1-6.1 {
306 db close
307 forcedelete test.db test.db-journal
308 sqlite3 db test.db
309 execsql {PRAGMA legacy_file_format}
310 } {0}
311 }
312 do_test descidx1-6.2 {
313 execsql {PRAGMA legacy_file_format=YES}
314 execsql {PRAGMA legacy_file_format}
315 } {1}
316 do_test descidx1-6.3 {
317 execsql {
318 CREATE TABLE t1(a,b,c);
319 }
320 get_file_format
321 } {1}
322 ifcapable vacuum {
323 # Verify that the file format is preserved across a vacuum.
324 do_test descidx1-6.3.1 {
325 execsql {VACUUM}
326 get_file_format
327 } {1}
328 }
329 do_test descidx1-6.4 {
330 db close
331 forcedelete test.db test.db-journal
332 sqlite3 db test.db
333 execsql {PRAGMA legacy_file_format=NO}
334 execsql {PRAGMA legacy_file_format}
335 } {0}
336 do_test descidx1-6.5 {
337 execsql {
338 CREATE TABLE t1(a,b,c);
339 CREATE INDEX i1 ON t1(a ASC, b DESC, c ASC);
340 INSERT INTO t1 VALUES(1,2,3);
341 INSERT INTO t1 VALUES(1,1,0);
342 INSERT INTO t1 VALUES(1,2,1);
343 INSERT INTO t1 VALUES(1,3,4);
344 }
345 get_file_format
346 } {4}
347 ifcapable vacuum {
348 # Verify that the file format is preserved across a vacuum.
349 do_test descidx1-6.6 {
350 execsql {VACUUM}
351 get_file_format
352 } {4}
353 do_test descidx1-6.7 {
354 execsql {
355 PRAGMA legacy_file_format=ON;
356 VACUUM;
357 }
358 get_file_format
359 } {4}
360 }
364 finish_test