Debian Codegraph @

test/descidx3.test

raw
1 # 2006 January 02
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: descidx3.test,v 1.6 2008/03/19 00:21:31 drh Exp $
15 #
16
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19
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
24
25 ifcapable !bloblit {
26 finish_test
27 return
28 }
29 #db eval {PRAGMA legacy_file_format=OFF}
30 sqlite3_db_config db LEGACY_FILE_FORMAT 0
31
32 # This procedure sets the value of the file-format in file 'test.db'
33 # to $newval. Also, the schema cookie is incremented.
34 #
35 proc set_file_format {newval} {
36 hexio_write test.db 44 [hexio_render_int32 $newval]
37 set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
38 incr schemacookie
39 hexio_write test.db 40 [hexio_render_int32 $schemacookie]
40 return {}
41 }
42
43 # This procedure returns the value of the file-format in file 'test.db'.
44 #
45 proc get_file_format {{fname test.db}} {
46 return [hexio_get_int [hexio_read $fname 44 4]]
47 }
48
49 # Verify that the file format starts as 4.
50 #
51 do_test descidx3-1.1 {
52 execsql {
53 CREATE TABLE t1(i INTEGER PRIMARY KEY,a,b,c,d);
54 CREATE INDEX t1i1 ON t1(a DESC, b ASC, c DESC);
55 CREATE INDEX t1i2 ON t1(b DESC, c ASC, d DESC);
56 }
57 get_file_format
58 } {4}
59
60 # Put some information in the table and verify that the descending
61 # index actually works.
62 #
63 do_test descidx3-2.1 {
64 execsql {
65 INSERT INTO t1 VALUES(1, NULL, NULL, NULL, NULL);
66 INSERT INTO t1 VALUES(2, 2, 2, 2, 2);
67 INSERT INTO t1 VALUES(3, 3, 3, 3, 3);
68 INSERT INTO t1 VALUES(4, 2.5, 2.5, 2.5, 2.5);
69 INSERT INTO t1 VALUES(5, -5, -5, -5, -5);
70 INSERT INTO t1 VALUES(6, 'six', 'six', 'six', 'six');
71 INSERT INTO t1 VALUES(7, x'77', x'77', x'77', x'77');
72 INSERT INTO t1 VALUES(8, 'eight', 'eight', 'eight', 'eight');
73 INSERT INTO t1 VALUES(9, x'7979', x'7979', x'7979', x'7979');
74 SELECT count(*) FROM t1;
75 }
76 } 9
77 do_test descidx3-2.2 {
78 execsql {
79 SELECT i FROM t1 ORDER BY a;
80 }
81 } {1 5 2 4 3 8 6 7 9}
82 do_test descidx3-2.3 {
83 execsql {
84 SELECT i FROM t1 ORDER BY a DESC;
85 }
86 } {9 7 6 8 3 4 2 5 1}
87
88 # The "natural" order for the index is decreasing
89 do_test descidx3-2.4 {
90 execsql {
91 SELECT i FROM t1 WHERE a<=x'7979';
92 }
93 } {9 7 6 8 3 4 2 5}
94 do_test descidx3-2.5 {
95 execsql {
96 SELECT i FROM t1 WHERE a>-99;
97 }
98 } {9 7 6 8 3 4 2 5}
99
100 # Even when all values of t1.a are the same, sorting by A returns
101 # the rows in reverse order because this the natural order of the
102 # index.
103 #
104 do_test descidx3-3.1 {
105 execsql {
106 UPDATE t1 SET a=1;
107 SELECT i FROM t1 ORDER BY a;
108 }
109 } {9 7 6 8 3 4 2 5 1}
110 do_test descidx3-3.2 {
111 execsql {
112 SELECT i FROM t1 WHERE a=1 AND b>0 AND b<'zzz'
113 }
114 } {2 4 3 8 6}
115 do_test descidx3-3.3 {
116 execsql {
117 SELECT i FROM t1 WHERE b>0 AND b<'zzz'
118 }
119 } {6 8 3 4 2}
120 do_test descidx3-3.4 {
121 execsql {
122 SELECT i FROM t1 WHERE a=1 AND b>-9999 AND b<x'ffffffff'
123 }
124 } {5 2 4 3 8 6 7 9}
125 do_test descidx3-3.5 {
126 execsql {
127 SELECT i FROM t1 WHERE b>-9999 AND b<x'ffffffff'
128 }
129 } {9 7 6 8 3 4 2 5}
130
131 ifcapable subquery {
132 # If the subquery capability is not compiled in to the binary, then
133 # the IN(...) operator is not available. Hence these tests cannot be
134 # run.
135 do_test descidx3-4.1 {
136 lsort [execsql {
137 UPDATE t1 SET a=2 WHERE i<6;
138 SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
139 }]
140 } {2 3 4 6 8}
141 do_test descidx3-4.2 {
142 execsql {
143 UPDATE t1 SET a=1;
144 SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
145 }
146 } {2 4 3 8 6}
147 do_test descidx3-4.3 {
148 execsql {
149 UPDATE t1 SET b=2;
150 SELECT i FROM t1 WHERE a IN (1,2) AND b>0 AND b<'zzz';
151 }
152 } {9 7 6 8 3 4 2 5 1}
153 }
154
155 finish_test