# # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #************************************************************************* # This file implements regression tests for SQLite library. The # focus of this file is testing DISTINCT, UNION, INTERSECT and EXCEPT # SELECT statements that use user-defined collation sequences. Also # GROUP BY clauses that use user-defined collation sequences. # # $Id: collate5.test,v 1.5 2005/09/07 22:48:16 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # # Tests are organised as follows: # collate5-1.* - DISTINCT # collate5-2.* - Compound SELECT # collate5-3.* - ORDER BY on compound SELECT # collate5-4.* - GROUP BY # Create the collation sequence 'TEXT', purely for asthetic reasons. The # test cases in this script could just as easily use BINARY. db collate TEXT [list string compare] # Mimic the SQLite 2 collation type NUMERIC. db collate numeric numeric_collate proc numeric_collate {lhs rhs} { if {$lhs == $rhs} {return 0} return [expr ($lhs>$rhs)?1:-1] } # # These tests - collate5-1.* - focus on the DISTINCT keyword. # do_test collate5-1.0 { execsql { CREATE TABLE collate5t1(a COLLATE nocase, b COLLATE text); INSERT INTO collate5t1 VALUES('a', 'apple'); INSERT INTO collate5t1 VALUES('A', 'Apple'); INSERT INTO collate5t1 VALUES('b', 'banana'); INSERT INTO collate5t1 VALUES('B', 'banana'); INSERT INTO collate5t1 VALUES('n', NULL); INSERT INTO collate5t1 VALUES('N', NULL); } } {} do_test collate5-1.1 { execsql { SELECT DISTINCT a FROM collate5t1; } } {a b n} do_test collate5-1.2 { execsql { SELECT DISTINCT b FROM collate5t1; } } {apple Apple banana {}} do_test collate5-1.3 { execsql { SELECT DISTINCT a, b FROM collate5t1; } } {a apple A Apple b banana n {}} # The remainder of this file tests compound SELECT statements. # Omit it if the library is compiled such that they are omitted. # ifcapable !compound { finish_test return } # # Tests named collate5-2.* focus on UNION, EXCEPT and INTERSECT # queries that use user-defined collation sequences. # # collate5-2.1.* - UNION # collate5-2.2.* - INTERSECT # collate5-2.3.* - EXCEPT # do_test collate5-2.0 { execsql { CREATE TABLE collate5t2(a COLLATE text, b COLLATE nocase); INSERT INTO collate5t2 VALUES('a', 'apple'); INSERT INTO collate5t2 VALUES('A', 'apple'); INSERT INTO collate5t2 VALUES('b', 'banana'); INSERT INTO collate5t2 VALUES('B', 'Banana'); } } {} do_test collate5-2.1.1 { execsql { SELECT a FROM collate5t1 UNION select a FROM collate5t2; } } {A B N} do_test collate5-2.1.2 { execsql { SELECT a FROM collate5t2 UNION select a FROM collate5t1; } } {A B N a b n} do_test collate5-2.1.3 { execsql { SELECT a, b FROM collate5t1 UNION select a, b FROM collate5t2; } } {A Apple A apple B Banana b banana N {}} do_test collate5-2.1.4 { execsql { SELECT a, b FROM collate5t2 UNION select a, b FROM collate5t1; } } {A Apple B banana N {} a apple b banana n {}} do_test collate5-2.2.1 { execsql { SELECT a FROM collate5t1 EXCEPT select a FROM collate5t2; } } {N} do_test collate5-2.2.2 { execsql { SELECT a FROM collate5t2 EXCEPT select a FROM collate5t1 WHERE a != 'a'; } } {A a} do_test collate5-2.2.3 { execsql { SELECT a, b FROM collate5t1 EXCEPT select a, b FROM collate5t2; } } {A Apple N {}} do_test collate5-2.2.4 { execsql { SELECT a, b FROM collate5t2 EXCEPT select a, b FROM collate5t1 where a != 'a'; } } {A apple a apple} do_test collate5-2.3.1 { execsql { SELECT a FROM collate5t1 INTERSECT select a FROM collate5t2; } } {A B} do_test collate5-2.3.2 { execsql { SELECT a FROM collate5t2 INTERSECT select a FROM collate5t1 WHERE a != 'a'; } } {B b} do_test collate5-2.3.3 { execsql { SELECT a, b FROM collate5t1 INTERSECT select a, b FROM collate5t2; } } {a apple B banana} do_test collate5-2.3.4 { execsql { SELECT a, b FROM collate5t2 INTERSECT select a, b FROM collate5t1; } } {A apple B Banana a apple b banana} # # This test ensures performs a UNION operation with a bunch of different # length records. The goal is to test that the logic that compares records # for the compound SELECT operators works with record lengths that lie # either side of the troublesome 256 and 65536 byte marks. # set ::lens [list \ 0 1 2 3 4 5 6 7 8 9 \ 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 \ 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 \ 65520 65521 65522 65523 65524 65525 65526 65527 65528 65529 65530 \ 65531 65532 65533 65534 65535 65536 65537 65538 65539 65540 65541 \ 65542 65543 65544 65545 65546 65547 65548 65549 65550 65551 ] do_test collate5-2.4.0 { execsql { BEGIN; CREATE TABLE collate5t3(a, b); } foreach ii $::lens { execsql "INSERT INTO collate5t3 VALUES($ii, '[string repeat a $ii]');" } expr [llength [execsql { COMMIT; SELECT * FROM collate5t3 UNION SELECT * FROM collate5t3; }]] / 2 } [llength $::lens] do_test collate5-2.4.1 { execsql {DROP TABLE collate5t3;} } {} unset ::lens # # These tests - collate5-3.* - focus on compound SELECT queries that # feature ORDER BY clauses. # do_test collate5-3.0 { execsql { SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2 ORDER BY 1; } } {a A a A b B b B n N} do_test collate5-3.1 { execsql { SELECT a FROM collate5t2 UNION ALL SELECT a FROM collate5t1 ORDER BY 1; } } {A A B B N a a b b n} do_test collate5-3.2 { execsql { SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2 ORDER BY 1 COLLATE TEXT; } } {A A B B N a a b b n} do_test collate5-3.3 { execsql { CREATE TABLE collate5t_cn(a COLLATE NUMERIC); CREATE TABLE collate5t_ct(a COLLATE TEXT); INSERT INTO collate5t_cn VALUES('1'); INSERT INTO collate5t_cn VALUES('11'); INSERT INTO collate5t_cn VALUES('101'); INSERT INTO collate5t_ct SELECT * FROM collate5t_cn; } } {} do_test collate5-3.4 { execsql { SELECT a FROM collate5t_cn INTERSECT SELECT a FROM collate5t_ct ORDER BY 1; } } {1 11 101} do_test collate5-3.5 { execsql { SELECT a FROM collate5t_ct INTERSECT SELECT a FROM collate5t_cn ORDER BY 1; } } {1 101 11} do_test collate5-3.20 { execsql { DROP TABLE collate5t_cn; DROP TABLE collate5t_ct; DROP TABLE collate5t1; DROP TABLE collate5t2; } } {} do_test collate5-4.0 { execsql { CREATE TABLE collate5t1(a COLLATE NOCASE, b COLLATE NUMERIC); INSERT INTO collate5t1 VALUES('a', '1'); INSERT INTO collate5t1 VALUES('A', '1.0'); INSERT INTO collate5t1 VALUES('b', '2'); INSERT INTO collate5t1 VALUES('B', '3'); } } {} do_test collate5-4.1 { string tolower [execsql { SELECT a, count(*) FROM collate5t1 GROUP BY a; }] } {a 2 b 2} do_test collate5-4.2 { execsql { SELECT a, b, count(*) FROM collate5t1 GROUP BY a, b ORDER BY a, b; } } {A 1.0 2 b 2 1 B 3 1} do_test collate5-4.3 { execsql { DROP TABLE collate5t1; } } {} finish_test