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