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
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
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
|
################################################################################
# inc/vcol_keys.inc #
# #
# Purpose: #
# Testing keys, indexes defined upon virtual columns. #
# #
# #
# #
#------------------------------------------------------------------------------#
# Original Author: Andrey Zhakov #
# Original Date: 2008-09-02 #
# Change Author: Oleksandr Byelkin (Monty program Ab)
# Date: 2009-03-24
# Change: Syntax changed
################################################################################
--echo # - UNIQUE KEY
--echo # - INDEX
--echo # - FULLTEXT INDEX
--echo # - SPATIAL INDEX (not supported)
--echo # - FOREIGN INDEX (partially supported)
--echo # - CHECK (allowed but not used)
--echo # UNIQUE
create table t1 (a int, b int as (a*2) unique);
drop table t1;
create table t1 (a int, b int as (a*2) persistent unique);
show create table t1;
describe t1;
drop table t1;
create table t1 (a int, b int as (a*2), unique key (b));
drop table t1;
create table t1 (a int, b int as (a*2) persistent, unique (b));
show create table t1;
describe t1;
drop table t1;
create table t1 (a int, b int as (a*2));
alter table t1 add unique key (b);
drop table t1;
create table t1 (a int, b int as (a*2) persistent);
alter table t1 add unique key (b);
drop table t1;
--echo # Testing data manipulation operations involving UNIQUE keys
--echo # on virtual columns can be found in:
--echo # - vcol_ins_upd.inc
--echo # - vcol_select.inc
--echo #
--echo # INDEX
create table t1 (a int, b int as (a*2), index (b));
drop table t1;
create table t1 (a int, b int as (a*2), index (a,b));
drop table t1;
create table t1 (a int, b int as (a*2) persistent, index (b));
show create table t1;
describe t1;
drop table t1;
create table t1 (a int, b int as (a*2) persistent, index (a,b));
show create table t1;
describe t1;
drop table t1;
create table t1 (a int, b int as (a*2));
alter table t1 add index (b);
alter table t1 add index (a,b);
drop table t1;
create table t1 (a int, b int as (a*2) persistent);
alter table t1 add index (b);
drop table t1;
create table t1 (a int, b int as (a*2) persistent);
alter table t1 add index (a,b);
create table t2 like t1;
drop table t2;
drop table t1;
--echo # Testing data manipulation operations involving INDEX
--echo # on virtual columns can be found in:
--echo # - vcol_select.inc
--echo #
--echo # TODO: FULLTEXT INDEX
--echo # SPATIAL INDEX
if (!$skip_spatial_index_check)
{
--echo # Error "All parts of a SPATIAL index must be NOT NULL"
--error ER_SPATIAL_CANT_HAVE_NULL
create table t1 (a int, b geometry as (a+1) persistent, spatial index (b));
create table t1 (a int, b int as (a+1) persistent);
--error ER_WRONG_ARGUMENTS
alter table t1 add spatial index (b);
drop table t1;
}
--echo # FOREIGN KEY
--echo # Rejected FK options.
--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
create table t1 (a int, b int as (a+1) persistent,
foreign key (b) references t2(a) on update set null);
--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
create table t1 (a int, b int as (a+1) persistent,
foreign key (b) references t2(a) on update cascade);
--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
create table t1 (a int, b int as (a+1) persistent,
foreign key (b) references t2(a) on delete set null);
create table t1 (a int, b int as (a+1) persistent);
--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
alter table t1 add foreign key (b) references t2(a) on update set null;
--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
alter table t1 add foreign key (b) references t2(a) on update cascade;
--error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
alter table t1 add foreign key (b) references t2(a) on delete set null;
drop table t1;
if ($with_foreign_keys) {
--error ER_CANT_CREATE_TABLE
create table t1 (a int, b int as (a+1), foreign key (b) references t2(a));
create table t1 (a int, b int as (a+1));
--error ER_CANT_CREATE_TABLE
alter table t1 add foreign key (b) references t2(a);
drop table t1;
}
--echo # Allowed FK options.
create table t2 (a int primary key, b char(5));
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on update restrict);
drop table t1;
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on update no action);
drop table t1;
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on delete restrict);
drop table t1;
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on delete cascade);
drop table t1;
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on delete no action);
drop table t1;
--echo
--echo # Testing data manipulation operations involving FOREIGN KEY
--echo # on virtual columns can be found in:
--echo # - vcol_ins_upd.inc
--echo # - vcol_select.inc
#
# Restrictions when indexed:
#
--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
create table t1 (a int, b timestamp as (now()), key (b));
create table t1 (a int, b timestamp as (now()));
--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
alter table t1 add index (b);
drop table t1;
--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
create table t1 (a int, b varchar(100) as (user()), key (b));
create table t1 (a int, b varchar(100) as (user()));
--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
alter table t1 add index (b);
drop table t1;
--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
create table t1 (a int, b double as (rand()), key (b));
create table t1 (a int, b double as (rand()));
--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
alter table t1 add index (b);
drop table t1;
#
# MDEV-11598 Assertion `!table || (!table->read_set... failed
#
CREATE OR REPLACE TABLE t1 (
f2 DOUBLE NOT NULL DEFAULT '0',
f3 DOUBLE NOT NULL DEFAULT '0',
f4 DOUBLE,
f5 DOUBLE DEFAULT '0',
v4 DOUBLE AS (IF(f4,f3,f2)) VIRTUAL,
KEY (f5),
KEY (v4)
);
INSERT INTO t1 (f2,f3,f4,f5) VALUES (5,4,1,0),(5,7,NULL,0);
INSERT INTO t1 (f2,f3,f4,f5) SELECT f2, f3, f5, f3 FROM t1;
INSERT INTO t1 (f2,f3,f4,f5) VALUES (5,0,NULL,1);
INSERT INTO t1 (f2,f3,f4,f5) SELECT f2, f5, f5, f3 FROM t1;
DELETE FROM t1 WHERE f5 = 1 OR v4 = 4 ORDER BY f5,v4 LIMIT 9;
SELECT * from t1;
DROP TABLE t1;
# Another similar failure
CREATE TABLE t1 (
d DECIMAL(63,0) NOT NULL DEFAULT 0,
c VARCHAR(64) NOT NULL DEFAULT '',
vd DECIMAL(63,0) AS (d) VIRTUAL,
vc VARCHAR(2048) AS (c) VIRTUAL,
pk BIGINT AUTO_INCREMENT,
PRIMARY KEY(pk));
INSERT INTO t1 (d,c) VALUES (0.5,'foo');
SELECT * FROM t1 WHERE vc != 'bar' ORDER BY vd;
DROP TABLE t1;
#
# MDEV-11729: Crash when using partial indexed virtual fields
#
CREATE TABLE t1 (
pk BIGINT,
c CHAR(64) NOT NULL DEFAULT '',
vc CHAR(64) AS (c) VIRTUAL,
PRIMARY KEY(pk),
INDEX(vc(32))
);
DELETE FROM t1 WHERE vc IS NULL ORDER BY pk;
DROP TABLE t1;
|
