1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/collate4.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,700 @@
1.4 +#
1.5 +# 2001 September 15
1.6 +#
1.7 +# The author disclaims copyright to this source code. In place of
1.8 +# a legal notice, here is a blessing:
1.9 +#
1.10 +# May you do good and not evil.
1.11 +# May you find forgiveness for yourself and forgive others.
1.12 +# May you share freely, never taking more than you give.
1.13 +#
1.14 +#***********************************************************************
1.15 +# This file implements regression tests for SQLite library. The
1.16 +# focus of this script is page cache subsystem.
1.17 +#
1.18 +# $Id: collate4.test,v 1.9 2008/01/05 17:39:30 danielk1977 Exp $
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +db collate TEXT text_collate
1.24 +proc text_collate {a b} {
1.25 + return [string compare $a $b]
1.26 +}
1.27 +
1.28 +# Do an SQL statement. Append the search count to the end of the result.
1.29 +#
1.30 +proc count sql {
1.31 + set ::sqlite_search_count 0
1.32 + return [concat [execsql $sql] $::sqlite_search_count]
1.33 +}
1.34 +
1.35 +# This procedure executes the SQL. Then it checks the generated program
1.36 +# for the SQL and appends a "nosort" to the result if the program contains the
1.37 +# SortCallback opcode. If the program does not contain the SortCallback
1.38 +# opcode it appends "sort"
1.39 +#
1.40 +proc cksort {sql} {
1.41 + set ::sqlite_sort_count 0
1.42 + set data [execsql $sql]
1.43 + if {$::sqlite_sort_count} {set x sort} {set x nosort}
1.44 + lappend data $x
1.45 + return $data
1.46 +}
1.47 +
1.48 +#
1.49 +# Test cases are organized roughly as follows:
1.50 +#
1.51 +# collate4-1.* ORDER BY.
1.52 +# collate4-2.* WHERE clauses.
1.53 +# collate4-3.* constraints (primary key, unique).
1.54 +# collate4-4.* simple min() or max() queries.
1.55 +# collate4-5.* REINDEX command
1.56 +# collate4-6.* INTEGER PRIMARY KEY indices.
1.57 +#
1.58 +
1.59 +#
1.60 +# These tests - collate4-1.* - check that indices are correctly
1.61 +# selected or not selected to implement ORDER BY clauses when
1.62 +# user defined collation sequences are involved.
1.63 +#
1.64 +# Because these tests also exercise all the different ways indices
1.65 +# can be created, they also serve to verify that indices are correctly
1.66 +# initialised with user-defined collation sequences when they are
1.67 +# created.
1.68 +#
1.69 +# Tests named collate4-1.1.* use indices with a single column. Tests
1.70 +# collate4-1.2.* use indices with two columns.
1.71 +#
1.72 +do_test collate4-1.1.0 {
1.73 + execsql {
1.74 + CREATE TABLE collate4t1(a COLLATE NOCASE, b COLLATE TEXT);
1.75 + INSERT INTO collate4t1 VALUES( 'a', 'a' );
1.76 + INSERT INTO collate4t1 VALUES( 'b', 'b' );
1.77 + INSERT INTO collate4t1 VALUES( NULL, NULL );
1.78 + INSERT INTO collate4t1 VALUES( 'B', 'B' );
1.79 + INSERT INTO collate4t1 VALUES( 'A', 'A' );
1.80 + CREATE INDEX collate4i1 ON collate4t1(a);
1.81 + CREATE INDEX collate4i2 ON collate4t1(b);
1.82 + }
1.83 +} {}
1.84 +do_test collate4-1.1.1 {
1.85 + cksort {SELECT a FROM collate4t1 ORDER BY a}
1.86 +} {{} a A b B nosort}
1.87 +do_test collate4-1.1.2 {
1.88 + cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE NOCASE}
1.89 +} {{} a A b B nosort}
1.90 +do_test collate4-1.1.3 {
1.91 + cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE TEXT}
1.92 +} {{} A B a b sort}
1.93 +do_test collate4-1.1.4 {
1.94 + cksort {SELECT b FROM collate4t1 ORDER BY b}
1.95 +} {{} A B a b nosort}
1.96 +do_test collate4-1.1.5 {
1.97 + cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT}
1.98 +} {{} A B a b nosort}
1.99 +do_test collate4-1.1.6 {
1.100 + cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE}
1.101 +} {{} a A b B sort}
1.102 +
1.103 +do_test collate4-1.1.7 {
1.104 + execsql {
1.105 + CREATE TABLE collate4t2(
1.106 + a PRIMARY KEY COLLATE NOCASE,
1.107 + b UNIQUE COLLATE TEXT
1.108 + );
1.109 + INSERT INTO collate4t2 VALUES( 'a', 'a' );
1.110 + INSERT INTO collate4t2 VALUES( NULL, NULL );
1.111 + INSERT INTO collate4t2 VALUES( 'B', 'B' );
1.112 + }
1.113 +} {}
1.114 +do_test collate4-1.1.8 {
1.115 + cksort {SELECT a FROM collate4t2 ORDER BY a}
1.116 +} {{} a B nosort}
1.117 +do_test collate4-1.1.9 {
1.118 + cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE NOCASE}
1.119 +} {{} a B nosort}
1.120 +do_test collate4-1.1.10 {
1.121 + cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE TEXT}
1.122 +} {{} B a sort}
1.123 +do_test collate4-1.1.11 {
1.124 + cksort {SELECT b FROM collate4t2 ORDER BY b}
1.125 +} {{} B a nosort}
1.126 +do_test collate4-1.1.12 {
1.127 + cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE TEXT}
1.128 +} {{} B a nosort}
1.129 +do_test collate4-1.1.13 {
1.130 + cksort {SELECT b FROM collate4t2 ORDER BY b COLLATE NOCASE}
1.131 +} {{} a B sort}
1.132 +
1.133 +do_test collate4-1.1.14 {
1.134 + execsql {
1.135 + CREATE TABLE collate4t3(
1.136 + b COLLATE TEXT,
1.137 + a COLLATE NOCASE,
1.138 + UNIQUE(a), PRIMARY KEY(b)
1.139 + );
1.140 + INSERT INTO collate4t3 VALUES( 'a', 'a' );
1.141 + INSERT INTO collate4t3 VALUES( NULL, NULL );
1.142 + INSERT INTO collate4t3 VALUES( 'B', 'B' );
1.143 + }
1.144 +} {}
1.145 +do_test collate4-1.1.15 {
1.146 + cksort {SELECT a FROM collate4t3 ORDER BY a}
1.147 +} {{} a B nosort}
1.148 +do_test collate4-1.1.16 {
1.149 + cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE NOCASE}
1.150 +} {{} a B nosort}
1.151 +do_test collate4-1.1.17 {
1.152 + cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE TEXT}
1.153 +} {{} B a sort}
1.154 +do_test collate4-1.1.18 {
1.155 + cksort {SELECT b FROM collate4t3 ORDER BY b}
1.156 +} {{} B a nosort}
1.157 +do_test collate4-1.1.19 {
1.158 + cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE TEXT}
1.159 +} {{} B a nosort}
1.160 +do_test collate4-1.1.20 {
1.161 + cksort {SELECT b FROM collate4t3 ORDER BY b COLLATE NOCASE}
1.162 +} {{} a B sort}
1.163 +
1.164 +do_test collate4-1.1.21 {
1.165 + execsql {
1.166 + CREATE TABLE collate4t4(a COLLATE NOCASE, b COLLATE TEXT);
1.167 + INSERT INTO collate4t4 VALUES( 'a', 'a' );
1.168 + INSERT INTO collate4t4 VALUES( 'b', 'b' );
1.169 + INSERT INTO collate4t4 VALUES( NULL, NULL );
1.170 + INSERT INTO collate4t4 VALUES( 'B', 'B' );
1.171 + INSERT INTO collate4t4 VALUES( 'A', 'A' );
1.172 + CREATE INDEX collate4i3 ON collate4t4(a COLLATE TEXT);
1.173 + CREATE INDEX collate4i4 ON collate4t4(b COLLATE NOCASE);
1.174 + }
1.175 +} {}
1.176 +do_test collate4-1.1.22 {
1.177 + cksort {SELECT a FROM collate4t4 ORDER BY a}
1.178 +} {{} a A b B sort}
1.179 +do_test collate4-1.1.23 {
1.180 + cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE}
1.181 +} {{} a A b B sort}
1.182 +do_test collate4-1.1.24 {
1.183 + cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT}
1.184 +} {{} A B a b nosort}
1.185 +do_test collate4-1.1.25 {
1.186 + cksort {SELECT b FROM collate4t4 ORDER BY b}
1.187 +} {{} A B a b sort}
1.188 +do_test collate4-1.1.26 {
1.189 + cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE TEXT}
1.190 +} {{} A B a b sort}
1.191 +do_test collate4-1.1.27 {
1.192 + cksort {SELECT b FROM collate4t4 ORDER BY b COLLATE NOCASE}
1.193 +} {{} a A b B nosort}
1.194 +
1.195 +do_test collate4-1.1.30 {
1.196 + execsql {
1.197 + DROP TABLE collate4t1;
1.198 + DROP TABLE collate4t2;
1.199 + DROP TABLE collate4t3;
1.200 + DROP TABLE collate4t4;
1.201 + }
1.202 +} {}
1.203 +
1.204 +do_test collate4-1.2.0 {
1.205 + execsql {
1.206 + CREATE TABLE collate4t1(a COLLATE NOCASE, b COLLATE TEXT);
1.207 + INSERT INTO collate4t1 VALUES( 'a', 'a' );
1.208 + INSERT INTO collate4t1 VALUES( 'b', 'b' );
1.209 + INSERT INTO collate4t1 VALUES( NULL, NULL );
1.210 + INSERT INTO collate4t1 VALUES( 'B', 'B' );
1.211 + INSERT INTO collate4t1 VALUES( 'A', 'A' );
1.212 + CREATE INDEX collate4i1 ON collate4t1(a, b);
1.213 + }
1.214 +} {}
1.215 +do_test collate4-1.2.1 {
1.216 + cksort {SELECT a FROM collate4t1 ORDER BY a}
1.217 +} {{} A a B b nosort}
1.218 +do_test collate4-1.2.2 {
1.219 + cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE nocase}
1.220 +} {{} A a B b nosort}
1.221 +do_test collate4-1.2.3 {
1.222 + cksort {SELECT a FROM collate4t1 ORDER BY a COLLATE text}
1.223 +} {{} A B a b sort}
1.224 +do_test collate4-1.2.4 {
1.225 + cksort {SELECT a FROM collate4t1 ORDER BY a, b}
1.226 +} {{} A a B b nosort}
1.227 +do_test collate4-1.2.5 {
1.228 + cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase}
1.229 +} {{} a A b B sort}
1.230 +do_test collate4-1.2.6 {
1.231 + cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text}
1.232 +} {{} A a B b nosort}
1.233 +
1.234 +do_test collate4-1.2.7 {
1.235 + execsql {
1.236 + CREATE TABLE collate4t2(
1.237 + a COLLATE NOCASE,
1.238 + b COLLATE TEXT,
1.239 + PRIMARY KEY(a, b)
1.240 + );
1.241 + INSERT INTO collate4t2 VALUES( 'a', 'a' );
1.242 + INSERT INTO collate4t2 VALUES( NULL, NULL );
1.243 + INSERT INTO collate4t2 VALUES( 'B', 'B' );
1.244 + }
1.245 +} {}
1.246 +do_test collate4-1.2.8 {
1.247 + cksort {SELECT a FROM collate4t2 ORDER BY a}
1.248 +} {{} a B nosort}
1.249 +do_test collate4-1.2.9 {
1.250 + cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE nocase}
1.251 +} {{} a B nosort}
1.252 +do_test collate4-1.2.10 {
1.253 + cksort {SELECT a FROM collate4t2 ORDER BY a COLLATE text}
1.254 +} {{} B a sort}
1.255 +do_test collate4-1.2.11 {
1.256 + cksort {SELECT a FROM collate4t2 ORDER BY a, b}
1.257 +} {{} a B nosort}
1.258 +do_test collate4-1.2.12 {
1.259 + cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE nocase}
1.260 +} {{} a B sort}
1.261 +do_test collate4-1.2.13 {
1.262 + cksort {SELECT a FROM collate4t2 ORDER BY a, b COLLATE text}
1.263 +} {{} a B nosort}
1.264 +
1.265 +do_test collate4-1.2.14 {
1.266 + execsql {
1.267 + CREATE TABLE collate4t3(a COLLATE NOCASE, b COLLATE TEXT);
1.268 + INSERT INTO collate4t3 VALUES( 'a', 'a' );
1.269 + INSERT INTO collate4t3 VALUES( 'b', 'b' );
1.270 + INSERT INTO collate4t3 VALUES( NULL, NULL );
1.271 + INSERT INTO collate4t3 VALUES( 'B', 'B' );
1.272 + INSERT INTO collate4t3 VALUES( 'A', 'A' );
1.273 + CREATE INDEX collate4i2 ON collate4t3(a COLLATE TEXT, b COLLATE NOCASE);
1.274 + }
1.275 +} {}
1.276 +do_test collate4-1.2.15 {
1.277 + cksort {SELECT a FROM collate4t3 ORDER BY a}
1.278 +} {{} a A b B sort}
1.279 +do_test collate4-1.2.16 {
1.280 + cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase}
1.281 +} {{} a A b B sort}
1.282 +do_test collate4-1.2.17 {
1.283 + cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text}
1.284 +} {{} A B a b nosort}
1.285 +do_test collate4-1.2.18 {
1.286 + cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b}
1.287 +} {{} A B a b sort}
1.288 +do_test collate4-1.2.19 {
1.289 + cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b COLLATE nocase}
1.290 +} {{} A B a b nosort}
1.291 +do_test collate4-1.2.20 {
1.292 + cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text, b COLLATE text}
1.293 +} {{} A B a b sort}
1.294 +do_test collate4-1.2.21 {
1.295 + cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC}
1.296 +} {b a B A {} nosort}
1.297 +do_test collate4-1.2.22 {
1.298 + cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text DESC, b}
1.299 +} {b a B A {} sort}
1.300 +do_test collate4-1.2.23 {
1.301 + cksort {SELECT a FROM collate4t3
1.302 + ORDER BY a COLLATE text DESC, b COLLATE nocase}
1.303 +} {b a B A {} sort}
1.304 +do_test collate4-1.2.24 {
1.305 + cksort {SELECT a FROM collate4t3
1.306 + ORDER BY a COLLATE text DESC, b COLLATE nocase DESC}
1.307 +} {b a B A {} nosort}
1.308 +
1.309 +do_test collate4-1.2.25 {
1.310 + execsql {
1.311 + DROP TABLE collate4t1;
1.312 + DROP TABLE collate4t2;
1.313 + DROP TABLE collate4t3;
1.314 + }
1.315 +} {}
1.316 +
1.317 +#
1.318 +# These tests - collate4-2.* - check that indices are correctly
1.319 +# selected or not selected to implement WHERE clauses when user
1.320 +# defined collation sequences are involved.
1.321 +#
1.322 +# Indices may optimise WHERE clauses using <, >, <=, >=, = or IN
1.323 +# operators.
1.324 +#
1.325 +do_test collate4-2.1.0 {
1.326 + execsql {
1.327 + CREATE TABLE collate4t1(a COLLATE NOCASE);
1.328 + CREATE TABLE collate4t2(b COLLATE TEXT);
1.329 +
1.330 + INSERT INTO collate4t1 VALUES('a');
1.331 + INSERT INTO collate4t1 VALUES('A');
1.332 + INSERT INTO collate4t1 VALUES('b');
1.333 + INSERT INTO collate4t1 VALUES('B');
1.334 + INSERT INTO collate4t1 VALUES('c');
1.335 + INSERT INTO collate4t1 VALUES('C');
1.336 + INSERT INTO collate4t1 VALUES('d');
1.337 + INSERT INTO collate4t1 VALUES('D');
1.338 + INSERT INTO collate4t1 VALUES('e');
1.339 + INSERT INTO collate4t1 VALUES('D');
1.340 +
1.341 + INSERT INTO collate4t2 VALUES('A');
1.342 + INSERT INTO collate4t2 VALUES('Z');
1.343 + }
1.344 +} {}
1.345 +do_test collate4-2.1.1 {
1.346 + count {
1.347 + SELECT * FROM collate4t2, collate4t1 WHERE a = b;
1.348 + }
1.349 +} {A a A A 19}
1.350 +do_test collate4-2.1.2 {
1.351 + execsql {
1.352 + CREATE INDEX collate4i1 ON collate4t1(a);
1.353 + }
1.354 + count {
1.355 + SELECT * FROM collate4t2, collate4t1 WHERE a = b;
1.356 + }
1.357 +} {A a A A 5}
1.358 +do_test collate4-2.1.3 {
1.359 + count {
1.360 + SELECT * FROM collate4t2, collate4t1 WHERE b = a;
1.361 + }
1.362 +} {A A 19}
1.363 +do_test collate4-2.1.4 {
1.364 + execsql {
1.365 + DROP INDEX collate4i1;
1.366 + CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
1.367 + }
1.368 + count {
1.369 + SELECT * FROM collate4t2, collate4t1 WHERE a = b;
1.370 + }
1.371 +} {A a A A 19}
1.372 +do_test collate4-2.1.5 {
1.373 + count {
1.374 + SELECT * FROM collate4t2, collate4t1 WHERE b = a;
1.375 + }
1.376 +} {A A 4}
1.377 +ifcapable subquery {
1.378 + do_test collate4-2.1.6 {
1.379 + count {
1.380 + SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
1.381 + }
1.382 + } {a A 10}
1.383 + do_test collate4-2.1.7 {
1.384 + execsql {
1.385 + DROP INDEX collate4i1;
1.386 + CREATE INDEX collate4i1 ON collate4t1(a);
1.387 + }
1.388 + count {
1.389 + SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
1.390 + }
1.391 + } {a A 6}
1.392 + do_test collate4-2.1.8 {
1.393 + count {
1.394 + SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
1.395 + }
1.396 + } {a A 5}
1.397 + do_test collate4-2.1.9 {
1.398 + execsql {
1.399 + DROP INDEX collate4i1;
1.400 + CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
1.401 + }
1.402 + count {
1.403 + SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
1.404 + }
1.405 + } {a A 9}
1.406 +}
1.407 +do_test collate4-2.1.10 {
1.408 + execsql {
1.409 + DROP TABLE collate4t1;
1.410 + DROP TABLE collate4t2;
1.411 + }
1.412 +} {}
1.413 +
1.414 +do_test collate4-2.2.0 {
1.415 + execsql {
1.416 + CREATE TABLE collate4t1(a COLLATE nocase, b COLLATE text, c);
1.417 + CREATE TABLE collate4t2(a COLLATE nocase, b COLLATE text, c COLLATE TEXT);
1.418 +
1.419 + INSERT INTO collate4t1 VALUES('0', '0', '0');
1.420 + INSERT INTO collate4t1 VALUES('0', '0', '1');
1.421 + INSERT INTO collate4t1 VALUES('0', '1', '0');
1.422 + INSERT INTO collate4t1 VALUES('0', '1', '1');
1.423 + INSERT INTO collate4t1 VALUES('1', '0', '0');
1.424 + INSERT INTO collate4t1 VALUES('1', '0', '1');
1.425 + INSERT INTO collate4t1 VALUES('1', '1', '0');
1.426 + INSERT INTO collate4t1 VALUES('1', '1', '1');
1.427 + insert into collate4t2 SELECT * FROM collate4t1;
1.428 + }
1.429 +} {}
1.430 +do_test collate4-2.2.1 {
1.431 + count {
1.432 + SELECT * FROM collate4t2 NATURAL JOIN collate4t1;
1.433 + }
1.434 +} {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}
1.435 +do_test collate4-2.2.1b {
1.436 + execsql {
1.437 + CREATE INDEX collate4i1 ON collate4t1(a, b, c);
1.438 + }
1.439 + count {
1.440 + SELECT * FROM collate4t2 NATURAL JOIN collate4t1;
1.441 + }
1.442 +} {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}
1.443 +do_test collate4-2.2.2 {
1.444 + execsql {
1.445 + DROP INDEX collate4i1;
1.446 + CREATE INDEX collate4i1 ON collate4t1(a, b, c COLLATE text);
1.447 + }
1.448 + count {
1.449 + SELECT * FROM collate4t2 NATURAL JOIN collate4t1;
1.450 + }
1.451 +} {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}
1.452 +
1.453 +do_test collate4-2.2.10 {
1.454 + execsql {
1.455 + DROP TABLE collate4t1;
1.456 + DROP TABLE collate4t2;
1.457 + }
1.458 +} {}
1.459 +
1.460 +#
1.461 +# These tests - collate4-3.* verify that indices that implement
1.462 +# UNIQUE and PRIMARY KEY constraints operate correctly with user
1.463 +# defined collation sequences.
1.464 +#
1.465 +do_test collate4-3.0 {
1.466 + execsql {
1.467 + CREATE TABLE collate4t1(a PRIMARY KEY COLLATE NOCASE);
1.468 + }
1.469 +} {}
1.470 +do_test collate4-3.1 {
1.471 + catchsql {
1.472 + INSERT INTO collate4t1 VALUES('abc');
1.473 + INSERT INTO collate4t1 VALUES('ABC');
1.474 + }
1.475 +} {1 {column a is not unique}}
1.476 +do_test collate4-3.2 {
1.477 + execsql {
1.478 + SELECT * FROM collate4t1;
1.479 + }
1.480 +} {abc}
1.481 +do_test collate4-3.3 {
1.482 + catchsql {
1.483 + INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
1.484 + }
1.485 +} {1 {column a is not unique}}
1.486 +do_test collate4-3.4 {
1.487 + catchsql {
1.488 + INSERT INTO collate4t1 VALUES(1);
1.489 + UPDATE collate4t1 SET a = 'abc';
1.490 + }
1.491 +} {1 {column a is not unique}}
1.492 +do_test collate4-3.5 {
1.493 + execsql {
1.494 + DROP TABLE collate4t1;
1.495 + CREATE TABLE collate4t1(a COLLATE NOCASE UNIQUE);
1.496 + }
1.497 +} {}
1.498 +do_test collate4-3.6 {
1.499 + catchsql {
1.500 + INSERT INTO collate4t1 VALUES('abc');
1.501 + INSERT INTO collate4t1 VALUES('ABC');
1.502 + }
1.503 +} {1 {column a is not unique}}
1.504 +do_test collate4-3.7 {
1.505 + execsql {
1.506 + SELECT * FROM collate4t1;
1.507 + }
1.508 +} {abc}
1.509 +do_test collate4-3.8 {
1.510 + catchsql {
1.511 + INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
1.512 + }
1.513 +} {1 {column a is not unique}}
1.514 +do_test collate4-3.9 {
1.515 + catchsql {
1.516 + INSERT INTO collate4t1 VALUES(1);
1.517 + UPDATE collate4t1 SET a = 'abc';
1.518 + }
1.519 +} {1 {column a is not unique}}
1.520 +do_test collate4-3.10 {
1.521 + execsql {
1.522 + DROP TABLE collate4t1;
1.523 + CREATE TABLE collate4t1(a);
1.524 + CREATE UNIQUE INDEX collate4i1 ON collate4t1(a COLLATE NOCASE);
1.525 + }
1.526 +} {}
1.527 +do_test collate4-3.11 {
1.528 + catchsql {
1.529 + INSERT INTO collate4t1 VALUES('abc');
1.530 + INSERT INTO collate4t1 VALUES('ABC');
1.531 + }
1.532 +} {1 {column a is not unique}}
1.533 +do_test collate4-3.12 {
1.534 + execsql {
1.535 + SELECT * FROM collate4t1;
1.536 + }
1.537 +} {abc}
1.538 +do_test collate4-3.13 {
1.539 + catchsql {
1.540 + INSERT INTO collate4t1 SELECT upper(a) FROM collate4t1;
1.541 + }
1.542 +} {1 {column a is not unique}}
1.543 +do_test collate4-3.14 {
1.544 + catchsql {
1.545 + INSERT INTO collate4t1 VALUES(1);
1.546 + UPDATE collate4t1 SET a = 'abc';
1.547 + }
1.548 +} {1 {column a is not unique}}
1.549 +
1.550 +do_test collate4-3.15 {
1.551 + execsql {
1.552 + DROP TABLE collate4t1;
1.553 + }
1.554 +} {}
1.555 +
1.556 +# Mimic the SQLite 2 collation type NUMERIC.
1.557 +db collate numeric numeric_collate
1.558 +proc numeric_collate {lhs rhs} {
1.559 + if {$lhs == $rhs} {return 0}
1.560 + return [expr ($lhs>$rhs)?1:-1]
1.561 +}
1.562 +
1.563 +#
1.564 +# These tests - collate4-4.* check that min() and max() only ever
1.565 +# use indices constructed with built-in collation type numeric.
1.566 +#
1.567 +# CHANGED: min() and max() now use the collation type. If there
1.568 +# is an indice that can be used, it is used.
1.569 +#
1.570 +do_test collate4-4.0 {
1.571 + execsql {
1.572 + CREATE TABLE collate4t1(a COLLATE TEXT);
1.573 + INSERT INTO collate4t1 VALUES('2');
1.574 + INSERT INTO collate4t1 VALUES('10');
1.575 + INSERT INTO collate4t1 VALUES('20');
1.576 + INSERT INTO collate4t1 VALUES('104');
1.577 + }
1.578 +} {}
1.579 +do_test collate4-4.1 {
1.580 + count {
1.581 + SELECT max(a) FROM collate4t1
1.582 + }
1.583 +} {20 3}
1.584 +do_test collate4-4.2 {
1.585 + count {
1.586 + SELECT min(a) FROM collate4t1
1.587 + }
1.588 +} {10 3}
1.589 +do_test collate4-4.3 {
1.590 + # Test that the index with collation type TEXT is used.
1.591 + execsql {
1.592 + CREATE INDEX collate4i1 ON collate4t1(a);
1.593 + }
1.594 + count {
1.595 + SELECT min(a) FROM collate4t1;
1.596 + }
1.597 +} {10 1}
1.598 +do_test collate4-4.4 {
1.599 + count {
1.600 + SELECT max(a) FROM collate4t1;
1.601 + }
1.602 +} {20 0}
1.603 +do_test collate4-4.5 {
1.604 + # Test that the index with collation type NUMERIC is not used.
1.605 + execsql {
1.606 + DROP INDEX collate4i1;
1.607 + CREATE INDEX collate4i1 ON collate4t1(a COLLATE NUMERIC);
1.608 + }
1.609 + count {
1.610 + SELECT min(a) FROM collate4t1;
1.611 + }
1.612 +} {10 3}
1.613 +do_test collate4-4.6 {
1.614 + count {
1.615 + SELECT max(a) FROM collate4t1;
1.616 + }
1.617 +} {20 3}
1.618 +do_test collate4-4.7 {
1.619 + execsql {
1.620 + DROP TABLE collate4t1;
1.621 + }
1.622 +} {}
1.623 +
1.624 +# Also test the scalar min() and max() functions.
1.625 +#
1.626 +do_test collate4-4.8 {
1.627 + execsql {
1.628 + CREATE TABLE collate4t1(a COLLATE TEXT, b COLLATE NUMERIC);
1.629 + INSERT INTO collate4t1 VALUES('11', '101');
1.630 + INSERT INTO collate4t1 VALUES('101', '11')
1.631 + }
1.632 +} {}
1.633 +do_test collate4-4.9 {
1.634 + execsql {
1.635 + SELECT max(a, b) FROM collate4t1;
1.636 + }
1.637 +} {11 11}
1.638 +do_test collate4-4.10 {
1.639 + execsql {
1.640 + SELECT max(b, a) FROM collate4t1;
1.641 + }
1.642 +} {101 101}
1.643 +do_test collate4-4.11 {
1.644 + execsql {
1.645 + SELECT max(a, '101') FROM collate4t1;
1.646 + }
1.647 +} {11 101}
1.648 +do_test collate4-4.12 {
1.649 + execsql {
1.650 + SELECT max('101', a) FROM collate4t1;
1.651 + }
1.652 +} {11 101}
1.653 +do_test collate4-4.13 {
1.654 + execsql {
1.655 + SELECT max(b, '101') FROM collate4t1;
1.656 + }
1.657 +} {101 101}
1.658 +do_test collate4-4.14 {
1.659 + execsql {
1.660 + SELECT max('101', b) FROM collate4t1;
1.661 + }
1.662 +} {101 101}
1.663 +
1.664 +do_test collate4-4.15 {
1.665 + execsql {
1.666 + DROP TABLE collate4t1;
1.667 + }
1.668 +} {}
1.669 +
1.670 +#
1.671 +# These tests - collate4.6.* - ensure that implict INTEGER PRIMARY KEY
1.672 +# indices do not confuse collation sequences.
1.673 +#
1.674 +# These indices are never used for sorting in SQLite. And you can't
1.675 +# create another index on an INTEGER PRIMARY KEY column, so we don't have
1.676 +# to test that.
1.677 +# (Revised 2004-Nov-22): The ROWID can be used for sorting now.
1.678 +#
1.679 +do_test collate4-6.0 {
1.680 + execsql {
1.681 + CREATE TABLE collate4t1(a INTEGER PRIMARY KEY);
1.682 + INSERT INTO collate4t1 VALUES(101);
1.683 + INSERT INTO collate4t1 VALUES(10);
1.684 + INSERT INTO collate4t1 VALUES(15);
1.685 + }
1.686 +} {}
1.687 +do_test collate4-6.1 {
1.688 + cksort {
1.689 + SELECT * FROM collate4t1 ORDER BY 1;
1.690 + }
1.691 +} {10 15 101 nosort}
1.692 +do_test collate4-6.2 {
1.693 + cksort {
1.694 + SELECT * FROM collate4t1 ORDER BY oid;
1.695 + }
1.696 +} {10 15 101 nosort}
1.697 +do_test collate4-6.3 {
1.698 + cksort {
1.699 + SELECT * FROM collate4t1 ORDER BY oid||'' COLLATE TEXT;
1.700 + }
1.701 +} {10 101 15 sort}
1.702 +
1.703 +finish_test