#

 # 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.6 2007/11/12 15:29:19 danielk1977 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