wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
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