test/collate5.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 file is testing DISTINCT, UNION, INTERSECT and EXCEPT
  14 # SELECT statements that use user-defined collation sequences. Also
  15 # GROUP BY clauses that use user-defined collation sequences.
  16 #
  17 # $Id: collate5.test,v 1.7 2008/09/16 11:58:20 drh Exp $
  18
  19 set testdir [file dirname $argv0]
  20 source $testdir/tester.tcl
  21
  22 set testprefix collate5
  23
  24
  25 #
  26 # Tests are organised as follows:
  27 # collate5-1.* - DISTINCT
  28 # collate5-2.* - Compound SELECT
  29 # collate5-3.* - ORDER BY on compound SELECT
  30 # collate5-4.* - GROUP BY
  31 # collate5-5.* - Collation sequence cases
  32
  33 # Create the collation sequence 'TEXT', purely for asthetic reasons. The
  34 # test cases in this script could just as easily use BINARY.
  35 db collate TEXT [list string compare]
  36
  37 # Mimic the SQLite 2 collation type NUMERIC.
  38 db collate numeric numeric_collate
  39 proc numeric_collate {lhs rhs} {
  40   if {$lhs == $rhs} {return 0}
  41   return [expr ($lhs>$rhs)?1:-1]
  42 }
  43
  44 #
  45 # These tests - collate5-1.* - focus on the DISTINCT keyword.
  46 #
  47 do_test collate5-1.0 {
  48   execsql {
  49     CREATE TABLE collate5t1(a COLLATE nocase, b COLLATE text);
  50
  51     INSERT INTO collate5t1 VALUES('a', 'apple');
  52     INSERT INTO collate5t1 VALUES('A', 'Apple');
  53     INSERT INTO collate5t1 VALUES('b', 'banana');
  54     INSERT INTO collate5t1 VALUES('B', 'banana');
  55     INSERT INTO collate5t1 VALUES('n', NULL);
  56     INSERT INTO collate5t1 VALUES('N', NULL);
  57   }
  58 } {}
  59 do_test collate5-1.1 {
  60   execsql {
  61     SELECT DISTINCT a FROM collate5t1;
  62   }
  63 } {a b n}
  64 do_test collate5-1.2 {
  65   execsql {
  66     SELECT DISTINCT b FROM collate5t1;
  67   }
  68 } {apple Apple banana {}}
  69 do_test collate5-1.3 {
  70   execsql {
  71     SELECT DISTINCT a, b FROM collate5t1;
  72   }
  73 } {a apple A Apple b banana n {}}
  74
  75 # Ticket #3376
  76 #
  77 do_test collate5-1.11 {
  78   execsql {
  79     CREATE TABLE tkt3376(a COLLATE nocase PRIMARY KEY);
  80     INSERT INTO tkt3376 VALUES('abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz');
  81     INSERT INTO tkt3376 VALUES('ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789');
  82     SELECT DISTINCT a FROM tkt3376;
  83   }
  84 } {abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789ABXYZ012234567890123456789}
  85 do_test collate5-1.12 {
  86   sqlite3 db2 :memory:
  87   db2 eval {
  88     PRAGMA encoding=UTF16le;
  89     CREATE TABLE tkt3376(a COLLATE nocase PRIMARY KEY);
  90     INSERT INTO tkt3376 VALUES('abc');
  91     INSERT INTO tkt3376 VALUES('ABX');
  92     SELECT DISTINCT a FROM tkt3376;
  93   }
  94 } {abc ABX}
  95 catch {db2 close}
  96
  97 # The remainder of this file tests compound SELECT statements.
  98 # Omit it if the library is compiled such that they are omitted.
  99 #
 100 ifcapable !compound {
 101   finish_test
 102   return
 103 }
 104
 105 #
 106 # Tests named collate5-2.* focus on UNION, EXCEPT and INTERSECT
 107 # queries that use user-defined collation sequences.
 108 #
 109 # collate5-2.1.* - UNION
 110 # collate5-2.2.* - INTERSECT
 111 # collate5-2.3.* - EXCEPT
 112 #
 113 do_test collate5-2.0 {
 114   execsql {
 115     CREATE TABLE collate5t2(a COLLATE text, b COLLATE nocase);
 116
 117     INSERT INTO collate5t2 VALUES('a', 'apple');
 118     INSERT INTO collate5t2 VALUES('A', 'apple');
 119     INSERT INTO collate5t2 VALUES('b', 'banana');
 120     INSERT INTO collate5t2 VALUES('B', 'Banana');
 121   }
 122 } {}
 123
 124 do_test collate5-2.1.1 {
 125   execsql {
 126     SELECT a FROM collate5t1 UNION select a FROM collate5t2;
 127   }
 128 } {A B N}
 129 do_test collate5-2.1.2 {
 130   execsql {
 131     SELECT a FROM collate5t2 UNION select a FROM collate5t1;
 132   }
 133 } {A B N a b n}
 134 do_test collate5-2.1.3 {
 135   execsql {
 136     SELECT a, b FROM collate5t1 UNION select a, b FROM collate5t2;
 137   }
 138 } {A Apple A apple B Banana b banana N {}}
 139 do_test collate5-2.1.4 {
 140   execsql {
 141     SELECT a, b FROM collate5t2 UNION select a, b FROM collate5t1;
 142   }
 143 } {A Apple B banana N {} a apple b banana n {}}
 144
 145 do_test collate5-2.2.1 {
 146   execsql {
 147     SELECT a FROM collate5t1 EXCEPT select a FROM collate5t2;
 148   }
 149 } {N}
 150 do_test collate5-2.2.2 {
 151   execsql {
 152     SELECT a FROM collate5t2 EXCEPT select a FROM collate5t1 WHERE a != 'a';
 153   }
 154 } {A a}
 155 do_test collate5-2.2.3 {
 156   execsql {
 157     SELECT a, b FROM collate5t1 EXCEPT select a, b FROM collate5t2;
 158   }
 159 } {A Apple N {}}
 160 do_test collate5-2.2.4 {
 161   execsql {
 162     SELECT a, b FROM collate5t2 EXCEPT select a, b FROM collate5t1
 163       where a != 'a';
 164   }
 165 } {A apple a apple}
 166
 167 do_test collate5-2.3.1 {
 168   execsql {
 169     SELECT a FROM collate5t1 INTERSECT select a FROM collate5t2;
 170   }
 171 } {A B}
 172 do_test collate5-2.3.2 {
 173   execsql {
 174     SELECT a FROM collate5t2 INTERSECT select a FROM collate5t1 WHERE a != 'a';
 175   }
 176 } {B b}
 177 do_test collate5-2.3.3 {
 178   execsql {
 179     SELECT a, b FROM collate5t1 INTERSECT select a, b FROM collate5t2;
 180   }
 181 } {a apple B banana}
 182 do_test collate5-2.3.4 {
 183   execsql {
 184     SELECT a, b FROM collate5t2 INTERSECT select a, b FROM collate5t1;
 185   }
 186 } {A apple B Banana a apple b banana}
 187
 188 #
 189 # This test ensures performs a UNION operation with a bunch of different
 190 # length records. The goal is to test that the logic that compares records
 191 # for the compound SELECT operators works with record lengths that lie
 192 # either side of the troublesome 256 and 65536 byte marks.
 193 #
 194 set ::lens [list \
 195   0 1 2 3 4 5 6 7 8 9 \
 196   240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 \
 197   257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 \
 198   65520 65521 65522 65523 65524 65525 65526 65527 65528 65529 65530 \
 199   65531 65532 65533 65534 65535 65536 65537 65538 65539 65540 65541 \
 200   65542 65543 65544 65545 65546 65547 65548 65549 65550 65551 ]
 201 do_test collate5-2.4.0 {
 202   execsql {
 203     BEGIN;
 204     CREATE TABLE collate5t3(a, b);
 205   }
 206   foreach ii $::lens {
 207     execsql "INSERT INTO collate5t3 VALUES($ii, '[string repeat a $ii]');"
 208   }
 209   expr [llength [execsql {
 210     COMMIT;
 211     SELECT * FROM collate5t3 UNION SELECT * FROM collate5t3;
 212   }]] / 2
 213 } [llength $::lens]
 214 do_test collate5-2.4.1 {
 215   execsql {DROP TABLE collate5t3;}
 216 } {}
 217 unset ::lens
 218
 219 #
 220 # These tests - collate5-3.* - focus on compound SELECT queries that
 221 # feature ORDER BY clauses.
 222 #
 223 do_test collate5-3.0 {
 224   execsql {
 225     SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2 ORDER BY 1;
 226   }
 227 } {/[aA] [aA] [aA] [aA] [bB] [bB] [bB] [bB] [nN] [nN]/}
 228 do_test collate5-3.1 {
 229   execsql {
 230     SELECT a FROM collate5t2 UNION ALL SELECT a FROM collate5t1 ORDER BY 1;
 231   }
 232 } {A A B B N a a b b n}
 233 do_test collate5-3.2 {
 234   execsql {
 235     SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2
 236       ORDER BY 1 COLLATE TEXT;
 237   }
 238 } {A A B B N a a b b n}
 239
 240 do_test collate5-3.3 {
 241   execsql {
 242     CREATE TABLE collate5t_cn(a COLLATE NUMERIC);
 243     CREATE TABLE collate5t_ct(a COLLATE TEXT);
 244     INSERT INTO collate5t_cn VALUES('1');
 245     INSERT INTO collate5t_cn VALUES('11');
 246     INSERT INTO collate5t_cn VALUES('101');
 247     INSERT INTO collate5t_ct SELECT * FROM collate5t_cn;
 248   }
 249 } {}
 250 do_test collate5-3.4 {
 251   execsql {
 252     SELECT a FROM collate5t_cn INTERSECT SELECT a FROM collate5t_ct ORDER BY 1;
 253   }
 254 } {1 11 101}
 255 do_test collate5-3.5 {
 256   execsql {
 257     SELECT a FROM collate5t_ct INTERSECT SELECT a FROM collate5t_cn ORDER BY 1;
 258   }
 259 } {1 101 11}
 260
 261 do_test collate5-3.20 {
 262   execsql {
 263     DROP TABLE collate5t_cn;
 264     DROP TABLE collate5t_ct;
 265     DROP TABLE collate5t1;
 266     DROP TABLE collate5t2;
 267   }
 268 } {}
 269
 270 do_test collate5-4.0 {
 271   execsql {
 272     CREATE TABLE collate5t1(a COLLATE NOCASE, b COLLATE NUMERIC);
 273     INSERT INTO collate5t1 VALUES('a', '1');
 274     INSERT INTO collate5t1 VALUES('A', '1.0');
 275     INSERT INTO collate5t1 VALUES('b', '2');
 276     INSERT INTO collate5t1 VALUES('B', '3');
 277   }
 278 } {}
 279 do_test collate5-4.1 {
 280   string tolower [execsql {
 281     SELECT a, count(*) FROM collate5t1 GROUP BY a;
 282   }]
 283 } {a 2 b 2}
 284 do_test collate5-4.2 {
 285   execsql {
 286     SELECT a, b, count(*) FROM collate5t1 GROUP BY a, b ORDER BY a, b;
 287   }
 288 } {/[aA] 1(.0)? 2 [bB] 2 1 [bB] 3 1/}
 289 do_test collate5-4.3 {
 290   execsql {
 291     DROP TABLE collate5t1;
 292   }
 293 } {}
 294
 295 #-------------------------------------------------------------------------
 296 reset_db
 297
 298 do_execsql_test 5.0 {
 299   CREATE TABLE t1(a, b COLLATE nocase);
 300   CREATE TABLE t2(c, d);
 301   INSERT INTO t2 VALUES(1, 'bbb');
 302 }
 303 do_execsql_test 5.1 {
 304   SELECT * FROM (
 305       SELECT a, b FROM t1 UNION ALL SELECT c, d FROM t2
 306   ) WHERE b='BbB';
 307 } {1 bbb}
 308
 309 reset_db
 310 do_execsql_test 5.2 {
 311   CREATE TABLE t1(a,b,c COLLATE NOCASE);
 312   INSERT INTO t1 VALUES(NULL,'C','c');
 313   CREATE VIEW v2 AS
 314     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,b FROM t1
 315     WHERE 'eT"3qRkL+oJMJjQ9z0'>=b
 316     ORDER BY a,b,c;
 317 }
 318
 319 do_execsql_test 5.3 {
 320   SELECT * FROM v2;
 321 } { {} C c }
 322
 323 do_execsql_test 5.4 {
 324   SELECT * FROM v2 WHERE c='c';
 325 } { {} C c }
 326
 327
 328 finish_test