/ Hex Artifact Content
Login

Artifact c00d300d90f0ae3fa4e4f4336c71b2345bfa819c:


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 53 45 4c 45 43  is testing SELEC
01d0: 54 20 73 74 61 74 65 6d 65 6e 74 73 20 74 68 61  T statements tha
01e0: 74 20 63 6f 6e 74 61 69 6e 0a 23 20 73 75 62 71  t contain.# subq
01f0: 75 65 72 69 65 73 20 69 6e 20 74 68 65 69 72 20  ueries in their 
0200: 46 52 4f 4d 20 63 6c 61 75 73 65 2e 0a 23 0a 23  FROM clause..#.#
0210: 20 24 49 64 3a 20 73 65 6c 65 63 74 36 2e 74 65   $Id: select6.te
0220: 73 74 2c 76 20 31 2e 31 35 20 32 30 30 34 2f 31  st,v 1.15 2004/1
0230: 31 2f 32 32 20 31 35 3a 30 35 3a 35 39 20 64 61  1/22 15:05:59 da
0240: 6e 69 65 6c 6b 31 39 37 37 20 45 78 70 20 24 0a  nielk1977 Exp $.
0250: 0a 73 65 74 20 74 65 73 74 64 69 72 20 5b 66 69  .set testdir [fi
0260: 6c 65 20 64 69 72 6e 61 6d 65 20 24 61 72 67 76  le dirname $argv
0270: 30 5d 0a 73 6f 75 72 63 65 20 24 74 65 73 74 64  0].source $testd
0280: 69 72 2f 74 65 73 74 65 72 2e 74 63 6c 0a 0a 64  ir/tester.tcl..d
0290: 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 36 2d 31  o_test select6-1
02a0: 2e 30 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b  .0 {.  execsql {
02b0: 0a 20 20 20 20 42 45 47 49 4e 3b 0a 20 20 20 20  .    BEGIN;.    
02c0: 43 52 45 41 54 45 20 54 41 42 4c 45 20 74 31 28  CREATE TABLE t1(
02d0: 78 2c 20 79 29 3b 0a 20 20 20 20 49 4e 53 45 52  x, y);.    INSER
02e0: 54 20 49 4e 54 4f 20 74 31 20 56 41 4c 55 45 53  T INTO t1 VALUES
02f0: 28 31 2c 31 29 3b 0a 20 20 20 20 49 4e 53 45 52  (1,1);.    INSER
0300: 54 20 49 4e 54 4f 20 74 31 20 56 41 4c 55 45 53  T INTO t1 VALUES
0310: 28 32 2c 32 29 3b 0a 20 20 20 20 49 4e 53 45 52  (2,2);.    INSER
0320: 54 20 49 4e 54 4f 20 74 31 20 56 41 4c 55 45 53  T INTO t1 VALUES
0330: 28 33 2c 32 29 3b 0a 20 20 20 20 49 4e 53 45 52  (3,2);.    INSER
0340: 54 20 49 4e 54 4f 20 74 31 20 56 41 4c 55 45 53  T INTO t1 VALUES
0350: 28 34 2c 33 29 3b 0a 20 20 20 20 49 4e 53 45 52  (4,3);.    INSER
0360: 54 20 49 4e 54 4f 20 74 31 20 56 41 4c 55 45 53  T INTO t1 VALUES
0370: 28 35 2c 33 29 3b 0a 20 20 20 20 49 4e 53 45 52  (5,3);.    INSER
0380: 54 20 49 4e 54 4f 20 74 31 20 56 41 4c 55 45 53  T INTO t1 VALUES
0390: 28 36 2c 33 29 3b 0a 20 20 20 20 49 4e 53 45 52  (6,3);.    INSER
03a0: 54 20 49 4e 54 4f 20 74 31 20 56 41 4c 55 45 53  T INTO t1 VALUES
03b0: 28 37 2c 33 29 3b 0a 20 20 20 20 49 4e 53 45 52  (7,3);.    INSER
03c0: 54 20 49 4e 54 4f 20 74 31 20 56 41 4c 55 45 53  T INTO t1 VALUES
03d0: 28 38 2c 34 29 3b 0a 20 20 20 20 49 4e 53 45 52  (8,4);.    INSER
03e0: 54 20 49 4e 54 4f 20 74 31 20 56 41 4c 55 45 53  T INTO t1 VALUES
03f0: 28 39 2c 34 29 3b 0a 20 20 20 20 49 4e 53 45 52  (9,4);.    INSER
0400: 54 20 49 4e 54 4f 20 74 31 20 56 41 4c 55 45 53  T INTO t1 VALUES
0410: 28 31 30 2c 34 29 3b 0a 20 20 20 20 49 4e 53 45  (10,4);.    INSE
0420: 52 54 20 49 4e 54 4f 20 74 31 20 56 41 4c 55 45  RT INTO t1 VALUE
0430: 53 28 31 31 2c 34 29 3b 0a 20 20 20 20 49 4e 53  S(11,4);.    INS
0440: 45 52 54 20 49 4e 54 4f 20 74 31 20 56 41 4c 55  ERT INTO t1 VALU
0450: 45 53 28 31 32 2c 34 29 3b 0a 20 20 20 20 49 4e  ES(12,4);.    IN
0460: 53 45 52 54 20 49 4e 54 4f 20 74 31 20 56 41 4c  SERT INTO t1 VAL
0470: 55 45 53 28 31 33 2c 34 29 3b 0a 20 20 20 20 49  UES(13,4);.    I
0480: 4e 53 45 52 54 20 49 4e 54 4f 20 74 31 20 56 41  NSERT INTO t1 VA
0490: 4c 55 45 53 28 31 34 2c 34 29 3b 0a 20 20 20 20  LUES(14,4);.    
04a0: 49 4e 53 45 52 54 20 49 4e 54 4f 20 74 31 20 56  INSERT INTO t1 V
04b0: 41 4c 55 45 53 28 31 35 2c 34 29 3b 0a 20 20 20  ALUES(15,4);.   
04c0: 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74 31 20   INSERT INTO t1 
04d0: 56 41 4c 55 45 53 28 31 36 2c 35 29 3b 0a 20 20  VALUES(16,5);.  
04e0: 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74 31    INSERT INTO t1
04f0: 20 56 41 4c 55 45 53 28 31 37 2c 35 29 3b 0a 20   VALUES(17,5);. 
0500: 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20 74     INSERT INTO t
0510: 31 20 56 41 4c 55 45 53 28 31 38 2c 35 29 3b 0a  1 VALUES(18,5);.
0520: 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20      INSERT INTO 
0530: 74 31 20 56 41 4c 55 45 53 28 31 39 2c 35 29 3b  t1 VALUES(19,5);
0540: 0a 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f  .    INSERT INTO
0550: 20 74 31 20 56 41 4c 55 45 53 28 32 30 2c 35 29   t1 VALUES(20,5)
0560: 3b 0a 20 20 20 20 43 4f 4d 4d 49 54 3b 0a 20 20  ;.    COMMIT;.  
0570: 20 20 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43    SELECT DISTINC
0580: 54 20 79 20 46 52 4f 4d 20 74 31 20 4f 52 44 45  T y FROM t1 ORDE
0590: 52 20 42 59 20 79 3b 0a 20 20 7d 0a 7d 20 7b 31  R BY y;.  }.} {1
05a0: 20 32 20 33 20 34 20 35 7d 0a 0a 64 6f 5f 74 65   2 3 4 5}..do_te
05b0: 73 74 20 73 65 6c 65 63 74 36 2d 31 2e 31 20 7b  st select6-1.1 {
05c0: 0a 20 20 65 78 65 63 73 71 6c 32 20 7b 53 45 4c  .  execsql2 {SEL
05d0: 45 43 54 20 2a 20 46 52 4f 4d 20 28 53 45 4c 45  ECT * FROM (SELE
05e0: 43 54 20 78 2c 20 79 20 46 52 4f 4d 20 74 31 20  CT x, y FROM t1 
05f0: 57 48 45 52 45 20 78 3c 32 29 7d 0a 7d 20 7b 78  WHERE x<2)}.} {x
0600: 20 31 20 79 20 31 7d 0a 64 6f 5f 74 65 73 74 20   1 y 1}.do_test 
0610: 73 65 6c 65 63 74 36 2d 31 2e 32 20 7b 0a 20 20  select6-1.2 {.  
0620: 65 78 65 63 73 71 6c 20 7b 53 45 4c 45 43 54 20  execsql {SELECT 
0630: 63 6f 75 6e 74 28 2a 29 20 46 52 4f 4d 20 28 53  count(*) FROM (S
0640: 45 4c 45 43 54 20 79 20 46 52 4f 4d 20 74 31 29  ELECT y FROM t1)
0650: 7d 0a 7d 20 7b 32 30 7d 0a 64 6f 5f 74 65 73 74  }.} {20}.do_test
0660: 20 73 65 6c 65 63 74 36 2d 31 2e 33 20 7b 0a 20   select6-1.3 {. 
0670: 20 65 78 65 63 73 71 6c 20 7b 53 45 4c 45 43 54   execsql {SELECT
0680: 20 63 6f 75 6e 74 28 2a 29 20 46 52 4f 4d 20 28   count(*) FROM (
0690: 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54 20  SELECT DISTINCT 
06a0: 79 20 46 52 4f 4d 20 74 31 29 7d 0a 7d 20 7b 35  y FROM t1)}.} {5
06b0: 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74  }.do_test select
06c0: 36 2d 31 2e 34 20 7b 0a 20 20 65 78 65 63 73 71  6-1.4 {.  execsq
06d0: 6c 20 7b 53 45 4c 45 43 54 20 63 6f 75 6e 74 28  l {SELECT count(
06e0: 2a 29 20 46 52 4f 4d 20 28 53 45 4c 45 43 54 20  *) FROM (SELECT 
06f0: 44 49 53 54 49 4e 43 54 20 2a 20 46 52 4f 4d 20  DISTINCT * FROM 
0700: 28 53 45 4c 45 43 54 20 79 20 46 52 4f 4d 20 74  (SELECT y FROM t
0710: 31 29 29 7d 0a 7d 20 7b 35 7d 0a 64 6f 5f 74 65  1))}.} {5}.do_te
0720: 73 74 20 73 65 6c 65 63 74 36 2d 31 2e 35 20 7b  st select6-1.5 {
0730: 0a 20 20 65 78 65 63 73 71 6c 20 7b 53 45 4c 45  .  execsql {SELE
0740: 43 54 20 63 6f 75 6e 74 28 2a 29 20 46 52 4f 4d  CT count(*) FROM
0750: 20 28 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20   (SELECT * FROM 
0760: 28 53 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54  (SELECT DISTINCT
0770: 20 79 20 46 52 4f 4d 20 74 31 29 29 7d 0a 7d 20   y FROM t1))}.} 
0780: 7b 35 7d 0a 0a 64 6f 5f 74 65 73 74 20 73 65 6c  {5}..do_test sel
0790: 65 63 74 36 2d 31 2e 36 20 7b 0a 20 20 65 78 65  ect6-1.6 {.  exe
07a0: 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43  csql {.    SELEC
07b0: 54 20 2a 20 0a 20 20 20 20 46 52 4f 4d 20 28 53  T * .    FROM (S
07c0: 45 4c 45 43 54 20 63 6f 75 6e 74 28 2a 29 2c 79  ELECT count(*),y
07d0: 20 46 52 4f 4d 20 74 31 20 47 52 4f 55 50 20 42   FROM t1 GROUP B
07e0: 59 20 79 29 20 41 53 20 61 2c 0a 20 20 20 20 20  Y y) AS a,.     
07f0: 20 20 20 20 28 53 45 4c 45 43 54 20 6d 61 78 28      (SELECT max(
0800: 78 29 2c 79 20 46 52 4f 4d 20 74 31 20 47 52 4f  x),y FROM t1 GRO
0810: 55 50 20 42 59 20 79 29 20 61 73 20 62 0a 20 20  UP BY y) as b.  
0820: 20 20 57 48 45 52 45 20 61 2e 79 3d 62 2e 79 20    WHERE a.y=b.y 
0830: 4f 52 44 45 52 20 42 59 20 61 2e 79 0a 20 20 7d  ORDER BY a.y.  }
0840: 0a 7d 20 7b 31 20 31 20 31 20 31 20 32 20 32 20  .} {1 1 1 1 2 2 
0850: 33 20 32 20 34 20 33 20 37 20 33 20 38 20 34 20  3 2 4 3 7 3 8 4 
0860: 31 35 20 34 20 35 20 35 20 32 30 20 35 7d 0a 64  15 4 5 5 20 5}.d
0870: 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 36 2d 31  o_test select6-1
0880: 2e 37 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b  .7 {.  execsql {
0890: 0a 20 20 20 20 53 45 4c 45 43 54 20 61 2e 79 2c  .    SELECT a.y,
08a0: 20 61 2e 5b 63 6f 75 6e 74 28 2a 29 5d 2c 20 5b   a.[count(*)], [
08b0: 6d 61 78 28 78 29 5d 2c 20 5b 63 6f 75 6e 74 28  max(x)], [count(
08c0: 2a 29 5d 0a 20 20 20 20 46 52 4f 4d 20 28 53 45  *)].    FROM (SE
08d0: 4c 45 43 54 20 63 6f 75 6e 74 28 2a 29 2c 79 20  LECT count(*),y 
08e0: 46 52 4f 4d 20 74 31 20 47 52 4f 55 50 20 42 59  FROM t1 GROUP BY
08f0: 20 79 29 20 41 53 20 61 2c 0a 20 20 20 20 20 20   y) AS a,.      
0900: 20 20 20 28 53 45 4c 45 43 54 20 6d 61 78 28 78     (SELECT max(x
0910: 29 2c 79 20 46 52 4f 4d 20 74 31 20 47 52 4f 55  ),y FROM t1 GROU
0920: 50 20 42 59 20 79 29 20 61 73 20 62 0a 20 20 20  P BY y) as b.   
0930: 20 57 48 45 52 45 20 61 2e 79 3d 62 2e 79 20 4f   WHERE a.y=b.y O
0940: 52 44 45 52 20 42 59 20 61 2e 79 0a 20 20 7d 0a  RDER BY a.y.  }.
0950: 7d 20 7b 31 20 31 20 31 20 31 20 32 20 32 20 33  } {1 1 1 1 2 2 3
0960: 20 32 20 33 20 34 20 37 20 34 20 34 20 38 20 31   2 3 4 7 4 4 8 1
0970: 35 20 38 20 35 20 35 20 32 30 20 35 7d 0a 64 6f  5 8 5 5 20 5}.do
0980: 5f 74 65 73 74 20 73 65 6c 65 63 74 36 2d 31 2e  _test select6-1.
0990: 38 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a  8 {.  execsql {.
09a0: 20 20 20 20 53 45 4c 45 43 54 20 71 2c 20 70 2c      SELECT q, p,
09b0: 20 72 0a 20 20 20 20 46 52 4f 4d 20 28 53 45 4c   r.    FROM (SEL
09c0: 45 43 54 20 63 6f 75 6e 74 28 2a 29 20 61 73 20  ECT count(*) as 
09d0: 70 20 2c 20 79 20 61 73 20 71 20 46 52 4f 4d 20  p , y as q FROM 
09e0: 74 31 20 47 52 4f 55 50 20 42 59 20 79 29 20 41  t1 GROUP BY y) A
09f0: 53 20 61 2c 0a 20 20 20 20 20 20 20 20 20 28 53  S a,.         (S
0a00: 45 4c 45 43 54 20 6d 61 78 28 78 29 20 61 73 20  ELECT max(x) as 
0a10: 72 2c 20 79 20 61 73 20 73 20 46 52 4f 4d 20 74  r, y as s FROM t
0a20: 31 20 47 52 4f 55 50 20 42 59 20 79 29 20 61 73  1 GROUP BY y) as
0a30: 20 62 0a 20 20 20 20 57 48 45 52 45 20 71 3d 73   b.    WHERE q=s
0a40: 20 4f 52 44 45 52 20 42 59 20 73 0a 20 20 7d 0a   ORDER BY s.  }.
0a50: 7d 20 7b 31 20 31 20 31 20 32 20 32 20 33 20 33  } {1 1 1 2 2 3 3
0a60: 20 34 20 37 20 34 20 38 20 31 35 20 35 20 35 20   4 7 4 8 15 5 5 
0a70: 32 30 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65  20}.do_test sele
0a80: 63 74 36 2d 31 2e 39 20 7b 0a 20 20 65 78 65 63  ct6-1.9 {.  exec
0a90: 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54  sql {.    SELECT
0aa0: 20 71 2c 20 70 2c 20 72 2c 20 62 2e 5b 6d 69 6e   q, p, r, b.[min
0ab0: 28 78 29 2b 79 5d 0a 20 20 20 20 46 52 4f 4d 20  (x)+y].    FROM 
0ac0: 28 53 45 4c 45 43 54 20 63 6f 75 6e 74 28 2a 29  (SELECT count(*)
0ad0: 20 61 73 20 70 20 2c 20 79 20 61 73 20 71 20 46   as p , y as q F
0ae0: 52 4f 4d 20 74 31 20 47 52 4f 55 50 20 42 59 20  ROM t1 GROUP BY 
0af0: 79 29 20 41 53 20 61 2c 0a 20 20 20 20 20 20 20  y) AS a,.       
0b00: 20 20 28 53 45 4c 45 43 54 20 6d 61 78 28 78 29    (SELECT max(x)
0b10: 20 61 73 20 72 2c 20 79 20 61 73 20 73 2c 20 6d   as r, y as s, m
0b20: 69 6e 28 78 29 2b 79 20 46 52 4f 4d 20 74 31 20  in(x)+y FROM t1 
0b30: 47 52 4f 55 50 20 42 59 20 79 29 20 61 73 20 62  GROUP BY y) as b
0b40: 0a 20 20 20 20 57 48 45 52 45 20 71 3d 73 20 4f  .    WHERE q=s O
0b50: 52 44 45 52 20 42 59 20 73 0a 20 20 7d 0a 7d 20  RDER BY s.  }.} 
0b60: 7b 31 20 31 20 31 20 32 20 32 20 32 20 33 20 34  {1 1 1 2 2 2 3 4
0b70: 20 33 20 34 20 37 20 37 20 34 20 38 20 31 35 20   3 4 7 7 4 8 15 
0b80: 31 32 20 35 20 35 20 32 30 20 32 31 7d 0a 0a 64  12 5 5 20 21}..d
0b90: 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 36 2d 32  o_test select6-2
0ba0: 2e 30 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b  .0 {.  execsql {
0bb0: 0a 20 20 20 20 43 52 45 41 54 45 20 54 41 42 4c  .    CREATE TABL
0bc0: 45 20 74 32 28 61 20 49 4e 54 45 47 45 52 20 50  E t2(a INTEGER P
0bd0: 52 49 4d 41 52 59 20 4b 45 59 2c 20 62 29 3b 0a  RIMARY KEY, b);.
0be0: 20 20 20 20 49 4e 53 45 52 54 20 49 4e 54 4f 20      INSERT INTO 
0bf0: 74 32 20 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d  t2 SELECT * FROM
0c00: 20 74 31 3b 0a 20 20 20 20 53 45 4c 45 43 54 20   t1;.    SELECT 
0c10: 44 49 53 54 49 4e 43 54 20 62 20 46 52 4f 4d 20  DISTINCT b FROM 
0c20: 74 32 20 4f 52 44 45 52 20 42 59 20 62 3b 0a 20  t2 ORDER BY b;. 
0c30: 20 7d 0a 7d 20 7b 31 20 32 20 33 20 34 20 35 7d   }.} {1 2 3 4 5}
0c40: 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 36  .do_test select6
0c50: 2d 32 2e 31 20 7b 0a 20 20 65 78 65 63 73 71 6c  -2.1 {.  execsql
0c60: 32 20 7b 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d  2 {SELECT * FROM
0c70: 20 28 53 45 4c 45 43 54 20 61 2c 20 62 20 46 52   (SELECT a, b FR
0c80: 4f 4d 20 74 32 20 57 48 45 52 45 20 61 3c 32 29  OM t2 WHERE a<2)
0c90: 7d 0a 7d 20 7b 61 20 31 20 62 20 31 7d 0a 64 6f  }.} {a 1 b 1}.do
0ca0: 5f 74 65 73 74 20 73 65 6c 65 63 74 36 2d 32 2e  _test select6-2.
0cb0: 32 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 53  2 {.  execsql {S
0cc0: 45 4c 45 43 54 20 63 6f 75 6e 74 28 2a 29 20 46  ELECT count(*) F
0cd0: 52 4f 4d 20 28 53 45 4c 45 43 54 20 62 20 46 52  ROM (SELECT b FR
0ce0: 4f 4d 20 74 32 29 7d 0a 7d 20 7b 32 30 7d 0a 64  OM t2)}.} {20}.d
0cf0: 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 36 2d 32  o_test select6-2
0d00: 2e 33 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b  .3 {.  execsql {
0d10: 53 45 4c 45 43 54 20 63 6f 75 6e 74 28 2a 29 20  SELECT count(*) 
0d20: 46 52 4f 4d 20 28 53 45 4c 45 43 54 20 44 49 53  FROM (SELECT DIS
0d30: 54 49 4e 43 54 20 62 20 46 52 4f 4d 20 74 32 29  TINCT b FROM t2)
0d40: 7d 0a 7d 20 7b 35 7d 0a 64 6f 5f 74 65 73 74 20  }.} {5}.do_test 
0d50: 73 65 6c 65 63 74 36 2d 32 2e 34 20 7b 0a 20 20  select6-2.4 {.  
0d60: 65 78 65 63 73 71 6c 20 7b 53 45 4c 45 43 54 20  execsql {SELECT 
0d70: 63 6f 75 6e 74 28 2a 29 20 46 52 4f 4d 20 28 53  count(*) FROM (S
0d80: 45 4c 45 43 54 20 44 49 53 54 49 4e 43 54 20 2a  ELECT DISTINCT *
0d90: 20 46 52 4f 4d 20 28 53 45 4c 45 43 54 20 62 20   FROM (SELECT b 
0da0: 46 52 4f 4d 20 74 32 29 29 7d 0a 7d 20 7b 35 7d  FROM t2))}.} {5}
0db0: 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 36  .do_test select6
0dc0: 2d 32 2e 35 20 7b 0a 20 20 65 78 65 63 73 71 6c  -2.5 {.  execsql
0dd0: 20 7b 53 45 4c 45 43 54 20 63 6f 75 6e 74 28 2a   {SELECT count(*
0de0: 29 20 46 52 4f 4d 20 28 53 45 4c 45 43 54 20 2a  ) FROM (SELECT *
0df0: 20 46 52 4f 4d 20 28 53 45 4c 45 43 54 20 44 49   FROM (SELECT DI
0e00: 53 54 49 4e 43 54 20 62 20 46 52 4f 4d 20 74 32  STINCT b FROM t2
0e10: 29 29 7d 0a 7d 20 7b 35 7d 0a 0a 64 6f 5f 74 65  ))}.} {5}..do_te
0e20: 73 74 20 73 65 6c 65 63 74 36 2d 32 2e 36 20 7b  st select6-2.6 {
0e30: 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20  .  execsql {.   
0e40: 20 53 45 4c 45 43 54 20 2a 20 0a 20 20 20 20 46   SELECT * .    F
0e50: 52 4f 4d 20 28 53 45 4c 45 43 54 20 63 6f 75 6e  ROM (SELECT coun
0e60: 74 28 2a 29 2c 62 20 46 52 4f 4d 20 74 32 20 47  t(*),b FROM t2 G
0e70: 52 4f 55 50 20 42 59 20 62 29 20 41 53 20 61 2c  ROUP BY b) AS a,
0e80: 0a 20 20 20 20 20 20 20 20 20 28 53 45 4c 45 43  .         (SELEC
0e90: 54 20 6d 61 78 28 61 29 2c 62 20 46 52 4f 4d 20  T max(a),b FROM 
0ea0: 74 32 20 47 52 4f 55 50 20 42 59 20 62 29 20 61  t2 GROUP BY b) a
0eb0: 73 20 62 0a 20 20 20 20 57 48 45 52 45 20 61 2e  s b.    WHERE a.
0ec0: 62 3d 62 2e 62 20 4f 52 44 45 52 20 42 59 20 61  b=b.b ORDER BY a
0ed0: 2e 62 0a 20 20 7d 0a 7d 20 7b 31 20 31 20 31 20  .b.  }.} {1 1 1 
0ee0: 31 20 32 20 32 20 33 20 32 20 34 20 33 20 37 20  1 2 2 3 2 4 3 7 
0ef0: 33 20 38 20 34 20 31 35 20 34 20 35 20 35 20 32  3 8 4 15 4 5 5 2
0f00: 30 20 35 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c  0 5}.do_test sel
0f10: 65 63 74 36 2d 32 2e 37 20 7b 0a 20 20 65 78 65  ect6-2.7 {.  exe
0f20: 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43  csql {.    SELEC
0f30: 54 20 61 2e 62 2c 20 61 2e 5b 63 6f 75 6e 74 28  T a.b, a.[count(
0f40: 2a 29 5d 2c 20 5b 6d 61 78 28 61 29 5d 2c 20 5b  *)], [max(a)], [
0f50: 63 6f 75 6e 74 28 2a 29 5d 0a 20 20 20 20 46 52  count(*)].    FR
0f60: 4f 4d 20 28 53 45 4c 45 43 54 20 63 6f 75 6e 74  OM (SELECT count
0f70: 28 2a 29 2c 62 20 46 52 4f 4d 20 74 32 20 47 52  (*),b FROM t2 GR
0f80: 4f 55 50 20 42 59 20 62 29 20 41 53 20 61 2c 0a  OUP BY b) AS a,.
0f90: 20 20 20 20 20 20 20 20 20 28 53 45 4c 45 43 54           (SELECT
0fa0: 20 6d 61 78 28 61 29 2c 62 20 46 52 4f 4d 20 74   max(a),b FROM t
0fb0: 32 20 47 52 4f 55 50 20 42 59 20 62 29 20 61 73  2 GROUP BY b) as
0fc0: 20 62 0a 20 20 20 20 57 48 45 52 45 20 61 2e 62   b.    WHERE a.b
0fd0: 3d 62 2e 62 20 4f 52 44 45 52 20 42 59 20 61 2e  =b.b ORDER BY a.
0fe0: 62 0a 20 20 7d 0a 7d 20 7b 31 20 31 20 31 20 31  b.  }.} {1 1 1 1
0ff0: 20 32 20 32 20 33 20 32 20 33 20 34 20 37 20 34   2 2 3 2 3 4 7 4
1000: 20 34 20 38 20 31 35 20 38 20 35 20 35 20 32 30   4 8 15 8 5 5 20
1010: 20 35 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65   5}.do_test sele
1020: 63 74 36 2d 32 2e 38 20 7b 0a 20 20 65 78 65 63  ct6-2.8 {.  exec
1030: 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54  sql {.    SELECT
1040: 20 71 2c 20 70 2c 20 72 0a 20 20 20 20 46 52 4f   q, p, r.    FRO
1050: 4d 20 28 53 45 4c 45 43 54 20 63 6f 75 6e 74 28  M (SELECT count(
1060: 2a 29 20 61 73 20 70 20 2c 20 62 20 61 73 20 71  *) as p , b as q
1070: 20 46 52 4f 4d 20 74 32 20 47 52 4f 55 50 20 42   FROM t2 GROUP B
1080: 59 20 62 29 20 41 53 20 61 2c 0a 20 20 20 20 20  Y b) AS a,.     
1090: 20 20 20 20 28 53 45 4c 45 43 54 20 6d 61 78 28      (SELECT max(
10a0: 61 29 20 61 73 20 72 2c 20 62 20 61 73 20 73 20  a) as r, b as s 
10b0: 46 52 4f 4d 20 74 32 20 47 52 4f 55 50 20 42 59  FROM t2 GROUP BY
10c0: 20 62 29 20 61 73 20 62 0a 20 20 20 20 57 48 45   b) as b.    WHE
10d0: 52 45 20 71 3d 73 20 4f 52 44 45 52 20 42 59 20  RE q=s ORDER BY 
10e0: 73 0a 20 20 7d 0a 7d 20 7b 31 20 31 20 31 20 32  s.  }.} {1 1 1 2
10f0: 20 32 20 33 20 33 20 34 20 37 20 34 20 38 20 31   2 3 3 4 7 4 8 1
1100: 35 20 35 20 35 20 32 30 7d 0a 64 6f 5f 74 65 73  5 5 5 20}.do_tes
1110: 74 20 73 65 6c 65 63 74 36 2d 32 2e 39 20 7b 0a  t select6-2.9 {.
1120: 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20    execsql {.    
1130: 53 45 4c 45 43 54 20 61 2e 71 2c 20 61 2e 70 2c  SELECT a.q, a.p,
1140: 20 62 2e 72 0a 20 20 20 20 46 52 4f 4d 20 28 53   b.r.    FROM (S
1150: 45 4c 45 43 54 20 63 6f 75 6e 74 28 2a 29 20 61  ELECT count(*) a
1160: 73 20 70 20 2c 20 62 20 61 73 20 71 20 46 52 4f  s p , b as q FRO
1170: 4d 20 74 32 20 47 52 4f 55 50 20 42 59 20 71 29  M t2 GROUP BY q)
1180: 20 41 53 20 61 2c 0a 20 20 20 20 20 20 20 20 20   AS a,.         
1190: 28 53 45 4c 45 43 54 20 6d 61 78 28 61 29 20 61  (SELECT max(a) a
11a0: 73 20 72 2c 20 62 20 61 73 20 73 20 46 52 4f 4d  s r, b as s FROM
11b0: 20 74 32 20 47 52 4f 55 50 20 42 59 20 73 29 20   t2 GROUP BY s) 
11c0: 61 73 20 62 0a 20 20 20 20 57 48 45 52 45 20 61  as b.    WHERE a
11d0: 2e 71 3d 62 2e 73 20 4f 52 44 45 52 20 42 59 20  .q=b.s ORDER BY 
11e0: 61 2e 71 0a 20 20 7d 0a 7d 20 7b 31 20 31 20 31  a.q.  }.} {1 1 1
11f0: 20 32 20 32 20 33 20 33 20 34 20 37 20 34 20 38   2 2 3 3 4 7 4 8
1200: 20 31 35 20 35 20 35 20 32 30 7d 0a 0a 64 6f 5f   15 5 5 20}..do_
1210: 74 65 73 74 20 73 71 6c 69 74 65 36 2d 33 2e 31  test sqlite6-3.1
1220: 20 7b 0a 20 20 65 78 65 63 73 71 6c 32 20 7b 0a   {.  execsql2 {.
1230: 20 20 20 20 53 45 4c 45 43 54 20 2a 20 46 52 4f      SELECT * FRO
1240: 4d 20 28 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d  M (SELECT * FROM
1250: 20 28 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20   (SELECT * FROM 
1260: 74 31 20 57 48 45 52 45 20 78 3d 33 29 29 3b 0a  t1 WHERE x=3));.
1270: 20 20 7d 0a 7d 20 7b 78 20 33 20 79 20 32 7d 0a    }.} {x 3 y 2}.
1280: 64 6f 5f 74 65 73 74 20 73 71 6c 69 74 65 36 2d  do_test sqlite6-
1290: 33 2e 32 20 7b 0a 20 20 65 78 65 63 73 71 6c 20  3.2 {.  execsql 
12a0: 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 2a 20 46  {.    SELECT * F
12b0: 52 4f 4d 0a 20 20 20 20 20 20 28 53 45 4c 45 43  ROM.      (SELEC
12c0: 54 20 61 2e 71 2c 20 61 2e 70 2c 20 62 2e 72 0a  T a.q, a.p, b.r.
12d0: 20 20 20 20 20 20 20 46 52 4f 4d 20 28 53 45 4c         FROM (SEL
12e0: 45 43 54 20 63 6f 75 6e 74 28 2a 29 20 61 73 20  ECT count(*) as 
12f0: 70 20 2c 20 62 20 61 73 20 71 20 46 52 4f 4d 20  p , b as q FROM 
1300: 74 32 20 47 52 4f 55 50 20 42 59 20 71 29 20 41  t2 GROUP BY q) A
1310: 53 20 61 2c 0a 20 20 20 20 20 20 20 20 20 20 20  S a,.           
1320: 20 28 53 45 4c 45 43 54 20 6d 61 78 28 61 29 20   (SELECT max(a) 
1330: 61 73 20 72 2c 20 62 20 61 73 20 73 20 46 52 4f  as r, b as s FRO
1340: 4d 20 74 32 20 47 52 4f 55 50 20 42 59 20 73 29  M t2 GROUP BY s)
1350: 20 61 73 20 62 0a 20 20 20 20 20 20 20 57 48 45   as b.       WHE
1360: 52 45 20 61 2e 71 3d 62 2e 73 20 4f 52 44 45 52  RE a.q=b.s ORDER
1370: 20 42 59 20 61 2e 71 29 0a 20 20 20 20 4f 52 44   BY a.q).    ORD
1380: 45 52 20 42 59 20 71 0a 20 20 7d 0a 7d 20 7b 31  ER BY q.  }.} {1
1390: 20 31 20 31 20 32 20 32 20 33 20 33 20 34 20 37   1 1 2 2 3 3 4 7
13a0: 20 34 20 38 20 31 35 20 35 20 35 20 32 30 7d 0a   4 8 15 5 5 20}.
13b0: 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 36 2d  do_test select6-
13c0: 33 2e 33 20 7b 0a 20 20 65 78 65 63 73 71 6c 20  3.3 {.  execsql 
13d0: 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 61 2c 62  {.    SELECT a,b
13e0: 2c 61 2b 62 20 46 52 4f 4d 20 28 53 45 4c 45 43  ,a+b FROM (SELEC
13f0: 54 20 61 76 67 28 78 29 20 61 73 20 27 61 27 2c  T avg(x) as 'a',
1400: 20 61 76 67 28 79 29 20 61 73 20 27 62 27 20 46   avg(y) as 'b' F
1410: 52 4f 4d 20 74 31 29 0a 20 20 7d 0a 7d 20 7b 31  ROM t1).  }.} {1
1420: 30 2e 35 20 33 2e 37 20 31 34 2e 32 7d 0a 64 6f  0.5 3.7 14.2}.do
1430: 5f 74 65 73 74 20 73 65 6c 65 63 74 36 2d 33 2e  _test select6-3.
1440: 34 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a  4 {.  execsql {.
1450: 20 20 20 20 53 45 4c 45 43 54 20 61 2c 62 2c 61      SELECT a,b,a
1460: 2b 62 20 46 52 4f 4d 20 28 53 45 4c 45 43 54 20  +b FROM (SELECT 
1470: 61 76 67 28 78 29 20 61 73 20 27 61 27 2c 20 61  avg(x) as 'a', a
1480: 76 67 28 79 29 20 61 73 20 27 62 27 20 46 52 4f  vg(y) as 'b' FRO
1490: 4d 20 74 31 20 57 48 45 52 45 20 79 3d 34 29 0a  M t1 WHERE y=4).
14a0: 20 20 7d 0a 7d 20 7b 31 31 2e 35 20 34 2e 30 20    }.} {11.5 4.0 
14b0: 31 35 2e 35 7d 0a 64 6f 5f 74 65 73 74 20 73 65  15.5}.do_test se
14c0: 6c 65 63 74 36 2d 33 2e 35 20 7b 0a 20 20 65 78  lect6-3.5 {.  ex
14d0: 65 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45  ecsql {.    SELE
14e0: 43 54 20 78 2c 79 2c 78 2b 79 20 46 52 4f 4d 20  CT x,y,x+y FROM 
14f0: 28 53 45 4c 45 43 54 20 61 76 67 28 61 29 20 61  (SELECT avg(a) a
1500: 73 20 27 78 27 2c 20 61 76 67 28 62 29 20 61 73  s 'x', avg(b) as
1510: 20 27 79 27 20 46 52 4f 4d 20 74 32 20 57 48 45   'y' FROM t2 WHE
1520: 52 45 20 61 3d 34 29 0a 20 20 7d 0a 7d 20 7b 34  RE a=4).  }.} {4
1530: 2e 30 20 33 2e 30 20 37 2e 30 7d 0a 64 6f 5f 74  .0 3.0 7.0}.do_t
1540: 65 73 74 20 73 65 6c 65 63 74 36 2d 33 2e 36 20  est select6-3.6 
1550: 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20  {.  execsql {.  
1560: 20 20 53 45 4c 45 43 54 20 61 2c 62 2c 61 2b 62    SELECT a,b,a+b
1570: 20 46 52 4f 4d 20 28 53 45 4c 45 43 54 20 61 76   FROM (SELECT av
1580: 67 28 78 29 20 61 73 20 27 61 27 2c 20 61 76 67  g(x) as 'a', avg
1590: 28 79 29 20 61 73 20 27 62 27 20 46 52 4f 4d 20  (y) as 'b' FROM 
15a0: 74 31 29 0a 20 20 20 20 57 48 45 52 45 20 61 3e  t1).    WHERE a>
15b0: 31 30 0a 20 20 7d 0a 7d 20 7b 31 30 2e 35 20 33  10.  }.} {10.5 3
15c0: 2e 37 20 31 34 2e 32 7d 0a 64 6f 5f 74 65 73 74  .7 14.2}.do_test
15d0: 20 73 65 6c 65 63 74 36 2d 33 2e 37 20 7b 0a 20   select6-3.7 {. 
15e0: 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53   execsql {.    S
15f0: 45 4c 45 43 54 20 61 2c 62 2c 61 2b 62 20 46 52  ELECT a,b,a+b FR
1600: 4f 4d 20 28 53 45 4c 45 43 54 20 61 76 67 28 78  OM (SELECT avg(x
1610: 29 20 61 73 20 27 61 27 2c 20 61 76 67 28 79 29  ) as 'a', avg(y)
1620: 20 61 73 20 27 62 27 20 46 52 4f 4d 20 74 31 29   as 'b' FROM t1)
1630: 0a 20 20 20 20 57 48 45 52 45 20 61 3c 31 30 0a  .    WHERE a<10.
1640: 20 20 7d 0a 7d 20 7b 7d 0a 64 6f 5f 74 65 73 74    }.} {}.do_test
1650: 20 73 65 6c 65 63 74 36 2d 33 2e 38 20 7b 0a 20   select6-3.8 {. 
1660: 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53   execsql {.    S
1670: 45 4c 45 43 54 20 61 2c 62 2c 61 2b 62 20 46 52  ELECT a,b,a+b FR
1680: 4f 4d 20 28 53 45 4c 45 43 54 20 61 76 67 28 78  OM (SELECT avg(x
1690: 29 20 61 73 20 27 61 27 2c 20 61 76 67 28 79 29  ) as 'a', avg(y)
16a0: 20 61 73 20 27 62 27 20 46 52 4f 4d 20 74 31 20   as 'b' FROM t1 
16b0: 57 48 45 52 45 20 79 3d 34 29 0a 20 20 20 20 57  WHERE y=4).    W
16c0: 48 45 52 45 20 61 3e 31 30 0a 20 20 7d 0a 7d 20  HERE a>10.  }.} 
16d0: 7b 31 31 2e 35 20 34 2e 30 20 31 35 2e 35 7d 0a  {11.5 4.0 15.5}.
16e0: 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 36 2d  do_test select6-
16f0: 33 2e 39 20 7b 0a 20 20 65 78 65 63 73 71 6c 20  3.9 {.  execsql 
1700: 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 61 2c 62  {.    SELECT a,b
1710: 2c 61 2b 62 20 46 52 4f 4d 20 28 53 45 4c 45 43  ,a+b FROM (SELEC
1720: 54 20 61 76 67 28 78 29 20 61 73 20 27 61 27 2c  T avg(x) as 'a',
1730: 20 61 76 67 28 79 29 20 61 73 20 27 62 27 20 46   avg(y) as 'b' F
1740: 52 4f 4d 20 74 31 20 57 48 45 52 45 20 79 3d 34  ROM t1 WHERE y=4
1750: 29 0a 20 20 20 20 57 48 45 52 45 20 61 3c 31 30  ).    WHERE a<10
1760: 0a 20 20 7d 0a 7d 20 7b 7d 0a 64 6f 5f 74 65 73  .  }.} {}.do_tes
1770: 74 20 73 65 6c 65 63 74 36 2d 33 2e 31 30 20 7b  t select6-3.10 {
1780: 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20  .  execsql {.   
1790: 20 53 45 4c 45 43 54 20 61 2c 62 2c 61 2b 62 20   SELECT a,b,a+b 
17a0: 46 52 4f 4d 20 28 53 45 4c 45 43 54 20 61 76 67  FROM (SELECT avg
17b0: 28 78 29 20 61 73 20 27 61 27 2c 20 79 20 61 73  (x) as 'a', y as
17c0: 20 27 62 27 20 46 52 4f 4d 20 74 31 20 47 52 4f   'b' FROM t1 GRO
17d0: 55 50 20 42 59 20 62 29 0a 20 20 20 20 4f 52 44  UP BY b).    ORD
17e0: 45 52 20 42 59 20 61 0a 20 20 7d 0a 7d 20 7b 31  ER BY a.  }.} {1
17f0: 2e 30 20 31 20 32 2e 30 20 32 2e 35 20 32 20 34  .0 1 2.0 2.5 2 4
1800: 2e 35 20 35 2e 35 20 33 20 38 2e 35 20 31 31 2e  .5 5.5 3 8.5 11.
1810: 35 20 34 20 31 35 2e 35 20 31 38 2e 30 20 35 20  5 4 15.5 18.0 5 
1820: 32 33 2e 30 7d 0a 64 6f 5f 74 65 73 74 20 73 65  23.0}.do_test se
1830: 6c 65 63 74 36 2d 33 2e 31 31 20 7b 0a 20 20 65  lect6-3.11 {.  e
1840: 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c  xecsql {.    SEL
1850: 45 43 54 20 61 2c 62 2c 61 2b 62 20 46 52 4f 4d  ECT a,b,a+b FROM
1860: 20 0a 20 20 20 20 20 20 20 28 53 45 4c 45 43 54   .       (SELECT
1870: 20 61 76 67 28 78 29 20 61 73 20 27 61 27 2c 20   avg(x) as 'a', 
1880: 79 20 61 73 20 27 62 27 20 46 52 4f 4d 20 74 31  y as 'b' FROM t1
1890: 20 47 52 4f 55 50 20 42 59 20 62 29 0a 20 20 20   GROUP BY b).   
18a0: 20 57 48 45 52 45 20 62 3c 34 20 4f 52 44 45 52   WHERE b<4 ORDER
18b0: 20 42 59 20 61 0a 20 20 7d 0a 7d 20 7b 31 2e 30   BY a.  }.} {1.0
18c0: 20 31 20 32 2e 30 20 32 2e 35 20 32 20 34 2e 35   1 2.0 2.5 2 4.5
18d0: 20 35 2e 35 20 33 20 38 2e 35 7d 0a 64 6f 5f 74   5.5 3 8.5}.do_t
18e0: 65 73 74 20 73 65 6c 65 63 74 36 2d 33 2e 31 32  est select6-3.12
18f0: 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20   {.  execsql {. 
1900: 20 20 20 53 45 4c 45 43 54 20 61 2c 62 2c 61 2b     SELECT a,b,a+
1910: 62 20 46 52 4f 4d 20 0a 20 20 20 20 20 20 20 28  b FROM .       (
1920: 53 45 4c 45 43 54 20 61 76 67 28 78 29 20 61 73  SELECT avg(x) as
1930: 20 27 61 27 2c 20 79 20 61 73 20 27 62 27 20 46   'a', y as 'b' F
1940: 52 4f 4d 20 74 31 20 47 52 4f 55 50 20 42 59 20  ROM t1 GROUP BY 
1950: 62 20 48 41 56 49 4e 47 20 61 3e 31 29 0a 20 20  b HAVING a>1).  
1960: 20 20 57 48 45 52 45 20 62 3c 34 20 4f 52 44 45    WHERE b<4 ORDE
1970: 52 20 42 59 20 61 0a 20 20 7d 0a 7d 20 7b 32 2e  R BY a.  }.} {2.
1980: 35 20 32 20 34 2e 35 20 35 2e 35 20 33 20 38 2e  5 2 4.5 5.5 3 8.
1990: 35 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63  5}.do_test selec
19a0: 74 36 2d 33 2e 31 33 20 7b 0a 20 20 65 78 65 63  t6-3.13 {.  exec
19b0: 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54  sql {.    SELECT
19c0: 20 61 2c 62 2c 61 2b 62 20 46 52 4f 4d 20 0a 20   a,b,a+b FROM . 
19d0: 20 20 20 20 20 20 28 53 45 4c 45 43 54 20 61 76        (SELECT av
19e0: 67 28 78 29 20 61 73 20 27 61 27 2c 20 79 20 61  g(x) as 'a', y a
19f0: 73 20 27 62 27 20 46 52 4f 4d 20 74 31 20 47 52  s 'b' FROM t1 GR
1a00: 4f 55 50 20 42 59 20 62 20 48 41 56 49 4e 47 20  OUP BY b HAVING 
1a10: 61 3e 31 29 0a 20 20 20 20 4f 52 44 45 52 20 42  a>1).    ORDER B
1a20: 59 20 61 0a 20 20 7d 0a 7d 20 7b 32 2e 35 20 32  Y a.  }.} {2.5 2
1a30: 20 34 2e 35 20 35 2e 35 20 33 20 38 2e 35 20 31   4.5 5.5 3 8.5 1
1a40: 31 2e 35 20 34 20 31 35 2e 35 20 31 38 2e 30 20  1.5 4 15.5 18.0 
1a50: 35 20 32 33 2e 30 7d 0a 64 6f 5f 74 65 73 74 20  5 23.0}.do_test 
1a60: 73 65 6c 65 63 74 36 2d 33 2e 31 34 20 7b 0a 20  select6-3.14 {. 
1a70: 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53   execsql {.    S
1a80: 45 4c 45 43 54 20 5b 63 6f 75 6e 74 28 2a 29 5d  ELECT [count(*)]
1a90: 2c 79 20 46 52 4f 4d 20 28 53 45 4c 45 43 54 20  ,y FROM (SELECT 
1aa0: 63 6f 75 6e 74 28 2a 29 2c 20 79 20 46 52 4f 4d  count(*), y FROM
1ab0: 20 74 31 20 47 52 4f 55 50 20 42 59 20 79 29 0a   t1 GROUP BY y).
1ac0: 20 20 20 20 4f 52 44 45 52 20 42 59 20 5b 63 6f      ORDER BY [co
1ad0: 75 6e 74 28 2a 29 5d 0a 20 20 7d 0a 7d 20 7b 31  unt(*)].  }.} {1
1ae0: 20 31 20 32 20 32 20 34 20 33 20 35 20 35 20 38   1 2 2 4 3 5 5 8
1af0: 20 34 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65   4}.do_test sele
1b00: 63 74 36 2d 33 2e 31 35 20 7b 0a 20 20 65 78 65  ct6-3.15 {.  exe
1b10: 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43  csql {.    SELEC
1b20: 54 20 5b 63 6f 75 6e 74 28 2a 29 5d 2c 79 20 46  T [count(*)],y F
1b30: 52 4f 4d 20 28 53 45 4c 45 43 54 20 63 6f 75 6e  ROM (SELECT coun
1b40: 74 28 2a 29 2c 20 79 20 46 52 4f 4d 20 74 31 20  t(*), y FROM t1 
1b50: 47 52 4f 55 50 20 42 59 20 79 29 0a 20 20 20 20  GROUP BY y).    
1b60: 4f 52 44 45 52 20 42 59 20 79 0a 20 20 7d 0a 7d  ORDER BY y.  }.}
1b70: 20 7b 31 20 31 20 32 20 32 20 34 20 33 20 38 20   {1 1 2 2 4 3 8 
1b80: 34 20 35 20 35 7d 0a 0a 64 6f 5f 74 65 73 74 20  4 5 5}..do_test 
1b90: 73 65 6c 65 63 74 36 2d 34 2e 31 20 7b 0a 20 20  select6-4.1 {.  
1ba0: 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53 45  execsql {.    SE
1bb0: 4c 45 43 54 20 61 2c 62 2c 63 20 46 52 4f 4d 20  LECT a,b,c FROM 
1bc0: 0a 20 20 20 20 20 20 28 53 45 4c 45 43 54 20 78  .      (SELECT x
1bd0: 20 41 53 20 27 61 27 2c 20 79 20 41 53 20 27 62   AS 'a', y AS 'b
1be0: 27 2c 20 78 2b 79 20 41 53 20 27 63 27 20 46 52  ', x+y AS 'c' FR
1bf0: 4f 4d 20 74 31 20 57 48 45 52 45 20 79 3d 34 29  OM t1 WHERE y=4)
1c00: 0a 20 20 20 20 57 48 45 52 45 20 61 3c 31 30 20  .    WHERE a<10 
1c10: 4f 52 44 45 52 20 42 59 20 61 3b 0a 20 20 7d 0a  ORDER BY a;.  }.
1c20: 7d 20 7b 38 20 34 20 31 32 20 39 20 34 20 31 33  } {8 4 12 9 4 13
1c30: 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74  }.do_test select
1c40: 36 2d 34 2e 32 20 7b 0a 20 20 65 78 65 63 73 71  6-4.2 {.  execsq
1c50: 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 79  l {.    SELECT y
1c60: 20 46 52 4f 4d 20 28 53 45 4c 45 43 54 20 44 49   FROM (SELECT DI
1c70: 53 54 49 4e 43 54 20 79 20 46 52 4f 4d 20 74 31  STINCT y FROM t1
1c80: 29 20 57 48 45 52 45 20 79 3c 35 20 4f 52 44 45  ) WHERE y<5 ORDE
1c90: 52 20 42 59 20 79 0a 20 20 7d 0a 7d 20 7b 31 20  R BY y.  }.} {1 
1ca0: 32 20 33 20 34 7d 0a 64 6f 5f 74 65 73 74 20 73  2 3 4}.do_test s
1cb0: 65 6c 65 63 74 36 2d 34 2e 33 20 7b 0a 20 20 65  elect6-4.3 {.  e
1cc0: 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c  xecsql {.    SEL
1cd0: 45 43 54 20 44 49 53 54 49 4e 43 54 20 79 20 46  ECT DISTINCT y F
1ce0: 52 4f 4d 20 28 53 45 4c 45 43 54 20 79 20 46 52  ROM (SELECT y FR
1cf0: 4f 4d 20 74 31 29 20 57 48 45 52 45 20 79 3c 35  OM t1) WHERE y<5
1d00: 20 4f 52 44 45 52 20 42 59 20 79 0a 20 20 7d 0a   ORDER BY y.  }.
1d10: 7d 20 7b 31 20 32 20 33 20 34 7d 0a 64 6f 5f 74  } {1 2 3 4}.do_t
1d20: 65 73 74 20 73 65 6c 65 63 74 36 2d 34 2e 34 20  est select6-4.4 
1d30: 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20  {.  execsql {.  
1d40: 20 20 53 45 4c 45 43 54 20 61 76 67 28 79 29 20    SELECT avg(y) 
1d50: 46 52 4f 4d 20 28 53 45 4c 45 43 54 20 44 49 53  FROM (SELECT DIS
1d60: 54 49 4e 43 54 20 79 20 46 52 4f 4d 20 74 31 29  TINCT y FROM t1)
1d70: 20 57 48 45 52 45 20 79 3c 35 20 4f 52 44 45 52   WHERE y<5 ORDER
1d80: 20 42 59 20 79 0a 20 20 7d 0a 7d 20 7b 32 2e 35   BY y.  }.} {2.5
1d90: 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74  }.do_test select
1da0: 36 2d 34 2e 35 20 7b 0a 20 20 65 78 65 63 73 71  6-4.5 {.  execsq
1db0: 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 61  l {.    SELECT a
1dc0: 76 67 28 79 29 20 46 52 4f 4d 20 28 53 45 4c 45  vg(y) FROM (SELE
1dd0: 43 54 20 44 49 53 54 49 4e 43 54 20 79 20 46 52  CT DISTINCT y FR
1de0: 4f 4d 20 74 31 20 57 48 45 52 45 20 79 3c 35 29  OM t1 WHERE y<5)
1df0: 20 4f 52 44 45 52 20 42 59 20 79 0a 20 20 7d 0a   ORDER BY y.  }.
1e00: 7d 20 7b 32 2e 35 7d 0a 0a 64 6f 5f 74 65 73 74  } {2.5}..do_test
1e10: 20 73 65 6c 65 63 74 36 2d 35 2e 31 20 7b 0a 20   select6-5.1 {. 
1e20: 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53   execsql {.    S
1e30: 45 4c 45 43 54 20 61 2c 78 2c 62 20 46 52 4f 4d  ELECT a,x,b FROM
1e40: 0a 20 20 20 20 20 20 28 53 45 4c 45 43 54 20 78  .      (SELECT x
1e50: 2b 33 20 41 53 20 27 61 27 2c 20 78 20 46 52 4f  +3 AS 'a', x FRO
1e60: 4d 20 74 31 20 57 48 45 52 45 20 79 3d 33 29 20  M t1 WHERE y=3) 
1e70: 41 53 20 27 70 27 2c 0a 20 20 20 20 20 20 28 53  AS 'p',.      (S
1e80: 45 4c 45 43 54 20 78 20 41 53 20 27 62 27 20 46  ELECT x AS 'b' F
1e90: 52 4f 4d 20 74 31 20 57 48 45 52 45 20 79 3d 34  ROM t1 WHERE y=4
1ea0: 29 20 41 53 20 27 71 27 0a 20 20 20 20 57 48 45  ) AS 'q'.    WHE
1eb0: 52 45 20 61 3d 62 0a 20 20 20 20 4f 52 44 45 52  RE a=b.    ORDER
1ec0: 20 42 59 20 61 0a 20 20 7d 0a 7d 20 7b 38 20 35   BY a.  }.} {8 5
1ed0: 20 38 20 39 20 36 20 39 20 31 30 20 37 20 31 30   8 9 6 9 10 7 10
1ee0: 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74  }.do_test select
1ef0: 36 2d 35 2e 32 20 7b 0a 20 20 65 78 65 63 73 71  6-5.2 {.  execsq
1f00: 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 61  l {.    SELECT a
1f10: 2c 78 2c 62 20 46 52 4f 4d 0a 20 20 20 20 20 20  ,x,b FROM.      
1f20: 28 53 45 4c 45 43 54 20 78 2b 33 20 41 53 20 27  (SELECT x+3 AS '
1f30: 61 27 2c 20 78 20 46 52 4f 4d 20 74 31 20 57 48  a', x FROM t1 WH
1f40: 45 52 45 20 79 3d 33 29 2c 0a 20 20 20 20 20 20  ERE y=3),.      
1f50: 28 53 45 4c 45 43 54 20 78 20 41 53 20 27 62 27  (SELECT x AS 'b'
1f60: 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20 79   FROM t1 WHERE y
1f70: 3d 34 29 0a 20 20 20 20 57 48 45 52 45 20 61 3d  =4).    WHERE a=
1f80: 62 0a 20 20 20 20 4f 52 44 45 52 20 42 59 20 61  b.    ORDER BY a
1f90: 0a 20 20 7d 0a 7d 20 7b 38 20 35 20 38 20 39 20  .  }.} {8 5 8 9 
1fa0: 36 20 39 20 31 30 20 37 20 31 30 7d 0a 0a 23 20  6 9 10 7 10}..# 
1fb0: 54 65 73 74 73 20 6f 66 20 63 6f 6d 70 6f 75 6e  Tests of compoun
1fc0: 64 20 73 75 62 2d 73 65 6c 65 63 74 73 0a 23 0a  d sub-selects.#.
1fd0: 69 66 63 61 70 61 62 6c 65 20 63 6f 6d 70 6f 75  ifcapable compou
1fe0: 6e 64 20 7b 0a 64 6f 5f 74 65 73 74 20 73 65 6c  nd {.do_test sel
1ff0: 65 63 74 35 2d 36 2e 31 20 7b 0a 20 20 65 78 65  ect5-6.1 {.  exe
2000: 63 73 71 6c 20 7b 0a 20 20 20 20 44 45 4c 45 54  csql {.    DELET
2010: 45 20 46 52 4f 4d 20 74 31 20 57 48 45 52 45 20  E FROM t1 WHERE 
2020: 78 3e 34 3b 0a 20 20 20 20 53 45 4c 45 43 54 20  x>4;.    SELECT 
2030: 2a 20 46 52 4f 4d 20 74 31 0a 20 20 7d 0a 7d 20  * FROM t1.  }.} 
2040: 7b 31 20 31 20 32 20 32 20 33 20 32 20 34 20 33  {1 1 2 2 3 2 4 3
2050: 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74  }.do_test select
2060: 36 2d 36 2e 32 20 7b 0a 20 20 65 78 65 63 73 71  6-6.2 {.  execsq
2070: 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20 2a  l {.    SELECT *
2080: 20 46 52 4f 4d 20 28 0a 20 20 20 20 20 20 53 45   FROM (.      SE
2090: 4c 45 43 54 20 78 20 41 53 20 27 61 27 20 46 52  LECT x AS 'a' FR
20a0: 4f 4d 20 74 31 20 55 4e 49 4f 4e 20 41 4c 4c 20  OM t1 UNION ALL 
20b0: 53 45 4c 45 43 54 20 78 2b 31 30 20 41 53 20 27  SELECT x+10 AS '
20c0: 61 27 20 46 52 4f 4d 20 74 31 0a 20 20 20 20 29  a' FROM t1.    )
20d0: 20 4f 52 44 45 52 20 42 59 20 61 3b 0a 20 20 7d   ORDER BY a;.  }
20e0: 0a 7d 20 7b 31 20 32 20 33 20 34 20 31 31 20 31  .} {1 2 3 4 11 1
20f0: 32 20 31 33 20 31 34 7d 0a 64 6f 5f 74 65 73 74  2 13 14}.do_test
2100: 20 73 65 6c 65 63 74 36 2d 36 2e 33 20 7b 0a 20   select6-6.3 {. 
2110: 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53   execsql {.    S
2120: 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 28 0a 20  ELECT * FROM (. 
2130: 20 20 20 20 20 53 45 4c 45 43 54 20 78 20 41 53       SELECT x AS
2140: 20 27 61 27 20 46 52 4f 4d 20 74 31 20 55 4e 49   'a' FROM t1 UNI
2150: 4f 4e 20 41 4c 4c 20 53 45 4c 45 43 54 20 78 2b  ON ALL SELECT x+
2160: 31 20 41 53 20 27 61 27 20 46 52 4f 4d 20 74 31  1 AS 'a' FROM t1
2170: 0a 20 20 20 20 29 20 4f 52 44 45 52 20 42 59 20  .    ) ORDER BY 
2180: 61 3b 0a 20 20 7d 0a 7d 20 7b 31 20 32 20 32 20  a;.  }.} {1 2 2 
2190: 33 20 33 20 34 20 34 20 35 7d 0a 64 6f 5f 74 65  3 3 4 4 5}.do_te
21a0: 73 74 20 73 65 6c 65 63 74 36 2d 36 2e 34 20 7b  st select6-6.4 {
21b0: 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20  .  execsql {.   
21c0: 20 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 28   SELECT * FROM (
21d0: 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20 78 20  .      SELECT x 
21e0: 41 53 20 27 61 27 20 46 52 4f 4d 20 74 31 20 55  AS 'a' FROM t1 U
21f0: 4e 49 4f 4e 20 53 45 4c 45 43 54 20 78 2b 31 20  NION SELECT x+1 
2200: 41 53 20 27 61 27 20 46 52 4f 4d 20 74 31 0a 20  AS 'a' FROM t1. 
2210: 20 20 20 29 20 4f 52 44 45 52 20 42 59 20 61 3b     ) ORDER BY a;
2220: 0a 20 20 7d 0a 7d 20 7b 31 20 32 20 33 20 34 20  .  }.} {1 2 3 4 
2230: 35 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63  5}.do_test selec
2240: 74 36 2d 36 2e 35 20 7b 0a 20 20 65 78 65 63 73  t6-6.5 {.  execs
2250: 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20  ql {.    SELECT 
2260: 2a 20 46 52 4f 4d 20 28 0a 20 20 20 20 20 20 53  * FROM (.      S
2270: 45 4c 45 43 54 20 78 20 41 53 20 27 61 27 20 46  ELECT x AS 'a' F
2280: 52 4f 4d 20 74 31 20 49 4e 54 45 52 53 45 43 54  ROM t1 INTERSECT
2290: 20 53 45 4c 45 43 54 20 78 2b 31 20 41 53 20 27   SELECT x+1 AS '
22a0: 61 27 20 46 52 4f 4d 20 74 31 0a 20 20 20 20 29  a' FROM t1.    )
22b0: 20 4f 52 44 45 52 20 42 59 20 61 3b 0a 20 20 7d   ORDER BY a;.  }
22c0: 0a 7d 20 7b 32 20 33 20 34 7d 0a 64 6f 5f 74 65  .} {2 3 4}.do_te
22d0: 73 74 20 73 65 6c 65 63 74 36 2d 36 2e 36 20 7b  st select6-6.6 {
22e0: 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20  .  execsql {.   
22f0: 20 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 28   SELECT * FROM (
2300: 0a 20 20 20 20 20 20 53 45 4c 45 43 54 20 78 20  .      SELECT x 
2310: 41 53 20 27 61 27 20 46 52 4f 4d 20 74 31 20 45  AS 'a' FROM t1 E
2320: 58 43 45 50 54 20 53 45 4c 45 43 54 20 78 2a 32  XCEPT SELECT x*2
2330: 20 41 53 20 27 61 27 20 46 52 4f 4d 20 74 31 0a   AS 'a' FROM t1.
2340: 20 20 20 20 29 20 4f 52 44 45 52 20 42 59 20 61      ) ORDER BY a
2350: 3b 0a 20 20 7d 0a 7d 20 7b 31 20 33 7d 0a 7d 20  ;.  }.} {1 3}.} 
2360: 3b 23 20 69 66 63 61 70 61 62 6c 65 20 63 6f 6d  ;# ifcapable com
2370: 70 6f 75 6e 64 0a 0a 23 20 53 75 62 73 65 6c 65  pound..# Subsele
2380: 63 74 73 20 77 69 74 68 20 6e 6f 20 46 52 4f 4d  cts with no FROM
2390: 20 63 6c 61 75 73 65 0a 23 0a 64 6f 5f 74 65 73   clause.#.do_tes
23a0: 74 20 73 65 6c 65 63 74 36 2d 37 2e 31 20 7b 0a  t select6-7.1 {.
23b0: 20 20 65 78 65 63 73 71 6c 20 7b 0a 20 20 20 20    execsql {.    
23c0: 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 28 53  SELECT * FROM (S
23d0: 45 4c 45 43 54 20 31 29 0a 20 20 7d 0a 7d 20 7b  ELECT 1).  }.} {
23e0: 31 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63  1}.do_test selec
23f0: 74 36 2d 37 2e 32 20 7b 0a 20 20 65 78 65 63 73  t6-7.2 {.  execs
2400: 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45 43 54 20  ql {.    SELECT 
2410: 63 2c 62 2c 61 2c 2a 20 46 52 4f 4d 20 28 53 45  c,b,a,* FROM (SE
2420: 4c 45 43 54 20 31 20 41 53 20 27 61 27 2c 20 32  LECT 1 AS 'a', 2
2430: 20 41 53 20 27 62 27 2c 20 27 61 62 63 27 20 41   AS 'b', 'abc' A
2440: 53 20 27 63 27 29 0a 20 20 7d 0a 7d 20 7b 61 62  S 'c').  }.} {ab
2450: 63 20 32 20 31 20 31 20 32 20 61 62 63 7d 0a 64  c 2 1 1 2 abc}.d
2460: 6f 5f 74 65 73 74 20 73 65 6c 65 63 74 36 2d 37  o_test select6-7
2470: 2e 33 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b  .3 {.  execsql {
2480: 0a 20 20 20 20 53 45 4c 45 43 54 20 63 2c 62 2c  .    SELECT c,b,
2490: 61 2c 2a 20 46 52 4f 4d 20 28 53 45 4c 45 43 54  a,* FROM (SELECT
24a0: 20 31 20 41 53 20 27 61 27 2c 20 32 20 41 53 20   1 AS 'a', 2 AS 
24b0: 27 62 27 2c 20 27 61 62 63 27 20 41 53 20 27 63  'b', 'abc' AS 'c
24c0: 27 20 57 48 45 52 45 20 30 29 0a 20 20 7d 0a 7d  ' WHERE 0).  }.}
24d0: 20 7b 7d 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65   {}.do_test sele
24e0: 63 74 36 2d 37 2e 34 20 7b 0a 20 20 65 78 65 63  ct6-7.4 {.  exec
24f0: 73 71 6c 32 20 7b 0a 20 20 20 20 53 45 4c 45 43  sql2 {.    SELEC
2500: 54 20 63 2c 62 2c 61 2c 2a 20 46 52 4f 4d 20 28  T c,b,a,* FROM (
2510: 53 45 4c 45 43 54 20 31 20 41 53 20 27 61 27 2c  SELECT 1 AS 'a',
2520: 20 32 20 41 53 20 27 62 27 2c 20 27 61 62 63 27   2 AS 'b', 'abc'
2530: 20 41 53 20 27 63 27 20 57 48 45 52 45 20 31 29   AS 'c' WHERE 1)
2540: 0a 20 20 7d 0a 7d 20 7b 63 20 61 62 63 20 62 20  .  }.} {c abc b 
2550: 32 20 61 20 31 20 61 20 31 20 62 20 32 20 63 20  2 a 1 a 1 b 2 c 
2560: 61 62 63 7d 0a 0a 23 20 54 68 65 20 72 65 6d 61  abc}..# The rema
2570: 69 6e 69 6e 67 20 74 65 73 74 73 20 69 6e 20 74  ining tests in t
2580: 68 69 73 20 66 69 6c 65 20 64 65 70 65 6e 64 20  his file depend 
2590: 6f 6e 20 74 68 65 20 45 58 50 4c 41 49 4e 20 6b  on the EXPLAIN k
25a0: 65 79 77 6f 72 64 2e 0a 23 20 53 6b 69 70 20 74  eyword..# Skip t
25b0: 68 65 73 65 20 74 65 73 74 73 20 69 66 20 45 58  hese tests if EX
25c0: 50 4c 41 49 4e 20 69 73 20 64 69 73 61 62 6c 65  PLAIN is disable
25d0: 64 20 69 6e 20 74 68 65 20 63 75 72 72 65 6e 74  d in the current
25e0: 20 62 75 69 6c 64 2e 0a 23 0a 69 66 63 61 70 61   build..#.ifcapa
25f0: 62 6c 65 20 7b 21 65 78 70 6c 61 69 6e 7d 20 7b  ble {!explain} {
2600: 0a 20 20 66 69 6e 69 73 68 5f 74 65 73 74 0a 20  .  finish_test. 
2610: 20 72 65 74 75 72 6e 0a 7d 0a 0a 23 20 54 68 65   return.}..# The
2620: 20 66 6f 6c 6c 6f 77 69 6e 67 20 70 72 6f 63 65   following proce
2630: 64 75 72 65 20 63 6f 6d 70 69 6c 65 73 20 74 68  dure compiles th
2640: 65 20 53 51 4c 20 67 69 76 65 6e 20 61 73 20 61  e SQL given as a
2650: 6e 20 61 72 67 75 6d 65 6e 74 20 61 6e 64 20 72  n argument and r
2660: 65 74 75 72 6e 73 0a 23 20 54 52 55 45 20 69 66  eturns.# TRUE if
2670: 20 74 68 61 74 20 53 51 4c 20 75 73 65 73 20 61   that SQL uses a
2680: 6e 79 20 74 72 61 6e 73 69 65 6e 74 20 74 61 62  ny transient tab
2690: 6c 65 73 20 61 6e 64 20 72 65 74 75 72 6e 73 20  les and returns 
26a0: 46 41 4c 53 45 20 69 66 20 6e 6f 0a 23 20 74 72  FALSE if no.# tr
26b0: 61 6e 73 69 65 6e 74 20 74 61 62 6c 65 73 20 61  ansient tables a
26c0: 72 65 20 75 73 65 64 2e 20 20 54 68 69 73 20 69  re used.  This i
26d0: 73 20 75 73 65 64 20 74 6f 20 6d 61 6b 65 20 73  s used to make s
26e0: 75 72 65 20 74 68 61 74 20 74 68 65 0a 23 20 73  ure that the.# s
26f0: 71 6c 69 74 65 46 6c 61 74 74 65 6e 53 75 62 71  qliteFlattenSubq
2700: 75 65 72 79 28 29 20 72 6f 75 74 69 6e 65 20 69  uery() routine i
2710: 6e 20 73 65 6c 65 63 74 2e 63 20 69 73 20 64 6f  n select.c is do
2720: 69 6e 67 20 69 74 73 20 6a 6f 62 2e 0a 23 0a 70  ing its job..#.p
2730: 72 6f 63 20 69 73 5f 66 6c 61 74 20 7b 73 71 6c  roc is_flat {sql
2740: 7d 20 7b 0a 20 20 72 65 74 75 72 6e 20 5b 65 78  } {.  return [ex
2750: 70 72 20 30 3e 5b 6c 73 65 61 72 63 68 20 5b 65  pr 0>[lsearch [e
2760: 78 65 63 73 71 6c 20 22 45 58 50 4c 41 49 4e 20  xecsql "EXPLAIN 
2770: 24 73 71 6c 22 5d 20 4f 70 65 6e 54 65 6d 70 5d  $sql"] OpenTemp]
2780: 5d 0a 7d 0a 0a 23 20 43 68 65 63 6b 20 74 68 61  ].}..# Check tha
2790: 74 20 74 68 65 20 66 6c 61 74 74 65 6e 65 72 20  t the flattener 
27a0: 77 6f 72 6b 73 20 63 6f 72 72 65 63 74 6c 79 20  works correctly 
27b0: 66 6f 72 20 64 65 65 70 6c 79 20 6e 65 73 74 65  for deeply neste
27c0: 64 20 73 75 62 71 75 65 72 69 65 73 0a 23 20 69  d subqueries.# i
27d0: 6e 76 6f 6c 76 69 6e 67 20 6a 6f 69 6e 73 2e 0a  nvolving joins..
27e0: 23 0a 64 6f 5f 74 65 73 74 20 73 65 6c 65 63 74  #.do_test select
27f0: 36 2d 38 2e 31 20 7b 0a 20 20 65 78 65 63 73 71  6-8.1 {.  execsq
2800: 6c 20 7b 0a 20 20 20 20 42 45 47 49 4e 3b 0a 20  l {.    BEGIN;. 
2810: 20 20 20 43 52 45 41 54 45 20 54 41 42 4c 45 20     CREATE TABLE 
2820: 74 33 28 70 2c 71 29 3b 0a 20 20 20 20 49 4e 53  t3(p,q);.    INS
2830: 45 52 54 20 49 4e 54 4f 20 74 33 20 56 41 4c 55  ERT INTO t3 VALU
2840: 45 53 28 31 2c 31 31 29 3b 0a 20 20 20 20 49 4e  ES(1,11);.    IN
2850: 53 45 52 54 20 49 4e 54 4f 20 74 33 20 56 41 4c  SERT INTO t3 VAL
2860: 55 45 53 28 32 2c 32 32 29 3b 0a 20 20 20 20 43  UES(2,22);.    C
2870: 52 45 41 54 45 20 54 41 42 4c 45 20 74 34 28 71  REATE TABLE t4(q
2880: 2c 72 29 3b 0a 20 20 20 20 49 4e 53 45 52 54 20  ,r);.    INSERT 
2890: 49 4e 54 4f 20 74 34 20 56 41 4c 55 45 53 28 31  INTO t4 VALUES(1
28a0: 31 2c 31 31 31 29 3b 0a 20 20 20 20 49 4e 53 45  1,111);.    INSE
28b0: 52 54 20 49 4e 54 4f 20 74 34 20 56 41 4c 55 45  RT INTO t4 VALUE
28c0: 53 28 32 32 2c 32 32 32 29 3b 0a 20 20 20 20 43  S(22,222);.    C
28d0: 4f 4d 4d 49 54 3b 0a 20 20 20 20 53 45 4c 45 43  OMMIT;.    SELEC
28e0: 54 20 2a 20 46 52 4f 4d 20 74 33 20 4e 41 54 55  T * FROM t3 NATU
28f0: 52 41 4c 20 4a 4f 49 4e 20 74 34 3b 0a 20 20 7d  RAL JOIN t4;.  }
2900: 0a 7d 20 7b 31 20 31 31 20 31 31 31 20 32 20 32  .} {1 11 111 2 2
2910: 32 20 32 32 32 7d 0a 64 6f 5f 74 65 73 74 20 73  2 222}.do_test s
2920: 65 6c 65 63 74 36 2d 38 2e 32 20 7b 0a 20 20 65  elect6-8.2 {.  e
2930: 78 65 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c  xecsql {.    SEL
2940: 45 43 54 20 79 2c 20 70 2c 20 71 2c 20 72 20 46  ECT y, p, q, r F
2950: 52 4f 4d 0a 20 20 20 20 20 20 20 28 53 45 4c 45  ROM.       (SELE
2960: 43 54 20 74 31 2e 79 20 41 53 20 79 2c 20 74 32  CT t1.y AS y, t2
2970: 2e 62 20 41 53 20 62 20 46 52 4f 4d 20 74 31 2c  .b AS b FROM t1,
2980: 20 74 32 20 57 48 45 52 45 20 74 31 2e 78 3d 74   t2 WHERE t1.x=t
2990: 32 2e 61 29 20 41 53 20 6d 2c 0a 20 20 20 20 20  2.a) AS m,.     
29a0: 20 20 28 53 45 4c 45 43 54 20 74 33 2e 70 20 41    (SELECT t3.p A
29b0: 53 20 70 2c 20 74 33 2e 71 20 41 53 20 71 2c 20  S p, t3.q AS q, 
29c0: 74 34 2e 72 20 41 53 20 72 20 46 52 4f 4d 20 74  t4.r AS r FROM t
29d0: 33 20 4e 41 54 55 52 41 4c 20 4a 4f 49 4e 20 74  3 NATURAL JOIN t
29e0: 34 29 20 61 73 20 6e 0a 20 20 20 20 57 48 45 52  4) as n.    WHER
29f0: 45 20 20 79 3d 70 0a 20 20 7d 0a 7d 20 7b 31 20  E  y=p.  }.} {1 
2a00: 31 20 31 31 20 31 31 31 20 32 20 32 20 32 32 20  1 11 111 2 2 22 
2a10: 32 32 32 20 32 20 32 20 32 32 20 32 32 32 7d 0a  222 2 2 22 222}.
2a20: 23 20 49 66 20 76 69 65 77 20 73 75 70 70 6f 72  # If view suppor
2a30: 74 20 69 73 20 6f 6d 69 74 74 65 64 20 66 72 6f  t is omitted fro
2a40: 6d 20 74 68 65 20 62 75 69 6c 64 2c 20 74 68 65  m the build, the
2a50: 6e 20 73 6f 20 69 73 20 74 68 65 20 71 75 65 72  n so is the quer
2a60: 79 20 0a 23 20 22 66 6c 61 74 74 65 6e 65 72 22  y .# "flattener"
2a70: 2e 20 53 6f 20 6f 6d 69 74 20 74 68 69 73 20 74  . So omit this t
2a80: 65 73 74 20 61 6e 64 20 74 65 73 74 20 73 65 6c  est and test sel
2a90: 65 63 74 36 2d 38 2e 36 20 69 6e 20 74 68 61 74  ect6-8.6 in that
2aa0: 20 63 61 73 65 2e 0a 69 66 63 61 70 61 62 6c 65   case..ifcapable
2ab0: 20 76 69 65 77 20 7b 0a 64 6f 5f 74 65 73 74 20   view {.do_test 
2ac0: 73 65 6c 65 63 74 36 2d 38 2e 33 20 7b 0a 20 20  select6-8.3 {.  
2ad0: 69 73 5f 66 6c 61 74 20 7b 0a 20 20 20 20 53 45  is_flat {.    SE
2ae0: 4c 45 43 54 20 79 2c 20 70 2c 20 71 2c 20 72 20  LECT y, p, q, r 
2af0: 46 52 4f 4d 0a 20 20 20 20 20 20 20 28 53 45 4c  FROM.       (SEL
2b00: 45 43 54 20 74 31 2e 79 20 41 53 20 79 2c 20 74  ECT t1.y AS y, t
2b10: 32 2e 62 20 41 53 20 62 20 46 52 4f 4d 20 74 31  2.b AS b FROM t1
2b20: 2c 20 74 32 20 57 48 45 52 45 20 74 31 2e 78 3d  , t2 WHERE t1.x=
2b30: 74 32 2e 61 29 20 41 53 20 6d 2c 0a 20 20 20 20  t2.a) AS m,.    
2b40: 20 20 20 28 53 45 4c 45 43 54 20 74 33 2e 70 20     (SELECT t3.p 
2b50: 41 53 20 70 2c 20 74 33 2e 71 20 41 53 20 71 2c  AS p, t3.q AS q,
2b60: 20 74 34 2e 72 20 41 53 20 72 20 46 52 4f 4d 20   t4.r AS r FROM 
2b70: 74 33 20 4e 41 54 55 52 41 4c 20 4a 4f 49 4e 20  t3 NATURAL JOIN 
2b80: 74 34 29 20 61 73 20 6e 0a 20 20 20 20 57 48 45  t4) as n.    WHE
2b90: 52 45 20 20 79 3d 70 0a 20 20 7d 0a 7d 20 7b 31  RE  y=p.  }.} {1
2ba0: 7d 0a 7d 20 3b 23 20 69 66 63 61 70 61 62 6c 65  }.} ;# ifcapable
2bb0: 20 76 69 65 77 0a 64 6f 5f 74 65 73 74 20 73 65   view.do_test se
2bc0: 6c 65 63 74 36 2d 38 2e 34 20 7b 0a 20 20 65 78  lect6-8.4 {.  ex
2bd0: 65 63 73 71 6c 20 7b 0a 20 20 20 20 53 45 4c 45  ecsql {.    SELE
2be0: 43 54 20 44 49 53 54 49 4e 43 54 20 79 2c 20 70  CT DISTINCT y, p
2bf0: 2c 20 71 2c 20 72 20 46 52 4f 4d 0a 20 20 20 20  , q, r FROM.    
2c00: 20 20 20 28 53 45 4c 45 43 54 20 74 31 2e 79 20     (SELECT t1.y 
2c10: 41 53 20 79 2c 20 74 32 2e 62 20 41 53 20 62 20  AS y, t2.b AS b 
2c20: 46 52 4f 4d 20 74 31 2c 20 74 32 20 57 48 45 52  FROM t1, t2 WHER
2c30: 45 20 74 31 2e 78 3d 74 32 2e 61 29 20 41 53 20  E t1.x=t2.a) AS 
2c40: 6d 2c 0a 20 20 20 20 20 20 20 28 53 45 4c 45 43  m,.       (SELEC
2c50: 54 20 74 33 2e 70 20 41 53 20 70 2c 20 74 33 2e  T t3.p AS p, t3.
2c60: 71 20 41 53 20 71 2c 20 74 34 2e 72 20 41 53 20  q AS q, t4.r AS 
2c70: 72 20 46 52 4f 4d 20 74 33 20 4e 41 54 55 52 41  r FROM t3 NATURA
2c80: 4c 20 4a 4f 49 4e 20 74 34 29 20 61 73 20 6e 0a  L JOIN t4) as n.
2c90: 20 20 20 20 57 48 45 52 45 20 20 79 3d 70 0a 20      WHERE  y=p. 
2ca0: 20 7d 0a 7d 20 7b 31 20 31 20 31 31 20 31 31 31   }.} {1 1 11 111
2cb0: 20 32 20 32 20 32 32 20 32 32 32 7d 0a 64 6f 5f   2 2 22 222}.do_
2cc0: 74 65 73 74 20 73 65 6c 65 63 74 36 2d 38 2e 35  test select6-8.5
2cd0: 20 7b 0a 20 20 65 78 65 63 73 71 6c 20 7b 0a 20   {.  execsql {. 
2ce0: 20 20 20 53 45 4c 45 43 54 20 2a 20 46 52 4f 4d     SELECT * FROM
2cf0: 20 0a 20 20 20 20 20 20 28 53 45 4c 45 43 54 20   .      (SELECT 
2d00: 79 2c 20 70 2c 20 71 2c 20 72 20 46 52 4f 4d 0a  y, p, q, r FROM.
2d10: 20 20 20 20 20 20 20 20 20 28 53 45 4c 45 43 54           (SELECT
2d20: 20 74 31 2e 79 20 41 53 20 79 2c 20 74 32 2e 62   t1.y AS y, t2.b
2d30: 20 41 53 20 62 20 46 52 4f 4d 20 74 31 2c 20 74   AS b FROM t1, t
2d40: 32 20 57 48 45 52 45 20 74 31 2e 78 3d 74 32 2e  2 WHERE t1.x=t2.
2d50: 61 29 20 41 53 20 6d 2c 0a 20 20 20 20 20 20 20  a) AS m,.       
2d60: 20 20 28 53 45 4c 45 43 54 20 74 33 2e 70 20 41    (SELECT t3.p A
2d70: 53 20 70 2c 20 74 33 2e 71 20 41 53 20 71 2c 20  S p, t3.q AS q, 
2d80: 74 34 2e 72 20 41 53 20 72 20 46 52 4f 4d 20 74  t4.r AS r FROM t
2d90: 33 20 4e 41 54 55 52 41 4c 20 4a 4f 49 4e 20 74  3 NATURAL JOIN t
2da0: 34 29 20 61 73 20 6e 0a 20 20 20 20 20 20 57 48  4) as n.      WH
2db0: 45 52 45 20 20 79 3d 70 29 20 41 53 20 65 2c 0a  ERE  y=p) AS e,.
2dc0: 20 20 20 20 20 20 28 53 45 4c 45 43 54 20 72 20        (SELECT r 
2dd0: 41 53 20 7a 20 46 52 4f 4d 20 74 34 20 57 48 45  AS z FROM t4 WHE
2de0: 52 45 20 71 3d 31 31 29 20 41 53 20 66 0a 20 20  RE q=11) AS f.  
2df0: 20 20 57 48 45 52 45 20 65 2e 72 3d 66 2e 7a 0a    WHERE e.r=f.z.
2e00: 20 20 7d 0a 7d 20 7b 31 20 31 20 31 31 20 31 31    }.} {1 1 11 11
2e10: 31 20 31 31 31 7d 0a 69 66 63 61 70 61 62 6c 65  1 111}.ifcapable
2e20: 20 76 69 65 77 20 7b 0a 64 6f 5f 74 65 73 74 20   view {.do_test 
2e30: 73 65 6c 65 63 74 36 2d 38 2e 36 20 7b 0a 20 20  select6-8.6 {.  
2e40: 69 73 5f 66 6c 61 74 20 7b 0a 20 20 20 20 53 45  is_flat {.    SE
2e50: 4c 45 43 54 20 2a 20 46 52 4f 4d 20 0a 20 20 20  LECT * FROM .   
2e60: 20 20 20 28 53 45 4c 45 43 54 20 79 2c 20 70 2c     (SELECT y, p,
2e70: 20 71 2c 20 72 20 46 52 4f 4d 0a 20 20 20 20 20   q, r FROM.     
2e80: 20 20 20 20 28 53 45 4c 45 43 54 20 74 31 2e 79      (SELECT t1.y
2e90: 20 41 53 20 79 2c 20 74 32 2e 62 20 41 53 20 62   AS y, t2.b AS b
2ea0: 20 46 52 4f 4d 20 74 31 2c 20 74 32 20 57 48 45   FROM t1, t2 WHE
2eb0: 52 45 20 74 31 2e 78 3d 74 32 2e 61 29 20 41 53  RE t1.x=t2.a) AS
2ec0: 20 6d 2c 0a 20 20 20 20 20 20 20 20 20 28 53 45   m,.         (SE
2ed0: 4c 45 43 54 20 74 33 2e 70 20 41 53 20 70 2c 20  LECT t3.p AS p, 
2ee0: 74 33 2e 71 20 41 53 20 71 2c 20 74 34 2e 72 20  t3.q AS q, t4.r 
2ef0: 41 53 20 72 20 46 52 4f 4d 20 74 33 20 4e 41 54  AS r FROM t3 NAT
2f00: 55 52 41 4c 20 4a 4f 49 4e 20 74 34 29 20 61 73  URAL JOIN t4) as
2f10: 20 6e 0a 20 20 20 20 20 20 57 48 45 52 45 20 20   n.      WHERE  
2f20: 79 3d 70 29 20 41 53 20 65 2c 0a 20 20 20 20 20  y=p) AS e,.     
2f30: 20 28 53 45 4c 45 43 54 20 72 20 41 53 20 7a 20   (SELECT r AS z 
2f40: 46 52 4f 4d 20 74 34 20 57 48 45 52 45 20 71 3d  FROM t4 WHERE q=
2f50: 31 31 29 20 41 53 20 66 0a 20 20 20 20 57 48 45  11) AS f.    WHE
2f60: 52 45 20 65 2e 72 3d 66 2e 7a 0a 20 20 7d 0a 7d  RE e.r=f.z.  }.}
2f70: 20 7b 31 7d 0a 7d 20 3b 23 20 69 66 63 61 70 61   {1}.} ;# ifcapa
2f80: 62 6c 65 20 76 69 65 77 0a 0a 0a 66 69 6e 69 73  ble view...finis
2f90: 68 5f 74 65 73 74 0a                             h_test.