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