xref: /sqlite-3.40.0/test/collate4.test (revision 4dcbdbff) 
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.8 2005/04/01 10:47:40 drh Exp $
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20db collate TEXT text_collate
21proc 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#
27proc 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#
37proc 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#
69do_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} {}
81do_test collate4-1.1.1 {
82  cksort {SELECT a FROM collate4t1 ORDER BY a}
83} {{} a A b B nosort}
84do_test collate4-1.1.2 {
85  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE NOCASE}
86} {{} a A b B nosort}
87do_test collate4-1.1.3 {
88  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE TEXT}
89} {{} A B a b sort}
90do_test collate4-1.1.4 {
91  cksort {SELECT b FROM collate4t1 ORDER BY b}
92} {{} A B a b nosort}
93do_test collate4-1.1.5 {
94  cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT}
95} {{} A B a b nosort}
96do_test collate4-1.1.6 {
97  cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE}
98} {{} a A b B sort}
99
100do_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} {}
111do_test collate4-1.1.8 {
112  cksort {SELECT a FROM collate4t2 ORDER BY a}
113} {{} a B nosort}
114do_test collate4-1.1.9 {
115  cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE NOCASE}
116} {{} a B nosort}
117do_test collate4-1.1.10 {
118  cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE TEXT}
119} {{} B a sort}
120do_test collate4-1.1.11 {
121  cksort {SELECT b FROM collate4t2 ORDER BY b}
122} {{} B a nosort}
123do_test collate4-1.1.12 {
124  cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE TEXT}
125} {{} B a nosort}
126do_test collate4-1.1.13 {
127  cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE NOCASE}
128} {{} a B sort}
129
130do_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} {}
142do_test collate4-1.1.15 {
143  cksort {SELECT a FROM collate4t3 ORDER BY a}
144} {{} a B nosort}
145do_test collate4-1.1.16 {
146  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE NOCASE}
147} {{} a B nosort}
148do_test collate4-1.1.17 {
149  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE TEXT}
150} {{} B a sort}
151do_test collate4-1.1.18 {
152  cksort {SELECT b FROM collate4t3 ORDER BY b}
153} {{} B a nosort}
154do_test collate4-1.1.19 {
155  cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE TEXT}
156} {{} B a nosort}
157do_test collate4-1.1.20 {
158  cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE NOCASE}
159} {{} a B sort}
160
161do_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} {}
173do_test collate4-1.1.22 {
174  cksort {SELECT a FROM collate4t4 ORDER BY a}
175} {{} a A b B sort}
176do_test collate4-1.1.23 {
177  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE}
178} {{} a A b B sort}
179do_test collate4-1.1.24 {
180  cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT}
181} {{} A B a b nosort}
182do_test collate4-1.1.25 {
183  cksort {SELECT b FROM collate4t4 ORDER BY b}
184} {{} A B a b sort}
185do_test collate4-1.1.26 {
186  cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE TEXT}
187} {{} A B a b sort}
188do_test collate4-1.1.27 {
189  cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE NOCASE}
190} {{} a A b B nosort}
191
192do_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
201do_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} {}
212do_test collate4-1.2.1 {
213  cksort {SELECT a FROM collate4t1 ORDER BY a}
214} {{} A a B b nosort}
215do_test collate4-1.2.2 {
216  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE nocase}
217} {{} A a B b nosort}
218do_test collate4-1.2.3 {
219  cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text}
220} {{} A B a b sort}
221do_test collate4-1.2.4 {
222  cksort {SELECT a FROM collate4t1 ORDER BY a, b}
223} {{} A a B b nosort}
224do_test collate4-1.2.5 {
225  cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase}
226} {{} a A b B sort}
227do_test collate4-1.2.6 {
228  cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text}
229} {{} A a B b nosort}
230
231do_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} {}
243do_test collate4-1.2.8 {
244  cksort {SELECT a FROM collate4t2 ORDER BY a}
245} {{} a B nosort}
246do_test collate4-1.2.9 {
247  cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE nocase}
248} {{} a B nosort}
249do_test collate4-1.2.10 {
250  cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE text}
251} {{} B a sort}
252do_test collate4-1.2.11 {
253  cksort {SELECT a FROM collate4t2 ORDER BY a, b}
254} {{} a B nosort}
255do_test collate4-1.2.12 {
256  cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE nocase}
257} {{} a B sort}
258do_test collate4-1.2.13 {
259  cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE text}
260} {{} a B nosort}
261
262do_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} {}
273do_test collate4-1.2.15 {
274  cksort {SELECT a FROM collate4t3 ORDER BY a}
275} {{} a A b B sort}
276do_test collate4-1.2.16 {
277  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase}
278} {{} a A b B sort}
279do_test collate4-1.2.17 {
280  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text}
281} {{} A B a b nosort}
282do_test collate4-1.2.18 {
283  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b}
284} {{} A B a b sort}
285do_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}
288do_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}
291do_test collate4-1.2.21 {
292  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC}
293} {b a B A {} nosort}
294do_test collate4-1.2.22 {
295  cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC, b}
296} {b a B A {} sort}
297do_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}
301do_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
306do_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#
322do_test collate4-2.1.0 {
323  execsql {
324    CREATE TABLE collate4t1(a COLLATE NOCASE);
325    CREATE TABLE collate4t2(b COLLATE TEXT);
326
327    INSERT INTO collate4t1 VALUES('a');
328    INSERT INTO collate4t1 VALUES('A');
329    INSERT INTO collate4t1 VALUES('b');
330    INSERT INTO collate4t1 VALUES('B');
331    INSERT INTO collate4t1 VALUES('c');
332    INSERT INTO collate4t1 VALUES('C');
333    INSERT INTO collate4t1 VALUES('d');
334    INSERT INTO collate4t1 VALUES('D');
335    INSERT INTO collate4t1 VALUES('e');
336    INSERT INTO collate4t1 VALUES('D');
337
338    INSERT INTO collate4t2 VALUES('A');
339    INSERT INTO collate4t2 VALUES('Z');
340  }
341} {}
342do_test collate4-2.1.1 {
343  count {
344    SELECT * FROM collate4t2, collate4t1 WHERE a = b;
345  }
346} {A a A A 19}
347do_test collate4-2.1.2 {
348  execsql {
349    CREATE INDEX collate4i1 ON collate4t1(a);
350  }
351  count {
352    SELECT * FROM collate4t2, collate4t1 WHERE a = b;
353  }
354} {A a A A 5}
355do_test collate4-2.1.3 {
356  count {
357    SELECT * FROM collate4t2, collate4t1 WHERE b = a;
358  }
359} {A A 19}
360do_test collate4-2.1.4 {
361  execsql {
362    DROP INDEX collate4i1;
363    CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
364  }
365  count {
366    SELECT * FROM collate4t2, collate4t1 WHERE a = b;
367  }
368} {A a A A 19}
369do_test collate4-2.1.5 {
370  count {
371    SELECT * FROM collate4t2, collate4t1 WHERE b = a;
372  }
373} {A A 4}
374ifcapable subquery {
375  do_test collate4-2.1.6 {
376    count {
377      SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
378    }
379  } {a A 10}
380  do_test collate4-2.1.7 {
381    execsql {
382      DROP INDEX collate4i1;
383      CREATE INDEX collate4i1 ON collate4t1(a);
384    }
385    count {
386      SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
387    }
388  } {a A 6}
389  do_test collate4-2.1.8 {
390    count {
391      SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
392    }
393  } {a A 5}
394  do_test collate4-2.1.9 {
395    execsql {
396      DROP INDEX collate4i1;
397      CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
398    }
399    count {
400      SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
401    }
402  } {a A 9}
403}
404do_test collate4-2.1.10 {
405  execsql {
406    DROP TABLE collate4t1;
407    DROP TABLE collate4t2;
408  }
409} {}
410
411do_test collate4-2.2.0 {
412  execsql {
413    CREATE TABLE collate4t1(a COLLATE nocase, b COLLATE text, c);
414    CREATE TABLE collate4t2(a COLLATE nocase, b COLLATE text, c COLLATE TEXT);
415
416    INSERT INTO collate4t1 VALUES('0', '0', '0');
417    INSERT INTO collate4t1 VALUES('0', '0', '1');
418    INSERT INTO collate4t1 VALUES('0', '1', '0');
419    INSERT INTO collate4t1 VALUES('0', '1', '1');
420    INSERT INTO collate4t1 VALUES('1', '0', '0');
421    INSERT INTO collate4t1 VALUES('1', '0', '1');
422    INSERT INTO collate4t1 VALUES('1', '1', '0');
423    INSERT INTO collate4t1 VALUES('1', '1', '1');
424    insert into collate4t2 SELECT * FROM collate4t1;
425  }
426} {}
427do_test collate4-2.2.1 {
428  count {
429    SELECT * FROM collate4t2 NATURAL JOIN collate4t1;
430  }
431} {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}
432do_test collate4-2.2.1b {
433  execsql {
434    CREATE INDEX collate4i1 ON collate4t1(a, b, c);
435  }
436  count {
437    SELECT * FROM collate4t2 NATURAL JOIN collate4t1;
438  }
439} {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}
440do_test collate4-2.2.2 {
441  execsql {
442    DROP INDEX collate4i1;
443    CREATE INDEX collate4i1 ON collate4t1(a, b, c COLLATE text);
444  }
445  count {
446    SELECT * FROM collate4t2 NATURAL JOIN collate4t1;
447  }
448} {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}
449
450do_test collate4-2.2.10 {
451  execsql {
452    DROP TABLE collate4t1;
453    DROP TABLE collate4t2;
454  }
455} {}
456
457#
458# These tests - collate4-3.* verify that indices that implement
459# UNIQUE and PRIMARY KEY constraints operate correctly with user
460# defined collation sequences.
461#
462do_test collate4-3.0 {
463  execsql {
464    CREATE TABLE collate4t1(a PRIMARY KEY COLLATE NOCASE);
465  }
466} {}
467do_test collate4-3.1 {
468  catchsql {
469    INSERT INTO collate4t1 VALUES('abc');
470    INSERT INTO collate4t1 VALUES('ABC');
471  }
472} {1 {column a is not unique}}
473do_test collate4-3.2 {
474  execsql {
475    SELECT * FROM collate4t1;
476  }
477} {abc}
478do_test collate4-3.3 {
479  catchsql {
480    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
481  }
482} {1 {column a is not unique}}
483do_test collate4-3.4 {
484  catchsql {
485    INSERT INTO collate4t1 VALUES(1);
486    UPDATE collate4t1 SET a = 'abc';
487  }
488} {1 {column a is not unique}}
489do_test collate4-3.5 {
490  execsql {
491    DROP TABLE collate4t1;
492    CREATE TABLE collate4t1(a COLLATE NOCASE UNIQUE);
493  }
494} {}
495do_test collate4-3.6 {
496  catchsql {
497    INSERT INTO collate4t1 VALUES('abc');
498    INSERT INTO collate4t1 VALUES('ABC');
499  }
500} {1 {column a is not unique}}
501do_test collate4-3.7 {
502  execsql {
503    SELECT * FROM collate4t1;
504  }
505} {abc}
506do_test collate4-3.8 {
507  catchsql {
508    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
509  }
510} {1 {column a is not unique}}
511do_test collate4-3.9 {
512  catchsql {
513    INSERT INTO collate4t1 VALUES(1);
514    UPDATE collate4t1 SET a = 'abc';
515  }
516} {1 {column a is not unique}}
517do_test collate4-3.10 {
518  execsql {
519    DROP TABLE collate4t1;
520    CREATE TABLE collate4t1(a);
521    CREATE UNIQUE INDEX collate4i1 ON collate4t1(a COLLATE NOCASE);
522  }
523} {}
524do_test collate4-3.11 {
525  catchsql {
526    INSERT INTO collate4t1 VALUES('abc');
527    INSERT INTO collate4t1 VALUES('ABC');
528  }
529} {1 {column a is not unique}}
530do_test collate4-3.12 {
531  execsql {
532    SELECT * FROM collate4t1;
533  }
534} {abc}
535do_test collate4-3.13 {
536  catchsql {
537    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
538  }
539} {1 {column a is not unique}}
540do_test collate4-3.14 {
541  catchsql {
542    INSERT INTO collate4t1 VALUES(1);
543    UPDATE collate4t1 SET a = 'abc';
544  }
545} {1 {column a is not unique}}
546
547do_test collate4-3.15 {
548  execsql {
549    DROP TABLE collate4t1;
550  }
551} {}
552
553# Mimic the SQLite 2 collation type NUMERIC.
554db collate numeric numeric_collate
555proc numeric_collate {lhs rhs} {
556  if {$lhs == $rhs} {return 0}
557  return [expr ($lhs>$rhs)?1:-1]
558}
559
560#
561# These tests - collate4-4.* check that min() and max() only ever
562# use indices constructed with built-in collation type numeric.
563#
564# CHANGED:  min() and max() now use the collation type. If there
565# is an indice that can be used, it is used.
566#
567do_test collate4-4.0 {
568  execsql {
569    CREATE TABLE collate4t1(a COLLATE TEXT);
570    INSERT INTO collate4t1 VALUES('2');
571    INSERT INTO collate4t1 VALUES('10');
572    INSERT INTO collate4t1 VALUES('20');
573    INSERT INTO collate4t1 VALUES('104');
574  }
575} {}
576do_test collate4-4.1 {
577  count {
578    SELECT max(a) FROM collate4t1
579  }
580} {20 3}
581do_test collate4-4.2 {
582  count {
583    SELECT min(a) FROM collate4t1
584  }
585} {10 3}
586do_test collate4-4.3 {
587  # Test that the index with collation type TEXT is used.
588  execsql {
589    CREATE INDEX collate4i1 ON collate4t1(a);
590  }
591  count {
592    SELECT min(a) FROM collate4t1;
593  }
594} {10 2}
595do_test collate4-4.4 {
596  count {
597    SELECT max(a) FROM collate4t1;
598  }
599} {20 1}
600do_test collate4-4.5 {
601  # Test that the index with collation type NUMERIC is not used.
602  execsql {
603    DROP INDEX collate4i1;
604    CREATE INDEX collate4i1 ON collate4t1(a COLLATE NUMERIC);
605  }
606  count {
607    SELECT min(a) FROM collate4t1;
608  }
609} {10 3}
610do_test collate4-4.6 {
611  count {
612    SELECT max(a) FROM collate4t1;
613  }
614} {20 3}
615do_test collate4-4.7 {
616  execsql {
617    DROP TABLE collate4t1;
618  }
619} {}
620
621# Also test the scalar min() and max() functions.
622#
623do_test collate4-4.8 {
624  execsql {
625    CREATE TABLE collate4t1(a COLLATE TEXT, b COLLATE NUMERIC);
626    INSERT INTO collate4t1 VALUES('11', '101');
627    INSERT INTO collate4t1 VALUES('101', '11')
628  }
629} {}
630do_test collate4-4.9 {
631  execsql {
632    SELECT max(a, b) FROM collate4t1;
633  }
634} {11 11}
635do_test collate4-4.10 {
636  execsql {
637    SELECT max(b, a) FROM collate4t1;
638  }
639} {101 101}
640do_test collate4-4.11 {
641  execsql {
642    SELECT max(a, '101') FROM collate4t1;
643  }
644} {11 101}
645do_test collate4-4.12 {
646  execsql {
647    SELECT max('101', a) FROM collate4t1;
648  }
649} {11 101}
650do_test collate4-4.13 {
651  execsql {
652    SELECT max(b, '101') FROM collate4t1;
653  }
654} {101 101}
655do_test collate4-4.14 {
656  execsql {
657    SELECT max('101', b) FROM collate4t1;
658  }
659} {101 101}
660
661do_test collate4-4.15 {
662  execsql {
663    DROP TABLE collate4t1;
664  }
665} {}
666
667#
668# These tests - collate4.6.* - ensure that implict INTEGER PRIMARY KEY
669# indices do not confuse collation sequences.
670#
671# These indices are never used for sorting in SQLite. And you can't
672# create another index on an INTEGER PRIMARY KEY column, so we don't have
673# to test that.
674# (Revised 2004-Nov-22):  The ROWID can be used for sorting now.
675#
676do_test collate4-6.0 {
677  execsql {
678    CREATE TABLE collate4t1(a INTEGER PRIMARY KEY);
679    INSERT INTO collate4t1 VALUES(101);
680    INSERT INTO collate4t1 VALUES(10);
681    INSERT INTO collate4t1 VALUES(15);
682  }
683} {}
684do_test collate4-6.1 {
685  cksort {
686    SELECT * FROM collate4t1 ORDER BY 1;
687  }
688} {10 15 101 nosort}
689do_test collate4-6.2 {
690  cksort {
691    SELECT * FROM collate4t1 ORDER BY oid;
692  }
693} {10 15 101 nosort}
694do_test collate4-6.3 {
695  cksort {
696    SELECT * FROM collate4t1 ORDER BY oid||'' COLLATE TEXT;
697  }
698} {10 101 15 sort}
699
700finish_test
701