000001  # 2010 July 16
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  #
000012  # This file implements tests to verify that the "testable statements" in 
000013  # the lang_select.html document are correct.
000014  #
000015  
000016  set testdir [file dirname $argv0]
000017  source $testdir/tester.tcl
000018  
000019  ifcapable !compound {
000020    finish_test
000021    return
000022  }
000023  
000024  do_execsql_test e_select-1.0 {
000025    CREATE TABLE t1(a, b);
000026    INSERT INTO t1 VALUES('a', 'one');
000027    INSERT INTO t1 VALUES('b', 'two');
000028    INSERT INTO t1 VALUES('c', 'three');
000029  
000030    CREATE TABLE t2(a, b);
000031    INSERT INTO t2 VALUES('a', 'I');
000032    INSERT INTO t2 VALUES('b', 'II');
000033    INSERT INTO t2 VALUES('c', 'III');
000034  
000035    CREATE TABLE t3(a, c);
000036    INSERT INTO t3 VALUES('a', 1);
000037    INSERT INTO t3 VALUES('b', 2);
000038  
000039    CREATE TABLE t4(a, c);
000040    INSERT INTO t4 VALUES('a', NULL);
000041    INSERT INTO t4 VALUES('b', 2);
000042  } {}
000043  set t1_cross_t2 [list                \
000044     a one   a I      a one   b II     \
000045     a one   c III    b two   a I      \
000046     b two   b II     b two   c III    \
000047     c three a I      c three b II     \
000048     c three c III                     \
000049  ]
000050  set t1_cross_t1 [list                  \
000051     a one   a one      a one   b two    \
000052     a one   c three    b two   a one    \
000053     b two   b two      b two   c three  \
000054     c three a one      c three b two    \
000055     c three c three                     \
000056  ]
000057  
000058  
000059  # This proc is a specialized version of [do_execsql_test].
000060  #
000061  # The second argument to this proc must be a SELECT statement that 
000062  # features a cross join of some time. Instead of the usual ",", 
000063  # "CROSS JOIN" or "INNER JOIN" join-op, the string %JOIN% must be 
000064  # substituted.
000065  #
000066  # This test runs the SELECT three times - once with:
000067  #
000068  #   * s/%JOIN%/,/
000069  #   * s/%JOIN%/JOIN/
000070  #   * s/%JOIN%/INNER JOIN/
000071  #   * s/%JOIN%/CROSS JOIN/
000072  #
000073  # and checks that each time the results of the SELECT are $res.
000074  #
000075  proc do_join_test {tn select res} {
000076    foreach {tn2 joinop} [list    1 ,    2 "CROSS JOIN"    3 "INNER JOIN"] {
000077      set S [string map [list %JOIN% $joinop] $select]
000078      uplevel do_execsql_test $tn.$tn2 [list $S] [list $res]
000079    }
000080  }
000081  
000082  #-------------------------------------------------------------------------
000083  # The following tests check that all paths on the syntax diagrams on
000084  # the lang_select.html page may be taken.
000085  #
000086  # -- syntax diagram join-constraint
000087  #
000088  do_join_test e_select-0.1.1 {
000089    SELECT count(*) FROM t1 %JOIN% t2 ON (t1.a=t2.a)
000090  } {3}
000091  do_join_test e_select-0.1.2 {
000092    SELECT count(*) FROM t1 %JOIN% t2 USING (a)
000093  } {3}
000094  do_join_test e_select-0.1.3 {
000095    SELECT count(*) FROM t1 %JOIN% t2
000096  } {9}
000097  do_catchsql_test e_select-0.1.4 {
000098    SELECT count(*) FROM t1, t2 ON (t1.a=t2.a) USING (a)
000099  } {1 {cannot have both ON and USING clauses in the same join}}
000100  do_catchsql_test e_select-0.1.5 {
000101    SELECT count(*) FROM t1, t2 USING (a) ON (t1.a=t2.a)
000102  } {1 {near "ON": syntax error}}
000103  
000104  # -- syntax diagram select-core
000105  #
000106  #   0: SELECT ...
000107  #   1: SELECT DISTINCT ...
000108  #   2: SELECT ALL ...
000109  #
000110  #   0: No FROM clause
000111  #   1: Has FROM clause
000112  #
000113  #   0: No WHERE clause
000114  #   1: Has WHERE clause
000115  #
000116  #   0: No GROUP BY clause
000117  #   1: Has GROUP BY clause
000118  #   2: Has GROUP BY and HAVING clauses
000119  #
000120  do_select_tests e_select-0.2 {
000121    0000.1  "SELECT 1, 2, 3 " {1 2 3}
000122    1000.1  "SELECT DISTINCT 1, 2, 3 " {1 2 3}
000123    2000.1  "SELECT ALL 1, 2, 3 " {1 2 3}
000124    
000125    0100.1  "SELECT a, b, a||b FROM t1 " {
000126      a one aone b two btwo c three cthree
000127    }
000128    1100.1  "SELECT DISTINCT a, b, a||b FROM t1 " {
000129      a one aone b two btwo c three cthree
000130    }
000131    1200.1  "SELECT ALL a, b, a||b FROM t1 " {
000132      a one aone b two btwo c three cthree
000133    }
000134  
000135    0010.1  "SELECT 1, 2, 3 WHERE 1 " {1 2 3}
000136    0010.2  "SELECT 1, 2, 3 WHERE 0 " {}
000137    0010.3  "SELECT 1, 2, 3 WHERE NULL " {}
000138  
000139    1010.1  "SELECT DISTINCT 1, 2, 3 WHERE 1 " {1 2 3}
000140  
000141    2010.1  "SELECT ALL 1, 2, 3 WHERE 1 " {1 2 3}
000142  
000143    0110.1  "SELECT a, b, a||b FROM t1 WHERE a!='x' " {
000144      a one aone b two btwo c three cthree
000145    }
000146    0110.2  "SELECT a, b, a||b FROM t1 WHERE a=='x'" {}
000147  
000148    1110.1  "SELECT DISTINCT a, b, a||b FROM t1 WHERE a!='x' " {
000149      a one aone b two btwo c three cthree
000150    }
000151  
000152    2110.0  "SELECT ALL a, b, a||b FROM t1 WHERE a=='x'" {}
000153  
000154    0001.1  "SELECT 1, 2, 3 GROUP BY 2" {1 2 3}
000155    0002.1  "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
000156    0002.2  "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}
000157  
000158    1001.1  "SELECT DISTINCT 1, 2, 3 GROUP BY 2" {1 2 3}
000159    1002.1  "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
000160    1002.2  "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}
000161  
000162    2001.1  "SELECT ALL 1, 2, 3 GROUP BY 2" {1 2 3}
000163    2002.1  "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
000164    2002.2  "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}
000165  
000166    0101.1  "SELECT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
000167    0102.1  "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=1" {
000168      1 a 1 c 1 b
000169    }
000170    0102.2  "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=2" { }
000171  
000172    1101.1  "SELECT DISTINCT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
000173    1102.1  "SELECT DISTINCT count(*), max(a) FROM t1 
000174             GROUP BY b HAVING count(*)=1" {
000175      1 a 1 c 1 b
000176    }
000177    1102.2  "SELECT DISTINCT count(*), max(a) FROM t1 
000178             GROUP BY b HAVING count(*)=2" { 
000179    }
000180  
000181    2101.1  "SELECT ALL count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
000182    2102.1  "SELECT ALL count(*), max(a) FROM t1 
000183             GROUP BY b HAVING count(*)=1" {
000184      1 a 1 c 1 b
000185    }
000186    2102.2  "SELECT ALL count(*), max(a) FROM t1 
000187             GROUP BY b HAVING count(*)=2" { 
000188    }
000189  
000190    0011.1  "SELECT 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3}
000191    0012.1  "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {}
000192    0012.2  "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)>1" {}
000193  
000194    1011.1  "SELECT DISTINCT 1, 2, 3 WHERE 0 GROUP BY 2" {}
000195    1012.1  "SELECT DISTINCT 1, 2, 3 WHERE 1 GROUP BY 2 HAVING count(*)=1" 
000196            {1 2 3}
000197    1012.2  "SELECT DISTINCT 1, 2, 3 WHERE NULL GROUP BY 2 HAVING count(*)>1" {}
000198  
000199    2011.1  "SELECT ALL 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3}
000200    2012.1  "SELECT ALL 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {}
000201    2012.2  "SELECT ALL 1, 2, 3 WHERE 'abc' GROUP BY 2 HAVING count(*)>1" {}
000202  
000203    0111.1  "SELECT count(*), max(a) FROM t1 WHERE a='a' GROUP BY b" {1 a}
000204    0112.1  "SELECT count(*), max(a) FROM t1 
000205             WHERE a='c' GROUP BY b HAVING count(*)=1" {1 c}
000206    0112.2  "SELECT count(*), max(a) FROM t1 
000207             WHERE 0 GROUP BY b HAVING count(*)=2" { }
000208    1111.1  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a<'c' GROUP BY b" 
000209            {1 a 1 b}
000210    1112.1  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a>'a'
000211             GROUP BY b HAVING count(*)=1" {
000212      1 c 1 b
000213    }
000214    1112.2  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE 0
000215             GROUP BY b HAVING count(*)=2" { 
000216    }
000217  
000218    2111.1  "SELECT ALL count(*), max(a) FROM t1 WHERE b>'one' GROUP BY b" 
000219            {1 c 1 b}
000220    2112.1  "SELECT ALL count(*), max(a) FROM t1 WHERE a!='b'
000221             GROUP BY b HAVING count(*)=1" {
000222      1 a 1 c
000223    }
000224    2112.2  "SELECT ALL count(*), max(a) FROM t1 
000225             WHERE 0 GROUP BY b HAVING count(*)=2" { }
000226  }
000227  
000228  
000229  # -- syntax diagram result-column
000230  #
000231  do_select_tests e_select-0.3 {
000232    1  "SELECT * FROM t1" {a one b two c three}
000233    2  "SELECT t1.* FROM t1" {a one b two c three}
000234    3  "SELECT 'x'||a||'x' FROM t1" {xax xbx xcx}
000235    4  "SELECT 'x'||a||'x' alias FROM t1" {xax xbx xcx}
000236    5  "SELECT 'x'||a||'x' AS alias FROM t1" {xax xbx xcx}
000237  }
000238  
000239  # -- syntax diagram join-source
000240  #
000241  # -- syntax diagram join-op
000242  #
000243  do_select_tests e_select-0.4 {
000244    1  "SELECT t1.rowid FROM t1" {1 2 3}
000245    2  "SELECT t1.rowid FROM t1,t2" {1 1 1 2 2 2 3 3 3}
000246    3  "SELECT t1.rowid FROM t1,t2,t3" {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3}
000247  
000248    4  "SELECT t1.rowid FROM t1" {1 2 3}
000249    5  "SELECT t1.rowid FROM t1 JOIN t2" {1 1 1 2 2 2 3 3 3}
000250    6  "SELECT t1.rowid FROM t1 JOIN t2 JOIN t3" 
000251       {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3}
000252  
000253    7  "SELECT t1.rowid FROM t1 NATURAL JOIN t3" {1 2}
000254    8  "SELECT t1.rowid FROM t1 NATURAL LEFT OUTER JOIN t3" {1 2 3}
000255    9  "SELECT t1.rowid FROM t1 NATURAL LEFT JOIN t3" {1 2 3}
000256    10 "SELECT t1.rowid FROM t1 NATURAL INNER JOIN t3" {1 2}
000257    11 "SELECT t1.rowid FROM t1 NATURAL CROSS JOIN t3" {1 2}
000258  
000259    12 "SELECT t1.rowid FROM t1 JOIN t3" {1 1 2 2 3 3}
000260    13 "SELECT t1.rowid FROM t1 LEFT OUTER JOIN t3" {1 1 2 2 3 3}
000261    14 "SELECT t1.rowid FROM t1 LEFT JOIN t3" {1 1 2 2 3 3}
000262    15 "SELECT t1.rowid FROM t1 INNER JOIN t3" {1 1 2 2 3 3}
000263    16 "SELECT t1.rowid FROM t1 CROSS JOIN t3" {1 1 2 2 3 3}
000264  }
000265  
000266  # -- syntax diagram compound-operator
000267  #
000268  do_select_tests e_select-0.5 {
000269    1  "SELECT rowid FROM t1 UNION ALL SELECT rowid+2 FROM t4" {1 2 3 3 4}
000270    2  "SELECT rowid FROM t1 UNION     SELECT rowid+2 FROM t4" {1 2 3 4}
000271    3  "SELECT rowid FROM t1 INTERSECT SELECT rowid+2 FROM t4" {3}
000272    4  "SELECT rowid FROM t1 EXCEPT    SELECT rowid+2 FROM t4" {1 2}
000273  }
000274  
000275  # -- syntax diagram ordering-term
000276  #
000277  do_select_tests e_select-0.6 {
000278    1  "SELECT b||a FROM t1 ORDER BY b||a"                  {onea threec twob}
000279    2  "SELECT b||a FROM t1 ORDER BY (b||a) COLLATE nocase" {onea threec twob}
000280    3  "SELECT b||a FROM t1 ORDER BY (b||a) ASC"            {onea threec twob}
000281    4  "SELECT b||a FROM t1 ORDER BY (b||a) DESC"           {twob threec onea}
000282  }
000283  
000284  # -- syntax diagram select-stmt
000285  #
000286  do_select_tests e_select-0.7 {
000287    1  "SELECT * FROM t1" {a one b two c three}
000288    2  "SELECT * FROM t1 ORDER BY b" {a one c three b two}
000289    3  "SELECT * FROM t1 ORDER BY b, a" {a one c three b two}
000290  
000291    4  "SELECT * FROM t1 LIMIT 10" {a one b two c three}
000292    5  "SELECT * FROM t1 LIMIT 10 OFFSET 5" {}
000293    6  "SELECT * FROM t1 LIMIT 10, 5" {}
000294  
000295    7  "SELECT * FROM t1 ORDER BY a LIMIT 10" {a one b two c three}
000296    8  "SELECT * FROM t1 ORDER BY b LIMIT 10 OFFSET 5" {}
000297    9  "SELECT * FROM t1 ORDER BY a,b LIMIT 10, 5" {}
000298  
000299    10  "SELECT * FROM t1 UNION SELECT b, a FROM t1" 
000300       {a one b two c three one a three c two b}
000301    11  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b" 
000302       {one a two b three c a one c three b two}
000303    12  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b, a" 
000304       {one a two b three c a one c three b two}
000305    13  "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10" 
000306       {a one b two c three one a three c two b}
000307    14  "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10 OFFSET 5" 
000308       {two b}
000309    15  "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10, 5" 
000310       {}
000311    16  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a LIMIT 10" 
000312       {a one b two c three one a three c two b}
000313    17  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b LIMIT 10 OFFSET 5" 
000314       {b two}
000315    18  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b LIMIT 10, 5" 
000316       {}
000317  }
000318  
000319  #-------------------------------------------------------------------------
000320  # The following tests focus on FROM clause (join) processing.
000321  #
000322  
000323  # EVIDENCE-OF: R-16074-54196 If the FROM clause is omitted from a simple
000324  # SELECT statement, then the input data is implicitly a single row zero
000325  # columns wide
000326  #
000327  do_select_tests e_select-1.1 {
000328    1 "SELECT 'abc'"            {abc}
000329    2 "SELECT 'abc' WHERE NULL" {}
000330    3 "SELECT NULL"             {{}}
000331    4 "SELECT count(*)"         {1}
000332    5 "SELECT count(*) WHERE 0" {0}
000333    6 "SELECT count(*) WHERE 1" {1}
000334  }
000335  
000336  # EVIDENCE-OF: R-45424-07352 If there is only a single table or subquery
000337  # in the FROM clause, then the input data used by the SELECT statement
000338  # is the contents of the named table.
000339  #
000340  #   The results of the SELECT queries suggest that they are operating on the
000341  #   contents of the table 'xx'.
000342  #
000343  do_execsql_test e_select-1.2.0 {
000344    CREATE TABLE xx(x, y);
000345    INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2');
000346    INSERT INTO xx VALUES(NULL, -16.87);
000347    INSERT INTO xx VALUES(-17.89, 'linguistically');
000348  } {}
000349  do_select_tests e_select-1.2 {
000350    1  "SELECT quote(x), quote(y) FROM xx" {
000351       'IiJlsIPepMuAhU' X'10B00B897A15BAA02E3F98DCE8F2' 
000352       NULL             -16.87                          
000353       -17.89           'linguistically'                
000354    }
000355  
000356    2  "SELECT count(*), count(x), count(y) FROM xx" {3 2 3}
000357    3  "SELECT sum(x), sum(y) FROM xx"               {-17.89 -16.87}
000358  }
000359  
000360  # EVIDENCE-OF: R-28355-09804 If there is more than one table or subquery
000361  # in FROM clause then the contents of all tables and/or subqueries are
000362  # joined into a single dataset for the simple SELECT statement to
000363  # operate on.
000364  #
000365  #   There are more detailed tests for subsequent requirements that add 
000366  #   more detail to this idea. We just add a single test that shows that
000367  #   data is coming from each of the three tables following the FROM clause
000368  #   here to show that the statement, vague as it is, is not incorrect.
000369  #
000370  do_select_tests e_select-1.3 {
000371    1 "SELECT * FROM t1, t2, t3" {
000372        a one a I a 1 a one a I b 2 a one b II a 1 
000373        a one b II b 2 a one c III a 1 a one c III b 2 
000374        b two a I a 1 b two a I b 2 b two b II a 1 
000375        b two b II b 2 b two c III a 1 b two c III b 2 
000376        c three a I a 1 c three a I b 2 c three b II a 1 
000377        c three b II b 2 c three c III a 1 c three c III b 2
000378    }
000379  }
000380  
000381  #
000382  # The following block of tests - e_select-1.4.* - test that the description
000383  # of cartesian joins in the SELECT documentation is consistent with SQLite.
000384  # In doing so, we test the following three requirements as a side-effect:
000385  #
000386  # EVIDENCE-OF: R-49872-03192 If the join-operator is "CROSS JOIN",
000387  # "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING
000388  # clause, then the result of the join is simply the cartesian product of
000389  # the left and right-hand datasets.
000390  #
000391  #    The tests are built on this assertion. Really, they test that the output
000392  #    of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result
000393  #    of calculating the cartesian product of the left and right-hand datasets. 
000394  #
000395  # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
000396  # JOIN", "JOIN" and "," join operators.
000397  #
000398  # EVIDENCE-OF: R-25071-21202 The "CROSS JOIN" join operator produces the
000399  # same result as the "INNER JOIN", "JOIN" and "," operators
000400  #
000401  #    All tests are run 4 times, with the only difference in each run being
000402  #    which of the 4 equivalent cartesian product join operators are used.
000403  #    Since the output data is the same in all cases, we consider that this
000404  #    qualifies as testing the two statements above.
000405  #
000406  do_execsql_test e_select-1.4.0 {
000407    CREATE TABLE x1(a, b);
000408    CREATE TABLE x2(c, d, e);
000409    CREATE TABLE x3(f, g, h, i);
000410  
000411    -- x1: 3 rows, 2 columns
000412    INSERT INTO x1 VALUES(24, 'converging');
000413    INSERT INTO x1 VALUES(NULL, X'CB71');
000414    INSERT INTO x1 VALUES('blonds', 'proprietary');
000415  
000416    -- x2: 2 rows, 3 columns
000417    INSERT INTO x2 VALUES(-60.06, NULL, NULL);
000418    INSERT INTO x2 VALUES(-58, NULL, 1.21);
000419  
000420    -- x3: 5 rows, 4 columns
000421    INSERT INTO x3 VALUES(-39.24, NULL, 'encompass', -1);
000422    INSERT INTO x3 VALUES('presenting', 51, 'reformation', 'dignified');
000423    INSERT INTO x3 VALUES('conducting', -87.24, 37.56, NULL);
000424    INSERT INTO x3 VALUES('coldest', -96, 'dramatists', 82.3);
000425    INSERT INTO x3 VALUES('alerting', NULL, -93.79, NULL);
000426  } {}
000427  
000428  # EVIDENCE-OF: R-59089-25828 The columns of the cartesian product
000429  # dataset are, in order, all the columns of the left-hand dataset
000430  # followed by all the columns of the right-hand dataset.
000431  #
000432  do_join_test e_select-1.4.1.1 {
000433    SELECT * FROM x1 %JOIN% x2 LIMIT 1
000434  } [concat {24 converging} {-60.06 {} {}}]
000435  
000436  do_join_test e_select-1.4.1.2 {
000437    SELECT * FROM x2 %JOIN% x1 LIMIT 1
000438  } [concat {-60.06 {} {}} {24 converging}]
000439  
000440  do_join_test e_select-1.4.1.3 {
000441    SELECT * FROM x3 %JOIN% x2 LIMIT 1
000442  } [concat {-39.24 {} encompass -1} {-60.06 {} {}}]
000443  
000444  do_join_test e_select-1.4.1.4 {
000445    SELECT * FROM x2 %JOIN% x3 LIMIT 1
000446  } [concat {-60.06 {} {}} {-39.24 {} encompass -1}]
000447  
000448  # EVIDENCE-OF: R-44414-54710 There is a row in the cartesian product
000449  # dataset formed by combining each unique combination of a row from the
000450  # left-hand and right-hand datasets.
000451  #
000452  do_join_test e_select-1.4.2.1 {
000453    SELECT * FROM x2 %JOIN% x3 ORDER BY +c, +f
000454  } [list -60.06 {} {}      -39.24 {} encompass -1                 \
000455          -60.06 {} {}      alerting {} -93.79 {}                  \
000456          -60.06 {} {}      coldest -96 dramatists 82.3            \
000457          -60.06 {} {}      conducting -87.24 37.56 {}             \
000458          -60.06 {} {}      presenting 51 reformation dignified    \
000459          -58 {} 1.21       -39.24 {} encompass -1                 \
000460          -58 {} 1.21       alerting {} -93.79 {}                  \
000461          -58 {} 1.21       coldest -96 dramatists 82.3            \
000462          -58 {} 1.21       conducting -87.24 37.56 {}             \
000463          -58 {} 1.21       presenting 51 reformation dignified    \
000464  ]
000465  # TODO: Come back and add a few more like the above.
000466  
000467  # EVIDENCE-OF: R-18439-38548 In other words, if the left-hand dataset
000468  # consists of Nleft rows of Mleft columns, and the right-hand dataset of
000469  # Nright rows of Mright columns, then the cartesian product is a dataset
000470  # of Nleft&times;Nright rows, each containing Mleft+Mright columns.
000471  #
000472  # x1, x2    (Nlhs=3, Nrhs=2)   (Mlhs=2, Mrhs=3)
000473  do_join_test e_select-1.4.3.1 { 
000474    SELECT count(*) FROM x1 %JOIN% x2 
000475  } [expr 3*2]
000476  do_test e_select-1.4.3.2 { 
000477    expr {[llength [execsql {SELECT * FROM x1, x2}]] / 6}
000478  } [expr 2+3]
000479  
000480  # x2, x3    (Nlhs=2, Nrhs=5)   (Mlhs=3, Mrhs=4)
000481  do_join_test e_select-1.4.3.3 { 
000482    SELECT count(*) FROM x2 %JOIN% x3 
000483  } [expr 2*5]
000484  do_test e_select-1.4.3.4 { 
000485    expr {[llength [execsql {SELECT * FROM x2 JOIN x3}]] / 10}
000486  } [expr 3+4]
000487  
000488  # x3, x1    (Nlhs=5, Nrhs=3)   (Mlhs=4, Mrhs=2)
000489  do_join_test e_select-1.4.3.5 { 
000490    SELECT count(*) FROM x3 %JOIN% x1 
000491  } [expr 5*3]
000492  do_test e_select-1.4.3.6 { 
000493    expr {[llength [execsql {SELECT * FROM x3 CROSS JOIN x1}]] / 15}
000494  } [expr 4+2]
000495  
000496  # x3, x3    (Nlhs=5, Nrhs=5)   (Mlhs=4, Mrhs=4)
000497  do_join_test e_select-1.4.3.7 { 
000498    SELECT count(*) FROM x3 %JOIN% x3 
000499  } [expr 5*5]
000500  do_test e_select-1.4.3.8 { 
000501    expr {[llength [execsql {SELECT * FROM x3 INNER JOIN x3 AS x4}]] / 25}
000502  } [expr 4+4]
000503  
000504  # Some extra cartesian product tests using tables t1 and t2.
000505  #
000506  do_execsql_test e_select-1.4.4.1 { SELECT * FROM t1, t2 } $t1_cross_t2
000507  do_execsql_test e_select-1.4.4.2 { SELECT * FROM t1 AS x, t1 AS y} $t1_cross_t1
000508  
000509  do_select_tests e_select-1.4.5 [list                                   \
000510      1 { SELECT * FROM t1 CROSS JOIN t2 }           $t1_cross_t2        \
000511      2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1        \
000512      3 { SELECT * FROM t1 INNER JOIN t2 }           $t1_cross_t2        \
000513      4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1        \
000514  ]
000515  
000516  # EVIDENCE-OF: R-38465-03616 If there is an ON clause then the ON
000517  # expression is evaluated for each row of the cartesian product as a
000518  # boolean expression. Only rows for which the expression evaluates to
000519  # true are included from the dataset.
000520  #
000521  foreach {tn select res} [list                                              \
000522      1 { SELECT * FROM t1 %JOIN% t2 ON (1) }       $t1_cross_t2             \
000523      2 { SELECT * FROM t1 %JOIN% t2 ON (0) }       [list]                   \
000524      3 { SELECT * FROM t1 %JOIN% t2 ON (NULL) }    [list]                   \
000525      4 { SELECT * FROM t1 %JOIN% t2 ON ('abc') }   [list]                   \
000526      5 { SELECT * FROM t1 %JOIN% t2 ON ('1ab') }   $t1_cross_t2             \
000527      6 { SELECT * FROM t1 %JOIN% t2 ON (0.9) }     $t1_cross_t2             \
000528      7 { SELECT * FROM t1 %JOIN% t2 ON ('0.9') }   $t1_cross_t2             \
000529      8 { SELECT * FROM t1 %JOIN% t2 ON (0.0) }     [list]                   \
000530                                                                             \
000531      9 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = t2.a) }             \
000532        {one I two II three III}                                             \
000533     10 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = 'a') }              \
000534        {one I one II one III}                                               \
000535     11 { SELECT t1.b, t2.b 
000536          FROM t1 %JOIN% t2 ON (CASE WHEN t1.a = 'a' THEN NULL ELSE 1 END) } \
000537        {two I two II two III three I three II three III}                    \
000538  ] {
000539    do_join_test e_select-1.3.$tn $select $res
000540  }
000541  
000542  # EVIDENCE-OF: R-49933-05137 If there is a USING clause then each of the
000543  # column names specified must exist in the datasets to both the left and
000544  # right of the join-operator.
000545  #
000546  do_select_tests e_select-1.4 -error {
000547    cannot join using column %s - column not present in both tables
000548  } {
000549    1 { SELECT * FROM t1, t3 USING (b) }   "b"
000550    2 { SELECT * FROM t3, t1 USING (c) }   "c"
000551    3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) }   "a"
000552  } 
000553  
000554  # EVIDENCE-OF: R-22776-52830 For each pair of named columns, the
000555  # expression "lhs.X = rhs.X" is evaluated for each row of the cartesian
000556  # product as a boolean expression. Only rows for which all such
000557  # expressions evaluates to true are included from the result set.
000558  #
000559  do_select_tests e_select-1.5 {
000560    1 { SELECT * FROM t1, t3 USING (a)   }  {a one 1 b two 2}
000561    2 { SELECT * FROM t3, t4 USING (a,c) }  {b 2}
000562  } 
000563  
000564  # EVIDENCE-OF: R-54046-48600 When comparing values as a result of a
000565  # USING clause, the normal rules for handling affinities, collation
000566  # sequences and NULL values in comparisons apply.
000567  #
000568  # EVIDENCE-OF: R-38422-04402 The column from the dataset on the
000569  # left-hand side of the join-operator is considered to be on the
000570  # left-hand side of the comparison operator (=) for the purposes of
000571  # collation sequence and affinity precedence.
000572  #
000573  do_execsql_test e_select-1.6.0 {
000574    CREATE TABLE t5(a COLLATE nocase, b COLLATE binary);
000575    INSERT INTO t5 VALUES('AA', 'cc');
000576    INSERT INTO t5 VALUES('BB', 'dd');
000577    INSERT INTO t5 VALUES(NULL, NULL);
000578    CREATE TABLE t6(a COLLATE binary, b COLLATE nocase);
000579    INSERT INTO t6 VALUES('aa', 'cc');
000580    INSERT INTO t6 VALUES('bb', 'DD');
000581    INSERT INTO t6 VALUES(NULL, NULL);
000582  } {}
000583  foreach {tn select res} {
000584    1 { SELECT * FROM t5 %JOIN% t6 USING (a) } {AA cc cc BB dd DD}
000585    2 { SELECT * FROM t6 %JOIN% t5 USING (a) } {}
000586    3 { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) %JOIN% t5 USING (a) } 
000587      {aa cc cc bb DD dd}
000588    4 { SELECT * FROM t5 %JOIN% t6 USING (a,b) } {AA cc}
000589    5 { SELECT * FROM t6 %JOIN% t5 USING (a,b) } {}
000590  } {
000591    do_join_test e_select-1.6.$tn $select $res
000592  }
000593  
000594  # EVIDENCE-OF: R-57047-10461 For each pair of columns identified by a
000595  # USING clause, the column from the right-hand dataset is omitted from
000596  # the joined dataset.
000597  #
000598  # EVIDENCE-OF: R-56132-15700 This is the only difference between a USING
000599  # clause and its equivalent ON constraint.
000600  #
000601  foreach {tn select res} {
000602    1a { SELECT * FROM t1 %JOIN% t2 USING (a)      } 
000603       {a one I b two II c three III}
000604    1b { SELECT * FROM t1 %JOIN% t2 ON (t1.a=t2.a) }
000605       {a one a I b two b II c three c III}
000606  
000607    2a { SELECT * FROM t3 %JOIN% t4 USING (a)      }  
000608       {a 1 {} b 2 2}
000609    2b { SELECT * FROM t3 %JOIN% t4 ON (t3.a=t4.a) } 
000610       {a 1 a {} b 2 b 2}
000611  
000612    3a { SELECT * FROM t3 %JOIN% t4 USING (a,c)                  } {b 2}
000613    3b { SELECT * FROM t3 %JOIN% t4 ON (t3.a=t4.a AND t3.c=t4.c) } {b 2 b 2}
000614  
000615    4a { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x 
000616         %JOIN% t5 USING (a) } 
000617       {aa cc cc bb DD dd}
000618    4b { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x
000619         %JOIN% t5 ON (x.a=t5.a) } 
000620       {aa cc AA cc bb DD BB dd}
000621  } {
000622    do_join_test e_select-1.7.$tn $select $res
000623  }
000624  # EVIDENCE-OF: R-42531-52874 If the join-operator is a "LEFT JOIN" or
000625  # "LEFT OUTER JOIN", then after the ON or USING filtering clauses have
000626  # been applied, an extra row is added to the output for each row in the
000627  # original left-hand input dataset that corresponds to no rows at all in
000628  # the composite dataset (if any).
000629  #
000630  do_execsql_test e_select-1.8.0 {
000631    CREATE TABLE t7(a, b, c);
000632    CREATE TABLE t8(a, d, e);
000633  
000634    INSERT INTO t7 VALUES('x', 'ex',  24);
000635    INSERT INTO t7 VALUES('y', 'why', 25);
000636  
000637    INSERT INTO t8 VALUES('x', 'abc', 24);
000638    INSERT INTO t8 VALUES('z', 'ghi', 26);
000639  } {}
000640  
000641  do_select_tests e_select-1.8 {
000642    1a "SELECT count(*) FROM t7 JOIN t8 ON (t7.a=t8.a)" {1}
000643    1b "SELECT count(*) FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" {2}
000644    2a "SELECT count(*) FROM t7 JOIN t8 USING (a)" {1}
000645    2b "SELECT count(*) FROM t7 LEFT JOIN t8 USING (a)" {2}
000646  }
000647  
000648  
000649  # EVIDENCE-OF: R-15607-52988 The added rows contain NULL values in the
000650  # columns that would normally contain values copied from the right-hand
000651  # input dataset.
000652  #
000653  do_select_tests e_select-1.9 {
000654    1a "SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)" {x ex 24 x abc 24}
000655    1b "SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" 
000656       {x ex 24 x abc 24 y why 25 {} {} {}}
000657    2a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24}
000658    2b "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}}
000659  }
000660  
000661  # EVIDENCE-OF: R-04932-55942 If the NATURAL keyword is in the
000662  # join-operator then an implicit USING clause is added to the
000663  # join-constraints. The implicit USING clause contains each of the
000664  # column names that appear in both the left and right-hand input
000665  # datasets.
000666  #
000667  do_select_tests e_select-1-10 {
000668    1a "SELECT * FROM t7 JOIN t8 USING (a)"        {x ex 24 abc 24}
000669    1b "SELECT * FROM t7 NATURAL JOIN t8"          {x ex 24 abc 24}
000670  
000671    2a "SELECT * FROM t8 JOIN t7 USING (a)"        {x abc 24 ex 24}
000672    2b "SELECT * FROM t8 NATURAL JOIN t7"          {x abc 24 ex 24}
000673  
000674    3a "SELECT * FROM t7 LEFT JOIN t8 USING (a)"   {x ex 24 abc 24 y why 25 {} {}}
000675    3b "SELECT * FROM t7 NATURAL LEFT JOIN t8"     {x ex 24 abc 24 y why 25 {} {}}
000676  
000677    4a "SELECT * FROM t8 LEFT JOIN t7 USING (a)"   {x abc 24 ex 24 z ghi 26 {} {}}
000678    4b "SELECT * FROM t8 NATURAL LEFT JOIN t7"     {x abc 24 ex 24 z ghi 26 {} {}}
000679  
000680    5a "SELECT * FROM t3 JOIN t4 USING (a,c)"      {b 2}
000681    5b "SELECT * FROM t3 NATURAL JOIN t4"          {b 2}
000682  
000683    6a "SELECT * FROM t3 LEFT JOIN t4 USING (a,c)" {a 1 b 2}
000684    6b "SELECT * FROM t3 NATURAL LEFT JOIN t4"     {a 1 b 2}
000685  } 
000686  
000687  # EVIDENCE-OF: R-49566-01570 If the left and right-hand input datasets
000688  # feature no common column names, then the NATURAL keyword has no effect
000689  # on the results of the join.
000690  #
000691  do_execsql_test e_select-1.11.0 {
000692    CREATE TABLE t10(x, y);
000693    INSERT INTO t10 VALUES(1, 'true');
000694    INSERT INTO t10 VALUES(0, 'false');
000695  } {}
000696  do_select_tests e_select-1-11 {
000697    1a "SELECT a, x FROM t1 CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0}
000698    1b "SELECT a, x FROM t1 NATURAL CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0}
000699  }
000700  
000701  # EVIDENCE-OF: R-39625-59133 A USING or ON clause may not be added to a
000702  # join that specifies the NATURAL keyword.
000703  #
000704  foreach {tn sql} {
000705    1 {SELECT * FROM t1 NATURAL LEFT JOIN t2 USING (a)}
000706    2 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (t1.a=t2.a)}
000707    3 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (45)}
000708  } {
000709    do_catchsql_test e_select-1.12.$tn "
000710      $sql
000711    " {1 {a NATURAL join may not have an ON or USING clause}}
000712  }
000713  
000714  #-------------------------------------------------------------------------
000715  # The next block of tests - e_select-3.* - concentrate on verifying 
000716  # statements made regarding WHERE clause processing.
000717  #
000718  drop_all_tables
000719  do_execsql_test e_select-3.0 {
000720    CREATE TABLE x1(k, x, y, z);
000721    INSERT INTO x1 VALUES(1, 'relinquished', 'aphasia', 78.43);
000722    INSERT INTO x1 VALUES(2, X'A8E8D66F',    X'07CF',   -81);
000723    INSERT INTO x1 VALUES(3, -22,            -27.57,    NULL);
000724    INSERT INTO x1 VALUES(4, NULL,           'bygone',  'picky');
000725    INSERT INTO x1 VALUES(5, NULL,           96.28,     NULL);
000726    INSERT INTO x1 VALUES(6, 0,              1,         2);
000727  
000728    CREATE TABLE x2(k, x, y2);
000729    INSERT INTO x2 VALUES(1, 50, X'B82838');
000730    INSERT INTO x2 VALUES(5, 84.79, 65.88);
000731    INSERT INTO x2 VALUES(3, -22, X'0E1BE452A393');
000732    INSERT INTO x2 VALUES(7, 'mistrusted', 'standardized');
000733  } {}
000734  
000735  # EVIDENCE-OF: R-60775-64916 If a WHERE clause is specified, the WHERE
000736  # expression is evaluated for each row in the input data as a boolean
000737  # expression. Only rows for which the WHERE clause expression evaluates
000738  # to true are included from the dataset before continuing.
000739  #
000740  do_execsql_test e_select-3.1.1 { SELECT k FROM x1 WHERE x }         {3}
000741  do_execsql_test e_select-3.1.2 { SELECT k FROM x1 WHERE y }         {3 5 6}
000742  do_execsql_test e_select-3.1.3 { SELECT k FROM x1 WHERE z }         {1 2 6}
000743  do_execsql_test e_select-3.1.4 { SELECT k FROM x1 WHERE '1'||z    } {1 2 4 6}
000744  do_execsql_test e_select-3.1.5 { SELECT k FROM x1 WHERE x IS NULL } {4 5}
000745  do_execsql_test e_select-3.1.6 { SELECT k FROM x1 WHERE z - 78.43 } {2 4 6}
000746  
000747  do_execsql_test e_select-3.2.1a {
000748    SELECT k FROM x1 LEFT JOIN x2 USING(k)
000749  } {1 2 3 4 5 6}
000750  do_execsql_test e_select-3.2.1b {
000751    SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k ORDER BY +k
000752  } {1 3 5}
000753  do_execsql_test e_select-3.2.2 {
000754    SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k IS NULL
000755  } {2 4 6}
000756  
000757  do_execsql_test e_select-3.2.3 {
000758    SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k
000759  } {3}
000760  do_execsql_test e_select-3.2.4 {
000761    SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k-3
000762  } {}
000763  
000764  #-------------------------------------------------------------------------
000765  # Tests below this point are focused on verifying the testable statements
000766  # related to caculating the result rows of a simple SELECT statement.
000767  #
000768  
000769  drop_all_tables
000770  do_execsql_test e_select-4.0 {
000771    CREATE TABLE z1(a, b, c);
000772    CREATE TABLE z2(d, e);
000773    CREATE TABLE z3(a, b);
000774  
000775    INSERT INTO z1 VALUES(51.65, -59.58, 'belfries');
000776    INSERT INTO z1 VALUES(-5, NULL, 75);
000777    INSERT INTO z1 VALUES(-2.2, -23.18, 'suiters');
000778    INSERT INTO z1 VALUES(NULL, 67, 'quartets');
000779    INSERT INTO z1 VALUES(-1.04, -32.3, 'aspen');
000780    INSERT INTO z1 VALUES(63, 'born', -26);
000781  
000782    INSERT INTO z2 VALUES(NULL, 21);
000783    INSERT INTO z2 VALUES(36, 6);
000784  
000785    INSERT INTO z3 VALUES('subsistence', 'gauze');
000786    INSERT INTO z3 VALUES(49.17, -67);
000787  } {}
000788  
000789  # EVIDENCE-OF: R-36327-17224 If a result expression is the special
000790  # expression "*" then all columns in the input data are substituted for
000791  # that one expression.
000792  #
000793  # EVIDENCE-OF: R-43693-30522 If the expression is the alias of a table
000794  # or subquery in the FROM clause followed by ".*" then all columns from
000795  # the named table or subquery are substituted for the single expression.
000796  #
000797  do_select_tests e_select-4.1 {
000798    1  "SELECT * FROM z1 LIMIT 1"             {51.65 -59.58 belfries}
000799    2  "SELECT * FROM z1,z2 LIMIT 1"          {51.65 -59.58 belfries {} 21}
000800    3  "SELECT z1.* FROM z1,z2 LIMIT 1"       {51.65 -59.58 belfries}
000801    4  "SELECT z2.* FROM z1,z2 LIMIT 1"       {{} 21}
000802    5  "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries}
000803  
000804    6  "SELECT count(*), * FROM z1"           {6 63 born -26}
000805    7  "SELECT max(a), * FROM z1"             {63 63 born -26}
000806    8  "SELECT *, min(a) FROM z1"             {-5 {} 75 -5}
000807  
000808    9  "SELECT *,* FROM z1,z2 LIMIT 1" {        
000809       51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21
000810    }
000811    10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" {        
000812       51.65 -59.58 belfries 51.65 -59.58 belfries
000813    }
000814  }
000815  
000816  # EVIDENCE-OF: R-38023-18396 It is an error to use a "*" or "alias.*"
000817  # expression in any context other than a result expression list.
000818  #
000819  # EVIDENCE-OF: R-44324-41166 It is also an error to use a "*" or
000820  # "alias.*" expression in a simple SELECT query that does not have a
000821  # FROM clause.
000822  #
000823  foreach {tn select err} {
000824    1.1  "SELECT a, b, c FROM z1 WHERE *"    {near "*": syntax error}
000825    1.2  "SELECT a, b, c FROM z1 GROUP BY *" {near "*": syntax error}
000826    1.3  "SELECT 1 + * FROM z1"              {near "*": syntax error}
000827    1.4  "SELECT * + 1 FROM z1"              {near "+": syntax error}
000828  
000829    2.1 "SELECT *" {no tables specified}
000830    2.2 "SELECT * WHERE 1" {no tables specified}
000831    2.3 "SELECT * WHERE 0" {no tables specified}
000832    2.4 "SELECT count(*), *" {no tables specified}
000833  } {
000834    do_catchsql_test e_select-4.2.$tn $select [list 1 $err]
000835  }
000836  
000837  # EVIDENCE-OF: R-08669-22397 The number of columns in the rows returned
000838  # by a simple SELECT statement is equal to the number of expressions in
000839  # the result expression list after substitution of * and alias.*
000840  # expressions.
000841  #
000842  foreach {tn select nCol} {
000843    1   "SELECT * FROM z1"   3
000844    2   "SELECT * FROM z1 NATURAL JOIN z3"            3
000845    3   "SELECT z1.* FROM z1 NATURAL JOIN z3"         3
000846    4   "SELECT z3.* FROM z1 NATURAL JOIN z3"         2
000847    5   "SELECT z1.*, z3.* FROM z1 NATURAL JOIN z3"   5
000848    6   "SELECT 1, 2, z1.* FROM z1"                   5
000849    7   "SELECT a, *, b, c FROM z1"                   6
000850  } {
000851    set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY]
000852    do_test e_select-4.3.$tn { sqlite3_column_count $::stmt } $nCol
000853    sqlite3_finalize $::stmt
000854  }
000855  
000856  
000857  
000858  # In lang_select.html, a non-aggregate query is defined as any simple SELECT
000859  # that has no GROUP BY clause and no aggregate expressions in the result
000860  # expression list. Other queries are aggregate queries. Test cases
000861  # e_select-4.4.* through e_select-4.12.*, inclusive, which test the part of
000862  # simple SELECT that is different for aggregate and non-aggregate queries
000863  # verify (in a way) that these definitions are consistent:
000864  #
000865  # EVIDENCE-OF: R-20637-43463 A simple SELECT statement is an aggregate
000866  # query if it contains either a GROUP BY clause or one or more aggregate
000867  # functions in the result-set.
000868  #
000869  # EVIDENCE-OF: R-23155-55597 Otherwise, if a simple SELECT contains no
000870  # aggregate functions or a GROUP BY clause, it is a non-aggregate query.
000871  #
000872  
000873  # EVIDENCE-OF: R-44050-47362 If the SELECT statement is a non-aggregate
000874  # query, then each expression in the result expression list is evaluated
000875  # for each row in the dataset filtered by the WHERE clause.
000876  #
000877  do_select_tests e_select-4.4 {
000878    1 "SELECT a, b FROM z1"
000879      {51.65 -59.58 -5 {} -2.2 -23.18 {} 67 -1.04 -32.3 63 born}
000880  
000881    2 "SELECT a IS NULL, b+1, * FROM z1" {
000882          0 -58.58   51.65 -59.58 belfries
000883          0 {}       -5 {} 75            
000884          0 -22.18   -2.2 -23.18 suiters
000885          1 68       {} 67 quartets    
000886          0 -31.3    -1.04 -32.3 aspen
000887          0 1        63 born -26
000888    }
000889  
000890    3 "SELECT 32*32, d||e FROM z2" {1024 {} 1024 366}
000891  }
000892  
000893  
000894  # Test cases e_select-4.5.* and e_select-4.6.* together show that:
000895  #
000896  # EVIDENCE-OF: R-51988-01124 The single row of result-set data created
000897  # by evaluating the aggregate and non-aggregate expressions in the
000898  # result-set forms the result of an aggregate query without a GROUP BY
000899  # clause.
000900  #
000901  
000902  # EVIDENCE-OF: R-57629-25253 If the SELECT statement is an aggregate
000903  # query without a GROUP BY clause, then each aggregate expression in the
000904  # result-set is evaluated once across the entire dataset.
000905  #
000906  do_select_tests e_select-4.5 {
000907    1 "SELECT count(a), max(a), count(b), max(b) FROM z1"      {5 63 5 born}
000908    2 "SELECT count(*), max(1)"                                {1 1}
000909  
000910    3 "SELECT sum(b+1) FROM z1 NATURAL LEFT JOIN z3"           {-43.06}
000911    4 "SELECT sum(b+2) FROM z1 NATURAL LEFT JOIN z3"           {-38.06}
000912    5 "SELECT sum(b IS NOT NULL) FROM z1 NATURAL LEFT JOIN z3" {5}
000913  }
000914  
000915  # EVIDENCE-OF: R-26684-40576 Each non-aggregate expression in the
000916  # result-set is evaluated once for an arbitrarily selected row of the
000917  # dataset.
000918  #
000919  # EVIDENCE-OF: R-27994-60376 The same arbitrarily selected row is used
000920  # for each non-aggregate expression.
000921  #
000922  #   Note: The results of many of the queries in this block of tests are
000923  #   technically undefined, as the documentation does not specify which row
000924  #   SQLite will arbitrarily select to use for the evaluation of the
000925  #   non-aggregate expressions.
000926  #
000927  drop_all_tables
000928  do_execsql_test e_select-4.6.0 {
000929    CREATE TABLE a1(one PRIMARY KEY, two);
000930    INSERT INTO a1 VALUES(1, 1);
000931    INSERT INTO a1 VALUES(2, 3);
000932    INSERT INTO a1 VALUES(3, 6);
000933    INSERT INTO a1 VALUES(4, 10);
000934  
000935    CREATE TABLE a2(one PRIMARY KEY, three);
000936    INSERT INTO a2 VALUES(1, 1);
000937    INSERT INTO a2 VALUES(3, 2);
000938    INSERT INTO a2 VALUES(6, 3);
000939    INSERT INTO a2 VALUES(10, 4);
000940  } {}
000941  do_select_tests e_select-4.6 {
000942    1 "SELECT one, two, count(*) FROM a1"                        {4 10 4} 
000943    2 "SELECT one, two, count(*) FROM a1 WHERE one<3"            {2 3 2} 
000944    3 "SELECT one, two, count(*) FROM a1 WHERE one>3"            {4 10 1} 
000945    4 "SELECT *, count(*) FROM a1 JOIN a2"                       {4 10 10 4 16} 
000946    5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {3 6 2 3}
000947    6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {3 6 2 3}
000948    7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 3 6}
000949  }
000950  
000951  # EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then
000952  # each non-aggregate expression is evaluated against a row consisting
000953  # entirely of NULL values.
000954  #
000955  do_select_tests e_select-4.7 {
000956    1  "SELECT one, two, count(*) FROM a1 WHERE 0"           {{} {} 0}
000957    2  "SELECT sum(two), * FROM a1, a2 WHERE three>5"        {{} {} {} {} {}}
000958    3  "SELECT max(one) IS NULL, one IS NULL, two IS NULL FROM a1 WHERE two=7" {
000959      1 1 1
000960    }
000961  } 
000962  
000963  # EVIDENCE-OF: R-64138-28774 An aggregate query without a GROUP BY
000964  # clause always returns exactly one row of data, even if there are zero
000965  # rows of input data.
000966  #
000967  foreach {tn select} {
000968    8.1  "SELECT count(*) FROM a1"
000969    8.2  "SELECT count(*) FROM a1 WHERE 0"
000970    8.3  "SELECT count(*) FROM a1 WHERE 1"
000971    8.4  "SELECT max(a1.one)+min(two), a1.one, two, * FROM a1, a2 WHERE 1"
000972    8.5  "SELECT max(a1.one)+min(two), a1.one, two, * FROM a1, a2 WHERE 0"
000973  } {
000974    # Set $nRow to the number of rows returned by $select:
000975    set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY]
000976    set nRow 0
000977    while {"SQLITE_ROW" == [sqlite3_step $::stmt]} { incr nRow }
000978    set rc [sqlite3_finalize $::stmt]
000979  
000980    # Test that $nRow==1 and that statement execution was successful 
000981    # (rc==SQLITE_OK).
000982    do_test e_select-4.$tn [list list $rc $nRow] {SQLITE_OK 1}
000983  }
000984  
000985  drop_all_tables
000986  do_execsql_test e_select-4.9.0 {
000987    CREATE TABLE b1(one PRIMARY KEY, two);
000988    INSERT INTO b1 VALUES(1, 'o');
000989    INSERT INTO b1 VALUES(4, 'f');
000990    INSERT INTO b1 VALUES(3, 't');
000991    INSERT INTO b1 VALUES(2, 't');
000992    INSERT INTO b1 VALUES(5, 'f');
000993    INSERT INTO b1 VALUES(7, 's');
000994    INSERT INTO b1 VALUES(6, 's');
000995  
000996    CREATE TABLE b2(x, y);
000997    INSERT INTO b2 VALUES(NULL, 0);
000998    INSERT INTO b2 VALUES(NULL, 1);
000999    INSERT INTO b2 VALUES('xyz', 2);
001000    INSERT INTO b2 VALUES('abc', 3);
001001    INSERT INTO b2 VALUES('xyz', 4);
001002  
001003    CREATE TABLE b3(a COLLATE nocase, b COLLATE binary);
001004    INSERT INTO b3 VALUES('abc', 'abc');
001005    INSERT INTO b3 VALUES('aBC', 'aBC');
001006    INSERT INTO b3 VALUES('Def', 'Def');
001007    INSERT INTO b3 VALUES('dEF', 'dEF');
001008  } {}
001009  
001010  # EVIDENCE-OF: R-07284-35990 If the SELECT statement is an aggregate
001011  # query with a GROUP BY clause, then each of the expressions specified
001012  # as part of the GROUP BY clause is evaluated for each row of the
001013  # dataset. Each row is then assigned to a "group" based on the results;
001014  # rows for which the results of evaluating the GROUP BY expressions are
001015  # the same get assigned to the same group.
001016  #
001017  #   These tests also show that the following is not untrue:
001018  #
001019  # EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do
001020  # not have to be expressions that appear in the result.
001021  #
001022  do_select_tests e_select-4.9 {
001023    1  "SELECT group_concat(one), two FROM b1 GROUP BY two" {
001024      /#,# f   1 o   #,#   s #,# t/
001025    }
001026    2  "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
001027      1,2,3,4 10    5,6,7 18
001028    }
001029    3  "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {
001030      4  1,5    2,6   3,7
001031    }
001032    4  "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" {
001033      4,3,5,7,6    1,2
001034    }
001035  }
001036  
001037  # EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL
001038  # values are considered equal.
001039  #
001040  do_select_tests e_select-4.10 {
001041    1  "SELECT group_concat(y) FROM b2 GROUP BY x" {/#,#   3   #,#/}
001042    2  "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1}
001043  } 
001044  
001045  # EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation
001046  # sequence with which to compare text values apply when evaluating
001047  # expressions in a GROUP BY clause.
001048  #
001049  do_select_tests e_select-4.11 {
001050    1  "SELECT count(*) FROM b3 GROUP BY b"      {1 1 1 1}
001051    2  "SELECT count(*) FROM b3 GROUP BY a"      {2 2}
001052    3  "SELECT count(*) FROM b3 GROUP BY +b"     {1 1 1 1}
001053    4  "SELECT count(*) FROM b3 GROUP BY +a"     {2 2}
001054    5  "SELECT count(*) FROM b3 GROUP BY b||''"  {1 1 1 1}
001055    6  "SELECT count(*) FROM b3 GROUP BY a||''"  {1 1 1 1}
001056  }
001057  
001058  # EVIDENCE-OF: R-63573-50730 The expressions in a GROUP BY clause may
001059  # not be aggregate expressions.
001060  #
001061  foreach {tn select} {
001062    12.1  "SELECT * FROM b3 GROUP BY count(*)"
001063    12.2  "SELECT max(a) FROM b3 GROUP BY max(b)"
001064    12.3  "SELECT group_concat(a) FROM b3 GROUP BY a, max(b)"
001065  } {
001066    set res {1 {aggregate functions are not allowed in the GROUP BY clause}}
001067    do_catchsql_test e_select-4.$tn $select $res
001068  }
001069  
001070  # EVIDENCE-OF: R-31537-00101 If a HAVING clause is specified, it is
001071  # evaluated once for each group of rows as a boolean expression. If the
001072  # result of evaluating the HAVING clause is false, the group is
001073  # discarded.
001074  #
001075  #   This requirement is tested by all e_select-4.13.* tests.
001076  #
001077  # EVIDENCE-OF: R-04132-09474 If the HAVING clause is an aggregate
001078  # expression, it is evaluated across all rows in the group.
001079  #
001080  #   Tested by e_select-4.13.1.*
001081  #
001082  # EVIDENCE-OF: R-28262-47447 If a HAVING clause is a non-aggregate
001083  # expression, it is evaluated with respect to an arbitrarily selected
001084  # row from the group.
001085  #
001086  #   Tested by e_select-4.13.2.*
001087  #
001088  #   Tests in this block also show that this is not untrue:
001089  #
001090  # EVIDENCE-OF: R-55403-13450 The HAVING expression may refer to values,
001091  # even aggregate functions, that are not in the result.
001092  #
001093  do_execsql_test e_select-4.13.0 {
001094    CREATE TABLE c1(up, down);
001095    INSERT INTO c1 VALUES('x', 1);
001096    INSERT INTO c1 VALUES('x', 2);
001097    INSERT INTO c1 VALUES('x', 4);
001098    INSERT INTO c1 VALUES('x', 8);
001099    INSERT INTO c1 VALUES('y', 16);
001100    INSERT INTO c1 VALUES('y', 32);
001101  
001102    CREATE TABLE c2(i, j);
001103    INSERT INTO c2 VALUES(1, 0);
001104    INSERT INTO c2 VALUES(2, 1);
001105    INSERT INTO c2 VALUES(3, 3);
001106    INSERT INTO c2 VALUES(4, 6);
001107    INSERT INTO c2 VALUES(5, 10);
001108    INSERT INTO c2 VALUES(6, 15);
001109    INSERT INTO c2 VALUES(7, 21);
001110    INSERT INTO c2 VALUES(8, 28);
001111    INSERT INTO c2 VALUES(9, 36);
001112  
001113    CREATE TABLE c3(i PRIMARY KEY, k TEXT);
001114    INSERT INTO c3 VALUES(1,  'hydrogen');
001115    INSERT INTO c3 VALUES(2,  'helium');
001116    INSERT INTO c3 VALUES(3,  'lithium');
001117    INSERT INTO c3 VALUES(4,  'beryllium');
001118    INSERT INTO c3 VALUES(5,  'boron');
001119    INSERT INTO c3 VALUES(94, 'plutonium');
001120  } {}
001121  
001122  do_select_tests e_select-4.13 {
001123    1.1  "SELECT up FROM c1 GROUP BY up HAVING count(*)>3" {x}
001124    1.2  "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y}
001125    1.3  "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x}
001126    1.4  "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4}
001127  
001128    2.1  "SELECT up FROM c1 GROUP BY up HAVING down>10" {y}
001129    2.2  "SELECT up FROM c1 GROUP BY up HAVING up='y'"  {y}
001130  
001131    2.3  "SELECT i, j FROM c2 GROUP BY i>4 HAVING i>6"  {9 36}
001132  }
001133  
001134  # EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then
001135  # evaluated once for each group of rows.
001136  #
001137  # EVIDENCE-OF: R-53735-47017 If the expression is an aggregate
001138  # expression, it is evaluated across all rows in the group.
001139  #
001140  do_select_tests e_select-4.15 {
001141    1  "SELECT sum(down) FROM c1 GROUP BY up" {15 48}
001142    2  "SELECT sum(j), max(j) FROM c2 GROUP BY (i%3)"     {54 36 27 21 39 28}
001143    3  "SELECT sum(j), max(j) FROM c2 GROUP BY (j%2)"     {80 36 40 21}
001144    4  "SELECT 1+sum(j), max(j)+1 FROM c2 GROUP BY (j%2)" {81 37 41 22}
001145    5  "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2"
001146          {3 4.33 1 2.0}
001147  } 
001148  
001149  # EVIDENCE-OF: R-62913-19830 Otherwise, it is evaluated against a single
001150  # arbitrarily chosen row from within the group.
001151  #
001152  # EVIDENCE-OF: R-53924-08809 If there is more than one non-aggregate
001153  # expression in the result-set, then all such expressions are evaluated
001154  # for the same row.
001155  #
001156  do_select_tests e_select-4.15 {
001157    1  "SELECT i, j FROM c2 GROUP BY i%2"             {8 28   9 36}
001158    2  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {8 28}
001159    3  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36}
001160    4  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36}
001161    5  "SELECT count(*), i, k FROM c2 NATURAL JOIN c3 GROUP BY substr(k, 1, 1)"
001162          {2 5 boron   2 2 helium   1 3 lithium}
001163  } 
001164  
001165  # EVIDENCE-OF: R-19334-12811 Each group of input dataset rows
001166  # contributes a single row to the set of result rows.
001167  #
001168  # EVIDENCE-OF: R-02223-49279 Subject to filtering associated with the
001169  # DISTINCT keyword, the number of rows returned by an aggregate query
001170  # with a GROUP BY clause is the same as the number of groups of rows
001171  # produced by applying the GROUP BY and HAVING clauses to the filtered
001172  # input dataset.
001173  #
001174  do_select_tests e_select.4.16 -count {
001175    1  "SELECT i, j FROM c2 GROUP BY i%2"          2
001176    2  "SELECT i, j FROM c2 GROUP BY i"            9
001177    3  "SELECT i, j FROM c2 GROUP BY i HAVING i<5" 4
001178  } 
001179  
001180  #-------------------------------------------------------------------------
001181  # The following tests attempt to verify statements made regarding the ALL
001182  # and DISTINCT keywords.
001183  #
001184  drop_all_tables
001185  do_execsql_test e_select-5.1.0 {
001186    CREATE TABLE h1(a, b);
001187    INSERT INTO h1 VALUES(1, 'one');
001188    INSERT INTO h1 VALUES(1, 'I');
001189    INSERT INTO h1 VALUES(1, 'i');
001190    INSERT INTO h1 VALUES(4, 'four');
001191    INSERT INTO h1 VALUES(4, 'IV');
001192    INSERT INTO h1 VALUES(4, 'iv');
001193  
001194    CREATE TABLE h2(x COLLATE nocase);
001195    INSERT INTO h2 VALUES('One');
001196    INSERT INTO h2 VALUES('Two');
001197    INSERT INTO h2 VALUES('Three');
001198    INSERT INTO h2 VALUES('Four');
001199    INSERT INTO h2 VALUES('one');
001200    INSERT INTO h2 VALUES('two');
001201    INSERT INTO h2 VALUES('three');
001202    INSERT INTO h2 VALUES('four');
001203  
001204    CREATE TABLE h3(c, d);
001205    INSERT INTO h3 VALUES(1, NULL);
001206    INSERT INTO h3 VALUES(2, NULL);
001207    INSERT INTO h3 VALUES(3, NULL);
001208    INSERT INTO h3 VALUES(4, '2');
001209    INSERT INTO h3 VALUES(5, NULL);
001210    INSERT INTO h3 VALUES(6, '2,3');
001211    INSERT INTO h3 VALUES(7, NULL);
001212    INSERT INTO h3 VALUES(8, '2,4');
001213    INSERT INTO h3 VALUES(9, '3');
001214  } {}
001215  
001216  # EVIDENCE-OF: R-60770-10612 One of the ALL or DISTINCT keywords may
001217  # follow the SELECT keyword in a simple SELECT statement.
001218  #
001219  do_select_tests e_select-5.1 {
001220    1   "SELECT ALL a FROM h1"      {1 1 1 4 4 4}
001221    2   "SELECT DISTINCT a FROM h1" {1 4}
001222  }
001223  
001224  # EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then
001225  # the entire set of result rows are returned by the SELECT.
001226  #
001227  # EVIDENCE-OF: R-01256-01950 If neither ALL or DISTINCT are present,
001228  # then the behavior is as if ALL were specified.
001229  #
001230  # EVIDENCE-OF: R-14442-41305 If the simple SELECT is a SELECT DISTINCT,
001231  # then duplicate rows are removed from the set of result rows before it
001232  # is returned.
001233  #
001234  #   The three testable statements above are tested by e_select-5.2.*,
001235  #   5.3.* and 5.4.* respectively.
001236  #
001237  do_select_tests e_select-5 {
001238    3.1 "SELECT ALL x FROM h2" {One Two Three Four one two three four}
001239    3.2 "SELECT ALL x FROM h1, h2 ON (x=b)" {One one Four four}
001240  
001241    3.1 "SELECT x FROM h2" {One Two Three Four one two three four}
001242    3.2 "SELECT x FROM h1, h2 ON (x=b)" {One one Four four}
001243  
001244    4.1 "SELECT DISTINCT x FROM h2" {One Two Three Four}
001245    4.2 "SELECT DISTINCT x FROM h1, h2 ON (x=b)" {One Four}
001246  } 
001247  
001248  # EVIDENCE-OF: R-02054-15343 For the purposes of detecting duplicate
001249  # rows, two NULL values are considered to be equal.
001250  #
001251  do_select_tests e_select-5.5 {
001252    1  "SELECT DISTINCT d FROM h3" {{} 2 2,3 2,4 3}
001253  }
001254  
001255  # EVIDENCE-OF: R-47709-27231 The usual rules apply for selecting a
001256  # collation sequence to compare text values.
001257  #
001258  do_select_tests e_select-5.6 {
001259    1  "SELECT DISTINCT b FROM h1"                  {one I i four IV iv}
001260    2  "SELECT DISTINCT b COLLATE nocase FROM h1"   {one I four IV}
001261    3  "SELECT DISTINCT x FROM h2"                  {One Two Three Four}
001262    4  "SELECT DISTINCT x COLLATE binary FROM h2"   {
001263      One Two Three Four one two three four
001264    }
001265  }
001266  
001267  #-------------------------------------------------------------------------
001268  # The following tests - e_select-7.* - test that statements made to do
001269  # with compound SELECT statements are correct.
001270  #
001271  
001272  # EVIDENCE-OF: R-39368-64333 In a compound SELECT, all the constituent
001273  # SELECTs must return the same number of result columns.
001274  #
001275  #   All the other tests in this section use compound SELECTs created
001276  #   using component SELECTs that do return the same number of columns.
001277  #   So the tests here just show that it is an error to attempt otherwise.
001278  #
001279  drop_all_tables
001280  do_execsql_test e_select-7.1.0 {
001281    CREATE TABLE j1(a, b, c);
001282    CREATE TABLE j2(e, f);
001283    CREATE TABLE j3(g);
001284  } {}
001285  do_select_tests e_select-7.1 -error {
001286    SELECTs to the left and right of %s do not have the same number of result columns
001287  } {
001288    1   "SELECT a, b FROM j1    UNION ALL SELECT g FROM j3"    {{UNION ALL}}
001289    2   "SELECT *    FROM j1    UNION ALL SELECT * FROM j3"    {{UNION ALL}}
001290    3   "SELECT a, b FROM j1    UNION ALL SELECT g FROM j3"    {{UNION ALL}}
001291    4   "SELECT a, b FROM j1    UNION ALL SELECT * FROM j3,j2" {{UNION ALL}}
001292    5   "SELECT *    FROM j3,j2 UNION ALL SELECT a, b FROM j1" {{UNION ALL}}
001293  
001294    6   "SELECT a, b FROM j1    UNION SELECT g FROM j3"        {UNION}
001295    7   "SELECT *    FROM j1    UNION SELECT * FROM j3"        {UNION}
001296    8   "SELECT a, b FROM j1    UNION SELECT g FROM j3"        {UNION}
001297    9   "SELECT a, b FROM j1    UNION SELECT * FROM j3,j2"     {UNION}
001298    10  "SELECT *    FROM j3,j2 UNION SELECT a, b FROM j1"     {UNION}
001299  
001300    11  "SELECT a, b FROM j1    INTERSECT SELECT g FROM j3"    {INTERSECT}
001301    12  "SELECT *    FROM j1    INTERSECT SELECT * FROM j3"    {INTERSECT}
001302    13  "SELECT a, b FROM j1    INTERSECT SELECT g FROM j3"    {INTERSECT}
001303    14  "SELECT a, b FROM j1    INTERSECT SELECT * FROM j3,j2" {INTERSECT}
001304    15  "SELECT *    FROM j3,j2 INTERSECT SELECT a, b FROM j1" {INTERSECT}
001305  
001306    16  "SELECT a, b FROM j1    EXCEPT SELECT g FROM j3"       {EXCEPT}
001307    17  "SELECT *    FROM j1    EXCEPT SELECT * FROM j3"       {EXCEPT}
001308    18  "SELECT a, b FROM j1    EXCEPT SELECT g FROM j3"       {EXCEPT}
001309    19  "SELECT a, b FROM j1    EXCEPT SELECT * FROM j3,j2"    {EXCEPT}
001310    20  "SELECT *    FROM j3,j2 EXCEPT SELECT a, b FROM j1"    {EXCEPT}
001311  } 
001312  
001313  # EVIDENCE-OF: R-01450-11152 As the components of a compound SELECT must
001314  # be simple SELECT statements, they may not contain ORDER BY or LIMIT
001315  # clauses.
001316  # 
001317  foreach {tn select op1 op2} {
001318    1   "SELECT * FROM j1 ORDER BY a UNION ALL SELECT * FROM j2,j3" 
001319        {ORDER BY} {UNION ALL}
001320    2   "SELECT count(*) FROM j1 ORDER BY 1 UNION ALL SELECT max(e) FROM j2"
001321        {ORDER BY} {UNION ALL}
001322    3   "SELECT count(*), * FROM j1 ORDER BY 1,2,3 UNION ALL SELECT *,* FROM j2"
001323        {ORDER BY} {UNION ALL}
001324    4   "SELECT * FROM j1 LIMIT 10 UNION ALL SELECT * FROM j2,j3" 
001325        LIMIT {UNION ALL}
001326    5   "SELECT * FROM j1 LIMIT 10 OFFSET 5 UNION ALL SELECT * FROM j2,j3" 
001327        LIMIT {UNION ALL}
001328    6   "SELECT a FROM j1 LIMIT (SELECT e FROM j2) UNION ALL SELECT g FROM j2,j3" 
001329        LIMIT {UNION ALL}
001330  
001331    7   "SELECT * FROM j1 ORDER BY a UNION SELECT * FROM j2,j3" 
001332        {ORDER BY} {UNION}
001333    8   "SELECT count(*) FROM j1 ORDER BY 1 UNION SELECT max(e) FROM j2"
001334        {ORDER BY} {UNION}
001335    9   "SELECT count(*), * FROM j1 ORDER BY 1,2,3 UNION SELECT *,* FROM j2"
001336        {ORDER BY} {UNION}
001337    10  "SELECT * FROM j1 LIMIT 10 UNION SELECT * FROM j2,j3" 
001338        LIMIT {UNION}
001339    11  "SELECT * FROM j1 LIMIT 10 OFFSET 5 UNION SELECT * FROM j2,j3" 
001340        LIMIT {UNION}
001341    12  "SELECT a FROM j1 LIMIT (SELECT e FROM j2) UNION SELECT g FROM j2,j3" 
001342        LIMIT {UNION}
001343  
001344    13  "SELECT * FROM j1 ORDER BY a EXCEPT SELECT * FROM j2,j3" 
001345        {ORDER BY} {EXCEPT}
001346    14  "SELECT count(*) FROM j1 ORDER BY 1 EXCEPT SELECT max(e) FROM j2"
001347        {ORDER BY} {EXCEPT}
001348    15  "SELECT count(*), * FROM j1 ORDER BY 1,2,3 EXCEPT SELECT *,* FROM j2"
001349        {ORDER BY} {EXCEPT}
001350    16  "SELECT * FROM j1 LIMIT 10 EXCEPT SELECT * FROM j2,j3" 
001351        LIMIT {EXCEPT}
001352    17  "SELECT * FROM j1 LIMIT 10 OFFSET 5 EXCEPT SELECT * FROM j2,j3" 
001353        LIMIT {EXCEPT}
001354    18  "SELECT a FROM j1 LIMIT (SELECT e FROM j2) EXCEPT SELECT g FROM j2,j3" 
001355        LIMIT {EXCEPT}
001356  
001357    19  "SELECT * FROM j1 ORDER BY a INTERSECT SELECT * FROM j2,j3" 
001358        {ORDER BY} {INTERSECT}
001359    20  "SELECT count(*) FROM j1 ORDER BY 1 INTERSECT SELECT max(e) FROM j2"
001360        {ORDER BY} {INTERSECT}
001361    21  "SELECT count(*), * FROM j1 ORDER BY 1,2,3 INTERSECT SELECT *,* FROM j2"
001362        {ORDER BY} {INTERSECT}
001363    22  "SELECT * FROM j1 LIMIT 10 INTERSECT SELECT * FROM j2,j3" 
001364        LIMIT {INTERSECT}
001365    23  "SELECT * FROM j1 LIMIT 10 OFFSET 5 INTERSECT SELECT * FROM j2,j3" 
001366        LIMIT {INTERSECT}
001367    24  "SELECT a FROM j1 LIMIT (SELECT e FROM j2) INTERSECT SELECT g FROM j2,j3" 
001368        LIMIT {INTERSECT}
001369  } {
001370    set err "$op1 clause should come after $op2 not before"
001371    do_catchsql_test e_select-7.2.$tn $select [list 1 $err]
001372  }
001373  
001374  # EVIDENCE-OF: R-45440-25633 ORDER BY and LIMIT clauses may only occur
001375  # at the end of the entire compound SELECT, and then only if the final
001376  # element of the compound is not a VALUES clause.
001377  #
001378  foreach {tn select} {
001379    1   "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 ORDER BY a"
001380    2   "SELECT count(*) FROM j1 UNION ALL SELECT max(e) FROM j2 ORDER BY 1"
001381    3   "SELECT count(*), * FROM j1 UNION ALL SELECT *,* FROM j2 ORDER BY 1,2,3"
001382    4   "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10" 
001383    5   "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
001384    6   "SELECT a FROM j1 UNION ALL SELECT g FROM j2,j3 LIMIT (SELECT 10)" 
001385  
001386    7   "SELECT * FROM j1 UNION SELECT * FROM j2,j3 ORDER BY a"
001387    8   "SELECT count(*) FROM j1 UNION SELECT max(e) FROM j2 ORDER BY 1"
001388    8b  "VALUES('8b') UNION SELECT max(e) FROM j2 ORDER BY 1"
001389    9   "SELECT count(*), * FROM j1 UNION SELECT *,* FROM j2 ORDER BY 1,2,3"
001390    10  "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10" 
001391    11  "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
001392    12  "SELECT a FROM j1 UNION SELECT g FROM j2,j3 LIMIT (SELECT 10)" 
001393  
001394    13  "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 ORDER BY a"
001395    14  "SELECT count(*) FROM j1 EXCEPT SELECT max(e) FROM j2 ORDER BY 1"
001396    15  "SELECT count(*), * FROM j1 EXCEPT SELECT *,* FROM j2 ORDER BY 1,2,3"
001397    16  "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 LIMIT 10" 
001398    17  "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
001399    18  "SELECT a FROM j1 EXCEPT SELECT g FROM j2,j3 LIMIT (SELECT 10)" 
001400  
001401    19  "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 ORDER BY a"
001402    20  "SELECT count(*) FROM j1 INTERSECT SELECT max(e) FROM j2 ORDER BY 1"
001403    21  "SELECT count(*), * FROM j1 INTERSECT SELECT *,* FROM j2 ORDER BY 1,2,3"
001404    22  "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10" 
001405    23  "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
001406    24  "SELECT a FROM j1 INTERSECT SELECT g FROM j2,j3 LIMIT (SELECT 10)" 
001407  } {
001408    do_test e_select-7.3.$tn { catch {execsql $select} msg } 0
001409  }
001410  foreach {tn select} {
001411    50   "SELECT * FROM j1 ORDER BY 1 UNION ALL SELECT * FROM j2,j3"
001412    51   "SELECT * FROM j1 LIMIT 1 UNION ALL SELECT * FROM j2,j3"
001413    52   "SELECT count(*) FROM j1 UNION ALL VALUES(11) ORDER BY 1"
001414    53   "SELECT count(*) FROM j1 UNION ALL VALUES(11) LIMIT 1"
001415  } {
001416    do_test e_select-7.3.$tn { catch {execsql $select} msg } 1
001417  }
001418  
001419  # EVIDENCE-OF: R-08531-36543 A compound SELECT created using UNION ALL
001420  # operator returns all the rows from the SELECT to the left of the UNION
001421  # ALL operator, and all the rows from the SELECT to the right of it.
001422  #
001423  drop_all_tables
001424  do_execsql_test e_select-7.4.0 {
001425    CREATE TABLE q1(a TEXT, b INTEGER, c);
001426    CREATE TABLE q2(d NUMBER, e BLOB);
001427    CREATE TABLE q3(f REAL, g);
001428  
001429    INSERT INTO q1 VALUES(16, -87.66, NULL);
001430    INSERT INTO q1 VALUES('legible', 94, -42.47);
001431    INSERT INTO q1 VALUES('beauty', 36, NULL);
001432  
001433    INSERT INTO q2 VALUES('legible', 1);
001434    INSERT INTO q2 VALUES('beauty', 2);
001435    INSERT INTO q2 VALUES(-65.91, 4);
001436    INSERT INTO q2 VALUES('emanating', -16.56);
001437  
001438    INSERT INTO q3 VALUES('beauty', 2);
001439    INSERT INTO q3 VALUES('beauty', 2);
001440  } {}
001441  do_select_tests e_select-7.4 {
001442    1   {SELECT a FROM q1 UNION ALL SELECT d FROM q2}
001443        {16 legible beauty legible beauty -65.91 emanating}
001444  
001445    2   {SELECT * FROM q1 WHERE a=16 UNION ALL SELECT 'x', * FROM q2 WHERE oid=1}
001446        {16 -87.66 {} x legible 1}
001447  
001448    3   {SELECT count(*) FROM q1 UNION ALL SELECT min(e) FROM q2} 
001449        {3 -16.56}
001450  
001451    4   {SELECT * FROM q2 UNION ALL SELECT * FROM q3} 
001452        {legible 1 beauty 2 -65.91 4 emanating -16.56 beauty 2 beauty 2}
001453  } 
001454  
001455  # EVIDENCE-OF: R-20560-39162 The UNION operator works the same way as
001456  # UNION ALL, except that duplicate rows are removed from the final
001457  # result set.
001458  #
001459  do_select_tests e_select-7.5 {
001460    1   {SELECT a FROM q1 UNION SELECT d FROM q2}
001461        {-65.91 16 beauty emanating legible}
001462  
001463    2   {SELECT * FROM q1 WHERE a=16 UNION SELECT 'x', * FROM q2 WHERE oid=1}
001464        {16 -87.66 {} x legible 1}
001465  
001466    3   {SELECT count(*) FROM q1 UNION SELECT min(e) FROM q2} 
001467        {-16.56 3}
001468  
001469    4   {SELECT * FROM q2 UNION SELECT * FROM q3} 
001470        {-65.91 4 beauty 2 emanating -16.56 legible 1}
001471  } 
001472  
001473  # EVIDENCE-OF: R-45764-31737 The INTERSECT operator returns the
001474  # intersection of the results of the left and right SELECTs.
001475  #
001476  do_select_tests e_select-7.6 {
001477    1   {SELECT a FROM q1 INTERSECT SELECT d FROM q2} {beauty legible}
001478    2   {SELECT * FROM q2 INTERSECT SELECT * FROM q3} {beauty 2}
001479  }
001480  
001481  # EVIDENCE-OF: R-25787-28949 The EXCEPT operator returns the subset of
001482  # rows returned by the left SELECT that are not also returned by the
001483  # right-hand SELECT.
001484  #
001485  do_select_tests e_select-7.7 {
001486    1   {SELECT a FROM q1 EXCEPT SELECT d FROM q2} {16}
001487  
001488    2   {SELECT * FROM q2 EXCEPT SELECT * FROM q3} 
001489        {-65.91 4 emanating -16.56 legible 1}
001490  }
001491  
001492  # EVIDENCE-OF: R-40729-56447 Duplicate rows are removed from the results
001493  # of INTERSECT and EXCEPT operators before the result set is returned.
001494  #
001495  do_select_tests e_select-7.8 {
001496    0   {SELECT * FROM q3} {beauty 2 beauty 2}
001497  
001498    1   {SELECT * FROM q3 INTERSECT SELECT * FROM q3} {beauty 2}
001499    2   {SELECT * FROM q3 EXCEPT SELECT a,b FROM q1}  {beauty 2}
001500  }
001501  
001502  # EVIDENCE-OF: R-46765-43362 For the purposes of determining duplicate
001503  # rows for the results of compound SELECT operators, NULL values are
001504  # considered equal to other NULL values and distinct from all non-NULL
001505  # values.
001506  #
001507  db nullvalue null
001508  do_select_tests e_select-7.9 {
001509    1   {SELECT NULL UNION ALL SELECT NULL} {null null}
001510    2   {SELECT NULL UNION     SELECT NULL} {null}
001511    3   {SELECT NULL INTERSECT SELECT NULL} {null}
001512    4   {SELECT NULL EXCEPT    SELECT NULL} {}
001513  
001514    5   {SELECT NULL UNION ALL SELECT 'ab'} {null ab}
001515    6   {SELECT NULL UNION     SELECT 'ab'} {null ab}
001516    7   {SELECT NULL INTERSECT SELECT 'ab'} {}
001517    8   {SELECT NULL EXCEPT    SELECT 'ab'} {null}
001518  
001519    9   {SELECT NULL UNION ALL SELECT 0} {null 0}
001520    10  {SELECT NULL UNION     SELECT 0} {null 0}
001521    11  {SELECT NULL INTERSECT SELECT 0} {}
001522    12  {SELECT NULL EXCEPT    SELECT 0} {null}
001523  
001524    13  {SELECT c FROM q1 UNION ALL SELECT g FROM q3} {null -42.47 null 2 2}
001525    14  {SELECT c FROM q1 UNION     SELECT g FROM q3} {null -42.47 2}
001526    15  {SELECT c FROM q1 INTERSECT SELECT g FROM q3} {}
001527    16  {SELECT c FROM q1 EXCEPT    SELECT g FROM q3} {null -42.47}
001528  }
001529  db nullvalue {} 
001530  
001531  # EVIDENCE-OF: R-51232-50224 The collation sequence used to compare two
001532  # text values is determined as if the columns of the left and right-hand
001533  # SELECT statements were the left and right-hand operands of the equals
001534  # (=) operator, except that greater precedence is not assigned to a
001535  # collation sequence specified with the postfix COLLATE operator.
001536  #
001537  drop_all_tables
001538  do_execsql_test e_select-7.10.0 {
001539    CREATE TABLE y1(a COLLATE nocase, b COLLATE binary, c);
001540    INSERT INTO y1 VALUES('Abc', 'abc', 'aBC');
001541  } {}
001542  do_select_tests e_select-7.10 {
001543    1   {SELECT 'abc'                UNION SELECT 'ABC'} {ABC abc}
001544    2   {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC'} {ABC}
001545    3   {SELECT 'abc'                UNION SELECT 'ABC' COLLATE nocase} {ABC}
001546    4   {SELECT 'abc' COLLATE binary UNION SELECT 'ABC' COLLATE nocase} {ABC abc}
001547    5   {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC' COLLATE binary} {ABC}
001548  
001549    6   {SELECT a FROM y1 UNION SELECT b FROM y1}                {abc}
001550    7   {SELECT b FROM y1 UNION SELECT a FROM y1}                {Abc abc}
001551    8   {SELECT a FROM y1 UNION SELECT c FROM y1}                {aBC}
001552  
001553    9   {SELECT a FROM y1 UNION SELECT c COLLATE binary FROM y1} {aBC}
001554  }
001555  
001556  # EVIDENCE-OF: R-32706-07403 No affinity transformations are applied to
001557  # any values when comparing rows as part of a compound SELECT.
001558  #
001559  drop_all_tables
001560  do_execsql_test e_select-7.10.0 {
001561    CREATE TABLE w1(a TEXT, b NUMBER);
001562    CREATE TABLE w2(a, b TEXT);
001563  
001564    INSERT INTO w1 VALUES('1', 4.1);
001565    INSERT INTO w2 VALUES(1, 4.1);
001566  } {}
001567  
001568  do_select_tests e_select-7.11 {
001569    1  { SELECT a FROM w1 UNION SELECT a FROM w2 } {1 1}
001570    2  { SELECT a FROM w2 UNION SELECT a FROM w1 } {1 1}
001571    3  { SELECT b FROM w1 UNION SELECT b FROM w2 } {4.1 4.1}
001572    4  { SELECT b FROM w2 UNION SELECT b FROM w1 } {4.1 4.1}
001573  
001574    5  { SELECT a FROM w1 INTERSECT SELECT a FROM w2 } {}
001575    6  { SELECT a FROM w2 INTERSECT SELECT a FROM w1 } {}
001576    7  { SELECT b FROM w1 INTERSECT SELECT b FROM w2 } {}
001577    8  { SELECT b FROM w2 INTERSECT SELECT b FROM w1 } {}
001578  
001579    9  { SELECT a FROM w1 EXCEPT SELECT a FROM w2 } {1}
001580    10 { SELECT a FROM w2 EXCEPT SELECT a FROM w1 } {1}
001581    11 { SELECT b FROM w1 EXCEPT SELECT b FROM w2 } {4.1}
001582    12 { SELECT b FROM w2 EXCEPT SELECT b FROM w1 } {4.1}
001583  }
001584  
001585  
001586  # EVIDENCE-OF: R-32562-20566 When three or more simple SELECTs are
001587  # connected into a compound SELECT, they group from left to right. In
001588  # other words, if "A", "B" and "C" are all simple SELECT statements, (A
001589  # op B op C) is processed as ((A op B) op C).
001590  #
001591  #   e_select-7.12.1: Precedence of UNION vs. INTERSECT 
001592  #   e_select-7.12.2: Precedence of UNION vs. UNION ALL 
001593  #   e_select-7.12.3: Precedence of UNION vs. EXCEPT
001594  #   e_select-7.12.4: Precedence of INTERSECT vs. UNION ALL 
001595  #   e_select-7.12.5: Precedence of INTERSECT vs. EXCEPT
001596  #   e_select-7.12.6: Precedence of UNION ALL vs. EXCEPT
001597  #   e_select-7.12.7: Check that "a EXCEPT b EXCEPT c" is processed as 
001598  #                   "(a EXCEPT b) EXCEPT c".
001599  #
001600  # The INTERSECT and EXCEPT operations are mutually commutative. So
001601  # the e_select-7.12.5 test cases do not prove very much.
001602  #
001603  drop_all_tables
001604  do_execsql_test e_select-7.12.0 {
001605    CREATE TABLE t1(x);
001606    INSERT INTO t1 VALUES(1);
001607    INSERT INTO t1 VALUES(2);
001608    INSERT INTO t1 VALUES(3);
001609  } {}
001610  foreach {tn select res} {
001611    1a "(1,2) INTERSECT (1)   UNION     (3)"   {1 3}
001612    1b "(3)   UNION     (1,2) INTERSECT (1)"   {1}
001613  
001614    2a "(1,2) UNION     (3)   UNION ALL (1)"   {1 2 3 1}
001615    2b "(1)   UNION ALL (3)   UNION     (1,2)" {1 2 3}
001616  
001617    3a "(1,2) UNION     (3)   EXCEPT    (1)"   {2 3}
001618    3b "(1,2) EXCEPT    (3)   UNION     (1)"   {1 2}
001619  
001620    4a "(1,2) INTERSECT (1)   UNION ALL (3)"   {1 3}
001621    4b "(3)   UNION     (1,2) INTERSECT (1)"   {1}
001622  
001623    5a "(1,2) INTERSECT (2)   EXCEPT    (2)"   {}
001624    5b "(2,3) EXCEPT    (2)   INTERSECT (2)"   {}
001625  
001626    6a "(2)   UNION ALL (2)   EXCEPT    (2)"   {}
001627    6b "(2)   EXCEPT    (2)   UNION ALL (2)"   {2}
001628  
001629    7  "(2,3) EXCEPT    (2)   EXCEPT    (3)"   {}
001630  } {
001631    set select [string map {( {SELECT x FROM t1 WHERE x IN (}} $select]
001632    do_execsql_test e_select-7.12.$tn $select [list {*}$res]
001633  }
001634  
001635  
001636  #-------------------------------------------------------------------------
001637  # ORDER BY clauses
001638  #
001639  
001640  drop_all_tables
001641  do_execsql_test e_select-8.1.0 {
001642    CREATE TABLE d1(x, y, z);
001643  
001644    INSERT INTO d1 VALUES(1, 2, 3);
001645    INSERT INTO d1 VALUES(2, 5, -1);
001646    INSERT INTO d1 VALUES(1, 2, 8);
001647    INSERT INTO d1 VALUES(1, 2, 7);
001648    INSERT INTO d1 VALUES(2, 4, 93);
001649    INSERT INTO d1 VALUES(1, 2, -20);
001650    INSERT INTO d1 VALUES(1, 4, 93);
001651    INSERT INTO d1 VALUES(1, 5, -1);
001652  
001653    CREATE TABLE d2(a, b);
001654    INSERT INTO d2 VALUES('gently', 'failings');
001655    INSERT INTO d2 VALUES('commercials', 'bathrobe');
001656    INSERT INTO d2 VALUES('iterate', 'sexton');
001657    INSERT INTO d2 VALUES('babied', 'charitableness');
001658    INSERT INTO d2 VALUES('solemnness', 'annexed');
001659    INSERT INTO d2 VALUES('rejoicing', 'liabilities');
001660    INSERT INTO d2 VALUES('pragmatist', 'guarded');
001661    INSERT INTO d2 VALUES('barked', 'interrupted');
001662    INSERT INTO d2 VALUES('reemphasizes', 'reply');
001663    INSERT INTO d2 VALUES('lad', 'relenting');
001664  } {}
001665  
001666  # EVIDENCE-OF: R-44988-41064 Rows are first sorted based on the results
001667  # of evaluating the left-most expression in the ORDER BY list, then ties
001668  # are broken by evaluating the second left-most expression and so on.
001669  #
001670  do_select_tests e_select-8.1 {
001671    1  "SELECT * FROM d1 ORDER BY x, y, z" {
001672       1 2 -20    1 2 3    1 2 7    1 2 8    
001673       1 4  93    1 5 -1   2 4 93   2 5 -1
001674    }
001675  }
001676  
001677  # EVIDENCE-OF: R-06617-54588 Each ORDER BY expression may be optionally
001678  # followed by one of the keywords ASC (smaller values are returned
001679  # first) or DESC (larger values are returned first).
001680  #
001681  #   Test cases e_select-8.2.* test the above.
001682  #
001683  # EVIDENCE-OF: R-18705-33393 If neither ASC or DESC are specified, rows
001684  # are sorted in ascending (smaller values first) order by default.
001685  #
001686  #   Test cases e_select-8.3.* test the above. All 8.3 test cases are
001687  #   copies of 8.2 test cases with the explicit "ASC" removed.
001688  #
001689  do_select_tests e_select-8 {
001690    2.1  "SELECT * FROM d1 ORDER BY x ASC, y ASC, z ASC" {
001691       1 2 -20    1 2 3    1 2 7    1 2 8    
001692       1 4  93    1 5 -1   2 4 93   2 5 -1
001693    }
001694    2.2  "SELECT * FROM d1 ORDER BY x DESC, y DESC, z DESC" {
001695       2 5 -1     2 4 93   1 5 -1   1 4  93    
001696       1 2 8      1 2 7    1 2 3    1 2 -20    
001697    }
001698    2.3 "SELECT * FROM d1 ORDER BY x DESC, y ASC, z DESC" {
001699       2 4 93   2 5 -1     1 2 8      1 2 7    
001700       1 2 3    1 2 -20    1 4  93    1 5 -1   
001701    }
001702    2.4  "SELECT * FROM d1 ORDER BY x DESC, y ASC, z ASC" {
001703       2 4 93   2 5 -1     1 2 -20    1 2 3    
001704       1 2 7    1 2 8      1 4  93    1 5 -1   
001705    }
001706  
001707    3.1  "SELECT * FROM d1 ORDER BY x, y, z" {
001708       1 2 -20    1 2 3    1 2 7    1 2 8    
001709       1 4  93    1 5 -1   2 4 93   2 5 -1
001710    }
001711    3.3  "SELECT * FROM d1 ORDER BY x DESC, y, z DESC" {
001712       2 4 93   2 5 -1     1 2 8      1 2 7    
001713       1 2 3    1 2 -20    1 4  93    1 5 -1   
001714    }
001715    3.4 "SELECT * FROM d1 ORDER BY x DESC, y, z" {
001716       2 4 93   2 5 -1     1 2 -20    1 2 3    
001717       1 2 7    1 2 8      1 4  93    1 5 -1   
001718    }
001719  }
001720  
001721  # EVIDENCE-OF: R-29779-04281 If the ORDER BY expression is a constant
001722  # integer K then the expression is considered an alias for the K-th
001723  # column of the result set (columns are numbered from left to right
001724  # starting with 1).
001725  #
001726  do_select_tests e_select-8.4 {
001727    1  "SELECT * FROM d1 ORDER BY 1 ASC, 2 ASC, 3 ASC" {
001728       1 2 -20    1 2 3    1 2 7    1 2 8    
001729       1 4  93    1 5 -1   2 4 93   2 5 -1
001730    }
001731    2  "SELECT * FROM d1 ORDER BY 1 DESC, 2 DESC, 3 DESC" {
001732       2 5 -1     2 4 93   1 5 -1   1 4  93    
001733       1 2 8      1 2 7    1 2 3    1 2 -20    
001734    }
001735    3 "SELECT * FROM d1 ORDER BY 1 DESC, 2 ASC, 3 DESC" {
001736       2 4 93   2 5 -1     1 2 8      1 2 7    
001737       1 2 3    1 2 -20    1 4  93    1 5 -1   
001738    }
001739    4  "SELECT * FROM d1 ORDER BY 1 DESC, 2 ASC, 3 ASC" {
001740       2 4 93   2 5 -1     1 2 -20    1 2 3    
001741       1 2 7    1 2 8      1 4  93    1 5 -1   
001742    }
001743    5  "SELECT * FROM d1 ORDER BY 1, 2, 3" {
001744       1 2 -20    1 2 3    1 2 7    1 2 8    
001745       1 4  93    1 5 -1   2 4 93   2 5 -1
001746    }
001747    6  "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3 DESC" {
001748       2 4 93   2 5 -1     1 2 8      1 2 7    
001749       1 2 3    1 2 -20    1 4  93    1 5 -1   
001750    }
001751    7  "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3" {
001752       2 4 93   2 5 -1     1 2 -20    1 2 3    
001753       1 2 7    1 2 8      1 4  93    1 5 -1   
001754    }
001755    8  "SELECT z, x FROM d1 ORDER BY 2" {
001756       /# 1    # 1    # 1   # 1 
001757        # 1    # 1    # 2   # 2/
001758    }
001759    9  "SELECT z, x FROM d1 ORDER BY 1" {
001760       /-20 1  -1 #   -1 #   3 1
001761       7 1     8 1   93 #   93 #/   
001762    }
001763  }
001764  
001765  # EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier
001766  # that corresponds to the alias of one of the output columns, then the
001767  # expression is considered an alias for that column.
001768  #
001769  do_select_tests e_select-8.5 {
001770    1   "SELECT z+1 AS abc FROM d1 ORDER BY abc" {
001771      -19 0 0 4 8 9 94 94
001772    }
001773    2   "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" {
001774      94 94 9 8 4 0 0 -19
001775    }
001776    3  "SELECT z AS x, x AS z FROM d1 ORDER BY z" {
001777      /# 1    # 1    # 1    # 1    # 1    # 1    # 2    # 2/
001778    }
001779    4  "SELECT z AS x, x AS z FROM d1 ORDER BY x" {
001780      /-20 1    -1 #    -1 #    3 1    7 1    8 1    93 #    93 #/
001781    }
001782  }
001783  
001784  # EVIDENCE-OF: R-65068-27207 Otherwise, if the ORDER BY expression is
001785  # any other expression, it is evaluated and the returned value used to
001786  # order the output rows.
001787  #
001788  # EVIDENCE-OF: R-03421-57988 If the SELECT statement is a simple SELECT,
001789  # then an ORDER BY may contain any arbitrary expressions.
001790  #
001791  do_select_tests e_select-8.6 {
001792    1   "SELECT * FROM d1 ORDER BY x+y+z" {
001793      1 2 -20    1 5 -1    1 2 3    2 5 -1 
001794      1 2 7      1 2 8     1 4 93   2 4 93
001795    }
001796    2   "SELECT * FROM d1 ORDER BY x*z" {
001797      1 2 -20    2 5 -1    1 5 -1    1 2 3 
001798      1 2 7      1 2 8     1 4 93    2 4 93
001799    }
001800    3   "SELECT * FROM d1 ORDER BY y*z" {
001801      1 2 -20    2 5 -1    1 5 -1    1 2 3 
001802      1 2 7      1 2 8     2 4 93    1 4 93
001803    }
001804  }
001805  
001806  # EVIDENCE-OF: R-28853-08147 However, if the SELECT is a compound
001807  # SELECT, then ORDER BY expressions that are not aliases to output
001808  # columns must be exactly the same as an expression used as an output
001809  # column.
001810  #
001811  do_select_tests e_select-8.7.1 -error {
001812    %s ORDER BY term does not match any column in the result set
001813  } {
001814    1   "SELECT x FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z"        1st
001815    2   "SELECT x,z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" 2nd
001816  } 
001817  
001818  do_select_tests e_select-8.7.2 {
001819    1   "SELECT x*z FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z" {
001820      -20 -2 -1 3 7 8 93 186 babied barked commercials gently 
001821      iterate lad pragmatist reemphasizes rejoicing solemnness
001822    }
001823    2   "SELECT x, x/z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" {
001824      1 -1 1 0 1 0 1 0 1 0 1 0 2 -2 2 0 
001825      babied charitableness barked interrupted commercials bathrobe gently
001826      failings iterate sexton lad relenting pragmatist guarded reemphasizes reply
001827      rejoicing liabilities solemnness annexed
001828    }
001829  } 
001830  
001831  do_execsql_test e_select-8.8.0 {
001832    CREATE TABLE d3(a);
001833    INSERT INTO d3 VALUES('text');
001834    INSERT INTO d3 VALUES(14.1);
001835    INSERT INTO d3 VALUES(13);
001836    INSERT INTO d3 VALUES(X'78787878');
001837    INSERT INTO d3 VALUES(15);
001838    INSERT INTO d3 VALUES(12.9);
001839    INSERT INTO d3 VALUES(null);
001840  
001841    CREATE TABLE d4(x COLLATE nocase);
001842    INSERT INTO d4 VALUES('abc');
001843    INSERT INTO d4 VALUES('ghi');
001844    INSERT INTO d4 VALUES('DEF');
001845    INSERT INTO d4 VALUES('JKL');
001846  } {}
001847  
001848  # EVIDENCE-OF: R-10883-17697 For the purposes of sorting rows, values
001849  # are compared in the same way as for comparison expressions.
001850  #
001851  #   The following tests verify that values of different types are sorted
001852  #   correctly, and that mixed real and integer values are compared properly.
001853  #
001854  do_execsql_test e_select-8.8.1 {
001855    SELECT a FROM d3 ORDER BY a
001856  } {{} 12.9 13 14.1 15 text xxxx}
001857  do_execsql_test e_select-8.8.2 {
001858    SELECT a FROM d3 ORDER BY a DESC
001859  } {xxxx text 15 14.1 13 12.9 {}}
001860  
001861  
001862  # EVIDENCE-OF: R-64199-22471 If the ORDER BY expression is assigned a
001863  # collation sequence using the postfix COLLATE operator, then the
001864  # specified collation sequence is used.
001865  #
001866  do_execsql_test e_select-8.9.1 {
001867    SELECT x FROM d4 ORDER BY 1 COLLATE binary
001868  } {DEF JKL abc ghi}
001869  do_execsql_test e_select-8.9.2 {
001870    SELECT x COLLATE binary FROM d4 ORDER BY 1 COLLATE nocase
001871  } {abc DEF ghi JKL}
001872  
001873  # EVIDENCE-OF: R-09398-26102 Otherwise, if the ORDER BY expression is 
001874  # an alias to an expression that has been assigned a collation sequence 
001875  # using the postfix COLLATE operator, then the collation sequence 
001876  # assigned to the aliased expression is used.
001877  #
001878  #   In the test 8.10.2, the only result-column expression has no alias. So the
001879  #   ORDER BY expression is not a reference to it and therefore does not inherit
001880  #   the collation sequence. In test 8.10.3, "x" is the alias (as well as the
001881  #   column name), so the ORDER BY expression is interpreted as an alias and the
001882  #   collation sequence attached to the result column is used for sorting.
001883  #
001884  do_execsql_test e_select-8.10.1 {
001885    SELECT x COLLATE binary FROM d4 ORDER BY 1
001886  } {DEF JKL abc ghi}
001887  do_execsql_test e_select-8.10.2 {
001888    SELECT x COLLATE binary FROM d4 ORDER BY x
001889  } {abc DEF ghi JKL}
001890  do_execsql_test e_select-8.10.3 {
001891    SELECT x COLLATE binary AS x FROM d4 ORDER BY x
001892  } {DEF JKL abc ghi}
001893  
001894  # EVIDENCE-OF: R-27301-09658 Otherwise, if the ORDER BY expression is a
001895  # column or an alias of an expression that is a column, then the default
001896  # collation sequence for the column is used.
001897  #
001898  do_execsql_test e_select-8.11.1 {
001899    SELECT x AS y FROM d4 ORDER BY y
001900  } {abc DEF ghi JKL}
001901  do_execsql_test e_select-8.11.2 {
001902    SELECT x||'' FROM d4 ORDER BY x
001903  } {abc DEF ghi JKL}
001904  
001905  # EVIDENCE-OF: R-49925-55905 Otherwise, the BINARY collation sequence is
001906  # used.
001907  #
001908  do_execsql_test e_select-8.12.1 {
001909    SELECT x FROM d4 ORDER BY x||''
001910  } {DEF JKL abc ghi}
001911  
001912  # EVIDENCE-OF: R-44130-32593 If an ORDER BY expression is not an integer
001913  # alias, then SQLite searches the left-most SELECT in the compound for a
001914  # result column that matches either the second or third rules above. If
001915  # a match is found, the search stops and the expression is handled as an
001916  # alias for the result column that it has been matched against.
001917  # Otherwise, the next SELECT to the right is tried, and so on.
001918  #
001919  do_execsql_test e_select-8.13.0 {
001920    CREATE TABLE d5(a, b);
001921    CREATE TABLE d6(c, d);
001922    CREATE TABLE d7(e, f);
001923   
001924    INSERT INTO d5 VALUES(1, 'f');
001925    INSERT INTO d6 VALUES(2, 'e');
001926    INSERT INTO d7 VALUES(3, 'd');
001927    INSERT INTO d5 VALUES(4, 'c');
001928    INSERT INTO d6 VALUES(5, 'b');
001929    INSERT INTO d7 VALUES(6, 'a');
001930  
001931    CREATE TABLE d8(x COLLATE nocase);
001932    CREATE TABLE d9(y COLLATE nocase);
001933  
001934    INSERT INTO d8 VALUES('a');
001935    INSERT INTO d9 VALUES('B');
001936    INSERT INTO d8 VALUES('c');
001937    INSERT INTO d9 VALUES('D');
001938  } {}
001939  do_select_tests e_select-8.13 {
001940    1   { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7
001941           ORDER BY a
001942        } {1 2 3 4 5 6}
001943    2   { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7
001944           ORDER BY c
001945        } {1 2 3 4 5 6}
001946    3   { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7
001947           ORDER BY e
001948        } {1 2 3 4 5 6}
001949    4   { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7
001950           ORDER BY 1
001951        } {1 2 3 4 5 6}
001952  
001953    5   { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY b } 
001954        {f 1   c 4   4 c   1 f}
001955    6   { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY 2 } 
001956        {f 1   c 4   4 c   1 f}
001957  
001958    7   { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY a } 
001959        {1 f   4 c   c 4   f 1}
001960    8   { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY 1 } 
001961        {1 f   4 c   c 4   f 1}
001962  
001963    9   { SELECT a, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY a+1 } 
001964        {f 2   c 5   4 c   1 f}
001965    10  { SELECT a, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 2 } 
001966        {f 2   c 5   4 c   1 f}
001967  
001968    11  { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY a+1 } 
001969        {2 f   5 c   c 5   f 2}
001970    12  { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 1 } 
001971        {2 f   5 c   c 5   f 2}
001972  } 
001973  
001974  # EVIDENCE-OF: R-39265-04070 If no matching expression can be found in
001975  # the result columns of any constituent SELECT, it is an error.
001976  #
001977  do_select_tests e_select-8.14 -error {
001978    %s ORDER BY term does not match any column in the result set
001979  } {
001980    1   { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a+1 }          1st
001981    2   { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a, a+1 }       2nd
001982    3   { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY 'hello' }  1st
001983    4   { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY blah    }  1st
001984    5   { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY c,d,c+d }  3rd
001985    6   { SELECT * FROM d5 EXCEPT SELECT * FROM d7 ORDER BY 1,2,b,a/b  }  4th
001986  } 
001987  
001988  # EVIDENCE-OF: R-03407-11483 Each term of the ORDER BY clause is
001989  # processed separately and may be matched against result columns from
001990  # different SELECT statements in the compound.
001991  # 
001992  do_select_tests e_select-8.15 {
001993    1  { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY a, d }
001994       {1 e   1 f   4 b   4 c}
001995    2  { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY c-1, b }
001996       {1 e   1 f   4 b   4 c}
001997    3  { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY 1, 2 }
001998       {1 e   1 f   4 b   4 c}
001999  } 
002000  
002001  
002002  #-------------------------------------------------------------------------
002003  # Tests related to statements made about the LIMIT/OFFSET clause.
002004  #
002005  do_execsql_test e_select-9.0 {
002006    CREATE TABLE f1(a, b);
002007    INSERT INTO f1 VALUES(26, 'z');
002008    INSERT INTO f1 VALUES(25, 'y');
002009    INSERT INTO f1 VALUES(24, 'x');
002010    INSERT INTO f1 VALUES(23, 'w');
002011    INSERT INTO f1 VALUES(22, 'v');
002012    INSERT INTO f1 VALUES(21, 'u');
002013    INSERT INTO f1 VALUES(20, 't');
002014    INSERT INTO f1 VALUES(19, 's');
002015    INSERT INTO f1 VALUES(18, 'r');
002016    INSERT INTO f1 VALUES(17, 'q');
002017    INSERT INTO f1 VALUES(16, 'p');
002018    INSERT INTO f1 VALUES(15, 'o');
002019    INSERT INTO f1 VALUES(14, 'n');
002020    INSERT INTO f1 VALUES(13, 'm');
002021    INSERT INTO f1 VALUES(12, 'l');
002022    INSERT INTO f1 VALUES(11, 'k');
002023    INSERT INTO f1 VALUES(10, 'j');
002024    INSERT INTO f1 VALUES(9, 'i');
002025    INSERT INTO f1 VALUES(8, 'h');
002026    INSERT INTO f1 VALUES(7, 'g');
002027    INSERT INTO f1 VALUES(6, 'f');
002028    INSERT INTO f1 VALUES(5, 'e');
002029    INSERT INTO f1 VALUES(4, 'd');
002030    INSERT INTO f1 VALUES(3, 'c');
002031    INSERT INTO f1 VALUES(2, 'b');
002032    INSERT INTO f1 VALUES(1, 'a');
002033  } {}
002034  
002035  # EVIDENCE-OF: R-30481-56627 Any scalar expression may be used in the
002036  # LIMIT clause, so long as it evaluates to an integer or a value that
002037  # can be losslessly converted to an integer.
002038  #
002039  do_select_tests e_select-9.1 {
002040    1  { SELECT b FROM f1 ORDER BY a LIMIT 5 } {a b c d e}
002041    2  { SELECT b FROM f1 ORDER BY a LIMIT 2+3 } {a b c d e}
002042    3  { SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b = 'e') } 
002043       {a b c d e}
002044    4  { SELECT b FROM f1 ORDER BY a LIMIT 5.0 } {a b c d e}
002045    5  { SELECT b FROM f1 ORDER BY a LIMIT '5' } {a b c d e}
002046  }
002047  
002048  # EVIDENCE-OF: R-46155-47219 If the expression evaluates to a NULL value
002049  # or any other value that cannot be losslessly converted to an integer,
002050  # an error is returned.
002051  #
002052  
002053  do_select_tests e_select-9.2 -error "datatype mismatch" {
002054    1  { SELECT b FROM f1 ORDER BY a LIMIT 'hello' } {}
002055    2  { SELECT b FROM f1 ORDER BY a LIMIT NULL } {}
002056    3  { SELECT b FROM f1 ORDER BY a LIMIT X'ABCD' } {}
002057    4  { SELECT b FROM f1 ORDER BY a LIMIT 5.1 } {}
002058    5  { SELECT b FROM f1 ORDER BY a LIMIT (SELECT group_concat(b) FROM f1) } {}
002059  } 
002060  
002061  # EVIDENCE-OF: R-03014-26414 If the LIMIT expression evaluates to a
002062  # negative value, then there is no upper bound on the number of rows
002063  # returned.
002064  #
002065  do_select_tests e_select-9.4 {
002066    1  { SELECT b FROM f1 ORDER BY a LIMIT -1 } 
002067       {a b c d e f g h i j k l m n o p q r s t u v w x y z}
002068    2  { SELECT b FROM f1 ORDER BY a LIMIT length('abc')-100 } 
002069       {a b c d e f g h i j k l m n o p q r s t u v w x y z}
002070    3  { SELECT b FROM f1 ORDER BY a LIMIT (SELECT count(*) FROM f1)/2 - 14 }
002071       {a b c d e f g h i j k l m n o p q r s t u v w x y z}
002072  }
002073  
002074  # EVIDENCE-OF: R-33750-29536 Otherwise, the SELECT returns the first N
002075  # rows of its result set only, where N is the value that the LIMIT
002076  # expression evaluates to.
002077  #
002078  do_select_tests e_select-9.5 {
002079    1  { SELECT b FROM f1 ORDER BY a LIMIT 0 } {}
002080    2  { SELECT b FROM f1 ORDER BY a DESC LIMIT 4 } {z y x w}
002081    3  { SELECT b FROM f1 ORDER BY a DESC LIMIT 8 } {z y x w v u t s}
002082    4  { SELECT b FROM f1 ORDER BY a DESC LIMIT '12.0' } {z y x w v u t s r q p o}
002083  }
002084  
002085  # EVIDENCE-OF: R-54935-19057 Or, if the SELECT statement would return
002086  # less than N rows without a LIMIT clause, then the entire result set is
002087  # returned.
002088  #
002089  do_select_tests e_select-9.6 {
002090    1  { SELECT b FROM f1 WHERE a>21 ORDER BY a LIMIT 10 } {v w x y z}
002091    2  { SELECT count(*) FROM f1 GROUP BY a/5 ORDER BY 1 LIMIT 10 } {2 4 5 5 5 5}
002092  } 
002093  
002094  
002095  # EVIDENCE-OF: R-24188-24349 The expression attached to the optional
002096  # OFFSET clause that may follow a LIMIT clause must also evaluate to an
002097  # integer, or a value that can be losslessly converted to an integer.
002098  #
002099  foreach {tn select} {
002100    1  { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET 'hello' } 
002101    2  { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET NULL } 
002102    3  { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET X'ABCD' } 
002103    4  { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET 5.1 } 
002104    5  { SELECT b FROM f1 ORDER BY a 
002105         LIMIT 2 OFFSET (SELECT group_concat(b) FROM f1) 
002106    } 
002107  } {
002108    do_catchsql_test e_select-9.7.$tn $select {1 {datatype mismatch}}
002109  }
002110  
002111  # EVIDENCE-OF: R-20467-43422 If an expression has an OFFSET clause, then
002112  # the first M rows are omitted from the result set returned by the
002113  # SELECT statement and the next N rows are returned, where M and N are
002114  # the values that the OFFSET and LIMIT clauses evaluate to,
002115  # respectively.
002116  #
002117  do_select_tests e_select-9.8 {
002118    1  { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 5} {f g h i j k l m n o}
002119    2  { SELECT b FROM f1 ORDER BY a LIMIT 2+3 OFFSET 10} {k l m n o}
002120    3  { SELECT b FROM f1 ORDER BY a 
002121         LIMIT  (SELECT a FROM f1 WHERE b='j') 
002122         OFFSET (SELECT a FROM f1 WHERE b='b') 
002123       } {c d e f g h i j k l}
002124    4  { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 3.0 } {d e f g h}
002125    5  { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 0 } {a b c d e}
002126    6  { SELECT b FROM f1 ORDER BY a LIMIT 0 OFFSET 10 } {}
002127    7  { SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET '1'||'5' } {p q r}
002128  }
002129  
002130  # EVIDENCE-OF: R-34648-44875 Or, if the SELECT would return less than
002131  # M+N rows if it did not have a LIMIT clause, then the first M rows are
002132  # skipped and the remaining rows (if any) are returned.
002133  #
002134  do_select_tests e_select-9.9 {
002135    1  { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 20} {u v w x y z}
002136    2  { SELECT a FROM f1 ORDER BY a DESC LIMIT 100 OFFSET 18+4} {4 3 2 1}
002137  }
002138  
002139  
002140  # EVIDENCE-OF: R-23293-62447 If the OFFSET clause evaluates to a
002141  # negative value, the results are the same as if it had evaluated to
002142  # zero.
002143  #
002144  do_select_tests e_select-9.10 {
002145    1  { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -1 } {a b c d e}
002146    2  { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -500 } {a b c d e}
002147    3  { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 0  } {a b c d e}
002148  } 
002149  
002150  # EVIDENCE-OF: R-19509-40356 Instead of a separate OFFSET clause, the
002151  # LIMIT clause may specify two scalar expressions separated by a comma.
002152  #
002153  # EVIDENCE-OF: R-33788-46243 In this case, the first expression is used
002154  # as the OFFSET expression and the second as the LIMIT expression.
002155  #
002156  do_select_tests e_select-9.11 {
002157    1  { SELECT b FROM f1 ORDER BY a LIMIT 5, 10 } {f g h i j k l m n o}
002158    2  { SELECT b FROM f1 ORDER BY a LIMIT 10, 2+3 } {k l m n o}
002159    3  { SELECT b FROM f1 ORDER BY a 
002160         LIMIT (SELECT a FROM f1 WHERE b='b'), (SELECT a FROM f1 WHERE b='j') 
002161       } {c d e f g h i j k l}
002162    4  { SELECT b FROM f1 ORDER BY a LIMIT 3.0, '5' } {d e f g h}
002163    5  { SELECT b FROM f1 ORDER BY a LIMIT 0, '5' } {a b c d e}
002164    6  { SELECT b FROM f1 ORDER BY a LIMIT 10, 0 } {}
002165    7  { SELECT b FROM f1 ORDER BY a LIMIT '1'||'5', 3 } {p q r}
002166  
002167    8  { SELECT b FROM f1 ORDER BY a LIMIT 20, 10 } {u v w x y z}
002168    9  { SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 } {4 3 2 1}
002169  
002170    10 { SELECT b FROM f1 ORDER BY a LIMIT -1, 5 } {a b c d e}
002171    11 { SELECT b FROM f1 ORDER BY a LIMIT -500, 5 } {a b c d e}
002172    12 { SELECT b FROM f1 ORDER BY a LIMIT 0, 5 } {a b c d e}
002173  }
002174  
002175  finish_test