AryaWu/sqlite
0
1#2# 2001 September 153#4# The author disclaims copyright to this source code. In place of5# 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. The13# focus of this file is testing DISTINCT, UNION, INTERSECT and EXCEPT14# SELECT statements that use user-defined collation sequences. Also15# 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 19set testdir [file dirname $argv0]20source $testdir/tester.tcl21 22set testprefix collate523 24 25#26# Tests are organised as follows:27# collate5-1.* - DISTINCT28# collate5-2.* - Compound SELECT29# collate5-3.* - ORDER BY on compound SELECT30# collate5-4.* - GROUP BY31# collate5-5.* - Collation sequence cases32 33# Create the collation sequence 'TEXT', purely for asthetic reasons. The34# test cases in this script could just as easily use BINARY.35db collate TEXT [list string compare]36 37# Mimic the SQLite 2 collation type NUMERIC.38db collate numeric numeric_collate39proc 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#47do_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} {}59do_test collate5-1.1 {60 execsql {61 SELECT DISTINCT a FROM collate5t1;62 }63} {a b n}64do_test collate5-1.2 {65 execsql {66 SELECT DISTINCT b FROM collate5t1;67 }68} {apple Apple banana {}}69do_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 #337676#77do_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}85do_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}95catch {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#100ifcapable !compound {101 finish_test102 return103}104 105#106# Tests named collate5-2.* focus on UNION, EXCEPT and INTERSECT107# queries that use user-defined collation sequences.108#109# collate5-2.1.* - UNION110# collate5-2.2.* - INTERSECT111# collate5-2.3.* - EXCEPT112#113do_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 124do_test collate5-2.1.1 {125 execsql {126 SELECT a FROM collate5t1 UNION select a FROM collate5t2;127 }128} {A B N}129do_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}134do_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 {}}139do_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 145do_test collate5-2.2.1 {146 execsql {147 SELECT a FROM collate5t1 EXCEPT select a FROM collate5t2;148 }149} {N}150do_test collate5-2.2.2 {151 execsql {152 SELECT a FROM collate5t2 EXCEPT select a FROM collate5t1 WHERE a != 'a';153 }154} {A a}155do_test collate5-2.2.3 {156 execsql {157 SELECT a, b FROM collate5t1 EXCEPT select a, b FROM collate5t2;158 }159} {A Apple N {}}160do_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 167do_test collate5-2.3.1 {168 execsql {169 SELECT a FROM collate5t1 INTERSECT select a FROM collate5t2;170 }171} {A B}172do_test collate5-2.3.2 {173 execsql {174 SELECT a FROM collate5t2 INTERSECT select a FROM collate5t1 WHERE a != 'a';175 }176} {B b}177do_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}182do_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 different190# length records. The goal is to test that the logic that compares records191# for the compound SELECT operators works with record lengths that lie192# either side of the troublesome 256 and 65536 byte marks.193#194set ::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 ]201do_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 }]] / 2213} [llength $::lens]214do_test collate5-2.4.1 {215 execsql {DROP TABLE collate5t3;}216} {}217unset ::lens218 219#220# These tests - collate5-3.* - focus on compound SELECT queries that 221# feature ORDER BY clauses.222#223do_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]/}228do_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}233do_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 240do_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} {}250do_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}255do_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 261do_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 270do_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} {}279do_test collate5-4.1 {280 string tolower [execsql {281 SELECT a, count(*) FROM collate5t1 GROUP BY a;282 }]283} {a 2 b 2}284do_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/}289do_test collate5-4.3 {290 execsql {291 DROP TABLE collate5t1;292 }293} {}294 295#-------------------------------------------------------------------------296reset_db297 298do_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}303do_execsql_test 5.1 {304 SELECT * FROM (305 SELECT a, b FROM t1 UNION ALL SELECT c, d FROM t2306 ) WHERE b='BbB';307} {1 bbb}308 309reset_db310do_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 AS314 SELECT a,b,c FROM t1 INTERSECT SELECT a,b,b FROM t1315 WHERE 'eT"3qRkL+oJMJjQ9z0'>=b316 ORDER BY a,b,c;317}318 319do_execsql_test 5.3 {320 SELECT * FROM v2;321} { {} C c }322 323do_execsql_test 5.4 {324 SELECT * FROM v2 WHERE c='c';325} { {} C c }326 327 328finish_test329