sqllogictest

Check-in [04ea7ef2df]
Login

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.000
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
Unified Diff Ignore Whitespace Patch
Changes to test/evidence/slt_lang_createtrigger.test.
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
# TBD-EVIDENCE-OF: R-35856-58769 However if an ON CONFLICT clause is
# specified as part of the statement causing the trigger to fire, then
# conflict handling policy of the outer statement is used instead.

# TBD-EVIDENCE-OF: R-32333-58476 Triggers are automatically dropped when the
# table that they are associated with (the table-name table) is dropped.

# TBD-EVIDENCE-OF: R-01977-60798 However if the the trigger actions
# reference other tables, the trigger is not dropped or modified if
# those other tables are dropped or modified.

# TBD-EVIDENCE-OF: R-31067-37494 Triggers are removed using the DROP TRIGGER
# statement.

# TBD-EVIDENCE-OF: R-46291-22228 The UPDATE, DELETE, and INSERT statements
# within triggers do not support the full syntax for UPDATE, DELETE, and
# INSERT statements.







|
|
|







102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
# TBD-EVIDENCE-OF: R-35856-58769 However if an ON CONFLICT clause is
# specified as part of the statement causing the trigger to fire, then
# conflict handling policy of the outer statement is used instead.

# TBD-EVIDENCE-OF: R-32333-58476 Triggers are automatically dropped when the
# table that they are associated with (the table-name table) is dropped.

# TBD-EVIDENCE-OF: R-45164-23268 However if the trigger actions reference
# other tables, the trigger is not dropped or modified if those other
# tables are dropped or modified.

# TBD-EVIDENCE-OF: R-31067-37494 Triggers are removed using the DROP TRIGGER
# statement.

# TBD-EVIDENCE-OF: R-46291-22228 The UPDATE, DELETE, and INSERT statements
# within triggers do not support the full syntax for UPDATE, DELETE, and
# INSERT statements.
Changes to test/evidence/slt_lang_update.test.
12
13
14
15
16
17
18
19



20


21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37


38


39



40





41
42
43




44



45


46




47
48
49




50



51






52



















53
54
55











56
57
58





59
60
61
62
63
64
65
66
67
68
69
70
71
72



73
74





75
76
77
78
79
80

81
82
83
84
85
86



87


88




89




90
91


92
93


94
95
96
statement ok
INSERT INTO t1 VALUES(NULL,'NULL')

statement ok
CREATE INDEX t1i1 ON t1(x)


# EVIDENCE-OF: R-51331-37626 The UPDATE statement is used to change the



# value of columns in selected rows of a table.



statement ok
UPDATE t1 SET x=2 WHERE x>0

# EVIDENCE-OF: R-39990-21878 Each assignment in an UPDATE specifies a
# column name to the left of the equals sign and an arbitrary expression
# to the right.

statement ok
UPDATE t1 SET y='unknown' WHERE x>0

statement error
UPDATE t1 SET z='foo'

statement error
UPDATE t1 SET z='foo' WHERE x>0



# EVIDENCE-OF: R-51230-45980 The expressions may use the values of other


# columns.









statement ok
UPDATE t1 SET y=x WHERE x>0





# EVIDENCE-OF: R-34438-08394 All expressions are evaluated before any



# assignments are made.







statement ok
UPDATE t1 SET y=y+2 WHERE x>0





# EVIDENCE-OF: R-55426-32478 A WHERE clause can be used to restrict



# which rows are updated.


























statement ok
UPDATE t1 SET x=1 WHERE x>0












statement ok
UPDATE t1 SET y='true' WHERE x>0






# TBD-EVIDENCE-OF: R-48588-18160 The optional conflict-clause allows the
# specification of an alternative constraint conflict resolution
# algorithm to use during this one UPDATE command.

# TBD-EVIDENCE-OF: R-55493-24001 There are additional syntax restrictions on
# UPDATE statements that occur within the body of a CREATE TRIGGER
# statement.

# TBD-EVIDENCE-OF: R-07956-29754 The table-name of the UPDATE must be
# unqualified.

# TBD-EVIDENCE-OF: R-09690-36749 In other words, the database-name. prefix
# on the table name of the UPDATE is not allowed within triggers.




# TBD-EVIDENCE-OF: R-08610-60711 The table to be updated must be in the same
# database as the table to which the trigger is attached.






# TBD-EVIDENCE-OF: R-19619-42762 The INDEXED BY and NOT INDEXED clauses are
# not allowed on UPDATE statements within triggers.

# TBD-EVIDENCE-OF: R-14001-24988 The LIMIT clause for UPDATE is unsupported
# within triggers.


# TBD-EVIDENCE-OF: R-59581-44104 If SQLite is built with the
# SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax
# of the UPDATE statement is extended with optional ORDER BY and LIMIT
# clauses




# TBD-EVIDENCE-OF: R-47158-42005 The optional LIMIT clause can be used to


# limit the number of rows modified, and thereby limit the size of the




# transaction.





# TBD-EVIDENCE-OF: R-27955-06688 The ORDER BY clause on an UPDATE statement


# is used only to determine which rows fall within the LIMIT.



# TBD-EVIDENCE-OF: R-45033-12484 The order in which rows are modified is
# arbitrary and is not determined by the ORDER BY clause.








|
>
>
>
|
>
>




|
|
<










>
>
|
>
>
|
>
>
>
|
>
>
>
>
>

|

>
>
>
>
|
>
>
>
|
>
>

>
>
>
>

|

>
>
>
>
|
>
>
>
|
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

|

>
>
>
>
>
>
>
>
>
>
>

|

>
>
>
>
>
|
|
|

|
<
<
|
<
<




>
>
>
|
|
>
>
>
>
>




|
|
>






>
>
>
|
>
>
|
>
>
>
>
|
>
>
>
>

|
>
>
|

>
>
|
|

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140


141


142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
statement ok
INSERT INTO t1 VALUES(NULL,'NULL')

statement ok
CREATE INDEX t1i1 ON t1(x)


# EVIDENCE-OF: R-38515-45264 An UPDATE statement is used to modify a
# subset of the values stored in zero or more rows of the database table
# identified by the qualified-table-name specified as part of the UPDATE
# statement.

statement ok
UPDATE t1 SET x=1 WHERE x>0

statement ok
UPDATE t1 SET x=2 WHERE x>0

statement ok
UPDATE t1 SET y='true' WHERE x>0


statement ok
UPDATE t1 SET y='unknown' WHERE x>0

statement error
UPDATE t1 SET z='foo'

statement error
UPDATE t1 SET z='foo' WHERE x>0

# TBD-EVIDENCE-OF: R-55869-30521 If the UPDATE statement does not have a
# WHERE clause, all rows in the table are modified by the UPDATE.

statement ok
UPDATE t1 SET x=3

query I rowsort
SELECT count(*) FROM t1 WHERE x=3
----
3

# EVIDENCE-OF: R-42117-40023 Otherwise, the UPDATE affects only those
# rows for which the result of evaluating the WHERE clause expression as
# a boolean expression is true.

statement ok
UPDATE t1 SET x=1 WHERE y='unknown'

query I rowsort
SELECT count(*) FROM t1 WHERE x=1
----
1

# EVIDENCE-OF: R-58129-20729 It is not an error if the WHERE clause does
# not evaluate to true for any row in the table - this just means that
# the UPDATE statement affects zero rows.

statement ok
UPDATE t1 SET x=1 WHERE y='foo'

# EVIDENCE-OF: R-40598-36595 For each affected row, the named columns
# are set to the values found by evaluating the corresponding scalar
# expressions.

statement ok
UPDATE t1 SET x=3+1

query I rowsort
SELECT count(*) FROM t1 WHERE x=4
----
3

# EVIDENCE-OF: R-09060-20018 If a single column-name appears more than
# once in the list of assignment expressions, all but the rightmost
# occurence is ignored.

statement ok
UPDATE t1 SET x=3, x=4, x=5

query I rowsort
SELECT count(*) FROM t1 WHERE x=3
----
0

query I rowsort
SELECT count(*) FROM t1 WHERE x=4
----
0

query I rowsort
SELECT count(*) FROM t1 WHERE x=5
----
3

# EVIDENCE-OF: R-40472-60438 Columns that do not appear in the list of
# assignments are left unmodified.

query I rowsort
SELECT count(*) FROM t1 WHERE y='unknown'
----
1

statement ok
UPDATE t1 SET x=2

query I rowsort
SELECT count(*) FROM t1 WHERE y='unknown'
----
1

# EVIDENCE-OF: R-36239-04077 The scalar expressions may refer to columns
# of the row being updated.

# EVIDENCE-OF: R-04558-24451 In this case all scalar expressions are
# evaluated before any assignments are made.

statement ok
UPDATE t1 SET x=x+2

query I rowsort
SELECT count(*) FROM t1 WHERE x=4
----
3

# TBD-EVIDENCE-OF: R-12619-24112 The optional conflict-clause allows the
# user to nominate a specific constraint conflict resolution algorithm
# to use during this one UPDATE command.

# TBD-EVIDENCE-OF: R-12123-54095 The table-name specified as part of an


# UPDATE statement within a trigger body must be unqualified.



# TBD-EVIDENCE-OF: R-09690-36749 In other words, the database-name. prefix
# on the table name of the UPDATE is not allowed within triggers.

# TBD-EVIDENCE-OF: R-06085-13761 Unless the table to which the trigger is
# attached is in the TEMP database, the table being updated by the
# trigger program must reside in the same database as it.

# TBD-EVIDENCE-OF: R-29512-54644 If the table to which the trigger is
# attached is in the TEMP database, then the unqualified name of the
# table being updated is resolved in the same way as it is for a
# top-level statement (by searching first the TEMP database, then the
# main database, then any other databases in the order they were
# attached).

# TBD-EVIDENCE-OF: R-19619-42762 The INDEXED BY and NOT INDEXED clauses are
# not allowed on UPDATE statements within triggers.

# TBD-EVIDENCE-OF: R-57359-59558 The LIMIT and ORDER BY clauses for UPDATE
# are unsupported within triggers, regardless of the compilation options
# used to build SQLite.

# TBD-EVIDENCE-OF: R-59581-44104 If SQLite is built with the
# SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax
# of the UPDATE statement is extended with optional ORDER BY and LIMIT
# clauses

# TBD-EVIDENCE-OF: R-58862-44169 If an UPDATE statement has a LIMIT clause,
# the maximum number of rows that will be updated is found by evaluating
# the accompanying expression and casting it to an integer value.

# TBD-EVIDENCE-OF: R-63582-45120 A negative value is interpreted as "no
# limit".

# TBD-EVIDENCE-OF: R-18628-11938 If the LIMIT expression evaluates to
# non-negative value N and the UPDATE statement has an ORDER BY clause,
# then all rows that would be updated in the absence of the LIMIT clause
# are sorted according to the ORDER BY and the first N updated.

# TBD-EVIDENCE-OF: R-30955-38324 If the UPDATE statement also has an OFFSET
# clause, then it is similarly evaluated and cast to an integer value.
# If the OFFSET expression evaluates to a non-negative value M, then the
# first M rows are skipped and the following N rows updated instead.

# TBD-EVIDENCE-OF: R-19486-35828 If the UPDATE statement has no ORDER BY
# clause, then all rows that would be updated in the absence of the
# LIMIT clause are assembled in an arbitrary order before applying the
# LIMIT and OFFSET clauses to determine which are actually updated.

# TBD-EVIDENCE-OF: R-10927-26133 The ORDER BY clause on an UPDATE statement
# is used only to determine which rows fall within the LIMIT. The order
# in which rows are modified is arbitrary and is not influenced by the
# ORDER BY clause.