1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/collate2.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,694 @@
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: collate2.test,v 1.6 2008/08/20 16:35:10 drh Exp $
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +#
1.24 +# Tests are organised as follows:
1.25 +#
1.26 +# collate2-1.* WHERE <expr> expressions (sqliteExprIfTrue).
1.27 +# collate2-2.* WHERE NOT <expr> expressions (sqliteExprIfFalse).
1.28 +# collate2-3.* SELECT <expr> expressions (sqliteExprCode).
1.29 +# collate2-4.* Precedence of collation/data types in binary comparisons
1.30 +# collate2-5.* JOIN syntax.
1.31 +#
1.32 +
1.33 +# Create a collation type BACKWARDS for use in testing. This collation type
1.34 +# is similar to the built-in TEXT collation type except the order of
1.35 +# characters in each string is reversed before the comparison is performed.
1.36 +db collate BACKWARDS backwards_collate
1.37 +proc backwards_collate {a b} {
1.38 + set ra {};
1.39 + set rb {}
1.40 + foreach c [split $a {}] { set ra $c$ra }
1.41 + foreach c [split $b {}] { set rb $c$rb }
1.42 + return [string compare $ra $rb]
1.43 +}
1.44 +
1.45 +# The following values are used in these tests:
1.46 +# NULL aa ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB
1.47 +#
1.48 +# The collation orders for each of the tested collation types are:
1.49 +#
1.50 +# BINARY: NULL AA AB Aa Ab BA BB Ba Bb aA aB aa ab bA bB ba bb
1.51 +# NOCASE: NULL aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB
1.52 +# BACKWARDS: NULL AA BA aA bA AB BB aB bB Aa Ba aa ba Ab Bb ab bb
1.53 +#
1.54 +# These tests verify that the default collation type for a column is used
1.55 +# for comparison operators (<, >, <=, >=, =) involving that column and
1.56 +# an expression that is not a column with a default collation type.
1.57 +#
1.58 +# The collation sequences BINARY and NOCASE are built-in, the BACKWARDS
1.59 +# collation sequence is implemented by the TCL proc backwards_collate
1.60 +# above.
1.61 +#
1.62 +do_test collate2-1.0 {
1.63 + execsql {
1.64 + CREATE TABLE collate2t1(
1.65 + a COLLATE BINARY,
1.66 + b COLLATE NOCASE,
1.67 + c COLLATE BACKWARDS
1.68 + );
1.69 + INSERT INTO collate2t1 VALUES( NULL, NULL, NULL );
1.70 +
1.71 + INSERT INTO collate2t1 VALUES( 'aa', 'aa', 'aa' );
1.72 + INSERT INTO collate2t1 VALUES( 'ab', 'ab', 'ab' );
1.73 + INSERT INTO collate2t1 VALUES( 'ba', 'ba', 'ba' );
1.74 + INSERT INTO collate2t1 VALUES( 'bb', 'bb', 'bb' );
1.75 +
1.76 + INSERT INTO collate2t1 VALUES( 'aA', 'aA', 'aA' );
1.77 + INSERT INTO collate2t1 VALUES( 'aB', 'aB', 'aB' );
1.78 + INSERT INTO collate2t1 VALUES( 'bA', 'bA', 'bA' );
1.79 + INSERT INTO collate2t1 VALUES( 'bB', 'bB', 'bB' );
1.80 +
1.81 + INSERT INTO collate2t1 VALUES( 'Aa', 'Aa', 'Aa' );
1.82 + INSERT INTO collate2t1 VALUES( 'Ab', 'Ab', 'Ab' );
1.83 + INSERT INTO collate2t1 VALUES( 'Ba', 'Ba', 'Ba' );
1.84 + INSERT INTO collate2t1 VALUES( 'Bb', 'Bb', 'Bb' );
1.85 +
1.86 + INSERT INTO collate2t1 VALUES( 'AA', 'AA', 'AA' );
1.87 + INSERT INTO collate2t1 VALUES( 'AB', 'AB', 'AB' );
1.88 + INSERT INTO collate2t1 VALUES( 'BA', 'BA', 'BA' );
1.89 + INSERT INTO collate2t1 VALUES( 'BB', 'BB', 'BB' );
1.90 + }
1.91 + if {[info exists collate_test_use_index]} {
1.92 + execsql {
1.93 + CREATE INDEX collate2t1_i1 ON collate2t1(a);
1.94 + CREATE INDEX collate2t1_i2 ON collate2t1(b);
1.95 + CREATE INDEX collate2t1_i3 ON collate2t1(c);
1.96 + }
1.97 + }
1.98 +} {}
1.99 +do_test collate2-1.1 {
1.100 + execsql {
1.101 + SELECT a FROM collate2t1 WHERE a > 'aa' ORDER BY 1;
1.102 + }
1.103 +} {ab bA bB ba bb}
1.104 +do_test collate2-1.1.1 {
1.105 + execsql {
1.106 + SELECT a FROM collate2t1 WHERE a COLLATE binary > 'aa' ORDER BY 1;
1.107 + }
1.108 +} {ab bA bB ba bb}
1.109 +do_test collate2-1.1.2 {
1.110 + execsql {
1.111 + SELECT a FROM collate2t1 WHERE b COLLATE binary > 'aa' ORDER BY 1;
1.112 + }
1.113 +} {ab bA bB ba bb}
1.114 +do_test collate2-1.1.3 {
1.115 + execsql {
1.116 + SELECT a FROM collate2t1 WHERE c COLLATE binary > 'aa' ORDER BY 1;
1.117 + }
1.118 +} {ab bA bB ba bb}
1.119 +do_test collate2-1.2 {
1.120 + execsql {
1.121 + SELECT b FROM collate2t1 WHERE b > 'aa' ORDER BY 1, oid;
1.122 + }
1.123 +} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
1.124 +do_test collate2-1.2.1 {
1.125 + execsql {
1.126 + SELECT b FROM collate2t1 WHERE a COLLATE nocase > 'aa'
1.127 + ORDER BY 1, oid;
1.128 + }
1.129 +} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
1.130 +do_test collate2-1.2.2 {
1.131 + execsql {
1.132 + SELECT b FROM collate2t1 WHERE b COLLATE nocase > 'aa'
1.133 + ORDER BY 1, oid;
1.134 + }
1.135 +} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
1.136 +do_test collate2-1.2.3 {
1.137 + execsql {
1.138 + SELECT b FROM collate2t1 WHERE c COLLATE nocase > 'aa'
1.139 + ORDER BY 1, oid;
1.140 + }
1.141 +} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
1.142 +do_test collate2-1.2.4 {
1.143 + execsql {
1.144 + SELECT b FROM collate2t1 WHERE b > 'aa' ORDER BY +b;
1.145 + }
1.146 +} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
1.147 +do_test collate2-1.2.5 {
1.148 + execsql {
1.149 + SELECT b FROM collate2t1 WHERE a COLLATE nocase > 'aa' ORDER BY +b;
1.150 + }
1.151 +} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
1.152 +do_test collate2-1.2.6 {
1.153 + execsql {
1.154 + SELECT b FROM collate2t1 WHERE b COLLATE nocase > 'aa' ORDER BY +b;
1.155 + }
1.156 +} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
1.157 +do_test collate2-1.2.7 {
1.158 + execsql {
1.159 + SELECT b FROM collate2t1 WHERE c COLLATE nocase > 'aa' ORDER BY +b;
1.160 + }
1.161 +} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
1.162 +do_test collate2-1.3 {
1.163 + execsql {
1.164 + SELECT c FROM collate2t1 WHERE c > 'aa' ORDER BY 1;
1.165 + }
1.166 +} {ba Ab Bb ab bb}
1.167 +do_test collate2-1.3.1 {
1.168 + execsql {
1.169 + SELECT c FROM collate2t1 WHERE a COLLATE backwards > 'aa'
1.170 + ORDER BY 1;
1.171 + }
1.172 +} {ba Ab Bb ab bb}
1.173 +do_test collate2-1.3.2 {
1.174 + execsql {
1.175 + SELECT c FROM collate2t1 WHERE b COLLATE backwards > 'aa'
1.176 + ORDER BY 1;
1.177 + }
1.178 +} {ba Ab Bb ab bb}
1.179 +do_test collate2-1.3.3 {
1.180 + execsql {
1.181 + SELECT c FROM collate2t1 WHERE c COLLATE backwards > 'aa'
1.182 + ORDER BY 1;
1.183 + }
1.184 +} {ba Ab Bb ab bb}
1.185 +do_test collate2-1.4 {
1.186 + execsql {
1.187 + SELECT a FROM collate2t1 WHERE a < 'aa' ORDER BY 1;
1.188 + }
1.189 +} {AA AB Aa Ab BA BB Ba Bb aA aB}
1.190 +do_test collate2-1.5 {
1.191 + execsql {
1.192 + SELECT b FROM collate2t1 WHERE b < 'aa' ORDER BY 1, oid;
1.193 + }
1.194 +} {}
1.195 +do_test collate2-1.5.1 {
1.196 + execsql {
1.197 + SELECT b FROM collate2t1 WHERE b < 'aa' ORDER BY +b;
1.198 + }
1.199 +} {}
1.200 +do_test collate2-1.6 {
1.201 + execsql {
1.202 + SELECT c FROM collate2t1 WHERE c < 'aa' ORDER BY 1;
1.203 + }
1.204 +} {AA BA aA bA AB BB aB bB Aa Ba}
1.205 +do_test collate2-1.7 {
1.206 + execsql {
1.207 + SELECT a FROM collate2t1 WHERE a = 'aa';
1.208 + }
1.209 +} {aa}
1.210 +do_test collate2-1.8 {
1.211 + execsql {
1.212 + SELECT b FROM collate2t1 WHERE b = 'aa' ORDER BY oid;
1.213 + }
1.214 +} {aa aA Aa AA}
1.215 +do_test collate2-1.9 {
1.216 + execsql {
1.217 + SELECT c FROM collate2t1 WHERE c = 'aa';
1.218 + }
1.219 +} {aa}
1.220 +do_test collate2-1.10 {
1.221 + execsql {
1.222 + SELECT a FROM collate2t1 WHERE a >= 'aa' ORDER BY 1;
1.223 + }
1.224 +} {aa ab bA bB ba bb}
1.225 +do_test collate2-1.11 {
1.226 + execsql {
1.227 + SELECT b FROM collate2t1 WHERE b >= 'aa' ORDER BY 1, oid;
1.228 + }
1.229 +} {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}
1.230 +do_test collate2-1.12 {
1.231 + execsql {
1.232 + SELECT c FROM collate2t1 WHERE c >= 'aa' ORDER BY 1;
1.233 + }
1.234 +} {aa ba Ab Bb ab bb}
1.235 +do_test collate2-1.13 {
1.236 + execsql {
1.237 + SELECT a FROM collate2t1 WHERE a <= 'aa' ORDER BY 1;
1.238 + }
1.239 +} {AA AB Aa Ab BA BB Ba Bb aA aB aa}
1.240 +do_test collate2-1.14 {
1.241 + execsql {
1.242 + SELECT b FROM collate2t1 WHERE b <= 'aa' ORDER BY 1, oid;
1.243 + }
1.244 +} {aa aA Aa AA}
1.245 +do_test collate2-1.15 {
1.246 + execsql {
1.247 + SELECT c FROM collate2t1 WHERE c <= 'aa' ORDER BY 1;
1.248 + }
1.249 +} {AA BA aA bA AB BB aB bB Aa Ba aa}
1.250 +do_test collate2-1.16 {
1.251 + execsql {
1.252 + SELECT a FROM collate2t1 WHERE a BETWEEN 'Aa' AND 'Bb' ORDER BY 1;
1.253 + }
1.254 +} {Aa Ab BA BB Ba Bb}
1.255 +do_test collate2-1.17 {
1.256 + execsql {
1.257 + SELECT b FROM collate2t1 WHERE b BETWEEN 'Aa' AND 'Bb' ORDER BY 1, oid;
1.258 + }
1.259 +} {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}
1.260 +do_test collate2-1.17.1 {
1.261 + execsql {
1.262 + SELECT b FROM collate2t1 WHERE b BETWEEN 'Aa' AND 'Bb' ORDER BY +b;
1.263 + }
1.264 +} {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}
1.265 +do_test collate2-1.18 {
1.266 + execsql {
1.267 + SELECT c FROM collate2t1 WHERE c BETWEEN 'Aa' AND 'Bb' ORDER BY 1;
1.268 + }
1.269 +} {Aa Ba aa ba Ab Bb}
1.270 +do_test collate2-1.19 {
1.271 + execsql {
1.272 + SELECT a FROM collate2t1 WHERE
1.273 + CASE a WHEN 'aa' THEN 1 ELSE 0 END
1.274 + ORDER BY 1, oid;
1.275 + }
1.276 +} {aa}
1.277 +do_test collate2-1.20 {
1.278 + execsql {
1.279 + SELECT b FROM collate2t1 WHERE
1.280 + CASE b WHEN 'aa' THEN 1 ELSE 0 END
1.281 + ORDER BY 1, oid;
1.282 + }
1.283 +} {aa aA Aa AA}
1.284 +do_test collate2-1.21 {
1.285 + execsql {
1.286 + SELECT c FROM collate2t1 WHERE
1.287 + CASE c WHEN 'aa' THEN 1 ELSE 0 END
1.288 + ORDER BY 1, oid;
1.289 + }
1.290 +} {aa}
1.291 +
1.292 +ifcapable subquery {
1.293 + do_test collate2-1.22 {
1.294 + execsql {
1.295 + SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb') ORDER BY 1, oid;
1.296 + }
1.297 + } {aa bb}
1.298 + do_test collate2-1.23 {
1.299 + execsql {
1.300 + SELECT b FROM collate2t1 WHERE b IN ('aa', 'bb') ORDER BY 1, oid;
1.301 + }
1.302 + } {aa aA Aa AA bb bB Bb BB}
1.303 + do_test collate2-1.24 {
1.304 + execsql {
1.305 + SELECT c FROM collate2t1 WHERE c IN ('aa', 'bb') ORDER BY 1, oid;
1.306 + }
1.307 + } {aa bb}
1.308 + do_test collate2-1.25 {
1.309 + execsql {
1.310 + SELECT a FROM collate2t1
1.311 + WHERE a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
1.312 + }
1.313 + } {aa bb}
1.314 + do_test collate2-1.26 {
1.315 + execsql {
1.316 + SELECT b FROM collate2t1
1.317 + WHERE b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
1.318 + }
1.319 + } {aa bb aA bB Aa Bb AA BB}
1.320 + do_test collate2-1.27 {
1.321 + execsql {
1.322 + SELECT c FROM collate2t1
1.323 + WHERE c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
1.324 + }
1.325 + } {aa bb}
1.326 +} ;# ifcapable subquery
1.327 +
1.328 +do_test collate2-2.1 {
1.329 + execsql {
1.330 + SELECT a FROM collate2t1 WHERE NOT a > 'aa' ORDER BY 1;
1.331 + }
1.332 +} {AA AB Aa Ab BA BB Ba Bb aA aB aa}
1.333 +do_test collate2-2.2 {
1.334 + execsql {
1.335 + SELECT b FROM collate2t1 WHERE NOT b > 'aa' ORDER BY 1, oid;
1.336 + }
1.337 +} {aa aA Aa AA}
1.338 +do_test collate2-2.3 {
1.339 + execsql {
1.340 + SELECT c FROM collate2t1 WHERE NOT c > 'aa' ORDER BY 1;
1.341 + }
1.342 +} {AA BA aA bA AB BB aB bB Aa Ba aa}
1.343 +do_test collate2-2.4 {
1.344 + execsql {
1.345 + SELECT a FROM collate2t1 WHERE NOT a < 'aa' ORDER BY 1;
1.346 + }
1.347 +} {aa ab bA bB ba bb}
1.348 +do_test collate2-2.5 {
1.349 + execsql {
1.350 + SELECT b FROM collate2t1 WHERE NOT b < 'aa' ORDER BY 1, oid;
1.351 + }
1.352 +} {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB}
1.353 +do_test collate2-2.6 {
1.354 + execsql {
1.355 + SELECT c FROM collate2t1 WHERE NOT c < 'aa' ORDER BY 1;
1.356 + }
1.357 +} {aa ba Ab Bb ab bb}
1.358 +do_test collate2-2.7 {
1.359 + execsql {
1.360 + SELECT a FROM collate2t1 WHERE NOT a = 'aa';
1.361 + }
1.362 +} {ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
1.363 +do_test collate2-2.8 {
1.364 + execsql {
1.365 + SELECT b FROM collate2t1 WHERE NOT b = 'aa';
1.366 + }
1.367 +} {ab ba bb aB bA bB Ab Ba Bb AB BA BB}
1.368 +do_test collate2-2.9 {
1.369 + execsql {
1.370 + SELECT c FROM collate2t1 WHERE NOT c = 'aa';
1.371 + }
1.372 +} {ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
1.373 +do_test collate2-2.10 {
1.374 + execsql {
1.375 + SELECT a FROM collate2t1 WHERE NOT a >= 'aa' ORDER BY 1;
1.376 + }
1.377 +} {AA AB Aa Ab BA BB Ba Bb aA aB}
1.378 +do_test collate2-2.11 {
1.379 + execsql {
1.380 + SELECT b FROM collate2t1 WHERE NOT b >= 'aa' ORDER BY 1, oid;
1.381 + }
1.382 +} {}
1.383 +do_test collate2-2.12 {
1.384 + execsql {
1.385 + SELECT c FROM collate2t1 WHERE NOT c >= 'aa' ORDER BY 1;
1.386 + }
1.387 +} {AA BA aA bA AB BB aB bB Aa Ba}
1.388 +do_test collate2-2.13 {
1.389 + execsql {
1.390 + SELECT a FROM collate2t1 WHERE NOT a <= 'aa' ORDER BY 1;
1.391 + }
1.392 +} {ab bA bB ba bb}
1.393 +do_test collate2-2.14 {
1.394 + execsql {
1.395 + SELECT b FROM collate2t1 WHERE NOT b <= 'aa' ORDER BY 1, oid;
1.396 + }
1.397 +} {ab aB Ab AB ba bA Ba BA bb bB Bb BB}
1.398 +do_test collate2-2.15 {
1.399 + execsql {
1.400 + SELECT c FROM collate2t1 WHERE NOT c <= 'aa' ORDER BY 1;
1.401 + }
1.402 +} {ba Ab Bb ab bb}
1.403 +do_test collate2-2.16 {
1.404 + execsql {
1.405 + SELECT a FROM collate2t1 WHERE a NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1;
1.406 + }
1.407 +} {AA AB aA aB aa ab bA bB ba bb}
1.408 +do_test collate2-2.17 {
1.409 + execsql {
1.410 + SELECT b FROM collate2t1 WHERE b NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1, oid;
1.411 + }
1.412 +} {}
1.413 +do_test collate2-2.18 {
1.414 + execsql {
1.415 + SELECT c FROM collate2t1 WHERE c NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1;
1.416 + }
1.417 +} {AA BA aA bA AB BB aB bB ab bb}
1.418 +do_test collate2-2.19 {
1.419 + execsql {
1.420 + SELECT a FROM collate2t1 WHERE NOT CASE a WHEN 'aa' THEN 1 ELSE 0 END;
1.421 + }
1.422 +} {{} ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
1.423 +do_test collate2-2.20 {
1.424 + execsql {
1.425 + SELECT b FROM collate2t1 WHERE NOT CASE b WHEN 'aa' THEN 1 ELSE 0 END;
1.426 + }
1.427 +} {{} ab ba bb aB bA bB Ab Ba Bb AB BA BB}
1.428 +do_test collate2-2.21 {
1.429 + execsql {
1.430 + SELECT c FROM collate2t1 WHERE NOT CASE c WHEN 'aa' THEN 1 ELSE 0 END;
1.431 + }
1.432 +} {{} ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
1.433 +
1.434 +ifcapable subquery {
1.435 + do_test collate2-2.22 {
1.436 + execsql {
1.437 + SELECT a FROM collate2t1 WHERE NOT a IN ('aa', 'bb');
1.438 + }
1.439 + } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
1.440 + do_test collate2-2.23 {
1.441 + execsql {
1.442 + SELECT b FROM collate2t1 WHERE NOT b IN ('aa', 'bb');
1.443 + }
1.444 + } {ab ba aB bA Ab Ba AB BA}
1.445 + do_test collate2-2.24 {
1.446 + execsql {
1.447 + SELECT c FROM collate2t1 WHERE NOT c IN ('aa', 'bb');
1.448 + }
1.449 + } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
1.450 + do_test collate2-2.25 {
1.451 + execsql {
1.452 + SELECT a FROM collate2t1
1.453 + WHERE NOT a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
1.454 + }
1.455 + } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
1.456 + do_test collate2-2.26 {
1.457 + execsql {
1.458 + SELECT b FROM collate2t1
1.459 + WHERE NOT b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
1.460 + }
1.461 + } {ab ba aB bA Ab Ba AB BA}
1.462 + do_test collate2-2.27 {
1.463 + execsql {
1.464 + SELECT c FROM collate2t1
1.465 + WHERE NOT c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
1.466 + }
1.467 + } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
1.468 +}
1.469 +
1.470 +do_test collate2-3.1 {
1.471 + execsql {
1.472 + SELECT a > 'aa' FROM collate2t1;
1.473 + }
1.474 +} {{} 0 1 1 1 0 0 1 1 0 0 0 0 0 0 0 0}
1.475 +do_test collate2-3.2 {
1.476 + execsql {
1.477 + SELECT b > 'aa' FROM collate2t1;
1.478 + }
1.479 +} {{} 0 1 1 1 0 1 1 1 0 1 1 1 0 1 1 1}
1.480 +do_test collate2-3.3 {
1.481 + execsql {
1.482 + SELECT c > 'aa' FROM collate2t1;
1.483 + }
1.484 +} {{} 0 1 1 1 0 0 0 0 0 1 0 1 0 0 0 0}
1.485 +do_test collate2-3.4 {
1.486 + execsql {
1.487 + SELECT a < 'aa' FROM collate2t1;
1.488 + }
1.489 +} {{} 0 0 0 0 1 1 0 0 1 1 1 1 1 1 1 1}
1.490 +do_test collate2-3.5 {
1.491 + execsql {
1.492 + SELECT b < 'aa' FROM collate2t1;
1.493 + }
1.494 +} {{} 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
1.495 +do_test collate2-3.6 {
1.496 + execsql {
1.497 + SELECT c < 'aa' FROM collate2t1;
1.498 + }
1.499 +} {{} 0 0 0 0 1 1 1 1 1 0 1 0 1 1 1 1}
1.500 +do_test collate2-3.7 {
1.501 + execsql {
1.502 + SELECT a = 'aa' FROM collate2t1;
1.503 + }
1.504 +} {{} 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
1.505 +do_test collate2-3.8 {
1.506 + execsql {
1.507 + SELECT b = 'aa' FROM collate2t1;
1.508 + }
1.509 +} {{} 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0}
1.510 +do_test collate2-3.9 {
1.511 + execsql {
1.512 + SELECT c = 'aa' FROM collate2t1;
1.513 + }
1.514 +} {{} 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
1.515 +do_test collate2-3.10 {
1.516 + execsql {
1.517 + SELECT a <= 'aa' FROM collate2t1;
1.518 + }
1.519 +} {{} 1 0 0 0 1 1 0 0 1 1 1 1 1 1 1 1}
1.520 +do_test collate2-3.11 {
1.521 + execsql {
1.522 + SELECT b <= 'aa' FROM collate2t1;
1.523 + }
1.524 +} {{} 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0}
1.525 +do_test collate2-3.12 {
1.526 + execsql {
1.527 + SELECT c <= 'aa' FROM collate2t1;
1.528 + }
1.529 +} {{} 1 0 0 0 1 1 1 1 1 0 1 0 1 1 1 1}
1.530 +do_test collate2-3.13 {
1.531 + execsql {
1.532 + SELECT a >= 'aa' FROM collate2t1;
1.533 + }
1.534 +} {{} 1 1 1 1 0 0 1 1 0 0 0 0 0 0 0 0}
1.535 +do_test collate2-3.14 {
1.536 + execsql {
1.537 + SELECT b >= 'aa' FROM collate2t1;
1.538 + }
1.539 +} {{} 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1}
1.540 +do_test collate2-3.15 {
1.541 + execsql {
1.542 + SELECT c >= 'aa' FROM collate2t1;
1.543 + }
1.544 +} {{} 1 1 1 1 0 0 0 0 0 1 0 1 0 0 0 0}
1.545 +do_test collate2-3.16 {
1.546 + execsql {
1.547 + SELECT a BETWEEN 'Aa' AND 'Bb' FROM collate2t1;
1.548 + }
1.549 +} {{} 0 0 0 0 0 0 0 0 1 1 1 1 0 0 1 1}
1.550 +do_test collate2-3.17 {
1.551 + execsql {
1.552 + SELECT b BETWEEN 'Aa' AND 'Bb' FROM collate2t1;
1.553 + }
1.554 +} {{} 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1}
1.555 +do_test collate2-3.18 {
1.556 + execsql {
1.557 + SELECT c BETWEEN 'Aa' AND 'Bb' FROM collate2t1;
1.558 + }
1.559 +} {{} 1 0 1 0 0 0 0 0 1 1 1 1 0 0 0 0}
1.560 +do_test collate2-3.19 {
1.561 + execsql {
1.562 + SELECT CASE a WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1;
1.563 + }
1.564 +} {0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
1.565 +do_test collate2-3.20 {
1.566 + execsql {
1.567 + SELECT CASE b WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1;
1.568 + }
1.569 +} {0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0}
1.570 +do_test collate2-3.21 {
1.571 + execsql {
1.572 + SELECT CASE c WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1;
1.573 + }
1.574 +} {0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
1.575 +
1.576 +ifcapable subquery {
1.577 + do_test collate2-3.22 {
1.578 + execsql {
1.579 + SELECT a IN ('aa', 'bb') FROM collate2t1;
1.580 + }
1.581 + } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
1.582 + do_test collate2-3.23 {
1.583 + execsql {
1.584 + SELECT b IN ('aa', 'bb') FROM collate2t1;
1.585 + }
1.586 + } {{} 1 0 0 1 1 0 0 1 1 0 0 1 1 0 0 1}
1.587 + do_test collate2-3.24 {
1.588 + execsql {
1.589 + SELECT c IN ('aa', 'bb') FROM collate2t1;
1.590 + }
1.591 + } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
1.592 + do_test collate2-3.25 {
1.593 + execsql {
1.594 + SELECT a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'))
1.595 + FROM collate2t1;
1.596 + }
1.597 + } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
1.598 + do_test collate2-3.26 {
1.599 + execsql {
1.600 + SELECT b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'))
1.601 + FROM collate2t1;
1.602 + }
1.603 + } {{} 1 0 0 1 1 0 0 1 1 0 0 1 1 0 0 1}
1.604 + do_test collate2-3.27 {
1.605 + execsql {
1.606 + SELECT c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'))
1.607 + FROM collate2t1;
1.608 + }
1.609 + } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
1.610 +}
1.611 +
1.612 +do_test collate2-4.0 {
1.613 + execsql {
1.614 + CREATE TABLE collate2t2(b COLLATE binary);
1.615 + CREATE TABLE collate2t3(b text);
1.616 + INSERT INTO collate2t2 VALUES('aa');
1.617 + INSERT INTO collate2t3 VALUES('aa');
1.618 + }
1.619 +} {}
1.620 +
1.621 +# Test that when both sides of a binary comparison operator have
1.622 +# default collation types, the collate type for the leftmost term
1.623 +# is used.
1.624 +do_test collate2-4.1 {
1.625 + execsql {
1.626 + SELECT collate2t1.a FROM collate2t1, collate2t2
1.627 + WHERE collate2t1.b = collate2t2.b;
1.628 + }
1.629 +} {aa aA Aa AA}
1.630 +do_test collate2-4.2 {
1.631 + execsql {
1.632 + SELECT collate2t1.a FROM collate2t1, collate2t2
1.633 + WHERE collate2t2.b = collate2t1.b;
1.634 + }
1.635 +} {aa}
1.636 +
1.637 +# Test that when one side has a default collation type and the other
1.638 +# does not, the collation type is used.
1.639 +do_test collate2-4.3 {
1.640 + execsql {
1.641 + SELECT collate2t1.a FROM collate2t1, collate2t3
1.642 + WHERE collate2t1.b = collate2t3.b||'';
1.643 + }
1.644 +} {aa aA Aa AA}
1.645 +do_test collate2-4.4 {
1.646 + execsql {
1.647 + SELECT collate2t1.a FROM collate2t1, collate2t3
1.648 + WHERE collate2t3.b||'' = collate2t1.b;
1.649 + }
1.650 +} {aa aA Aa AA}
1.651 +
1.652 +do_test collate2-4.5 {
1.653 + execsql {
1.654 + DROP TABLE collate2t3;
1.655 + }
1.656 +} {}
1.657 +
1.658 +#
1.659 +# Test that the default collation types are used when the JOIN syntax
1.660 +# is used in place of a WHERE clause.
1.661 +#
1.662 +# SQLite transforms the JOIN syntax into a WHERE clause internally, so
1.663 +# the focus of these tests is to ensure that the table on the left-hand-side
1.664 +# of the join determines the collation type used.
1.665 +#
1.666 +do_test collate2-5.0 {
1.667 + execsql {
1.668 + SELECT collate2t1.b FROM collate2t1 JOIN collate2t2 USING (b);
1.669 + }
1.670 +} {aa aA Aa AA}
1.671 +do_test collate2-5.1 {
1.672 + execsql {
1.673 + SELECT collate2t1.b FROM collate2t2 JOIN collate2t1 USING (b);
1.674 + }
1.675 +} {aa}
1.676 +do_test collate2-5.2 {
1.677 + execsql {
1.678 + SELECT collate2t1.b FROM collate2t1 NATURAL JOIN collate2t2;
1.679 + }
1.680 +} {aa aA Aa AA}
1.681 +do_test collate2-5.3 {
1.682 + execsql {
1.683 + SELECT collate2t1.b FROM collate2t2 NATURAL JOIN collate2t1;
1.684 + }
1.685 +} {aa}
1.686 +do_test collate2-5.4 {
1.687 + execsql {
1.688 + SELECT collate2t2.b FROM collate2t1 LEFT OUTER JOIN collate2t2 USING (b) order by collate2t1.oid;
1.689 + }
1.690 +} {{} aa {} {} {} aa {} {} {} aa {} {} {} aa {} {} {}}
1.691 +do_test collate2-5.5 {
1.692 + execsql {
1.693 + SELECT collate2t1.b, collate2t2.b FROM collate2t2 LEFT OUTER JOIN collate2t1 USING (b);
1.694 + }
1.695 +} {aa aa}
1.696 +
1.697 +finish_test