sqllogictest
Check-in [99714e1f5b]
Not logged in

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

Overview
Comment:Evidence testing of SQLite SQL language.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:99714e1f5b941a20e2e1a3b54b92fb858550d16c
User & Date: shaneh 2010-08-10 03:33:20
Context
2010-08-10
04:23
Added evidence testing for triggers; check-in: 15543e896a user: shaneh tags: trunk
03:33
Evidence testing of SQLite SQL language. check-in: 99714e1f5b user: shaneh tags: trunk
2010-08-02
19:07
Better support for the Oracle ODBC interface; Added option to xStatement to suppress printing of errors. check-in: 81505d3c5f user: shaneh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Added test/evidence/slt_lang_createview.test.













































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
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
hash-threshold 8

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

statement ok
INSERT INTO t1 VALUES(1,'true')

statement ok
INSERT INTO t1 VALUES(0,'false')

statement ok
INSERT INTO t1 VALUES(NULL,'NULL')

statement ok
CREATE INDEX t1i1 ON t1(x)


# TBD-EVIDENCE-OF: R-13439-14752 The CREATE VIEW command assigns a name to a
# pre-packaged SELECT statement.

statement ok
CREATE VIEW view1 AS SELECT x FROM t1 WHERE x>0

# already exists
statement error
CREATE VIEW view1 AS SELECT x FROM t1 WHERE x>0

# TBD-EVIDENCE-OF: R-63075-15970 Once the view is created, it can be used in
# the FROM clause of another SELECT in place of a table name.

# PARTIAL-EVIDENCE-OF: R-48816-31606 If the "TEMP" or "TEMPORARY" keyword occurs
# in between "CREATE" and "VIEW" then the view that is created is only
# visible to the process that opened the database and is automatically
# deleted when the database is closed.

onlyif sqlite
statement ok
CREATE TEMP VIEW view2 AS SELECT x FROM t1 WHERE x>0

onlyif sqlite
statement ok
CREATE TEMPORARY VIEW view3 AS SELECT x FROM t1 WHERE x>0

# TBD-EVIDENCE-OF: R-49139-10687 If a <database-name> is specified,
# then the view is created in the named database.

# TBD-EVIDENCE-OF: R-48195-55336 It is an error to specify both a
# <database-name> and the TEMP keyword on a VIEW, unless the
# <database-name> is "temp".

# TBD-EVIDENCE-OF: R-49817-02954 If no database name is specified, and the
# TEMP keyword is not present, the VIEW is created in the main database.

# EVIDENCE-OF: R-16775-34716 You cannot DELETE, INSERT, or UPDATE a
# view.

skipif mssql  # this is allowed
statement error
DELETE FROM view1 WHERE x>0

onlyif mssql  # this is allowed
statement ok
DELETE FROM view1 WHERE x>0

statement error
INSERT INTO view1 VALUES(2,'unknown')

skipif mssql  # this is allowed
statement error
UPDATE view1 SET x=2

onlyif mssql  # this is allowed
statement ok
UPDATE view1 SET x=2

# PARTIAL-EVIDENCE-OF: R-05363-17893 Views are read-only in SQLite.

onlyif sqlite
statement error
DELETE FROM view1 WHERE x>0

onlyif sqlite
statement error
INSERT INTO view1 VALUES(2,'unknown')

onlyif sqlite
statement error
INSERT OR REPLACE INTO view1 VALUES(2,'unknown')

onlyif sqlite
statement error
UPDATE view1 SET x=2

# TBD-EVIDENCE-OF: R-16876-26469 However, in many cases you can use an
# INSTEAD OF trigger on the view to accomplish the same thing.

# EVIDENCE-OF: R-10484-47921 Views are removed with the DROP VIEW
# command.

statement ok
DROP VIEW view1

onlyif sqlite
statement ok
DROP VIEW view2

onlyif sqlite
statement ok
DROP VIEW view3

# already dropped
statement error
DROP VIEW view1

# never existed
statement error
DROP VIEW viewX

Added test/evidence/slt_lang_dropindex.test.































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
hash-threshold 8

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

statement ok
INSERT INTO t1 VALUES(1,'true')

statement ok
INSERT INTO t1 VALUES(0,'false')

statement ok
INSERT INTO t1 VALUES(NULL,'NULL')

statement ok
CREATE INDEX t1i1 ON t1(x)


# EVIDENCE-OF: R-42037-15614 The DROP INDEX statement removes an index
# added with the CREATE INDEX statement.

statement ok
DROP INDEX t1i1;

# this should error, as already dropped
statement error
DROP INDEX t1i1;

# this should error, as never existed
statement error
DROP INDEX tXiX;

Added test/evidence/slt_lang_droptable.test.























































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
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
hash-threshold 8

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

statement ok
INSERT INTO t1 VALUES(1,'true')

statement ok
INSERT INTO t1 VALUES(0,'false')

statement ok
INSERT INTO t1 VALUES(NULL,'NULL')

statement ok
CREATE INDEX t1i1 ON t1(x)


# EVIDENCE-OF: R-01463-03846 The DROP TABLE statement removes a table
# added with the CREATE TABLE statement.

statement ok
DROP TABLE t1

# already dropped
statement error
DROP TABLE t1

# never existed
statement error
DROP TABLE tX

# TBD-EVIDENCE-OF: R-40197-14811 The dropped table is completely removed
# from the database schema and the disk file.

# EVIDENCE-OF: R-33950-57093 All indices and triggers associated with
# the table are also deleted.

# this should error, as was dropped with table
statement error
DROP INDEX t1i1;

# PARTIAL-EVIDENCE-OF: R-57089-01510 The optional IF EXISTS clause suppresses
# the error that would normally result if the table does not exist.

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

skipif mssql
DROP TABLE IF EXISTS t1

skipif mssql
DROP TABLE IF EXISTS t1

# TBD-EVIDENCE-OF: R-55943-26846 If foreign key constraints are enabled, a
# DROP TABLE command performs an implicit DELETE FROM <tbl>
# command before removing the table from the database schema.

# TBD-EVIDENCE-OF: R-02002-57504 Any triggers attached to the table are
# dropped from the database schema before the implicit DELETE FROM
# <tbl> is executed, so this cannot cause any triggers to fire.

# TBD-EVIDENCE-OF: R-33044-60878 an implicit DELETE FROM <tbl> does
# cause any configured foreign key actions to take place.

# TBD-EVIDENCE-OF: R-17649-15849 If the implicit DELETE FROM <tbl>
# executed as part of a DROP TABLE command violates any immediate
# foreign key constraints, an error is returned and the table is not
# dropped.

# TBD-EVIDENCE-OF: R-01608-60102 If the implicit DELETE FROM <tbl>
# causes any deferred foreign key constraints to be violated, and the
# violations still exist when the transaction is committed, an error is
# returned at the time of commit.

Added test/evidence/slt_lang_reindex.test.





















































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
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
hash-threshold 8

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

statement ok
INSERT INTO t1 VALUES(1,'true')

statement ok
INSERT INTO t1 VALUES(0,'false')

statement ok
INSERT INTO t1 VALUES(NULL,'NULL')

statement ok
CREATE INDEX t1i1 ON t1(x)

# There is no REINDEX in the SQL92 standard.
# So far, this only runs well on SQLite and Postgres.
# I believe MySQL uses the form REPAIR TABLE [tbl_name].

# skip this entire file if ms sql server
onlyif mssql
halt

# skip this entire file if oracle
onlyif oracle
halt

# skip this entire file if oracle
onlyif mysql
halt

# EVIDENCE-OF: R-52173-44778 The REINDEX command is used to delete and
# recreate indices from scratch.

statement ok
REINDEX t1i1

statement error
REINDEX tXiX

# TBD-EVIDENCE-OF: R-38396-20088 In the first form, all indices in all
# attached databases that use the named collation sequence are
# recreated.

# TBD-EVIDENCE-OF: R-46980-03026 In the second form, if
# [database-name.]table/index-name identifies a table, then all indices
# associated with the table are rebuilt.

# TBD-EVIDENCE-OF: R-50401-40957 If an index is identified, then only this
# specific index is deleted and recreated.

# TBD-EVIDENCE-OF: R-59524-35239 If no database-name is specified and there
# exists both a table or index and a collation sequence of the specified
# name, then indices associated with the collation sequence only are
# reconstructed.

Added test/evidence/slt_lang_update.test.

































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
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
hash-threshold 8

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

statement ok
INSERT INTO t1 VALUES(1,'true')

statement ok
INSERT INTO t1 VALUES(0,'false')

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.