wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 #
2 # 2001 September 15
3 #
4 # The author disclaims copyright to this source code. In place of
5 # a legal notice, here is a blessing:
6 #
7 # May you do good and not evil.
8 # May you find forgiveness for yourself and forgive others.
9 # May you share freely, never taking more than you give.
10 #
11 #***********************************************************************
12 # This file implements regression tests for SQLite library. The
13 # focus of this script is page cache subsystem.
14 #
15 # $Id: collate4.test,v 1.9 2008/01/05 17:39:30 danielk1977 Exp $
16  
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19  
20 db collate TEXT text_collate
21 proc text_collate {a b} {
22 return [string compare $a $b]
23 }
24  
25 # Do an SQL statement. Append the search count to the end of the result.
26 #
27 proc count sql {
28 set ::sqlite_search_count 0
29 return [concat [execsql $sql] $::sqlite_search_count]
30 }
31  
32 # This procedure executes the SQL. Then it checks the generated program
33 # for the SQL and appends a "nosort" to the result if the program contains the
34 # SortCallback opcode. If the program does not contain the SortCallback
35 # opcode it appends "sort"
36 #
37 proc cksort {sql} {
38 set ::sqlite_sort_count 0
39 set data [execsql $sql]
40 if {$::sqlite_sort_count} {set x sort} {set x nosort}
41 lappend data $x
42 return $data
43 }
44  
45 #
46 # Test cases are organized roughly as follows:
47 #
48 # collate4-1.* ORDER BY.
49 # collate4-2.* WHERE clauses.
50 # collate4-3.* constraints (primary key, unique).
51 # collate4-4.* simple min() or max() queries.
52 # collate4-5.* REINDEX command
53 # collate4-6.* INTEGER PRIMARY KEY indices.
54 #
55  
56 #
57 # These tests - collate4-1.* - check that indices are correctly
58 # selected or not selected to implement ORDER BY clauses when
59 # user defined collation sequences are involved.
60 #
61 # Because these tests also exercise all the different ways indices
62 # can be created, they also serve to verify that indices are correctly
63 # initialised with user-defined collation sequences when they are
64 # created.
65 #
66 # Tests named collate4-1.1.* use indices with a single column. Tests
67 # collate4-1.2.* use indices with two columns.
68 #
69 do_test collate4-1.1.0 {
70 execsql {
71 CREATE TABLE collate4t1(a COLLATE NOCASE, b COLLATE TEXT);
72 INSERT INTO collate4t1 VALUES( 'a', 'a' );
73 INSERT INTO collate4t1 VALUES( 'b', 'b' );
74 INSERT INTO collate4t1 VALUES( NULL, NULL );
75 INSERT INTO collate4t1 VALUES( 'B', 'B' );
76 INSERT INTO collate4t1 VALUES( 'A', 'A' );
77 CREATE INDEX collate4i1 ON collate4t1(a);
78 CREATE INDEX collate4i2 ON collate4t1(b);
79 }
80 } {}
81 do_test collate4-1.1.1 {
82 cksort {SELECT a FROM collate4t1 ORDER BY a}
83 } {{} a A b B nosort}
84 do_test collate4-1.1.2 {
85 cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE NOCASE}
86 } {{} a A b B nosort}
87 do_test collate4-1.1.3 {
88 cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE TEXT}
89 } {{} A B a b sort}
90 do_test collate4-1.1.4 {
91 cksort {SELECT b FROM collate4t1 ORDER BY b}
92 } {{} A B a b nosort}
93 do_test collate4-1.1.5 {
94 cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT}
95 } {{} A B a b nosort}
96 do_test collate4-1.1.6 {
97 cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE}
98 } {{} a A b B sort}
99  
100 do_test collate4-1.1.7 {
101 execsql {
102 CREATE TABLE collate4t2(
103 a PRIMARY KEY COLLATE NOCASE,
104 b UNIQUE COLLATE TEXT
105 );
106 INSERT INTO collate4t2 VALUES( 'a', 'a' );
107 INSERT INTO collate4t2 VALUES( NULL, NULL );
108 INSERT INTO collate4t2 VALUES( 'B', 'B' );
109 }
110 } {}
111 do_test collate4-1.1.8 {
112 cksort {SELECT a FROM collate4t2 ORDER BY a}
113 } {{} a B nosort}
114 do_test collate4-1.1.9 {
115 cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE NOCASE}
116 } {{} a B nosort}
117 do_test collate4-1.1.10 {
118 cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE TEXT}
119 } {{} B a sort}
120 do_test collate4-1.1.11 {
121 cksort {SELECT b FROM collate4t2 ORDER BY b}
122 } {{} B a nosort}
123 do_test collate4-1.1.12 {
124 cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE TEXT}
125 } {{} B a nosort}
126 do_test collate4-1.1.13 {
127 cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE NOCASE}
128 } {{} a B sort}
129  
130 do_test collate4-1.1.14 {
131 execsql {
132 CREATE TABLE collate4t3(
133 b COLLATE TEXT,
134 a COLLATE NOCASE,
135 UNIQUE(a), PRIMARY KEY(b)
136 );
137 INSERT INTO collate4t3 VALUES( 'a', 'a' );
138 INSERT INTO collate4t3 VALUES( NULL, NULL );
139 INSERT INTO collate4t3 VALUES( 'B', 'B' );
140 }
141 } {}
142 do_test collate4-1.1.15 {
143 cksort {SELECT a FROM collate4t3 ORDER BY a}
144 } {{} a B nosort}
145 do_test collate4-1.1.16 {
146 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE NOCASE}
147 } {{} a B nosort}
148 do_test collate4-1.1.17 {
149 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE TEXT}
150 } {{} B a sort}
151 do_test collate4-1.1.18 {
152 cksort {SELECT b FROM collate4t3 ORDER BY b}
153 } {{} B a nosort}
154 do_test collate4-1.1.19 {
155 cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE TEXT}
156 } {{} B a nosort}
157 do_test collate4-1.1.20 {
158 cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE NOCASE}
159 } {{} a B sort}
160  
161 do_test collate4-1.1.21 {
162 execsql {
163 CREATE TABLE collate4t4(a COLLATE NOCASE, b COLLATE TEXT);
164 INSERT INTO collate4t4 VALUES( 'a', 'a' );
165 INSERT INTO collate4t4 VALUES( 'b', 'b' );
166 INSERT INTO collate4t4 VALUES( NULL, NULL );
167 INSERT INTO collate4t4 VALUES( 'B', 'B' );
168 INSERT INTO collate4t4 VALUES( 'A', 'A' );
169 CREATE INDEX collate4i3 ON collate4t4(a COLLATE TEXT);
170 CREATE INDEX collate4i4 ON collate4t4(b COLLATE NOCASE);
171 }
172 } {}
173 do_test collate4-1.1.22 {
174 cksort {SELECT a FROM collate4t4 ORDER BY a}
175 } {{} a A b B sort}
176 do_test collate4-1.1.23 {
177 cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE}
178 } {{} a A b B sort}
179 do_test collate4-1.1.24 {
180 cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT}
181 } {{} A B a b nosort}
182 do_test collate4-1.1.25 {
183 cksort {SELECT b FROM collate4t4 ORDER BY b}
184 } {{} A B a b sort}
185 do_test collate4-1.1.26 {
186 cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE TEXT}
187 } {{} A B a b sort}
188 do_test collate4-1.1.27 {
189 cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE NOCASE}
190 } {{} a A b B nosort}
191  
192 do_test collate4-1.1.30 {
193 execsql {
194 DROP TABLE collate4t1;
195 DROP TABLE collate4t2;
196 DROP TABLE collate4t3;
197 DROP TABLE collate4t4;
198 }
199 } {}
200  
201 do_test collate4-1.2.0 {
202 execsql {
203 CREATE TABLE collate4t1(a COLLATE NOCASE, b COLLATE TEXT);
204 INSERT INTO collate4t1 VALUES( 'a', 'a' );
205 INSERT INTO collate4t1 VALUES( 'b', 'b' );
206 INSERT INTO collate4t1 VALUES( NULL, NULL );
207 INSERT INTO collate4t1 VALUES( 'B', 'B' );
208 INSERT INTO collate4t1 VALUES( 'A', 'A' );
209 CREATE INDEX collate4i1 ON collate4t1(a, b);
210 }
211 } {}
212 do_test collate4-1.2.1 {
213 cksort {SELECT a FROM collate4t1 ORDER BY a}
214 } {{} A a B b nosort}
215 do_test collate4-1.2.2 {
216 cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE nocase}
217 } {{} A a B b nosort}
218 do_test collate4-1.2.3 {
219 cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text}
220 } {{} A B a b sort}
221 do_test collate4-1.2.4 {
222 cksort {SELECT a FROM collate4t1 ORDER BY a, b}
223 } {{} A a B b nosort}
224 do_test collate4-1.2.5 {
225 cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase}
226 } {{} a A b B sort}
227 do_test collate4-1.2.6 {
228 cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text}
229 } {{} A a B b nosort}
230  
231 do_test collate4-1.2.7 {
232 execsql {
233 CREATE TABLE collate4t2(
234 a COLLATE NOCASE,
235 b COLLATE TEXT,
236 PRIMARY KEY(a, b)
237 );
238 INSERT INTO collate4t2 VALUES( 'a', 'a' );
239 INSERT INTO collate4t2 VALUES( NULL, NULL );
240 INSERT INTO collate4t2 VALUES( 'B', 'B' );
241 }
242 } {}
243 do_test collate4-1.2.8 {
244 cksort {SELECT a FROM collate4t2 ORDER BY a}
245 } {{} a B nosort}
246 do_test collate4-1.2.9 {
247 cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE nocase}
248 } {{} a B nosort}
249 do_test collate4-1.2.10 {
250 cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE text}
251 } {{} B a sort}
252 do_test collate4-1.2.11 {
253 cksort {SELECT a FROM collate4t2 ORDER BY a, b}
254 } {{} a B nosort}
255 do_test collate4-1.2.12 {
256 cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE nocase}
257 } {{} a B sort}
258 do_test collate4-1.2.13 {
259 cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE text}
260 } {{} a B nosort}
261  
262 do_test collate4-1.2.14 {
263 execsql {
264 CREATE TABLE collate4t3(a COLLATE NOCASE, b COLLATE TEXT);
265 INSERT INTO collate4t3 VALUES( 'a', 'a' );
266 INSERT INTO collate4t3 VALUES( 'b', 'b' );
267 INSERT INTO collate4t3 VALUES( NULL, NULL );
268 INSERT INTO collate4t3 VALUES( 'B', 'B' );
269 INSERT INTO collate4t3 VALUES( 'A', 'A' );
270 CREATE INDEX collate4i2 ON collate4t3(a COLLATE TEXT, b COLLATE NOCASE);
271 }
272 } {}
273 do_test collate4-1.2.15 {
274 cksort {SELECT a FROM collate4t3 ORDER BY a}
275 } {{} a A b B sort}
276 do_test collate4-1.2.16 {
277 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase}
278 } {{} a A b B sort}
279 do_test collate4-1.2.17 {
280 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text}
281 } {{} A B a b nosort}
282 do_test collate4-1.2.18 {
283 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b}
284 } {{} A B a b sort}
285 do_test collate4-1.2.19 {
286 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b COLLATE nocase}
287 } {{} A B a b nosort}
288 do_test collate4-1.2.20 {
289 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b COLLATE text}
290 } {{} A B a b sort}
291 do_test collate4-1.2.21 {
292 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC}
293 } {b a B A {} nosort}
294 do_test collate4-1.2.22 {
295 cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC, b}
296 } {b a B A {} sort}
297 do_test collate4-1.2.23 {
298 cksort {SELECT a FROM collate4t3
299 ORDER BY a COLLATE text DESC, b COLLATE nocase}
300 } {b a B A {} sort}
301 do_test collate4-1.2.24 {
302 cksort {SELECT a FROM collate4t3
303 ORDER BY a COLLATE text DESC, b COLLATE nocase DESC}
304 } {b a B A {} nosort}
305  
306 do_test collate4-1.2.25 {
307 execsql {
308 DROP TABLE collate4t1;
309 DROP TABLE collate4t2;
310 DROP TABLE collate4t3;
311 }
312 } {}
313  
314 #
315 # These tests - collate4-2.* - check that indices are correctly
316 # selected or not selected to implement WHERE clauses when user
317 # defined collation sequences are involved.
318 #
319 # Indices may optimise WHERE clauses using <, >, <=, >=, = or IN
320 # operators.
321 #
322 do_test collate4-2.1.0 {
323 execsql {
324 PRAGMA automatic_index=OFF;
325 CREATE TABLE collate4t1(a COLLATE NOCASE);
326 CREATE TABLE collate4t2(b COLLATE TEXT);
327  
328 INSERT INTO collate4t1 VALUES('a');
329 INSERT INTO collate4t1 VALUES('A');
330 INSERT INTO collate4t1 VALUES('b');
331 INSERT INTO collate4t1 VALUES('B');
332 INSERT INTO collate4t1 VALUES('c');
333 INSERT INTO collate4t1 VALUES('C');
334 INSERT INTO collate4t1 VALUES('d');
335 INSERT INTO collate4t1 VALUES('D');
336 INSERT INTO collate4t1 VALUES('e');
337 INSERT INTO collate4t1 VALUES('D');
338  
339 INSERT INTO collate4t2 VALUES('A');
340 INSERT INTO collate4t2 VALUES('Z');
341 }
342 } {}
343 do_test collate4-2.1.1 {
344 count {
345 SELECT * FROM collate4t2, collate4t1 WHERE a = b;
346 }
347 } {A a A A 19}
348 do_test collate4-2.1.2 {
349 execsql {
350 CREATE INDEX collate4i1 ON collate4t1(a);
351 }
352 count {
353 SELECT * FROM collate4t2, collate4t1 WHERE a = b;
354 }
355 } {A a A A 5}
356 do_test collate4-2.1.3 {
357 count {
358 SELECT * FROM collate4t2, collate4t1 WHERE b = a;
359 }
360 } {A A 19}
361 do_test collate4-2.1.4 {
362 execsql {
363 DROP INDEX collate4i1;
364 CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
365 }
366 count {
367 SELECT * FROM collate4t2, collate4t1 WHERE a = b;
368 }
369 } {A a A A 19}
370 do_test collate4-2.1.5 {
371 count {
372 SELECT * FROM collate4t2, collate4t1 WHERE b = a;
373 }
374 } {A A 4}
375 ifcapable subquery {
376 do_test collate4-2.1.6 {
377 count {
378 SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
379 }
380 } {a A 10}
381 do_test collate4-2.1.7 {
382 execsql {
383 DROP INDEX collate4i1;
384 CREATE INDEX collate4i1 ON collate4t1(a);
385 }
386 count {
387 SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
388 }
389 } {a A 6}
390 do_test collate4-2.1.8 {
391 count {
392 SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
393 }
394 } {a A 5}
395 do_test collate4-2.1.9 {
396 execsql {
397 DROP INDEX collate4i1;
398 CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
399 }
400 count {
401 SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
402 }
403 } {a A 9}
404 }
405 do_test collate4-2.1.10 {
406 execsql {
407 DROP TABLE collate4t1;
408 DROP TABLE collate4t2;
409 }
410 } {}
411  
412 do_test collate4-2.2.0 {
413 execsql {
414 CREATE TABLE collate4t1(a COLLATE nocase, b COLLATE text, c);
415 CREATE TABLE collate4t2(a COLLATE nocase, b COLLATE text, c COLLATE TEXT);
416  
417 INSERT INTO collate4t1 VALUES('0', '0', '0');
418 INSERT INTO collate4t1 VALUES('0', '0', '1');
419 INSERT INTO collate4t1 VALUES('0', '1', '0');
420 INSERT INTO collate4t1 VALUES('0', '1', '1');
421 INSERT INTO collate4t1 VALUES('1', '0', '0');
422 INSERT INTO collate4t1 VALUES('1', '0', '1');
423 INSERT INTO collate4t1 VALUES('1', '1', '0');
424 INSERT INTO collate4t1 VALUES('1', '1', '1');
425 insert into collate4t2 SELECT * FROM collate4t1;
426 }
427 } {}
428 do_test collate4-2.2.1 {
429 count {
430 SELECT * FROM collate4t2 NOT INDEXED NATURAL JOIN collate4t1 NOT INDEXED;
431 }
432 } {0 0 0 0 0 1 0 1 0 0 1 1 1 0 0 1 0 1 1 1 0 1 1 1 63}
433 do_test collate4-2.2.1b {
434 execsql {
435 CREATE INDEX collate4i1 ON collate4t1(a, b, c);
436 }
437 count {
438 SELECT * FROM collate4t2 NATURAL JOIN collate4t1;
439 }
440 } {0 0 0 0 0 1 0 1 0 0 1 1 1 0 0 1 0 1 1 1 0 1 1 1 29}
441 do_test collate4-2.2.2 {
442 execsql {
443 DROP INDEX collate4i1;
444 CREATE INDEX collate4i1 ON collate4t1(a, b, c COLLATE text);
445 }
446 count {
447 SELECT * FROM collate4t2 NATURAL JOIN collate4t1;
448 }
449 } {0 0 0 0 0 1 0 1 0 0 1 1 1 0 0 1 0 1 1 1 0 1 1 1 22}
450  
451 do_test collate4-2.2.10 {
452 execsql {
453 DROP TABLE collate4t1;
454 DROP TABLE collate4t2;
455 }
456 } {}
457  
458 #
459 # These tests - collate4-3.* verify that indices that implement
460 # UNIQUE and PRIMARY KEY constraints operate correctly with user
461 # defined collation sequences.
462 #
463 do_test collate4-3.0 {
464 execsql {
465 CREATE TABLE collate4t1(a PRIMARY KEY COLLATE NOCASE);
466 }
467 } {}
468 do_test collate4-3.1 {
469 catchsql {
470 INSERT INTO collate4t1 VALUES('abc');
471 INSERT INTO collate4t1 VALUES('ABC');
472 }
473 } {1 {column a is not unique}}
474 do_test collate4-3.2 {
475 execsql {
476 SELECT * FROM collate4t1;
477 }
478 } {abc}
479 do_test collate4-3.3 {
480 catchsql {
481 INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
482 }
483 } {1 {column a is not unique}}
484 do_test collate4-3.4 {
485 catchsql {
486 INSERT INTO collate4t1 VALUES(1);
487 UPDATE collate4t1 SET a = 'abc';
488 }
489 } {1 {column a is not unique}}
490 do_test collate4-3.5 {
491 execsql {
492 DROP TABLE collate4t1;
493 CREATE TABLE collate4t1(a COLLATE NOCASE UNIQUE);
494 }
495 } {}
496 do_test collate4-3.6 {
497 catchsql {
498 INSERT INTO collate4t1 VALUES('abc');
499 INSERT INTO collate4t1 VALUES('ABC');
500 }
501 } {1 {column a is not unique}}
502 do_test collate4-3.7 {
503 execsql {
504 SELECT * FROM collate4t1;
505 }
506 } {abc}
507 do_test collate4-3.8 {
508 catchsql {
509 INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
510 }
511 } {1 {column a is not unique}}
512 do_test collate4-3.9 {
513 catchsql {
514 INSERT INTO collate4t1 VALUES(1);
515 UPDATE collate4t1 SET a = 'abc';
516 }
517 } {1 {column a is not unique}}
518 do_test collate4-3.10 {
519 execsql {
520 DROP TABLE collate4t1;
521 CREATE TABLE collate4t1(a);
522 CREATE UNIQUE INDEX collate4i1 ON collate4t1(a COLLATE NOCASE);
523 }
524 } {}
525 do_test collate4-3.11 {
526 catchsql {
527 INSERT INTO collate4t1 VALUES('abc');
528 INSERT INTO collate4t1 VALUES('ABC');
529 }
530 } {1 {column a is not unique}}
531 do_test collate4-3.12 {
532 execsql {
533 SELECT * FROM collate4t1;
534 }
535 } {abc}
536 do_test collate4-3.13 {
537 catchsql {
538 INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
539 }
540 } {1 {column a is not unique}}
541 do_test collate4-3.14 {
542 catchsql {
543 INSERT INTO collate4t1 VALUES(1);
544 UPDATE collate4t1 SET a = 'abc';
545 }
546 } {1 {column a is not unique}}
547  
548 do_test collate4-3.15 {
549 execsql {
550 DROP TABLE collate4t1;
551 }
552 } {}
553  
554 # Mimic the SQLite 2 collation type NUMERIC.
555 db collate numeric numeric_collate
556 proc numeric_collate {lhs rhs} {
557 if {$lhs == $rhs} {return 0}
558 return [expr ($lhs>$rhs)?1:-1]
559 }
560  
561 #
562 # These tests - collate4-4.* check that min() and max() only ever
563 # use indices constructed with built-in collation type numeric.
564 #
565 # CHANGED: min() and max() now use the collation type. If there
566 # is an indice that can be used, it is used.
567 #
568 do_test collate4-4.0 {
569 execsql {
570 CREATE TABLE collate4t1(a COLLATE TEXT);
571 INSERT INTO collate4t1 VALUES('2');
572 INSERT INTO collate4t1 VALUES('10');
573 INSERT INTO collate4t1 VALUES('20');
574 INSERT INTO collate4t1 VALUES('104');
575 }
576 } {}
577 do_test collate4-4.1 {
578 count {
579 SELECT max(a) FROM collate4t1
580 }
581 } {20 3}
582 do_test collate4-4.2 {
583 count {
584 SELECT min(a) FROM collate4t1
585 }
586 } {10 3}
587 do_test collate4-4.3 {
588 # Test that the index with collation type TEXT is used.
589 execsql {
590 CREATE INDEX collate4i1 ON collate4t1(a);
591 }
592 count {
593 SELECT min(a) FROM collate4t1;
594 }
595 } {10 1}
596 do_test collate4-4.4 {
597 count {
598 SELECT max(a) FROM collate4t1;
599 }
600 } {20 0}
601 do_test collate4-4.5 {
602 # Test that the index with collation type NUMERIC is not used.
603 execsql {
604 DROP INDEX collate4i1;
605 CREATE INDEX collate4i1 ON collate4t1(a COLLATE NUMERIC);
606 }
607 count {
608 SELECT min(a) FROM collate4t1;
609 }
610 } {10 3}
611 do_test collate4-4.6 {
612 count {
613 SELECT max(a) FROM collate4t1;
614 }
615 } {20 3}
616 do_test collate4-4.7 {
617 execsql {
618 DROP TABLE collate4t1;
619 }
620 } {}
621  
622 # Also test the scalar min() and max() functions.
623 #
624 do_test collate4-4.8 {
625 execsql {
626 CREATE TABLE collate4t1(a COLLATE TEXT, b COLLATE NUMERIC);
627 INSERT INTO collate4t1 VALUES('11', '101');
628 INSERT INTO collate4t1 VALUES('101', '11')
629 }
630 } {}
631 do_test collate4-4.9 {
632 execsql {
633 SELECT max(a, b) FROM collate4t1;
634 }
635 } {11 11}
636 do_test collate4-4.10 {
637 execsql {
638 SELECT max(b, a) FROM collate4t1;
639 }
640 } {101 101}
641 do_test collate4-4.11 {
642 execsql {
643 SELECT max(a, '101') FROM collate4t1;
644 }
645 } {11 101}
646 do_test collate4-4.12 {
647 execsql {
648 SELECT max('101', a) FROM collate4t1;
649 }
650 } {11 101}
651 do_test collate4-4.13 {
652 execsql {
653 SELECT max(b, '101') FROM collate4t1;
654 }
655 } {101 101}
656 do_test collate4-4.14 {
657 execsql {
658 SELECT max('101', b) FROM collate4t1;
659 }
660 } {101 101}
661  
662 do_test collate4-4.15 {
663 execsql {
664 DROP TABLE collate4t1;
665 }
666 } {}
667  
668 #
669 # These tests - collate4.6.* - ensure that implict INTEGER PRIMARY KEY
670 # indices do not confuse collation sequences.
671 #
672 # These indices are never used for sorting in SQLite. And you can't
673 # create another index on an INTEGER PRIMARY KEY column, so we don't have
674 # to test that.
675 # (Revised 2004-Nov-22): The ROWID can be used for sorting now.
676 #
677 do_test collate4-6.0 {
678 execsql {
679 CREATE TABLE collate4t1(a INTEGER PRIMARY KEY);
680 INSERT INTO collate4t1 VALUES(101);
681 INSERT INTO collate4t1 VALUES(10);
682 INSERT INTO collate4t1 VALUES(15);
683 }
684 } {}
685 do_test collate4-6.1 {
686 cksort {
687 SELECT * FROM collate4t1 ORDER BY 1;
688 }
689 } {10 15 101 nosort}
690 do_test collate4-6.2 {
691 cksort {
692 SELECT * FROM collate4t1 ORDER BY oid;
693 }
694 } {10 15 101 nosort}
695 do_test collate4-6.3 {
696 cksort {
697 SELECT * FROM collate4t1 ORDER BY oid||'' COLLATE TEXT;
698 }
699 } {10 101 15 sort}
700  
701 finish_test