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.6 2007/11/12 15:29:19 danielk1977 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 # The remainder of this file tests compound SELECT statements.

    73 # Omit it if the library is compiled such that they are omitted.

    74 #

    75 ifcapable !compound {

    76   finish_test

    77   return

    78 }

    79 

    80 #

    81 # Tests named collate5-2.* focus on UNION, EXCEPT and INTERSECT

    82 # queries that use user-defined collation sequences.

    83 #

    84 # collate5-2.1.* - UNION

    85 # collate5-2.2.* - INTERSECT

    86 # collate5-2.3.* - EXCEPT

    87 #

    88 do_test collate5-2.0 {

    89   execsql {

    90     CREATE TABLE collate5t2(a COLLATE text, b COLLATE nocase);

    91 

    92     INSERT INTO collate5t2 VALUES('a', 'apple');

    93     INSERT INTO collate5t2 VALUES('A', 'apple');

    94     INSERT INTO collate5t2 VALUES('b', 'banana');

    95     INSERT INTO collate5t2 VALUES('B', 'Banana');

    96   } 

    97 } {}

    98 

    99 do_test collate5-2.1.1 {

   100   execsql {

   101     SELECT a FROM collate5t1 UNION select a FROM collate5t2;

   102   }

   103 } {A B N}

   104 do_test collate5-2.1.2 {

   105   execsql {

   106     SELECT a FROM collate5t2 UNION select a FROM collate5t1;

   107   }

   108 } {A B N a b n}

   109 do_test collate5-2.1.3 {

   110   execsql {

   111     SELECT a, b FROM collate5t1 UNION select a, b FROM collate5t2;

   112   }

   113 } {A Apple A apple B Banana b banana N {}}

   114 do_test collate5-2.1.4 {

   115   execsql {

   116     SELECT a, b FROM collate5t2 UNION select a, b FROM collate5t1;

   117   }

   118 } {A Apple B banana N {} a apple b banana n {}}

   119 

   120 do_test collate5-2.2.1 {

   121   execsql {

   122     SELECT a FROM collate5t1 EXCEPT select a FROM collate5t2;

   123   }

   124 } {N}

   125 do_test collate5-2.2.2 {

   126   execsql {

   127     SELECT a FROM collate5t2 EXCEPT select a FROM collate5t1 WHERE a != 'a';

   128   }

   129 } {A a}

   130 do_test collate5-2.2.3 {

   131   execsql {

   132     SELECT a, b FROM collate5t1 EXCEPT select a, b FROM collate5t2;

   133   }

   134 } {A Apple N {}}

   135 do_test collate5-2.2.4 {

   136   execsql {

   137     SELECT a, b FROM collate5t2 EXCEPT select a, b FROM collate5t1 

   138       where a != 'a';

   139   }

   140 } {A apple a apple}

   141 

   142 do_test collate5-2.3.1 {

   143   execsql {

   144     SELECT a FROM collate5t1 INTERSECT select a FROM collate5t2;

   145   }

   146 } {A B}

   147 do_test collate5-2.3.2 {

   148   execsql {

   149     SELECT a FROM collate5t2 INTERSECT select a FROM collate5t1 WHERE a != 'a';

   150   }

   151 } {B b}

   152 do_test collate5-2.3.3 {

   153   execsql {

   154     SELECT a, b FROM collate5t1 INTERSECT select a, b FROM collate5t2;

   155   }

   156 } {a apple B banana}

   157 do_test collate5-2.3.4 {

   158   execsql {

   159     SELECT a, b FROM collate5t2 INTERSECT select a, b FROM collate5t1;

   160   }

   161 } {A apple B Banana a apple b banana}

   162 

   163 #

   164 # This test ensures performs a UNION operation with a bunch of different

   165 # length records. The goal is to test that the logic that compares records

   166 # for the compound SELECT operators works with record lengths that lie

   167 # either side of the troublesome 256 and 65536 byte marks.

   168 #

   169 set ::lens [list \

   170   0 1 2 3 4 5 6 7 8 9 \

   171   240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 \

   172   257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 \

   173   65520 65521 65522 65523 65524 65525 65526 65527 65528 65529 65530 \

   174   65531 65532 65533 65534 65535 65536 65537 65538 65539 65540 65541 \

   175   65542 65543 65544 65545 65546 65547 65548 65549 65550 65551 ]

   176 do_test collate5-2.4.0 {

   177   execsql {

   178     BEGIN;

   179     CREATE TABLE collate5t3(a, b);

   180   }

   181   foreach ii $::lens { 

   182     execsql "INSERT INTO collate5t3 VALUES($ii, '[string repeat a $ii]');"

   183   }

   184   expr [llength [execsql {

   185     COMMIT;

   186     SELECT * FROM collate5t3 UNION SELECT * FROM collate5t3;

   187   }]] / 2

   188 } [llength $::lens]

   189 do_test collate5-2.4.1 {

   190   execsql {DROP TABLE collate5t3;}

   191 } {}

   192 unset ::lens

   193 

   194 #

   195 # These tests - collate5-3.* - focus on compound SELECT queries that 

   196 # feature ORDER BY clauses.

   197 #

   198 do_test collate5-3.0 {

   199   execsql {

   200     SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2 ORDER BY 1;

   201   }

   202 } {a A a A b B b B n N}

   203 do_test collate5-3.1 {

   204   execsql {

   205     SELECT a FROM collate5t2 UNION ALL SELECT a FROM collate5t1 ORDER BY 1;

   206   }

   207 } {A A B B N a a b b n}

   208 do_test collate5-3.2 {

   209   execsql {

   210     SELECT a FROM collate5t1 UNION ALL SELECT a FROM collate5t2 

   211       ORDER BY 1 COLLATE TEXT;

   212   }

   213 } {A A B B N a a b b n}

   214 

   215 do_test collate5-3.3 {

   216   execsql {

   217     CREATE TABLE collate5t_cn(a COLLATE NUMERIC);

   218     CREATE TABLE collate5t_ct(a COLLATE TEXT);

   219     INSERT INTO collate5t_cn VALUES('1');

   220     INSERT INTO collate5t_cn VALUES('11');

   221     INSERT INTO collate5t_cn VALUES('101');

   222     INSERT INTO collate5t_ct SELECT * FROM collate5t_cn;

   223   }

   224 } {}

   225 do_test collate5-3.4 {

   226   execsql {

   227     SELECT a FROM collate5t_cn INTERSECT SELECT a FROM collate5t_ct ORDER BY 1;

   228   }

   229 } {1 11 101}

   230 do_test collate5-3.5 {

   231   execsql {

   232     SELECT a FROM collate5t_ct INTERSECT SELECT a FROM collate5t_cn ORDER BY 1;

   233   }

   234 } {1 101 11}

   235 

   236 do_test collate5-3.20 {

   237   execsql {

   238     DROP TABLE collate5t_cn;

   239     DROP TABLE collate5t_ct;

   240     DROP TABLE collate5t1;

   241     DROP TABLE collate5t2;

   242   }

   243 } {}

   244 

   245 do_test collate5-4.0 {

   246   execsql {

   247     CREATE TABLE collate5t1(a COLLATE NOCASE, b COLLATE NUMERIC); 

   248     INSERT INTO collate5t1 VALUES('a', '1');

   249     INSERT INTO collate5t1 VALUES('A', '1.0');

   250     INSERT INTO collate5t1 VALUES('b', '2');

   251     INSERT INTO collate5t1 VALUES('B', '3');

   252   }

   253 } {}

   254 do_test collate5-4.1 {

   255   string tolower [execsql {

   256     SELECT a, count(*) FROM collate5t1 GROUP BY a;

   257   }]

   258 } {a 2 b 2}

   259 do_test collate5-4.2 {

   260   execsql {

   261     SELECT a, b, count(*) FROM collate5t1 GROUP BY a, b ORDER BY a, b;

   262   }

   263 } {A 1.0 2 b 2 1 B 3 1}

   264 do_test collate5-4.3 {

   265   execsql {

   266     DROP TABLE collate5t1;

   267   }

   268 } {}

   269 

   270 finish_test