xref: /sqlite-3.40.0/test/collate4.test (revision 9012bcbc) 
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.6 2004/12/19 00:11:36 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}
374do_test collate4-2.1.6 {
375  count {
376    SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
377  }
378} {a A 10}
379do_test collate4-2.1.7 {
380  execsql {
381    DROP INDEX collate4i1;
382    CREATE INDEX collate4i1 ON collate4t1(a);
383  }
384  count {
385    SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
386  }
387} {a A 6}
388do_test collate4-2.1.8 {
389  count {
390    SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
391  }
392} {a A 5}
393do_test collate4-2.1.9 {
394  execsql {
395    DROP INDEX collate4i1;
396    CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
397  }
398  count {
399    SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
400  }
401} {a A 9}
402do_test collate4-2.1.10 {
403  execsql {
404    DROP TABLE collate4t1;
405    DROP TABLE collate4t2;
406  }
407} {}
408
409do_test collate4-2.2.0 {
410  execsql {
411    CREATE TABLE collate4t1(a COLLATE nocase, b COLLATE text, c);
412    CREATE TABLE collate4t2(a COLLATE nocase, b COLLATE text, c COLLATE TEXT);
413
414    INSERT INTO collate4t1 VALUES('0', '0', '0');
415    INSERT INTO collate4t1 VALUES('0', '0', '1');
416    INSERT INTO collate4t1 VALUES('0', '1', '0');
417    INSERT INTO collate4t1 VALUES('0', '1', '1');
418    INSERT INTO collate4t1 VALUES('1', '0', '0');
419    INSERT INTO collate4t1 VALUES('1', '0', '1');
420    INSERT INTO collate4t1 VALUES('1', '1', '0');
421    INSERT INTO collate4t1 VALUES('1', '1', '1');
422    insert into collate4t2 SELECT * FROM collate4t1;
423  }
424} {}
425do_test collate4-2.2.1 {
426  count {
427    SELECT * FROM collate4t2 NATURAL JOIN collate4t1;
428  }
429} {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}
430do_test collate4-2.2.1b {
431  execsql {
432    CREATE INDEX collate4i1 ON collate4t1(a, b, c);
433  }
434  count {
435    SELECT * FROM collate4t2 NATURAL JOIN collate4t1;
436  }
437} {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}
438do_test collate4-2.2.2 {
439  execsql {
440    DROP INDEX collate4i1;
441    CREATE INDEX collate4i1 ON collate4t1(a, b, c COLLATE text);
442  }
443  count {
444    SELECT * FROM collate4t2 NATURAL JOIN collate4t1;
445  }
446} {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}
447
448do_test collate4-2.2.10 {
449  execsql {
450    DROP TABLE collate4t1;
451    DROP TABLE collate4t2;
452  }
453} {}
454
455#
456# These tests - collate4-3.* verify that indices that implement
457# UNIQUE and PRIMARY KEY constraints operate correctly with user
458# defined collation sequences.
459#
460do_test collate4-3.0 {
461  execsql {
462    CREATE TABLE collate4t1(a PRIMARY KEY COLLATE NOCASE);
463  }
464} {}
465do_test collate4-3.1 {
466  catchsql {
467    INSERT INTO collate4t1 VALUES('abc');
468    INSERT INTO collate4t1 VALUES('ABC');
469  }
470} {1 {column a is not unique}}
471do_test collate4-3.2 {
472  execsql {
473    SELECT * FROM collate4t1;
474  }
475} {abc}
476do_test collate4-3.3 {
477  catchsql {
478    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
479  }
480} {1 {column a is not unique}}
481do_test collate4-3.4 {
482  catchsql {
483    INSERT INTO collate4t1 VALUES(1);
484    UPDATE collate4t1 SET a = 'abc';
485  }
486} {1 {column a is not unique}}
487do_test collate4-3.5 {
488  execsql {
489    DROP TABLE collate4t1;
490    CREATE TABLE collate4t1(a COLLATE NOCASE UNIQUE);
491  }
492} {}
493do_test collate4-3.6 {
494  catchsql {
495    INSERT INTO collate4t1 VALUES('abc');
496    INSERT INTO collate4t1 VALUES('ABC');
497  }
498} {1 {column a is not unique}}
499do_test collate4-3.7 {
500  execsql {
501    SELECT * FROM collate4t1;
502  }
503} {abc}
504do_test collate4-3.8 {
505  catchsql {
506    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
507  }
508} {1 {column a is not unique}}
509do_test collate4-3.9 {
510  catchsql {
511    INSERT INTO collate4t1 VALUES(1);
512    UPDATE collate4t1 SET a = 'abc';
513  }
514} {1 {column a is not unique}}
515do_test collate4-3.10 {
516  execsql {
517    DROP TABLE collate4t1;
518    CREATE TABLE collate4t1(a);
519    CREATE UNIQUE INDEX collate4i1 ON collate4t1(a COLLATE NOCASE);
520  }
521} {}
522do_test collate4-3.11 {
523  catchsql {
524    INSERT INTO collate4t1 VALUES('abc');
525    INSERT INTO collate4t1 VALUES('ABC');
526  }
527} {1 {column a is not unique}}
528do_test collate4-3.12 {
529  execsql {
530    SELECT * FROM collate4t1;
531  }
532} {abc}
533do_test collate4-3.13 {
534  catchsql {
535    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
536  }
537} {1 {column a is not unique}}
538do_test collate4-3.14 {
539  catchsql {
540    INSERT INTO collate4t1 VALUES(1);
541    UPDATE collate4t1 SET a = 'abc';
542  }
543} {1 {column a is not unique}}
544
545do_test collate4-3.15 {
546  execsql {
547    DROP TABLE collate4t1;
548  }
549} {}
550
551# Mimic the SQLite 2 collation type NUMERIC.
552db collate numeric numeric_collate
553proc numeric_collate {lhs rhs} {
554  if {$lhs == $rhs} {return 0}
555  return [expr ($lhs>$rhs)?1:-1]
556}
557
558#
559# These tests - collate4-4.* check that min() and max() only ever
560# use indices constructed with built-in collation type numeric.
561#
562# CHANGED:  min() and max() now use the collation type. If there
563# is an indice that can be used, it is used.
564#
565do_test collate4-4.0 {
566  execsql {
567    CREATE TABLE collate4t1(a COLLATE TEXT);
568    INSERT INTO collate4t1 VALUES('2');
569    INSERT INTO collate4t1 VALUES('10');
570    INSERT INTO collate4t1 VALUES('20');
571    INSERT INTO collate4t1 VALUES('104');
572  }
573} {}
574do_test collate4-4.1 {
575  count {
576    SELECT max(a) FROM collate4t1
577  }
578} {20 3}
579do_test collate4-4.2 {
580  count {
581    SELECT min(a) FROM collate4t1
582  }
583} {10 3}
584do_test collate4-4.3 {
585  # Test that the index with collation type TEXT is used.
586  execsql {
587    CREATE INDEX collate4i1 ON collate4t1(a);
588  }
589  count {
590    SELECT min(a) FROM collate4t1;
591  }
592} {10 2}
593do_test collate4-4.4 {
594  count {
595    SELECT max(a) FROM collate4t1;
596  }
597} {20 1}
598do_test collate4-4.5 {
599  # Test that the index with collation type NUMERIC is not used.
600  execsql {
601    DROP INDEX collate4i1;
602    CREATE INDEX collate4i1 ON collate4t1(a COLLATE NUMERIC);
603  }
604  count {
605    SELECT min(a) FROM collate4t1;
606  }
607} {10 3}
608do_test collate4-4.6 {
609  count {
610    SELECT max(a) FROM collate4t1;
611  }
612} {20 3}
613do_test collate4-4.7 {
614  execsql {
615    DROP TABLE collate4t1;
616  }
617} {}
618
619# Also test the scalar min() and max() functions.
620#
621do_test collate4-4.8 {
622  execsql {
623    CREATE TABLE collate4t1(a COLLATE TEXT, b COLLATE NUMERIC);
624    INSERT INTO collate4t1 VALUES('11', '101');
625    INSERT INTO collate4t1 VALUES('101', '11')
626  }
627} {}
628do_test collate4-4.9 {
629  execsql {
630    SELECT max(a, b) FROM collate4t1;
631  }
632} {11 11}
633do_test collate4-4.10 {
634  execsql {
635    SELECT max(b, a) FROM collate4t1;
636  }
637} {101 101}
638do_test collate4-4.11 {
639  execsql {
640    SELECT max(a, '101') FROM collate4t1;
641  }
642} {11 101}
643do_test collate4-4.12 {
644  execsql {
645    SELECT max('101', a) FROM collate4t1;
646  }
647} {11 101}
648do_test collate4-4.13 {
649  execsql {
650    SELECT max(b, '101') FROM collate4t1;
651  }
652} {101 101}
653do_test collate4-4.14 {
654  execsql {
655    SELECT max('101', b) FROM collate4t1;
656  }
657} {101 101}
658
659do_test collate4-4.15 {
660  execsql {
661    DROP TABLE collate4t1;
662  }
663} {}
664
665#
666# These tests - collate4.6.* - ensure that implict INTEGER PRIMARY KEY
667# indices do not confuse collation sequences.
668#
669# These indices are never used for sorting in SQLite. And you can't
670# create another index on an INTEGER PRIMARY KEY column, so we don't have
671# to test that.
672# (Revised 2004-Nov-22):  The ROWID can be used for sorting now.
673#
674do_test collate4-6.0 {
675  execsql {
676    CREATE TABLE collate4t1(a INTEGER PRIMARY KEY);
677    INSERT INTO collate4t1 VALUES(101);
678    INSERT INTO collate4t1 VALUES(10);
679    INSERT INTO collate4t1 VALUES(15);
680  }
681} {}
682do_test collate4-6.1 {
683  cksort {
684    SELECT * FROM collate4t1 ORDER BY 1;
685  }
686} {10 15 101 nosort}
687do_test collate4-6.2 {
688  cksort {
689    SELECT * FROM collate4t1 ORDER BY oid;
690  }
691} {10 15 101 nosort}
692do_test collate4-6.3 {
693  cksort {
694    SELECT * FROM collate4t1 ORDER BY oid||'' COLLATE TEXT;
695  }
696} {10 101 15 sort}
697
698finish_test
699