-
Notifications
You must be signed in to change notification settings - Fork 225
/
Copy patht09_check.expected
149 lines (149 loc) · 7.35 KB
/
t09_check.expected
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
138
139
140
141
142
143
144
145
146
147
148
149
(test='1. Test CHECK CONSTRAINT behavior')
(rows inserted=1)
[INSERT INTO t1 VALUES('greenish')] failed with rc 403 CHECK constraint violation CHECK constraint failed for 'valid_colors' unable to add record rc = 320
[INSERT INTO t1 VALUES('black')] failed with rc 403 CHECK constraint violation CHECK constraint failed for 'valid_colors' unable to add record rc = 320
(color='red')
(test='2. ALTER TABLE')
(csc2='schema
{
cstring color[11] null = yes
}
')
(rows inserted=1)
(rows inserted=1)
[ALTER TABLE t1 ADD CONSTRAINT valid_colors CHECK (color IN ('red', 'green', 'blue'))] failed with rc 240 Record violates check constraints rrn xx genid xx
(csc2='schema
{
cstring color[11] null = yes
}
')
(rows deleted=1)
(csc2='schema
{
cstring color[11] null = yes
}
constraints
{
check "valid_colors" = {where color IN ('red', 'green', 'blue')}
}
')
(color='blue')
[INSERT INTO t1 VALUES('white')] failed with rc 403 CHECK constraint violation CHECK constraint failed for 'valid_colors' unable to add record rc = 320
(csc2='schema
{
cstring color[11] null = yes
}
')
(rows inserted=1)
(color='blue')
(color='white')
(test='3. Invalid use cases')
[CREATE TABLE t1(i INT, CHECK ())] failed with rc -3 near ")": syntax error
[CREATE TABLE t1(i INT, CHECK (SELECT 1))] failed with rc -3 near "SELECT": syntax error
[CREATE TABLE t1(i INT, CHECK (i > MAX(i)))] failed with rc 240 malformed database schema (t1) - misuse of aggregate function MAX()
[CREATE TABLE t1(i INT, CHECK (i > (SELECT MAX(i))))] failed with rc 240 malformed database schema (t1) - subqueries prohibited in CHECK constraints
(test='4. Test for updates')
(rows inserted=1)
[UPDATE t1 SET i = 10 WHERE i = 1] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_3DCCE6D2' unable to update record rc = 320
(i=1)
(test='5. Test for some obscure expressions')
[INSERT INTO t1 VALUES(1)] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_50FF6780' unable to add record rc = 320
[INSERT INTO t1 VALUES(11)] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_50FF6780' unable to add record rc = 320
(rows inserted=1)
(rows inserted=1)
[INSERT INTO t1 VALUES(1)] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_D376ECF2' unable to add record rc = 320
[INSERT INTO t1 VALUES(11)] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_D376ECF2' unable to add record rc = 320
[INSERT INTO t1 VALUES("aaa")] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_688DD408' unable to add record rc = 320
[INSERT INTO t1 VALUES("aaa")] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_1E2CE26C' unable to add record rc = 320
(test='6. Test for strings')
[INSERT INTO t1 VALUES('foo')] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_BB206189' unable to add record rc = 320
(rows inserted=1)
(v='bar')
[INSERT INTO t1 VALUES('foo')] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_3AA1A76' unable to add record rc = 320
(rows inserted=1)
(v='bar')
(test='7. TRUNCATE TABLE')
(rows inserted=3)
(COUNT(*)=3=1)
(COUNT(*)=0=1)
(test='8. Multiple check constraints')
(name='FK', type='FOREIGN KEY', tablename='t2', keyname='$KEY_1E994F88', foreigntablename='t1', foreignkeyname='$KEY_877B2989', iscascadingdelete='N', iscascadingupdate='N', expr=NULL)
(name='CONS1', type='CHECK', tablename=NULL, keyname=NULL, foreigntablename=NULL, foreignkeyname=NULL, iscascadingdelete=NULL, iscascadingupdate=NULL, expr='i > 10')
(name='$CONSTRAINT_9C3BA03F', type='CHECK', tablename=NULL, keyname=NULL, foreigntablename=NULL, foreignkeyname=NULL, iscascadingdelete=NULL, iscascadingupdate=NULL, expr='i < 100')
[INSERT INTO t2 values(1)] failed with rc 403 CHECK constraint violation CHECK constraint failed for 'CONS1' unable to add record rc = 320
[INSERT INTO t2 values(11)] failed with rc 3 Transaction violates foreign key constraint t2(i) -> t1(i): key value does not exist in parent table
[INSERT INTO t2 values(111)] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_9C3BA03F' unable to add record rc = 320
(rows inserted=3)
[INSERT INTO t2 values(1)] failed with rc 403 CHECK constraint violation CHECK constraint failed for 'CONS1' unable to add record rc = 320
(rows inserted=1)
[INSERT INTO t2 values(111)] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_9C3BA03F' unable to add record rc = 320
(i=1)
(i=11)
(i=111)
(test='9. Check expression using a keyword')
[CREATE TABLE t1(order VARCHAR(100), CHECK(order IN ("aaa")))] failed with rc -3 near "order": syntax error
[CREATE TABLE t1("order" VARCHAR(100), CHECK(order IN ("aaa")))] failed with rc -3 near "order": syntax error
[INSERT INTO t1 VALUES('aaaa')] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_A48D84B' unable to add record rc = 320
(rows inserted=1)
(order='aaa')
(test='10. Check during FK cascade')
(csc2='schema
{
int i null = yes
}
keys
{
uniqnulls "$KEY_877B2989" = i
}
')
(csc2='schema
{
int i null = yes
}
keys
{
uniqnulls "$KEY_1E994F88" = i
}
constraints
{
"$KEY_1E994F88" -> <"t1":"$KEY_877B2989"> on update cascade
check "valid_pct" = {where i BETWEEN 0 AND 100}
}
')
(rows inserted=1)
(rows inserted=1)
[UPDATE t1 SET i=110 WHERE i=1] failed with rc 3 CHECK constraint violation verify key constraint cannot cascade update table 't2' rc 320
(i=1)
(i=1)
(test='10. Test for NULLs')
(rows inserted=1)
(rows inserted=1)
(v=NULL)
(v='aaa')
(rows inserted=1)
(rows inserted=1)
(rows inserted=1)
[INSERT INTO t2 VALUES('baaa')] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_7823CE20' unable to add record rc = 320
(v=NULL)
(v='aaa')
(v='aaaa')
[INSERT INTO t3 VALUES(NULL)] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_9A13D8EC' unable to add record rc = 320
(rows inserted=1)
(rows inserted=1)
(v='')
(v='aaa')
(test='11. Test for VUTF8s')
(rows inserted=1)
[INSERT INTO t1 VALUES("long_fooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo")] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_1E783736' unable to add record rc = 320
[INSERT INTO t1 VALUES("fooz")] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_1E783736' unable to add record rc = 320
[INSERT INTO t1 VALUES("bar")] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_1E783736' unable to add record rc = 320
(rows inserted=1)
(foo_only=NULL)
(foo_only='foo')
(rows inserted=1)
[INSERT INTO t2 VALUES("long_fooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo")] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_1E783736' unable to add record rc = 320
[INSERT INTO t2 VALUES("fooz")] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_1E783736' unable to add record rc = 320
[INSERT INTO t2 VALUES("bar")] failed with rc 403 CHECK constraint violation CHECK constraint failed for '$CONSTRAINT_1E783736' unable to add record rc = 320
(rows inserted=1)
(foo_only=NULL)
(foo_only='foo')