Import SQLite 3.8.7.4.

third_party/sqlite/src/test/e_select2.test

 # 2010 September 24
 #
 # 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.
 #
 #***********************************************************************
@@ skipping 326 matching lines @@
   e_select-2.1.2 { CREATE INDEX i1 ON t1(a) }
   e_select-2.1.3 { CREATE INDEX i1 ON t2(a) }
   e_select-2.1.4 { CREATE INDEX i1 ON t3(b) }
 } {
 
   catchsql { DROP INDEX i1 }
   catchsql { DROP INDEX i2 }
   catchsql { DROP INDEX i3 }
   execsql $indexes
 
-  # 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.
   #
   # 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
   #
   test_join $tn.1.1  "t1, t2"                {t1 t2}
   test_join $tn.1.2  "t1 INNER JOIN t2"      {t1 t2}
   test_join $tn.1.3  "t1 CROSS JOIN t2"      {t1 t2}
   test_join $tn.1.4  "t1 JOIN t2"            {t1 t2}
   test_join $tn.1.5  "t2, t3"                {t2 t3}
   test_join $tn.1.6  "t2 INNER JOIN t3"      {t2 t3}
   test_join $tn.1.7  "t2 CROSS JOIN t3"      {t2 t3}
   test_join $tn.1.8  "t2 JOIN t3"            {t2 t3}
   test_join $tn.1.9  "t2, t2 AS x"           {t2 t2}
   test_join $tn.1.10 "t2 INNER JOIN t2 AS x" {t2 t2}
   test_join $tn.1.11 "t2 CROSS JOIN t2 AS x" {t2 t2}
   test_join $tn.1.12 "t2 JOIN t2 AS x"       {t2 t2}
 
-  # 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.
   #
   test_join $tn.2.1  "t1, t2 ON (t1.a=t2.a)"  {t1 t2 -on {te_equals a a}}
   test_join $tn.2.2  "t2, t1 ON (t1.a=t2.a)"  {t2 t1 -on {te_equals a a}}
   test_join $tn.2.3  "t2, t1 ON (1)"          {t2 t1 -on te_true}
   test_join $tn.2.4  "t2, t1 ON (NULL)"       {t2 t1 -on te_false}
   test_join $tn.2.5  "t2, t1 ON (1.1-1.1)"    {t2 t1 -on te_false}
   test_join $tn.2.6  "t1, t2 ON (1.1-1.0)"    {t1 t2 -on te_true}
 
 
   test_join $tn.3 "t1 LEFT JOIN t2 ON (t1.a=t2.a)" {t1 t2 -left -on {te_equals a a}}
@@ skipping 112 matching lines @@
 
 do_execsql_test e_select-2.2.0 {
   CREATE TABLE t4(x TEXT COLLATE nocase);
   CREATE TABLE t5(y INTEGER, z TEXT COLLATE binary);
 
   INSERT INTO t4 VALUES('2.0');
   INSERT INTO t4 VALUES('TWO');
   INSERT INTO t5 VALUES(2, 'two');
 } {}
 
-# EVIDENCE-OF: R-55824-40976 A sub-select specified in the join-source
-# following the FROM clause in a simple SELECT statement is handled as
-# if it was a table containing the data returned by executing the
-# sub-select statement.
+# EVIDENCE-OF: R-59237-46742 A subquery specified in the
+# table-or-subquery following the FROM clause in a simple SELECT
+# statement is handled as if it was a table containing the data returned
+# by executing the subquery statement.
 #
-# EVIDENCE-OF: R-42612-06757 Each column of the sub-select dataset
-# inherits the collation sequence and affinity of the corresponding
-# expression in the sub-select statement.
+# EVIDENCE-OF: R-27438-53558 Each column of the subquery has the
+# collation sequence and affinity of the corresponding expression in the
+# subquery statement.
 #
 foreach {tn subselect select spec} {
   1   "SELECT * FROM t2"   "SELECT * FROM t1 JOIN %ss%" 
       {t1 %ss%}
 
   2   "SELECT * FROM t2"   "SELECT * FROM t1 JOIN %ss% AS x ON (t1.a=x.a)" 
       {t1 %ss% -on {te_equals 0 0}}
 
   3   "SELECT * FROM t2"   "SELECT * FROM %ss% AS x JOIN t1 ON (t1.a=x.a)" 
       {%ss% t1 -on {te_equals 0 0}}
@@ skipping 46 matching lines @@
 
   # Check that the actual data returned by the $select query is the same
   # as the expected data calculated using [te_tbljoin] above.
   #
   te_dataset_eq_unordered e_select-2.2.1.$tn [
     te_read_sql db [string map [list %ss% "($subselect)"] $select]
   ] $te
 }
 
 finish_test