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