AryaWu/sqlite
0
1#2# 2001 September 153#4# The author disclaims copyright to this source code. In place of5# 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. The13# 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 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20set ::testprefix collate221 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 comparisons29# collate2-5.* JOIN syntax.30#31 32# Create a collation type BACKWARDS for use in testing. This collation type33# is similar to the built-in TEXT collation type except the order of34# characters in each string is reversed before the comparison is performed.35db collate BACKWARDS backwards_collate36proc 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 used54# 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 BACKWARDS58# collation sequence is implemented by the TCL proc backwards_collate59# above.60#61do_test collate2-1.0 {62 execsql {63 CREATE TABLE collate2t1(64 a COLLATE BINARY, 65 b COLLATE NOCASE, 66 c COLLATE BACKWARDS67 );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} {}98do_test collate2-1.1 {99 execsql {100 SELECT a FROM collate2t1 WHERE a > 'aa' ORDER BY 1;101 }102} {ab bA bB ba bb}103do_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}108do_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}113do_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}118do_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}123do_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}129do_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}135do_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}141do_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}146do_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}151do_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}156do_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}161do_test collate2-1.3 {162 execsql {163 SELECT c FROM collate2t1 WHERE c > 'aa' ORDER BY 1;164 }165} {ba Ab Bb ab bb}166do_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}172do_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}178do_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}184do_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}189do_test collate2-1.5 {190 execsql {191 SELECT b FROM collate2t1 WHERE b < 'aa' ORDER BY 1, oid;192 }193} {}194do_test collate2-1.5.1 {195 execsql {196 SELECT b FROM collate2t1 WHERE b < 'aa' ORDER BY +b;197 }198} {}199do_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}204do_test collate2-1.7 {205 execsql {206 SELECT a FROM collate2t1 WHERE a = 'aa';207 }208} {aa}209do_test collate2-1.8 {210 execsql {211 SELECT b FROM collate2t1 WHERE b = 'aa' ORDER BY oid;212 }213} {aa aA Aa AA}214do_test collate2-1.9 {215 execsql {216 SELECT c FROM collate2t1 WHERE c = 'aa';217 }218} {aa}219do_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}224do_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}229do_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}234do_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}239do_test collate2-1.14 {240 execsql {241 SELECT b FROM collate2t1 WHERE b <= 'aa' ORDER BY 1, oid;242 }243} {aa aA Aa AA}244do_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}249do_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}254do_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}259do_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}264do_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}269do_test collate2-1.19 {270 execsql {271 SELECT a FROM collate2t1 WHERE 272 CASE a WHEN 'aa' THEN 1 ELSE 0 END273 ORDER BY 1, oid;274 }275} {aa}276do_test collate2-1.20 {277 execsql {278 SELECT b FROM collate2t1 WHERE 279 CASE b WHEN 'aa' THEN 1 ELSE 0 END280 ORDER BY 1, oid;281 }282} {aa aA Aa AA}283do_test collate2-1.21 {284 execsql {285 SELECT c FROM collate2t1 WHERE 286 CASE c WHEN 'aa' THEN 1 ELSE 0 END287 ORDER BY 1, oid;288 }289} {aa}290 291ifcapable 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 subquery326 327do_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}332do_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}337do_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}342do_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}347do_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}352do_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}357do_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}362do_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}367do_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}372do_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}377do_test collate2-2.11 {378 execsql {379 SELECT b FROM collate2t1 WHERE NOT b >= 'aa' ORDER BY 1, oid;380 }381} {}382do_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}387do_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}392do_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}397do_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}402do_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}407do_test collate2-2.17 {408 execsql {409 SELECT b FROM collate2t1 WHERE b NOT BETWEEN 'Aa' AND 'Bb' ORDER BY 1, oid;410 }411} {}412do_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}417do_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}422do_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}427do_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 433ifcapable 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 469do_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}474do_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}479do_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}484do_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}489do_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}494do_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}499do_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}504do_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}509do_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}514do_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}519do_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}524do_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}529do_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}534do_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}539do_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}544do_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}549do_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}554do_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}559do_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}564do_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}569do_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 575ifcapable 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 611do_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 have621# default collation types, the collate type for the leftmost term622# is used.623do_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}629do_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 other637# does not, the collation type is used.638do_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}645do_test collate2-4.4 {646 execsql {647 SELECT collate2t1.a FROM collate2t1, collate2t3 648 WHERE collate2t3.b||'' = collate2t1.b649 ORDER BY +collate2t1.a DESC;650 }651} {aa aA Aa AA}652 653do_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 syntax661# is used in place of a WHERE clause.662#663# SQLite transforms the JOIN syntax into a WHERE clause internally, so664# the focus of these tests is to ensure that the table on the left-hand-side665# of the join determines the collation type used. 666#667do_test collate2-5.0 {668 execsql {669 SELECT collate2t1.b FROM collate2t1 JOIN collate2t2 USING (b);670 }671} {aa aA Aa AA}672do_test collate2-5.1 {673 execsql {674 SELECT collate2t1.b FROM collate2t2 JOIN collate2t1 USING (b);675 }676} {aa}677do_test collate2-5.2 {678 execsql {679 SELECT collate2t1.b FROM collate2t1 NATURAL JOIN collate2t2;680 }681} {aa aA Aa AA}682do_test collate2-5.3 {683 execsql {684 SELECT collate2t1.b FROM collate2t2 NATURAL JOIN collate2t1;685 }686} {aa}687do_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 {} {} {}}692do_test collate2-5.4.2 {693 execsql {694 SELECT collate2t2.b FROM collate2t2 RIGHT JOIN collate2t1 ON collate2t1.b=collate2t2.b695 ORDER BY collate2t1.oid;696 }697} {{} aa {} {} {} aa {} {} {} aa {} {} {} aa {} {} {}}698do_test collate2-5.4.3 {699 execsql {700 SELECT collate2t2.b FROM collate2t1 LEFT JOIN collate2t2 ON collate2t2.b=collate2t1.b701 ORDER BY collate2t1.oid;702 }703} {{} aa {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}}704do_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}709do_test collate2-5.5.2 {710 execsql {711 SELECT collate2t1.b, collate2t2.b712 FROM collate2t1 RIGHT JOIN collate2t2 ON collate2t2.b=collate2t1.b713 }714} {aa aa}715 716do_execsql_test 6.1 {717 CREATE TABLE t1(x);718 INSERT INTO t1 VALUES('b');719 INSERT INTO t1 VALUES('B');720}721do_execsql_test 6.2 {722 SELECT * FROM t1 WHERE x COLLATE nocase BETWEEN 'a' AND 'c';723} {b B}724do_execsql_test 6.3 {725 SELECT * FROM t1 WHERE x BETWEEN 'a' COLLATE nocase AND 'c' COLLATE nocase;726} {b B}727do_execsql_test 6.4 {728 SELECT * FROM t1 729 WHERE x COLLATE nocase BETWEEN 'a' COLLATE nocase AND 'c' COLLATE nocase;730} {b B}731do_execsql_test 6.5 {732 SELECT * FROM t1 WHERE +x COLLATE nocase BETWEEN 'a' AND 'c';733} {b B}734do_execsql_test 6.6 {735 SELECT * FROM t1 WHERE +x BETWEEN 'a' COLLATE nocase AND 'c' COLLATE nocase;736} {b B}737do_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 742finish_test743