/ Check-in [be1acb61]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update some requirements marks to conform with improvements in the documentation.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: be1acb610f7e594b417dd8409b7a7aa8f3af5f77
User & Date: drh 2014-01-27 15:02:07
Context
2014-01-27
16:35
Fix a couple of problems with new test scripts causing the permutations test to fail. check-in: 48d736c0 user: dan tags: trunk
15:02
Update some requirements marks to conform with improvements in the documentation. check-in: be1acb61 user: drh tags: trunk
14:19
Fix a problem in pagerfault.test causing an assert() to fail. check-in: ba8d2d50 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/e_select.test.

   329    329     2 "SELECT 'abc' WHERE NULL" {}
   330    330     3 "SELECT NULL"             {{}}
   331    331     4 "SELECT count(*)"         {1}
   332    332     5 "SELECT count(*) WHERE 0" {0}
   333    333     6 "SELECT count(*) WHERE 1" {1}
   334    334   }
   335    335   
   336         -# EVIDENCE-OF: R-48114-33255 If there is only a single table in the
   337         -# join-source following the FROM clause, then the input data used by the
   338         -# SELECT statement is the contents of the named table.
          336  +# EVIDENCE-OF: R-45424-07352 If there is only a single table or subquery
          337  +# in the FROM clause, then the input data used by the SELECT statement
          338  +# is the contents of the named table.
   339    339   #
   340    340   #   The results of the SELECT queries suggest that they are operating on the
   341    341   #   contents of the table 'xx'.
   342    342   #
   343    343   do_execsql_test e_select-1.2.0 {
   344    344     CREATE TABLE xx(x, y);
   345    345     INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2');
................................................................................
   353    353        -17.89           'linguistically'                
   354    354     }
   355    355   
   356    356     2  "SELECT count(*), count(x), count(y) FROM xx" {3 2 3}
   357    357     3  "SELECT sum(x), sum(y) FROM xx"               {-17.89 -16.87}
   358    358   }
   359    359   
   360         -# EVIDENCE-OF: R-23593-12456 If there is more than one table specified
   361         -# as part of the join-source following the FROM keyword, then the
   362         -# contents of each named table are joined into a single dataset for the
   363         -# simple SELECT statement to operate on.
          360  +# EVIDENCE-OF: R-28355-09804 If there is more than one table or subquery
          361  +# in FROM clause then the contents of all tables and/or subqueries are
          362  +# joined into a single dataset for the simple SELECT statement to
          363  +# operate on.
   364    364   #
   365    365   #   There are more detailed tests for subsequent requirements that add 
   366    366   #   more detail to this idea. We just add a single test that shows that
   367    367   #   data is coming from each of the three tables following the FROM clause
   368    368   #   here to show that the statement, vague as it is, is not incorrect.
   369    369   #
   370    370   do_select_tests e_select-1.3 {
................................................................................
   379    379   }
   380    380   
   381    381   #
   382    382   # The following block of tests - e_select-1.4.* - test that the description
   383    383   # of cartesian joins in the SELECT documentation is consistent with SQLite.
   384    384   # In doing so, we test the following three requirements as a side-effect:
   385    385   #
   386         -# EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER
   387         -# JOIN", "JOIN" or a comma (",") and there is no ON or USING clause,
   388         -# then the result of the join is simply the cartesian product of the
   389         -# left and right-hand datasets.
          386  +# EVIDENCE-OF: R-49872-03192 If the join-operator is "CROSS JOIN",
          387  +# "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING
          388  +# clause, then the result of the join is simply the cartesian product of
          389  +# the left and right-hand datasets.
   390    390   #
   391    391   #    The tests are built on this assertion. Really, they test that the output
   392    392   #    of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result
   393    393   #    of calculating the cartesian product of the left and right-hand datasets. 
   394    394   #
   395    395   # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
   396    396   # JOIN", "JOIN" and "," join operators.
................................................................................
   509    509   do_select_tests e_select-1.4.5 [list                                   \
   510    510       1 { SELECT * FROM t1 CROSS JOIN t2 }           $t1_cross_t2        \
   511    511       2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1        \
   512    512       3 { SELECT * FROM t1 INNER JOIN t2 }           $t1_cross_t2        \
   513    513       4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1        \
   514    514   ]
   515    515   
   516         -
   517         -# EVIDENCE-OF: R-22775-56496 If there is an ON clause specified, then
   518         -# the ON expression is evaluated for each row of the cartesian product
   519         -# as a boolean expression. All rows for which the expression evaluates
   520         -# to false are excluded from the dataset.
          516  +# EVIDENCE-OF: R-38465-03616 If there is an ON clause then the ON
          517  +# expression is evaluated for each row of the cartesian product as a
          518  +# boolean expression. Only rows for which the expression evaluates to
          519  +# true are included from the dataset.
   521    520   #
   522    521   foreach {tn select res} [list                                              \
   523    522       1 { SELECT * FROM t1 %JOIN% t2 ON (1) }       $t1_cross_t2             \
   524    523       2 { SELECT * FROM t1 %JOIN% t2 ON (0) }       [list]                   \
   525    524       3 { SELECT * FROM t1 %JOIN% t2 ON (NULL) }    [list]                   \
   526    525       4 { SELECT * FROM t1 %JOIN% t2 ON ('abc') }   [list]                   \
   527    526       5 { SELECT * FROM t1 %JOIN% t2 ON ('1ab') }   $t1_cross_t2             \
................................................................................
   536    535      11 { SELECT t1.b, t2.b 
   537    536           FROM t1 %JOIN% t2 ON (CASE WHEN t1.a = 'a' THEN NULL ELSE 1 END) } \
   538    537         {two I two II two III three I three II three III}                    \
   539    538   ] {
   540    539     do_join_test e_select-1.3.$tn $select $res
   541    540   }
   542    541   
   543         -# EVIDENCE-OF: R-63358-54862 If there is a USING clause specified as
   544         -# part of the join-constraint, then each of the column names specified
   545         -# must exist in the datasets to both the left and right of the join-op.
          542  +# EVIDENCE-OF: R-49933-05137 If there is a USING clause then each of the
          543  +# column names specified must exist in the datasets to both the left and
          544  +# right of the join-operator.
   546    545   #
   547    546   do_select_tests e_select-1.4 -error {
   548    547     cannot join using column %s - column not present in both tables
   549    548   } {
   550    549     1 { SELECT * FROM t1, t3 USING (b) }   "b"
   551    550     2 { SELECT * FROM t3, t1 USING (c) }   "c"
   552    551     3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) }   "a"
   553    552   } 
   554    553   
   555         -# EVIDENCE-OF: R-55987-04584 For each pair of namesake columns, the
          554  +# EVIDENCE-OF: R-22776-52830 For each pair of named columns, the
   556    555   # expression "lhs.X = rhs.X" is evaluated for each row of the cartesian
   557         -# product as a boolean expression. All rows for which one or more of the
   558         -# expressions evaluates to false are excluded from the result set.
          556  +# product as a boolean expression. Only rows for which all such
          557  +# expressions evaluates to true are included from the result set.
   559    558   #
   560    559   do_select_tests e_select-1.5 {
   561    560     1 { SELECT * FROM t1, t3 USING (a)   }  {a one 1 b two 2}
   562    561     2 { SELECT * FROM t3, t4 USING (a,c) }  {b 2}
   563    562   } 
   564    563   
   565    564   # EVIDENCE-OF: R-54046-48600 When comparing values as a result of a
   566    565   # USING clause, the normal rules for handling affinities, collation
   567    566   # sequences and NULL values in comparisons apply.
   568    567   #
   569         -# EVIDENCE-OF: R-35466-18578 The column from the dataset on the
   570         -# left-hand side of the join operator is considered to be on the
          568  +# EVIDENCE-OF: R-38422-04402 The column from the dataset on the
          569  +# left-hand side of the join-operator is considered to be on the
   571    570   # left-hand side of the comparison operator (=) for the purposes of
   572    571   # collation sequence and affinity precedence.
   573    572   #
   574    573   do_execsql_test e_select-1.6.0 {
   575    574     CREATE TABLE t5(a COLLATE nocase, b COLLATE binary);
   576    575     INSERT INTO t5 VALUES('AA', 'cc');
   577    576     INSERT INTO t5 VALUES('BB', 'dd');
................................................................................
   618    617        {aa cc cc bb DD dd}
   619    618     4b { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x
   620    619          %JOIN% t5 ON (x.a=t5.a) } 
   621    620        {aa cc AA cc bb DD BB dd}
   622    621   } {
   623    622     do_join_test e_select-1.7.$tn $select $res
   624    623   }
   625         -
   626         -# EVIDENCE-OF: R-41434-12448 If the join-op is a "LEFT JOIN" or "LEFT
   627         -# OUTER JOIN", then after the ON or USING filtering clauses have been
   628         -# applied, an extra row is added to the output for each row in the
          624  +# EVIDENCE-OF: R-42531-52874 If the join-operator is a "LEFT JOIN" or
          625  +# "LEFT OUTER JOIN", then after the ON or USING filtering clauses have
          626  +# been applied, an extra row is added to the output for each row in the
   629    627   # original left-hand input dataset that corresponds to no rows at all in
   630    628   # the composite dataset (if any).
   631    629   #
   632    630   do_execsql_test e_select-1.8.0 {
   633    631     CREATE TABLE t7(a, b, c);
   634    632     CREATE TABLE t8(a, d, e);
   635    633   
................................................................................
   656    654     1a "SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)" {x ex 24 x abc 24}
   657    655     1b "SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" 
   658    656        {x ex 24 x abc 24 y why 25 {} {} {}}
   659    657     2a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24}
   660    658     2b "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}}
   661    659   }
   662    660   
   663         -# EVIDENCE-OF: R-01809-52134 If the NATURAL keyword is added to any of
   664         -# the join-ops, then an implicit USING clause is added to the
          661  +# EVIDENCE-OF: R-04932-55942 If the NATURAL keyword is in the
          662  +# join-operator then an implicit USING clause is added to the
   665    663   # join-constraints. The implicit USING clause contains each of the
   666    664   # column names that appear in both the left and right-hand input
   667    665   # datasets.
   668    666   #
   669    667   do_select_tests e_select-1-10 {
   670    668     1a "SELECT * FROM t7 JOIN t8 USING (a)"        {x ex 24 abc 24}
   671    669     1b "SELECT * FROM t7 NATURAL JOIN t8"          {x ex 24 abc 24}

Changes to test/e_select2.test.

   340    340   } {
   341    341   
   342    342     catchsql { DROP INDEX i1 }
   343    343     catchsql { DROP INDEX i2 }
   344    344     catchsql { DROP INDEX i3 }
   345    345     execsql $indexes
   346    346   
   347         -  # EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER
   348         -  # JOIN", "JOIN" or a comma (",") and there is no ON or USING clause,
   349         -  # then the result of the join is simply the cartesian product of the
   350         -  # left and right-hand datasets.
          347  +  # EVIDENCE-OF: R-49872-03192 If the join-operator is "CROSS JOIN",
          348  +  # "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING
          349  +  # clause, then the result of the join is simply the cartesian product of
          350  +  # the left and right-hand datasets.
   351    351     #
   352    352     # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER
   353    353     # JOIN", "JOIN" and "," join operators.
   354    354     #
   355    355     # EVIDENCE-OF: R-25071-21202 The "CROSS JOIN" join operator produces the
   356    356     # same result as the "INNER JOIN", "JOIN" and "," operators
   357    357     #
................................................................................
   364    364     test_join $tn.1.7  "t2 CROSS JOIN t3"      {t2 t3}
   365    365     test_join $tn.1.8  "t2 JOIN t3"            {t2 t3}
   366    366     test_join $tn.1.9  "t2, t2 AS x"           {t2 t2}
   367    367     test_join $tn.1.10 "t2 INNER JOIN t2 AS x" {t2 t2}
   368    368     test_join $tn.1.11 "t2 CROSS JOIN t2 AS x" {t2 t2}
   369    369     test_join $tn.1.12 "t2 JOIN t2 AS x"       {t2 t2}
   370    370   
   371         -  # EVIDENCE-OF: R-22775-56496 If there is an ON clause specified, then
   372         -  # the ON expression is evaluated for each row of the cartesian product
   373         -  # as a boolean expression. All rows for which the expression evaluates
   374         -  # to false are excluded from the dataset.
          371  +  # EVIDENCE-OF: R-38465-03616 If there is an ON clause then the ON
          372  +  # expression is evaluated for each row of the cartesian product as a
          373  +  # boolean expression. Only rows for which the expression evaluates to
          374  +  # true are included from the dataset.
   375    375     #
   376    376     test_join $tn.2.1  "t1, t2 ON (t1.a=t2.a)"  {t1 t2 -on {te_equals a a}}
   377    377     test_join $tn.2.2  "t2, t1 ON (t1.a=t2.a)"  {t2 t1 -on {te_equals a a}}
   378    378     test_join $tn.2.3  "t2, t1 ON (1)"          {t2 t1 -on te_true}
   379    379     test_join $tn.2.4  "t2, t1 ON (NULL)"       {t2 t1 -on te_false}
   380    380     test_join $tn.2.5  "t2, t1 ON (1.1-1.1)"    {t2 t1 -on te_false}
   381    381     test_join $tn.2.6  "t1, t2 ON (1.1-1.0)"    {t1 t2 -on te_true}
................................................................................
   500    500     CREATE TABLE t5(y INTEGER, z TEXT COLLATE binary);
   501    501   
   502    502     INSERT INTO t4 VALUES('2.0');
   503    503     INSERT INTO t4 VALUES('TWO');
   504    504     INSERT INTO t5 VALUES(2, 'two');
   505    505   } {}
   506    506   
   507         -# EVIDENCE-OF: R-55824-40976 A sub-select specified in the join-source
   508         -# following the FROM clause in a simple SELECT statement is handled as
   509         -# if it was a table containing the data returned by executing the
   510         -# sub-select statement.
          507  +# EVIDENCE-OF: R-59237-46742 A subquery specified in the
          508  +# table-or-subquery following the FROM clause in a simple SELECT
          509  +# statement is handled as if it was a table containing the data returned
          510  +# by executing the subquery statement.
   511    511   #
   512         -# EVIDENCE-OF: R-42612-06757 Each column of the sub-select dataset
   513         -# inherits the collation sequence and affinity of the corresponding
   514         -# expression in the sub-select statement.
          512  +# EVIDENCE-OF: R-27438-53558 Each column of the subquery has the
          513  +# collation sequence and affinity of the corresponding expression in the
          514  +# subquery statement.
   515    515   #
   516    516   foreach {tn subselect select spec} {
   517    517     1   "SELECT * FROM t2"   "SELECT * FROM t1 JOIN %ss%" 
   518    518         {t1 %ss%}
   519    519   
   520    520     2   "SELECT * FROM t2"   "SELECT * FROM t1 JOIN %ss% AS x ON (t1.a=x.a)" 
   521    521         {t1 %ss% -on {te_equals 0 0}}

Changes to test/printf2.test.

    55     55   do_execsql_test printf2-1.10 {
    56     56     SELECT printf('%lld',314159.2653);
    57     57   } {314159}
    58     58   do_execsql_test printf2-1.11 {
    59     59     SELECT printf('%lld%n',314159.2653,'hi');
    60     60   } {314159}
    61     61   
    62         -# EVIDENCE-OF: R-20555-31089 The %z format is interchangable with %s.
           62  +# EVIDENCE-OF: R-17002-27534 The %z format is interchangeable with %s.
    63     63   #
    64     64   do_execsql_test printf2-1.12 {
    65     65     SELECT printf('%.*z',5,'abcdefghijklmnop');
    66     66   } {abcde}
    67     67   do_execsql_test printf2-1.13 {
    68     68     SELECT printf('%c','abcdefghijklmnop');
    69     69   } {a}