xref: /sqlite-3.40.0/test/collate4.test (revision f2fcd075) 
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
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    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} {}
343do_test collate4-2.1.1 {
344  count {
345    SELECT * FROM collate4t2, collate4t1 WHERE a = b;
346  }
347} {A a A A 19}
348do_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}
356do_test collate4-2.1.3 {
357  count {
358    SELECT * FROM collate4t2, collate4t1 WHERE b = a;
359  }
360} {A A 19}
361do_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}
370do_test collate4-2.1.5 {
371  count {
372    SELECT * FROM collate4t2, collate4t1 WHERE b = a;
373  }
374} {A A 4}
375ifcapable 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}
405do_test collate4-2.1.10 {
406  execsql {
407    DROP TABLE collate4t1;
408    DROP TABLE collate4t2;
409  }
410} {}
411
412do_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} {}
428do_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}
433do_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}
441do_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
451do_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#
463do_test collate4-3.0 {
464  execsql {
465    CREATE TABLE collate4t1(a PRIMARY KEY COLLATE NOCASE);
466  }
467} {}
468do_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}}
474do_test collate4-3.2 {
475  execsql {
476    SELECT * FROM collate4t1;
477  }
478} {abc}
479do_test collate4-3.3 {
480  catchsql {
481    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
482  }
483} {1 {column a is not unique}}
484do_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}}
490do_test collate4-3.5 {
491  execsql {
492    DROP TABLE collate4t1;
493    CREATE TABLE collate4t1(a COLLATE NOCASE UNIQUE);
494  }
495} {}
496do_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}}
502do_test collate4-3.7 {
503  execsql {
504    SELECT * FROM collate4t1;
505  }
506} {abc}
507do_test collate4-3.8 {
508  catchsql {
509    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
510  }
511} {1 {column a is not unique}}
512do_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}}
518do_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} {}
525do_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}}
531do_test collate4-3.12 {
532  execsql {
533    SELECT * FROM collate4t1;
534  }
535} {abc}
536do_test collate4-3.13 {
537  catchsql {
538    INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
539  }
540} {1 {column a is not unique}}
541do_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
548do_test collate4-3.15 {
549  execsql {
550    DROP TABLE collate4t1;
551  }
552} {}
553
554# Mimic the SQLite 2 collation type NUMERIC.
555db collate numeric numeric_collate
556proc 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#
568do_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} {}
577do_test collate4-4.1 {
578  count {
579    SELECT max(a) FROM collate4t1
580  }
581} {20 3}
582do_test collate4-4.2 {
583  count {
584    SELECT min(a) FROM collate4t1
585  }
586} {10 3}
587do_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}
596do_test collate4-4.4 {
597  count {
598    SELECT max(a) FROM collate4t1;
599  }
600} {20 0}
601do_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}
611do_test collate4-4.6 {
612  count {
613    SELECT max(a) FROM collate4t1;
614  }
615} {20 3}
616do_test collate4-4.7 {
617  execsql {
618    DROP TABLE collate4t1;
619  }
620} {}
621
622# Also test the scalar min() and max() functions.
623#
624do_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} {}
631do_test collate4-4.9 {
632  execsql {
633    SELECT max(a, b) FROM collate4t1;
634  }
635} {11 11}
636do_test collate4-4.10 {
637  execsql {
638    SELECT max(b, a) FROM collate4t1;
639  }
640} {101 101}
641do_test collate4-4.11 {
642  execsql {
643    SELECT max(a, '101') FROM collate4t1;
644  }
645} {11 101}
646do_test collate4-4.12 {
647  execsql {
648    SELECT max('101', a) FROM collate4t1;
649  }
650} {11 101}
651do_test collate4-4.13 {
652  execsql {
653    SELECT max(b, '101') FROM collate4t1;
654  }
655} {101 101}
656do_test collate4-4.14 {
657  execsql {
658    SELECT max('101', b) FROM collate4t1;
659  }
660} {101 101}
661
662do_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#
677do_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} {}
685do_test collate4-6.1 {
686  cksort {
687    SELECT * FROM collate4t1 ORDER BY 1;
688  }
689} {10 15 101 nosort}
690do_test collate4-6.2 {
691  cksort {
692    SELECT * FROM collate4t1 ORDER BY oid;
693  }
694} {10 15 101 nosort}
695do_test collate4-6.3 {
696  cksort {
697    SELECT * FROM collate4t1 ORDER BY oid||'' COLLATE TEXT;
698  }
699} {10 101 15 sort}
700
701finish_test
702