Annotation of embedaddon/sqlite3/test/collate4.test, revision 1.1.1.1
1.1 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to collate4.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test