Debian Codegraph @

test/descidx2.test

raw
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: descidx2.test,v 1.5 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
26 #db eval {PRAGMA legacy_file_format=OFF}
27 sqlite3_db_config db LEGACY_FILE_FORMAT 0
28
29 # This procedure sets the value of the file-format in file 'test.db'
30 # to $newval. Also, the schema cookie is incremented.
31 #
32 proc set_file_format {newval} {
33 hexio_write test.db 44 [hexio_render_int32 $newval]
34 set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
35 incr schemacookie
36 hexio_write test.db 40 [hexio_render_int32 $schemacookie]
37 return {}
38 }
39
40 # This procedure returns the value of the file-format in file 'test.db'.
41 #
42 proc get_file_format {{fname test.db}} {
43 return [hexio_get_int [hexio_read $fname 44 4]]
44 }
45
46
47 # Verify that the file format starts as 4
48 #
49 do_test descidx2-1.1 {
50 execsql {
51 CREATE TABLE t1(a,b);
52 CREATE INDEX i1 ON t1(b ASC);
53 }
54 get_file_format
55 } {4}
56 do_test descidx2-1.2 {
57 execsql {
58 CREATE INDEX i2 ON t1(a DESC);
59 }
60 get_file_format
61 } {4}
62
63 # Before adding any information to the database, set the file format
64 # back to three. Then close and reopen the database. With the file
65 # format set to three, SQLite should ignore the DESC argument on the
66 # index.
67 #
68 do_test descidx2-2.0 {
69 set_file_format 3
70 db close
71 sqlite3 db test.db
72 get_file_format
73 } {3}
74
75 # Put some information in the table and verify that the DESC
76 # on the index is ignored.
77 #
78 do_test descidx2-2.1 {
79 execsql {
80 INSERT INTO t1 VALUES(1,1);
81 INSERT INTO t1 VALUES(2,2);
82 INSERT INTO t1 SELECT a+2, a+2 FROM t1;
83 INSERT INTO t1 SELECT a+4, a+4 FROM t1;
84 SELECT b FROM t1 WHERE a>3 AND a<7;
85 }
86 } {4 5 6}
87 do_test descidx2-2.2 {
88 execsql {
89 SELECT a FROM t1 WHERE b>3 AND b<7;
90 }
91 } {4 5 6}
92 do_test descidx2-2.3 {
93 execsql {
94 SELECT b FROM t1 WHERE a>=3 AND a<7;
95 }
96 } {3 4 5 6}
97 do_test descidx2-2.4 {
98 execsql {
99 SELECT b FROM t1 WHERE a>3 AND a<=7;
100 }
101 } {4 5 6 7}
102 do_test descidx2-2.5 {
103 execsql {
104 SELECT b FROM t1 WHERE a>=3 AND a<=7;
105 }
106 } {3 4 5 6 7}
107 do_test descidx2-2.6 {
108 execsql {
109 SELECT a FROM t1 WHERE b>=3 AND b<=7;
110 }
111 } {3 4 5 6 7}
112
113 # This procedure executes the SQL. Then it checks to see if the OP_Sort
114 # opcode was executed. If an OP_Sort did occur, then "sort" is appended
115 # to the result. If no OP_Sort happened, then "nosort" is appended.
116 #
117 # This procedure is used to check to make sure sorting is or is not
118 # occurring as expected.
119 #
120 proc cksort {sql} {
121 set ::sqlite_sort_count 0
122 set data [execsql $sql]
123 if {$::sqlite_sort_count} {set x sort} {set x nosort}
124 lappend data $x
125 return $data
126 }
127
128 # Test sorting using a descending index.
129 #
130 do_test descidx2-3.1 {
131 cksort {SELECT a FROM t1 ORDER BY a}
132 } {1 2 3 4 5 6 7 8 nosort}
133 do_test descidx2-3.2 {
134 cksort {SELECT a FROM t1 ORDER BY a ASC}
135 } {1 2 3 4 5 6 7 8 nosort}
136 do_test descidx2-3.3 {
137 cksort {SELECT a FROM t1 ORDER BY a DESC}
138 } {8 7 6 5 4 3 2 1 nosort}
139 do_test descidx2-3.4 {
140 cksort {SELECT b FROM t1 ORDER BY a}
141 } {1 2 3 4 5 6 7 8 nosort}
142 do_test descidx2-3.5 {
143 cksort {SELECT b FROM t1 ORDER BY a ASC}
144 } {1 2 3 4 5 6 7 8 nosort}
145 do_test descidx2-3.6 {
146 cksort {SELECT b FROM t1 ORDER BY a DESC}
147 } {8 7 6 5 4 3 2 1 nosort}
148 do_test descidx2-3.7 {
149 cksort {SELECT a FROM t1 ORDER BY b}
150 } {1 2 3 4 5 6 7 8 nosort}
151 do_test descidx2-3.8 {
152 cksort {SELECT a FROM t1 ORDER BY b ASC}
153 } {1 2 3 4 5 6 7 8 nosort}
154 do_test descidx2-3.9 {
155 cksort {SELECT a FROM t1 ORDER BY b DESC}
156 } {8 7 6 5 4 3 2 1 nosort}
157 do_test descidx2-3.10 {
158 cksort {SELECT b FROM t1 ORDER BY b}
159 } {1 2 3 4 5 6 7 8 nosort}
160 do_test descidx2-3.11 {
161 cksort {SELECT b FROM t1 ORDER BY b ASC}
162 } {1 2 3 4 5 6 7 8 nosort}
163 do_test descidx2-3.12 {
164 cksort {SELECT b FROM t1 ORDER BY b DESC}
165 } {8 7 6 5 4 3 2 1 nosort}
166
167 do_test descidx2-3.21 {
168 cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a}
169 } {4 5 6 7 nosort}
170 do_test descidx2-3.22 {
171 cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
172 } {4 5 6 7 nosort}
173 do_test descidx2-3.23 {
174 cksort {SELECT a FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
175 } {7 6 5 4 nosort}
176 do_test descidx2-3.24 {
177 cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a}
178 } {4 5 6 7 nosort}
179 do_test descidx2-3.25 {
180 cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a ASC}
181 } {4 5 6 7 nosort}
182 do_test descidx2-3.26 {
183 cksort {SELECT b FROM t1 WHERE a>3 AND a<8 ORDER BY a DESC}
184 } {7 6 5 4 nosort}
185
186 finish_test