gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/collate2.test (about) 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: collate2.test,v 1.6 2008/08/20 16:35:10 drh Exp $ 16 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 20 set ::testprefix collate2 21 22 # 23 # Tests are organised as follows: 24 # 25 # collate2-1.* WHERE <expr> expressions (sqliteExprIfTrue). 26 # collate2-2.* WHERE NOT <expr> expressions (sqliteExprIfFalse). 27 # collate2-3.* SELECT <expr> expressions (sqliteExprCode). 28 # collate2-4.* Precedence of collation/data types in binary comparisons 29 # collate2-5.* JOIN syntax. 30 # 31 32 # Create a collation type BACKWARDS for use in testing. This collation type 33 # is similar to the built-in TEXT collation type except the order of 34 # characters in each string is reversed before the comparison is performed. 35 db collate BACKWARDS backwards_collate 36 proc backwards_collate {a b} { 37 set ra {}; 38 set rb {} 39 foreach c [split $a {}] { set ra $c$ra } 40 foreach c [split $b {}] { set rb $c$rb } 41 return [string compare $ra $rb] 42 } 43 44 # The following values are used in these tests: 45 # NULL aa ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB 46 # 47 # The collation orders for each of the tested collation types are: 48 # 49 # BINARY: NULL AA AB Aa Ab BA BB Ba Bb aA aB aa ab bA bB ba bb 50 # NOCASE: NULL aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB 51 # BACKWARDS: NULL AA BA aA bA AB BB aB bB Aa Ba aa ba Ab Bb ab bb 52 # 53 # These tests verify that the default collation type for a column is used 54 # for comparison operators (<, >, <=, >=, =) involving that column and 55 # an expression that is not a column with a default collation type. 56 # 57 # The collation sequences BINARY and NOCASE are built-in, the BACKWARDS 58 # collation sequence is implemented by the TCL proc backwards_collate 59 # above. 60 # 61 do_test collate2-1.0 { 62 execsql { 63 CREATE TABLE collate2t1( 64 a COLLATE BINARY, 65 b COLLATE NOCASE, 66 c COLLATE BACKWARDS 67 ); 68 INSERT INTO collate2t1 VALUES( NULL, NULL, NULL ); 69 70 INSERT INTO collate2t1 VALUES( 'aa', 'aa', 'aa' ); 71 INSERT INTO collate2t1 VALUES( 'ab', 'ab', 'ab' ); 72 INSERT INTO collate2t1 VALUES( 'ba', 'ba', 'ba' ); 73 INSERT INTO collate2t1 VALUES( 'bb', 'bb', 'bb' ); 74 75 INSERT INTO collate2t1 VALUES( 'aA', 'aA', 'aA' ); 76 INSERT INTO collate2t1 VALUES( 'aB', 'aB', 'aB' ); 77 INSERT INTO collate2t1 VALUES( 'bA', 'bA', 'bA' ); 78 INSERT INTO collate2t1 VALUES( 'bB', 'bB', 'bB' ); 79 80 INSERT INTO collate2t1 VALUES( 'Aa', 'Aa', 'Aa' ); 81 INSERT INTO collate2t1 VALUES( 'Ab', 'Ab', 'Ab' ); 82 INSERT INTO collate2t1 VALUES( 'Ba', 'Ba', 'Ba' ); 83 INSERT INTO collate2t1 VALUES( 'Bb', 'Bb', 'Bb' ); 84 85 INSERT INTO collate2t1 VALUES( 'AA', 'AA', 'AA' ); 86 INSERT INTO collate2t1 VALUES( 'AB', 'AB', 'AB' ); 87 INSERT INTO collate2t1 VALUES( 'BA', 'BA', 'BA' ); 88 INSERT INTO collate2t1 VALUES( 'BB', 'BB', 'BB' ); 89 } 90 if {[info exists collate_test_use_index]} { 91 execsql { 92 CREATE INDEX collate2t1_i1 ON collate2t1(a); 93 CREATE INDEX collate2t1_i2 ON collate2t1(b); 94 CREATE INDEX collate2t1_i3 ON collate2t1(c); 95 } 96 } 97 } {} 98 do_test collate2-1.1 { 99 execsql { 100 SELECT a FROM collate2t1 WHERE a > 'aa' ORDER BY 1; 101 } 102 } {ab bA bB ba bb} 103 do_test collate2-1.1.1 { 104 execsql { 105 SELECT a FROM collate2t1 WHERE a COLLATE binary > 'aa' ORDER BY 1; 106 } 107 } {ab bA bB ba bb} 108 do_test collate2-1.1.2 { 109 execsql { 110 SELECT a FROM collate2t1 WHERE b COLLATE binary > 'aa' ORDER BY 1; 111 } 112 } {ab bA bB ba bb} 113 do_test collate2-1.1.3 { 114 execsql { 115 SELECT a FROM collate2t1 WHERE c COLLATE binary > 'aa' ORDER BY 1; 116 } 117 } {ab bA bB ba bb} 118 do_test collate2-1.2 { 119 execsql { 120 SELECT b FROM collate2t1 WHERE b > 'aa' ORDER BY 1, oid; 121 } 122 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB} 123 do_test collate2-1.2.1 { 124 execsql { 125 SELECT b FROM collate2t1 WHERE a COLLATE nocase > 'aa' 126 ORDER BY 1, oid; 127 } 128 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB} 129 do_test collate2-1.2.2 { 130 execsql { 131 SELECT b FROM collate2t1 WHERE b COLLATE nocase > 'aa' 132 ORDER BY 1, oid; 133 } 134 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB} 135 do_test collate2-1.2.3 { 136 execsql { 137 SELECT b FROM collate2t1 WHERE c COLLATE nocase > 'aa' 138 ORDER BY 1, oid; 139 } 140 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB} 141 do_test collate2-1.2.4 { 142 execsql { 143 SELECT b FROM collate2t1 WHERE b > 'aa' ORDER BY +b; 144 } 145 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB} 146 do_test collate2-1.2.5 { 147 execsql { 148 SELECT b FROM collate2t1 WHERE a COLLATE nocase > 'aa' ORDER BY +b; 149 } 150 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB} 151 do_test collate2-1.2.6 { 152 execsql { 153 SELECT b FROM collate2t1 WHERE b COLLATE nocase > 'aa' ORDER BY +b; 154 } 155 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB} 156 do_test collate2-1.2.7 { 157 execsql { 158 SELECT b FROM collate2t1 WHERE c COLLATE nocase > 'aa' ORDER BY +b; 159 } 160 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB} 161 do_test collate2-1.3 { 162 execsql { 163 SELECT c FROM collate2t1 WHERE c > 'aa' ORDER BY 1; 164 } 165 } {ba Ab Bb ab bb} 166 do_test collate2-1.3.1 { 167 execsql { 168 SELECT c FROM collate2t1 WHERE a COLLATE backwards > 'aa' 169 ORDER BY 1; 170 } 171 } {ba Ab Bb ab bb} 172 do_test collate2-1.3.2 { 173 execsql { 174 SELECT c FROM collate2t1 WHERE b COLLATE backwards > 'aa' 175 ORDER BY 1; 176 } 177 } {ba Ab Bb ab bb} 178 do_test collate2-1.3.3 { 179 execsql { 180 SELECT c FROM collate2t1 WHERE c COLLATE backwards > 'aa' 181 ORDER BY 1; 182 } 183 } {ba Ab Bb ab bb} 184 do_test collate2-1.4 { 185 execsql { 186 SELECT a FROM collate2t1 WHERE a < 'aa' ORDER BY 1; 187 } 188 } {AA AB Aa Ab BA BB Ba Bb aA aB} 189 do_test collate2-1.5 { 190 execsql { 191 SELECT b FROM collate2t1 WHERE b < 'aa' ORDER BY 1, oid; 192 } 193 } {} 194 do_test collate2-1.5.1 { 195 execsql { 196 SELECT b FROM collate2t1 WHERE b < 'aa' ORDER BY +b; 197 } 198 } {} 199 do_test collate2-1.6 { 200 execsql { 201 SELECT c FROM collate2t1 WHERE c < 'aa' ORDER BY 1; 202 } 203 } {AA BA aA bA AB BB aB bB Aa Ba} 204 do_test collate2-1.7 { 205 execsql { 206 SELECT a FROM collate2t1 WHERE a = 'aa'; 207 } 208 } {aa} 209 do_test collate2-1.8 { 210 execsql { 211 SELECT b FROM collate2t1 WHERE b = 'aa' ORDER BY oid; 212 } 213 } {aa aA Aa AA} 214 do_test collate2-1.9 { 215 execsql { 216 SELECT c FROM collate2t1 WHERE c = 'aa'; 217 } 218 } {aa} 219 do_test collate2-1.10 { 220 execsql { 221 SELECT a FROM collate2t1 WHERE a >= 'aa' ORDER BY 1; 222 } 223 } {aa ab bA bB ba bb} 224 do_test collate2-1.11 { 225 execsql { 226 SELECT b FROM collate2t1 WHERE b >= 'aa' ORDER BY 1, oid; 227 } 228 } {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB} 229 do_test collate2-1.12 { 230 execsql { 231 SELECT c FROM collate2t1 WHERE c >= 'aa' ORDER BY 1; 232 } 233 } {aa ba Ab Bb ab bb} 234 do_test collate2-1.13 { 235 execsql { 236 SELECT a FROM collate2t1 WHERE a <= 'aa' ORDER BY 1; 237 } 238 } {AA AB Aa Ab BA BB Ba Bb aA aB aa} 239 do_test collate2-1.14 { 240 execsql { 241 SELECT b FROM collate2t1 WHERE b <= 'aa' ORDER BY 1, oid; 242 } 243 } {aa aA Aa AA} 244 do_test collate2-1.15 { 245 execsql { 246 SELECT c FROM collate2t1 WHERE c <= 'aa' ORDER BY 1; 247 } 248 } {AA BA aA bA AB BB aB bB Aa Ba aa} 249 do_test collate2-1.16 { 250 execsql { 251 SELECT a FROM collate2t1 WHERE a BETWEEN 'Aa' AND 'Bb' ORDER BY 1; 252 } 253 } {Aa Ab BA BB Ba Bb} 254 do_test collate2-1.17 { 255 execsql { 256 SELECT b FROM collate2t1 WHERE b BETWEEN 'Aa' AND 'Bb' ORDER BY 1, oid; 257 } 258 } {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB} 259 do_test collate2-1.17.1 { 260 execsql { 261 SELECT b FROM collate2t1 WHERE b BETWEEN 'Aa' AND 'Bb' ORDER BY +b; 262 } 263 } {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB} 264 do_test collate2-1.18 { 265 execsql { 266 SELECT c FROM collate2t1 WHERE c BETWEEN 'Aa' AND 'Bb' ORDER BY 1; 267 } 268 } {Aa Ba aa ba Ab Bb} 269 do_test collate2-1.19 { 270 execsql { 271 SELECT a FROM collate2t1 WHERE 272 CASE a WHEN 'aa' THEN 1 ELSE 0 END 273 ORDER BY 1, oid; 274 } 275 } {aa} 276 do_test collate2-1.20 { 277 execsql { 278 SELECT b FROM collate2t1 WHERE 279 CASE b WHEN 'aa' THEN 1 ELSE 0 END 280 ORDER BY 1, oid; 281 } 282 } {aa aA Aa AA} 283 do_test collate2-1.21 { 284 execsql { 285 SELECT c FROM collate2t1 WHERE 286 CASE c WHEN 'aa' THEN 1 ELSE 0 END 287 ORDER BY 1, oid; 288 } 289 } {aa} 290 291 ifcapable subquery { 292 do_test collate2-1.22 { 293 execsql { 294 SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb') ORDER BY 1, oid; 295 } 296 } {aa bb} 297 do_test collate2-1.23 { 298 execsql { 299 SELECT b FROM collate2t1 WHERE b IN ('aa', 'bb') ORDER BY 1, oid; 300 } 301 } {aa aA Aa AA bb bB Bb BB} 302 do_test collate2-1.24 { 303 execsql { 304 SELECT c FROM collate2t1 WHERE c IN ('aa', 'bb') ORDER BY 1, oid; 305 } 306 } {aa bb} 307 do_test collate2-1.25 { 308 execsql { 309 SELECT a FROM collate2t1 310 WHERE a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb')); 311 } 312 } {aa bb} 313 do_test collate2-1.26 { 314 execsql { 315 SELECT b FROM collate2t1 316 WHERE b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb')); 317 } 318 } {aa bb aA bB Aa Bb AA BB} 319 do_test collate2-1.27 { 320 execsql { 321 SELECT c FROM collate2t1 322 WHERE c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb')); 323 } 324 } {aa bb} 325 } ;# ifcapable subquery 326 327 do_test collate2-2.1 { 328 execsql { 329 SELECT a FROM collate2t1 WHERE NOT a > 'aa' ORDER BY 1; 330 } 331 } {AA AB Aa Ab BA BB Ba Bb aA aB aa} 332 do_test collate2-2.2 { 333 execsql { 334 SELECT b FROM collate2t1 WHERE NOT b > 'aa' ORDER BY 1, oid; 335 } 336 } {aa aA Aa AA} 337 do_test collate2-2.3 { 338 execsql { 339 SELECT c FROM collate2t1 WHERE NOT c > 'aa' ORDER BY 1; 340 } 341 } {AA BA aA bA AB BB aB bB Aa Ba aa} 342 do_test collate2-2.4 { 343 execsql { 344 SELECT a FROM collate2t1 WHERE NOT a < 'aa' ORDER BY 1; 345 } 346 } {aa ab bA bB ba bb} 347 do_test collate2-2.5 { 348 execsql { 349 SELECT b FROM collate2t1 WHERE NOT b < 'aa' ORDER BY 1, oid; 350 } 351 } {aa aA Aa AA ab aB Ab AB ba bA Ba BA bb bB Bb BB} 352 do_test collate2-2.6 { 353 execsql { 354 SELECT c FROM collate2t1 WHERE NOT c < 'aa' ORDER BY 1; 355 } 356 } {aa ba Ab Bb ab bb} 357 do_test collate2-2.7 { 358 execsql { 359 SELECT a FROM collate2t1 WHERE NOT a = 'aa'; 360 } 361 } {ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB} 362 do_test collate2-2.8 { 363 execsql { 364 SELECT b FROM collate2t1 WHERE NOT b = 'aa'; 365 } 366 } {ab ba bb aB bA bB Ab Ba Bb AB BA BB} 367 do_test collate2-2.9 { 368 execsql { 369 SELECT c FROM collate2t1 WHERE NOT c = 'aa'; 370 } 371 } {ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB} 372 do_test collate2-2.10 { 373 execsql { 374 SELECT a FROM collate2t1 WHERE NOT a >= 'aa' ORDER BY 1; 375 } 376 } {AA AB Aa Ab BA BB Ba Bb aA aB} 377 do_test collate2-2.11 { 378 execsql { 379 SELECT b FROM collate2t1 WHERE NOT b >= 'aa' ORDER BY 1, oid; 380 } 381 } {} 382 do_test collate2-2.12 { 383 execsql { 384 SELECT c FROM collate2t1 WHERE NOT c >= 'aa' ORDER BY 1; 385 } 386 } {AA BA aA bA AB BB aB bB Aa Ba} 387 do_test collate2-2.13 { 388 execsql { 389 SELECT a FROM collate2t1 WHERE NOT a <= 'aa' ORDER BY 1; 390 } 391 } {ab bA bB ba bb} 392 do_test collate2-2.14 { 393 execsql { 394 SELECT b FROM collate2t1 WHERE NOT b <= 'aa' ORDER BY 1, oid; 395 } 396 } {ab aB Ab AB ba bA Ba BA bb bB Bb BB} 397 do_test collate2-2.15 { 398 execsql { 399 SELECT c FROM collate2t1 WHERE NOT c <= 'aa' ORDER BY 1; 400 } 401 } {ba Ab Bb ab bb} 402 do_test collate2-2.16 { 403 execsql { 404 SELECT a FROM collate2t1 WHERE a NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1; 405 } 406 } {AA AB aA aB aa ab bA bB ba bb} 407 do_test collate2-2.17 { 408 execsql { 409 SELECT b FROM collate2t1 WHERE b NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1, oid; 410 } 411 } {} 412 do_test collate2-2.18 { 413 execsql { 414 SELECT c FROM collate2t1 WHERE c NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1; 415 } 416 } {AA BA aA bA AB BB aB bB ab bb} 417 do_test collate2-2.19 { 418 execsql { 419 SELECT a FROM collate2t1 WHERE NOT CASE a WHEN 'aa' THEN 1 ELSE 0 END; 420 } 421 } {{} ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB} 422 do_test collate2-2.20 { 423 execsql { 424 SELECT b FROM collate2t1 WHERE NOT CASE b WHEN 'aa' THEN 1 ELSE 0 END; 425 } 426 } {{} ab ba bb aB bA bB Ab Ba Bb AB BA BB} 427 do_test collate2-2.21 { 428 execsql { 429 SELECT c FROM collate2t1 WHERE NOT CASE c WHEN 'aa' THEN 1 ELSE 0 END; 430 } 431 } {{} ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB} 432 433 ifcapable subquery { 434 do_test collate2-2.22 { 435 execsql { 436 SELECT a FROM collate2t1 WHERE NOT a IN ('aa', 'bb'); 437 } 438 } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB} 439 do_test collate2-2.23 { 440 execsql { 441 SELECT b FROM collate2t1 WHERE NOT b IN ('aa', 'bb'); 442 } 443 } {ab ba aB bA Ab Ba AB BA} 444 do_test collate2-2.24 { 445 execsql { 446 SELECT c FROM collate2t1 WHERE NOT c IN ('aa', 'bb'); 447 } 448 } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB} 449 do_test collate2-2.25 { 450 execsql { 451 SELECT a FROM collate2t1 452 WHERE NOT a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb')); 453 } 454 } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB} 455 do_test collate2-2.26 { 456 execsql { 457 SELECT b FROM collate2t1 458 WHERE NOT b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb')); 459 } 460 } {ab ba aB bA Ab Ba AB BA} 461 do_test collate2-2.27 { 462 execsql { 463 SELECT c FROM collate2t1 464 WHERE NOT c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb')); 465 } 466 } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB} 467 } 468 469 do_test collate2-3.1 { 470 execsql { 471 SELECT a > 'aa' FROM collate2t1; 472 } 473 } {{} 0 1 1 1 0 0 1 1 0 0 0 0 0 0 0 0} 474 do_test collate2-3.2 { 475 execsql { 476 SELECT b > 'aa' FROM collate2t1; 477 } 478 } {{} 0 1 1 1 0 1 1 1 0 1 1 1 0 1 1 1} 479 do_test collate2-3.3 { 480 execsql { 481 SELECT c > 'aa' FROM collate2t1; 482 } 483 } {{} 0 1 1 1 0 0 0 0 0 1 0 1 0 0 0 0} 484 do_test collate2-3.4 { 485 execsql { 486 SELECT a < 'aa' FROM collate2t1; 487 } 488 } {{} 0 0 0 0 1 1 0 0 1 1 1 1 1 1 1 1} 489 do_test collate2-3.5 { 490 execsql { 491 SELECT b < 'aa' FROM collate2t1; 492 } 493 } {{} 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 494 do_test collate2-3.6 { 495 execsql { 496 SELECT c < 'aa' FROM collate2t1; 497 } 498 } {{} 0 0 0 0 1 1 1 1 1 0 1 0 1 1 1 1} 499 do_test collate2-3.7 { 500 execsql { 501 SELECT a = 'aa' FROM collate2t1; 502 } 503 } {{} 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 504 do_test collate2-3.8 { 505 execsql { 506 SELECT b = 'aa' FROM collate2t1; 507 } 508 } {{} 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0} 509 do_test collate2-3.9 { 510 execsql { 511 SELECT c = 'aa' FROM collate2t1; 512 } 513 } {{} 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 514 do_test collate2-3.10 { 515 execsql { 516 SELECT a <= 'aa' FROM collate2t1; 517 } 518 } {{} 1 0 0 0 1 1 0 0 1 1 1 1 1 1 1 1} 519 do_test collate2-3.11 { 520 execsql { 521 SELECT b <= 'aa' FROM collate2t1; 522 } 523 } {{} 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0} 524 do_test collate2-3.12 { 525 execsql { 526 SELECT c <= 'aa' FROM collate2t1; 527 } 528 } {{} 1 0 0 0 1 1 1 1 1 0 1 0 1 1 1 1} 529 do_test collate2-3.13 { 530 execsql { 531 SELECT a >= 'aa' FROM collate2t1; 532 } 533 } {{} 1 1 1 1 0 0 1 1 0 0 0 0 0 0 0 0} 534 do_test collate2-3.14 { 535 execsql { 536 SELECT b >= 'aa' FROM collate2t1; 537 } 538 } {{} 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1} 539 do_test collate2-3.15 { 540 execsql { 541 SELECT c >= 'aa' FROM collate2t1; 542 } 543 } {{} 1 1 1 1 0 0 0 0 0 1 0 1 0 0 0 0} 544 do_test collate2-3.16 { 545 execsql { 546 SELECT a BETWEEN 'Aa' AND 'Bb' FROM collate2t1; 547 } 548 } {{} 0 0 0 0 0 0 0 0 1 1 1 1 0 0 1 1} 549 do_test collate2-3.17 { 550 execsql { 551 SELECT b BETWEEN 'Aa' AND 'Bb' FROM collate2t1; 552 } 553 } {{} 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1} 554 do_test collate2-3.18 { 555 execsql { 556 SELECT c BETWEEN 'Aa' AND 'Bb' FROM collate2t1; 557 } 558 } {{} 1 0 1 0 0 0 0 0 1 1 1 1 0 0 0 0} 559 do_test collate2-3.19 { 560 execsql { 561 SELECT CASE a WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1; 562 } 563 } {0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 564 do_test collate2-3.20 { 565 execsql { 566 SELECT CASE b WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1; 567 } 568 } {0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0} 569 do_test collate2-3.21 { 570 execsql { 571 SELECT CASE c WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1; 572 } 573 } {0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0} 574 575 ifcapable subquery { 576 do_test collate2-3.22 { 577 execsql { 578 SELECT a IN ('aa', 'bb') FROM collate2t1; 579 } 580 } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0} 581 do_test collate2-3.23 { 582 execsql { 583 SELECT b IN ('aa', 'bb') FROM collate2t1; 584 } 585 } {{} 1 0 0 1 1 0 0 1 1 0 0 1 1 0 0 1} 586 do_test collate2-3.24 { 587 execsql { 588 SELECT c IN ('aa', 'bb') FROM collate2t1; 589 } 590 } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0} 591 do_test collate2-3.25 { 592 execsql { 593 SELECT a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb')) 594 FROM collate2t1; 595 } 596 } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0} 597 do_test collate2-3.26 { 598 execsql { 599 SELECT b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb')) 600 FROM collate2t1; 601 } 602 } {{} 1 0 0 1 1 0 0 1 1 0 0 1 1 0 0 1} 603 do_test collate2-3.27 { 604 execsql { 605 SELECT c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb')) 606 FROM collate2t1; 607 } 608 } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0} 609 } 610 611 do_test collate2-4.0 { 612 execsql { 613 CREATE TABLE collate2t2(b COLLATE binary); 614 CREATE TABLE collate2t3(b text); 615 INSERT INTO collate2t2 VALUES('aa'); 616 INSERT INTO collate2t3 VALUES('aa'); 617 } 618 } {} 619 620 # Test that when both sides of a binary comparison operator have 621 # default collation types, the collate type for the leftmost term 622 # is used. 623 do_test collate2-4.1 { 624 execsql { 625 SELECT collate2t1.a FROM collate2t1, collate2t2 626 WHERE collate2t1.b = collate2t2.b; 627 } 628 } {aa aA Aa AA} 629 do_test collate2-4.2 { 630 execsql { 631 SELECT collate2t1.a FROM collate2t1, collate2t2 632 WHERE collate2t2.b = collate2t1.b; 633 } 634 } {aa} 635 636 # Test that when one side has a default collation type and the other 637 # does not, the collation type is used. 638 do_test collate2-4.3 { 639 execsql { 640 SELECT collate2t1.a FROM collate2t1, collate2t3 641 WHERE collate2t1.b = collate2t3.b||'' 642 ORDER BY +collate2t1.a DESC; 643 } 644 } {aa aA Aa AA} 645 do_test collate2-4.4 { 646 execsql { 647 SELECT collate2t1.a FROM collate2t1, collate2t3 648 WHERE collate2t3.b||'' = collate2t1.b 649 ORDER BY +collate2t1.a DESC; 650 } 651 } {aa aA Aa AA} 652 653 do_test collate2-4.5 { 654 execsql { 655 DROP TABLE collate2t3; 656 } 657 } {} 658 659 # 660 # Test that the default collation types are used when the JOIN syntax 661 # is used in place of a WHERE clause. 662 # 663 # SQLite transforms the JOIN syntax into a WHERE clause internally, so 664 # the focus of these tests is to ensure that the table on the left-hand-side 665 # of the join determines the collation type used. 666 # 667 do_test collate2-5.0 { 668 execsql { 669 SELECT collate2t1.b FROM collate2t1 JOIN collate2t2 USING (b); 670 } 671 } {aa aA Aa AA} 672 do_test collate2-5.1 { 673 execsql { 674 SELECT collate2t1.b FROM collate2t2 JOIN collate2t1 USING (b); 675 } 676 } {aa} 677 do_test collate2-5.2 { 678 execsql { 679 SELECT collate2t1.b FROM collate2t1 NATURAL JOIN collate2t2; 680 } 681 } {aa aA Aa AA} 682 do_test collate2-5.3 { 683 execsql { 684 SELECT collate2t1.b FROM collate2t2 NATURAL JOIN collate2t1; 685 } 686 } {aa} 687 do_test collate2-5.4.1 { 688 execsql { 689 SELECT collate2t2.b FROM collate2t1 LEFT JOIN collate2t2 USING (b) order by collate2t1.oid; 690 } 691 } {{} aa {} {} {} aa {} {} {} aa {} {} {} aa {} {} {}} 692 do_test collate2-5.4.2 { 693 execsql { 694 SELECT collate2t2.b FROM collate2t2 RIGHT JOIN collate2t1 ON collate2t1.b=collate2t2.b 695 ORDER BY collate2t1.oid; 696 } 697 } {{} aa {} {} {} aa {} {} {} aa {} {} {} aa {} {} {}} 698 do_test collate2-5.4.3 { 699 execsql { 700 SELECT collate2t2.b FROM collate2t1 LEFT JOIN collate2t2 ON collate2t2.b=collate2t1.b 701 ORDER BY collate2t1.oid; 702 } 703 } {{} aa {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} 704 do_test collate2-5.5.1 { 705 execsql { 706 SELECT collate2t1.b, collate2t2.b FROM collate2t2 LEFT OUTER JOIN collate2t1 USING (b); 707 } 708 } {aa aa} 709 do_test collate2-5.5.2 { 710 execsql { 711 SELECT collate2t1.b, collate2t2.b 712 FROM collate2t1 RIGHT JOIN collate2t2 ON collate2t2.b=collate2t1.b 713 } 714 } {aa aa} 715 716 do_execsql_test 6.1 { 717 CREATE TABLE t1(x); 718 INSERT INTO t1 VALUES('b'); 719 INSERT INTO t1 VALUES('B'); 720 } 721 do_execsql_test 6.2 { 722 SELECT * FROM t1 WHERE x COLLATE nocase BETWEEN 'a' AND 'c'; 723 } {b B} 724 do_execsql_test 6.3 { 725 SELECT * FROM t1 WHERE x BETWEEN 'a' COLLATE nocase AND 'c' COLLATE nocase; 726 } {b B} 727 do_execsql_test 6.4 { 728 SELECT * FROM t1 729 WHERE x COLLATE nocase BETWEEN 'a' COLLATE nocase AND 'c' COLLATE nocase; 730 } {b B} 731 do_execsql_test 6.5 { 732 SELECT * FROM t1 WHERE +x COLLATE nocase BETWEEN 'a' AND 'c'; 733 } {b B} 734 do_execsql_test 6.6 { 735 SELECT * FROM t1 WHERE +x BETWEEN 'a' COLLATE nocase AND 'c' COLLATE nocase; 736 } {b B} 737 do_execsql_test 6.7 { 738 SELECT * FROM t1 739 WHERE +x COLLATE nocase BETWEEN 'a' COLLATE nocase AND 'c' COLLATE nocase; 740 } {b B} 741 742 finish_test