wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2008 January 5 |
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 | # $Id: minmax3.test,v 1.5 2008/07/12 14:52:20 drh Exp $ |
||
12 | |||
13 | set testdir [file dirname $argv0] |
||
14 | source $testdir/tester.tcl |
||
15 | |||
16 | # Do not use a codec for tests in this file, as the database file is |
||
17 | # manipulated directly using tcl scripts (using the [hexio_write] command). |
||
18 | # |
||
19 | do_not_use_codec |
||
20 | |||
21 | # Do an SQL statement. Append the search count to the end of the result. |
||
22 | # |
||
23 | proc count sql { |
||
24 | set ::sqlite_search_count 0 |
||
25 | return [concat [execsql $sql] $::sqlite_search_count] |
||
26 | } |
||
27 | |||
28 | # This procedure sets the value of the file-format in file 'test.db' |
||
29 | # to $newval. Also, the schema cookie is incremented. |
||
30 | # |
||
31 | proc set_file_format {newval} { |
||
32 | hexio_write test.db 44 [hexio_render_int32 $newval] |
||
33 | set schemacookie [hexio_get_int [hexio_read test.db 40 4]] |
||
34 | incr schemacookie |
||
35 | hexio_write test.db 40 [hexio_render_int32 $schemacookie] |
||
36 | return {} |
||
37 | } |
||
38 | |||
39 | do_test minmax3-1.0 { |
||
40 | execsql { |
||
41 | CREATE TABLE t1(x, y, z); |
||
42 | } |
||
43 | db close |
||
44 | set_file_format 4 |
||
45 | sqlite3 db test.db |
||
46 | execsql { |
||
47 | BEGIN; |
||
48 | INSERT INTO t1 VALUES('1', 'I', 'one'); |
||
49 | INSERT INTO t1 VALUES('2', 'IV', 'four'); |
||
50 | INSERT INTO t1 VALUES('2', NULL, 'three'); |
||
51 | INSERT INTO t1 VALUES('2', 'II', 'two'); |
||
52 | INSERT INTO t1 VALUES('2', 'V', 'five'); |
||
53 | INSERT INTO t1 VALUES('3', 'VI', 'six'); |
||
54 | COMMIT; |
||
55 | PRAGMA automatic_index=OFF; |
||
56 | } |
||
57 | } {} |
||
58 | do_test minmax3-1.1.1 { |
||
59 | # Linear scan. |
||
60 | count { SELECT max(y) FROM t1 WHERE x = '2'; } |
||
61 | } {V 5} |
||
62 | do_test minmax3-1.1.2 { |
||
63 | # Index optimizes the WHERE x='2' constraint. |
||
64 | execsql { CREATE INDEX i1 ON t1(x) } |
||
65 | count { SELECT max(y) FROM t1 WHERE x = '2'; } |
||
66 | } {V 9} |
||
67 | do_test minmax3-1.1.3 { |
||
68 | # Index optimizes the WHERE x='2' constraint and the MAX(y). |
||
69 | execsql { CREATE INDEX i2 ON t1(x,y) } |
||
70 | count { SELECT max(y) FROM t1 WHERE x = '2'; } |
||
71 | } {V 1} |
||
72 | do_test minmax3-1.1.4 { |
||
73 | # Index optimizes the WHERE x='2' constraint and the MAX(y). |
||
74 | execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) } |
||
75 | count { SELECT max(y) FROM t1 WHERE x = '2'; } |
||
76 | } {V 1} |
||
77 | do_test minmax3-1.1.5 { |
||
78 | count { SELECT max(y) FROM t1 WHERE x = '2' AND y != 'V'; } |
||
79 | } {IV 2} |
||
80 | do_test minmax3-1.1.6 { |
||
81 | count { SELECT max(y) FROM t1 WHERE x = '2' AND y < 'V'; } |
||
82 | } {IV 1} |
||
83 | do_test minmax3-1.1.6 { |
||
84 | count { SELECT max(y) FROM t1 WHERE x = '2' AND z != 'five'; } |
||
85 | } {IV 4} |
||
86 | |||
87 | do_test minmax3-1.2.1 { |
||
88 | # Linear scan of t1. |
||
89 | execsql { DROP INDEX i1 ; DROP INDEX i2 } |
||
90 | count { SELECT min(y) FROM t1 WHERE x = '2'; } |
||
91 | } {II 5} |
||
92 | do_test minmax3-1.2.2 { |
||
93 | # Index i1 optimizes the WHERE x='2' constraint. |
||
94 | execsql { CREATE INDEX i1 ON t1(x) } |
||
95 | count { SELECT min(y) FROM t1 WHERE x = '2'; } |
||
96 | } {II 9} |
||
97 | do_test minmax3-1.2.3 { |
||
98 | # Index i2 optimizes the WHERE x='2' constraint and the min(y). |
||
99 | execsql { CREATE INDEX i2 ON t1(x,y) } |
||
100 | count { SELECT min(y) FROM t1 WHERE x = '2'; } |
||
101 | } {II 1} |
||
102 | do_test minmax3-1.2.4 { |
||
103 | # Index optimizes the WHERE x='2' constraint and the MAX(y). |
||
104 | execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) } |
||
105 | count { SELECT min(y) FROM t1 WHERE x = '2'; } |
||
106 | } {II 1} |
||
107 | |||
108 | do_test minmax3-1.3.1 { |
||
109 | # Linear scan |
||
110 | execsql { DROP INDEX i1 ; DROP INDEX i2 } |
||
111 | count { SELECT min(y) FROM t1; } |
||
112 | } {I 5} |
||
113 | do_test minmax3-1.3.2 { |
||
114 | # Index i1 optimizes the min(y) |
||
115 | execsql { CREATE INDEX i1 ON t1(y) } |
||
116 | count { SELECT min(y) FROM t1; } |
||
117 | } {I 1} |
||
118 | do_test minmax3-1.3.3 { |
||
119 | # Index i1 optimizes the min(y) |
||
120 | execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) } |
||
121 | count { SELECT min(y) FROM t1; } |
||
122 | } {I 1} |
||
123 | |||
124 | do_test minmax3-1.4.1 { |
||
125 | # Linear scan |
||
126 | execsql { DROP INDEX i1 } |
||
127 | count { SELECT max(y) FROM t1; } |
||
128 | } {VI 5} |
||
129 | do_test minmax3-1.4.2 { |
||
130 | # Index i1 optimizes the max(y) |
||
131 | execsql { CREATE INDEX i1 ON t1(y) } |
||
132 | count { SELECT max(y) FROM t1; } |
||
133 | } {VI 0} |
||
134 | do_test minmax3-1.4.3 { |
||
135 | # Index i1 optimizes the max(y) |
||
136 | execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) } |
||
137 | execsql { SELECT y from t1} |
||
138 | count { SELECT max(y) FROM t1; } |
||
139 | } {VI 0} |
||
140 | do_test minmax3-1.4.4 { |
||
141 | execsql { DROP INDEX i1 } |
||
142 | } {} |
||
143 | |||
144 | do_test minmax3-2.1 { |
||
145 | execsql { |
||
146 | CREATE TABLE t2(a, b); |
||
147 | CREATE INDEX i3 ON t2(a, b); |
||
148 | INSERT INTO t2 VALUES(1, NULL); |
||
149 | INSERT INTO t2 VALUES(1, 1); |
||
150 | INSERT INTO t2 VALUES(1, 2); |
||
151 | INSERT INTO t2 VALUES(1, 3); |
||
152 | INSERT INTO t2 VALUES(2, NULL); |
||
153 | INSERT INTO t2 VALUES(2, 1); |
||
154 | INSERT INTO t2 VALUES(2, 2); |
||
155 | INSERT INTO t2 VALUES(2, 3); |
||
156 | INSERT INTO t2 VALUES(3, 1); |
||
157 | INSERT INTO t2 VALUES(3, 2); |
||
158 | INSERT INTO t2 VALUES(3, 3); |
||
159 | } |
||
160 | } {} |
||
161 | do_test minmax3-2.2 { |
||
162 | execsql { SELECT min(b) FROM t2 WHERE a = 1; } |
||
163 | } {1} |
||
164 | do_test minmax3-2.3 { |
||
165 | execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; } |
||
166 | } {2} |
||
167 | do_test minmax3-2.4 { |
||
168 | execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; } |
||
169 | } {1} |
||
170 | do_test minmax3-2.5 { |
||
171 | execsql { SELECT min(b) FROM t2 WHERE a = 1; } |
||
172 | } {1} |
||
173 | do_test minmax3-2.6 { |
||
174 | execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; } |
||
175 | } {1} |
||
176 | do_test minmax3-2.7 { |
||
177 | execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; } |
||
178 | } {{}} |
||
179 | do_test minmax3-2.8 { |
||
180 | execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; } |
||
181 | } {{}} |
||
182 | |||
183 | do_test minmax3-3.1 { |
||
184 | execsql { |
||
185 | DROP TABLE t2; |
||
186 | CREATE TABLE t2(a, b); |
||
187 | CREATE INDEX i3 ON t2(a, b DESC); |
||
188 | INSERT INTO t2 VALUES(1, NULL); |
||
189 | INSERT INTO t2 VALUES(1, 1); |
||
190 | INSERT INTO t2 VALUES(1, 2); |
||
191 | INSERT INTO t2 VALUES(1, 3); |
||
192 | INSERT INTO t2 VALUES(2, NULL); |
||
193 | INSERT INTO t2 VALUES(2, 1); |
||
194 | INSERT INTO t2 VALUES(2, 2); |
||
195 | INSERT INTO t2 VALUES(2, 3); |
||
196 | INSERT INTO t2 VALUES(3, 1); |
||
197 | INSERT INTO t2 VALUES(3, 2); |
||
198 | INSERT INTO t2 VALUES(3, 3); |
||
199 | } |
||
200 | } {} |
||
201 | do_test minmax3-3.2 { |
||
202 | execsql { SELECT min(b) FROM t2 WHERE a = 1; } |
||
203 | } {1} |
||
204 | do_test minmax3-3.3 { |
||
205 | execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; } |
||
206 | } {2} |
||
207 | do_test minmax3-3.4 { |
||
208 | execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; } |
||
209 | } {1} |
||
210 | do_test minmax3-3.5 { |
||
211 | execsql { SELECT min(b) FROM t2 WHERE a = 1; } |
||
212 | } {1} |
||
213 | do_test minmax3-3.6 { |
||
214 | execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; } |
||
215 | } {1} |
||
216 | do_test minmax3-3.7 { |
||
217 | execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; } |
||
218 | } {{}} |
||
219 | do_test minmax3-3.8 { |
||
220 | execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; } |
||
221 | } {{}} |
||
222 | |||
223 | do_test minmax3-4.1 { |
||
224 | execsql { |
||
225 | CREATE TABLE t4(x); |
||
226 | INSERT INTO t4 VALUES('abc'); |
||
227 | INSERT INTO t4 VALUES('BCD'); |
||
228 | SELECT max(x) FROM t4; |
||
229 | } |
||
230 | } {abc} |
||
231 | do_test minmax3-4.2 { |
||
232 | execsql { |
||
233 | SELECT max(x COLLATE nocase) FROM t4; |
||
234 | } |
||
235 | } {BCD} |
||
236 | do_test minmax3-4.3 { |
||
237 | execsql { |
||
238 | SELECT max(x), max(x COLLATE nocase) FROM t4; |
||
239 | } |
||
240 | } {abc BCD} |
||
241 | do_test minmax3-4.4 { |
||
242 | execsql { |
||
243 | SELECT max(x COLLATE binary), max(x COLLATE nocase) FROM t4; |
||
244 | } |
||
245 | } {abc BCD} |
||
246 | do_test minmax3-4.5 { |
||
247 | execsql { |
||
248 | SELECT max(x COLLATE nocase), max(x COLLATE rtrim) FROM t4; |
||
249 | } |
||
250 | } {BCD abc} |
||
251 | do_test minmax3-4.6 { |
||
252 | execsql { |
||
253 | SELECT max(x COLLATE nocase), max(x) FROM t4; |
||
254 | } |
||
255 | } {BCD abc} |
||
256 | do_test minmax3-4.10 { |
||
257 | execsql { |
||
258 | SELECT min(x) FROM t4; |
||
259 | } |
||
260 | } {BCD} |
||
261 | do_test minmax3-4.11 { |
||
262 | execsql { |
||
263 | SELECT min(x COLLATE nocase) FROM t4; |
||
264 | } |
||
265 | } {abc} |
||
266 | do_test minmax3-4.12 { |
||
267 | execsql { |
||
268 | SELECT min(x), min(x COLLATE nocase) FROM t4; |
||
269 | } |
||
270 | } {BCD abc} |
||
271 | do_test minmax3-4.13 { |
||
272 | execsql { |
||
273 | SELECT min(x COLLATE binary), min(x COLLATE nocase) FROM t4; |
||
274 | } |
||
275 | } {BCD abc} |
||
276 | do_test minmax3-4.14 { |
||
277 | execsql { |
||
278 | SELECT min(x COLLATE nocase), min(x COLLATE rtrim) FROM t4; |
||
279 | } |
||
280 | } {abc BCD} |
||
281 | do_test minmax3-4.15 { |
||
282 | execsql { |
||
283 | SELECT min(x COLLATE nocase), min(x) FROM t4; |
||
284 | } |
||
285 | } {abc BCD} |
||
286 | |||
287 | |||
288 | finish_test |