sqllogictest
Check-in [04ea7ef2df]
Not logged in

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

Overview
Comment:Update to SLT evidence marks.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:04ea7ef2dfa435508d07b2cdc7cabb5d267a968e
User & Date: shaneh 2010-09-24 18:59:00
Context
2010-09-25
04:03
Evidence tests for aggregate functions. check-in: 1ab2242b4f user: shaneh tags: trunk
2010-09-24
18:59
Update to SLT evidence marks. check-in: 04ea7ef2df user: shaneh tags: trunk
2010-09-01
18:52
Basic evidence tests for REPLACE. check-in: 9b2ad097cb user: shaneh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/evidence/slt_lang_createtrigger.test.

   102    102   # TBD-EVIDENCE-OF: R-35856-58769 However if an ON CONFLICT clause is
   103    103   # specified as part of the statement causing the trigger to fire, then
   104    104   # conflict handling policy of the outer statement is used instead.
   105    105   
   106    106   # TBD-EVIDENCE-OF: R-32333-58476 Triggers are automatically dropped when the
   107    107   # table that they are associated with (the table-name table) is dropped.
   108    108   
   109         -# TBD-EVIDENCE-OF: R-01977-60798 However if the the trigger actions
   110         -# reference other tables, the trigger is not dropped or modified if
   111         -# those other tables are dropped or modified.
          109  +# TBD-EVIDENCE-OF: R-45164-23268 However if the trigger actions reference
          110  +# other tables, the trigger is not dropped or modified if those other
          111  +# tables are dropped or modified.
   112    112   
   113    113   # TBD-EVIDENCE-OF: R-31067-37494 Triggers are removed using the DROP TRIGGER
   114    114   # statement.
   115    115   
   116    116   # TBD-EVIDENCE-OF: R-46291-22228 The UPDATE, DELETE, and INSERT statements
   117    117   # within triggers do not support the full syntax for UPDATE, DELETE, and
   118    118   # INSERT statements.

Changes to test/evidence/slt_lang_update.test.

    12     12   statement ok
    13     13   INSERT INTO t1 VALUES(NULL,'NULL')
    14     14   
    15     15   statement ok
    16     16   CREATE INDEX t1i1 ON t1(x)
    17     17   
    18     18   
    19         -# EVIDENCE-OF: R-51331-37626 The UPDATE statement is used to change the
    20         -# value of columns in selected rows of a table.
           19  +# EVIDENCE-OF: R-38515-45264 An UPDATE statement is used to modify a
           20  +# subset of the values stored in zero or more rows of the database table
           21  +# identified by the qualified-table-name specified as part of the UPDATE
           22  +# statement.
           23  +
           24  +statement ok
           25  +UPDATE t1 SET x=1 WHERE x>0
    21     26   
    22     27   statement ok
    23     28   UPDATE t1 SET x=2 WHERE x>0
    24     29   
    25         -# EVIDENCE-OF: R-39990-21878 Each assignment in an UPDATE specifies a
    26         -# column name to the left of the equals sign and an arbitrary expression
    27         -# to the right.
           30  +statement ok
           31  +UPDATE t1 SET y='true' WHERE x>0
    28     32   
    29     33   statement ok
    30     34   UPDATE t1 SET y='unknown' WHERE x>0
    31     35   
    32     36   statement error
    33     37   UPDATE t1 SET z='foo'
    34     38   
    35     39   statement error
    36     40   UPDATE t1 SET z='foo' WHERE x>0
    37     41   
    38         -# EVIDENCE-OF: R-51230-45980 The expressions may use the values of other
    39         -# columns.
           42  +# TBD-EVIDENCE-OF: R-55869-30521 If the UPDATE statement does not have a
           43  +# WHERE clause, all rows in the table are modified by the UPDATE.
           44  +
           45  +statement ok
           46  +UPDATE t1 SET x=3
           47  +
           48  +query I rowsort
           49  +SELECT count(*) FROM t1 WHERE x=3
           50  +----
           51  +3
           52  +
           53  +# EVIDENCE-OF: R-42117-40023 Otherwise, the UPDATE affects only those
           54  +# rows for which the result of evaluating the WHERE clause expression as
           55  +# a boolean expression is true.
           56  +
           57  +statement ok
           58  +UPDATE t1 SET x=1 WHERE y='unknown'
           59  +
           60  +query I rowsort
           61  +SELECT count(*) FROM t1 WHERE x=1
           62  +----
           63  +1
           64  +
           65  +# EVIDENCE-OF: R-58129-20729 It is not an error if the WHERE clause does
           66  +# not evaluate to true for any row in the table - this just means that
           67  +# the UPDATE statement affects zero rows.
           68  +
           69  +statement ok
           70  +UPDATE t1 SET x=1 WHERE y='foo'
           71  +
           72  +# EVIDENCE-OF: R-40598-36595 For each affected row, the named columns
           73  +# are set to the values found by evaluating the corresponding scalar
           74  +# expressions.
           75  +
           76  +statement ok
           77  +UPDATE t1 SET x=3+1
           78  +
           79  +query I rowsort
           80  +SELECT count(*) FROM t1 WHERE x=4
           81  +----
           82  +3
           83  +
           84  +# EVIDENCE-OF: R-09060-20018 If a single column-name appears more than
           85  +# once in the list of assignment expressions, all but the rightmost
           86  +# occurence is ignored.
    40     87   
    41     88   statement ok
    42         -UPDATE t1 SET y=x WHERE x>0
           89  +UPDATE t1 SET x=3, x=4, x=5
    43     90   
    44         -# EVIDENCE-OF: R-34438-08394 All expressions are evaluated before any
    45         -# assignments are made.
           91  +query I rowsort
           92  +SELECT count(*) FROM t1 WHERE x=3
           93  +----
           94  +0
           95  +
           96  +query I rowsort
           97  +SELECT count(*) FROM t1 WHERE x=4
           98  +----
           99  +0
          100  +
          101  +query I rowsort
          102  +SELECT count(*) FROM t1 WHERE x=5
          103  +----
          104  +3
          105  +
          106  +# EVIDENCE-OF: R-40472-60438 Columns that do not appear in the list of
          107  +# assignments are left unmodified.
          108  +
          109  +query I rowsort
          110  +SELECT count(*) FROM t1 WHERE y='unknown'
          111  +----
          112  +1
    46    113   
    47    114   statement ok
    48         -UPDATE t1 SET y=y+2 WHERE x>0
          115  +UPDATE t1 SET x=2
    49    116   
    50         -# EVIDENCE-OF: R-55426-32478 A WHERE clause can be used to restrict
    51         -# which rows are updated.
          117  +query I rowsort
          118  +SELECT count(*) FROM t1 WHERE y='unknown'
          119  +----
          120  +1
          121  +
          122  +# EVIDENCE-OF: R-36239-04077 The scalar expressions may refer to columns
          123  +# of the row being updated.
          124  +
          125  +# EVIDENCE-OF: R-04558-24451 In this case all scalar expressions are
          126  +# evaluated before any assignments are made.
    52    127   
    53    128   statement ok
    54         -UPDATE t1 SET x=1 WHERE x>0
          129  +UPDATE t1 SET x=x+2
    55    130   
    56         -statement ok
    57         -UPDATE t1 SET y='true' WHERE x>0
          131  +query I rowsort
          132  +SELECT count(*) FROM t1 WHERE x=4
          133  +----
          134  +3
    58    135   
    59         -# TBD-EVIDENCE-OF: R-48588-18160 The optional conflict-clause allows the
    60         -# specification of an alternative constraint conflict resolution
    61         -# algorithm to use during this one UPDATE command.
          136  +# TBD-EVIDENCE-OF: R-12619-24112 The optional conflict-clause allows the
          137  +# user to nominate a specific constraint conflict resolution algorithm
          138  +# to use during this one UPDATE command.
    62    139   
    63         -# TBD-EVIDENCE-OF: R-55493-24001 There are additional syntax restrictions on
    64         -# UPDATE statements that occur within the body of a CREATE TRIGGER
    65         -# statement.
    66         -
    67         -# TBD-EVIDENCE-OF: R-07956-29754 The table-name of the UPDATE must be
    68         -# unqualified.
          140  +# TBD-EVIDENCE-OF: R-12123-54095 The table-name specified as part of an
          141  +# UPDATE statement within a trigger body must be unqualified.
    69    142   
    70    143   # TBD-EVIDENCE-OF: R-09690-36749 In other words, the database-name. prefix
    71    144   # on the table name of the UPDATE is not allowed within triggers.
    72    145   
    73         -# TBD-EVIDENCE-OF: R-08610-60711 The table to be updated must be in the same
    74         -# database as the table to which the trigger is attached.
          146  +# TBD-EVIDENCE-OF: R-06085-13761 Unless the table to which the trigger is
          147  +# attached is in the TEMP database, the table being updated by the
          148  +# trigger program must reside in the same database as it.
          149  +
          150  +# TBD-EVIDENCE-OF: R-29512-54644 If the table to which the trigger is
          151  +# attached is in the TEMP database, then the unqualified name of the
          152  +# table being updated is resolved in the same way as it is for a
          153  +# top-level statement (by searching first the TEMP database, then the
          154  +# main database, then any other databases in the order they were
          155  +# attached).
    75    156   
    76    157   # TBD-EVIDENCE-OF: R-19619-42762 The INDEXED BY and NOT INDEXED clauses are
    77    158   # not allowed on UPDATE statements within triggers.
    78    159   
    79         -# TBD-EVIDENCE-OF: R-14001-24988 The LIMIT clause for UPDATE is unsupported
    80         -# within triggers.
          160  +# TBD-EVIDENCE-OF: R-57359-59558 The LIMIT and ORDER BY clauses for UPDATE
          161  +# are unsupported within triggers, regardless of the compilation options
          162  +# used to build SQLite.
    81    163   
    82    164   # TBD-EVIDENCE-OF: R-59581-44104 If SQLite is built with the
    83    165   # SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax
    84    166   # of the UPDATE statement is extended with optional ORDER BY and LIMIT
    85    167   # clauses
    86    168   
    87         -# TBD-EVIDENCE-OF: R-47158-42005 The optional LIMIT clause can be used to
    88         -# limit the number of rows modified, and thereby limit the size of the
    89         -# transaction.
          169  +# TBD-EVIDENCE-OF: R-58862-44169 If an UPDATE statement has a LIMIT clause,
          170  +# the maximum number of rows that will be updated is found by evaluating
          171  +# the accompanying expression and casting it to an integer value.
          172  +
          173  +# TBD-EVIDENCE-OF: R-63582-45120 A negative value is interpreted as "no
          174  +# limit".
          175  +
          176  +# TBD-EVIDENCE-OF: R-18628-11938 If the LIMIT expression evaluates to
          177  +# non-negative value N and the UPDATE statement has an ORDER BY clause,
          178  +# then all rows that would be updated in the absence of the LIMIT clause
          179  +# are sorted according to the ORDER BY and the first N updated.
          180  +
          181  +# TBD-EVIDENCE-OF: R-30955-38324 If the UPDATE statement also has an OFFSET
          182  +# clause, then it is similarly evaluated and cast to an integer value.
          183  +# If the OFFSET expression evaluates to a non-negative value M, then the
          184  +# first M rows are skipped and the following N rows updated instead.
    90    185   
    91         -# TBD-EVIDENCE-OF: R-27955-06688 The ORDER BY clause on an UPDATE statement
    92         -# is used only to determine which rows fall within the LIMIT.
          186  +# TBD-EVIDENCE-OF: R-19486-35828 If the UPDATE statement has no ORDER BY
          187  +# clause, then all rows that would be updated in the absence of the
          188  +# LIMIT clause are assembled in an arbitrary order before applying the
          189  +# LIMIT and OFFSET clauses to determine which are actually updated.
    93    190   
    94         -# TBD-EVIDENCE-OF: R-45033-12484 The order in which rows are modified is
    95         -# arbitrary and is not determined by the ORDER BY clause.
          191  +# TBD-EVIDENCE-OF: R-10927-26133 The ORDER BY clause on an UPDATE statement
          192  +# is used only to determine which rows fall within the LIMIT. The order
          193  +# in which rows are modified is arbitrary and is not influenced by the
          194  +# ORDER BY clause.
    96    195