Import SQLite 3.8.7.4.

third_party/sqlite/src/test/e_select.test

 # 2010 July 16
 #
 # The author disclaims copyright to this source code.  In place of
 # a legal notice, here is a blessing:
 #
 #    May you do good and not evil.
 #    May you find forgiveness for yourself and forgive others.
 #    May you share freely, never taking more than you give.
 #
 #***********************************************************************
 #
 # This file implements tests to verify that the "testable statements" in 
 # the lang_select.html document are correct.
 #
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
 
+ifcapable !compound {
+  finish_test
+  return
+}
+
 do_execsql_test e_select-1.0 {
   CREATE TABLE t1(a, b);
   INSERT INTO t1 VALUES('a', 'one');
   INSERT INTO t1 VALUES('b', 'two');
   INSERT INTO t1 VALUES('c', 'three');
 
   CREATE TABLE t2(a, b);
   INSERT INTO t2 VALUES('a', 'I');
   INSERT INTO t2 VALUES('b', 'II');
   INSERT INTO t2 VALUES('c', 'III');
@@ skipping 42 matching lines @@
   foreach {tn2 joinop} [list    1 ,    2 "CROSS JOIN"    3 "INNER JOIN"] {
     set S [string map [list %JOIN% $joinop] $select]
     uplevel do_execsql_test $tn.$tn2 [list $S] [list $res]
   }
 }
 
 #-------------------------------------------------------------------------
 # The following tests check that all paths on the syntax diagrams on
 # the lang_select.html page may be taken.
 #
-# EVIDENCE-OF: R-18428-22111 -- syntax diagram join-constraint
+# -- syntax diagram join-constraint
 #
 do_join_test e_select-0.1.1 {
   SELECT count(*) FROM t1 %JOIN% t2 ON (t1.a=t2.a)
 } {3}
 do_join_test e_select-0.1.2 {
   SELECT count(*) FROM t1 %JOIN% t2 USING (a)
 } {3}
 do_join_test e_select-0.1.3 {
   SELECT count(*) FROM t1 %JOIN% t2
 } {9}
 do_catchsql_test e_select-0.1.4 {
   SELECT count(*) FROM t1, t2 ON (t1.a=t2.a) USING (a)
 } {1 {cannot have both ON and USING clauses in the same join}}
 do_catchsql_test e_select-0.1.5 {
   SELECT count(*) FROM t1, t2 USING (a) ON (t1.a=t2.a)
 } {1 {near "ON": syntax error}}
 
-# EVIDENCE-OF: R-44854-11739 -- syntax diagram select-core
+# -- syntax diagram select-core
 #
 #   0: SELECT ...
 #   1: SELECT DISTINCT ...
 #   2: SELECT ALL ...
 #
 #   0: No FROM clause
 #   1: Has FROM clause
 #
 #   0: No WHERE clause
 #   1: Has WHERE clause
@@ skipping 104 matching lines @@
           {1 c 1 b}
   2112.1  "SELECT ALL count(*), max(a) FROM t1 WHERE a!='b'
            GROUP BY b HAVING count(*)=1" {
     1 a 1 c
   }
   2112.2  "SELECT ALL count(*), max(a) FROM t1 
            WHERE 0 GROUP BY b HAVING count(*)=2" { }
 }
 
 
-# EVIDENCE-OF: R-23316-20169 -- syntax diagram result-column
+# -- syntax diagram result-column
 #
 do_select_tests e_select-0.3 {
   1  "SELECT * FROM t1" {a one b two c three}
   2  "SELECT t1.* FROM t1" {a one b two c three}
   3  "SELECT 'x'||a||'x' FROM t1" {xax xbx xcx}
   4  "SELECT 'x'||a||'x' alias FROM t1" {xax xbx xcx}
   5  "SELECT 'x'||a||'x' AS alias FROM t1" {xax xbx xcx}
 }
 
-# EVIDENCE-OF: R-41233-21397 -- syntax diagram join-source
+# -- syntax diagram join-source
 #
-# EVIDENCE-OF: R-45040-11121 -- syntax diagram join-op
+# -- syntax diagram join-op
 #
 do_select_tests e_select-0.4 {
   1  "SELECT t1.rowid FROM t1" {1 2 3}
   2  "SELECT t1.rowid FROM t1,t2" {1 1 1 2 2 2 3 3 3}
   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}
 
   4  "SELECT t1.rowid FROM t1" {1 2 3}
   5  "SELECT t1.rowid FROM t1 JOIN t2" {1 1 1 2 2 2 3 3 3}
   6  "SELECT t1.rowid FROM t1 JOIN t2 JOIN t3" 
      {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3}
 
   7  "SELECT t1.rowid FROM t1 NATURAL JOIN t3" {1 2}
   8  "SELECT t1.rowid FROM t1 NATURAL LEFT OUTER JOIN t3" {1 2 3}
   9  "SELECT t1.rowid FROM t1 NATURAL LEFT JOIN t3" {1 2 3}
   10 "SELECT t1.rowid FROM t1 NATURAL INNER JOIN t3" {1 2}
   11 "SELECT t1.rowid FROM t1 NATURAL CROSS JOIN t3" {1 2}
 
   12 "SELECT t1.rowid FROM t1 JOIN t3" {1 1 2 2 3 3}
   13 "SELECT t1.rowid FROM t1 LEFT OUTER JOIN t3" {1 1 2 2 3 3}
   14 "SELECT t1.rowid FROM t1 LEFT JOIN t3" {1 1 2 2 3 3}
   15 "SELECT t1.rowid FROM t1 INNER JOIN t3" {1 1 2 2 3 3}
   16 "SELECT t1.rowid FROM t1 CROSS JOIN t3" {1 1 2 2 3 3}
 }
 
-# EVIDENCE-OF: R-56911-63533 -- syntax diagram compound-operator
+# -- syntax diagram compound-operator
 #
 do_select_tests e_select-0.5 {
   1  "SELECT rowid FROM t1 UNION ALL SELECT rowid+2 FROM t4" {1 2 3 3 4}
   2  "SELECT rowid FROM t1 UNION     SELECT rowid+2 FROM t4" {1 2 3 4}
   3  "SELECT rowid FROM t1 INTERSECT SELECT rowid+2 FROM t4" {3}
   4  "SELECT rowid FROM t1 EXCEPT    SELECT rowid+2 FROM t4" {1 2}
 }
 
-# EVIDENCE-OF: R-60388-27458 -- syntax diagram ordering-term
+# -- syntax diagram ordering-term
 #
 do_select_tests e_select-0.6 {
   1  "SELECT b||a FROM t1 ORDER BY b||a"                  {onea threec twob}
   2  "SELECT b||a FROM t1 ORDER BY (b||a) COLLATE nocase" {onea threec twob}
   3  "SELECT b||a FROM t1 ORDER BY (b||a) ASC"            {onea threec twob}
   4  "SELECT b||a FROM t1 ORDER BY (b||a) DESC"           {twob threec onea}
 }
 
-# EVIDENCE-OF: R-36494-33519 -- syntax diagram select-stmt
+# -- syntax diagram select-stmt
 #
 do_select_tests e_select-0.7 {
   1  "SELECT * FROM t1" {a one b two c three}
   2  "SELECT * FROM t1 ORDER BY b" {a one c three b two}
   3  "SELECT * FROM t1 ORDER BY b, a" {a one c three b two}
 
   4  "SELECT * FROM t1 LIMIT 10" {a one b two c three}
   5  "SELECT * FROM t1 LIMIT 10 OFFSET 5" {}
   6  "SELECT * FROM t1 LIMIT 10, 5" {}
 
@@ skipping 31 matching lines @@
 #
 do_select_tests e_select-1.1 {
   1 "SELECT 'abc'"            {abc}
   2 "SELECT 'abc' WHERE NULL" {}
   3 "SELECT NULL"             {{}}
   4 "SELECT count(*)"         {1}
   5 "SELECT count(*) WHERE 0" {0}
   6 "SELECT count(*) WHERE 1" {1}
 }
 
-# EVIDENCE-OF: R-48114-33255 If there is only a single table in the
-# join-source following the FROM clause, then the input data used by the
-# SELECT statement is the contents of the named table.
+# EVIDENCE-OF: R-45424-07352 If there is only a single table or subquery
+# in the FROM clause, then the input data used by the SELECT statement
+# is the contents of the named table.
 #
 #   The results of the SELECT queries suggest that they are operating on the
 #   contents of the table 'xx'.
 #
 do_execsql_test e_select-1.2.0 {
   CREATE TABLE xx(x, y);
   INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2');
   INSERT INTO xx VALUES(NULL, -16.87);
   INSERT INTO xx VALUES(-17.89, 'linguistically');
 } {}
 do_select_tests e_select-1.2 {
   1  "SELECT quote(x), quote(y) FROM xx" {
      'IiJlsIPepMuAhU' X'10B00B897A15BAA02E3F98DCE8F2' 
      NULL             -16.87                          
      -17.89           'linguistically'                
   }
 
   2  "SELECT count(*), count(x), count(y) FROM xx" {3 2 3}
   3  "SELECT sum(x), sum(y) FROM xx"               {-17.89 -16.87}
 }
 
-# EVIDENCE-OF: R-23593-12456 If there is more than one table specified
-# as part of the join-source following the FROM keyword, then the
-# contents of each named table are joined into a single dataset for the
-# simple SELECT statement to operate on.
+# EVIDENCE-OF: R-28355-09804 If there is more than one table or subquery
+# in FROM clause then the contents of all tables and/or subqueries are
+# joined into a single dataset for the simple SELECT statement to
+# operate on.
 #
 #   There are more detailed tests for subsequent requirements that add 
 #   more detail to this idea. We just add a single test that shows that
 #   data is coming from each of the three tables following the FROM clause
 #   here to show that the statement, vague as it is, is not incorrect.
 #
 do_select_tests e_select-1.3 {
   1 "SELECT * FROM t1, t2, t3" {
       a one a I a 1 a one a I b 2 a one b II a 1 
       a one b II b 2 a one c III a 1 a one c III b 2 
       b two a I a 1 b two a I b 2 b two b II a 1 
       b two b II b 2 b two c III a 1 b two c III b 2 
       c three a I a 1 c three a I b 2 c three b II a 1 
       c three b II b 2 c three c III a 1 c three c III b 2
   }
 }
 
 #
 # The following block of tests - e_select-1.4.* - test that the description
 # of cartesian joins in the SELECT documentation is consistent with SQLite.
 # In doing so, we test the following three requirements as a side-effect:
 #
-# EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER
-# JOIN", "JOIN" or a comma (",") and there is no ON or USING clause,
-# then the result of the join is simply the cartesian product of the
-# left and right-hand datasets.
+# EVIDENCE-OF: R-49872-03192 If the join-operator is "CROSS JOIN",
+# "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING
+# clause, then the result of the join is simply the cartesian product of
+# the left and right-hand datasets.
 #
 #    The tests are built on this assertion. Really, they test that the output
 #    of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result
 #    of calculating the cartesian product of the left and right-hand datasets. 
 #
 # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
 # JOIN", "JOIN" and "," join operators.
 #
-# EVIDENCE-OF: R-07544-24155 The "CROSS JOIN" join operator produces the
-# same data as the "INNER JOIN", "JOIN" and "," operators
+# EVIDENCE-OF: R-25071-21202 The "CROSS JOIN" join operator produces the
+# same result as the "INNER JOIN", "JOIN" and "," operators
 #
 #    All tests are run 4 times, with the only difference in each run being
 #    which of the 4 equivalent cartesian product join operators are used.
 #    Since the output data is the same in all cases, we consider that this
 #    qualifies as testing the two statements above.
 #
 do_execsql_test e_select-1.4.0 {
   CREATE TABLE x1(a, b);
   CREATE TABLE x2(c, d, e);
   CREATE TABLE x3(f, g, h, i);
@@ skipping 33 matching lines @@
 
 do_join_test e_select-1.4.1.4 {
   SELECT * FROM x2 %JOIN% x3 LIMIT 1
 } [concat {-60.06 {} {}} {-39.24 {} encompass -1}]
 
 # EVIDENCE-OF: R-44414-54710 There is a row in the cartesian product
 # dataset formed by combining each unique combination of a row from the
 # left-hand and right-hand datasets.
 #
 do_join_test e_select-1.4.2.1 {
-  SELECT * FROM x2 %JOIN% x3
+  SELECT * FROM x2 %JOIN% x3 ORDER BY +c, +f
 } [list -60.06 {} {}      -39.24 {} encompass -1                 \
+        -60.06 {} {}      alerting {} -93.79 {}                  \
+        -60.06 {} {}      coldest -96 dramatists 82.3            \
+        -60.06 {} {}      conducting -87.24 37.56 {}             \
         -60.06 {} {}      presenting 51 reformation dignified    \
-        -60.06 {} {}      conducting -87.24 37.56 {}             \
-        -60.06 {} {}      coldest -96 dramatists 82.3            \
-        -60.06 {} {}      alerting {} -93.79 {}                  \
         -58 {} 1.21       -39.24 {} encompass -1                 \
+        -58 {} 1.21       alerting {} -93.79 {}                  \
+        -58 {} 1.21       coldest -96 dramatists 82.3            \
+        -58 {} 1.21       conducting -87.24 37.56 {}             \
         -58 {} 1.21       presenting 51 reformation dignified    \
-        -58 {} 1.21       conducting -87.24 37.56 {}             \
-        -58 {} 1.21       coldest -96 dramatists 82.3            \
-        -58 {} 1.21       alerting {} -93.79 {}                  \
 ]
 # TODO: Come back and add a few more like the above.
 
-# EVIDENCE-OF: R-20659-43267 In other words, if the left-hand dataset
-# consists of Nlhs rows of Mlhs columns, and the right-hand dataset of
-# Nrhs rows of Mrhs columns, then the cartesian product is a dataset of
-# Nlhs.Nrhs rows, each containing Mlhs+Mrhs columns.
+# EVIDENCE-OF: R-18439-38548 In other words, if the left-hand dataset
+# consists of Nleft rows of Mleft columns, and the right-hand dataset of
+# Nright rows of Mright columns, then the cartesian product is a dataset
+# of Nleft×Nright rows, each containing Mleft+Mright columns.
 #
 # x1, x2    (Nlhs=3, Nrhs=2)   (Mlhs=2, Mrhs=3)
 do_join_test e_select-1.4.3.1 { 
   SELECT count(*) FROM x1 %JOIN% x2 
 } [expr 3*2]
 do_test e_select-1.4.3.2 { 
   expr {[llength [execsql {SELECT * FROM x1, x2}]] / 6}
 } [expr 2+3]
 
 # x2, x3    (Nlhs=2, Nrhs=5)   (Mlhs=3, Mrhs=4)
@@ skipping 25 matching lines @@
 do_execsql_test e_select-1.4.4.1 { SELECT * FROM t1, t2 } $t1_cross_t2
 do_execsql_test e_select-1.4.4.2 { SELECT * FROM t1 AS x, t1 AS y} $t1_cross_t1
 
 do_select_tests e_select-1.4.5 [list                                   \
     1 { SELECT * FROM t1 CROSS JOIN t2 }           $t1_cross_t2        \
     2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1        \
     3 { SELECT * FROM t1 INNER JOIN t2 }           $t1_cross_t2        \
     4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1        \
 ]
 
-
-# EVIDENCE-OF: R-22775-56496 If there is an ON clause specified, then
-# the ON expression is evaluated for each row of the cartesian product
-# as a boolean expression. All rows for which the expression evaluates
-# to false are excluded from the dataset.
+# EVIDENCE-OF: R-38465-03616 If there is an ON clause then the ON
+# expression is evaluated for each row of the cartesian product as a
+# boolean expression. Only rows for which the expression evaluates to
+# true are included from the dataset.
 #
 foreach {tn select res} [list                                              \
     1 { SELECT * FROM t1 %JOIN% t2 ON (1) }       $t1_cross_t2             \
     2 { SELECT * FROM t1 %JOIN% t2 ON (0) }       [list]                   \
     3 { SELECT * FROM t1 %JOIN% t2 ON (NULL) }    [list]                   \
     4 { SELECT * FROM t1 %JOIN% t2 ON ('abc') }   [list]                   \
     5 { SELECT * FROM t1 %JOIN% t2 ON ('1ab') }   $t1_cross_t2             \
     6 { SELECT * FROM t1 %JOIN% t2 ON (0.9) }     $t1_cross_t2             \
     7 { SELECT * FROM t1 %JOIN% t2 ON ('0.9') }   $t1_cross_t2             \
     8 { SELECT * FROM t1 %JOIN% t2 ON (0.0) }     [list]                   \
                                                                            \
     9 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = t2.a) }             \
       {one I two II three III}                                             \
    10 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = 'a') }              \
       {one I one II one III}                                               \
    11 { SELECT t1.b, t2.b 
         FROM t1 %JOIN% t2 ON (CASE WHEN t1.a = 'a' THEN NULL ELSE 1 END) } \
       {two I two II two III three I three II three III}                    \
 ] {
   do_join_test e_select-1.3.$tn $select $res
 }
 
-# EVIDENCE-OF: R-63358-54862 If there is a USING clause specified as
-# part of the join-constraint, then each of the column names specified
-# must exist in the datasets to both the left and right of the join-op.
+# EVIDENCE-OF: R-49933-05137 If there is a USING clause then each of the
+# column names specified must exist in the datasets to both the left and
+# right of the join-operator.
 #
 do_select_tests e_select-1.4 -error {
   cannot join using column %s - column not present in both tables
 } {
   1 { SELECT * FROM t1, t3 USING (b) }   "b"
   2 { SELECT * FROM t3, t1 USING (c) }   "c"
   3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) }   "a"
 } 
 
-# EVIDENCE-OF: R-55987-04584 For each pair of namesake columns, the
+# EVIDENCE-OF: R-22776-52830 For each pair of named columns, the
 # expression "lhs.X = rhs.X" is evaluated for each row of the cartesian
-# product as a boolean expression. All rows for which one or more of the
-# expressions evaluates to false are excluded from the result set.
+# product as a boolean expression. Only rows for which all such
+# expressions evaluates to true are included from the result set.
 #
 do_select_tests e_select-1.5 {
   1 { SELECT * FROM t1, t3 USING (a)   }  {a one 1 b two 2}
   2 { SELECT * FROM t3, t4 USING (a,c) }  {b 2}
 } 
 
 # EVIDENCE-OF: R-54046-48600 When comparing values as a result of a
 # USING clause, the normal rules for handling affinities, collation
 # sequences and NULL values in comparisons apply.
 #
-# EVIDENCE-OF: R-35466-18578 The column from the dataset on the
-# left-hand side of the join operator is considered to be on the
+# EVIDENCE-OF: R-38422-04402 The column from the dataset on the
+# left-hand side of the join-operator is considered to be on the
 # left-hand side of the comparison operator (=) for the purposes of
 # collation sequence and affinity precedence.
 #
 do_execsql_test e_select-1.6.0 {
   CREATE TABLE t5(a COLLATE nocase, b COLLATE binary);
   INSERT INTO t5 VALUES('AA', 'cc');
   INSERT INTO t5 VALUES('BB', 'dd');
   INSERT INTO t5 VALUES(NULL, NULL);
   CREATE TABLE t6(a COLLATE binary, b COLLATE nocase);
   INSERT INTO t6 VALUES('aa', 'cc');
@@ skipping 34 matching lines @@
 
   4a { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x 
        %JOIN% t5 USING (a) } 
      {aa cc cc bb DD dd}
   4b { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x
        %JOIN% t5 ON (x.a=t5.a) } 
      {aa cc AA cc bb DD BB dd}
 } {
   do_join_test e_select-1.7.$tn $select $res
 }
-
-# EVIDENCE-OF: R-41434-12448 If the join-op is a "LEFT JOIN" or "LEFT
-# OUTER JOIN", then after the ON or USING filtering clauses have been
-# applied, an extra row is added to the output for each row in the
+# EVIDENCE-OF: R-42531-52874 If the join-operator is a "LEFT JOIN" or
+# "LEFT OUTER JOIN", then after the ON or USING filtering clauses have
+# been applied, an extra row is added to the output for each row in the
 # original left-hand input dataset that corresponds to no rows at all in
 # the composite dataset (if any).
 #
 do_execsql_test e_select-1.8.0 {
   CREATE TABLE t7(a, b, c);
   CREATE TABLE t8(a, d, e);
 
   INSERT INTO t7 VALUES('x', 'ex',  24);
   INSERT INTO t7 VALUES('y', 'why', 25);
 
@@ skipping 14 matching lines @@
 # input dataset.
 #
 do_select_tests e_select-1.9 {
   1a "SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)" {x ex 24 x abc 24}
   1b "SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" 
      {x ex 24 x abc 24 y why 25 {} {} {}}
   2a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24}
   2b "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}}
 }
 
-# EVIDENCE-OF: R-01809-52134 If the NATURAL keyword is added to any of
-# the join-ops, then an implicit USING clause is added to the
+# EVIDENCE-OF: R-04932-55942 If the NATURAL keyword is in the
+# join-operator then an implicit USING clause is added to the
 # join-constraints. The implicit USING clause contains each of the
 # column names that appear in both the left and right-hand input
 # datasets.
 #
 do_select_tests e_select-1-10 {
   1a "SELECT * FROM t7 JOIN t8 USING (a)"        {x ex 24 abc 24}
   1b "SELECT * FROM t7 NATURAL JOIN t8"          {x ex 24 abc 24}
 
   2a "SELECT * FROM t8 JOIN t7 USING (a)"        {x abc 24 ex 24}
   2b "SELECT * FROM t8 NATURAL JOIN t7"          {x abc 24 ex 24}
@@ skipping 52 matching lines @@
   INSERT INTO x1 VALUES(5, NULL,           96.28,     NULL);
   INSERT INTO x1 VALUES(6, 0,              1,         2);
 
   CREATE TABLE x2(k, x, y2);
   INSERT INTO x2 VALUES(1, 50, X'B82838');
   INSERT INTO x2 VALUES(5, 84.79, 65.88);
   INSERT INTO x2 VALUES(3, -22, X'0E1BE452A393');
   INSERT INTO x2 VALUES(7, 'mistrusted', 'standardized');
 } {}
 
-# EVIDENCE-OF: R-06999-14330 If a WHERE clause is specified, the WHERE
+# EVIDENCE-OF: R-60775-64916 If a WHERE clause is specified, the WHERE
 # expression is evaluated for each row in the input data as a boolean
-# expression. All rows for which the WHERE clause expression evaluates
-# to false are excluded from the dataset before continuing.
+# expression. Only rows for which the WHERE clause expression evaluates
+# to true are included from the dataset before continuing.
 #
 do_execsql_test e_select-3.1.1 { SELECT k FROM x1 WHERE x }         {3}
 do_execsql_test e_select-3.1.2 { SELECT k FROM x1 WHERE y }         {3 5 6}
 do_execsql_test e_select-3.1.3 { SELECT k FROM x1 WHERE z }         {1 2 6}
 do_execsql_test e_select-3.1.4 { SELECT k FROM x1 WHERE '1'||z    } {1 2 4 6}
 do_execsql_test e_select-3.1.5 { SELECT k FROM x1 WHERE x IS NULL } {4 5}
 do_execsql_test e_select-3.1.6 { SELECT k FROM x1 WHERE z - 78.43 } {2 4 6}
 
 do_execsql_test e_select-3.2.1a {
   SELECT k FROM x1 LEFT JOIN x2 USING(k)
@@ skipping 47 matching lines @@
 #
 do_select_tests e_select-4.1 {
   1  "SELECT * FROM z1 LIMIT 1"             {51.65 -59.58 belfries}
   2  "SELECT * FROM z1,z2 LIMIT 1"          {51.65 -59.58 belfries {} 21}
   3  "SELECT z1.* FROM z1,z2 LIMIT 1"       {51.65 -59.58 belfries}
   4  "SELECT z2.* FROM z1,z2 LIMIT 1"       {{} 21}
   5  "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries}
 
   6  "SELECT count(*), * FROM z1"           {6 63 born -26}
   7  "SELECT max(a), * FROM z1"             {63 63 born -26}
-  8  "SELECT *, min(a) FROM z1"             {63 born -26 -5}
+  8  "SELECT *, min(a) FROM z1"             {-5 {} 75 -5}
 
   9  "SELECT *,* FROM z1,z2 LIMIT 1" {        
      51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21
   }
   10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" {        
      51.65 -59.58 belfries 51.65 -59.58 belfries
   }
 }
 
-# EVIDENCE-OF: R-61869-22578 It is an error to use a "*" or "alias.*"
-# expression in any context other than than a result expression list.
+# EVIDENCE-OF: R-38023-18396 It is an error to use a "*" or "alias.*"
+# expression in any context other than a result expression list.
 #
 # EVIDENCE-OF: R-44324-41166 It is also an error to use a "*" or
 # "alias.*" expression in a simple SELECT query that does not have a
 # FROM clause.
 #
 foreach {tn select err} {
   1.1  "SELECT a, b, c FROM z1 WHERE *"    {near "*": syntax error}
   1.2  "SELECT a, b, c FROM z1 GROUP BY *" {near "*": syntax error}
   1.3  "SELECT 1 + * FROM z1"              {near "*": syntax error}
   1.4  "SELECT * + 1 FROM z1"              {near "+": syntax error}
@@ skipping 172 matching lines @@
   INSERT INTO b2 VALUES('abc', 3);
   INSERT INTO b2 VALUES('xyz', 4);
 
   CREATE TABLE b3(a COLLATE nocase, b COLLATE binary);
   INSERT INTO b3 VALUES('abc', 'abc');
   INSERT INTO b3 VALUES('aBC', 'aBC');
   INSERT INTO b3 VALUES('Def', 'Def');
   INSERT INTO b3 VALUES('dEF', 'dEF');
 } {}
 
-# EVIDENCE-OF: R-57754-57109 If the SELECT statement is an aggregate
+# EVIDENCE-OF: R-07284-35990 If the SELECT statement is an aggregate
 # query with a GROUP BY clause, then each of the expressions specified
 # as part of the GROUP BY clause is evaluated for each row of the
 # dataset. Each row is then assigned to a "group" based on the results;
 # rows for which the results of evaluating the GROUP BY expressions are
-# the same are assigned to the same group.
+# the same get assigned to the same group.
 #
 #   These tests also show that the following is not untrue:
 #
 # EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do
 # not have to be expressions that appear in the result.
 #
 do_select_tests e_select-4.9 {
   1  "SELECT group_concat(one), two FROM b1 GROUP BY two" {
-    4,5 f   1 o   7,6   s 3,2 t
+    /#,# f   1 o   #,#   s #,# t/
   }
   2  "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
-    1,4,3,2 10    5,7,6 18
+    1,2,3,4 10    5,6,7 18
   }
   3  "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {
     4  1,5    2,6   3,7
   }
   4  "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" {
     4,3,5,7,6    1,2
   }
 }
 
 # EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL
 # values are considered equal.
 #
 do_select_tests e_select-4.10 {
-  1  "SELECT group_concat(y) FROM b2 GROUP BY x" {0,1   3   2,4}
+  1  "SELECT group_concat(y) FROM b2 GROUP BY x" {/#,#   3   #,#/}
   2  "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1}
 } 
 
 # EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation
 # sequence with which to compare text values apply when evaluating
 # expressions in a GROUP BY clause.
 #
 do_select_tests e_select-4.11 {
   1  "SELECT count(*) FROM b3 GROUP BY b"      {1 1 1 1}
   2  "SELECT count(*) FROM b3 GROUP BY a"      {2 2}
@@ skipping 165 matching lines @@
 # follow the SELECT keyword in a simple SELECT statement.
 #
 do_select_tests e_select-5.1 {
   1   "SELECT ALL a FROM h1"      {1 1 1 4 4 4}
   2   "SELECT DISTINCT a FROM h1" {1 4}
 }
 
 # EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then
 # the entire set of result rows are returned by the SELECT.
 #
-# EVIDENCE-OF: R-47911-02086 If neither ALL or DISTINCT are present,
-# then the behaviour is as if ALL were specified.
+# EVIDENCE-OF: R-01256-01950 If neither ALL or DISTINCT are present,
+# then the behavior is as if ALL were specified.
 #
 # EVIDENCE-OF: R-14442-41305 If the simple SELECT is a SELECT DISTINCT,
 # then duplicate rows are removed from the set of result rows before it
 # is returned.
 #
 #   The three testable statements above are tested by e_select-5.2.*,
 #   5.3.* and 5.4.* respectively.
 #
 do_select_tests e_select-5 {
   3.1 "SELECT ALL x FROM h2" {One Two Three Four one two three four}
   3.2 "SELECT ALL x FROM h1, h2 ON (x=b)" {One one Four four}
 
   3.1 "SELECT x FROM h2" {One Two Three Four one two three four}
   3.2 "SELECT x FROM h1, h2 ON (x=b)" {One one Four four}
 
-  4.1 "SELECT DISTINCT x FROM h2" {four one three two}
-  4.2 "SELECT DISTINCT x FROM h1, h2 ON (x=b)" {four one}
+  4.1 "SELECT DISTINCT x FROM h2" {One Two Three Four}
+  4.2 "SELECT DISTINCT x FROM h1, h2 ON (x=b)" {One Four}
 } 
 
 # EVIDENCE-OF: R-02054-15343 For the purposes of detecting duplicate
 # rows, two NULL values are considered to be equal.
 #
 do_select_tests e_select-5.5 {
   1  "SELECT DISTINCT d FROM h3" {{} 2 2,3 2,4 3}
 }
 
 # EVIDENCE-OF: R-58359-52112 The normal rules for selecting a collation
 # sequence to compare text values with apply.
 #
 do_select_tests e_select-5.6 {
-  1  "SELECT DISTINCT b FROM h1"                  {I IV four i iv one}
-  2  "SELECT DISTINCT b COLLATE nocase FROM h1"   {four i iv one}
-  3  "SELECT DISTINCT x FROM h2"                  {four one three two}
+  1  "SELECT DISTINCT b FROM h1"                  {one I i four IV iv}
+  2  "SELECT DISTINCT b COLLATE nocase FROM h1"   {one I four IV}
+  3  "SELECT DISTINCT x FROM h2"                  {One Two Three Four}
   4  "SELECT DISTINCT x COLLATE binary FROM h2"   {
-    Four One Three Two four one three two
+    One Two Three Four one two three four
   }
 }
 
 #-------------------------------------------------------------------------
 # The following tests - e_select-7.* - test that statements made to do
 # with compound SELECT statements are correct.
 #
 
 # EVIDENCE-OF: R-39368-64333 In a compound SELECT, all the constituent
 # SELECTs must return the same number of result columns.
@@ skipping 90 matching lines @@
       LIMIT {INTERSECT}
   23  "SELECT * FROM j1 LIMIT 10 OFFSET 5 INTERSECT SELECT * FROM j2,j3" 
       LIMIT {INTERSECT}
   24  "SELECT a FROM j1 LIMIT (SELECT e FROM j2) INTERSECT SELECT g FROM j2,j3" 
       LIMIT {INTERSECT}
 } {
   set err "$op1 clause should come after $op2 not before"
   do_catchsql_test e_select-7.2.$tn $select [list 1 $err]
 }
 
-# EVIDENCE-OF: R-22874-32655 ORDER BY and LIMIT clauses may only occur
-# at the end of the entire compound SELECT.
+# EVIDENCE-OF: R-45440-25633 ORDER BY and LIMIT clauses may only occur
+# at the end of the entire compound SELECT, and then only if the final
+# element of the compound is not a VALUES clause.
 #
 foreach {tn select} {
   1   "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 ORDER BY a"
   2   "SELECT count(*) FROM j1 UNION ALL SELECT max(e) FROM j2 ORDER BY 1"
   3   "SELECT count(*), * FROM j1 UNION ALL SELECT *,* FROM j2 ORDER BY 1,2,3"
   4   "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10" 
   5   "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
   6   "SELECT a FROM j1 UNION ALL SELECT g FROM j2,j3 LIMIT (SELECT 10)" 
 
   7   "SELECT * FROM j1 UNION SELECT * FROM j2,j3 ORDER BY a"
   8   "SELECT count(*) FROM j1 UNION SELECT max(e) FROM j2 ORDER BY 1"
+  8b  "VALUES('8b') UNION SELECT max(e) FROM j2 ORDER BY 1"
   9   "SELECT count(*), * FROM j1 UNION SELECT *,* FROM j2 ORDER BY 1,2,3"
   10  "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10" 
   11  "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
   12  "SELECT a FROM j1 UNION SELECT g FROM j2,j3 LIMIT (SELECT 10)" 
 
   13  "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 ORDER BY a"
   14  "SELECT count(*) FROM j1 EXCEPT SELECT max(e) FROM j2 ORDER BY 1"
   15  "SELECT count(*), * FROM j1 EXCEPT SELECT *,* FROM j2 ORDER BY 1,2,3"
   16  "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 LIMIT 10" 
   17  "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
   18  "SELECT a FROM j1 EXCEPT SELECT g FROM j2,j3 LIMIT (SELECT 10)" 
 
   19  "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 ORDER BY a"
   20  "SELECT count(*) FROM j1 INTERSECT SELECT max(e) FROM j2 ORDER BY 1"
   21  "SELECT count(*), * FROM j1 INTERSECT SELECT *,* FROM j2 ORDER BY 1,2,3"
   22  "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10" 
   23  "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 
   24  "SELECT a FROM j1 INTERSECT SELECT g FROM j2,j3 LIMIT (SELECT 10)" 
 } {
   do_test e_select-7.3.$tn { catch {execsql $select} msg } 0
 }
+foreach {tn select} {
+  50   "SELECT * FROM j1 ORDER BY 1 UNION ALL SELECT * FROM j2,j3"
+  51   "SELECT * FROM j1 LIMIT 1 UNION ALL SELECT * FROM j2,j3"
+  52   "SELECT count(*) FROM j1 UNION ALL VALUES(11) ORDER BY 1"
+  53   "SELECT count(*) FROM j1 UNION ALL VALUES(11) LIMIT 1"
+} {
+  do_test e_select-7.3.$tn { catch {execsql $select} msg } 1
+}
 
 # EVIDENCE-OF: R-08531-36543 A compound SELECT created using UNION ALL
 # operator returns all the rows from the SELECT to the left of the UNION
 # ALL operator, and all the rows from the SELECT to the right of it.
 #
 drop_all_tables
 do_execsql_test e_select-7.4.0 {
   CREATE TABLE q1(a TEXT, b INTEGER, c);
   CREATE TABLE q2(d NUMBER, e BLOB);
   CREATE TABLE q3(f REAL, g);
@@ skipping 318 matching lines @@
   }
   6  "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3 DESC" {
      2 4 93   2 5 -1     1 2 8      1 2 7    
      1 2 3    1 2 -20    1 4  93    1 5 -1   
   }
   7  "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3" {
      2 4 93   2 5 -1     1 2 -20    1 2 3    
      1 2 7    1 2 8      1 4  93    1 5 -1   
   }
   8  "SELECT z, x FROM d1 ORDER BY 2" {
-     3 1     8 1    7 1   -20 1 
-     93 1   -1 1   -1 2   93 2
+     /# 1    # 1    # 1   # 1 
+      # 1    # 1    # 2   # 2/
   }
   9  "SELECT z, x FROM d1 ORDER BY 1" {
-     -20 1  -1 2   -1 1   3 1     
-     7 1     8 1   93 2   93 1   
+     /-20 1  -1 #   -1 #   3 1
+     7 1     8 1   93 #   93 #/   
   }
 }
 
 # EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier
 # that corresponds to the alias of one of the output columns, then the
 # expression is considered an alias for that column.
 #
 do_select_tests e_select-8.5 {
   1   "SELECT z+1 AS abc FROM d1 ORDER BY abc" {
     -19 0 0 4 8 9 94 94
   }
   2   "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" {
     94 94 9 8 4 0 0 -19
   }
   3  "SELECT z AS x, x AS z FROM d1 ORDER BY z" {
-    3 1    8 1    7 1    -20 1    93 1    -1 1    -1 2    93 2
+    /# 1    # 1    # 1    # 1    # 1    # 1    # 2    # 2/
   }
   4  "SELECT z AS x, x AS z FROM d1 ORDER BY x" {
-    -20 1    -1 2    -1 1    3 1    7 1    8 1    93 2    93 1
+    /-20 1    -1 #    -1 #    3 1    7 1    8 1    93 #    93 #/
   }
 }
 
-# EVIDENCE-OF: R-27923-38747 Otherwise, if the ORDER BY expression is
-# any other expression, it is evaluated and the the returned value used
-# to order the output rows.
+# EVIDENCE-OF: R-65068-27207 Otherwise, if the ORDER BY expression is
+# any other expression, it is evaluated and the returned value used to
+# order the output rows.
 #
 # EVIDENCE-OF: R-03421-57988 If the SELECT statement is a simple SELECT,
 # then an ORDER BY may contain any arbitrary expressions.
 #
 do_select_tests e_select-8.6 {
   1   "SELECT * FROM d1 ORDER BY x+y+z" {
     1 2 -20    1 5 -1    1 2 3    2 5 -1 
     1 2 7      1 2 8     1 4 93   2 4 93
   }
   2   "SELECT * FROM d1 ORDER BY x*z" {
@@ skipping 369 matching lines @@
 
   8  { SELECT b FROM f1 ORDER BY a LIMIT 20, 10 } {u v w x y z}
   9  { SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 } {4 3 2 1}
 
   10 { SELECT b FROM f1 ORDER BY a LIMIT -1, 5 } {a b c d e}
   11 { SELECT b FROM f1 ORDER BY a LIMIT -500, 5 } {a b c d e}
   12 { SELECT b FROM f1 ORDER BY a LIMIT 0, 5 } {a b c d e}
 }
 
 finish_test