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