/ Hex Artifact Content
Login

Artifact 5389d9895968d1196c457d59b3ee6515d771d328:


0000: 23 20 32 30 30 31 20 53 65 70 74 65 6d 62 65 72  # 2001 September
0010: 20 31 35 0a 23 0a 23 20 54 68 65 20 61 75 74 68   15.#.# The auth
0020: 6f 72 20 64 69 73 63 6c 61 69 6d 73 20 63 6f 70  or disclaims cop
0030: 79 72 69 67 68 74 20 74 6f 20 74 68 69 73 20 73  yright to this s
0040: 6f 75 72 63 65 20 63 6f 64 65 2e 20 20 49 6e 20  ource code.  In 
0050: 70 6c 61 63 65 20 6f 66 0a 23 20 61 20 6c 65 67  place of.# a leg
0060: 61 6c 20 6e 6f 74 69 63 65 2c 20 68 65 72 65 20  al notice, here 
0070: 69 73 20 61 20 62 6c 65 73 73 69 6e 67 3a 0a 23  is a blessing:.#
0080: 0a 23 20 20 20 20 4d 61 79 20 79 6f 75 20 64 6f  .#    May you do
0090: 20 67 6f 6f 64 20 61 6e 64 20 6e 6f 74 20 65 76   good and not ev
00a0: 69 6c 2e 0a 23 20 20 20 20 4d 61 79 20 79 6f 75  il..#    May you
00b0: 20 66 69 6e 64 20 66 6f 72 67 69 76 65 6e 65 73   find forgivenes
00c0: 73 20 66 6f 72 20 79 6f 75 72 73 65 6c 66 20 61  s for yourself a
00d0: 6e 64 20 66 6f 72 67 69 76 65 20 6f 74 68 65 72  nd forgive other
00e0: 73 2e 0a 23 20 20 20 20 4d 61 79 20 79 6f 75 20  s..#    May you 
00f0: 73 68 61 72 65 20 66 72 65 65 6c 79 2c 20 6e 65  share freely, ne
0100: 76 65 72 20 74 61 6b 69 6e 67 20 6d 6f 72 65 20  ver taking more 
0110: 74 68 61 6e 20 79 6f 75 20 67 69 76 65 2e 0a 23  than you give..#
0120: 0a 23 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a  .#**************
0130: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a  ****************
0140: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a  ****************
0150: 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a  ****************
0160: 2a 2a 2a 2a 2a 2a 2a 2a 2a 0a 23 20 54 68 69 73  *********.# This
0170: 20 66 69 6c 65 20 69 6d 70 6c 65 6d 65 6e 74 73   file implements
0180: 20 72 65 67 72 65 73 73 69 6f 6e 20 74 65 73 74   regression test
0190: 73 20 66 6f 72 20 53 51 4c 69 74 65 20 6c 69 62  s for SQLite lib
01a0: 72 61 72 79 2e 20 20 54 68 65 0a 23 20 66 6f 63  rary.  The.# foc
01b0: 75 73 20 6f 66 20 74 68 69 73 20 66 69 6c 65 20  us of this file 
01c0: 69 73 20 74 65 73 74 69 6e 67 20 55 4e 49 4f 4e  is testing UNION
01d0: 2c 20 49 4e 54 45 52 53 45 43 54 20 61 6e 64 20  , INTERSECT and 
01e0: 45 58 43 45 50 54 20 6f 70 65 72 61 74 6f 72 73  EXCEPT operators
01f0: 0a 23 20 69 6e 20 53 45 4c 45 43 54 20 73 74 61  .# in SELECT sta
0200: 74 65 6d 65 6e 74 73 2e 0a 23 0a 0a 73 65 74 20  tements..#..set 
0210: 74 65 73 74 64 69 72 20 5b 66 69 6c 65 20 64 69  testdir [file di
0220: 72 6e 61 6d 65 20 24 61 72 67 76 30 5d 0a 73 6f  rname $argv0].so
0230: 75 72 63 65 20 24 74 65 73 74 64 69 72 2f 74 65  urce $testdir/te
0240: 73 74 65 72 2e 74 63 6c 0a 0a 23 20 4d 6f 73 74  ster.tcl..# Most
0250: 20 74 65 73 74 73 20 69 6e 20 74 68 69 73 20 66   tests in this f
0260: 69 6c 65 20 64 65 70 65 6e 64 20 6f 6e 20 63 6f  ile depend on co
0270: 6d 70 6f 75 6e 64 2d 73 65 6c 65 63 74 2e 20 42  mpound-select. B
0280: 75 74 20 74 68 65 72 65 20 61 72 65 20 61 20 63  ut there are a c
0290: 6f 75 70 6c 65 0a 23 20 72 69 67 68 74 20 61 74  ouple.# right at
02a0: 20 74 68 65 20 65 6e 64 20 74 68 61 74 20 74 65   the end that te
02b0: 73 74 20 44 49 53 54 49 4e 43 54 2c 20 73 6f 20  st DISTINCT, so 
02c0: 77 65 20 63 61 6e 6e 6f 74 20 6f 6d 69 74 20 74  we cannot omit t
02d0: 68 65 20 65 6e 74 69 72 65 20 66 69 6c 65 2e 0a  he entire file..
02e0: 23 0a 69 66 63 61 70 61 62 6c 65 20 63 6f 6d 70  #.ifcapable comp
02f0: 6f 75 6e 64 20 7b 0a 0a 23 20 42 75 69 6c 64 20  ound {..# Build 
0300: 73 6f 6d 65 20 74 65 73 74 20 64 61 74 61 0a 23  some test data.#
0310: 0a 65 78 65 63 73 71 6c 20 7b 0a 20 20 43 52 45  .execsql {.  CRE
0320: 41 54 45 20 54 41 42 4c 45 20 74 31 28 6e 20 69  ATE TABLE t1(n i
0330: 6e 74 2c 20 6c 6f 67 20 69 6e 74 29 3b 0a 20 20  nt, log int);.  
0340: 42 45 47 49 4e 3b 0a 7d 0a 66 6f 72 20 7b 73 65  BEGIN;.}.for {se
0350: 74 20 69 20 31 7d 20 7b 24 69 3c 33 32 7d 20 7b  t i 1} {$i<32} {
0360: 69 6e 63 72 20 69 7d 20 7b 0a 20 20 66 6f 72 20  incr i} {.  for 
0370: 7b 73 65 74 20 6a 20 30 7d 20 7b 28 31 3c 3c 24  {set j 0} {(1<<$
0380: 6a 29 3c 24 69 7d 20 7b 69 6e 63 72 20 6a 7d 20  j)<$i} {incr j} 
0390: 7b 7d 0a 20 20 65 78 65 63 73 71 6c 20 22 49 4e  {}.  execsql "IN
03a0: 53 45 52 54 20 49 4e 54 4f 20 74 31 20 56 41 4c  SERT INTO t1 VAL
03b0: 55 45 53 28 24 69 2c 24 6a 29 22 0a 7d 0a 65 78  UES($i,$j)".}.ex
03c0: 65 63 73 71 6c 20 7b 0a 20 20 43 4f 4d 4d 49 54  ecsql {.  COMMIT
03d0: 3b 0a 7d 0a 0a 64 6f 5f 74 65 73 74 20 73 65 6c  ;.}..do_test sel
03e0: 65 63 74 34 2d 31 2e 30 20 7b 0a 20 20 65 78 65  ect4-1.0 {.  exe
03f0: 63 73 71 6c 20 7b 53 45 4c 45 43 54 20 44 49 53  csql {SELECT DIS
0400: 54 49 4e 43 54 20 6c 6f 67 20 46 52 4f 4d 20 74  TINCT log FROM t
0410: 31 20 4f 52 44 45 52 20 42 59 20 6c 6f 67 7d 0a  1 ORDER BY log}.
0420: 7d 20 7b 30 20 31 20 32 20 33 20 34 20 35 7d 0a  } {0 1 2 3 4 5}.
0430: 0a 23 20 55 6e 69 6f 6e 20 41 6c 6c 20 6f 70 65  .# Union All ope
0440: 72 61 74 6f 72 0a 23 0a 64 6f 5f 74 65 73 74 20  rator.#.do_test 
0450: 73 65 6c 65 63 74 34 2d 31 2e 31 61 20 7b 0a 20  select4-1.1a {. 
0460: 20 6c 73 6f 72 74 20 5b 65 78 65 63 73 71 6c 20   lsort [execsql 
0470: 7b 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54  {SELECT DISTINCT
0480: 20 6c 6f 67 20 46 52 4f 4d 20 74 31 7d 5d 0a 7d   log FROM t1}].}
0490: 20 7b 30 20 31 20 32 20 33 20 34 20 35 7d 0a 64   {0 1 2 3 4 5}.d
04a0: 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 31  o_test select4-1
04b0: 2e 31 62 20 7b 0a 20 20 6c 73 6f 72 74 20 5b 65  .1b {.  lsort [e
04c0: 78 65 63 73 71 6c 20 7b 53 45 4c 45 43 54 20 6e  xecsql {SELECT n
04d0: 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20 6c   FROM t1 WHERE l
04e0: 6f 67 3d 33 7d 5d 0a 7d 20 7b 35 20 36 20 37 20  og=3}].} {5 6 7 
04f0: 38 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63  8}.do_test selec
0500: 74 34 2d 31 2e 31 63 20 7b 0a 20 20 65 78 65 63  t4-1.1c {.  exec
0510: 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54  sql {.    SELECT
0520: 20 44 49 53 54 49 4e 43 54 20 6c 6f 67 20 46 52   DISTINCT log FR
0530: 4f 4d 20 74 31 0a 20 20 20 20 55 4e 49 4f 4e 20  OM t1.    UNION 
0540: 41 4c 4c 0a 20 20 20 20 53 45 4c 45 43 54 20 6e  ALL.    SELECT n
0550: 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20 6c   FROM t1 WHERE l
0560: 6f 67 3d 33 0a 20 20 20 20 4f 52 44 45 52 20 42  og=3.    ORDER B
0570: 59 20 6c 6f 67 3b 0a 20 20 7d 0a 7d 20 7b 30 20  Y log;.  }.} {0 
0580: 31 20 32 20 33 20 34 20 35 20 35 20 36 20 37 20  1 2 3 4 5 5 6 7 
0590: 38 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63  8}.do_test selec
05a0: 74 34 2d 31 2e 31 64 20 7b 0a 20 20 65 78 65 63  t4-1.1d {.  exec
05b0: 73 71 6c 20 7b 0a 20 20 20 20 43 52 45 41 54 45  sql {.    CREATE
05c0: 20 54 41 42 4c 45 20 74 32 20 41 53 0a 20 20 20   TABLE t2 AS.   
05d0: 20 20 20 53 45 4c 45 43 54 20 44 49 53 54 49 4e     SELECT DISTIN
05e0: 43 54 20 6c 6f 67 20 46 52 4f 4d 20 74 31 0a 20  CT log FROM t1. 
05f0: 20 20 20 20 20 55 4e 49 4f 4e 20 41 4c 4c 0a 20       UNION ALL. 
0600: 20 20 20 20 20 53 45 4c 45 43 54 20 6e 20 46 52       SELECT n FR
0610: 4f 4d 20 74 31 20 57 48 45 52 45 20 6c 6f 67 3d  OM t1 WHERE log=
0620: 33 0a 20 20 20 20 20 20 4f 52 44 45 52 20 42 59  3.      ORDER BY
0630: 20 6c 6f 67 3b 0a 20 20 20 20 53 45 4c 45 43 54   log;.    SELECT
0640: 20 2a 20 46 52 4f 4d 20 74 32 3b 0a 20 20 7d 0a   * FROM t2;.  }.
0650: 7d 20 7b 30 20 31 20 32 20 33 20 34 20 35 20 35  } {0 1 2 3 4 5 5
0660: 20 36 20 37 20 38 7d 0a 65 78 65 63 73 71 6c 20   6 7 8}.execsql 
0670: 7b 44 52 4f 50 20 54 41 42 4c 45 20 74 32 7d 0a  {DROP TABLE t2}.
0680: 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d  do_test select4-
0690: 31 2e 31 65 20 7b 0a 20 20 65 78 65 63 73 71 6c  1.1e {.  execsql
06a0: 20 7b 0a 20 20 20 20 43 52 45 41 54 45 20 54 41   {.    CREATE TA
06b0: 42 4c 45 20 74 32 20 41 53 0a 20 20 20 20 20 20  BLE t2 AS.      
06c0: 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54 20  SELECT DISTINCT 
06d0: 6c 6f 67 20 46 52 4f 4d 20 74 31 0a 20 20 20 20  log FROM t1.    
06e0: 20 20 55 4e 49 4f 4e 20 41 4c 4c 0a 20 20 20 20    UNION ALL.    
06f0: 20 20 53 45 4c 45 43 54 20 6e 20 46 52 4f 4d 20    SELECT n FROM 
0700: 74 31 20 57 48 45 52 45 20 6c 6f 67 3d 33 0a 20  t1 WHERE log=3. 
0710: 20 20 20 20 20 4f 52 44 45 52 20 42 59 20 6c 6f       ORDER BY lo
0720: 67 20 44 45 53 43 3b 0a 20 20 20 20 53 45 4c 45  g DESC;.    SELE
0730: 43 54 20 2a 20 46 52 4f 4d 20 74 32 3b 0a 20 20  CT * FROM t2;.  
0740: 7d 0a 7d 20 7b 38 20 37 20 36 20 35 20 35 20 34  }.} {8 7 6 5 5 4
0750: 20 33 20 32 20 31 20 30 7d 0a 65 78 65 63 73 71   3 2 1 0}.execsq
0760: 6c 20 7b 44 52 4f 50 20 54 41 42 4c 45 20 74 32  l {DROP TABLE t2
0770: 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74  }.do_test select
0780: 34 2d 31 2e 31 66 20 7b 0a 20 20 65 78 65 63 73  4-1.1f {.  execs
0790: 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20  ql {.    SELECT 
07a0: 44 49 53 54 49 4e 43 54 20 6c 6f 67 20 46 52 4f  DISTINCT log FRO
07b0: 4d 20 74 31 0a 20 20 20 20 55 4e 49 4f 4e 20 41  M t1.    UNION A
07c0: 4c 4c 0a 20 20 20 20 53 45 4c 45 43 54 20 6e 20  LL.    SELECT n 
07d0: 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20 6c 6f  FROM t1 WHERE lo
07e0: 67 3d 32 0a 20 20 7d 0a 7d 20 7b 30 20 31 20 32  g=2.  }.} {0 1 2
07f0: 20 33 20 34 20 35 20 33 20 34 7d 0a 64 6f 5f 74   3 4 5 3 4}.do_t
0800: 65 73 74 20 73 65 6c 65 63 74 34 2d 31 2e 31 67  est select4-1.1g
0810: 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20   {.  execsql {. 
0820: 20 20 20 43 52 45 41 54 45 20 54 41 42 4c 45 20     CREATE TABLE 
0830: 74 32 20 41 53 20 0a 20 20 20 20 20 20 53 45 4c  t2 AS .      SEL
0840: 45 43 54 20 44 49 53 54 49 4e 43 54 20 6c 6f 67  ECT DISTINCT log
0850: 20 46 52 4f 4d 20 74 31 0a 20 20 20 20 20 20 55   FROM t1.      U
0860: 4e 49 4f 4e 20 41 4c 4c 0a 20 20 20 20 20 20 53  NION ALL.      S
0870: 45 4c 45 43 54 20 6e 20 46 52 4f 4d 20 74 31 20  ELECT n FROM t1 
0880: 57 48 45 52 45 20 6c 6f 67 3d 32 3b 0a 20 20 20  WHERE log=2;.   
0890: 20 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 74   SELECT * FROM t
08a0: 32 3b 0a 20 20 7d 0a 7d 20 7b 30 20 31 20 32 20  2;.  }.} {0 1 2 
08b0: 33 20 34 20 35 20 33 20 34 7d 0a 65 78 65 63 73  3 4 5 3 4}.execs
08c0: 71 6c 20 7b 44 52 4f 50 20 54 41 42 4c 45 20 74  ql {DROP TABLE t
08d0: 32 7d 0a 69 66 63 61 70 61 62 6c 65 20 73 75 62  2}.ifcapable sub
08e0: 71 75 65 72 79 20 7b 0a 20 20 64 6f 5f 74 65 73  query {.  do_tes
08f0: 74 20 73 65 6c 65 63 74 34 2d 31 2e 32 20 7b 0a  t select4-1.2 {.
0900: 20 20 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20      execsql {.  
0910: 20 20 20 20 53 45 4c 45 43 54 20 6c 6f 67 20 46      SELECT log F
0920: 52 4f 4d 20 74 31 20 57 48 45 52 45 20 6e 20 49  ROM t1 WHERE n I
0930: 4e 20 0a 20 20 20 20 20 20 20 20 28 53 45 4c 45  N .        (SELE
0940: 43 54 20 44 49 53 54 49 4e 43 54 20 6c 6f 67 20  CT DISTINCT log 
0950: 46 52 4f 4d 20 74 31 20 55 4e 49 4f 4e 20 41 4c  FROM t1 UNION AL
0960: 4c 0a 20 20 20 20 20 20 20 20 20 53 45 4c 45 43  L.         SELEC
0970: 54 20 6e 20 46 52 4f 4d 20 74 31 20 57 48 45 52  T n FROM t1 WHER
0980: 45 20 6c 6f 67 3d 33 29 0a 20 20 20 20 20 20 4f  E log=3).      O
0990: 52 44 45 52 20 42 59 20 6c 6f 67 3b 0a 20 20 20  RDER BY log;.   
09a0: 20 7d 0a 20 20 7d 20 7b 30 20 31 20 32 20 32 20   }.  } {0 1 2 2 
09b0: 33 20 33 20 33 20 33 7d 0a 7d 0a 0a 23 20 45 56  3 3 3 3}.}..# EV
09c0: 49 44 45 4e 43 45 2d 4f 46 3a 20 52 2d 30 32 36  IDENCE-OF: R-026
09d0: 34 34 2d 32 32 31 33 31 20 49 6e 20 61 20 63 6f  44-22131 In a co
09e0: 6d 70 6f 75 6e 64 20 53 45 4c 45 43 54 20 73 74  mpound SELECT st
09f0: 61 74 65 6d 65 6e 74 2c 20 6f 6e 6c 79 20 74 68  atement, only th
0a00: 65 0a 23 20 6c 61 73 74 20 6f 72 20 72 69 67 68  e.# last or righ
0a10: 74 2d 6d 6f 73 74 20 73 69 6d 70 6c 65 20 53 45  t-most simple SE
0a20: 4c 45 43 54 20 6d 61 79 20 68 61 76 65 20 61 6e  LECT may have an
0a30: 20 4f 52 44 45 52 20 42 59 20 63 6c 61 75 73 65   ORDER BY clause
0a40: 2e 0a 23 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65  ..#.do_test sele
0a50: 63 74 34 2d 31 2e 33 20 7b 0a 20 20 73 65 74 20  ct4-1.3 {.  set 
0a60: 76 20 5b 63 61 74 63 68 20 7b 65 78 65 63 73 71  v [catch {execsq
0a70: 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 44  l {.    SELECT D
0a80: 49 53 54 49 4e 43 54 20 6c 6f 67 20 46 52 4f 4d  ISTINCT log FROM
0a90: 20 74 31 20 4f 52 44 45 52 20 42 59 20 6c 6f 67   t1 ORDER BY log
0aa0: 0a 20 20 20 20 55 4e 49 4f 4e 20 41 4c 4c 0a 20  .    UNION ALL. 
0ab0: 20 20 20 53 45 4c 45 43 54 20 6e 20 46 52 4f 4d     SELECT n FROM
0ac0: 20 74 31 20 57 48 45 52 45 20 6c 6f 67 3d 33 0a   t1 WHERE log=3.
0ad0: 20 20 20 20 4f 52 44 45 52 20 42 59 20 6c 6f 67      ORDER BY log
0ae0: 3b 0a 20 20 7d 7d 20 6d 73 67 5d 0a 20 20 6c 61  ;.  }} msg].  la
0af0: 70 70 65 6e 64 20 76 20 24 6d 73 67 0a 7d 20 7b  ppend v $msg.} {
0b00: 31 20 7b 4f 52 44 45 52 20 42 59 20 63 6c 61 75  1 {ORDER BY clau
0b10: 73 65 20 73 68 6f 75 6c 64 20 63 6f 6d 65 20 61  se should come a
0b20: 66 74 65 72 20 55 4e 49 4f 4e 20 41 4c 4c 20 6e  fter UNION ALL n
0b30: 6f 74 20 62 65 66 6f 72 65 7d 7d 0a 64 6f 5f 63  ot before}}.do_c
0b40: 61 74 63 68 73 71 6c 5f 74 65 73 74 20 73 65 6c  atchsql_test sel
0b50: 65 63 74 34 2d 31 2e 34 20 7b 0a 20 20 53 45 4c  ect4-1.4 {.  SEL
0b60: 45 43 54 20 28 56 41 4c 55 45 53 28 30 29 20 49  ECT (VALUES(0) I
0b70: 4e 54 45 52 53 45 43 54 20 53 45 4c 45 43 54 28  NTERSECT SELECT(
0b80: 30 29 20 55 4e 49 4f 4e 20 53 45 4c 45 43 54 28  0) UNION SELECT(
0b90: 30 29 20 4f 52 44 45 52 20 42 59 20 31 20 55 4e  0) ORDER BY 1 UN
0ba0: 49 4f 4e 0a 20 20 20 20 20 20 20 20 20 20 53 45  ION.          SE
0bb0: 4c 45 43 54 20 30 20 55 4e 49 4f 4e 20 53 45 4c  LECT 0 UNION SEL
0bc0: 45 43 54 20 30 20 4f 52 44 45 52 20 42 59 20 31  ECT 0 ORDER BY 1
0bd0: 29 3b 0a 7d 20 7b 31 20 7b 4f 52 44 45 52 20 42  );.} {1 {ORDER B
0be0: 59 20 63 6c 61 75 73 65 20 73 68 6f 75 6c 64 20  Y clause should 
0bf0: 63 6f 6d 65 20 61 66 74 65 72 20 55 4e 49 4f 4e  come after UNION
0c00: 20 6e 6f 74 20 62 65 66 6f 72 65 7d 7d 0a 0a 23   not before}}..#
0c10: 20 55 6e 69 6f 6e 20 6f 70 65 72 61 74 6f 72 0a   Union operator.
0c20: 23 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74  #.do_test select
0c30: 34 2d 32 2e 31 20 7b 0a 20 20 65 78 65 63 73 71  4-2.1 {.  execsq
0c40: 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 44  l {.    SELECT D
0c50: 49 53 54 49 4e 43 54 20 6c 6f 67 20 46 52 4f 4d  ISTINCT log FROM
0c60: 20 74 31 0a 20 20 20 20 55 4e 49 4f 4e 0a 20 20   t1.    UNION.  
0c70: 20 20 53 45 4c 45 43 54 20 6e 20 46 52 4f 4d 20    SELECT n FROM 
0c80: 74 31 20 57 48 45 52 45 20 6c 6f 67 3d 33 0a 20  t1 WHERE log=3. 
0c90: 20 20 20 4f 52 44 45 52 20 42 59 20 6c 6f 67 3b     ORDER BY log;
0ca0: 0a 20 20 7d 0a 7d 20 7b 30 20 31 20 32 20 33 20  .  }.} {0 1 2 3 
0cb0: 34 20 35 20 36 20 37 20 38 7d 0a 69 66 63 61 70  4 5 6 7 8}.ifcap
0cc0: 61 62 6c 65 20 73 75 62 71 75 65 72 79 20 7b 0a  able subquery {.
0cd0: 20 20 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74    do_test select
0ce0: 34 2d 32 2e 32 20 7b 0a 20 20 20 20 65 78 65 63  4-2.2 {.    exec
0cf0: 73 71 6c 20 7b 0a 20 20 20 20 20 20 53 45 4c 45  sql {.      SELE
0d00: 43 54 20 6c 6f 67 20 46 52 4f 4d 20 74 31 20 57  CT log FROM t1 W
0d10: 48 45 52 45 20 6e 20 49 4e 20 0a 20 20 20 20 20  HERE n IN .     
0d20: 20 20 20 28 53 45 4c 45 43 54 20 44 49 53 54 49     (SELECT DISTI
0d30: 4e 43 54 20 6c 6f 67 20 46 52 4f 4d 20 74 31 20  NCT log FROM t1 
0d40: 55 4e 49 4f 4e 0a 20 20 20 20 20 20 20 20 20 53  UNION.         S
0d50: 45 4c 45 43 54 20 6e 20 46 52 4f 4d 20 74 31 20  ELECT n FROM t1 
0d60: 57 48 45 52 45 20 6c 6f 67 3d 33 29 0a 20 20 20  WHERE log=3).   
0d70: 20 20 20 4f 52 44 45 52 20 42 59 20 6c 6f 67 3b     ORDER BY log;
0d80: 0a 20 20 20 20 7d 0a 20 20 7d 20 7b 30 20 31 20  .    }.  } {0 1 
0d90: 32 20 32 20 33 20 33 20 33 20 33 7d 0a 7d 0a 64  2 2 3 3 3 3}.}.d
0da0: 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 32  o_test select4-2
0db0: 2e 33 20 7b 0a 20 20 73 65 74 20 76 20 5b 63 61  .3 {.  set v [ca
0dc0: 74 63 68 20 7b 65 78 65 63 73 71 6c 20 7b 0a 20  tch {execsql {. 
0dd0: 20 20 20 53 45 4c 45 43 54 20 44 49 53 54 49 4e     SELECT DISTIN
0de0: 43 54 20 6c 6f 67 20 46 52 4f 4d 20 74 31 20 4f  CT log FROM t1 O
0df0: 52 44 45 52 20 42 59 20 6c 6f 67 0a 20 20 20 20  RDER BY log.    
0e00: 55 4e 49 4f 4e 0a 20 20 20 20 53 45 4c 45 43 54  UNION.    SELECT
0e10: 20 6e 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45   n FROM t1 WHERE
0e20: 20 6c 6f 67 3d 33 0a 20 20 20 20 4f 52 44 45 52   log=3.    ORDER
0e30: 20 42 59 20 6c 6f 67 3b 0a 20 20 7d 7d 20 6d 73   BY log;.  }} ms
0e40: 67 5d 0a 20 20 6c 61 70 70 65 6e 64 20 76 20 24  g].  lappend v $
0e50: 6d 73 67 0a 7d 20 7b 31 20 7b 4f 52 44 45 52 20  msg.} {1 {ORDER 
0e60: 42 59 20 63 6c 61 75 73 65 20 73 68 6f 75 6c 64  BY clause should
0e70: 20 63 6f 6d 65 20 61 66 74 65 72 20 55 4e 49 4f   come after UNIO
0e80: 4e 20 6e 6f 74 20 62 65 66 6f 72 65 7d 7d 0a 64  N not before}}.d
0e90: 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 32  o_test select4-2
0ea0: 2e 34 20 7b 0a 20 20 73 65 74 20 76 20 5b 63 61  .4 {.  set v [ca
0eb0: 74 63 68 20 7b 65 78 65 63 73 71 6c 20 7b 0a 20  tch {execsql {. 
0ec0: 20 20 20 53 45 4c 45 43 54 20 30 20 4f 52 44 45     SELECT 0 ORDE
0ed0: 52 20 42 59 20 28 53 45 4c 45 43 54 20 30 29 20  R BY (SELECT 0) 
0ee0: 55 4e 49 4f 4e 20 53 45 4c 45 43 54 20 30 3b 0a  UNION SELECT 0;.
0ef0: 20 20 7d 7d 20 6d 73 67 5d 0a 20 20 6c 61 70 70    }} msg].  lapp
0f00: 65 6e 64 20 76 20 24 6d 73 67 0a 7d 20 7b 31 20  end v $msg.} {1 
0f10: 7b 4f 52 44 45 52 20 42 59 20 63 6c 61 75 73 65  {ORDER BY clause
0f20: 20 73 68 6f 75 6c 64 20 63 6f 6d 65 20 61 66 74   should come aft
0f30: 65 72 20 55 4e 49 4f 4e 20 6e 6f 74 20 62 65 66  er UNION not bef
0f40: 6f 72 65 7d 7d 0a 64 6f 5f 65 78 65 63 73 71 6c  ore}}.do_execsql
0f50: 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 32 2e  _test select4-2.
0f60: 35 20 7b 0a 20 20 53 45 4c 45 43 54 20 31 32 33  5 {.  SELECT 123
0f70: 20 41 53 20 78 20 4f 52 44 45 52 20 42 59 20 28   AS x ORDER BY (
0f80: 53 45 4c 45 43 54 20 78 20 4f 52 44 45 52 20 42  SELECT x ORDER B
0f90: 59 20 31 29 3b 0a 7d 20 7b 31 32 33 7d 0a 0a 23  Y 1);.} {123}..#
0fa0: 20 45 78 63 65 70 74 20 6f 70 65 72 61 74 6f 72   Except operator
0fb0: 0a 23 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63  .#.do_test selec
0fc0: 74 34 2d 33 2e 31 2e 31 20 7b 0a 20 20 65 78 65  t4-3.1.1 {.  exe
0fd0: 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43  csql {.    SELEC
0fe0: 54 20 44 49 53 54 49 4e 43 54 20 6c 6f 67 20 46  T DISTINCT log F
0ff0: 52 4f 4d 20 74 31 0a 20 20 20 20 45 58 43 45 50  ROM t1.    EXCEP
1000: 54 0a 20 20 20 20 53 45 4c 45 43 54 20 6e 20 46  T.    SELECT n F
1010: 52 4f 4d 20 74 31 20 57 48 45 52 45 20 6c 6f 67  ROM t1 WHERE log
1020: 3d 33 0a 20 20 20 20 4f 52 44 45 52 20 42 59 20  =3.    ORDER BY 
1030: 6c 6f 67 3b 0a 20 20 7d 0a 7d 20 7b 30 20 31 20  log;.  }.} {0 1 
1040: 32 20 33 20 34 7d 0a 64 6f 5f 74 65 73 74 20 73  2 3 4}.do_test s
1050: 65 6c 65 63 74 34 2d 33 2e 31 2e 32 20 7b 0a 20  elect4-3.1.2 {. 
1060: 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 43   execsql {.    C
1070: 52 45 41 54 45 20 54 41 42 4c 45 20 74 32 20 41  REATE TABLE t2 A
1080: 53 20 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20  S .      SELECT 
1090: 44 49 53 54 49 4e 43 54 20 6c 6f 67 20 46 52 4f  DISTINCT log FRO
10a0: 4d 20 74 31 0a 20 20 20 20 20 20 45 58 43 45 50  M t1.      EXCEP
10b0: 54 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20 6e  T.      SELECT n
10c0: 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20 6c   FROM t1 WHERE l
10d0: 6f 67 3d 33 0a 20 20 20 20 20 20 4f 52 44 45 52  og=3.      ORDER
10e0: 20 42 59 20 6c 6f 67 3b 0a 20 20 20 20 53 45 4c   BY log;.    SEL
10f0: 45 43 54 20 2a 20 46 52 4f 4d 20 74 32 3b 0a 20  ECT * FROM t2;. 
1100: 20 7d 0a 7d 20 7b 30 20 31 20 32 20 33 20 34 7d   }.} {0 1 2 3 4}
1110: 0a 65 78 65 63 73 71 6c 20 7b 44 52 4f 50 20 54  .execsql {DROP T
1120: 41 42 4c 45 20 74 32 7d 0a 64 6f 5f 74 65 73 74  ABLE t2}.do_test
1130: 20 73 65 6c 65 63 74 34 2d 33 2e 31 2e 33 20 7b   select4-3.1.3 {
1140: 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20  .  execsql {.   
1150: 20 43 52 45 41 54 45 20 54 41 42 4c 45 20 74 32   CREATE TABLE t2
1160: 20 41 53 20 0a 20 20 20 20 20 20 53 45 4c 45 43   AS .      SELEC
1170: 54 20 44 49 53 54 49 4e 43 54 20 6c 6f 67 20 46  T DISTINCT log F
1180: 52 4f 4d 20 74 31 0a 20 20 20 20 20 20 45 58 43  ROM t1.      EXC
1190: 45 50 54 0a 20 20 20 20 20 20 53 45 4c 45 43 54  EPT.      SELECT
11a0: 20 6e 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45   n FROM t1 WHERE
11b0: 20 6c 6f 67 3d 33 0a 20 20 20 20 20 20 4f 52 44   log=3.      ORD
11c0: 45 52 20 42 59 20 6c 6f 67 20 44 45 53 43 3b 0a  ER BY log DESC;.
11d0: 20 20 20 20 53 45 4c 45 43 54 20 2a 20 46 52 4f      SELECT * FRO
11e0: 4d 20 74 32 3b 0a 20 20 7d 0a 7d 20 7b 34 20 33  M t2;.  }.} {4 3
11f0: 20 32 20 31 20 30 7d 0a 65 78 65 63 73 71 6c 20   2 1 0}.execsql 
1200: 7b 44 52 4f 50 20 54 41 42 4c 45 20 74 32 7d 0a  {DROP TABLE t2}.
1210: 69 66 63 61 70 61 62 6c 65 20 73 75 62 71 75 65  ifcapable subque
1220: 72 79 20 7b 0a 20 20 64 6f 5f 74 65 73 74 20 73  ry {.  do_test s
1230: 65 6c 65 63 74 34 2d 33 2e 32 20 7b 0a 20 20 20  elect4-3.2 {.   
1240: 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 20   execsql {.     
1250: 20 53 45 4c 45 43 54 20 6c 6f 67 20 46 52 4f 4d   SELECT log FROM
1260: 20 74 31 20 57 48 45 52 45 20 6e 20 49 4e 20 0a   t1 WHERE n IN .
1270: 20 20 20 20 20 20 20 20 28 53 45 4c 45 43 54 20          (SELECT 
1280: 44 49 53 54 49 4e 43 54 20 6c 6f 67 20 46 52 4f  DISTINCT log FRO
1290: 4d 20 74 31 20 45 58 43 45 50 54 0a 20 20 20 20  M t1 EXCEPT.    
12a0: 20 20 20 20 20 53 45 4c 45 43 54 20 6e 20 46 52       SELECT n FR
12b0: 4f 4d 20 74 31 20 57 48 45 52 45 20 6c 6f 67 3d  OM t1 WHERE log=
12c0: 33 29 0a 20 20 20 20 20 20 4f 52 44 45 52 20 42  3).      ORDER B
12d0: 59 20 6c 6f 67 3b 0a 20 20 20 20 7d 0a 20 20 7d  Y log;.    }.  }
12e0: 20 7b 30 20 31 20 32 20 32 7d 0a 7d 0a 64 6f 5f   {0 1 2 2}.}.do_
12f0: 74 65 73 74 20 73 65 6c 65 63 74 34 2d 33 2e 33  test select4-3.3
1300: 20 7b 0a 20 20 73 65 74 20 76 20 5b 63 61 74 63   {.  set v [catc
1310: 68 20 7b 65 78 65 63 73 71 6c 20 7b 0a 20 20 20  h {execsql {.   
1320: 20 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54   SELECT DISTINCT
1330: 20 6c 6f 67 20 46 52 4f 4d 20 74 31 20 4f 52 44   log FROM t1 ORD
1340: 45 52 20 42 59 20 6c 6f 67 0a 20 20 20 20 45 58  ER BY log.    EX
1350: 43 45 50 54 0a 20 20 20 20 53 45 4c 45 43 54 20  CEPT.    SELECT 
1360: 6e 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20  n FROM t1 WHERE 
1370: 6c 6f 67 3d 33 0a 20 20 20 20 4f 52 44 45 52 20  log=3.    ORDER 
1380: 42 59 20 6c 6f 67 3b 0a 20 20 7d 7d 20 6d 73 67  BY log;.  }} msg
1390: 5d 0a 20 20 6c 61 70 70 65 6e 64 20 76 20 24 6d  ].  lappend v $m
13a0: 73 67 0a 7d 20 7b 31 20 7b 4f 52 44 45 52 20 42  sg.} {1 {ORDER B
13b0: 59 20 63 6c 61 75 73 65 20 73 68 6f 75 6c 64 20  Y clause should 
13c0: 63 6f 6d 65 20 61 66 74 65 72 20 45 58 43 45 50  come after EXCEP
13d0: 54 20 6e 6f 74 20 62 65 66 6f 72 65 7d 7d 0a 0a  T not before}}..
13e0: 23 20 49 6e 74 65 72 73 65 63 74 20 6f 70 65 72  # Intersect oper
13f0: 61 74 6f 72 0a 23 0a 64 6f 5f 74 65 73 74 20 73  ator.#.do_test s
1400: 65 6c 65 63 74 34 2d 34 2e 31 2e 31 20 7b 0a 20  elect4-4.1.1 {. 
1410: 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53   execsql {.    S
1420: 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54 20 6c  ELECT DISTINCT l
1430: 6f 67 20 46 52 4f 4d 20 74 31 0a 20 20 20 20 49  og FROM t1.    I
1440: 4e 54 45 52 53 45 43 54 0a 20 20 20 20 53 45 4c  NTERSECT.    SEL
1450: 45 43 54 20 6e 20 46 52 4f 4d 20 74 31 20 57 48  ECT n FROM t1 WH
1460: 45 52 45 20 6c 6f 67 3d 33 0a 20 20 20 20 4f 52  ERE log=3.    OR
1470: 44 45 52 20 42 59 20 6c 6f 67 3b 0a 20 20 7d 0a  DER BY log;.  }.
1480: 7d 20 7b 35 7d 0a 0a 64 6f 5f 74 65 73 74 20 73  } {5}..do_test s
1490: 65 6c 65 63 74 34 2d 34 2e 31 2e 32 20 7b 0a 20  elect4-4.1.2 {. 
14a0: 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53   execsql {.    S
14b0: 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54 20 6c  ELECT DISTINCT l
14c0: 6f 67 20 46 52 4f 4d 20 74 31 0a 20 20 20 20 55  og FROM t1.    U
14d0: 4e 49 4f 4e 20 41 4c 4c 0a 20 20 20 20 53 45 4c  NION ALL.    SEL
14e0: 45 43 54 20 36 0a 20 20 20 20 49 4e 54 45 52 53  ECT 6.    INTERS
14f0: 45 43 54 0a 20 20 20 20 53 45 4c 45 43 54 20 6e  ECT.    SELECT n
1500: 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20 6c   FROM t1 WHERE l
1510: 6f 67 3d 33 0a 20 20 20 20 4f 52 44 45 52 20 42  og=3.    ORDER B
1520: 59 20 74 31 2e 6c 6f 67 3b 0a 20 20 7d 0a 7d 20  Y t1.log;.  }.} 
1530: 7b 35 20 36 7d 0a 0a 64 6f 5f 74 65 73 74 20 73  {5 6}..do_test s
1540: 65 6c 65 63 74 34 2d 34 2e 31 2e 33 20 7b 0a 20  elect4-4.1.3 {. 
1550: 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 43   execsql {.    C
1560: 52 45 41 54 45 20 54 41 42 4c 45 20 74 32 20 41  REATE TABLE t2 A
1570: 53 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20 44  S.      SELECT D
1580: 49 53 54 49 4e 43 54 20 6c 6f 67 20 46 52 4f 4d  ISTINCT log FROM
1590: 20 74 31 20 55 4e 49 4f 4e 20 41 4c 4c 20 53 45   t1 UNION ALL SE
15a0: 4c 45 43 54 20 36 0a 20 20 20 20 20 20 49 4e 54  LECT 6.      INT
15b0: 45 52 53 45 43 54 0a 20 20 20 20 20 20 53 45 4c  ERSECT.      SEL
15c0: 45 43 54 20 6e 20 46 52 4f 4d 20 74 31 20 57 48  ECT n FROM t1 WH
15d0: 45 52 45 20 6c 6f 67 3d 33 0a 20 20 20 20 20 20  ERE log=3.      
15e0: 4f 52 44 45 52 20 42 59 20 6c 6f 67 3b 0a 20 20  ORDER BY log;.  
15f0: 20 20 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20    SELECT * FROM 
1600: 74 32 3b 0a 20 20 7d 0a 7d 20 7b 35 20 36 7d 0a  t2;.  }.} {5 6}.
1610: 65 78 65 63 73 71 6c 20 7b 44 52 4f 50 20 54 41  execsql {DROP TA
1620: 42 4c 45 20 74 32 7d 0a 64 6f 5f 74 65 73 74 20  BLE t2}.do_test 
1630: 73 65 6c 65 63 74 34 2d 34 2e 31 2e 34 20 7b 0a  select4-4.1.4 {.
1640: 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20    execsql {.    
1650: 43 52 45 41 54 45 20 54 41 42 4c 45 20 74 32 20  CREATE TABLE t2 
1660: 41 53 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20  AS.      SELECT 
1670: 44 49 53 54 49 4e 43 54 20 6c 6f 67 20 46 52 4f  DISTINCT log FRO
1680: 4d 20 74 31 20 55 4e 49 4f 4e 20 41 4c 4c 20 53  M t1 UNION ALL S
1690: 45 4c 45 43 54 20 36 0a 20 20 20 20 20 20 49 4e  ELECT 6.      IN
16a0: 54 45 52 53 45 43 54 0a 20 20 20 20 20 20 53 45  TERSECT.      SE
16b0: 4c 45 43 54 20 6e 20 46 52 4f 4d 20 74 31 20 57  LECT n FROM t1 W
16c0: 48 45 52 45 20 6c 6f 67 3d 33 0a 20 20 20 20 20  HERE log=3.     
16d0: 20 4f 52 44 45 52 20 42 59 20 6c 6f 67 20 44 45   ORDER BY log DE
16e0: 53 43 3b 0a 20 20 20 20 53 45 4c 45 43 54 20 2a  SC;.    SELECT *
16f0: 20 46 52 4f 4d 20 74 32 3b 0a 20 20 7d 0a 7d 20   FROM t2;.  }.} 
1700: 7b 36 20 35 7d 0a 65 78 65 63 73 71 6c 20 7b 44  {6 5}.execsql {D
1710: 52 4f 50 20 54 41 42 4c 45 20 74 32 7d 0a 69 66  ROP TABLE t2}.if
1720: 63 61 70 61 62 6c 65 20 73 75 62 71 75 65 72 79  capable subquery
1730: 20 7b 0a 20 20 64 6f 5f 74 65 73 74 20 73 65 6c   {.  do_test sel
1740: 65 63 74 34 2d 34 2e 32 20 7b 0a 20 20 20 20 65  ect4-4.2 {.    e
1750: 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 20 20 53  xecsql {.      S
1760: 45 4c 45 43 54 20 6c 6f 67 20 46 52 4f 4d 20 74  ELECT log FROM t
1770: 31 20 57 48 45 52 45 20 6e 20 49 4e 20 0a 20 20  1 WHERE n IN .  
1780: 20 20 20 20 20 20 28 53 45 4c 45 43 54 20 44 49        (SELECT DI
1790: 53 54 49 4e 43 54 20 6c 6f 67 20 46 52 4f 4d 20  STINCT log FROM 
17a0: 74 31 20 49 4e 54 45 52 53 45 43 54 0a 20 20 20  t1 INTERSECT.   
17b0: 20 20 20 20 20 20 53 45 4c 45 43 54 20 6e 20 46        SELECT n F
17c0: 52 4f 4d 20 74 31 20 57 48 45 52 45 20 6c 6f 67  ROM t1 WHERE log
17d0: 3d 33 29 0a 20 20 20 20 20 20 4f 52 44 45 52 20  =3).      ORDER 
17e0: 42 59 20 6c 6f 67 3b 0a 20 20 20 20 7d 0a 20 20  BY log;.    }.  
17f0: 7d 20 7b 33 7d 0a 7d 0a 64 6f 5f 74 65 73 74 20  } {3}.}.do_test 
1800: 73 65 6c 65 63 74 34 2d 34 2e 33 20 7b 0a 20 20  select4-4.3 {.  
1810: 73 65 74 20 76 20 5b 63 61 74 63 68 20 7b 65 78  set v [catch {ex
1820: 65 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45  ecsql {.    SELE
1830: 43 54 20 44 49 53 54 49 4e 43 54 20 6c 6f 67 20  CT DISTINCT log 
1840: 46 52 4f 4d 20 74 31 20 4f 52 44 45 52 20 42 59  FROM t1 ORDER BY
1850: 20 6c 6f 67 0a 20 20 20 20 49 4e 54 45 52 53 45   log.    INTERSE
1860: 43 54 0a 20 20 20 20 53 45 4c 45 43 54 20 6e 20  CT.    SELECT n 
1870: 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20 6c 6f  FROM t1 WHERE lo
1880: 67 3d 33 0a 20 20 20 20 4f 52 44 45 52 20 42 59  g=3.    ORDER BY
1890: 20 6c 6f 67 3b 0a 20 20 7d 7d 20 6d 73 67 5d 0a   log;.  }} msg].
18a0: 20 20 6c 61 70 70 65 6e 64 20 76 20 24 6d 73 67    lappend v $msg
18b0: 0a 7d 20 7b 31 20 7b 4f 52 44 45 52 20 42 59 20  .} {1 {ORDER BY 
18c0: 63 6c 61 75 73 65 20 73 68 6f 75 6c 64 20 63 6f  clause should co
18d0: 6d 65 20 61 66 74 65 72 20 49 4e 54 45 52 53 45  me after INTERSE
18e0: 43 54 20 6e 6f 74 20 62 65 66 6f 72 65 7d 7d 0a  CT not before}}.
18f0: 64 6f 5f 63 61 74 63 68 73 71 6c 5f 74 65 73 74  do_catchsql_test
1900: 20 73 65 6c 65 63 74 34 2d 34 2e 34 20 7b 0a 20   select4-4.4 {. 
1910: 20 53 45 4c 45 43 54 20 33 20 49 4e 20 28 0a 20   SELECT 3 IN (. 
1920: 20 20 20 53 45 4c 45 43 54 20 30 20 4f 52 44 45     SELECT 0 ORDE
1930: 52 20 42 59 20 31 0a 20 20 20 20 49 4e 54 45 52  R BY 1.    INTER
1940: 53 45 43 54 0a 20 20 20 20 53 45 4c 45 43 54 20  SECT.    SELECT 
1950: 31 0a 20 20 20 20 49 4e 54 45 52 53 45 43 54 20  1.    INTERSECT 
1960: 0a 20 20 20 20 53 45 4c 45 43 54 20 32 0a 20 20  .    SELECT 2.  
1970: 20 20 4f 52 44 45 52 20 42 59 20 31 0a 20 20 29    ORDER BY 1.  )
1980: 3b 0a 7d 20 7b 31 20 7b 4f 52 44 45 52 20 42 59  ;.} {1 {ORDER BY
1990: 20 63 6c 61 75 73 65 20 73 68 6f 75 6c 64 20 63   clause should c
19a0: 6f 6d 65 20 61 66 74 65 72 20 49 4e 54 45 52 53  ome after INTERS
19b0: 45 43 54 20 6e 6f 74 20 62 65 66 6f 72 65 7d 7d  ECT not before}}
19c0: 0a 0a 23 20 56 61 72 69 6f 75 73 20 65 72 72 6f  ..# Various erro
19d0: 72 20 6d 65 73 73 61 67 65 73 20 77 68 69 6c 65  r messages while
19e0: 20 70 72 6f 63 65 73 73 69 6e 67 20 55 4e 49 4f   processing UNIO
19f0: 4e 20 6f 72 20 49 4e 54 45 52 53 45 43 54 0a 23  N or INTERSECT.#
1a00: 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 34  .do_test select4
1a10: 2d 35 2e 31 20 7b 0a 20 20 73 65 74 20 76 20 5b  -5.1 {.  set v [
1a20: 63 61 74 63 68 20 7b 65 78 65 63 73 71 6c 20 7b  catch {execsql {
1a30: 0a 20 20 20 20 53 45 4c 45 43 54 20 44 49 53 54  .    SELECT DIST
1a40: 49 4e 43 54 20 6c 6f 67 20 46 52 4f 4d 20 74 32  INCT log FROM t2
1a50: 0a 20 20 20 20 55 4e 49 4f 4e 20 41 4c 4c 0a 20  .    UNION ALL. 
1a60: 20 20 20 53 45 4c 45 43 54 20 6e 20 46 52 4f 4d     SELECT n FROM
1a70: 20 74 31 20 57 48 45 52 45 20 6c 6f 67 3d 33 0a   t1 WHERE log=3.
1a80: 20 20 20 20 4f 52 44 45 52 20 42 59 20 6c 6f 67      ORDER BY log
1a90: 3b 0a 20 20 7d 7d 20 6d 73 67 5d 0a 20 20 6c 61  ;.  }} msg].  la
1aa0: 70 70 65 6e 64 20 76 20 24 6d 73 67 0a 7d 20 7b  ppend v $msg.} {
1ab0: 31 20 7b 6e 6f 20 73 75 63 68 20 74 61 62 6c 65  1 {no such table
1ac0: 3a 20 74 32 7d 7d 0a 64 6f 5f 74 65 73 74 20 73  : t2}}.do_test s
1ad0: 65 6c 65 63 74 34 2d 35 2e 32 20 7b 0a 20 20 73  elect4-5.2 {.  s
1ae0: 65 74 20 76 20 5b 63 61 74 63 68 20 7b 65 78 65  et v [catch {exe
1af0: 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43  csql {.    SELEC
1b00: 54 20 44 49 53 54 49 4e 43 54 20 6c 6f 67 20 41  T DISTINCT log A
1b10: 53 20 22 78 79 7a 7a 79 22 20 46 52 4f 4d 20 74  S "xyzzy" FROM t
1b20: 31 0a 20 20 20 20 55 4e 49 4f 4e 20 41 4c 4c 0a  1.    UNION ALL.
1b30: 20 20 20 20 53 45 4c 45 43 54 20 6e 20 46 52 4f      SELECT n FRO
1b40: 4d 20 74 31 20 57 48 45 52 45 20 6c 6f 67 3d 33  M t1 WHERE log=3
1b50: 0a 20 20 20 20 4f 52 44 45 52 20 42 59 20 78 79  .    ORDER BY xy
1b60: 7a 7a 79 3b 0a 20 20 7d 7d 20 6d 73 67 5d 0a 20  zzy;.  }} msg]. 
1b70: 20 6c 61 70 70 65 6e 64 20 76 20 24 6d 73 67 0a   lappend v $msg.
1b80: 7d 20 7b 30 20 7b 30 20 31 20 32 20 33 20 34 20  } {0 {0 1 2 3 4 
1b90: 35 20 35 20 36 20 37 20 38 7d 7d 0a 64 6f 5f 74  5 5 6 7 8}}.do_t
1ba0: 65 73 74 20 73 65 6c 65 63 74 34 2d 35 2e 32 62  est select4-5.2b
1bb0: 20 7b 0a 20 20 73 65 74 20 76 20 5b 63 61 74 63   {.  set v [catc
1bc0: 68 20 7b 65 78 65 63 73 71 6c 20 7b 0a 20 20 20  h {execsql {.   
1bd0: 20 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54   SELECT DISTINCT
1be0: 20 6c 6f 67 20 41 53 20 78 79 7a 7a 79 20 46 52   log AS xyzzy FR
1bf0: 4f 4d 20 74 31 0a 20 20 20 20 55 4e 49 4f 4e 20  OM t1.    UNION 
1c00: 41 4c 4c 0a 20 20 20 20 53 45 4c 45 43 54 20 6e  ALL.    SELECT n
1c10: 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20 6c   FROM t1 WHERE l
1c20: 6f 67 3d 33 0a 20 20 20 20 4f 52 44 45 52 20 42  og=3.    ORDER B
1c30: 59 20 22 78 79 7a 7a 79 22 3b 0a 20 20 7d 7d 20  Y "xyzzy";.  }} 
1c40: 6d 73 67 5d 0a 20 20 6c 61 70 70 65 6e 64 20 76  msg].  lappend v
1c50: 20 24 6d 73 67 0a 7d 20 7b 30 20 7b 30 20 31 20   $msg.} {0 {0 1 
1c60: 32 20 33 20 34 20 35 20 35 20 36 20 37 20 38 7d  2 3 4 5 5 6 7 8}
1c70: 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74  }.do_test select
1c80: 34 2d 35 2e 32 63 20 7b 0a 20 20 73 65 74 20 76  4-5.2c {.  set v
1c90: 20 5b 63 61 74 63 68 20 7b 65 78 65 63 73 71 6c   [catch {execsql
1ca0: 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 44 49   {.    SELECT DI
1cb0: 53 54 49 4e 43 54 20 6c 6f 67 20 46 52 4f 4d 20  STINCT log FROM 
1cc0: 74 31 0a 20 20 20 20 55 4e 49 4f 4e 20 41 4c 4c  t1.    UNION ALL
1cd0: 0a 20 20 20 20 53 45 4c 45 43 54 20 6e 20 46 52  .    SELECT n FR
1ce0: 4f 4d 20 74 31 20 57 48 45 52 45 20 6c 6f 67 3d  OM t1 WHERE log=
1cf0: 33 0a 20 20 20 20 4f 52 44 45 52 20 42 59 20 22  3.    ORDER BY "
1d00: 78 79 7a 7a 79 22 3b 0a 20 20 7d 7d 20 6d 73 67  xyzzy";.  }} msg
1d10: 5d 0a 20 20 6c 61 70 70 65 6e 64 20 76 20 24 6d  ].  lappend v $m
1d20: 73 67 0a 7d 20 7b 31 20 7b 31 73 74 20 4f 52 44  sg.} {1 {1st ORD
1d30: 45 52 20 42 59 20 74 65 72 6d 20 64 6f 65 73 20  ER BY term does 
1d40: 6e 6f 74 20 6d 61 74 63 68 20 61 6e 79 20 63 6f  not match any co
1d50: 6c 75 6d 6e 20 69 6e 20 74 68 65 20 72 65 73 75  lumn in the resu
1d60: 6c 74 20 73 65 74 7d 7d 0a 64 6f 5f 74 65 73 74  lt set}}.do_test
1d70: 20 73 65 6c 65 63 74 34 2d 35 2e 32 64 20 7b 0a   select4-5.2d {.
1d80: 20 20 73 65 74 20 76 20 5b 63 61 74 63 68 20 7b    set v [catch {
1d90: 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53 45  execsql {.    SE
1da0: 4c 45 43 54 20 44 49 53 54 49 4e 43 54 20 6c 6f  LECT DISTINCT lo
1db0: 67 20 46 52 4f 4d 20 74 31 0a 20 20 20 20 49 4e  g FROM t1.    IN
1dc0: 54 45 52 53 45 43 54 0a 20 20 20 20 53 45 4c 45  TERSECT.    SELE
1dd0: 43 54 20 6e 20 46 52 4f 4d 20 74 31 20 57 48 45  CT n FROM t1 WHE
1de0: 52 45 20 6c 6f 67 3d 33 0a 20 20 20 20 4f 52 44  RE log=3.    ORD
1df0: 45 52 20 42 59 20 22 78 79 7a 7a 79 22 3b 0a 20  ER BY "xyzzy";. 
1e00: 20 7d 7d 20 6d 73 67 5d 0a 20 20 6c 61 70 70 65   }} msg].  lappe
1e10: 6e 64 20 76 20 24 6d 73 67 0a 7d 20 7b 31 20 7b  nd v $msg.} {1 {
1e20: 31 73 74 20 4f 52 44 45 52 20 42 59 20 74 65 72  1st ORDER BY ter
1e30: 6d 20 64 6f 65 73 20 6e 6f 74 20 6d 61 74 63 68  m does not match
1e40: 20 61 6e 79 20 63 6f 6c 75 6d 6e 20 69 6e 20 74   any column in t
1e50: 68 65 20 72 65 73 75 6c 74 20 73 65 74 7d 7d 0a  he result set}}.
1e60: 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d  do_test select4-
1e70: 35 2e 32 65 20 7b 0a 20 20 73 65 74 20 76 20 5b  5.2e {.  set v [
1e80: 63 61 74 63 68 20 7b 65 78 65 63 73 71 6c 20 7b  catch {execsql {
1e90: 0a 20 20 20 20 53 45 4c 45 43 54 20 44 49 53 54  .    SELECT DIST
1ea0: 49 4e 43 54 20 6c 6f 67 20 46 52 4f 4d 20 74 31  INCT log FROM t1
1eb0: 0a 20 20 20 20 55 4e 49 4f 4e 20 41 4c 4c 0a 20  .    UNION ALL. 
1ec0: 20 20 20 53 45 4c 45 43 54 20 6e 20 46 52 4f 4d     SELECT n FROM
1ed0: 20 74 31 20 57 48 45 52 45 20 6c 6f 67 3d 33 0a   t1 WHERE log=3.
1ee0: 20 20 20 20 4f 52 44 45 52 20 42 59 20 6e 3b 0a      ORDER BY n;.
1ef0: 20 20 7d 7d 20 6d 73 67 5d 0a 20 20 6c 61 70 70    }} msg].  lapp
1f00: 65 6e 64 20 76 20 24 6d 73 67 0a 7d 20 7b 30 20  end v $msg.} {0 
1f10: 7b 30 20 31 20 32 20 33 20 34 20 35 20 35 20 36  {0 1 2 3 4 5 5 6
1f20: 20 37 20 38 7d 7d 0a 64 6f 5f 74 65 73 74 20 73   7 8}}.do_test s
1f30: 65 6c 65 63 74 34 2d 35 2e 32 66 20 7b 0a 20 20  elect4-5.2f {.  
1f40: 63 61 74 63 68 73 71 6c 20 7b 0a 20 20 20 20 53  catchsql {.    S
1f50: 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54 20 6c  ELECT DISTINCT l
1f60: 6f 67 20 46 52 4f 4d 20 74 31 0a 20 20 20 20 55  og FROM t1.    U
1f70: 4e 49 4f 4e 20 41 4c 4c 0a 20 20 20 20 53 45 4c  NION ALL.    SEL
1f80: 45 43 54 20 6e 20 46 52 4f 4d 20 74 31 20 57 48  ECT n FROM t1 WH
1f90: 45 52 45 20 6c 6f 67 3d 33 0a 20 20 20 20 4f 52  ERE log=3.    OR
1fa0: 44 45 52 20 42 59 20 6c 6f 67 3b 0a 20 20 7d 0a  DER BY log;.  }.
1fb0: 7d 20 7b 30 20 7b 30 20 31 20 32 20 33 20 34 20  } {0 {0 1 2 3 4 
1fc0: 35 20 35 20 36 20 37 20 38 7d 7d 0a 64 6f 5f 74  5 5 6 7 8}}.do_t
1fd0: 65 73 74 20 73 65 6c 65 63 74 34 2d 35 2e 32 67  est select4-5.2g
1fe0: 20 7b 0a 20 20 63 61 74 63 68 73 71 6c 20 7b 0a   {.  catchsql {.
1ff0: 20 20 20 20 53 45 4c 45 43 54 20 44 49 53 54 49      SELECT DISTI
2000: 4e 43 54 20 6c 6f 67 20 46 52 4f 4d 20 74 31 0a  NCT log FROM t1.
2010: 20 20 20 20 55 4e 49 4f 4e 20 41 4c 4c 0a 20 20      UNION ALL.  
2020: 20 20 53 45 4c 45 43 54 20 6e 20 46 52 4f 4d 20    SELECT n FROM 
2030: 74 31 20 57 48 45 52 45 20 6c 6f 67 3d 33 0a 20  t1 WHERE log=3. 
2040: 20 20 20 4f 52 44 45 52 20 42 59 20 31 3b 0a 20     ORDER BY 1;. 
2050: 20 7d 0a 7d 20 7b 30 20 7b 30 20 31 20 32 20 33   }.} {0 {0 1 2 3
2060: 20 34 20 35 20 35 20 36 20 37 20 38 7d 7d 0a 64   4 5 5 6 7 8}}.d
2070: 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 35  o_test select4-5
2080: 2e 32 68 20 7b 0a 20 20 63 61 74 63 68 73 71 6c  .2h {.  catchsql
2090: 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 44 49   {.    SELECT DI
20a0: 53 54 49 4e 43 54 20 6c 6f 67 20 46 52 4f 4d 20  STINCT log FROM 
20b0: 74 31 0a 20 20 20 20 55 4e 49 4f 4e 20 41 4c 4c  t1.    UNION ALL
20c0: 0a 20 20 20 20 53 45 4c 45 43 54 20 6e 20 46 52  .    SELECT n FR
20d0: 4f 4d 20 74 31 20 57 48 45 52 45 20 6c 6f 67 3d  OM t1 WHERE log=
20e0: 33 0a 20 20 20 20 4f 52 44 45 52 20 42 59 20 32  3.    ORDER BY 2
20f0: 3b 0a 20 20 7d 0a 7d 20 7b 31 20 7b 31 73 74 20  ;.  }.} {1 {1st 
2100: 4f 52 44 45 52 20 42 59 20 74 65 72 6d 20 6f 75  ORDER BY term ou
2110: 74 20 6f 66 20 72 61 6e 67 65 20 2d 20 73 68 6f  t of range - sho
2120: 75 6c 64 20 62 65 20 62 65 74 77 65 65 6e 20 31  uld be between 1
2130: 20 61 6e 64 20 31 7d 7d 0a 64 6f 5f 74 65 73 74   and 1}}.do_test
2140: 20 73 65 6c 65 63 74 34 2d 35 2e 32 69 20 7b 0a   select4-5.2i {.
2150: 20 20 63 61 74 63 68 73 71 6c 20 7b 0a 20 20 20    catchsql {.   
2160: 20 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54   SELECT DISTINCT
2170: 20 31 2c 20 6c 6f 67 20 46 52 4f 4d 20 74 31 0a   1, log FROM t1.
2180: 20 20 20 20 55 4e 49 4f 4e 20 41 4c 4c 0a 20 20      UNION ALL.  
2190: 20 20 53 45 4c 45 43 54 20 32 2c 20 6e 20 46 52    SELECT 2, n FR
21a0: 4f 4d 20 74 31 20 57 48 45 52 45 20 6c 6f 67 3d  OM t1 WHERE log=
21b0: 33 0a 20 20 20 20 4f 52 44 45 52 20 42 59 20 32  3.    ORDER BY 2
21c0: 2c 20 31 3b 0a 20 20 7d 0a 7d 20 7b 30 20 7b 31  , 1;.  }.} {0 {1
21d0: 20 30 20 31 20 31 20 31 20 32 20 31 20 33 20 31   0 1 1 1 2 1 3 1
21e0: 20 34 20 31 20 35 20 32 20 35 20 32 20 36 20 32   4 1 5 2 5 2 6 2
21f0: 20 37 20 32 20 38 7d 7d 0a 64 6f 5f 74 65 73 74   7 2 8}}.do_test
2200: 20 73 65 6c 65 63 74 34 2d 35 2e 32 6a 20 7b 0a   select4-5.2j {.
2210: 20 20 63 61 74 63 68 73 71 6c 20 7b 0a 20 20 20    catchsql {.   
2220: 20 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54   SELECT DISTINCT
2230: 20 31 2c 20 6c 6f 67 20 46 52 4f 4d 20 74 31 0a   1, log FROM t1.
2240: 20 20 20 20 55 4e 49 4f 4e 20 41 4c 4c 0a 20 20      UNION ALL.  
2250: 20 20 53 45 4c 45 43 54 20 32 2c 20 6e 20 46 52    SELECT 2, n FR
2260: 4f 4d 20 74 31 20 57 48 45 52 45 20 6c 6f 67 3d  OM t1 WHERE log=
2270: 33 0a 20 20 20 20 4f 52 44 45 52 20 42 59 20 31  3.    ORDER BY 1
2280: 2c 20 32 20 44 45 53 43 3b 0a 20 20 7d 0a 7d 20  , 2 DESC;.  }.} 
2290: 7b 30 20 7b 31 20 35 20 31 20 34 20 31 20 33 20  {0 {1 5 1 4 1 3 
22a0: 31 20 32 20 31 20 31 20 31 20 30 20 32 20 38 20  1 2 1 1 1 0 2 8 
22b0: 32 20 37 20 32 20 36 20 32 20 35 7d 7d 0a 64 6f  2 7 2 6 2 5}}.do
22c0: 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 35 2e  _test select4-5.
22d0: 32 6b 20 7b 0a 20 20 63 61 74 63 68 73 71 6c 20  2k {.  catchsql 
22e0: 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 44 49 53  {.    SELECT DIS
22f0: 54 49 4e 43 54 20 31 2c 20 6c 6f 67 20 46 52 4f  TINCT 1, log FRO
2300: 4d 20 74 31 0a 20 20 20 20 55 4e 49 4f 4e 20 41  M t1.    UNION A
2310: 4c 4c 0a 20 20 20 20 53 45 4c 45 43 54 20 32 2c  LL.    SELECT 2,
2320: 20 6e 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45   n FROM t1 WHERE
2330: 20 6c 6f 67 3d 33 0a 20 20 20 20 4f 52 44 45 52   log=3.    ORDER
2340: 20 42 59 20 6e 2c 20 31 3b 0a 20 20 7d 0a 7d 20   BY n, 1;.  }.} 
2350: 7b 30 20 7b 31 20 30 20 31 20 31 20 31 20 32 20  {0 {1 0 1 1 1 2 
2360: 31 20 33 20 31 20 34 20 31 20 35 20 32 20 35 20  1 3 1 4 1 5 2 5 
2370: 32 20 36 20 32 20 37 20 32 20 38 7d 7d 0a 64 6f  2 6 2 7 2 8}}.do
2380: 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 35 2e  _test select4-5.
2390: 33 20 7b 0a 20 20 73 65 74 20 76 20 5b 63 61 74  3 {.  set v [cat
23a0: 63 68 20 7b 65 78 65 63 73 71 6c 20 7b 0a 20 20  ch {execsql {.  
23b0: 20 20 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43    SELECT DISTINC
23c0: 54 20 6c 6f 67 2c 20 6e 20 46 52 4f 4d 20 74 31  T log, n FROM t1
23d0: 0a 20 20 20 20 55 4e 49 4f 4e 20 41 4c 4c 0a 20  .    UNION ALL. 
23e0: 20 20 20 53 45 4c 45 43 54 20 6e 20 46 52 4f 4d     SELECT n FROM
23f0: 20 74 31 20 57 48 45 52 45 20 6c 6f 67 3d 33 0a   t1 WHERE log=3.
2400: 20 20 20 20 4f 52 44 45 52 20 42 59 20 6c 6f 67      ORDER BY log
2410: 3b 0a 20 20 7d 7d 20 6d 73 67 5d 0a 20 20 6c 61  ;.  }} msg].  la
2420: 70 70 65 6e 64 20 76 20 24 6d 73 67 0a 7d 20 7b  ppend v $msg.} {
2430: 31 20 7b 53 45 4c 45 43 54 73 20 74 6f 20 74 68  1 {SELECTs to th
2440: 65 20 6c 65 66 74 20 61 6e 64 20 72 69 67 68 74  e left and right
2450: 20 6f 66 20 55 4e 49 4f 4e 20 41 4c 4c 20 64 6f   of UNION ALL do
2460: 20 6e 6f 74 20 68 61 76 65 20 74 68 65 20 73 61   not have the sa
2470: 6d 65 20 6e 75 6d 62 65 72 20 6f 66 20 72 65 73  me number of res
2480: 75 6c 74 20 63 6f 6c 75 6d 6e 73 7d 7d 0a 64 6f  ult columns}}.do
2490: 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 35 2e  _test select4-5.
24a0: 33 2d 33 38 30 37 2d 31 20 7b 0a 20 20 63 61 74  3-3807-1 {.  cat
24b0: 63 68 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45  chsql {.    SELE
24c0: 43 54 20 31 20 55 4e 49 4f 4e 20 53 45 4c 45 43  CT 1 UNION SELEC
24d0: 54 20 32 2c 20 33 20 55 4e 49 4f 4e 20 53 45 4c  T 2, 3 UNION SEL
24e0: 45 43 54 20 34 2c 20 35 20 4f 52 44 45 52 20 42  ECT 4, 5 ORDER B
24f0: 59 20 31 3b 0a 20 20 7d 0a 7d 20 7b 31 20 7b 53  Y 1;.  }.} {1 {S
2500: 45 4c 45 43 54 73 20 74 6f 20 74 68 65 20 6c 65  ELECTs to the le
2510: 66 74 20 61 6e 64 20 72 69 67 68 74 20 6f 66 20  ft and right of 
2520: 55 4e 49 4f 4e 20 64 6f 20 6e 6f 74 20 68 61 76  UNION do not hav
2530: 65 20 74 68 65 20 73 61 6d 65 20 6e 75 6d 62 65  e the same numbe
2540: 72 20 6f 66 20 72 65 73 75 6c 74 20 63 6f 6c 75  r of result colu
2550: 6d 6e 73 7d 7d 0a 64 6f 5f 74 65 73 74 20 73 65  mns}}.do_test se
2560: 6c 65 63 74 34 2d 35 2e 34 20 7b 0a 20 20 73 65  lect4-5.4 {.  se
2570: 74 20 76 20 5b 63 61 74 63 68 20 7b 65 78 65 63  t v [catch {exec
2580: 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54  sql {.    SELECT
2590: 20 6c 6f 67 20 46 52 4f 4d 20 74 31 20 57 48 45   log FROM t1 WHE
25a0: 52 45 20 6e 3d 32 0a 20 20 20 20 55 4e 49 4f 4e  RE n=2.    UNION
25b0: 20 41 4c 4c 0a 20 20 20 20 53 45 4c 45 43 54 20   ALL.    SELECT 
25c0: 6c 6f 67 20 46 52 4f 4d 20 74 31 20 57 48 45 52  log FROM t1 WHER
25d0: 45 20 6e 3d 33 0a 20 20 20 20 55 4e 49 4f 4e 20  E n=3.    UNION 
25e0: 41 4c 4c 0a 20 20 20 20 53 45 4c 45 43 54 20 6c  ALL.    SELECT l
25f0: 6f 67 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45  og FROM t1 WHERE
2600: 20 6e 3d 34 0a 20 20 20 20 55 4e 49 4f 4e 20 41   n=4.    UNION A
2610: 4c 4c 0a 20 20 20 20 53 45 4c 45 43 54 20 6c 6f  LL.    SELECT lo
2620: 67 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20  g FROM t1 WHERE 
2630: 6e 3d 35 0a 20 20 20 20 4f 52 44 45 52 20 42 59  n=5.    ORDER BY
2640: 20 6c 6f 67 3b 0a 20 20 7d 7d 20 6d 73 67 5d 0a   log;.  }} msg].
2650: 20 20 6c 61 70 70 65 6e 64 20 76 20 24 6d 73 67    lappend v $msg
2660: 0a 7d 20 7b 30 20 7b 31 20 32 20 32 20 33 7d 7d  .} {0 {1 2 2 3}}
2670: 0a 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74  ..do_test select
2680: 34 2d 36 2e 31 20 7b 0a 20 20 65 78 65 63 73 71  4-6.1 {.  execsq
2690: 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 6c  l {.    SELECT l
26a0: 6f 67 2c 20 63 6f 75 6e 74 28 2a 29 20 61 73 20  og, count(*) as 
26b0: 63 6e 74 20 46 52 4f 4d 20 74 31 20 47 52 4f 55  cnt FROM t1 GROU
26c0: 50 20 42 59 20 6c 6f 67 0a 20 20 20 20 55 4e 49  P BY log.    UNI
26d0: 4f 4e 0a 20 20 20 20 53 45 4c 45 43 54 20 6c 6f  ON.    SELECT lo
26e0: 67 2c 20 6e 20 46 52 4f 4d 20 74 31 20 57 48 45  g, n FROM t1 WHE
26f0: 52 45 20 6e 3d 37 0a 20 20 20 20 4f 52 44 45 52  RE n=7.    ORDER
2700: 20 42 59 20 63 6e 74 2c 20 6c 6f 67 3b 0a 20 20   BY cnt, log;.  
2710: 7d 0a 7d 20 7b 30 20 31 20 31 20 31 20 32 20 32  }.} {0 1 1 1 2 2
2720: 20 33 20 34 20 33 20 37 20 34 20 38 20 35 20 31   3 4 3 7 4 8 5 1
2730: 35 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63  5}.do_test selec
2740: 74 34 2d 36 2e 32 20 7b 0a 20 20 65 78 65 63 73  t4-6.2 {.  execs
2750: 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20  ql {.    SELECT 
2760: 6c 6f 67 2c 20 63 6f 75 6e 74 28 2a 29 20 46 52  log, count(*) FR
2770: 4f 4d 20 74 31 20 47 52 4f 55 50 20 42 59 20 6c  OM t1 GROUP BY l
2780: 6f 67 0a 20 20 20 20 55 4e 49 4f 4e 0a 20 20 20  og.    UNION.   
2790: 20 53 45 4c 45 43 54 20 6c 6f 67 2c 20 6e 20 46   SELECT log, n F
27a0: 52 4f 4d 20 74 31 20 57 48 45 52 45 20 6e 3d 37  ROM t1 WHERE n=7
27b0: 0a 20 20 20 20 4f 52 44 45 52 20 42 59 20 63 6f  .    ORDER BY co
27c0: 75 6e 74 28 2a 29 2c 20 6c 6f 67 3b 0a 20 20 7d  unt(*), log;.  }
27d0: 0a 7d 20 7b 30 20 31 20 31 20 31 20 32 20 32 20  .} {0 1 1 1 2 2 
27e0: 33 20 34 20 33 20 37 20 34 20 38 20 35 20 31 35  3 4 3 7 4 8 5 15
27f0: 7d 0a 0a 23 20 4e 55 4c 4c 73 20 61 72 65 20 69  }..# NULLs are i
2800: 6e 64 69 73 74 69 6e 63 74 20 66 6f 72 20 74 68  ndistinct for th
2810: 65 20 55 4e 49 4f 4e 20 6f 70 65 72 61 74 6f 72  e UNION operator
2820: 2e 0a 23 20 4d 61 6b 65 20 73 75 72 65 20 74 68  ..# Make sure th
2830: 65 20 55 4e 49 4f 4e 20 6f 70 65 72 61 74 6f 72  e UNION operator
2840: 20 72 65 63 6f 67 6e 69 7a 65 73 20 74 68 69 73   recognizes this
2850: 0a 23 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63  .#.do_test selec
2860: 74 34 2d 36 2e 33 20 7b 0a 20 20 65 78 65 63 73  t4-6.3 {.  execs
2870: 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20  ql {.    SELECT 
2880: 4e 55 4c 4c 20 55 4e 49 4f 4e 20 53 45 4c 45 43  NULL UNION SELEC
2890: 54 20 4e 55 4c 4c 20 55 4e 49 4f 4e 0a 20 20 20  T NULL UNION.   
28a0: 20 53 45 4c 45 43 54 20 31 20 55 4e 49 4f 4e 20   SELECT 1 UNION 
28b0: 53 45 4c 45 43 54 20 32 20 41 53 20 27 78 27 0a  SELECT 2 AS 'x'.
28c0: 20 20 20 20 4f 52 44 45 52 20 42 59 20 78 3b 0a      ORDER BY x;.
28d0: 20 20 7d 0a 7d 20 7b 7b 7d 20 31 20 32 7d 0a 64    }.} {{} 1 2}.d
28e0: 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 36  o_test select4-6
28f0: 2e 33 2e 31 20 7b 0a 20 20 65 78 65 63 73 71 6c  .3.1 {.  execsql
2900: 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 4e 55   {.    SELECT NU
2910: 4c 4c 20 55 4e 49 4f 4e 20 41 4c 4c 20 53 45 4c  LL UNION ALL SEL
2920: 45 43 54 20 4e 55 4c 4c 20 55 4e 49 4f 4e 20 41  ECT NULL UNION A
2930: 4c 4c 0a 20 20 20 20 53 45 4c 45 43 54 20 31 20  LL.    SELECT 1 
2940: 55 4e 49 4f 4e 20 41 4c 4c 20 53 45 4c 45 43 54  UNION ALL SELECT
2950: 20 32 20 41 53 20 27 78 27 0a 20 20 20 20 4f 52   2 AS 'x'.    OR
2960: 44 45 52 20 42 59 20 78 3b 0a 20 20 7d 0a 7d 20  DER BY x;.  }.} 
2970: 7b 7b 7d 20 7b 7d 20 31 20 32 7d 0a 0a 23 20 4d  {{} {} 1 2}..# M
2980: 61 6b 65 20 73 75 72 65 20 74 68 65 20 44 49 53  ake sure the DIS
2990: 54 49 4e 43 54 20 6b 65 79 77 6f 72 64 20 74 72  TINCT keyword tr
29a0: 65 61 74 73 20 4e 55 4c 4c 73 20 61 73 20 69 6e  eats NULLs as in
29b0: 64 69 73 74 69 6e 63 74 2e 0a 23 0a 69 66 63 61  distinct..#.ifca
29c0: 70 61 62 6c 65 20 73 75 62 71 75 65 72 79 20 7b  pable subquery {
29d0: 0a 20 20 64 6f 5f 74 65 73 74 20 73 65 6c 65 63  .  do_test selec
29e0: 74 34 2d 36 2e 34 20 7b 0a 20 20 20 20 65 78 65  t4-6.4 {.    exe
29f0: 63 73 71 6c 20 7b 0a 20 20 20 20 20 20 53 45 4c  csql {.      SEL
2a00: 45 43 54 20 2a 20 46 52 4f 4d 20 28 0a 20 20 20  ECT * FROM (.   
2a10: 20 20 20 20 20 20 53 45 4c 45 43 54 20 4e 55 4c        SELECT NUL
2a20: 4c 2c 20 31 20 55 4e 49 4f 4e 20 41 4c 4c 20 53  L, 1 UNION ALL S
2a30: 45 4c 45 43 54 20 4e 55 4c 4c 2c 20 31 0a 20 20  ELECT NULL, 1.  
2a40: 20 20 20 20 29 3b 0a 20 20 20 20 7d 0a 20 20 7d      );.    }.  }
2a50: 20 7b 7b 7d 20 31 20 7b 7d 20 31 7d 0a 20 20 64   {{} 1 {} 1}.  d
2a60: 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 36  o_test select4-6
2a70: 2e 35 20 7b 0a 20 20 20 20 65 78 65 63 73 71 6c  .5 {.    execsql
2a80: 20 7b 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20   {.      SELECT 
2a90: 44 49 53 54 49 4e 43 54 20 2a 20 46 52 4f 4d 20  DISTINCT * FROM 
2aa0: 28 0a 20 20 20 20 20 20 20 20 20 53 45 4c 45 43  (.         SELEC
2ab0: 54 20 4e 55 4c 4c 2c 20 31 20 55 4e 49 4f 4e 20  T NULL, 1 UNION 
2ac0: 41 4c 4c 20 53 45 4c 45 43 54 20 4e 55 4c 4c 2c  ALL SELECT NULL,
2ad0: 20 31 0a 20 20 20 20 20 20 29 3b 0a 20 20 20 20   1.      );.    
2ae0: 7d 0a 20 20 7d 20 7b 7b 7d 20 31 7d 0a 20 20 64  }.  } {{} 1}.  d
2af0: 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 36  o_test select4-6
2b00: 2e 36 20 7b 0a 20 20 20 20 65 78 65 63 73 71 6c  .6 {.    execsql
2b10: 20 7b 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20   {.      SELECT 
2b20: 44 49 53 54 49 4e 43 54 20 2a 20 46 52 4f 4d 20  DISTINCT * FROM 
2b30: 28 0a 20 20 20 20 20 20 20 20 20 53 45 4c 45 43  (.         SELEC
2b40: 54 20 31 2c 32 20 20 55 4e 49 4f 4e 20 41 4c 4c  T 1,2  UNION ALL
2b50: 20 53 45 4c 45 43 54 20 31 2c 32 0a 20 20 20 20   SELECT 1,2.    
2b60: 20 20 29 3b 0a 20 20 20 20 7d 0a 20 20 7d 20 7b    );.    }.  } {
2b70: 31 20 32 7d 0a 7d 0a 0a 23 20 54 65 73 74 20 64  1 2}.}..# Test d
2b80: 69 73 74 69 6e 63 74 6e 65 73 73 20 6f 66 20 4e  istinctness of N
2b90: 55 4c 4c 20 69 6e 20 6f 74 68 65 72 20 77 61 79  ULL in other way
2ba0: 73 2e 0a 23 0a 64 6f 5f 74 65 73 74 20 73 65 6c  s..#.do_test sel
2bb0: 65 63 74 34 2d 36 2e 37 20 7b 0a 20 20 65 78 65  ect4-6.7 {.  exe
2bc0: 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43  csql {.    SELEC
2bd0: 54 20 4e 55 4c 4c 20 45 58 43 45 50 54 20 53 45  T NULL EXCEPT SE
2be0: 4c 45 43 54 20 4e 55 4c 4c 0a 20 20 7d 0a 7d 20  LECT NULL.  }.} 
2bf0: 7b 7d 0a 0a 0a 23 20 4d 61 6b 65 20 73 75 72 65  {}...# Make sure
2c00: 20 63 6f 6c 75 6d 6e 20 6e 61 6d 65 73 20 61 72   column names ar
2c10: 65 20 63 6f 72 72 65 63 74 20 77 68 65 6e 20 61  e correct when a
2c20: 20 63 6f 6d 70 6f 75 6e 64 20 73 65 6c 65 63 74   compound select
2c30: 20 61 70 70 65 61 72 73 20 61 73 0a 23 20 61 6e   appears as.# an
2c40: 20 65 78 70 72 65 73 73 69 6f 6e 20 69 6e 20 74   expression in t
2c50: 68 65 20 57 48 45 52 45 20 63 6c 61 75 73 65 2e  he WHERE clause.
2c60: 0a 23 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63  .#.do_test selec
2c70: 74 34 2d 37 2e 31 20 7b 0a 20 20 65 78 65 63 73  t4-7.1 {.  execs
2c80: 71 6c 20 7b 0a 20 20 20 20 43 52 45 41 54 45 20  ql {.    CREATE 
2c90: 54 41 42 4c 45 20 74 32 20 41 53 20 53 45 4c 45  TABLE t2 AS SELE
2ca0: 43 54 20 6c 6f 67 20 41 53 20 27 78 27 2c 20 63  CT log AS 'x', c
2cb0: 6f 75 6e 74 28 2a 29 20 41 53 20 27 79 27 20 46  ount(*) AS 'y' F
2cc0: 52 4f 4d 20 74 31 20 47 52 4f 55 50 20 42 59 20  ROM t1 GROUP BY 
2cd0: 6c 6f 67 3b 0a 20 20 20 20 53 45 4c 45 43 54 20  log;.    SELECT 
2ce0: 2a 20 46 52 4f 4d 20 74 32 20 4f 52 44 45 52 20  * FROM t2 ORDER 
2cf0: 42 59 20 78 3b 0a 20 20 7d 0a 7d 20 7b 30 20 31  BY x;.  }.} {0 1
2d00: 20 31 20 31 20 32 20 32 20 33 20 34 20 34 20 38   1 1 2 2 3 4 4 8
2d10: 20 35 20 31 35 7d 20 20 0a 69 66 63 61 70 61 62   5 15}  .ifcapab
2d20: 6c 65 20 73 75 62 71 75 65 72 79 20 7b 0a 20 20  le subquery {.  
2d30: 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d  do_test select4-
2d40: 37 2e 32 20 7b 0a 20 20 20 20 65 78 65 63 73 71  7.2 {.    execsq
2d50: 6c 32 20 7b 0a 20 20 20 20 20 20 53 45 4c 45 43  l2 {.      SELEC
2d60: 54 20 2a 20 46 52 4f 4d 20 74 31 20 57 48 45 52  T * FROM t1 WHER
2d70: 45 20 6e 20 49 4e 20 28 53 45 4c 45 43 54 20 6e  E n IN (SELECT n
2d80: 20 46 52 4f 4d 20 74 31 20 49 4e 54 45 52 53 45   FROM t1 INTERSE
2d90: 43 54 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d  CT SELECT x FROM
2da0: 20 74 32 29 0a 20 20 20 20 20 20 4f 52 44 45 52   t2).      ORDER
2db0: 20 42 59 20 6e 0a 20 20 20 20 7d 0a 20 20 7d 20   BY n.    }.  } 
2dc0: 7b 6e 20 31 20 6c 6f 67 20 30 20 6e 20 32 20 6c  {n 1 log 0 n 2 l
2dd0: 6f 67 20 31 20 6e 20 33 20 6c 6f 67 20 32 20 6e  og 1 n 3 log 2 n
2de0: 20 34 20 6c 6f 67 20 32 20 6e 20 35 20 6c 6f 67   4 log 2 n 5 log
2df0: 20 33 7d 0a 20 20 64 6f 5f 74 65 73 74 20 73 65   3}.  do_test se
2e00: 6c 65 63 74 34 2d 37 2e 33 20 7b 0a 20 20 20 20  lect4-7.3 {.    
2e10: 65 78 65 63 73 71 6c 32 20 7b 0a 20 20 20 20 20  execsql2 {.     
2e20: 20 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 74   SELECT * FROM t
2e30: 31 20 57 48 45 52 45 20 6e 20 49 4e 20 28 53 45  1 WHERE n IN (SE
2e40: 4c 45 43 54 20 6e 20 46 52 4f 4d 20 74 31 20 45  LECT n FROM t1 E
2e50: 58 43 45 50 54 20 53 45 4c 45 43 54 20 78 20 46  XCEPT SELECT x F
2e60: 52 4f 4d 20 74 32 29 0a 20 20 20 20 20 20 4f 52  ROM t2).      OR
2e70: 44 45 52 20 42 59 20 6e 20 4c 49 4d 49 54 20 32  DER BY n LIMIT 2
2e80: 0a 20 20 20 20 7d 0a 20 20 7d 20 7b 6e 20 36 20  .    }.  } {n 6 
2e90: 6c 6f 67 20 33 20 6e 20 37 20 6c 6f 67 20 33 7d  log 3 n 7 log 3}
2ea0: 0a 20 20 64 6f 5f 74 65 73 74 20 73 65 6c 65 63  .  do_test selec
2eb0: 74 34 2d 37 2e 34 20 7b 0a 20 20 20 20 65 78 65  t4-7.4 {.    exe
2ec0: 63 73 71 6c 32 20 7b 0a 20 20 20 20 20 20 53 45  csql2 {.      SE
2ed0: 4c 45 43 54 20 2a 20 46 52 4f 4d 20 74 31 20 57  LECT * FROM t1 W
2ee0: 48 45 52 45 20 6e 20 49 4e 20 28 53 45 4c 45 43  HERE n IN (SELEC
2ef0: 54 20 6e 20 46 52 4f 4d 20 74 31 20 55 4e 49 4f  T n FROM t1 UNIO
2f00: 4e 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20  N SELECT x FROM 
2f10: 74 32 29 0a 20 20 20 20 20 20 4f 52 44 45 52 20  t2).      ORDER 
2f20: 42 59 20 6e 20 4c 49 4d 49 54 20 32 0a 20 20 20  BY n LIMIT 2.   
2f30: 20 7d 0a 20 20 7d 20 7b 6e 20 31 20 6c 6f 67 20   }.  } {n 1 log 
2f40: 30 20 6e 20 32 20 6c 6f 67 20 31 7d 0a 7d 20 3b  0 n 2 log 1}.} ;
2f50: 23 20 69 66 63 61 70 61 62 6c 65 20 73 75 62 71  # ifcapable subq
2f60: 75 65 72 79 0a 0a 7d 20 3b 23 20 69 66 63 61 70  uery..} ;# ifcap
2f70: 61 62 6c 65 20 63 6f 6d 70 6f 75 6e 64 0a 0a 23  able compound..#
2f80: 20 4d 61 6b 65 20 73 75 72 65 20 44 49 53 54 49   Make sure DISTI
2f90: 4e 43 54 20 77 6f 72 6b 73 20 61 70 70 72 6f 70  NCT works approp
2fa0: 72 69 61 74 65 6c 79 20 6f 6e 20 54 45 58 54 20  riately on TEXT 
2fb0: 61 6e 64 20 4e 55 4d 45 52 49 43 20 63 6f 6c 75  and NUMERIC colu
2fc0: 6d 6e 73 2e 0a 64 6f 5f 74 65 73 74 20 73 65 6c  mns..do_test sel
2fd0: 65 63 74 34 2d 38 2e 31 20 7b 0a 20 20 65 78 65  ect4-8.1 {.  exe
2fe0: 63 73 71 6c 20 7b 0a 20 20 20 20 42 45 47 49 4e  csql {.    BEGIN
2ff0: 3b 0a 20 20 20 20 43 52 45 41 54 45 20 54 41 42  ;.    CREATE TAB
3000: 4c 45 20 74 33 28 61 20 74 65 78 74 2c 20 62 20  LE t3(a text, b 
3010: 66 6c 6f 61 74 2c 20 63 20 74 65 78 74 29 3b 0a  float, c text);.
3020: 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20      INSERT INTO 
3030: 74 33 20 56 41 4c 55 45 53 28 31 2c 20 31 2e 31  t3 VALUES(1, 1.1
3040: 2c 20 27 31 2e 31 27 29 3b 0a 20 20 20 20 49 4e  , '1.1');.    IN
3050: 53 45 52 54 20 49 4e 54 4f 20 74 33 20 56 41 4c  SERT INTO t3 VAL
3060: 55 45 53 28 32 2c 20 31 2e 31 30 2c 20 27 31 2e  UES(2, 1.10, '1.
3070: 31 30 27 29 3b 0a 20 20 20 20 49 4e 53 45 52 54  10');.    INSERT
3080: 20 49 4e 54 4f 20 74 33 20 56 41 4c 55 45 53 28   INTO t3 VALUES(
3090: 33 2c 20 31 2e 31 30 2c 20 27 31 2e 31 27 29 3b  3, 1.10, '1.1');
30a0: 0a 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f  .    INSERT INTO
30b0: 20 74 33 20 56 41 4c 55 45 53 28 34 2c 20 31 2e   t3 VALUES(4, 1.
30c0: 31 2c 20 27 31 2e 31 30 27 29 3b 0a 20 20 20 20  1, '1.10');.    
30d0: 49 4e 53 45 52 54 20 49 4e 54 4f 20 74 33 20 56  INSERT INTO t3 V
30e0: 41 4c 55 45 53 28 35 2c 20 31 2e 32 2c 20 27 31  ALUES(5, 1.2, '1
30f0: 2e 32 27 29 3b 0a 20 20 20 20 49 4e 53 45 52 54  .2');.    INSERT
3100: 20 49 4e 54 4f 20 74 33 20 56 41 4c 55 45 53 28   INTO t3 VALUES(
3110: 36 2c 20 31 2e 33 2c 20 27 31 2e 33 27 29 3b 0a  6, 1.3, '1.3');.
3120: 20 20 20 20 43 4f 4d 4d 49 54 3b 0a 20 20 7d 0a      COMMIT;.  }.
3130: 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20    execsql {.    
3140: 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54 20  SELECT DISTINCT 
3150: 62 20 46 52 4f 4d 20 74 33 20 4f 52 44 45 52 20  b FROM t3 ORDER 
3160: 42 59 20 63 3b 0a 20 20 7d 0a 7d 20 7b 31 2e 31  BY c;.  }.} {1.1
3170: 20 31 2e 32 20 31 2e 33 7d 0a 64 6f 5f 74 65 73   1.2 1.3}.do_tes
3180: 74 20 73 65 6c 65 63 74 34 2d 38 2e 32 20 7b 0a  t select4-8.2 {.
3190: 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20    execsql {.    
31a0: 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54 20  SELECT DISTINCT 
31b0: 63 20 46 52 4f 4d 20 74 33 20 4f 52 44 45 52 20  c FROM t3 ORDER 
31c0: 42 59 20 63 3b 0a 20 20 7d 0a 7d 20 7b 31 2e 31  BY c;.  }.} {1.1
31d0: 20 31 2e 31 30 20 31 2e 32 20 31 2e 33 7d 0a 0a   1.10 1.2 1.3}..
31e0: 23 20 4d 61 6b 65 20 73 75 72 65 20 74 68 65 20  # Make sure the 
31f0: 6e 61 6d 65 73 20 6f 66 20 63 6f 6c 75 6d 6e 73  names of columns
3200: 20 61 72 65 20 74 61 6b 65 6e 20 66 72 6f 6d 20   are taken from 
3210: 74 68 65 20 72 69 67 68 74 2d 6d 6f 73 74 20 73  the right-most s
3220: 75 62 71 75 65 72 79 0a 23 20 72 69 67 68 74 20  ubquery.# right 
3230: 69 6e 20 61 20 63 6f 6d 70 6f 75 6e 64 20 71 75  in a compound qu
3240: 65 72 79 2e 20 20 54 69 63 6b 65 74 20 23 31 37  ery.  Ticket #17
3250: 32 31 0a 23 0a 69 66 63 61 70 61 62 6c 65 20 63  21.#.ifcapable c
3260: 6f 6d 70 6f 75 6e 64 20 7b 0a 0a 64 6f 5f 74 65  ompound {..do_te
3270: 73 74 20 73 65 6c 65 63 74 34 2d 39 2e 31 20 7b  st select4-9.1 {
3280: 0a 20 20 65 78 65 63 73 71 6c 32 20 7b 0a 20 20  .  execsql2 {.  
3290: 20 20 53 45 4c 45 43 54 20 78 2c 20 79 20 46 52    SELECT x, y FR
32a0: 4f 4d 20 74 32 20 55 4e 49 4f 4e 20 53 45 4c 45  OM t2 UNION SELE
32b0: 43 54 20 61 2c 20 62 20 46 52 4f 4d 20 74 33 20  CT a, b FROM t3 
32c0: 4f 52 44 45 52 20 42 59 20 78 20 4c 49 4d 49 54  ORDER BY x LIMIT
32d0: 20 31 0a 20 20 7d 0a 7d 20 7b 78 20 30 20 79 20   1.  }.} {x 0 y 
32e0: 31 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63  1}.do_test selec
32f0: 74 34 2d 39 2e 32 20 7b 0a 20 20 65 78 65 63 73  t4-9.2 {.  execs
3300: 71 6c 32 20 7b 0a 20 20 20 20 53 45 4c 45 43 54  ql2 {.    SELECT
3310: 20 78 2c 20 79 20 46 52 4f 4d 20 74 32 20 55 4e   x, y FROM t2 UN
3320: 49 4f 4e 20 41 4c 4c 20 53 45 4c 45 43 54 20 61  ION ALL SELECT a
3330: 2c 20 62 20 46 52 4f 4d 20 74 33 20 4f 52 44 45  , b FROM t3 ORDE
3340: 52 20 42 59 20 78 20 4c 49 4d 49 54 20 31 0a 20  R BY x LIMIT 1. 
3350: 20 7d 0a 7d 20 7b 78 20 30 20 79 20 31 7d 0a 64   }.} {x 0 y 1}.d
3360: 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 39  o_test select4-9
3370: 2e 33 20 7b 0a 20 20 65 78 65 63 73 71 6c 32 20  .3 {.  execsql2 
3380: 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 78 2c 20  {.    SELECT x, 
3390: 79 20 46 52 4f 4d 20 74 32 20 45 58 43 45 50 54  y FROM t2 EXCEPT
33a0: 20 53 45 4c 45 43 54 20 61 2c 20 62 20 46 52 4f   SELECT a, b FRO
33b0: 4d 20 74 33 20 4f 52 44 45 52 20 42 59 20 78 20  M t3 ORDER BY x 
33c0: 4c 49 4d 49 54 20 31 0a 20 20 7d 0a 7d 20 7b 78  LIMIT 1.  }.} {x
33d0: 20 30 20 79 20 31 7d 0a 64 6f 5f 74 65 73 74 20   0 y 1}.do_test 
33e0: 73 65 6c 65 63 74 34 2d 39 2e 34 20 7b 0a 20 20  select4-9.4 {.  
33f0: 65 78 65 63 73 71 6c 32 20 7b 0a 20 20 20 20 53  execsql2 {.    S
3400: 45 4c 45 43 54 20 78 2c 20 79 20 46 52 4f 4d 20  ELECT x, y FROM 
3410: 74 32 20 49 4e 54 45 52 53 45 43 54 20 53 45 4c  t2 INTERSECT SEL
3420: 45 43 54 20 30 20 41 53 20 61 2c 20 31 20 41 53  ECT 0 AS a, 1 AS
3430: 20 62 3b 0a 20 20 7d 0a 7d 20 7b 78 20 30 20 79   b;.  }.} {x 0 y
3440: 20 31 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65   1}.do_test sele
3450: 63 74 34 2d 39 2e 35 20 7b 0a 20 20 65 78 65 63  ct4-9.5 {.  exec
3460: 73 71 6c 32 20 7b 0a 20 20 20 20 53 45 4c 45 43  sql2 {.    SELEC
3470: 54 20 30 20 41 53 20 78 2c 20 31 20 41 53 20 79  T 0 AS x, 1 AS y
3480: 0a 20 20 20 20 55 4e 49 4f 4e 0a 20 20 20 20 53  .    UNION.    S
3490: 45 4c 45 43 54 20 32 20 41 53 20 70 2c 20 33 20  ELECT 2 AS p, 3 
34a0: 41 53 20 71 0a 20 20 20 20 55 4e 49 4f 4e 0a 20  AS q.    UNION. 
34b0: 20 20 20 53 45 4c 45 43 54 20 34 20 41 53 20 61     SELECT 4 AS a
34c0: 2c 20 35 20 41 53 20 62 0a 20 20 20 20 4f 52 44  , 5 AS b.    ORD
34d0: 45 52 20 42 59 20 78 20 4c 49 4d 49 54 20 31 0a  ER BY x LIMIT 1.
34e0: 20 20 7d 0a 7d 20 7b 78 20 30 20 79 20 31 7d 0a    }.} {x 0 y 1}.
34f0: 0a 69 66 63 61 70 61 62 6c 65 20 73 75 62 71 75  .ifcapable subqu
3500: 65 72 79 20 7b 0a 64 6f 5f 74 65 73 74 20 73 65  ery {.do_test se
3510: 6c 65 63 74 34 2d 39 2e 36 20 7b 0a 20 20 65 78  lect4-9.6 {.  ex
3520: 65 63 73 71 6c 32 20 7b 0a 20 20 20 20 53 45 4c  ecsql2 {.    SEL
3530: 45 43 54 20 2a 20 46 52 4f 4d 20 28 0a 20 20 20  ECT * FROM (.   
3540: 20 20 20 53 45 4c 45 43 54 20 30 20 41 53 20 78     SELECT 0 AS x
3550: 2c 20 31 20 41 53 20 79 0a 20 20 20 20 20 20 55  , 1 AS y.      U
3560: 4e 49 4f 4e 0a 20 20 20 20 20 20 53 45 4c 45 43  NION.      SELEC
3570: 54 20 32 20 41 53 20 70 2c 20 33 20 41 53 20 71  T 2 AS p, 3 AS q
3580: 0a 20 20 20 20 20 20 55 4e 49 4f 4e 0a 20 20 20  .      UNION.   
3590: 20 20 20 53 45 4c 45 43 54 20 34 20 41 53 20 61     SELECT 4 AS a
35a0: 2c 20 35 20 41 53 20 62 0a 20 20 20 20 29 20 4f  , 5 AS b.    ) O
35b0: 52 44 45 52 20 42 59 20 31 20 4c 49 4d 49 54 20  RDER BY 1 LIMIT 
35c0: 31 3b 0a 20 20 7d 0a 7d 20 7b 78 20 30 20 79 20  1;.  }.} {x 0 y 
35d0: 31 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63  1}.do_test selec
35e0: 74 34 2d 39 2e 37 20 7b 0a 20 20 65 78 65 63 73  t4-9.7 {.  execs
35f0: 71 6c 32 20 7b 0a 20 20 20 20 53 45 4c 45 43 54  ql2 {.    SELECT
3600: 20 2a 20 46 52 4f 4d 20 28 0a 20 20 20 20 20 20   * FROM (.      
3610: 53 45 4c 45 43 54 20 30 20 41 53 20 78 2c 20 31  SELECT 0 AS x, 1
3620: 20 41 53 20 79 0a 20 20 20 20 20 20 55 4e 49 4f   AS y.      UNIO
3630: 4e 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20 32  N.      SELECT 2
3640: 20 41 53 20 70 2c 20 33 20 41 53 20 71 0a 20 20   AS p, 3 AS q.  
3650: 20 20 20 20 55 4e 49 4f 4e 0a 20 20 20 20 20 20      UNION.      
3660: 53 45 4c 45 43 54 20 34 20 41 53 20 61 2c 20 35  SELECT 4 AS a, 5
3670: 20 41 53 20 62 0a 20 20 20 20 29 20 4f 52 44 45   AS b.    ) ORDE
3680: 52 20 42 59 20 78 20 4c 49 4d 49 54 20 31 3b 0a  R BY x LIMIT 1;.
3690: 20 20 7d 0a 7d 20 7b 78 20 30 20 79 20 31 7d 0a    }.} {x 0 y 1}.
36a0: 7d 20 3b 23 20 69 66 63 61 70 61 62 6c 65 20 73  } ;# ifcapable s
36b0: 75 62 71 75 65 72 79 0a 0a 64 6f 5f 74 65 73 74  ubquery..do_test
36c0: 20 73 65 6c 65 63 74 34 2d 39 2e 38 20 7b 0a 20   select4-9.8 {. 
36d0: 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53   execsql {.    S
36e0: 45 4c 45 43 54 20 30 20 41 53 20 78 2c 20 31 20  ELECT 0 AS x, 1 
36f0: 41 53 20 79 0a 20 20 20 20 55 4e 49 4f 4e 0a 20  AS y.    UNION. 
3700: 20 20 20 53 45 4c 45 43 54 20 32 20 41 53 20 79     SELECT 2 AS y
3710: 2c 20 2d 33 20 41 53 20 78 0a 20 20 20 20 4f 52  , -3 AS x.    OR
3720: 44 45 52 20 42 59 20 78 20 4c 49 4d 49 54 20 31  DER BY x LIMIT 1
3730: 3b 0a 20 20 7d 0a 7d 20 7b 30 20 31 7d 0a 0a 64  ;.  }.} {0 1}..d
3740: 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 39  o_test select4-9
3750: 2e 39 2e 31 20 7b 0a 20 20 65 78 65 63 73 71 6c  .9.1 {.  execsql
3760: 32 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 31  2 {.    SELECT 1
3770: 20 41 53 20 61 2c 20 32 20 41 53 20 62 20 55 4e   AS a, 2 AS b UN
3780: 49 4f 4e 20 41 4c 4c 20 53 45 4c 45 43 54 20 33  ION ALL SELECT 3
3790: 20 41 53 20 62 2c 20 34 20 41 53 20 61 0a 20 20   AS b, 4 AS a.  
37a0: 7d 0a 7d 20 7b 61 20 31 20 62 20 32 20 61 20 33  }.} {a 1 b 2 a 3
37b0: 20 62 20 34 7d 0a 0a 69 66 63 61 70 61 62 6c 65   b 4}..ifcapable
37c0: 20 73 75 62 71 75 65 72 79 20 7b 0a 64 6f 5f 74   subquery {.do_t
37d0: 65 73 74 20 73 65 6c 65 63 74 34 2d 39 2e 39 2e  est select4-9.9.
37e0: 32 20 7b 0a 20 20 65 78 65 63 73 71 6c 32 20 7b  2 {.  execsql2 {
37f0: 0a 20 20 20 20 53 45 4c 45 43 54 20 2a 20 46 52  .    SELECT * FR
3800: 4f 4d 20 28 53 45 4c 45 43 54 20 31 20 41 53 20  OM (SELECT 1 AS 
3810: 61 2c 20 32 20 41 53 20 62 20 55 4e 49 4f 4e 20  a, 2 AS b UNION 
3820: 41 4c 4c 20 53 45 4c 45 43 54 20 33 20 41 53 20  ALL SELECT 3 AS 
3830: 62 2c 20 34 20 41 53 20 61 29 0a 20 20 20 20 20  b, 4 AS a).     
3840: 57 48 45 52 45 20 62 3d 33 0a 20 20 7d 0a 7d 20  WHERE b=3.  }.} 
3850: 7b 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63  {}.do_test selec
3860: 74 34 2d 39 2e 31 30 20 7b 0a 20 20 65 78 65 63  t4-9.10 {.  exec
3870: 73 71 6c 32 20 7b 0a 20 20 20 20 53 45 4c 45 43  sql2 {.    SELEC
3880: 54 20 2a 20 46 52 4f 4d 20 28 53 45 4c 45 43 54  T * FROM (SELECT
3890: 20 31 20 41 53 20 61 2c 20 32 20 41 53 20 62 20   1 AS a, 2 AS b 
38a0: 55 4e 49 4f 4e 20 41 4c 4c 20 53 45 4c 45 43 54  UNION ALL SELECT
38b0: 20 33 20 41 53 20 62 2c 20 34 20 41 53 20 61 29   3 AS b, 4 AS a)
38c0: 0a 20 20 20 20 20 57 48 45 52 45 20 62 3d 32 0a  .     WHERE b=2.
38d0: 20 20 7d 0a 7d 20 7b 61 20 31 20 62 20 32 7d 0a    }.} {a 1 b 2}.
38e0: 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d  do_test select4-
38f0: 39 2e 31 31 20 7b 0a 20 20 65 78 65 63 73 71 6c  9.11 {.  execsql
3900: 32 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 2a  2 {.    SELECT *
3910: 20 46 52 4f 4d 20 28 53 45 4c 45 43 54 20 31 20   FROM (SELECT 1 
3920: 41 53 20 61 2c 20 32 20 41 53 20 62 20 55 4e 49  AS a, 2 AS b UNI
3930: 4f 4e 20 41 4c 4c 20 53 45 4c 45 43 54 20 33 20  ON ALL SELECT 3 
3940: 41 53 20 65 2c 20 34 20 41 53 20 62 29 0a 20 20  AS e, 4 AS b).  
3950: 20 20 20 57 48 45 52 45 20 62 3d 32 0a 20 20 7d     WHERE b=2.  }
3960: 0a 7d 20 7b 61 20 31 20 62 20 32 7d 0a 64 6f 5f  .} {a 1 b 2}.do_
3970: 74 65 73 74 20 73 65 6c 65 63 74 34 2d 39 2e 31  test select4-9.1
3980: 32 20 7b 0a 20 20 65 78 65 63 73 71 6c 32 20 7b  2 {.  execsql2 {
3990: 0a 20 20 20 20 53 45 4c 45 43 54 20 2a 20 46 52  .    SELECT * FR
39a0: 4f 4d 20 28 53 45 4c 45 43 54 20 31 20 41 53 20  OM (SELECT 1 AS 
39b0: 61 2c 20 32 20 41 53 20 62 20 55 4e 49 4f 4e 20  a, 2 AS b UNION 
39c0: 41 4c 4c 20 53 45 4c 45 43 54 20 33 20 41 53 20  ALL SELECT 3 AS 
39d0: 65 2c 20 34 20 41 53 20 62 29 0a 20 20 20 20 20  e, 4 AS b).     
39e0: 57 48 45 52 45 20 62 3e 30 0a 20 20 7d 0a 7d 20  WHERE b>0.  }.} 
39f0: 7b 61 20 31 20 62 20 32 20 61 20 33 20 62 20 34  {a 1 b 2 a 3 b 4
3a00: 7d 0a 7d 20 3b 23 20 69 66 63 61 70 61 62 6c 65  }.} ;# ifcapable
3a10: 20 73 75 62 71 75 65 72 79 0a 0a 23 20 54 72 79   subquery..# Try
3a20: 20 63 6f 6d 62 69 6e 69 6e 67 20 44 49 53 54 49   combining DISTI
3a30: 4e 43 54 2c 20 4c 49 4d 49 54 2c 20 61 6e 64 20  NCT, LIMIT, and 
3a40: 4f 46 46 53 45 54 2e 20 20 4d 61 6b 65 20 73 75  OFFSET.  Make su
3a50: 72 65 20 74 68 65 79 20 61 6c 6c 20 77 6f 72 6b  re they all work
3a60: 0a 23 20 74 6f 67 65 74 68 65 72 2e 0a 23 0a 64  .# together..#.d
3a70: 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 31  o_test select4-1
3a80: 30 2e 31 20 7b 0a 20 20 65 78 65 63 73 71 6c 20  0.1 {.  execsql 
3a90: 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 44 49 53  {.    SELECT DIS
3aa0: 54 49 4e 43 54 20 6c 6f 67 20 46 52 4f 4d 20 74  TINCT log FROM t
3ab0: 31 20 4f 52 44 45 52 20 42 59 20 6c 6f 67 0a 20  1 ORDER BY log. 
3ac0: 20 7d 0a 7d 20 7b 30 20 31 20 32 20 33 20 34 20   }.} {0 1 2 3 4 
3ad0: 35 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63  5}.do_test selec
3ae0: 74 34 2d 31 30 2e 32 20 7b 0a 20 20 65 78 65 63  t4-10.2 {.  exec
3af0: 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54  sql {.    SELECT
3b00: 20 44 49 53 54 49 4e 43 54 20 6c 6f 67 20 46 52   DISTINCT log FR
3b10: 4f 4d 20 74 31 20 4f 52 44 45 52 20 42 59 20 6c  OM t1 ORDER BY l
3b20: 6f 67 20 4c 49 4d 49 54 20 34 0a 20 20 7d 0a 7d  og LIMIT 4.  }.}
3b30: 20 7b 30 20 31 20 32 20 33 7d 0a 64 6f 5f 74 65   {0 1 2 3}.do_te
3b40: 73 74 20 73 65 6c 65 63 74 34 2d 31 30 2e 33 20  st select4-10.3 
3b50: 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20  {.  execsql {.  
3b60: 20 20 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43    SELECT DISTINC
3b70: 54 20 6c 6f 67 20 46 52 4f 4d 20 74 31 20 4f 52  T log FROM t1 OR
3b80: 44 45 52 20 42 59 20 6c 6f 67 20 4c 49 4d 49 54  DER BY log LIMIT
3b90: 20 30 0a 20 20 7d 0a 7d 20 7b 7d 0a 64 6f 5f 74   0.  }.} {}.do_t
3ba0: 65 73 74 20 73 65 6c 65 63 74 34 2d 31 30 2e 34  est select4-10.4
3bb0: 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20   {.  execsql {. 
3bc0: 20 20 20 53 45 4c 45 43 54 20 44 49 53 54 49 4e     SELECT DISTIN
3bd0: 43 54 20 6c 6f 67 20 46 52 4f 4d 20 74 31 20 4f  CT log FROM t1 O
3be0: 52 44 45 52 20 42 59 20 6c 6f 67 20 4c 49 4d 49  RDER BY log LIMI
3bf0: 54 20 2d 31 0a 20 20 7d 0a 7d 20 7b 30 20 31 20  T -1.  }.} {0 1 
3c00: 32 20 33 20 34 20 35 7d 0a 64 6f 5f 74 65 73 74  2 3 4 5}.do_test
3c10: 20 73 65 6c 65 63 74 34 2d 31 30 2e 35 20 7b 0a   select4-10.5 {.
3c20: 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20    execsql {.    
3c30: 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54 20  SELECT DISTINCT 
3c40: 6c 6f 67 20 46 52 4f 4d 20 74 31 20 4f 52 44 45  log FROM t1 ORDE
3c50: 52 20 42 59 20 6c 6f 67 20 4c 49 4d 49 54 20 2d  R BY log LIMIT -
3c60: 31 20 4f 46 46 53 45 54 20 32 0a 20 20 7d 0a 7d  1 OFFSET 2.  }.}
3c70: 20 7b 32 20 33 20 34 20 35 7d 0a 64 6f 5f 74 65   {2 3 4 5}.do_te
3c80: 73 74 20 73 65 6c 65 63 74 34 2d 31 30 2e 36 20  st select4-10.6 
3c90: 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20  {.  execsql {.  
3ca0: 20 20 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43    SELECT DISTINC
3cb0: 54 20 6c 6f 67 20 46 52 4f 4d 20 74 31 20 4f 52  T log FROM t1 OR
3cc0: 44 45 52 20 42 59 20 6c 6f 67 20 4c 49 4d 49 54  DER BY log LIMIT
3cd0: 20 33 20 4f 46 46 53 45 54 20 32 0a 20 20 7d 0a   3 OFFSET 2.  }.
3ce0: 7d 20 7b 32 20 33 20 34 7d 0a 64 6f 5f 74 65 73  } {2 3 4}.do_tes
3cf0: 74 20 73 65 6c 65 63 74 34 2d 31 30 2e 37 20 7b  t select4-10.7 {
3d00: 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20  .  execsql {.   
3d10: 20 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54   SELECT DISTINCT
3d20: 20 6c 6f 67 20 46 52 4f 4d 20 74 31 20 4f 52 44   log FROM t1 ORD
3d30: 45 52 20 42 59 20 2b 6c 6f 67 20 4c 49 4d 49 54  ER BY +log LIMIT
3d40: 20 33 20 4f 46 46 53 45 54 20 32 30 0a 20 20 7d   3 OFFSET 20.  }
3d50: 0a 7d 20 7b 7d 0a 64 6f 5f 74 65 73 74 20 73 65  .} {}.do_test se
3d60: 6c 65 63 74 34 2d 31 30 2e 38 20 7b 0a 20 20 65  lect4-10.8 {.  e
3d70: 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c  xecsql {.    SEL
3d80: 45 43 54 20 44 49 53 54 49 4e 43 54 20 6c 6f 67  ECT DISTINCT log
3d90: 20 46 52 4f 4d 20 74 31 20 4f 52 44 45 52 20 42   FROM t1 ORDER B
3da0: 59 20 6c 6f 67 20 4c 49 4d 49 54 20 30 20 4f 46  Y log LIMIT 0 OF
3db0: 46 53 45 54 20 33 0a 20 20 7d 0a 7d 20 7b 7d 0a  FSET 3.  }.} {}.
3dc0: 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d  do_test select4-
3dd0: 31 30 2e 39 20 7b 0a 20 20 65 78 65 63 73 71 6c  10.9 {.  execsql
3de0: 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 44 49   {.    SELECT DI
3df0: 53 54 49 4e 43 54 20 6d 61 78 28 6e 29 2c 20 6c  STINCT max(n), l
3e00: 6f 67 20 46 52 4f 4d 20 74 31 20 4f 52 44 45 52  og FROM t1 ORDER
3e10: 20 42 59 20 2b 6c 6f 67 3b 20 2d 2d 20 4c 49 4d   BY +log; -- LIM
3e20: 49 54 20 32 20 4f 46 46 53 45 54 20 31 0a 20 20  IT 2 OFFSET 1.  
3e30: 7d 0a 7d 20 7b 33 31 20 35 7d 0a 0a 23 20 4d 61  }.} {31 5}..# Ma
3e40: 6b 65 20 73 75 72 65 20 63 6f 6d 70 6f 75 6e 64  ke sure compound
3e50: 20 53 45 4c 45 43 54 73 20 77 69 74 68 20 77 69   SELECTs with wi
3e60: 6c 64 6c 79 20 64 69 66 66 65 72 65 6e 74 20 6e  ldly different n
3e70: 75 6d 62 65 72 73 20 6f 66 20 63 6f 6c 75 6d 6e  umbers of column
3e80: 73 0a 23 20 64 6f 20 6e 6f 74 20 63 61 75 73 65  s.# do not cause
3e90: 20 61 73 73 65 72 74 69 6f 6e 20 66 61 75 6c 74   assertion fault
3ea0: 73 20 64 75 65 20 74 6f 20 72 65 67 69 73 74 65  s due to registe
3eb0: 72 20 61 6c 6c 6f 63 61 74 69 6f 6e 20 69 73 73  r allocation iss
3ec0: 75 65 73 2e 0a 23 0a 64 6f 5f 74 65 73 74 20 73  ues..#.do_test s
3ed0: 65 6c 65 63 74 34 2d 31 31 2e 31 20 7b 0a 20 20  elect4-11.1 {.  
3ee0: 63 61 74 63 68 73 71 6c 20 7b 0a 20 20 20 20 53  catchsql {.    S
3ef0: 45 4c 45 43 54 20 78 2c 78 2c 78 2c 78 2c 78 2c  ELECT x,x,x,x,x,
3f00: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
3f10: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
3f20: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
3f30: 78 20 46 52 4f 4d 20 74 32 0a 20 20 20 20 55 4e  x FROM t2.    UN
3f40: 49 4f 4e 0a 20 20 20 20 53 45 4c 45 43 54 20 78  ION.    SELECT x
3f50: 20 46 52 4f 4d 20 74 32 0a 20 20 7d 0a 7d 20 7b   FROM t2.  }.} {
3f60: 31 20 7b 53 45 4c 45 43 54 73 20 74 6f 20 74 68  1 {SELECTs to th
3f70: 65 20 6c 65 66 74 20 61 6e 64 20 72 69 67 68 74  e left and right
3f80: 20 6f 66 20 55 4e 49 4f 4e 20 64 6f 20 6e 6f 74   of UNION do not
3f90: 20 68 61 76 65 20 74 68 65 20 73 61 6d 65 20 6e   have the same n
3fa0: 75 6d 62 65 72 20 6f 66 20 72 65 73 75 6c 74 20  umber of result 
3fb0: 63 6f 6c 75 6d 6e 73 7d 7d 0a 64 6f 5f 74 65 73  columns}}.do_tes
3fc0: 74 20 73 65 6c 65 63 74 34 2d 31 31 2e 32 20 7b  t select4-11.2 {
3fd0: 0a 20 20 63 61 74 63 68 73 71 6c 20 7b 0a 20 20  .  catchsql {.  
3fe0: 20 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20    SELECT x FROM 
3ff0: 74 32 0a 20 20 20 20 55 4e 49 4f 4e 0a 20 20 20  t2.    UNION.   
4000: 20 53 45 4c 45 43 54 20 78 2c 78 2c 78 2c 78 2c   SELECT x,x,x,x,
4010: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
4020: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
4030: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
4040: 78 2c 78 20 46 52 4f 4d 20 74 32 0a 20 20 7d 0a  x,x FROM t2.  }.
4050: 7d 20 7b 31 20 7b 53 45 4c 45 43 54 73 20 74 6f  } {1 {SELECTs to
4060: 20 74 68 65 20 6c 65 66 74 20 61 6e 64 20 72 69   the left and ri
4070: 67 68 74 20 6f 66 20 55 4e 49 4f 4e 20 64 6f 20  ght of UNION do 
4080: 6e 6f 74 20 68 61 76 65 20 74 68 65 20 73 61 6d  not have the sam
4090: 65 20 6e 75 6d 62 65 72 20 6f 66 20 72 65 73 75  e number of resu
40a0: 6c 74 20 63 6f 6c 75 6d 6e 73 7d 7d 0a 64 6f 5f  lt columns}}.do_
40b0: 74 65 73 74 20 73 65 6c 65 63 74 34 2d 31 31 2e  test select4-11.
40c0: 33 20 7b 0a 20 20 63 61 74 63 68 73 71 6c 20 7b  3 {.  catchsql {
40d0: 0a 20 20 20 20 53 45 4c 45 43 54 20 78 2c 78 2c  .    SELECT x,x,
40e0: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
40f0: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
4100: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
4110: 78 2c 78 2c 78 2c 78 20 46 52 4f 4d 20 74 32 0a  x,x,x,x FROM t2.
4120: 20 20 20 20 55 4e 49 4f 4e 20 41 4c 4c 0a 20 20      UNION ALL.  
4130: 20 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20    SELECT x FROM 
4140: 74 32 0a 20 20 7d 0a 7d 20 7b 31 20 7b 53 45 4c  t2.  }.} {1 {SEL
4150: 45 43 54 73 20 74 6f 20 74 68 65 20 6c 65 66 74  ECTs to the left
4160: 20 61 6e 64 20 72 69 67 68 74 20 6f 66 20 55 4e   and right of UN
4170: 49 4f 4e 20 41 4c 4c 20 64 6f 20 6e 6f 74 20 68  ION ALL do not h
4180: 61 76 65 20 74 68 65 20 73 61 6d 65 20 6e 75 6d  ave the same num
4190: 62 65 72 20 6f 66 20 72 65 73 75 6c 74 20 63 6f  ber of result co
41a0: 6c 75 6d 6e 73 7d 7d 0a 64 6f 5f 74 65 73 74 20  lumns}}.do_test 
41b0: 73 65 6c 65 63 74 34 2d 31 31 2e 34 20 7b 0a 20  select4-11.4 {. 
41c0: 20 63 61 74 63 68 73 71 6c 20 7b 0a 20 20 20 20   catchsql {.    
41d0: 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 32  SELECT x FROM t2
41e0: 0a 20 20 20 20 55 4e 49 4f 4e 20 41 4c 4c 0a 20  .    UNION ALL. 
41f0: 20 20 20 53 45 4c 45 43 54 20 78 2c 78 2c 78 2c     SELECT x,x,x,
4200: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
4210: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
4220: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
4230: 78 2c 78 2c 78 20 46 52 4f 4d 20 74 32 0a 20 20  x,x,x FROM t2.  
4240: 7d 0a 7d 20 7b 31 20 7b 53 45 4c 45 43 54 73 20  }.} {1 {SELECTs 
4250: 74 6f 20 74 68 65 20 6c 65 66 74 20 61 6e 64 20  to the left and 
4260: 72 69 67 68 74 20 6f 66 20 55 4e 49 4f 4e 20 41  right of UNION A
4270: 4c 4c 20 64 6f 20 6e 6f 74 20 68 61 76 65 20 74  LL do not have t
4280: 68 65 20 73 61 6d 65 20 6e 75 6d 62 65 72 20 6f  he same number o
4290: 66 20 72 65 73 75 6c 74 20 63 6f 6c 75 6d 6e 73  f result columns
42a0: 7d 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63  }}.do_test selec
42b0: 74 34 2d 31 31 2e 35 20 7b 0a 20 20 63 61 74 63  t4-11.5 {.  catc
42c0: 68 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43  hsql {.    SELEC
42d0: 54 20 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  T x,x,x,x,x,x,x,
42e0: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
42f0: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
4300: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 20 46 52  x,x,x,x,x,x,x FR
4310: 4f 4d 20 74 32 0a 20 20 20 20 45 58 43 45 50 54  OM t2.    EXCEPT
4320: 0a 20 20 20 20 53 45 4c 45 43 54 20 78 20 46 52  .    SELECT x FR
4330: 4f 4d 20 74 32 0a 20 20 7d 0a 7d 20 7b 31 20 7b  OM t2.  }.} {1 {
4340: 53 45 4c 45 43 54 73 20 74 6f 20 74 68 65 20 6c  SELECTs to the l
4350: 65 66 74 20 61 6e 64 20 72 69 67 68 74 20 6f 66  eft and right of
4360: 20 45 58 43 45 50 54 20 64 6f 20 6e 6f 74 20 68   EXCEPT do not h
4370: 61 76 65 20 74 68 65 20 73 61 6d 65 20 6e 75 6d  ave the same num
4380: 62 65 72 20 6f 66 20 72 65 73 75 6c 74 20 63 6f  ber of result co
4390: 6c 75 6d 6e 73 7d 7d 0a 64 6f 5f 74 65 73 74 20  lumns}}.do_test 
43a0: 73 65 6c 65 63 74 34 2d 31 31 2e 36 20 7b 0a 20  select4-11.6 {. 
43b0: 20 63 61 74 63 68 73 71 6c 20 7b 0a 20 20 20 20   catchsql {.    
43c0: 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 32  SELECT x FROM t2
43d0: 0a 20 20 20 20 45 58 43 45 50 54 0a 20 20 20 20  .    EXCEPT.    
43e0: 53 45 4c 45 43 54 20 78 2c 78 2c 78 2c 78 2c 78  SELECT x,x,x,x,x
43f0: 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78  ,x,x,x,x,x,x,x,x
4400: 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78  ,x,x,x,x,x,x,x,x
4410: 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78  ,x,x,x,x,x,x,x,x
4420: 2c 78 20 46 52 4f 4d 20 74 32 0a 20 20 7d 0a 7d  ,x FROM t2.  }.}
4430: 20 7b 31 20 7b 53 45 4c 45 43 54 73 20 74 6f 20   {1 {SELECTs to 
4440: 74 68 65 20 6c 65 66 74 20 61 6e 64 20 72 69 67  the left and rig
4450: 68 74 20 6f 66 20 45 58 43 45 50 54 20 64 6f 20  ht of EXCEPT do 
4460: 6e 6f 74 20 68 61 76 65 20 74 68 65 20 73 61 6d  not have the sam
4470: 65 20 6e 75 6d 62 65 72 20 6f 66 20 72 65 73 75  e number of resu
4480: 6c 74 20 63 6f 6c 75 6d 6e 73 7d 7d 0a 64 6f 5f  lt columns}}.do_
4490: 74 65 73 74 20 73 65 6c 65 63 74 34 2d 31 31 2e  test select4-11.
44a0: 37 20 7b 0a 20 20 63 61 74 63 68 73 71 6c 20 7b  7 {.  catchsql {
44b0: 0a 20 20 20 20 53 45 4c 45 43 54 20 78 2c 78 2c  .    SELECT x,x,
44c0: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
44d0: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
44e0: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
44f0: 78 2c 78 2c 78 2c 78 20 46 52 4f 4d 20 74 32 0a  x,x,x,x FROM t2.
4500: 20 20 20 20 49 4e 54 45 52 53 45 43 54 0a 20 20      INTERSECT.  
4510: 20 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20    SELECT x FROM 
4520: 74 32 0a 20 20 7d 0a 7d 20 7b 31 20 7b 53 45 4c  t2.  }.} {1 {SEL
4530: 45 43 54 73 20 74 6f 20 74 68 65 20 6c 65 66 74  ECTs to the left
4540: 20 61 6e 64 20 72 69 67 68 74 20 6f 66 20 49 4e   and right of IN
4550: 54 45 52 53 45 43 54 20 64 6f 20 6e 6f 74 20 68  TERSECT do not h
4560: 61 76 65 20 74 68 65 20 73 61 6d 65 20 6e 75 6d  ave the same num
4570: 62 65 72 20 6f 66 20 72 65 73 75 6c 74 20 63 6f  ber of result co
4580: 6c 75 6d 6e 73 7d 7d 0a 64 6f 5f 74 65 73 74 20  lumns}}.do_test 
4590: 73 65 6c 65 63 74 34 2d 31 31 2e 38 20 7b 0a 20  select4-11.8 {. 
45a0: 20 63 61 74 63 68 73 71 6c 20 7b 0a 20 20 20 20   catchsql {.    
45b0: 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 32  SELECT x FROM t2
45c0: 0a 20 20 20 20 49 4e 54 45 52 53 45 43 54 0a 20  .    INTERSECT. 
45d0: 20 20 20 53 45 4c 45 43 54 20 78 2c 78 2c 78 2c     SELECT x,x,x,
45e0: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
45f0: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
4600: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
4610: 78 2c 78 2c 78 20 46 52 4f 4d 20 74 32 0a 20 20  x,x,x FROM t2.  
4620: 7d 0a 7d 20 7b 31 20 7b 53 45 4c 45 43 54 73 20  }.} {1 {SELECTs 
4630: 74 6f 20 74 68 65 20 6c 65 66 74 20 61 6e 64 20  to the left and 
4640: 72 69 67 68 74 20 6f 66 20 49 4e 54 45 52 53 45  right of INTERSE
4650: 43 54 20 64 6f 20 6e 6f 74 20 68 61 76 65 20 74  CT do not have t
4660: 68 65 20 73 61 6d 65 20 6e 75 6d 62 65 72 20 6f  he same number o
4670: 66 20 72 65 73 75 6c 74 20 63 6f 6c 75 6d 6e 73  f result columns
4680: 7d 7d 0a 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65  }}..do_test sele
4690: 63 74 34 2d 31 31 2e 31 31 20 7b 0a 20 20 63 61  ct4-11.11 {.  ca
46a0: 74 63 68 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c  tchsql {.    SEL
46b0: 45 43 54 20 78 20 46 52 4f 4d 20 74 32 0a 20 20  ECT x FROM t2.  
46c0: 20 20 55 4e 49 4f 4e 0a 20 20 20 20 53 45 4c 45    UNION.    SELE
46d0: 43 54 20 78 20 46 52 4f 4d 20 74 32 0a 20 20 20  CT x FROM t2.   
46e0: 20 55 4e 49 4f 4e 20 41 4c 4c 0a 20 20 20 20 53   UNION ALL.    S
46f0: 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 32 0a  ELECT x FROM t2.
4700: 20 20 20 20 45 58 43 45 50 54 0a 20 20 20 20 53      EXCEPT.    S
4710: 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 32 0a  ELECT x FROM t2.
4720: 20 20 20 20 49 4e 54 45 52 53 45 43 54 0a 20 20      INTERSECT.  
4730: 20 20 53 45 4c 45 43 54 20 78 2c 78 2c 78 2c 78    SELECT x,x,x,x
4740: 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78  ,x,x,x,x,x,x,x,x
4750: 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78  ,x,x,x,x,x,x,x,x
4760: 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78  ,x,x,x,x,x,x,x,x
4770: 2c 78 2c 78 2c 78 20 46 52 4f 4d 20 74 32 0a 20  ,x,x,x FROM t2. 
4780: 20 7d 0a 7d 20 7b 31 20 7b 53 45 4c 45 43 54 73   }.} {1 {SELECTs
4790: 20 74 6f 20 74 68 65 20 6c 65 66 74 20 61 6e 64   to the left and
47a0: 20 72 69 67 68 74 20 6f 66 20 49 4e 54 45 52 53   right of INTERS
47b0: 45 43 54 20 64 6f 20 6e 6f 74 20 68 61 76 65 20  ECT do not have 
47c0: 74 68 65 20 73 61 6d 65 20 6e 75 6d 62 65 72 20  the same number 
47d0: 6f 66 20 72 65 73 75 6c 74 20 63 6f 6c 75 6d 6e  of result column
47e0: 73 7d 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65  s}}.do_test sele
47f0: 63 74 34 2d 31 31 2e 31 32 20 7b 0a 20 20 63 61  ct4-11.12 {.  ca
4800: 74 63 68 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c  tchsql {.    SEL
4810: 45 43 54 20 78 20 46 52 4f 4d 20 74 32 0a 20 20  ECT x FROM t2.  
4820: 20 20 55 4e 49 4f 4e 0a 20 20 20 20 53 45 4c 45    UNION.    SELE
4830: 43 54 20 78 20 46 52 4f 4d 20 74 32 0a 20 20 20  CT x FROM t2.   
4840: 20 55 4e 49 4f 4e 20 41 4c 4c 0a 20 20 20 20 53   UNION ALL.    S
4850: 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 32 0a  ELECT x FROM t2.
4860: 20 20 20 20 45 58 43 45 50 54 0a 20 20 20 20 53      EXCEPT.    S
4870: 45 4c 45 43 54 20 78 2c 78 2c 78 2c 78 2c 78 2c  ELECT x,x,x,x,x,
4880: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
4890: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
48a0: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
48b0: 78 2c 78 20 46 52 4f 4d 20 74 32 0a 20 20 20 20  x,x FROM t2.    
48c0: 45 58 43 45 50 54 0a 20 20 20 20 53 45 4c 45 43  EXCEPT.    SELEC
48d0: 54 20 78 20 46 52 4f 4d 20 74 32 0a 20 20 7d 0a  T x FROM t2.  }.
48e0: 7d 20 7b 31 20 7b 53 45 4c 45 43 54 73 20 74 6f  } {1 {SELECTs to
48f0: 20 74 68 65 20 6c 65 66 74 20 61 6e 64 20 72 69   the left and ri
4900: 67 68 74 20 6f 66 20 45 58 43 45 50 54 20 64 6f  ght of EXCEPT do
4910: 20 6e 6f 74 20 68 61 76 65 20 74 68 65 20 73 61   not have the sa
4920: 6d 65 20 6e 75 6d 62 65 72 20 6f 66 20 72 65 73  me number of res
4930: 75 6c 74 20 63 6f 6c 75 6d 6e 73 7d 7d 0a 64 6f  ult columns}}.do
4940: 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 31 31  _test select4-11
4950: 2e 31 33 20 7b 0a 20 20 63 61 74 63 68 73 71 6c  .13 {.  catchsql
4960: 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 78 20   {.    SELECT x 
4970: 46 52 4f 4d 20 74 32 0a 20 20 20 20 55 4e 49 4f  FROM t2.    UNIO
4980: 4e 0a 20 20 20 20 53 45 4c 45 43 54 20 78 20 46  N.    SELECT x F
4990: 52 4f 4d 20 74 32 0a 20 20 20 20 55 4e 49 4f 4e  ROM t2.    UNION
49a0: 20 41 4c 4c 0a 20 20 20 20 53 45 4c 45 43 54 20   ALL.    SELECT 
49b0: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
49c0: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
49d0: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
49e0: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 20 46 52  x,x,x,x,x,x,x FR
49f0: 4f 4d 20 74 32 0a 20 20 20 20 55 4e 49 4f 4e 20  OM t2.    UNION 
4a00: 41 4c 4c 0a 20 20 20 20 53 45 4c 45 43 54 20 78  ALL.    SELECT x
4a10: 20 46 52 4f 4d 20 74 32 0a 20 20 20 20 45 58 43   FROM t2.    EXC
4a20: 45 50 54 0a 20 20 20 20 53 45 4c 45 43 54 20 78  EPT.    SELECT x
4a30: 20 46 52 4f 4d 20 74 32 0a 20 20 7d 0a 7d 20 7b   FROM t2.  }.} {
4a40: 31 20 7b 53 45 4c 45 43 54 73 20 74 6f 20 74 68  1 {SELECTs to th
4a50: 65 20 6c 65 66 74 20 61 6e 64 20 72 69 67 68 74  e left and right
4a60: 20 6f 66 20 55 4e 49 4f 4e 20 41 4c 4c 20 64 6f   of UNION ALL do
4a70: 20 6e 6f 74 20 68 61 76 65 20 74 68 65 20 73 61   not have the sa
4a80: 6d 65 20 6e 75 6d 62 65 72 20 6f 66 20 72 65 73  me number of res
4a90: 75 6c 74 20 63 6f 6c 75 6d 6e 73 7d 7d 0a 64 6f  ult columns}}.do
4aa0: 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 31 31  _test select4-11
4ab0: 2e 31 34 20 7b 0a 20 20 63 61 74 63 68 73 71 6c  .14 {.  catchsql
4ac0: 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 78 20   {.    SELECT x 
4ad0: 46 52 4f 4d 20 74 32 0a 20 20 20 20 55 4e 49 4f  FROM t2.    UNIO
4ae0: 4e 0a 20 20 20 20 53 45 4c 45 43 54 20 78 2c 78  N.    SELECT x,x
4af0: 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78  ,x,x,x,x,x,x,x,x
4b00: 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78  ,x,x,x,x,x,x,x,x
4b10: 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78  ,x,x,x,x,x,x,x,x
4b20: 2c 78 2c 78 2c 78 2c 78 2c 78 20 46 52 4f 4d 20  ,x,x,x,x,x FROM 
4b30: 74 32 0a 20 20 20 20 55 4e 49 4f 4e 0a 20 20 20  t2.    UNION.   
4b40: 20 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74   SELECT x FROM t
4b50: 32 0a 20 20 20 20 55 4e 49 4f 4e 20 41 4c 4c 0a  2.    UNION ALL.
4b60: 20 20 20 20 53 45 4c 45 43 54 20 78 20 46 52 4f      SELECT x FRO
4b70: 4d 20 74 32 0a 20 20 20 20 45 58 43 45 50 54 0a  M t2.    EXCEPT.
4b80: 20 20 20 20 53 45 4c 45 43 54 20 78 20 46 52 4f      SELECT x FRO
4b90: 4d 20 74 32 0a 20 20 7d 0a 7d 20 7b 31 20 7b 53  M t2.  }.} {1 {S
4ba0: 45 4c 45 43 54 73 20 74 6f 20 74 68 65 20 6c 65  ELECTs to the le
4bb0: 66 74 20 61 6e 64 20 72 69 67 68 74 20 6f 66 20  ft and right of 
4bc0: 55 4e 49 4f 4e 20 64 6f 20 6e 6f 74 20 68 61 76  UNION do not hav
4bd0: 65 20 74 68 65 20 73 61 6d 65 20 6e 75 6d 62 65  e the same numbe
4be0: 72 20 6f 66 20 72 65 73 75 6c 74 20 63 6f 6c 75  r of result colu
4bf0: 6d 6e 73 7d 7d 0a 64 6f 5f 74 65 73 74 20 73 65  mns}}.do_test se
4c00: 6c 65 63 74 34 2d 31 31 2e 31 35 20 7b 0a 20 20  lect4-11.15 {.  
4c10: 63 61 74 63 68 73 71 6c 20 7b 0a 20 20 20 20 53  catchsql {.    S
4c20: 45 4c 45 43 54 20 78 2c 78 2c 78 2c 78 2c 78 2c  ELECT x,x,x,x,x,
4c30: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
4c40: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
4c50: 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c 78 2c  x,x,x,x,x,x,x,x,
4c60: 78 2c 78 20 46 52 4f 4d 20 74 32 0a 20 20 20 20  x,x FROM t2.    
4c70: 55 4e 49 4f 4e 0a 20 20 20 20 53 45 4c 45 43 54  UNION.    SELECT
4c80: 20 78 20 46 52 4f 4d 20 74 32 0a 20 20 20 20 49   x FROM t2.    I
4c90: 4e 54 45 52 53 45 43 54 0a 20 20 20 20 53 45 4c  NTERSECT.    SEL
4ca0: 45 43 54 20 78 20 46 52 4f 4d 20 74 32 0a 20 20  ECT x FROM t2.  
4cb0: 20 20 55 4e 49 4f 4e 20 41 4c 4c 0a 20 20 20 20    UNION ALL.    
4cc0: 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 32  SELECT x FROM t2
4cd0: 0a 20 20 20 20 45 58 43 45 50 54 0a 20 20 20 20  .    EXCEPT.    
4ce0: 53 45 4c 45 43 54 20 78 20 46 52 4f 4d 20 74 32  SELECT x FROM t2
4cf0: 0a 20 20 7d 0a 7d 20 7b 31 20 7b 53 45 4c 45 43  .  }.} {1 {SELEC
4d00: 54 73 20 74 6f 20 74 68 65 20 6c 65 66 74 20 61  Ts to the left a
4d10: 6e 64 20 72 69 67 68 74 20 6f 66 20 55 4e 49 4f  nd right of UNIO
4d20: 4e 20 64 6f 20 6e 6f 74 20 68 61 76 65 20 74 68  N do not have th
4d30: 65 20 73 61 6d 65 20 6e 75 6d 62 65 72 20 6f 66  e same number of
4d40: 20 72 65 73 75 6c 74 20 63 6f 6c 75 6d 6e 73 7d   result columns}
4d50: 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74  }.do_test select
4d60: 34 2d 31 31 2e 31 36 20 7b 0a 20 20 63 61 74 63  4-11.16 {.  catc
4d70: 68 73 71 6c 20 7b 0a 20 20 20 20 49 4e 53 45 52  hsql {.    INSER
4d80: 54 20 49 4e 54 4f 20 74 32 28 72 6f 77 69 64 29  T INTO t2(rowid)
4d90: 20 56 41 4c 55 45 53 28 32 29 20 55 4e 49 4f 4e   VALUES(2) UNION
4da0: 20 53 45 4c 45 43 54 20 33 2c 34 20 55 4e 49 4f   SELECT 3,4 UNIO
4db0: 4e 20 53 45 4c 45 43 54 20 35 2c 36 20 4f 52 44  N SELECT 5,6 ORD
4dc0: 45 52 20 42 59 20 31 3b 0a 20 20 7d 0a 7d 20 7b  ER BY 1;.  }.} {
4dd0: 31 20 7b 53 45 4c 45 43 54 73 20 74 6f 20 74 68  1 {SELECTs to th
4de0: 65 20 6c 65 66 74 20 61 6e 64 20 72 69 67 68 74  e left and right
4df0: 20 6f 66 20 55 4e 49 4f 4e 20 64 6f 20 6e 6f 74   of UNION do not
4e00: 20 68 61 76 65 20 74 68 65 20 73 61 6d 65 20 6e   have the same n
4e10: 75 6d 62 65 72 20 6f 66 20 72 65 73 75 6c 74 20  umber of result 
4e20: 63 6f 6c 75 6d 6e 73 7d 7d 0a 0a 64 6f 5f 74 65  columns}}..do_te
4e30: 73 74 20 73 65 6c 65 63 74 34 2d 31 32 2e 31 20  st select4-12.1 
4e40: 7b 0a 20 20 73 71 6c 69 74 65 33 20 64 62 32 20  {.  sqlite3 db2 
4e50: 3a 6d 65 6d 6f 72 79 3a 0a 20 20 63 61 74 63 68  :memory:.  catch
4e60: 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54  sql {.    SELECT
4e70: 20 31 20 55 4e 49 4f 4e 20 53 45 4c 45 43 54 20   1 UNION SELECT 
4e80: 32 2c 33 20 55 4e 49 4f 4e 20 53 45 4c 45 43 54  2,3 UNION SELECT
4e90: 20 34 2c 35 20 4f 52 44 45 52 20 42 59 20 31 3b   4,5 ORDER BY 1;
4ea0: 0a 20 20 7d 20 64 62 32 0a 7d 20 7b 31 20 7b 53  .  } db2.} {1 {S
4eb0: 45 4c 45 43 54 73 20 74 6f 20 74 68 65 20 6c 65  ELECTs to the le
4ec0: 66 74 20 61 6e 64 20 72 69 67 68 74 20 6f 66 20  ft and right of 
4ed0: 55 4e 49 4f 4e 20 64 6f 20 6e 6f 74 20 68 61 76  UNION do not hav
4ee0: 65 20 74 68 65 20 73 61 6d 65 20 6e 75 6d 62 65  e the same numbe
4ef0: 72 20 6f 66 20 72 65 73 75 6c 74 20 63 6f 6c 75  r of result colu
4f00: 6d 6e 73 7d 7d 0a 0a 7d 20 3b 23 20 69 66 63 61  mns}}..} ;# ifca
4f10: 70 61 62 6c 65 20 63 6f 6d 70 6f 75 6e 64 0a 0a  pable compound..
4f20: 0a 23 20 54 69 63 6b 65 74 20 5b 33 35 35 37 61  .# Ticket [3557a
4f30: 64 36 35 61 30 37 36 63 5d 20 2d 20 49 6e 63 6f  d65a076c] - Inco
4f40: 72 72 65 63 74 20 44 49 53 54 49 4e 43 54 20 70  rrect DISTINCT p
4f50: 72 6f 63 65 73 73 69 6e 67 20 77 69 74 68 20 61  rocessing with a
4f60: 6e 0a 23 20 69 6e 64 65 78 65 64 20 71 75 65 72  n.# indexed quer
4f70: 79 20 75 73 69 6e 67 20 49 4e 2e 0a 23 0a 64 6f  y using IN..#.do
4f80: 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 31 33  _test select4-13
4f90: 2e 31 20 7b 0a 20 20 73 71 6c 69 74 65 33 20 64  .1 {.  sqlite3 d
4fa0: 62 20 74 65 73 74 2e 64 62 0a 20 20 64 62 20 65  b test.db.  db e
4fb0: 76 61 6c 20 7b 0a 20 20 20 20 43 52 45 41 54 45  val {.    CREATE
4fc0: 20 54 41 42 4c 45 20 74 31 33 28 61 2c 62 29 3b   TABLE t13(a,b);
4fd0: 0a 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f  .    INSERT INTO
4fe0: 20 74 31 33 20 56 41 4c 55 45 53 28 31 2c 31 29   t13 VALUES(1,1)
4ff0: 3b 0a 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54  ;.    INSERT INT
5000: 4f 20 74 31 33 20 56 41 4c 55 45 53 28 32 2c 31  O t13 VALUES(2,1
5010: 29 3b 0a 20 20 20 20 49 4e 53 45 52 54 20 49 4e  );.    INSERT IN
5020: 54 4f 20 74 31 33 20 56 41 4c 55 45 53 28 33 2c  TO t13 VALUES(3,
5030: 31 29 3b 0a 20 20 20 20 49 4e 53 45 52 54 20 49  1);.    INSERT I
5040: 4e 54 4f 20 74 31 33 20 56 41 4c 55 45 53 28 32  NTO t13 VALUES(2
5050: 2c 32 29 3b 0a 20 20 20 20 49 4e 53 45 52 54 20  ,2);.    INSERT 
5060: 49 4e 54 4f 20 74 31 33 20 56 41 4c 55 45 53 28  INTO t13 VALUES(
5070: 33 2c 32 29 3b 0a 20 20 20 20 49 4e 53 45 52 54  3,2);.    INSERT
5080: 20 49 4e 54 4f 20 74 31 33 20 56 41 4c 55 45 53   INTO t13 VALUES
5090: 28 34 2c 32 29 3b 0a 20 20 20 20 43 52 45 41 54  (4,2);.    CREAT
50a0: 45 20 49 4e 44 45 58 20 74 31 33 61 62 20 4f 4e  E INDEX t13ab ON
50b0: 20 74 31 33 28 61 2c 62 29 3b 0a 20 20 20 20 53   t13(a,b);.    S
50c0: 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54 20 62  ELECT DISTINCT b
50d0: 20 66 72 6f 6d 20 74 31 33 20 57 48 45 52 45 20   from t13 WHERE 
50e0: 61 20 49 4e 20 28 31 2c 32 2c 33 29 3b 0a 20 20  a IN (1,2,3);.  
50f0: 7d 0a 7d 20 7b 31 20 32 7d 0a 0a 23 20 32 30 31  }.} {1 2}..# 201
5100: 34 2d 30 32 2d 31 38 3a 20 4d 61 6b 65 20 73 75  4-02-18: Make su
5110: 72 65 20 63 6f 6d 70 6f 75 6e 64 20 53 45 4c 45  re compound SELE
5120: 43 54 73 20 77 6f 72 6b 20 77 69 74 68 20 56 41  CTs work with VA
5130: 4c 55 45 53 20 63 6c 61 75 73 65 73 0a 23 0a 64  LUES clauses.#.d
5140: 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 74 20 73  o_execsql_test s
5150: 65 6c 65 63 74 34 2d 31 34 2e 31 20 7b 0a 20 20  elect4-14.1 {.  
5160: 43 52 45 41 54 45 20 54 41 42 4c 45 20 74 31 34  CREATE TABLE t14
5170: 28 61 2c 62 2c 63 29 3b 0a 20 20 49 4e 53 45 52  (a,b,c);.  INSER
5180: 54 20 49 4e 54 4f 20 74 31 34 20 56 41 4c 55 45  T INTO t14 VALUE
5190: 53 28 31 2c 32 2c 33 29 2c 28 34 2c 35 2c 36 29  S(1,2,3),(4,5,6)
51a0: 3b 0a 20 20 53 45 4c 45 43 54 20 2a 20 46 52 4f  ;.  SELECT * FRO
51b0: 4d 20 74 31 34 20 49 4e 54 45 52 53 45 43 54 20  M t14 INTERSECT 
51c0: 56 41 4c 55 45 53 28 33 2c 32 2c 31 29 2c 28 32  VALUES(3,2,1),(2
51d0: 2c 33 2c 31 29 2c 28 31 2c 32 2c 33 29 2c 28 32  ,3,1),(1,2,3),(2
51e0: 2c 31 2c 33 29 3b 0a 7d 20 7b 31 20 32 20 33 7d  ,1,3);.} {1 2 3}
51f0: 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 74  .do_execsql_test
5200: 20 73 65 6c 65 63 74 34 2d 31 34 2e 32 20 7b 0a   select4-14.2 {.
5210: 20 20 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20    SELECT * FROM 
5220: 74 31 34 20 49 4e 54 45 52 53 45 43 54 20 56 41  t14 INTERSECT VA
5230: 4c 55 45 53 28 31 2c 32 2c 33 29 3b 0a 7d 20 7b  LUES(1,2,3);.} {
5240: 31 20 32 20 33 7d 0a 64 6f 5f 65 78 65 63 73 71  1 2 3}.do_execsq
5250: 6c 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 31  l_test select4-1
5260: 34 2e 33 20 7b 0a 20 20 53 45 4c 45 43 54 20 2a  4.3 {.  SELECT *
5270: 20 46 52 4f 4d 20 74 31 34 0a 20 20 20 55 4e 49   FROM t14.   UNI
5280: 4f 4e 20 56 41 4c 55 45 53 28 33 2c 32 2c 31 29  ON VALUES(3,2,1)
5290: 2c 28 32 2c 33 2c 31 29 2c 28 31 2c 32 2c 33 29  ,(2,3,1),(1,2,3)
52a0: 2c 28 37 2c 38 2c 39 29 2c 28 34 2c 35 2c 36 29  ,(7,8,9),(4,5,6)
52b0: 0a 20 20 20 55 4e 49 4f 4e 20 53 45 4c 45 43 54  .   UNION SELECT
52c0: 20 2a 20 46 52 4f 4d 20 74 31 34 20 4f 52 44 45   * FROM t14 ORDE
52d0: 52 20 42 59 20 31 2c 20 32 2c 20 33 0a 7d 20 7b  R BY 1, 2, 3.} {
52e0: 31 20 32 20 33 20 32 20 33 20 31 20 33 20 32 20  1 2 3 2 3 1 3 2 
52f0: 31 20 34 20 35 20 36 20 37 20 38 20 39 7d 0a 64  1 4 5 6 7 8 9}.d
5300: 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 74 20 73  o_execsql_test s
5310: 65 6c 65 63 74 34 2d 31 34 2e 34 20 7b 0a 20 20  elect4-14.4 {.  
5320: 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 74 31  SELECT * FROM t1
5330: 34 0a 20 20 20 55 4e 49 4f 4e 20 56 41 4c 55 45  4.   UNION VALUE
5340: 53 28 33 2c 32 2c 31 29 0a 20 20 20 55 4e 49 4f  S(3,2,1).   UNIO
5350: 4e 20 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20  N SELECT * FROM 
5360: 74 31 34 20 4f 52 44 45 52 20 42 59 20 31 2c 20  t14 ORDER BY 1, 
5370: 32 2c 20 33 0a 7d 20 7b 31 20 32 20 33 20 33 20  2, 3.} {1 2 3 3 
5380: 32 20 31 20 34 20 35 20 36 7d 0a 64 6f 5f 65 78  2 1 4 5 6}.do_ex
5390: 65 63 73 71 6c 5f 74 65 73 74 20 73 65 6c 65 63  ecsql_test selec
53a0: 74 34 2d 31 34 2e 35 20 7b 0a 20 20 53 45 4c 45  t4-14.5 {.  SELE
53b0: 43 54 20 2a 20 46 52 4f 4d 20 74 31 34 20 45 58  CT * FROM t14 EX
53c0: 43 45 50 54 20 56 41 4c 55 45 53 28 33 2c 32 2c  CEPT VALUES(3,2,
53d0: 31 29 2c 28 32 2c 33 2c 31 29 2c 28 31 2c 32 2c  1),(2,3,1),(1,2,
53e0: 33 29 2c 28 32 2c 31 2c 33 29 3b 0a 7d 20 7b 34  3),(2,1,3);.} {4
53f0: 20 35 20 36 7d 0a 64 6f 5f 65 78 65 63 73 71 6c   5 6}.do_execsql
5400: 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 31 34  _test select4-14
5410: 2e 36 20 7b 0a 20 20 53 45 4c 45 43 54 20 2a 20  .6 {.  SELECT * 
5420: 46 52 4f 4d 20 74 31 34 20 45 58 43 45 50 54 20  FROM t14 EXCEPT 
5430: 56 41 4c 55 45 53 28 31 2c 32 2c 33 29 0a 7d 20  VALUES(1,2,3).} 
5440: 7b 34 20 35 20 36 7d 0a 64 6f 5f 65 78 65 63 73  {4 5 6}.do_execs
5450: 71 6c 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d  ql_test select4-
5460: 31 34 2e 37 20 7b 0a 20 20 53 45 4c 45 43 54 20  14.7 {.  SELECT 
5470: 2a 20 46 52 4f 4d 20 74 31 34 20 45 58 43 45 50  * FROM t14 EXCEP
5480: 54 20 56 41 4c 55 45 53 28 31 2c 32 2c 33 29 20  T VALUES(1,2,3) 
5490: 45 58 43 45 50 54 20 56 41 4c 55 45 53 28 34 2c  EXCEPT VALUES(4,
54a0: 35 2c 36 29 0a 7d 20 7b 7d 0a 64 6f 5f 65 78 65  5,6).} {}.do_exe
54b0: 63 73 71 6c 5f 74 65 73 74 20 73 65 6c 65 63 74  csql_test select
54c0: 34 2d 31 34 2e 38 20 7b 0a 20 20 53 45 4c 45 43  4-14.8 {.  SELEC
54d0: 54 20 2a 20 46 52 4f 4d 20 74 31 34 20 45 58 43  T * FROM t14 EXC
54e0: 45 50 54 20 56 41 4c 55 45 53 28 27 61 27 2c 27  EPT VALUES('a','
54f0: 62 27 2c 27 63 27 29 20 45 58 43 45 50 54 20 56  b','c') EXCEPT V
5500: 41 4c 55 45 53 28 34 2c 35 2c 36 29 0a 7d 20 7b  ALUES(4,5,6).} {
5510: 31 20 32 20 33 7d 0a 64 6f 5f 65 78 65 63 73 71  1 2 3}.do_execsq
5520: 6c 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 31  l_test select4-1
5530: 34 2e 39 20 7b 0a 20 20 53 45 4c 45 43 54 20 2a  4.9 {.  SELECT *
5540: 20 46 52 4f 4d 20 74 31 34 20 55 4e 49 4f 4e 20   FROM t14 UNION 
5550: 41 4c 4c 20 56 41 4c 55 45 53 28 33 2c 32 2c 31  ALL VALUES(3,2,1
5560: 29 2c 28 32 2c 33 2c 31 29 2c 28 31 2c 32 2c 33  ),(2,3,1),(1,2,3
5570: 29 2c 28 32 2c 31 2c 33 29 3b 0a 7d 20 7b 31 20  ),(2,1,3);.} {1 
5580: 32 20 33 20 34 20 35 20 36 20 33 20 32 20 31 20  2 3 4 5 6 3 2 1 
5590: 32 20 33 20 31 20 31 20 32 20 33 20 32 20 31 20  2 3 1 1 2 3 2 1 
55a0: 33 7d 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65  3}.do_execsql_te
55b0: 73 74 20 73 65 6c 65 63 74 34 2d 31 34 2e 31 30  st select4-14.10
55c0: 20 7b 0a 20 20 53 45 4c 45 43 54 20 28 56 41 4c   {.  SELECT (VAL
55d0: 55 45 53 28 31 29 2c 28 32 29 2c 28 33 29 2c 28  UES(1),(2),(3),(
55e0: 34 29 29 0a 7d 20 7b 31 7d 0a 64 6f 5f 65 78 65  4)).} {1}.do_exe
55f0: 63 73 71 6c 5f 74 65 73 74 20 73 65 6c 65 63 74  csql_test select
5600: 34 2d 31 34 2e 31 31 20 7b 0a 20 20 53 45 4c 45  4-14.11 {.  SELE
5610: 43 54 20 28 53 45 4c 45 43 54 20 31 20 55 4e 49  CT (SELECT 1 UNI
5620: 4f 4e 20 41 4c 4c 20 53 45 4c 45 43 54 20 32 20  ON ALL SELECT 2 
5630: 55 4e 49 4f 4e 20 41 4c 4c 20 53 45 4c 45 43 54  UNION ALL SELECT
5640: 20 33 20 55 4e 49 4f 4e 20 41 4c 4c 20 53 45 4c   3 UNION ALL SEL
5650: 45 43 54 20 34 29 0a 7d 20 7b 31 7d 0a 64 6f 5f  ECT 4).} {1}.do_
5660: 65 78 65 63 73 71 6c 5f 74 65 73 74 20 73 65 6c  execsql_test sel
5670: 65 63 74 34 2d 31 34 2e 31 32 20 7b 0a 20 20 56  ect4-14.12 {.  V
5680: 41 4c 55 45 53 28 31 29 20 55 4e 49 4f 4e 20 56  ALUES(1) UNION V
5690: 41 4c 55 45 53 28 32 29 3b 0a 7d 20 7b 31 20 32  ALUES(2);.} {1 2
56a0: 7d 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73  }.do_execsql_tes
56b0: 74 20 73 65 6c 65 63 74 34 2d 31 34 2e 31 33 20  t select4-14.13 
56c0: 7b 0a 20 20 56 41 4c 55 45 53 28 31 29 2c 28 32  {.  VALUES(1),(2
56d0: 29 2c 28 33 29 20 45 58 43 45 50 54 20 56 41 4c  ),(3) EXCEPT VAL
56e0: 55 45 53 28 32 29 3b 0a 7d 20 7b 31 20 33 7d 0a  UES(2);.} {1 3}.
56f0: 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 74 20  do_execsql_test 
5700: 73 65 6c 65 63 74 34 2d 31 34 2e 31 34 20 7b 0a  select4-14.14 {.
5710: 20 20 56 41 4c 55 45 53 28 31 29 2c 28 32 29 2c    VALUES(1),(2),
5720: 28 33 29 20 45 58 43 45 50 54 20 56 41 4c 55 45  (3) EXCEPT VALUE
5730: 53 28 31 29 2c 28 33 29 3b 0a 7d 20 7b 32 7d 0a  S(1),(3);.} {2}.
5740: 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 74 20  do_execsql_test 
5750: 73 65 6c 65 63 74 34 2d 31 34 2e 31 35 20 7b 0a  select4-14.15 {.
5760: 20 20 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20    SELECT * FROM 
5770: 28 53 45 4c 45 43 54 20 31 32 33 29 2c 20 28 53  (SELECT 123), (S
5780: 45 4c 45 43 54 20 34 35 36 29 20 4f 4e 20 6c 69  ELECT 456) ON li
5790: 6b 65 6c 79 28 30 20 4f 52 20 31 29 20 4f 52 20  kely(0 OR 1) OR 
57a0: 30 3b 0a 7d 20 7b 31 32 33 20 34 35 36 7d 0a 64  0;.} {123 456}.d
57b0: 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 74 20 73  o_execsql_test s
57c0: 65 6c 65 63 74 34 2d 31 34 2e 31 36 20 7b 0a 20  elect4-14.16 {. 
57d0: 20 56 41 4c 55 45 53 28 31 29 2c 28 32 29 2c 28   VALUES(1),(2),(
57e0: 33 29 2c 28 34 29 20 55 4e 49 4f 4e 20 41 4c 4c  3),(4) UNION ALL
57f0: 20 53 45 4c 45 43 54 20 35 20 4c 49 4d 49 54 20   SELECT 5 LIMIT 
5800: 39 39 3b 0a 7d 20 7b 31 20 32 20 33 20 34 20 35  99;.} {1 2 3 4 5
5810: 7d 0a 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73  }.do_execsql_tes
5820: 74 20 73 65 6c 65 63 74 34 2d 31 34 2e 31 37 20  t select4-14.17 
5830: 7b 0a 20 20 56 41 4c 55 45 53 28 31 29 2c 28 32  {.  VALUES(1),(2
5840: 29 2c 28 33 29 2c 28 34 29 20 55 4e 49 4f 4e 20  ),(3),(4) UNION 
5850: 41 4c 4c 20 53 45 4c 45 43 54 20 35 20 4c 49 4d  ALL SELECT 5 LIM
5860: 49 54 20 33 3b 0a 7d 20 7b 31 20 32 20 33 7d 0a  IT 3;.} {1 2 3}.
5870: 0a 23 20 54 69 63 6b 65 74 20 68 74 74 70 73 3a  .# Ticket https:
5880: 2f 2f 77 77 77 2e 73 71 6c 69 74 65 2e 6f 72 67  //www.sqlite.org
5890: 2f 73 72 63 2f 69 6e 66 6f 2f 64 30 36 61 32 35  /src/info/d06a25
58a0: 63 38 34 34 35 34 61 33 37 32 0a 23 20 49 6e 63  c84454a372.# Inc
58b0: 6f 72 72 65 63 74 20 61 6e 73 77 65 72 20 64 75  orrect answer du
58c0: 65 20 74 6f 20 74 77 6f 20 63 6f 2d 72 6f 75 74  e to two co-rout
58d0: 69 6e 65 73 20 75 73 69 6e 67 20 74 68 65 20 73  ines using the s
58e0: 61 6d 65 20 72 65 67 69 73 74 65 72 73 20 61 6e  ame registers an
58f0: 64 20 65 78 70 65 63 74 69 6e 67 0a 23 20 74 68  d expecting.# th
5900: 6f 73 65 20 72 65 67 69 73 74 65 72 20 76 61 6c  ose register val
5910: 75 65 73 20 74 6f 20 62 65 20 70 72 65 73 65 72  ues to be preser
5920: 76 65 64 20 61 63 72 6f 73 73 20 61 20 59 69 65  ved across a Yie
5930: 6c 64 2e 0a 23 0a 64 6f 5f 65 78 65 63 73 71 6c  ld..#.do_execsql
5940: 5f 74 65 73 74 20 73 65 6c 65 63 74 34 2d 31 35  _test select4-15
5950: 2e 31 20 7b 0a 20 20 44 52 4f 50 20 54 41 42 4c  .1 {.  DROP TABL
5960: 45 20 49 46 20 45 58 49 53 54 53 20 74 78 3b 0a  E IF EXISTS tx;.
5970: 20 20 43 52 45 41 54 45 20 54 41 42 4c 45 20 74    CREATE TABLE t
5980: 78 28 69 64 20 49 4e 54 45 47 45 52 20 50 52 49  x(id INTEGER PRI
5990: 4d 41 52 59 20 4b 45 59 2c 20 61 2c 20 62 29 3b  MARY KEY, a, b);
59a0: 0a 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74  .  INSERT INTO t
59b0: 78 28 61 2c 62 29 20 56 41 4c 55 45 53 28 33 33  x(a,b) VALUES(33
59c0: 2c 34 35 36 29 3b 0a 20 20 49 4e 53 45 52 54 20  ,456);.  INSERT 
59d0: 49 4e 54 4f 20 74 78 28 61 2c 62 29 20 56 41 4c  INTO tx(a,b) VAL
59e0: 55 45 53 28 33 33 2c 37 38 39 29 3b 0a 0a 20 20  UES(33,789);..  
59f0: 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54 20  SELECT DISTINCT 
5a00: 74 30 2e 69 64 2c 20 74 30 2e 61 2c 20 74 30 2e  t0.id, t0.a, t0.
5a10: 62 0a 20 20 20 20 46 52 4f 4d 20 74 78 20 41 53  b.    FROM tx AS
5a20: 20 74 30 2c 20 74 78 20 41 53 20 74 31 0a 20 20   t0, tx AS t1.  
5a30: 20 57 48 45 52 45 20 74 30 2e 61 3d 74 31 2e 61   WHERE t0.a=t1.a
5a40: 20 41 4e 44 20 74 31 2e 61 3d 33 33 20 41 4e 44   AND t1.a=33 AND
5a50: 20 74 30 2e 62 3d 34 35 36 0a 20 20 55 4e 49 4f   t0.b=456.  UNIO
5a60: 4e 0a 20 20 53 45 4c 45 43 54 20 44 49 53 54 49  N.  SELECT DISTI
5a70: 4e 43 54 20 74 30 2e 69 64 2c 20 74 30 2e 61 2c  NCT t0.id, t0.a,
5a80: 20 74 30 2e 62 0a 20 20 20 20 46 52 4f 4d 20 74   t0.b.    FROM t
5a90: 78 20 41 53 20 74 30 2c 20 74 78 20 41 53 20 74  x AS t0, tx AS t
5aa0: 31 0a 20 20 20 57 48 45 52 45 20 74 30 2e 61 3d  1.   WHERE t0.a=
5ab0: 74 31 2e 61 20 41 4e 44 20 74 31 2e 61 3d 33 33  t1.a AND t1.a=33
5ac0: 20 41 4e 44 20 74 30 2e 62 3d 37 38 39 0a 20 20   AND t0.b=789.  
5ad0: 20 4f 52 44 45 52 20 42 59 20 31 3b 0a 7d 20 7b   ORDER BY 1;.} {
5ae0: 31 20 33 33 20 34 35 36 20 32 20 33 33 20 37 38  1 33 456 2 33 78
5af0: 39 7d 0a 0a 23 20 45 6e 68 61 6e 63 65 6d 65 6e  9}..# Enhancemen
5b00: 74 20 28 32 30 31 36 2d 30 33 2d 31 35 29 3a 20  t (2016-03-15): 
5b10: 20 55 73 65 20 61 20 63 6f 2d 72 6f 75 74 69 6e   Use a co-routin
5b20: 65 20 66 6f 72 20 73 75 62 71 75 65 72 69 65 73  e for subqueries
5b30: 20 69 66 20 74 68 65 0a 23 20 73 75 62 71 75 65   if the.# subque
5b40: 72 79 20 69 73 20 67 75 61 72 61 6e 74 65 65 64  ry is guaranteed
5b50: 20 74 6f 20 62 65 20 74 68 65 20 6f 75 74 65 72   to be the outer
5b60: 2d 6d 6f 73 74 20 71 75 65 72 79 0a 23 0a 64 6f  -most query.#.do
5b70: 5f 65 78 65 63 73 71 6c 5f 74 65 73 74 20 73 65  _execsql_test se
5b80: 6c 65 63 74 34 2d 31 36 2e 31 20 7b 0a 20 20 44  lect4-16.1 {.  D
5b90: 52 4f 50 20 54 41 42 4c 45 20 49 46 20 45 58 49  ROP TABLE IF EXI
5ba0: 53 54 53 20 74 31 3b 0a 20 20 43 52 45 41 54 45  STS t1;.  CREATE
5bb0: 20 54 41 42 4c 45 20 74 31 28 61 2c 62 2c 63 2c   TABLE t1(a,b,c,
5bc0: 64 2c 65 2c 66 2c 67 2c 68 2c 69 2c 6a 2c 6b 2c  d,e,f,g,h,i,j,k,
5bd0: 6c 2c 6d 2c 6e 2c 6f 2c 70 2c 71 2c 72 2c 73 2c  l,m,n,o,p,q,r,s,
5be0: 74 2c 75 2c 76 2c 77 2c 78 2c 79 2c 7a 2c 0a 20  t,u,v,w,x,y,z,. 
5bf0: 20 50 52 49 4d 41 52 59 20 4b 45 59 28 61 2c 62   PRIMARY KEY(a,b
5c00: 20 44 45 53 43 29 29 20 57 49 54 48 4f 55 54 20   DESC)) WITHOUT 
5c10: 52 4f 57 49 44 3b 0a 0a 20 20 57 49 54 48 20 52  ROWID;..  WITH R
5c20: 45 43 55 52 53 49 56 45 20 63 28 78 29 20 41 53  ECURSIVE c(x) AS
5c30: 20 28 56 41 4c 55 45 53 28 31 29 20 55 4e 49 4f   (VALUES(1) UNIO
5c40: 4e 20 41 4c 4c 20 53 45 4c 45 43 54 20 78 2b 31  N ALL SELECT x+1
5c50: 20 46 52 4f 4d 20 63 20 57 48 45 52 45 20 78 3c   FROM c WHERE x<
5c60: 31 30 30 29 0a 20 20 49 4e 53 45 52 54 20 49 4e  100).  INSERT IN
5c70: 54 4f 20 74 31 28 61 2c 62 2c 63 2c 64 29 0a 20  TO t1(a,b,c,d). 
5c80: 20 20 20 53 45 4c 45 43 54 20 78 25 31 30 2c 20     SELECT x%10, 
5c90: 78 2f 31 30 2c 20 78 2c 20 70 72 69 6e 74 66 28  x/10, x, printf(
5ca0: 27 78 79 7a 25 64 61 62 63 27 2c 78 29 20 46 52  'xyz%dabc',x) FR
5cb0: 4f 4d 20 63 3b 0a 0a 20 20 53 45 4c 45 43 54 20  OM c;..  SELECT 
5cc0: 74 33 2e 63 20 46 52 4f 4d 20 0a 20 20 20 20 28  t3.c FROM .    (
5cd0: 53 45 4c 45 43 54 20 61 2c 6d 61 78 28 62 29 20  SELECT a,max(b) 
5ce0: 41 53 20 6d 20 46 52 4f 4d 20 74 31 20 57 48 45  AS m FROM t1 WHE
5cf0: 52 45 20 61 3e 3d 35 20 47 52 4f 55 50 20 42 59  RE a>=5 GROUP BY
5d00: 20 61 29 20 41 53 20 74 32 0a 20 20 20 20 4a 4f   a) AS t2.    JO
5d10: 49 4e 20 74 31 20 41 53 20 74 33 0a 20 20 57 48  IN t1 AS t3.  WH
5d20: 45 52 45 20 74 32 2e 61 3d 74 33 2e 61 20 41 4e  ERE t2.a=t3.a AN
5d30: 44 20 74 32 2e 6d 3d 74 33 2e 62 0a 20 20 4f 52  D t2.m=t3.b.  OR
5d40: 44 45 52 20 42 59 20 74 33 2e 61 3b 0a 7d 20 7b  DER BY t3.a;.} {
5d50: 39 35 20 39 36 20 39 37 20 39 38 20 39 39 7d 0a  95 96 97 98 99}.
5d60: 64 6f 5f 65 78 65 63 73 71 6c 5f 74 65 73 74 20  do_execsql_test 
5d70: 73 65 6c 65 63 74 34 2d 31 36 2e 32 20 7b 0a 20  select4-16.2 {. 
5d80: 20 53 45 4c 45 43 54 20 74 33 2e 63 20 46 52 4f   SELECT t3.c FRO
5d90: 4d 20 0a 20 20 20 20 28 53 45 4c 45 43 54 20 61  M .    (SELECT a
5da0: 2c 6d 61 78 28 62 29 20 41 53 20 6d 20 46 52 4f  ,max(b) AS m FRO
5db0: 4d 20 74 31 20 57 48 45 52 45 20 61 3e 3d 35 20  M t1 WHERE a>=5 
5dc0: 47 52 4f 55 50 20 42 59 20 61 29 20 41 53 20 74  GROUP BY a) AS t
5dd0: 32 0a 20 20 20 20 43 52 4f 53 53 20 4a 4f 49 4e  2.    CROSS JOIN
5de0: 20 74 31 20 41 53 20 74 33 0a 20 20 57 48 45 52   t1 AS t3.  WHER
5df0: 45 20 74 32 2e 61 3d 74 33 2e 61 20 41 4e 44 20  E t2.a=t3.a AND 
5e00: 74 32 2e 6d 3d 74 33 2e 62 0a 20 20 4f 52 44 45  t2.m=t3.b.  ORDE
5e10: 52 20 42 59 20 74 33 2e 61 3b 0a 7d 20 7b 39 35  R BY t3.a;.} {95
5e20: 20 39 36 20 39 37 20 39 38 20 39 39 7d 0a 64 6f   96 97 98 99}.do
5e30: 5f 65 78 65 63 73 71 6c 5f 74 65 73 74 20 73 65  _execsql_test se
5e40: 6c 65 63 74 34 2d 31 36 2e 33 20 7b 0a 20 20 53  lect4-16.3 {.  S
5e50: 45 4c 45 43 54 20 74 33 2e 63 20 46 52 4f 4d 20  ELECT t3.c FROM 
5e60: 0a 20 20 20 20 28 53 45 4c 45 43 54 20 61 2c 6d  .    (SELECT a,m
5e70: 61 78 28 62 29 20 41 53 20 6d 20 46 52 4f 4d 20  ax(b) AS m FROM 
5e80: 74 31 20 57 48 45 52 45 20 61 3e 3d 35 20 47 52  t1 WHERE a>=5 GR
5e90: 4f 55 50 20 42 59 20 61 29 20 41 53 20 74 32 0a  OUP BY a) AS t2.
5ea0: 20 20 20 20 4c 45 46 54 20 4a 4f 49 4e 20 74 31      LEFT JOIN t1
5eb0: 20 41 53 20 74 33 0a 20 20 57 48 45 52 45 20 74   AS t3.  WHERE t
5ec0: 32 2e 61 3d 74 33 2e 61 20 41 4e 44 20 74 32 2e  2.a=t3.a AND t2.
5ed0: 6d 3d 74 33 2e 62 0a 20 20 4f 52 44 45 52 20 42  m=t3.b.  ORDER B
5ee0: 59 20 74 33 2e 61 3b 0a 7d 20 7b 39 35 20 39 36  Y t3.a;.} {95 96
5ef0: 20 39 37 20 39 38 20 39 39 7d 0a 0a 23 20 54 69   97 98 99}..# Ti
5f00: 63 6b 65 74 20 68 74 74 70 73 3a 2f 2f 77 77 77  cket https://www
5f10: 2e 73 71 6c 69 74 65 2e 6f 72 67 2f 73 72 63 2f  .sqlite.org/src/
5f20: 74 6b 74 76 69 65 77 2f 66 37 66 38 63 39 37 65  tktview/f7f8c97e
5f30: 39 37 35 39 37 38 64 34 35 20 20 6f 6e 20 32 30  975978d45  on 20
5f40: 31 36 2d 30 34 2d 32 35 0a 23 0a 23 20 54 68 65  16-04-25.#.# The
5f50: 20 77 68 65 72 65 20 70 75 73 68 2d 64 6f 77 6e   where push-down
5f60: 20 6f 70 74 69 6d 69 7a 61 74 69 6f 6e 20 66 72   optimization fr
5f70: 6f 6d 20 32 30 31 35 2d 30 36 2d 30 32 20 69 73  om 2015-06-02 is
5f80: 20 73 75 70 70 6f 73 65 20 74 6f 20 64 69 73 61   suppose to disa
5f90: 62 6c 65 0a 23 20 6f 6e 20 61 67 67 72 65 67 61  ble.# on aggrega
5fa0: 74 65 20 73 75 62 71 75 65 72 69 65 73 2e 20 20  te subqueries.  
5fb0: 42 75 74 20 69 66 20 74 68 65 20 73 75 62 71 75  But if the subqu
5fc0: 65 72 79 20 69 73 20 61 20 63 6f 6d 70 6f 75 6e  ery is a compoun
5fd0: 64 20 77 68 65 72 65 20 74 68 65 0a 23 20 6c 61  d where the.# la
5fe0: 73 74 20 53 45 4c 45 43 54 20 69 73 20 6e 6f 6e  st SELECT is non
5ff0: 2d 61 67 67 72 65 67 61 74 65 20 62 75 74 20 73  -aggregate but s
6000: 6f 6d 65 20 6f 74 68 65 72 20 53 45 4c 45 43 54  ome other SELECT
6010: 20 69 73 20 61 6e 20 61 67 67 72 65 67 61 74 65   is an aggregate
6020: 2c 20 74 68 65 0a 23 20 74 65 73 74 20 69 73 20  , the.# test is 
6030: 69 6e 63 6f 6d 70 6c 65 74 65 20 61 6e 64 20 74  incomplete and t
6040: 68 65 20 6f 70 74 69 6d 69 7a 61 74 69 6f 6e 20  he optimization 
6050: 69 73 20 6e 6f 74 20 70 72 6f 70 65 72 6c 79 20  is not properly 
6060: 64 69 73 61 62 6c 65 64 2e 0a 23 20 0a 23 20 54  disabled..# .# T
6070: 68 65 20 66 6f 6c 6c 6f 77 69 6e 67 20 74 65 73  he following tes
6080: 74 20 63 61 73 65 73 20 76 65 72 69 66 79 20 74  t cases verify t
6090: 68 61 74 20 74 68 65 20 66 69 78 20 77 6f 72 6b  hat the fix work
60a0: 73 2e 0a 23 0a 64 6f 5f 65 78 65 63 73 71 6c 5f  s..#.do_execsql_
60b0: 74 65 73 74 20 73 65 6c 65 63 74 34 2d 31 37 2e  test select4-17.
60c0: 31 20 7b 0a 20 20 44 52 4f 50 20 54 41 42 4c 45  1 {.  DROP TABLE
60d0: 20 49 46 20 45 58 49 53 54 53 20 74 31 3b 0a 20   IF EXISTS t1;. 
60e0: 20 43 52 45 41 54 45 20 54 41 42 4c 45 20 74 31   CREATE TABLE t1
60f0: 28 61 20 69 6e 74 2c 20 62 20 69 6e 74 29 3b 0a  (a int, b int);.
6100: 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74 31    INSERT INTO t1
6110: 20 56 41 4c 55 45 53 28 31 2c 32 29 2c 28 31 2c   VALUES(1,2),(1,
6120: 31 38 29 2c 28 32 2c 31 39 29 3b 0a 20 20 53 45  18),(2,19);.  SE
6130: 4c 45 43 54 20 78 2c 20 79 20 46 52 4f 4d 20 28  LECT x, y FROM (
6140: 0a 20 20 20 20 53 45 4c 45 43 54 20 39 38 20 41  .    SELECT 98 A
6150: 53 20 78 2c 20 39 39 20 41 53 20 79 0a 20 20 20  S x, 99 AS y.   
6160: 20 55 4e 49 4f 4e 0a 20 20 20 20 53 45 4c 45 43   UNION.    SELEC
6170: 54 20 61 20 41 53 20 78 2c 20 73 75 6d 28 62 29  T a AS x, sum(b)
6180: 20 41 53 20 79 20 46 52 4f 4d 20 74 31 20 47 52   AS y FROM t1 GR
6190: 4f 55 50 20 42 59 20 61 0a 20 20 29 20 41 53 20  OUP BY a.  ) AS 
61a0: 77 20 57 48 45 52 45 20 79 3e 3d 32 30 0a 20 20  w WHERE y>=20.  
61b0: 4f 52 44 45 52 20 42 59 20 2b 78 3b 0a 7d 20 7b  ORDER BY +x;.} {
61c0: 31 20 32 30 20 39 38 20 39 39 7d 0a 64 6f 5f 65  1 20 98 99}.do_e
61d0: 78 65 63 73 71 6c 5f 74 65 73 74 20 73 65 6c 65  xecsql_test sele
61e0: 63 74 34 2d 31 37 2e 32 20 7b 0a 20 20 53 45 4c  ct4-17.2 {.  SEL
61f0: 45 43 54 20 78 2c 20 79 20 46 52 4f 4d 20 28 0a  ECT x, y FROM (.
6200: 20 20 20 20 53 45 4c 45 43 54 20 61 20 41 53 20      SELECT a AS 
6210: 78 2c 20 73 75 6d 28 62 29 20 41 53 20 79 20 46  x, sum(b) AS y F
6220: 52 4f 4d 20 74 31 20 47 52 4f 55 50 20 42 59 20  ROM t1 GROUP BY 
6230: 61 0a 20 20 20 20 55 4e 49 4f 4e 0a 20 20 20 20  a.    UNION.    
6240: 53 45 4c 45 43 54 20 39 38 20 41 53 20 78 2c 20  SELECT 98 AS x, 
6250: 39 39 20 41 53 20 79 0a 20 20 29 20 41 53 20 77  99 AS y.  ) AS w
6260: 20 57 48 45 52 45 20 79 3e 3d 32 30 0a 20 20 4f   WHERE y>=20.  O
6270: 52 44 45 52 20 42 59 20 2b 78 3b 0a 7d 20 7b 31  RDER BY +x;.} {1
6280: 20 32 30 20 39 38 20 39 39 7d 0a 64 6f 5f 63 61   20 98 99}.do_ca
6290: 74 63 68 73 71 6c 5f 74 65 73 74 20 73 65 6c 65  tchsql_test sele
62a0: 63 74 34 2d 31 37 2e 33 20 7b 0a 20 20 53 45 4c  ct4-17.3 {.  SEL
62b0: 45 43 54 20 78 2c 20 79 20 46 52 4f 4d 20 28 0a  ECT x, y FROM (.
62c0: 20 20 20 20 53 45 4c 45 43 54 20 61 20 41 53 20      SELECT a AS 
62d0: 78 2c 20 73 75 6d 28 62 29 20 41 53 20 79 20 46  x, sum(b) AS y F
62e0: 52 4f 4d 20 74 31 20 47 52 4f 55 50 20 42 59 20  ROM t1 GROUP BY 
62f0: 61 20 4c 49 4d 49 54 20 33 0a 20 20 20 20 55 4e  a LIMIT 3.    UN
6300: 49 4f 4e 0a 20 20 20 20 53 45 4c 45 43 54 20 39  ION.    SELECT 9
6310: 38 20 41 53 20 78 2c 20 39 39 20 41 53 20 79 0a  8 AS x, 99 AS y.
6320: 20 20 29 20 41 53 20 77 20 57 48 45 52 45 20 79    ) AS w WHERE y
6330: 3e 3d 32 30 0a 20 20 4f 52 44 45 52 20 42 59 20  >=20.  ORDER BY 
6340: 2b 78 3b 0a 7d 20 7b 31 20 7b 4c 49 4d 49 54 20  +x;.} {1 {LIMIT 
6350: 63 6c 61 75 73 65 20 73 68 6f 75 6c 64 20 63 6f  clause should co
6360: 6d 65 20 61 66 74 65 72 20 55 4e 49 4f 4e 20 6e  me after UNION n
6370: 6f 74 20 62 65 66 6f 72 65 7d 7d 0a 0a 0a 0a 66  ot before}}....f
6380: 69 6e 69 73 68 5f 74 65 73 74 0a                 inish_test.