Return to selectA.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    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.1.1.1 2012/02/21 17:04:16 misho 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}
 1136: do_test selectA-3.73 {
 1137:   execsql {
 1138:     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
 1139:     ORDER BY a DESC,b,c
 1140:   }
 1141: } {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}
 1142: do_test selectA-3.74 {
 1143:   execsql {
 1144:     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
 1145:     ORDER BY a,c,b
 1146:   }
 1147: } {{} 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}
 1148: do_test selectA-3.75 {
 1149:   execsql {
 1150:     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
 1151:     ORDER BY b,a,c
 1152:   }
 1153: } {{} 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}
 1154: do_test selectA-3.76 {
 1155:   execsql {
 1156:     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
 1157:     ORDER BY b COLLATE NOCASE,a,c
 1158:   }
 1159: } {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}
 1160: do_test selectA-3.77 {
 1161:   execsql {
 1162:     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
 1163:     ORDER BY b COLLATE NOCASE DESC,a,c
 1164:   }
 1165: } {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}
 1166: do_test selectA-3.78 {
 1167:   execsql {
 1168:     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
 1169:     ORDER BY c,b,a
 1170:   }
 1171: } {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}
 1172: do_test selectA-3.79 {
 1173:   execsql {
 1174:     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
 1175:     ORDER BY c,a,b
 1176:   }
 1177: } {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}
 1178: do_test selectA-3.80 {
 1179:   execsql {
 1180:     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
 1181:     ORDER BY c DESC,a,b
 1182:   }
 1183: } {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}
 1184: do_test selectA-3.81 {
 1185:   execsql {
 1186:     SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
 1187:     ORDER BY c COLLATE BINARY DESC,a,b
 1188:   }
 1189: } {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}
 1190: do_test selectA-3.82 {
 1191:   execsql {
 1192:     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
 1193:     ORDER BY a,b,c
 1194:   }
 1195: } {{} 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}
 1196: do_test selectA-3.83 {
 1197:   execsql {
 1198:     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
 1199:     ORDER BY a DESC,b,c
 1200:   }
 1201: } {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}
 1202: do_test selectA-3.84 {
 1203:   execsql {
 1204:     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
 1205:     ORDER BY a,c,b
 1206:   }
 1207: } {{} 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}
 1208: do_test selectA-3.85 {
 1209:   execsql {
 1210:     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
 1211:     ORDER BY b,a,c
 1212:   }
 1213: } {{} 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}
 1214: do_test selectA-3.86 {
 1215:   execsql {
 1216:     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
 1217:     ORDER BY b COLLATE NOCASE,a,c
 1218:   }
 1219: } {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}
 1220: do_test selectA-3.87 {
 1221:   execsql {
 1222:     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
 1223:     ORDER BY y COLLATE NOCASE DESC,x,z
 1224:   }
 1225: } {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}
 1226: do_test selectA-3.88 {
 1227:   execsql {
 1228:     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
 1229:     ORDER BY c,b,a
 1230:   }
 1231: } {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}
 1232: do_test selectA-3.89 {
 1233:   execsql {
 1234:     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
 1235:     ORDER BY c,a,b
 1236:   }
 1237: } {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}
 1238: do_test selectA-3.90 {
 1239:   execsql {
 1240:     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
 1241:     ORDER BY c DESC,a,b
 1242:   }
 1243: } {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}
 1244: do_test selectA-3.91 {
 1245:   execsql {
 1246:     SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
 1247:     ORDER BY c COLLATE BINARY DESC,a,b
 1248:   }
 1249: } {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}
 1250: do_test selectA-3.92 {
 1251:   execsql {
 1252:     SELECT x,y,z FROM t2
 1253:     INTERSECT SELECT a,b,c FROM t3
 1254:     EXCEPT SELECT c,b,a FROM t1
 1255:     UNION SELECT a,b,c FROM t3
 1256:     INTERSECT SELECT a,b,c FROM t3
 1257:     EXCEPT SELECT c,b,a FROM t1
 1258:     UNION SELECT a,b,c FROM t3
 1259:     ORDER BY y COLLATE NOCASE DESC,x,z
 1260:   }
 1261: } {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}
 1262: do_test selectA-3.93 {
 1263:   execsql {
 1264:     SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1));
 1265:   }
 1266: } {A}
 1267: do_test selectA-3.94 {
 1268:   execsql {
 1269:     SELECT lower((SELECT c FROM t1 UNION ALL SELECT z FROM t2 ORDER BY 1));
 1270:   }
 1271: } {a}
 1272: do_test selectA-3.95 {
 1273:   execsql {
 1274:     SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1));
 1275:   }
 1276: } {{}}
 1277: do_test selectA-3.96 {
 1278:   execsql {
 1279:     SELECT lower((SELECT z FROM t2 EXCEPT SELECT c FROM t1 ORDER BY 1));
 1280:   }
 1281: } {m}
 1282: do_test selectA-3.97 {
 1283:   execsql {
 1284:     SELECT upper((SELECT x FROM (
 1285:       SELECT x,y,z FROM t2
 1286:       INTERSECT SELECT a,b,c FROM t3
 1287:       EXCEPT SELECT c,b,a FROM t1
 1288:       UNION SELECT a,b,c FROM t3
 1289:       INTERSECT SELECT a,b,c FROM t3
 1290:       EXCEPT SELECT c,b,a FROM t1
 1291:       UNION SELECT a,b,c FROM t3
 1292:       ORDER BY y COLLATE NOCASE DESC,x,z)))
 1293:   }
 1294: } {MAD}
 1295: 
 1296: finish_test