test/collate1.test

   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 testing collation sequences.
  14 #
  15
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18 set testprefix collate1
  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 # A problem reported on the mailing list:  A CREATE TABLE statement
 308 # is allowed to have two or more COLLATE clauses on the same column.
 309 # That probably ought to be an error, but we allow it for backwards
 310 # compatibility.  Just make sure it works and doesn't leak memory.
 311 #
 312 do_test collate1-5.1 {
 313   execsql {
 314     CREATE TABLE c5(
 315       id INTEGER PRIMARY KEY,
 316       a TEXT COLLATE binary COLLATE nocase COLLATE rtrim,
 317       b TEXT COLLATE nocase COLLATE binary,
 318       c TEXT COLLATE rtrim COLLATE binary COLLATE rtrim COLLATE nocase
 319     );
 320     INSERT INTO c5 VALUES(1, 'abc','abc','abc');
 321     INSERT INTO c5 VALUES(2, 'abc   ','ABC','ABC');
 322     SELECT id FROM c5 WHERE a='abc' ORDER BY id;
 323   }
 324 } {1 2}
 325 do_test collate1-5.2 {
 326   execsql {
 327     SELECT id FROM c5 WHERE b='abc' ORDER BY id;
 328   }
 329 } {1}
 330 do_test collate1-5.3 {
 331   execsql {
 332     SELECT id FROM c5 WHERE c='abc' ORDER BY id;
 333   }
 334 } {1 2}
 335
 336
 337
 338 #-------------------------------------------------------------------------
 339 # Fix problems with handling collation sequences named '"""'.
 340 #
 341 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1
 342 do_execsql_test 6.1 {
 343   SELECT """""""";
 344 } {\"\"\"}
 345
 346 do_catchsql_test 6.2 {
 347   CREATE TABLE x1(a);
 348   SELECT a FROM x1 ORDER BY a COLLATE """""""";
 349 } {1 {no such collation sequence: """}}
 350
 351 do_catchsql_test 6.3 {
 352   SELECT a FROM x1 ORDER BY 1 COLLATE """""""";
 353 } {1 {no such collation sequence: """}}
 354
 355 do_catchsql_test 6.4 {
 356   SELECT 0 UNION SELECT 0 ORDER BY 1 COLLATE """""""";
 357 } {1 {no such collation sequence: """}}
 358
 359 db collate {"""} [list string compare -nocase]
 360
 361 do_execsql_test 6.5 {
 362   PRAGMA foreign_keys = ON;
 363   CREATE TABLE p1(a PRIMARY KEY COLLATE '"""');
 364   CREATE TABLE c1(x, y REFERENCES p1);
 365 } {}
 366
 367 do_execsql_test 6.6 {
 368   INSERT INTO p1 VALUES('abc');
 369   INSERT INTO c1 VALUES(1, 'ABC');
 370 }
 371
 372 ifcapable foreignkey {
 373   do_catchsql_test 6.7 {
 374     DELETE FROM p1 WHERE rowid = 1
 375   } {1 {FOREIGN KEY constraint failed}}
 376 }
 377
 378 do_execsql_test 6.8 {
 379   INSERT INTO p1 VALUES('abb');
 380   INSERT INTO p1 VALUES('wxz');
 381   INSERT INTO p1 VALUES('wxy');
 382
 383   INSERT INTO c1 VALUES(2, 'abb');
 384   INSERT INTO c1 VALUES(3, 'wxz');
 385   INSERT INTO c1 VALUES(4, 'WXY');
 386   SELECT x, y FROM c1 ORDER BY y COLLATE """""""";
 387 } {2 abb 1 ABC 4 WXY 3 wxz}
 388
 389 # 2015-04-15:  Nested COLLATE operators
 390 #
 391 do_execsql_test 7.0 {
 392    SELECT 'abc' UNION ALL SELECT 'DEF'
 393     ORDER BY 1 COLLATE nocase COLLATE nocase COLLATE nocase COLLATE nocase;
 394 } {abc DEF}
 395 do_execsql_test 7.1 {
 396    SELECT 'abc' UNION ALL SELECT 'DEF'
 397     ORDER BY 1 COLLATE nocase COLLATE nocase COLLATE nocase COLLATE binary;
 398 } {DEF abc}
 399 do_execsql_test 7.2 {
 400    SELECT 'abc' UNION ALL SELECT 'DEF'
 401     ORDER BY 1 COLLATE binary COLLATE binary COLLATE binary COLLATE nocase;
 402 } {abc DEF}
 403
 404 # 2019-06-14
 405 # https://sqlite.org/src/info/f1580ba1b574e9e9
 406 #
 407 do_execsql_test 8.0 {
 408   SELECT ' ' > char(20) COLLATE rtrim;
 409 } 0
 410 do_execsql_test 8.1 {
 411   SELECT '' < char(20) COLLATE rtrim;
 412 } 1
 413 do_execsql_test 8.2 {
 414   DROP TABLE IF EXISTS t0;
 415   CREATE TABLE t0(c0 COLLATE RTRIM, c1 BLOB UNIQUE,
 416                   PRIMARY KEY (c0, c1)) WITHOUT ROWID;
 417   INSERT INTO t0 VALUES (123, 3), (' ', 1), ('  ', 2), ('', 4);
 418   SELECT * FROM t0 WHERE c1 = 1;
 419 } {{ } 1}
 420
 421 # 2019-10-09
 422 # ALWAYS() macro fails following OOM
 423 # Problem detected by dbsqlfuzz.
 424 #
 425 do_execsql_test 9.0 {
 426   CREATE TABLE t1(a, b);
 427   CREATE TABLE t2(c, d);
 428 }
 429
 430 do_faultsim_test 9.1 -faults oom* -body {
 431   execsql {
 432     SELECT * FROM (
 433         SELECT b COLLATE nocase IN (SELECT c FROM t2) FROM t1
 434     );
 435   }
 436 } -test {
 437   faultsim_test_result {0 {}}
 438 }
 439
 440 # 2020-01-03 dbsqlfuzz find
 441 #
 442 reset_db
 443 do_catchsql_test 10.0 {
 444   CREATE TABLE t1(a INTEGER PRIMARY KEY,b);
 445   INSERT INTO t1 VALUES(0,NULL);
 446   CREATE TABLE t2(x UNIQUE);
 447   CREATE VIEW v1a(z,y) AS SELECT x COLLATE x FROM t2;
 448   SELECT a,b,z,y,'' FROM t1 JOIN v1a ON b IS NOT FALSE;
 449 } {1 {no such collation sequence: x}}
 450
 451
 452 finish_test