xref: /sqlite-3.40.0/test/collate4.test (revision ef5ecb41) 
1#
2# The author or author's hereby grant to the public domain a non-exclusive,
3# fully paid-up, perpetual, license in the software and all related
4# intellectual property to make, have made, use, have used, reproduce,
5# prepare derivative works, distribute, perform and display the work.
6#
7#*************************************************************************
8# This file implements regression tests for SQLite library.  The
9# focus of this file is testing indices that use user-defined collation
10# sequences.
11#
12
13set testdir [file dirname $argv0]
14source $testdir/tester.tcl
15
16db collate TEXT text_collate
17proc text_collate {a b} {
18  return [string compare $a $b]
19}
20
21# Do an SQL statement.  Append the search count to the end of the result.
22#
23proc count sql {
24  set ::sqlite_search_count 0
25  return [concat [execsql $sql] $::sqlite_search_count]
26}
27
28# This procedure executes the SQL.  Then it checks the generated program
29# for the SQL and appends a "nosort" to the result if the program contains the
30# SortCallback opcode.  If the program does not contain the SortCallback
31# opcode it appends "sort"
32#
33proc cksort {sql} {
34  set data [execsql $sql]
35  set prog [execsql "EXPLAIN $sql"]
36  if {[regexp Sort $prog]} {set x sort} {set x nosort}
37  lappend data $x
38  return $data
39}
40
41#
42# Test cases are organized roughly as follows:
43#
44# collate4-1.*      ORDER BY.
45# collate4-2.*      WHERE clauses.
46# collate4-3.*      constraints (primary key, unique).
47# collate4-4.*      simple min() or max() queries.
48# collate4-5.*      REINDEX command
49# collate4-6.*      INTEGER PRIMARY KEY indices.
50#
51
52#
53# These tests - collate4-1.* - check that indices are correctly
54# selected or not selected to implement ORDER BY clauses when
55# user defined collation sequences are involved.
56#
57# Because these tests also exercise all the different ways indices
58# can be created, they also serve to verify that indices are correctly
59# initialised with user-defined collation sequences when they are
60# created.
61#
62# Tests named collate4-1.1.* use indices with a single column. Tests
63# collate4-1.2.* use indices with two columns.
64#
65do_test collate4-1.1.0 {
66  execsql {
67    CREATE TABLE collate4t1(a COLLATE NOCASE, b COLLATE TEXT);
68    INSERT INTO collate4t1 VALUES( 'a', 'a' );
69    INSERT INTO collate4t1 VALUES( 'b', 'b' );
70    INSERT INTO collate4t1 VALUES( NULL, NULL );
71    INSERT INTO collate4t1 VALUES( 'B', 'B' );
72    INSERT INTO collate4t1 VALUES( 'A', 'A' );
73    CREATE INDEX collate4i1 ON collate4t1(a);
74    CREATE INDEX collate4i2 ON collate4t1(b);
75  }
76} {}
77do_test collate4-1.1.1 {
78  cksort {SELECT a FROM collate4t1 ORDER BY a}
79} {{} a A b B nosort}
80do_test collate4-1.1.2 {
81  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE NOCASE}
82} {{} a A b B nosort}
83do_test collate4-1.1.3 {
84  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE TEXT}
85} {{} A B a b sort}
86do_test collate4-1.1.4 {
87  cksort {SELECT b FROM collate4t1 ORDER BY b}
88} {{} A B a b nosort}
89do_test collate4-1.1.5 {
90  cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT}
91} {{} A B a b nosort}
92do_test collate4-1.1.6 {
93  cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE}
94} {{} A a B b sort}
95
96do_test collate4-1.1.7 {
97  execsql {
98    CREATE TABLE collate4t2(
99      a PRIMARY KEY COLLATE NOCASE,
100      b UNIQUE COLLATE TEXT
101    );
102    INSERT INTO collate4t2 VALUES( 'a', 'a' );
103    INSERT INTO collate4t2 VALUES( NULL, NULL );
104    INSERT INTO collate4t2 VALUES( 'B', 'B' );
105  }
106} {}
107do_test collate4-1.1.8 {
108  cksort {SELECT a FROM collate4t2 ORDER BY a}
109} {{} a B nosort}
110do_test collate4-1.1.9 {
111  cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE NOCASE}
112} {{} a B nosort}
113do_test collate4-1.1.10 {
114  cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE TEXT}
115} {{} B a sort}
116do_test collate4-1.1.11 {
117  cksort {SELECT b FROM collate4t2 ORDER BY b}
118} {{} B a nosort}
119do_test collate4-1.1.12 {
120  cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE TEXT}
121} {{} B a nosort}
122do_test collate4-1.1.13 {
123  cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE NOCASE}
124} {{} a B sort}
125
126do_test collate4-1.1.14 {
127  execsql {
128    CREATE TABLE collate4t3(
129      b COLLATE TEXT,
130      a COLLATE NOCASE,
131      UNIQUE(a), PRIMARY KEY(b)
132    );
133    INSERT INTO collate4t3 VALUES( 'a', 'a' );
134    INSERT INTO collate4t3 VALUES( NULL, NULL );
135    INSERT INTO collate4t3 VALUES( 'B', 'B' );
136  }
137} {}
138do_test collate4-1.1.15 {
139  cksort {SELECT a FROM collate4t3 ORDER BY a}
140} {{} a B nosort}
141do_test collate4-1.1.16 {
142  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE NOCASE}
143} {{} a B nosort}
144do_test collate4-1.1.17 {
145  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE TEXT}
146} {{} B a sort}
147do_test collate4-1.1.18 {
148  cksort {SELECT b FROM collate4t3 ORDER BY b}
149} {{} B a nosort}
150do_test collate4-1.1.19 {
151  cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE TEXT}
152} {{} B a nosort}
153do_test collate4-1.1.20 {
154  cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE NOCASE}
155} {{} a B sort}
156
157do_test collate4-1.1.21 {
158  execsql {
159    CREATE TABLE collate4t4(a COLLATE NOCASE, b COLLATE TEXT);
160    INSERT INTO collate4t4 VALUES( 'a', 'a' );
161    INSERT INTO collate4t4 VALUES( 'b', 'b' );
162    INSERT INTO collate4t4 VALUES( NULL, NULL );
163    INSERT INTO collate4t4 VALUES( 'B', 'B' );
164    INSERT INTO collate4t4 VALUES( 'A', 'A' );
165    CREATE INDEX collate4i3 ON collate4t4(a COLLATE TEXT);
166    CREATE INDEX collate4i4 ON collate4t4(b COLLATE NOCASE);
167  }
168} {}
169do_test collate4-1.1.22 {
170  cksort {SELECT a FROM collate4t4 ORDER BY a}
171} {{} A a B b sort}
172do_test collate4-1.1.23 {
173  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE}
174} {{} A a B b sort}
175do_test collate4-1.1.24 {
176  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT}
177} {{} A B a b nosort}
178do_test collate4-1.1.25 {
179  cksort {SELECT b FROM collate4t4 ORDER BY b}
180} {{} A B a b sort}
181do_test collate4-1.1.26 {
182  cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE TEXT}
183} {{} A B a b sort}
184do_test collate4-1.1.27 {
185  cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE NOCASE}
186} {{} a A b B nosort}
187
188do_test collate4-1.1.30 {
189  execsql {
190    DROP TABLE collate4t1;
191    DROP TABLE collate4t2;
192    DROP TABLE collate4t3;
193    DROP TABLE collate4t4;
194  }
195} {}
196
197do_test collate4-1.2.0 {
198  execsql {
199    CREATE TABLE collate4t1(a COLLATE NOCASE, b COLLATE TEXT);
200    INSERT INTO collate4t1 VALUES( 'a', 'a' );
201    INSERT INTO collate4t1 VALUES( 'b', 'b' );
202    INSERT INTO collate4t1 VALUES( NULL, NULL );
203    INSERT INTO collate4t1 VALUES( 'B', 'B' );
204    INSERT INTO collate4t1 VALUES( 'A', 'A' );
205    CREATE INDEX collate4i1 ON collate4t1(a, b);
206  }
207} {}
208do_test collate4-1.2.1 {
209  cksort {SELECT a FROM collate4t1 ORDER BY a}
210} {{} A a B b nosort}
211do_test collate4-1.2.2 {
212  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE nocase}
213} {{} A a B b nosort}
214do_test collate4-1.2.3 {
215  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text}
216} {{} A B a b sort}
217do_test collate4-1.2.4 {
218  cksort {SELECT a FROM collate4t1 ORDER BY a, b}
219} {{} A a B b nosort}
220do_test collate4-1.2.5 {
221  cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase}
222} {{} A a B b sort}
223do_test collate4-1.2.6 {
224  cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text}
225} {{} A a B b nosort}
226
227do_test collate4-1.2.7 {
228  execsql {
229    CREATE TABLE collate4t2(
230      a COLLATE NOCASE,
231      b COLLATE TEXT,
232      PRIMARY KEY(a, b)
233    );
234    INSERT INTO collate4t2 VALUES( 'a', 'a' );
235    INSERT INTO collate4t2 VALUES( NULL, NULL );
236    INSERT INTO collate4t2 VALUES( 'B', 'B' );
237  }
238} {}
239do_test collate4-1.2.8 {
240  cksort {SELECT a FROM collate4t2 ORDER BY a}
241} {{} a B nosort}
242do_test collate4-1.2.9 {
243  cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE nocase}
244} {{} a B nosort}
245do_test collate4-1.2.10 {
246  cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE text}
247} {{} B a sort}
248do_test collate4-1.2.11 {
249  cksort {SELECT a FROM collate4t2 ORDER BY a, b}
250} {{} a B nosort}
251do_test collate4-1.2.12 {
252  cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE nocase}
253} {{} a B sort}
254do_test collate4-1.2.13 {
255  cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE text}
256} {{} a B nosort}
257
258do_test collate4-1.2.14 {
259  execsql {
260    CREATE TABLE collate4t3(a COLLATE NOCASE, b COLLATE TEXT);
261    INSERT INTO collate4t3 VALUES( 'a', 'a' );
262    INSERT INTO collate4t3 VALUES( 'b', 'b' );
263    INSERT INTO collate4t3 VALUES( NULL, NULL );
264    INSERT INTO collate4t3 VALUES( 'B', 'B' );
265    INSERT INTO collate4t3 VALUES( 'A', 'A' );
266    CREATE INDEX collate4i2 ON collate4t3(a COLLATE TEXT, b COLLATE NOCASE);
267  }
268} {}
269do_test collate4-1.2.15 {
270  cksort {SELECT a FROM collate4t3 ORDER BY a}
271} {{} A a B b sort}
272do_test collate4-1.2.16 {
273  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase}
274} {{} A a B b sort}
275do_test collate4-1.2.17 {
276  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text}
277} {{} A B a b nosort}
278do_test collate4-1.2.18 {
279  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b}
280} {{} A B a b sort}
281do_test collate4-1.2.19 {
282  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b COLLATE nocase}
283} {{} A B a b nosort}
284do_test collate4-1.2.20 {
285  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b COLLATE text}
286} {{} A B a b sort}
287do_test collate4-1.2.21 {
288  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC}
289} {b a B A {} nosort}
290do_test collate4-1.2.22 {
291  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC, b}
292} {b a B A {} sort}
293do_test collate4-1.2.23 {
294  cksort {SELECT a FROM collate4t3
295            ORDER BY a COLLATE text DESC, b COLLATE nocase}
296} {b a B A {} sort}
297do_test collate4-1.2.24 {
298  cksort {SELECT a FROM collate4t3
299            ORDER BY a COLLATE text DESC, b COLLATE nocase DESC}
300} {b a B A {} nosort}
301
302do_test collate4-1.2.25 {
303  execsql {
304    DROP TABLE collate4t1;
305    DROP TABLE collate4t2;
306    DROP TABLE collate4t3;
307  }
308} {}
309
310#
311# These tests - collate4-2.* - check that indices are correctly
312# selected or not selected to implement WHERE clauses when user
313# defined collation sequences are involved.
314#
315# Indices may optimise WHERE clauses using <, >, <=, >=, = or IN
316# operators.
317#
318do_test collate4-2.1.0 {
319  execsql {
320    CREATE TABLE collate4t1(a COLLATE NOCASE);
321    CREATE TABLE collate4t2(b COLLATE TEXT);
322
323    INSERT INTO collate4t1 VALUES('a');
324    INSERT INTO collate4t1 VALUES('A');
325    INSERT INTO collate4t1 VALUES('b');
326    INSERT INTO collate4t1 VALUES('B');
327    INSERT INTO collate4t1 VALUES('c');
328    INSERT INTO collate4t1 VALUES('C');
329    INSERT INTO collate4t1 VALUES('d');
330    INSERT INTO collate4t1 VALUES('D');
331    INSERT INTO collate4t1 VALUES('e');
332    INSERT INTO collate4t1 VALUES('D');
333
334    INSERT INTO collate4t2 VALUES('A');
335    INSERT INTO collate4t2 VALUES('Z');
336  }
337} {}
338do_test collate4-2.1.1 {
339  count {
340    SELECT * FROM collate4t2, collate4t1 WHERE a = b;
341  }
342} {A a A A 19}
343do_test collate4-2.1.2 {
344  execsql {
345    CREATE INDEX collate4i1 ON collate4t1(a);
346  }
347  count {
348    SELECT * FROM collate4t2, collate4t1 WHERE a = b;
349  }
350} {A a A A 7}
351do_test collate4-2.1.3 {
352  count {
353    SELECT * FROM collate4t2, collate4t1 WHERE b = a;
354  }
355} {A A 19}
356do_test collate4-2.1.4 {
357  execsql {
358    DROP INDEX collate4i1;
359    CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
360  }
361  count {
362    SELECT * FROM collate4t2, collate4t1 WHERE a = b;
363  }
364} {A a A A 19}
365do_test collate4-2.1.5 {
366  count {
367    SELECT * FROM collate4t2, collate4t1 WHERE b = a;
368  }
369} {A A 5}
370do_test collate4-2.1.6 {
371  count {
372    SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
373  }
374} {a A 10}
375do_test collate4-2.1.7 {
376  execsql {
377    DROP INDEX collate4i1;
378    CREATE INDEX collate4i1 ON collate4t1(a);
379  }
380  count {
381    SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
382  }
383} {a A 8}
384do_test collate4-2.1.8 {
385  count {
386    SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
387  }
388} {a A 7}
389do_test collate4-2.1.9 {
390  execsql {
391    DROP INDEX collate4i1;
392    CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
393  }
394  count {
395    SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
396  }
397} {a A 9}
398do_test collate4-2.1.10 {
399  execsql {
400    DROP TABLE collate4t1;
401    DROP TABLE collate4t2;
402  }
403} {}
404
405do_test collate4-2.2.0 {
406  execsql {
407    CREATE TABLE collate4t1(a COLLATE nocase, b COLLATE text, c);
408    CREATE TABLE collate4t2(a COLLATE nocase, b COLLATE text, c COLLATE TEXT);
409
410    INSERT INTO collate4t1 VALUES('0', '0', '0');
411    INSERT INTO collate4t1 VALUES('0', '0', '1');
412    INSERT INTO collate4t1 VALUES('0', '1', '0');
413    INSERT INTO collate4t1 VALUES('0', '1', '1');
414    INSERT INTO collate4t1 VALUES('1', '0', '0');
415    INSERT INTO collate4t1 VALUES('1', '0', '1');
416    INSERT INTO collate4t1 VALUES('1', '1', '0');
417    INSERT INTO collate4t1 VALUES('1', '1', '1');
418    insert into collate4t2 SELECT * FROM collate4t1;
419  }
420} {}
421do_test collate4-2.2.1 {
422  count {
423    SELECT * FROM collate4t2 NATURAL JOIN collate4t1;
424  }
425} {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}
426do_test collate4-2.2.1 {
427  execsql {
428    CREATE INDEX collate4i1 ON collate4t1(a, b, c);
429  }
430  count {
431    SELECT * FROM collate4t2 NATURAL JOIN collate4t1;
432  }
433} {0 0 0 0 0 1 0 1 0 0 1 1 1 0 0 1 0 1 1 1 0 1 1 1 45}
434do_test collate4-2.2.2 {
435  execsql {
436    DROP INDEX collate4i1;
437    CREATE INDEX collate4i1 ON collate4t1(a, b, c COLLATE text);
438  }
439  count {
440    SELECT * FROM collate4t2 NATURAL JOIN collate4t1;
441  }
442} {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}
443
444do_test collate4-2.2.10 {
445  execsql {
446    DROP TABLE collate4t1;
447    DROP TABLE collate4t2;
448  }
449} {}
450
451#
452# These tests - collate4-3.* verify that indices that implement
453# UNIQUE and PRIMARY KEY constraints operate correctly with user
454# defined collation sequences.
455#
456do_test collate4-3.0 {
457  execsql {
458    CREATE TABLE collate4t1(a PRIMARY KEY COLLATE NOCASE);
459  }
460} {}
461do_test collate4-3.1 {
462  catchsql {
463    INSERT INTO collate4t1 VALUES('abc');
464    INSERT INTO collate4t1 VALUES('ABC');
465  }
466} {1 {column a is not unique}}
467do_test collate4-3.2 {
468  execsql {
469    SELECT * FROM collate4t1;
470  }
471} {abc}
472do_test collate4-3.3 {
473  catchsql {
474    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
475  }
476} {1 {column a is not unique}}
477do_test collate4-3.4 {
478  catchsql {
479    INSERT INTO collate4t1 VALUES(1);
480    UPDATE collate4t1 SET a = 'abc';
481  }
482} {1 {column a is not unique}}
483do_test collate4-3.5 {
484  execsql {
485    DROP TABLE collate4t1;
486    CREATE TABLE collate4t1(a COLLATE NOCASE UNIQUE);
487  }
488} {}
489do_test collate4-3.6 {
490  catchsql {
491    INSERT INTO collate4t1 VALUES('abc');
492    INSERT INTO collate4t1 VALUES('ABC');
493  }
494} {1 {column a is not unique}}
495do_test collate4-3.7 {
496  execsql {
497    SELECT * FROM collate4t1;
498  }
499} {abc}
500do_test collate4-3.8 {
501  catchsql {
502    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
503  }
504} {1 {column a is not unique}}
505do_test collate4-3.9 {
506  catchsql {
507    INSERT INTO collate4t1 VALUES(1);
508    UPDATE collate4t1 SET a = 'abc';
509  }
510} {1 {column a is not unique}}
511do_test collate4-3.10 {
512  execsql {
513    DROP TABLE collate4t1;
514    CREATE TABLE collate4t1(a);
515    CREATE UNIQUE INDEX collate4i1 ON collate4t1(a COLLATE NOCASE);
516  }
517} {}
518do_test collate4-3.11 {
519  catchsql {
520    INSERT INTO collate4t1 VALUES('abc');
521    INSERT INTO collate4t1 VALUES('ABC');
522  }
523} {1 {column a is not unique}}
524do_test collate4-3.12 {
525  execsql {
526    SELECT * FROM collate4t1;
527  }
528} {abc}
529do_test collate4-3.13 {
530  catchsql {
531    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
532  }
533} {1 {column a is not unique}}
534do_test collate4-3.14 {
535  catchsql {
536    INSERT INTO collate4t1 VALUES(1);
537    UPDATE collate4t1 SET a = 'abc';
538  }
539} {1 {column a is not unique}}
540
541do_test collate4-3.15 {
542  execsql {
543    DROP TABLE collate4t1;
544  }
545} {}
546
547#
548# These tests - collate4-4.* check that min() and max() only ever
549# use indices constructed with built-in collation type numeric.
550#
551# CHANGED:  min() and max() now use the collation type. If there
552# is an indice that can be used, it is used.
553#
554
555# FIX ME: min() and max() are currently broken.
556if 0 {
557
558do_test collate4-4.0 {
559  execsql {
560    CREATE TABLE collate4t1(a COLLATE TEXT);
561    INSERT INTO collate4t1 VALUES(2);
562    INSERT INTO collate4t1 VALUES(10);
563    INSERT INTO collate4t1 VALUES(20);
564    INSERT INTO collate4t1 VALUES(104);
565  }
566} {}
567do_test collate4-4.1 {
568  count {
569    SELECT max(a) FROM collate4t1
570  }
571} {20 3}
572do_test collate4-4.2 {
573  count {
574    SELECT min(a) FROM collate4t1
575  }
576} {10 3}
577do_test collate4-4.3 {
578  # Test that the index with collation type TEXT is used.
579  execsql {
580    CREATE INDEX collate4i1 ON collate4t1(a);
581  }
582  count {
583    SELECT min(a) FROM collate4t1;
584  }
585} {10 1}
586do_test collate4-4.4 {
587  count {
588    SELECT max(a) FROM collate4t1;
589  }
590} {20 1}
591do_test collate4-4.5 {
592  # Test that the index with collation type NUMERIC is not used.
593  execsql {
594    DROP INDEX collate4i1;
595    CREATE INDEX collate4i1 ON collate4t1(a COLLATE NUMERIC);
596  }
597  count {
598    SELECT min(a) FROM collate4t1;
599  }
600} {10 3}
601do_test collate4-4.6 {
602  count {
603    SELECT max(a) FROM collate4t1;
604  }
605} {20 3}
606do_test collate4-4.7 {
607  execsql {
608    DROP TABLE collate4t1;
609  }
610} {}
611
612# Also test the scalar min() and max() functions.
613#
614do_test collate4-4.8 {
615  execsql {
616    CREATE TABLE collate4t1(a NUMERIC, b TEXT,
617                            c COLLATE TEXT, d COLLATE NUMERIC);
618    INSERT INTO collate4t1 VALUES(11, 101, 1001, 10001);
619    INSERT INTO collate4t1 VALUES(20002, 2002, 202, 22);
620  }
621} {}
622do_test collate4-4.9 {
623  execsql {
624    SELECT max(a, b, c) FROM collate4t1;
625  }
626} {11 202}
627do_test collate4-4.10 {
628  execsql {
629    SELECT max(c, b, a) FROM collate4t1;
630  }
631} {11 202}
632do_test collate4-4.11 {
633  execsql {
634    SELECT max(a, b) FROM collate4t1;
635  }
636} {101 20002}
637do_test collate4-4.12 {
638  execsql {
639    SELECT max(b, a) FROM collate4t1;
640  }
641} {101 20002}
642do_test collate4-4.13 {
643  execsql {
644    SELECT max(b, a) FROM collate4t1;
645  }
646} {101 20002}
647do_test collate4-4.14 {
648  execsql {
649    SELECT max(b, '11') FROM collate4t1;
650  }
651} {11 2002}
652do_test collate4-4.15 {
653  execsql {
654    SELECT max('11', b) FROM collate4t1;
655  }
656} {11 2002}
657do_test collate4-4.16 {
658  execsql {
659    SELECT max(11, b) FROM collate4t1;
660  }
661} {101 2002}
662do_test collate4-4.17 {
663  execsql {
664    SELECT max(b, 11) FROM collate4t1;
665  }
666} {101 2002}
667do_test collate4-4.18 {
668  execsql {
669    SELECT max(c, d) FROM collate4t1;
670  }
671} {1001 22}
672do_test collate4-4.19 {
673  execsql {
674    SELECT max(d, c) FROM collate4t1;
675  }
676} {10001 202}
677do_test collate4-4.20 {
678  execsql {
679    DROP TABLE collate4t1;
680  }
681} {}
682
683}
684
685#
686# These tests - collate4-5.* - test the REINDEX command.
687#
688# FIX ME: Find out if version 3 needs REINDEX.
689if 0 {
690
691proc install_normal_collate {} {
692  db collate collate1 "string compare"
693}
694proc inverse_collate {l r} {
695  expr -1 * [string compare $l $r]
696}
697proc install_inverse_collate {} {
698  db collate collate1 inverse_collate
699}
700install_normal_collate
701
702do_test collate4-5.0 {
703  execsql {
704    CREATE TABLE collate4t1(a COLLATE collate1);
705    INSERT INTO collate4t1 VALUES('A');
706    INSERT INTO collate4t1 VALUES(NULL);
707    INSERT INTO collate4t1 VALUES('B');
708    CREATE INDEX collate4i1 ON collate4t1(a);
709  }
710} {}
711do_test collate4-5.1 {
712  cksort {
713    SELECT * FROM collate4t1 ORDER BY 1;
714  }
715} {{} A B nosort}
716do_test collate4-5.2 {
717  install_inverse_collate
718  cksort {
719    SELECT * FROM collate4t1 ORDER BY 1;
720  }
721} {{} A B nosort}      ;# This is incorrect - because we need to REINDEX
722do_test collate4-5.3 {
723  install_inverse_collate
724  cksort {
725    REINDEX collate4t1;
726    SELECT * FROM collate4t1 ORDER BY 1;
727  }
728} {{} B A nosort}
729do_test collate4-5.4 {
730  install_normal_collate
731  cksort {
732    REINDEX;
733    SELECT * FROM collate4t1 ORDER BY 1;
734  }
735} {{} A B nosort}
736do_test collate4-5.5 {
737  install_inverse_collate
738  cksort {
739    REINDEX main.collate4t1;
740    SELECT * FROM collate4t1 ORDER BY 1;
741  }
742} {{} B A nosort}
743do_test collate4-5.6 {
744  catchsql {
745    REINDEX garbage;
746  }
747} {1 {no such table: garbage}}
748do_test collate4-5.7 {
749  execsql {
750    DROP TABLE collate4t1;
751    CREATE TEMP TABLE collate4t1(a COLLATE collate1, b COLLATE collate1);
752    CREATE INDEX collatei1 ON collate4t1(a);
753    CREATE INDEX collatei2 ON collate4t1(b);
754    INSERT INTO collate4t1 VALUES(1, 1);
755    INSERT INTO collate4t1 VALUES(NULL, NULL);
756    INSERT INTO collate4t1 VALUES(2, 2);
757  }
758} {}
759do_test collate4-5.8 {
760  cksort {
761    SELECT * FROM collate4t1 ORDER BY 1
762  }
763} {{} {} 2 2 1 1 nosort}
764do_test collate4-5.9 {
765  install_normal_collate
766  cksort {
767    REINDEX;
768    SELECT * FROM collate4t1 order by 2;
769  }
770} {{} {} 1 1 2 2 nosort}
771do_test collate4-5.10 {
772  install_inverse_collate
773  cksort {
774    REINDEX collate4t1;
775    SELECT * FROM collate4t1 order by 1;
776  }
777} {{} {} 2 2 1 1 nosort}
778do_test collate4-5.11 {
779  install_normal_collate
780  cksort {
781    REINDEX temp.collate4t1;
782    SELECT * FROM collate4t1 order by 2;
783  }
784} {{} {} 1 1 2 2 nosort}
785
786# This checks that if a REINDEX operation produces a conflict an error
787# is raised and the checkpoint rolled back.
788do_test collate4-5.12 {
789  execsql {
790    BEGIN;
791    CREATE UNIQUE INDEX collate4i3 ON collate4t1(a);
792    INSERT INTO collate4t1 VALUES(3, 3);
793  }
794  db collate collate1 "expr 0 ;"
795  catchsql {
796    REINDEX;
797  }
798} {1 {indexed columns are not unique}}
799do_test collate4-5.13 {
800  execsql {
801    COMMIT;
802    SELECT * FROM collate4t1;
803  }
804} {1 1 {} {} 2 2 3 3}
805
806# Do an EXPLAIN REINDEX, just in case it leaks memory or something.
807do_test collate4-5.14 {
808  execsql {
809    EXPLAIN REINDEX;
810  }
811  expr 0
812} {0}
813do_test collate4-5.15 {
814  execsql {
815    EXPLAIN REINDEX collate4t1;
816  }
817  expr 0
818} {0}
819
820do_test collate4-5.16 {
821  execsql {
822    DROP TABLE collate4t1;
823  }
824} {}
825
826}
827
828#
829# These tests - collate4.6.* - ensure that implict INTEGER PRIMARY KEY
830# indices do not confuse collation sequences.
831#
832# These indices are never used for sorting in SQLite. And you can't
833# create another index on an INTEGER PRIMARY KEY column, so we don't have
834# to test that.
835#
836do_test collate4-6.0 {
837  execsql {
838    CREATE TABLE collate4t1(a INTEGER PRIMARY KEY);
839    INSERT INTO collate4t1 VALUES(101);
840    INSERT INTO collate4t1 VALUES(10);
841    INSERT INTO collate4t1 VALUES(15);
842  }
843} {}
844do_test collate4-6.1 {
845  cksort {
846    SELECT * FROM collate4t1 ORDER BY 1;
847  }
848} {10 15 101 sort}
849do_test collate4-6.2 {
850  cksort {
851    SELECT * FROM collate4t1 ORDER BY oid;
852  }
853} {10 15 101 sort}
854do_test collate4-6.3 {
855  cksort {
856    SELECT * FROM collate4t1 ORDER BY oid||'' COLLATE TEXT;
857  }
858} {10 101 15 sort}
859
860finish_test
861