sqlite3/test/collate4.test

   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
  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 2}
 595 do_test collate4-4.4 {
 596   count {
 597     SELECT max(a) FROM collate4t1;
 598   }
 599 } {20 1}
 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