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