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     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 } {}

   342 do_test collate4-2.1.1 {

   343   count {

   344     SELECT * FROM collate4t2, collate4t1 WHERE a = b;

   345   }

   346 } {A a A A 19}

   347 do_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}

   355 do_test collate4-2.1.3 {

   356   count {

   357     SELECT * FROM collate4t2, collate4t1 WHERE b = a;

   358   }

   359 } {A A 19}

   360 do_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}

   369 do_test collate4-2.1.5 {

   370   count {

   371     SELECT * FROM collate4t2, collate4t1 WHERE b = a;

   372   }

   373 } {A A 4}

   374 ifcapable 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 }

   404 do_test collate4-2.1.10 {

   405   execsql {

   406     DROP TABLE collate4t1;

   407     DROP TABLE collate4t2;

   408   }

   409 } {}

   410 

   411 do_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 } {}

   427 do_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}

   432 do_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}

   440 do_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 

   450 do_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 #

   462 do_test collate4-3.0 {

   463   execsql {

   464     CREATE TABLE collate4t1(a PRIMARY KEY COLLATE NOCASE);

   465   }

   466 } {}

   467 do_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}}

   473 do_test collate4-3.2 {

   474   execsql {

   475     SELECT * FROM collate4t1;

   476   }

   477 } {abc}

   478 do_test collate4-3.3 {

   479   catchsql {

   480     INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;

   481   }

   482 } {1 {column a is not unique}}

   483 do_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}}

   489 do_test collate4-3.5 {

   490   execsql {

   491     DROP TABLE collate4t1;

   492     CREATE TABLE collate4t1(a COLLATE NOCASE UNIQUE);

   493   }

   494 } {}

   495 do_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}}

   501 do_test collate4-3.7 {

   502   execsql {

   503     SELECT * FROM collate4t1;

   504   }

   505 } {abc}

   506 do_test collate4-3.8 {

   507   catchsql {

   508     INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;

   509   }

   510 } {1 {column a is not unique}}

   511 do_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}}

   517 do_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 } {}

   524 do_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}}

   530 do_test collate4-3.12 {

   531   execsql {

   532     SELECT * FROM collate4t1;

   533   }

   534 } {abc}

   535 do_test collate4-3.13 {

   536   catchsql {

   537     INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;

   538   }

   539 } {1 {column a is not unique}}

   540 do_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 

   547 do_test collate4-3.15 {

   548   execsql {

   549     DROP TABLE collate4t1;

   550   }

   551 } {}

   552 

   553 # Mimic the SQLite 2 collation type NUMERIC.

   554 db collate numeric numeric_collate

   555 proc 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 #

   567 do_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 } {}

   576 do_test collate4-4.1 {

   577   count {

   578     SELECT max(a) FROM collate4t1

   579   }

   580 } {20 3}

   581 do_test collate4-4.2 {

   582   count {

   583     SELECT min(a) FROM collate4t1

   584   }

   585 } {10 3}

   586 do_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 1}

   595 do_test collate4-4.4 {

   596   count {

   597     SELECT max(a) FROM collate4t1;

   598   }

   599 } {20 0}

   600 do_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}

   610 do_test collate4-4.6 {

   611   count {

   612     SELECT max(a) FROM collate4t1;

   613   }

   614 } {20 3}

   615 do_test collate4-4.7 {

   616   execsql {

   617     DROP TABLE collate4t1;

   618   }

   619 } {}

   620 

   621 # Also test the scalar min() and max() functions.

   622 #

   623 do_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 } {}

   630 do_test collate4-4.9 {

   631   execsql {

   632     SELECT max(a, b) FROM collate4t1;

   633   }

   634 } {11 11}

   635 do_test collate4-4.10 {

   636   execsql {

   637     SELECT max(b, a) FROM collate4t1;

   638   }

   639 } {101 101}

   640 do_test collate4-4.11 {

   641   execsql {

   642     SELECT max(a, '101') FROM collate4t1;

   643   }

   644 } {11 101}

   645 do_test collate4-4.12 {

   646   execsql {

   647     SELECT max('101', a) FROM collate4t1;

   648   }

   649 } {11 101}

   650 do_test collate4-4.13 {

   651   execsql {

   652     SELECT max(b, '101') FROM collate4t1;

   653   }

   654 } {101 101}

   655 do_test collate4-4.14 {

   656   execsql {

   657     SELECT max('101', b) FROM collate4t1;

   658   }

   659 } {101 101}

   660 

   661 do_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 #

   676 do_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 } {}

   684 do_test collate4-6.1 {

   685   cksort {

   686     SELECT * FROM collate4t1 ORDER BY 1;

   687   }

   688 } {10 15 101 nosort}

   689 do_test collate4-6.2 {

   690   cksort {

   691     SELECT * FROM collate4t1 ORDER BY oid;

   692   }

   693 } {10 15 101 nosort}

   694 do_test collate4-6.3 {

   695   cksort {

   696     SELECT * FROM collate4t1 ORDER BY oid||'' COLLATE TEXT;

   697   }

   698 } {10 101 15 sort}

   699 

   700 finish_test