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: collate1.test,v 1.5 2007/02/01 23:02:46 drh Exp $

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 #

    21 # Tests are roughly organised as follows:

    22 #

    23 # collate1-1.* - Single-field ORDER BY with an explicit COLLATE clause.

    24 # collate1-2.* - Multi-field ORDER BY with an explicit COLLATE clause.

    25 # collate1-3.* - ORDER BY using a default collation type. Also that an 

    26 #                explict collate type overrides a default collate type.

    27 # collate1-4.* - ORDER BY using a data type.

    28 #

    29 

    30 #

    31 # Collation type 'HEX'. If an argument can be interpreted as a hexadecimal

    32 # number, then it is converted to one before the comparison is performed. 

    33 # Numbers are less than other strings. If neither argument is a number, 

    34 # [string compare] is used.

    35 #

    36 db collate HEX hex_collate

    37 proc hex_collate {lhs rhs} {

    38   set lhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $lhs]

    39   set rhs_ishex [regexp {^(0x|)[1234567890abcdefABCDEF]+$} $rhs]

    40   if {$lhs_ishex && $rhs_ishex} { 

    41     set lhsx [scan $lhs %x]

    42     set rhsx [scan $rhs %x]

    43     if {$lhs < $rhs} {return -1}

    44     if {$lhs == $rhs} {return 0}

    45     if {$lhs > $rhs} {return 1}

    46   }

    47   if {$lhs_ishex} {

    48     return -1;

    49   }

    50   if {$rhs_ishex} {

    51     return 1;

    52   }

    53   return [string compare $lhs $rhs]

    54 }

    55 db function hex {format 0x%X}

    56 

    57 # Mimic the SQLite 2 collation type NUMERIC.

    58 db collate numeric numeric_collate

    59 proc numeric_collate {lhs rhs} {

    60   if {$lhs == $rhs} {return 0} 

    61   return [expr ($lhs>$rhs)?1:-1]

    62 }

    63 

    64 do_test collate1-1.0 {

    65   execsql {

    66     CREATE TABLE collate1t1(c1, c2);

    67     INSERT INTO collate1t1 VALUES(45, hex(45));

    68     INSERT INTO collate1t1 VALUES(NULL, NULL);

    69     INSERT INTO collate1t1 VALUES(281, hex(281));

    70   }

    71 } {}

    72 do_test collate1-1.1 {

    73   execsql {

    74     SELECT c2 FROM collate1t1 ORDER BY 1;

    75   }

    76 } {{} 0x119 0x2D}

    77 do_test collate1-1.2 {

    78   execsql {

    79     SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex;

    80   }

    81 } {{} 0x2D 0x119}

    82 do_test collate1-1.3 {

    83   execsql {

    84     SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex DESC;

    85   }

    86 } {0x119 0x2D {}}

    87 do_test collate1-1.4 {

    88   execsql {

    89    SELECT c2 FROM collate1t1 ORDER BY 1 COLLATE hex ASC;

    90   }

    91 } {{} 0x2D 0x119}

    92 do_test collate1-1.5 {

    93   execsql {

    94     SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1

    95   }

    96 } {{} 0x2D 0x119}

    97 do_test collate1-1.6 {

    98   execsql {

    99     SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 ASC

   100   }

   101 } {{} 0x2D 0x119}

   102 do_test collate1-1.7 {

   103   execsql {

   104     SELECT c2 COLLATE hex FROM collate1t1 ORDER BY 1 DESC

   105   }

   106 } {0x119 0x2D {}}

   107 do_test collate1-1.99 {

   108   execsql {

   109     DROP TABLE collate1t1;

   110   }

   111 } {}

   112 

   113 do_test collate1-2.0 {

   114   execsql {

   115     CREATE TABLE collate1t1(c1, c2);

   116     INSERT INTO collate1t1 VALUES('5', '0x11');

   117     INSERT INTO collate1t1 VALUES('5', '0xA');

   118     INSERT INTO collate1t1 VALUES(NULL, NULL);

   119     INSERT INTO collate1t1 VALUES('7', '0xA');

   120     INSERT INTO collate1t1 VALUES('11', '0x11');

   121     INSERT INTO collate1t1 VALUES('11', '0x101');

   122   }

   123 } {}

   124 do_test collate1-2.2 {

   125   execsql {

   126     SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE numeric, 2 COLLATE hex;

   127   }

   128 } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}

   129 do_test collate1-2.3 {

   130   execsql {

   131     SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary, 2 COLLATE hex;

   132   }

   133 } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}

   134 do_test collate1-2.4 {

   135   execsql {

   136     SELECT c1, c2 FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex;

   137   }

   138 } {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}}

   139 do_test collate1-2.5 {

   140   execsql {

   141     SELECT c1, c2 FROM collate1t1 

   142         ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex DESC;

   143   }

   144 } {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}}

   145 do_test collate1-2.6 {

   146   execsql {

   147     SELECT c1, c2 FROM collate1t1 

   148         ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC;

   149   }

   150 } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}

   151 do_test collate1-2.12.1 {

   152   execsql {

   153     SELECT c1 COLLATE numeric, c2 FROM collate1t1 

   154      ORDER BY 1, 2 COLLATE hex;

   155   }

   156 } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}

   157 do_test collate1-2.12.2 {

   158   execsql {

   159     SELECT c1 COLLATE hex, c2 FROM collate1t1 

   160      ORDER BY 1 COLLATE numeric, 2 COLLATE hex;

   161   }

   162 } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}

   163 do_test collate1-2.12.3 {

   164   execsql {

   165     SELECT c1, c2 COLLATE hex FROM collate1t1 

   166      ORDER BY 1 COLLATE numeric, 2;

   167   }

   168 } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}

   169 do_test collate1-2.12.4 {

   170   execsql {

   171     SELECT c1 COLLATE numeric, c2 COLLATE hex

   172       FROM collate1t1 

   173      ORDER BY 1, 2;

   174   }

   175 } {{} {} 5 0xA 5 0x11 7 0xA 11 0x11 11 0x101}

   176 do_test collate1-2.13 {

   177   execsql {

   178     SELECT c1 COLLATE binary, c2 COLLATE hex

   179       FROM collate1t1

   180      ORDER BY 1, 2;

   181   }

   182 } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}

   183 do_test collate1-2.14 {

   184   execsql {

   185     SELECT c1, c2

   186       FROM collate1t1 ORDER BY 1 COLLATE binary DESC, 2 COLLATE hex;

   187   }

   188 } {7 0xA 5 0xA 5 0x11 11 0x11 11 0x101 {} {}}

   189 do_test collate1-2.15 {

   190   execsql {

   191     SELECT c1 COLLATE binary, c2 COLLATE hex

   192       FROM collate1t1 

   193      ORDER BY 1 DESC, 2 DESC;

   194   }

   195 } {7 0xA 5 0x11 5 0xA 11 0x101 11 0x11 {} {}}

   196 do_test collate1-2.16 {

   197   execsql {

   198     SELECT c1 COLLATE hex, c2 COLLATE binary

   199       FROM collate1t1 

   200      ORDER BY 1 COLLATE binary ASC, 2 COLLATE hex ASC;

   201   }

   202 } {{} {} 11 0x11 11 0x101 5 0xA 5 0x11 7 0xA}

   203 do_test collate1-2.99 {

   204   execsql {

   205     DROP TABLE collate1t1;

   206   }

   207 } {}

   208 

   209 #

   210 # These tests ensure that the default collation type for a column is used 

   211 # by an ORDER BY clause correctly. The focus is all the different ways

   212 # the column can be referenced. i.e. a, collate2t1.a, main.collate2t1.a etc.

   213 #

   214 do_test collate1-3.0 {

   215   execsql {

   216     CREATE TABLE collate1t1(a COLLATE hex, b);

   217     INSERT INTO collate1t1 VALUES( '0x5', 5 );

   218     INSERT INTO collate1t1 VALUES( '1', 1 );

   219     INSERT INTO collate1t1 VALUES( '0x45', 69 );

   220     INSERT INTO collate1t1 VALUES( NULL, NULL );

   221     SELECT * FROM collate1t1 ORDER BY a;

   222   }

   223 } {{} {} 1 1 0x5 5 0x45 69}

   224 

   225 do_test collate1-3.1 {

   226   execsql {

   227     SELECT * FROM collate1t1 ORDER BY 1;

   228   }

   229 } {{} {} 1 1 0x5 5 0x45 69}

   230 do_test collate1-3.2 {

   231   execsql {

   232     SELECT * FROM collate1t1 ORDER BY collate1t1.a;

   233   }

   234 } {{} {} 1 1 0x5 5 0x45 69}

   235 do_test collate1-3.3 {

   236   execsql {

   237     SELECT * FROM collate1t1 ORDER BY main.collate1t1.a;

   238   }

   239 } {{} {} 1 1 0x5 5 0x45 69}

   240 do_test collate1-3.4 {

   241   execsql {

   242     SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1;

   243   }

   244 } {{} {} 1 1 0x5 5 0x45 69}

   245 do_test collate1-3.5 {

   246   execsql {

   247     SELECT a as c1, b as c2 FROM collate1t1 ORDER BY c1 COLLATE binary;

   248   }

   249 } {{} {} 0x45 69 0x5 5 1 1}

   250 do_test collate1-3.5.1 {

   251   execsql {

   252     SELECT a COLLATE binary as c1, b as c2

   253       FROM collate1t1 ORDER BY c1;

   254   }

   255 } {{} {} 0x45 69 0x5 5 1 1}

   256 do_test collate1-3.6 {

   257   execsql {

   258     DROP TABLE collate1t1;

   259   }

   260 } {}

   261 

   262 # Update for SQLite version 3. The collate1-4.* test cases were written

   263 # before manifest types were introduced. The following test cases still

   264 # work, due to the 'affinity' mechanism, but they don't prove anything

   265 # about collation sequences.

   266 #

   267 do_test collate1-4.0 {

   268   execsql {

   269     CREATE TABLE collate1t1(c1 numeric, c2 text);

   270     INSERT INTO collate1t1 VALUES(1, 1);

   271     INSERT INTO collate1t1 VALUES(12, 12);

   272     INSERT INTO collate1t1 VALUES(NULL, NULL);

   273     INSERT INTO collate1t1 VALUES(101, 101);

   274   }

   275 } {}

   276 do_test collate1-4.1 {

   277   execsql {

   278     SELECT c1 FROM collate1t1 ORDER BY 1;

   279   }

   280 } {{} 1 12 101}

   281 do_test collate1-4.2 {

   282   execsql {

   283     SELECT c2 FROM collate1t1 ORDER BY 1;

   284   }

   285 } {{} 1 101 12}

   286 do_test collate1-4.3 {

   287   execsql {

   288     SELECT c2+0 FROM collate1t1 ORDER BY 1;

   289   }

   290 } {{} 1 12 101}

   291 do_test collate1-4.4 {

   292   execsql {

   293     SELECT c1||'' FROM collate1t1 ORDER BY 1;

   294   }

   295 } {{} 1 101 12}

   296 do_test collate1-4.4.1 {

   297   execsql {

   298     SELECT (c1||'') COLLATE numeric FROM collate1t1 ORDER BY 1;

   299   }

   300 } {{} 1 12 101}

   301 do_test collate1-4.5 {

   302   execsql {

   303     DROP TABLE collate1t1;

   304   }

   305 } {}

   306 

   307 finish_test