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
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
ij> -- SQL Test Suite, V6.0, Schema Definition, schema5.std
-- 59-byte ID
-- TEd Version #
-- date_time print
-- *******************************************************************
-- ****** THIS FILE SHOULD BE RUN UNDER AUTHORIZATION ID FLATER ******
-- *******************************************************************
-- This is a standard schema definition.
--0 CREATE SCHEMA AUTHORIZATION FLATER
CREATE SCHEMA FLATER;
0 rows inserted/updated/deleted
ij> set schema FLATER;
0 rows inserted/updated/deleted
ij> -- VIEW FR1 tests forward references in schema definitions. This view
-- was checked by test 0523 in SDL032; that test was removed prior to
-- the release of V4. I personally believe that two-pass SDL processing
-- is the Right Thing and ought to be required, but I speak only for
-- myself.
-- CREATE VIEW FR1 AS SELECT * FROM DV1
CREATE TABLE CONCATBUF (ZZ CHAR(240));
0 rows inserted/updated/deleted
ij> CREATE TABLE USIG (C1 INT, C_1 INT);
0 rows inserted/updated/deleted
ij> CREATE TABLE U_SIG (C1 INT, C_1 INT);
0 rows inserted/updated/deleted
ij> CREATE VIEW DV1 AS
SELECT DISTINCT HOURS FROM HU.WORKS;
0 rows inserted/updated/deleted
ij> -- This small one-column table is used to generate an
-- indicator overflow data exception for SQLSTATE testing.
-- If the table cannot be created, the test is assumed passed.
-- Save the error message and then use TEd to delete the CREATE TABLE
-- as well as the GRANT ALL PRIVILEGES ON TINY TO SCHANZLE below.
-- Use the following TEd change: del *schema5.[sop]* /TINY/
-- Test number 0491 in program DML082 may also need to be deleted.
CREATE TABLE TINY (C1 VARCHAR(33000));
ERROR 42611: The length, precision, or scale attribute for column, or type mapping 'VARCHAR(33000)' is not valid.
ij> -- For generation of "with check option violation" SQLSTATE.
CREATE TABLE BASE_WCOV (C1 INT);
0 rows inserted/updated/deleted
ij> CREATE VIEW WCOV AS SELECT * FROM BASE_WCOV WHERE
--0 C1 > 0 WITH CHECK OPTION
C1 > 0 ;
0 rows inserted/updated/deleted
ij> CREATE TABLE BASE_VS1 (C1 INT, C2 INT);
0 rows inserted/updated/deleted
ij> CREATE VIEW VS1 AS SELECT * FROM BASE_VS1 WHERE C1 = 0;
0 rows inserted/updated/deleted
ij> CREATE VIEW VS2 AS
SELECT A.C1 FROM BASE_VS1 A WHERE EXISTS
(SELECT B.C2 FROM BASE_VS1 B WHERE B.C2 = A.C1);
0 rows inserted/updated/deleted
ij> CREATE VIEW VS3 AS
SELECT A.C2 FROM BASE_VS1 A WHERE A.C2 IN
(SELECT B.C1 FROM BASE_VS1 B WHERE B.C1 < A.C2);
0 rows inserted/updated/deleted
ij> CREATE VIEW VS4 AS
SELECT A.C1 FROM BASE_VS1 A WHERE A.C1 < ALL
(SELECT B.C2 FROM BASE_VS1 B);
0 rows inserted/updated/deleted
ij> CREATE VIEW VS5 AS
SELECT A.C1 FROM BASE_VS1 A WHERE A.C1 < SOME
(SELECT B.C2 FROM BASE_VS1 B);
0 rows inserted/updated/deleted
ij> CREATE VIEW VS6 AS
SELECT A.C1 FROM BASE_VS1 A WHERE A.C1 < ANY
(SELECT B.C2 FROM BASE_VS1 B);
0 rows inserted/updated/deleted
ij> --0 GRANT ALL PRIVILEGES ON TINY TO SCHANZLE
--0 GRANT ALL PRIVILEGES ON BASE_WCOV TO SCHANZLE
--0 GRANT ALL PRIVILEGES ON WCOV TO SCHANZLE
--0 GRANT ALL PRIVILEGES ON VS1 TO SCHANZLE
-- Test granting of privileges that we don't have to start with.
-- We have GRANT OPTION, but we should not be able to grant unrestricted
-- update on STAFF3 since our own update is restricted to two columns.
-- Do not change SCHEMA1 to grant unrestricted update.
-- * expect error message *
--0 GRANT SELECT, UPDATE ON HU.STAFF3 TO SCHANZLE
-- Same thing for views.
-- * expect error message *
--0 GRANT SELECT, UPDATE ON HU.VSTAFF3 TO SCHANZLE
-- See whether GRANT ALL PRIVILEGES gives you GRANT OPTION.
-- It should not. GRANT OPTION is not technically a privilege.
-- * expect error message *
--0 GRANT SELECT ON CUGINI.BADG1 TO SCHANZLE
-- See whether GRANT OPTION on a view gives you GRANT OPTION
-- on the base table.
-- * expect error message *
--0 GRANT SELECT ON CUGINI.BADG2 TO SCHANZLE
-- Delimited identifiers.
CREATE VIEW "SULLIVAN.SELECT" ("sullivan.select") AS
SELECT C1 FROM BASE_VS1;
0 rows inserted/updated/deleted
ij> --0 GRANT ALL PRIVILEGES ON "SULLIVAN.SELECT" TO SCHANZLE
-- Please be aware of the following errata; they are not being
-- tested here.
-- Check for erratum which allowed duplicate
-- <unique constraint definition>s
-- Reference ISO/IEC JTC1/SC21 N6789 section 11.7 SR7
-- and Annex E #4
--
-- The following should be flagged or rejected:
-- CREATE TABLE T0512 (C1 INT NOT NULL, C2 INT NOT NULL, C3 INT NOT NULL,
-- UNIQUE (C1,C2), UNIQUE (C3), UNIQUE (C2,C1))
CREATE TABLE T0512 (C1 INT NOT NULL, C2 INT NOT NULL, C3 INT NOT NULL,
CONSTRAINT T0512_C1C2 UNIQUE (C1,C2), UNIQUE (C3),
CONSTRAINT T0512_C2C1 UNIQUE (C2,C1));
ERROR 42Z93: Constraints 'T0512_C2C1' and 'T0512_C1C2' have the same set of columns, which is not allowed.
ij> --0 PASS: if there was an error for a duplicate unique constraint
-- Check for erratum which allowed recursive view definitions.
-- Reference ISO/IEC JTC1/SC21 N6789 section 11.19 <view definition> SR4
-- and Annex E #6
--
-- The following should be flagged or rejected:
-- CREATE VIEW T0513 (C1, C2) AS
-- SELECT T0513.C2, BASE_VS1.C1 FROM T0513, BASE_VS1
CREATE VIEW T0513 (C1, C2) AS
SELECT T0513.C2, BASE_VS1.C1 FROM T0513, BASE_VS1;
ERROR 42X05: Table/View 'T0513' does not exist.
ij> --0 PASS: if an error is returned that the view is circular
-- ************* End of Schema *************
disconnect;
ij>
|
