1 # 2008 June 24

     2 #

     3 # The author disclaims copyright to this source code.  In place of

     4 # a legal notice, here is a blessing:

     5 #

     6 #    May you do good and not evil.

     7 #    May you find forgiveness for yourself and forgive others.

     8 #    May you share freely, never taking more than you give.

     9 #

    10 #***********************************************************************

    11 # This file implements regression tests for SQLite library. 

    12 #

    13 # The focus of this file is testing the compound-SELECT merge

    14 # optimization.  Or, in other words, making sure that all

    15 # possible combinations of UNION, UNION ALL, EXCEPT, and

    16 # INTERSECT work together with an ORDER BY clause (with or w/o

    17 # explicit sort order and explicit collating secquites) and

    18 # with and without optional LIMIT and OFFSET clauses.

    19 #

    20 # $Id: selectA.test,v 1.6 2008/08/21 14:24:29 drh Exp $

    21 

    22 set testdir [file dirname $argv0]

    23 source $testdir/tester.tcl

    24 

    25 ifcapable !compound {

    26   finish_test

    27   return

    28 }

    29 

    30 do_test selectA-1.0 {

    31   execsql {

    32     CREATE TABLE t1(a,b,c COLLATE NOCASE);

    33     INSERT INTO t1 VALUES(1,'a','a');

    34     INSERT INTO t1 VALUES(9.9, 'b', 'B');

    35     INSERT INTO t1 VALUES(NULL, 'C', 'c');

    36     INSERT INTO t1 VALUES('hello', 'd', 'D');

    37     INSERT INTO t1 VALUES(x'616263', 'e', 'e');

    38     SELECT * FROM t1;

    39   }

    40 } {1 a a 9.9 b B {} C c hello d D abc e e}

    41 do_test selectA-1.1 {

    42   execsql {

    43     CREATE TABLE t2(x,y,z COLLATE NOCASE);

    44     INSERT INTO t2 VALUES(NULL,'U','u');

    45     INSERT INTO t2 VALUES('mad', 'Z', 'z');

    46     INSERT INTO t2 VALUES(x'68617265', 'm', 'M');

    47     INSERT INTO t2 VALUES(5.2e6, 'X', 'x');

    48     INSERT INTO t2 VALUES(-23, 'Y', 'y');

    49     SELECT * FROM t2;

    50   }

    51 } {{} U u mad Z z hare m M 5200000.0 X x -23 Y y}

    52 do_test selectA-1.2 {

    53   execsql {

    54     CREATE TABLE t3(a,b,c COLLATE NOCASE);

    55     INSERT INTO t3 SELECT * FROM t1;

    56     INSERT INTO t3 SELECT * FROM t2;

    57     INSERT INTO t3 SELECT * FROM t1;

    58     INSERT INTO t3 SELECT * FROM t2;

    59     INSERT INTO t3 SELECT * FROM t1;

    60     INSERT INTO t3 SELECT * FROM t2;

    61     SELECT count(*) FROM t3;

    62   }

    63 } {30}

    64 

    65 do_test selectA-2.1 {

    66   execsql {

    67     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

    68     ORDER BY a,b,c

    69   }

    70 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

    71 do_test selectA-2.1.1 {   # Ticket #3314

    72   execsql {

    73     SELECT t1.a, t1.b, t1.c FROM t1 UNION ALL SELECT x,y,z FROM t2

    74     ORDER BY a,b,c

    75   }

    76 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

    77 do_test selectA-2.1.2 {   # Ticket #3314

    78   execsql {

    79     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

    80     ORDER BY t1.a, t1.b, t1.c

    81   }

    82 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

    83 do_test selectA-2.2 {

    84   execsql {

    85     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

    86     ORDER BY a DESC,b,c

    87   }

    88 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}

    89 do_test selectA-2.3 {

    90   execsql {

    91     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

    92     ORDER BY a,c,b

    93   }

    94 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

    95 do_test selectA-2.4 {

    96   execsql {

    97     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

    98     ORDER BY b,a,c

    99   }

   100 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}

   101 do_test selectA-2.5 {

   102   execsql {

   103     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

   104     ORDER BY b COLLATE NOCASE,a,c

   105   }

   106 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   107 do_test selectA-2.6 {

   108   execsql {

   109     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

   110     ORDER BY b COLLATE NOCASE DESC,a,c

   111   }

   112 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   113 do_test selectA-2.7 {

   114   execsql {

   115     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

   116     ORDER BY c,b,a

   117   }

   118 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   119 do_test selectA-2.8 {

   120   execsql {

   121     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

   122     ORDER BY c,a,b

   123   }

   124 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   125 do_test selectA-2.9 {

   126   execsql {

   127     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

   128     ORDER BY c DESC,a,b

   129   }

   130 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   131 do_test selectA-2.10 {

   132   execsql {

   133     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

   134     ORDER BY c COLLATE BINARY DESC,a,b

   135   }

   136 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}

   137 do_test selectA-2.11 {

   138   execsql {

   139     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   140     ORDER BY a,b,c

   141   }

   142 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   143 do_test selectA-2.12 {

   144   execsql {

   145     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   146     ORDER BY a DESC,b,c

   147   }

   148 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}

   149 do_test selectA-2.13 {

   150   execsql {

   151     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   152     ORDER BY a,c,b

   153   }

   154 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   155 do_test selectA-2.14 {

   156   execsql {

   157     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   158     ORDER BY b,a,c

   159   }

   160 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}

   161 do_test selectA-2.15 {

   162   execsql {

   163     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   164     ORDER BY b COLLATE NOCASE,a,c

   165   }

   166 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   167 do_test selectA-2.16 {

   168   execsql {

   169     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   170     ORDER BY b COLLATE NOCASE DESC,a,c

   171   }

   172 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   173 do_test selectA-2.17 {

   174   execsql {

   175     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   176     ORDER BY c,b,a

   177   }

   178 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   179 do_test selectA-2.18 {

   180   execsql {

   181     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   182     ORDER BY c,a,b

   183   }

   184 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   185 do_test selectA-2.19 {

   186   execsql {

   187     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   188     ORDER BY c DESC,a,b

   189   }

   190 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   191 do_test selectA-2.20 {

   192   execsql {

   193     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   194     ORDER BY c COLLATE BINARY DESC,a,b

   195   }

   196 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}

   197 do_test selectA-2.21 {

   198   execsql {

   199     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   200     ORDER BY a,b,c

   201   }

   202 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   203 do_test selectA-2.22 {

   204   execsql {

   205     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   206     ORDER BY a DESC,b,c

   207   }

   208 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}

   209 do_test selectA-2.23 {

   210   execsql {

   211     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   212     ORDER BY a,c,b

   213   }

   214 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   215 do_test selectA-2.24 {

   216   execsql {

   217     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   218     ORDER BY b,a,c

   219   }

   220 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}

   221 do_test selectA-2.25 {

   222   execsql {

   223     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   224     ORDER BY b COLLATE NOCASE,a,c

   225   }

   226 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   227 do_test selectA-2.26 {

   228   execsql {

   229     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   230     ORDER BY b COLLATE NOCASE DESC,a,c

   231   }

   232 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   233 do_test selectA-2.27 {

   234   execsql {

   235     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   236     ORDER BY c,b,a

   237   }

   238 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   239 do_test selectA-2.28 {

   240   execsql {

   241     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   242     ORDER BY c,a,b

   243   }

   244 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   245 do_test selectA-2.29 {

   246   execsql {

   247     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   248     ORDER BY c DESC,a,b

   249   }

   250 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   251 do_test selectA-2.30 {

   252   execsql {

   253     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   254     ORDER BY c COLLATE BINARY DESC,a,b

   255   }

   256 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}

   257 do_test selectA-2.31 {

   258   execsql {

   259     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   260     ORDER BY a,b,c

   261   }

   262 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   263 do_test selectA-2.32 {

   264   execsql {

   265     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   266     ORDER BY a DESC,b,c

   267   }

   268 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}

   269 do_test selectA-2.33 {

   270   execsql {

   271     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   272     ORDER BY a,c,b

   273   }

   274 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   275 do_test selectA-2.34 {

   276   execsql {

   277     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   278     ORDER BY b,a,c

   279   }

   280 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}

   281 do_test selectA-2.35 {

   282   execsql {

   283     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   284     ORDER BY b COLLATE NOCASE,a,c

   285   }

   286 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   287 do_test selectA-2.36 {

   288   execsql {

   289     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   290     ORDER BY b COLLATE NOCASE DESC,a,c

   291   }

   292 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   293 do_test selectA-2.37 {

   294   execsql {

   295     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   296     ORDER BY c,b,a

   297   }

   298 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   299 do_test selectA-2.38 {

   300   execsql {

   301     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   302     ORDER BY c,a,b

   303   }

   304 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   305 do_test selectA-2.39 {

   306   execsql {

   307     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   308     ORDER BY c DESC,a,b

   309   }

   310 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   311 do_test selectA-2.40 {

   312   execsql {

   313     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   314     ORDER BY c COLLATE BINARY DESC,a,b

   315   }

   316 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}

   317 do_test selectA-2.41 {

   318   execsql {

   319     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'

   320     ORDER BY a,b,c

   321   }

   322 } {{} C c 1 a a 9.9 b B}

   323 do_test selectA-2.42 {

   324   execsql {

   325     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'

   326     ORDER BY a,b,c

   327   }

   328 } {hello d D abc e e}

   329 do_test selectA-2.43 {

   330   execsql {

   331     SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1

   332     ORDER BY a,b,c

   333   }

   334 } {hello d D abc e e}

   335 do_test selectA-2.44 {

   336   execsql {

   337     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'

   338     ORDER BY a,b,c

   339   }

   340 } {hello d D abc e e}

   341 do_test selectA-2.45 {

   342   execsql {

   343     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

   344     ORDER BY a,b,c

   345   }

   346 } {{} C c 1 a a 9.9 b B}

   347 do_test selectA-2.46 {

   348   execsql {

   349     SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1

   350     ORDER BY a,b,c

   351   }

   352 } {{} C c 1 a a 9.9 b B}

   353 do_test selectA-2.47 {

   354   execsql {

   355     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'

   356     ORDER BY a DESC

   357   }

   358 } {9.9 b B 1 a a {} C c}

   359 do_test selectA-2.48 {

   360   execsql {

   361     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'

   362     ORDER BY a DESC

   363   }

   364 } {abc e e hello d D}

   365 do_test selectA-2.49 {

   366   execsql {

   367     SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1

   368     ORDER BY a DESC

   369   }

   370 } {abc e e hello d D}

   371 do_test selectA-2.50 {

   372   execsql {

   373     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'

   374     ORDER BY a DESC

   375   }

   376 } {abc e e hello d D}

   377 do_test selectA-2.51 {

   378   execsql {

   379     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

   380     ORDER BY a DESC

   381   }

   382 } {9.9 b B 1 a a {} C c}

   383 do_test selectA-2.52 {

   384   execsql {

   385     SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1

   386     ORDER BY a DESC

   387   }

   388 } {9.9 b B 1 a a {} C c}

   389 do_test selectA-2.53 {

   390   execsql {

   391     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'

   392     ORDER BY b, a DESC

   393   }

   394 } {{} C c 1 a a 9.9 b B}

   395 do_test selectA-2.54 {

   396   execsql {

   397     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'

   398     ORDER BY b

   399   }

   400 } {hello d D abc e e}

   401 do_test selectA-2.55 {

   402   execsql {

   403     SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1

   404     ORDER BY b DESC, c

   405   }

   406 } {abc e e hello d D}

   407 do_test selectA-2.56 {

   408   execsql {

   409     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'

   410     ORDER BY b, c DESC, a

   411   }

   412 } {hello d D abc e e}

   413 do_test selectA-2.57 {

   414   execsql {

   415     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

   416     ORDER BY b COLLATE NOCASE

   417   }

   418 } {1 a a 9.9 b B {} C c}

   419 do_test selectA-2.58 {

   420   execsql {

   421     SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1

   422     ORDER BY b

   423   }

   424 } {{} C c 1 a a 9.9 b B}

   425 do_test selectA-2.59 {

   426   execsql {

   427     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'

   428     ORDER BY c, a DESC

   429   }

   430 } {1 a a 9.9 b B {} C c}

   431 do_test selectA-2.60 {

   432   execsql {

   433     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'

   434     ORDER BY c

   435   }

   436 } {hello d D abc e e}

   437 do_test selectA-2.61 {

   438   execsql {

   439     SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1

   440     ORDER BY c COLLATE BINARY, b DESC, c, a, b, c, a, b, c

   441   }

   442 } {hello d D abc e e}

   443 do_test selectA-2.62 {

   444   execsql {

   445     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'

   446     ORDER BY c DESC, a

   447   }

   448 } {abc e e hello d D}

   449 do_test selectA-2.63 {

   450   execsql {

   451     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

   452     ORDER BY c COLLATE NOCASE

   453   }

   454 } {1 a a 9.9 b B {} C c}

   455 do_test selectA-2.64 {

   456   execsql {

   457     SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1

   458     ORDER BY c

   459   }

   460 } {1 a a 9.9 b B {} C c}

   461 do_test selectA-2.65 {

   462   execsql {

   463     SELECT a,b,c FROM t3 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

   464     ORDER BY c COLLATE NOCASE

   465   }

   466 } {1 a a 9.9 b B {} C c}

   467 do_test selectA-2.66 {

   468   execsql {

   469     SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t3

   470     ORDER BY c

   471   }

   472 } {1 a a 9.9 b B {} C c}

   473 do_test selectA-2.67 {

   474   execsql {

   475     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t3 WHERE b<'d'

   476     ORDER BY c DESC, a

   477   }

   478 } {abc e e hello d D}

   479 do_test selectA-2.68 {

   480   execsql {

   481     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'

   482     INTERSECT SELECT a,b,c FROM t3

   483     EXCEPT SELECT b,c,a FROM t3

   484     ORDER BY c DESC, a

   485   }

   486 } {abc e e hello d D}

   487 do_test selectA-2.69 {

   488   execsql {

   489     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

   490     INTERSECT SELECT a,b,c FROM t3

   491     EXCEPT SELECT b,c,a FROM t3

   492     ORDER BY c COLLATE NOCASE

   493   }

   494 } {1 a a 9.9 b B {} C c}

   495 do_test selectA-2.70 {

   496   execsql {

   497     SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1

   498     INTERSECT SELECT a,b,c FROM t3

   499     EXCEPT SELECT b,c,a FROM t3

   500     ORDER BY c

   501   }

   502 } {1 a a 9.9 b B {} C c}

   503 do_test selectA-2.71 {

   504   execsql {

   505     SELECT a,b,c FROM t1 WHERE b<'d'

   506     INTERSECT SELECT a,b,c FROM t1

   507     INTERSECT SELECT a,b,c FROM t3

   508     EXCEPT SELECT b,c,a FROM t3

   509     INTERSECT SELECT a,b,c FROM t1

   510     EXCEPT SELECT x,y,z FROM t2

   511     INTERSECT SELECT a,b,c FROM t3

   512     EXCEPT SELECT y,x,z FROM t2

   513     INTERSECT SELECT a,b,c FROM t1

   514     EXCEPT SELECT c,b,a FROM t3

   515     ORDER BY c

   516   }

   517 } {1 a a 9.9 b B {} C c}

   518 do_test selectA-2.72 {

   519   execsql {

   520     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2

   521     ORDER BY a,b,c

   522   }

   523 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   524 do_test selectA-2.73 {

   525   execsql {

   526     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2

   527     ORDER BY a DESC,b,c

   528   }

   529 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}

   530 do_test selectA-2.74 {

   531   execsql {

   532     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2

   533     ORDER BY a,c,b

   534   }

   535 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   536 do_test selectA-2.75 {

   537   execsql {

   538     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2

   539     ORDER BY b,a,c

   540   }

   541 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}

   542 do_test selectA-2.76 {

   543   execsql {

   544     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2

   545     ORDER BY b COLLATE NOCASE,a,c

   546   }

   547 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   548 do_test selectA-2.77 {

   549   execsql {

   550     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2

   551     ORDER BY b COLLATE NOCASE DESC,a,c

   552   }

   553 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   554 do_test selectA-2.78 {

   555   execsql {

   556     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2

   557     ORDER BY c,b,a

   558   }

   559 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   560 do_test selectA-2.79 {

   561   execsql {

   562     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2

   563     ORDER BY c,a,b

   564   }

   565 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   566 do_test selectA-2.80 {

   567   execsql {

   568     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2

   569     ORDER BY c DESC,a,b

   570   }

   571 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   572 do_test selectA-2.81 {

   573   execsql {

   574     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2

   575     ORDER BY c COLLATE BINARY DESC,a,b

   576   }

   577 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}

   578 do_test selectA-2.82 {

   579   execsql {

   580     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3

   581     ORDER BY a,b,c

   582   }

   583 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   584 do_test selectA-2.83 {

   585   execsql {

   586     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3

   587     ORDER BY a DESC,b,c

   588   }

   589 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}

   590 do_test selectA-2.84 {

   591   execsql {

   592     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3

   593     ORDER BY a,c,b

   594   }

   595 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   596 do_test selectA-2.85 {

   597   execsql {

   598     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3

   599     ORDER BY b,a,c

   600   }

   601 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}

   602 do_test selectA-2.86 {

   603   execsql {

   604     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3

   605     ORDER BY b COLLATE NOCASE,a,c

   606   }

   607 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   608 do_test selectA-2.87 {

   609   execsql {

   610     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3

   611     ORDER BY y COLLATE NOCASE DESC,x,z

   612   }

   613 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   614 do_test selectA-2.88 {

   615   execsql {

   616     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3

   617     ORDER BY c,b,a

   618   }

   619 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   620 do_test selectA-2.89 {

   621   execsql {

   622     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3

   623     ORDER BY c,a,b

   624   }

   625 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   626 do_test selectA-2.90 {

   627   execsql {

   628     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3

   629     ORDER BY c DESC,a,b

   630   }

   631 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   632 do_test selectA-2.91 {

   633   execsql {

   634     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3

   635     ORDER BY c COLLATE BINARY DESC,a,b

   636   }

   637 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}

   638 do_test selectA-2.92 {

   639   execsql {

   640     SELECT x,y,z FROM t2

   641     INTERSECT SELECT a,b,c FROM t3

   642     EXCEPT SELECT c,b,a FROM t1

   643     UNION SELECT a,b,c FROM t3

   644     INTERSECT SELECT a,b,c FROM t3

   645     EXCEPT SELECT c,b,a FROM t1

   646     UNION SELECT a,b,c FROM t3

   647     ORDER BY y COLLATE NOCASE DESC,x,z

   648   }

   649 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   650 do_test selectA-2.93 {

   651   execsql {

   652     SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1));

   653   }

   654 } {A}

   655 do_test selectA-2.94 {

   656   execsql {

   657     SELECT lower((SELECT c FROM t1 UNION ALL SELECT z FROM t2 ORDER BY 1));

   658   }

   659 } {a}

   660 do_test selectA-2.95 {

   661   execsql {

   662     SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1));

   663   }

   664 } {{}}

   665 do_test selectA-2.96 {

   666   execsql {

   667     SELECT lower((SELECT z FROM t2 EXCEPT SELECT c FROM t1 ORDER BY 1));

   668   }

   669 } {m}

   670 

   671 

   672 do_test selectA-3.0 {

   673   execsql {

   674     CREATE UNIQUE INDEX t1a ON t1(a);

   675     CREATE UNIQUE INDEX t1b ON t1(b);

   676     CREATE UNIQUE INDEX t1c ON t1(c);

   677     CREATE UNIQUE INDEX t2x ON t2(x);

   678     CREATE UNIQUE INDEX t2y ON t2(y);

   679     CREATE UNIQUE INDEX t2z ON t2(z);

   680     SELECT name FROM sqlite_master WHERE type='index'

   681   }

   682 } {t1a t1b t1c t2x t2y t2z}

   683 do_test selectA-3.1 {

   684   execsql {

   685     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

   686     ORDER BY a,b,c

   687   }

   688 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   689 do_test selectA-3.1.1 {  # Ticket #3314

   690   execsql {

   691     SELECT t1.a,b,t1.c FROM t1 UNION ALL SELECT x,y,z FROM t2

   692     ORDER BY a,t1.b,t1.c

   693   }

   694 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   695 do_test selectA-3.2 {

   696   execsql {

   697     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

   698     ORDER BY a DESC,b,c

   699   }

   700 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}

   701 do_test selectA-3.3 {

   702   execsql {

   703     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

   704     ORDER BY a,c,b

   705   }

   706 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   707 do_test selectA-3.4 {

   708   execsql {

   709     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

   710     ORDER BY b,a,c

   711   }

   712 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}

   713 do_test selectA-3.5 {

   714   execsql {

   715     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

   716     ORDER BY b COLLATE NOCASE,a,c

   717   }

   718 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   719 do_test selectA-3.6 {

   720   execsql {

   721     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

   722     ORDER BY b COLLATE NOCASE DESC,a,c

   723   }

   724 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   725 do_test selectA-3.7 {

   726   execsql {

   727     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

   728     ORDER BY c,b,a

   729   }

   730 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   731 do_test selectA-3.8 {

   732   execsql {

   733     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

   734     ORDER BY c,a,b

   735   }

   736 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   737 do_test selectA-3.9 {

   738   execsql {

   739     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

   740     ORDER BY c DESC,a,b

   741   }

   742 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   743 do_test selectA-3.10 {

   744   execsql {

   745     SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2

   746     ORDER BY c COLLATE BINARY DESC,a,b

   747   }

   748 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}

   749 do_test selectA-3.11 {

   750   execsql {

   751     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   752     ORDER BY a,b,c

   753   }

   754 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   755 do_test selectA-3.12 {

   756   execsql {

   757     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   758     ORDER BY a DESC,b,c

   759   }

   760 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}

   761 do_test selectA-3.13 {

   762   execsql {

   763     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   764     ORDER BY a,c,b

   765   }

   766 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   767 do_test selectA-3.14 {

   768   execsql {

   769     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   770     ORDER BY b,a,c

   771   }

   772 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}

   773 do_test selectA-3.15 {

   774   execsql {

   775     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   776     ORDER BY b COLLATE NOCASE,a,c

   777   }

   778 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   779 do_test selectA-3.16 {

   780   execsql {

   781     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   782     ORDER BY b COLLATE NOCASE DESC,a,c

   783   }

   784 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   785 do_test selectA-3.17 {

   786   execsql {

   787     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   788     ORDER BY c,b,a

   789   }

   790 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   791 do_test selectA-3.18 {

   792   execsql {

   793     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   794     ORDER BY c,a,b

   795   }

   796 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   797 do_test selectA-3.19 {

   798   execsql {

   799     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   800     ORDER BY c DESC,a,b

   801   }

   802 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   803 do_test selectA-3.20 {

   804   execsql {

   805     SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1

   806     ORDER BY c COLLATE BINARY DESC,a,b

   807   }

   808 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}

   809 do_test selectA-3.21 {

   810   execsql {

   811     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   812     ORDER BY a,b,c

   813   }

   814 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   815 do_test selectA-3.22 {

   816   execsql {

   817     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   818     ORDER BY a DESC,b,c

   819   }

   820 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}

   821 do_test selectA-3.23 {

   822   execsql {

   823     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   824     ORDER BY a,c,b

   825   }

   826 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   827 do_test selectA-3.24 {

   828   execsql {

   829     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   830     ORDER BY b,a,c

   831   }

   832 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}

   833 do_test selectA-3.25 {

   834   execsql {

   835     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   836     ORDER BY b COLLATE NOCASE,a,c

   837   }

   838 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   839 do_test selectA-3.26 {

   840   execsql {

   841     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   842     ORDER BY b COLLATE NOCASE DESC,a,c

   843   }

   844 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   845 do_test selectA-3.27 {

   846   execsql {

   847     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   848     ORDER BY c,b,a

   849   }

   850 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   851 do_test selectA-3.28 {

   852   execsql {

   853     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   854     ORDER BY c,a,b

   855   }

   856 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   857 do_test selectA-3.29 {

   858   execsql {

   859     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   860     ORDER BY c DESC,a,b

   861   }

   862 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   863 do_test selectA-3.30 {

   864   execsql {

   865     SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2

   866     ORDER BY c COLLATE BINARY DESC,a,b

   867   }

   868 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}

   869 do_test selectA-3.31 {

   870   execsql {

   871     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   872     ORDER BY a,b,c

   873   }

   874 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   875 do_test selectA-3.32 {

   876   execsql {

   877     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   878     ORDER BY a DESC,b,c

   879   }

   880 } {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}

   881 do_test selectA-3.33 {

   882   execsql {

   883     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   884     ORDER BY a,c,b

   885   }

   886 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}

   887 do_test selectA-3.34 {

   888   execsql {

   889     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   890     ORDER BY b,a,c

   891   }

   892 } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}

   893 do_test selectA-3.35 {

   894   execsql {

   895     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   896     ORDER BY b COLLATE NOCASE,a,c

   897   }

   898 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   899 do_test selectA-3.36 {

   900   execsql {

   901     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   902     ORDER BY b COLLATE NOCASE DESC,a,c

   903   }

   904 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   905 do_test selectA-3.37 {

   906   execsql {

   907     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   908     ORDER BY c,b,a

   909   }

   910 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   911 do_test selectA-3.38 {

   912   execsql {

   913     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   914     ORDER BY c,a,b

   915   }

   916 } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}

   917 do_test selectA-3.39 {

   918   execsql {

   919     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   920     ORDER BY c DESC,a,b

   921   }

   922 } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}

   923 do_test selectA-3.40 {

   924   execsql {

   925     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1

   926     ORDER BY c COLLATE BINARY DESC,a,b

   927   }

   928 } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}

   929 do_test selectA-3.41 {

   930   execsql {

   931     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'

   932     ORDER BY a,b,c

   933   }

   934 } {{} C c 1 a a 9.9 b B}

   935 do_test selectA-3.42 {

   936   execsql {

   937     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'

   938     ORDER BY a,b,c

   939   }

   940 } {hello d D abc e e}

   941 do_test selectA-3.43 {

   942   execsql {

   943     SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1

   944     ORDER BY a,b,c

   945   }

   946 } {hello d D abc e e}

   947 do_test selectA-3.44 {

   948   execsql {

   949     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'

   950     ORDER BY a,b,c

   951   }

   952 } {hello d D abc e e}

   953 do_test selectA-3.45 {

   954   execsql {

   955     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

   956     ORDER BY a,b,c

   957   }

   958 } {{} C c 1 a a 9.9 b B}

   959 do_test selectA-3.46 {

   960   execsql {

   961     SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1

   962     ORDER BY a,b,c

   963   }

   964 } {{} C c 1 a a 9.9 b B}

   965 do_test selectA-3.47 {

   966   execsql {

   967     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'

   968     ORDER BY a DESC

   969   }

   970 } {9.9 b B 1 a a {} C c}

   971 do_test selectA-3.48 {

   972   execsql {

   973     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'

   974     ORDER BY a DESC

   975   }

   976 } {abc e e hello d D}

   977 do_test selectA-3.49 {

   978   execsql {

   979     SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1

   980     ORDER BY a DESC

   981   }

   982 } {abc e e hello d D}

   983 do_test selectA-3.50 {

   984   execsql {

   985     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'

   986     ORDER BY a DESC

   987   }

   988 } {abc e e hello d D}

   989 do_test selectA-3.51 {

   990   execsql {

   991     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

   992     ORDER BY a DESC

   993   }

   994 } {9.9 b B 1 a a {} C c}

   995 do_test selectA-3.52 {

   996   execsql {

   997     SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1

   998     ORDER BY a DESC

   999   }

  1000 } {9.9 b B 1 a a {} C c}

  1001 do_test selectA-3.53 {

  1002   execsql {

  1003     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'

  1004     ORDER BY b, a DESC

  1005   }

  1006 } {{} C c 1 a a 9.9 b B}

  1007 do_test selectA-3.54 {

  1008   execsql {

  1009     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'

  1010     ORDER BY b

  1011   }

  1012 } {hello d D abc e e}

  1013 do_test selectA-3.55 {

  1014   execsql {

  1015     SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1

  1016     ORDER BY b DESC, c

  1017   }

  1018 } {abc e e hello d D}

  1019 do_test selectA-3.56 {

  1020   execsql {

  1021     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'

  1022     ORDER BY b, c DESC, a

  1023   }

  1024 } {hello d D abc e e}

  1025 do_test selectA-3.57 {

  1026   execsql {

  1027     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

  1028     ORDER BY b COLLATE NOCASE

  1029   }

  1030 } {1 a a 9.9 b B {} C c}

  1031 do_test selectA-3.58 {

  1032   execsql {

  1033     SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1

  1034     ORDER BY b

  1035   }

  1036 } {{} C c 1 a a 9.9 b B}

  1037 do_test selectA-3.59 {

  1038   execsql {

  1039     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'

  1040     ORDER BY c, a DESC

  1041   }

  1042 } {1 a a 9.9 b B {} C c}

  1043 do_test selectA-3.60 {

  1044   execsql {

  1045     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'

  1046     ORDER BY c

  1047   }

  1048 } {hello d D abc e e}

  1049 do_test selectA-3.61 {

  1050   execsql {

  1051     SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1

  1052     ORDER BY c COLLATE BINARY, b DESC, c, a, b, c, a, b, c

  1053   }

  1054 } {hello d D abc e e}

  1055 do_test selectA-3.62 {

  1056   execsql {

  1057     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'

  1058     ORDER BY c DESC, a

  1059   }

  1060 } {abc e e hello d D}

  1061 do_test selectA-3.63 {

  1062   execsql {

  1063     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

  1064     ORDER BY c COLLATE NOCASE

  1065   }

  1066 } {1 a a 9.9 b B {} C c}

  1067 do_test selectA-3.64 {

  1068   execsql {

  1069     SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1

  1070     ORDER BY c

  1071   }

  1072 } {1 a a 9.9 b B {} C c}

  1073 do_test selectA-3.65 {

  1074   execsql {

  1075     SELECT a,b,c FROM t3 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

  1076     ORDER BY c COLLATE NOCASE

  1077   }

  1078 } {1 a a 9.9 b B {} C c}

  1079 do_test selectA-3.66 {

  1080   execsql {

  1081     SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t3

  1082     ORDER BY c

  1083   }

  1084 } {1 a a 9.9 b B {} C c}

  1085 do_test selectA-3.67 {

  1086   execsql {

  1087     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t3 WHERE b<'d'

  1088     ORDER BY c DESC, a

  1089   }

  1090 } {abc e e hello d D}

  1091 do_test selectA-3.68 {

  1092   execsql {

  1093     SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'

  1094     INTERSECT SELECT a,b,c FROM t3

  1095     EXCEPT SELECT b,c,a FROM t3

  1096     ORDER BY c DESC, a

  1097   }

  1098 } {abc e e hello d D}

  1099 do_test selectA-3.69 {

  1100   execsql {

  1101     SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'

  1102     INTERSECT SELECT a,b,c FROM t3

  1103     EXCEPT SELECT b,c,a FROM t3

  1104     ORDER BY c COLLATE NOCASE

  1105   }

  1106 } {1 a a 9.9 b B {} C c}

  1107 do_test selectA-3.70 {

  1108   execsql {

  1109     SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1

  1110     INTERSECT SELECT a,b,c FROM t3

  1111     EXCEPT SELECT b,c,a FROM t3

  1112     ORDER BY c

  1113   }

  1114 } {1 a a 9.9 b B {} C c}

  1115 do_test selectA-3.71 {

  1116   execsql {

  1117     SELECT a,b,c FROM t1 WHERE b<'d'

  1118     INTERSECT SELECT a,b,c FROM t1

  1119     INTERSECT SELECT a,b,c FROM t3

  1120     EXCEPT SELECT b,c,a FROM t3

  1121     INTERSECT SELECT a,b,c FROM t1

  1122     EXCEPT SELECT x,y,z FROM t2

  1123     INTERSECT SELECT a,b,c FROM t3

  1124     EXCEPT SELECT y,x,z FROM t2

  1125     INTERSECT SELECT a,b,c FROM t1

  1126     EXCEPT SELECT c,b,a FROM t3

  1127     ORDER BY c

  1128   }

  1129 } {1 a a 9.9 b B {} C c}

  1130 do_test selectA-3.72 {

  1131   execsql {

  1132     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2

  1133     ORDER BY a,b,c

  1134   }

  1135 } {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}