## 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_collateproc 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