Index: test/e_select.test ================================================================== --- test/e_select.test +++ test/e_select.test @@ -331,13 +331,13 @@ 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 { @@ -355,14 +355,14 @@ 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. @@ -381,14 +381,14 @@ # # 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. # @@ -511,15 +511,14 @@ 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] \ @@ -538,26 +537,26 @@ {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} } @@ -564,12 +563,12 @@ # 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); @@ -620,14 +619,13 @@ %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); @@ -658,12 +656,12 @@ {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 { Index: test/e_select2.test ================================================================== --- test/e_select2.test +++ test/e_select2.test @@ -342,14 +342,14 @@ 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-25071-21202 The "CROSS JOIN" join operator produces the @@ -366,14 +366,14 @@ 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} @@ -502,18 +502,18 @@ 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-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-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-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%} Index: test/printf2.test ================================================================== --- test/printf2.test +++ test/printf2.test @@ -57,11 +57,11 @@ } {314159} do_execsql_test printf2-1.11 { SELECT printf('%lld%n',314159.2653,'hi'); } {314159} -# EVIDENCE-OF: R-20555-31089 The %z format is interchangable with %s. +# EVIDENCE-OF: R-17002-27534 The %z format is interchangeable with %s. # do_execsql_test printf2-1.12 { SELECT printf('%.*z',5,'abcdefghijklmnop'); } {abcde} do_execsql_test printf2-1.13 {