Debian Codegraph @

mysql-test/suite/vcol/inc/vcol_keys.inc

raw
1 ################################################################################
2 # inc/vcol_keys.inc #
3 # #
4 # Purpose: #
5 # Testing keys, indexes defined upon virtual columns. #
6 # #
7 # #
8 # #
9 #------------------------------------------------------------------------------#
10 # Original Author: Andrey Zhakov #
11 # Original Date: 2008-09-02 #
12 # Change Author: Oleksandr Byelkin (Monty program Ab)
13 # Date: 2009-03-24
14 # Change: Syntax changed
15 ################################################################################
16
17
18 --source include/have_sequence.inc
19
20 --echo # - UNIQUE KEY
21 --echo # - INDEX
22 --echo # - FULLTEXT INDEX
23 --echo # - SPATIAL INDEX (not supported)
24 --echo # - FOREIGN INDEX (partially supported)
25 --echo # - CHECK (allowed but not used)
26
27 --echo # UNIQUE
28 create table t1 (a int, b int as (a*2) unique);
29 drop table t1;
30 create table t1 (a int, b int as (a*2) persistent unique);
31 show create table t1;
32 describe t1;
33 drop table t1;
34
35 create table t1 (a int, b int as (a*2), unique key (b));
36 drop table t1;
37 create table t1 (a int, b int as (a*2) persistent, unique (b));
38 show create table t1;
39 describe t1;
40 drop table t1;
41
42 create table t1 (a int, b int as (a*2));
43 alter table t1 add unique key (b);
44 drop table t1;
45 create table t1 (a int, b int as (a*2) persistent);
46 alter table t1 add unique key (b);
47 drop table t1;
48
49 --echo # Testing data manipulation operations involving UNIQUE keys
50 --echo # on virtual columns can be found in:
51 --echo # - vcol_ins_upd.inc
52 --echo # - vcol_select.inc
53
54 --echo #
55 --echo # INDEX
56 create table t1 (a int, b int as (a*2), index (b));
57 drop table t1;
58 create table t1 (a int, b int as (a*2), index (a,b));
59 drop table t1;
60
61 create table t1 (a int, b int as (a*2) persistent, index (b));
62 show create table t1;
63 describe t1;
64 drop table t1;
65
66 create table t1 (a int, b int as (a*2) persistent, index (a,b));
67 show create table t1;
68 describe t1;
69 drop table t1;
70
71 create table t1 (a int, b int as (a*2));
72 alter table t1 add index (b);
73 alter table t1 add index (a,b);
74 drop table t1;
75
76 create table t1 (a int, b int as (a*2) persistent);
77 alter table t1 add index (b);
78 drop table t1;
79
80 create table t1 (a int, b int as (a*2) persistent);
81 alter table t1 add index (a,b);
82 create table t2 like t1;
83 drop table t2;
84 drop table t1;
85
86 --echo # Testing data manipulation operations involving INDEX
87 --echo # on virtual columns can be found in:
88 --echo # - vcol_select.inc
89
90 --echo #
91 --echo # TODO: FULLTEXT INDEX
92
93 --echo # SPATIAL INDEX
94 if (!$skip_spatial_index_check)
95 {
96 --echo # Error "All parts of a SPATIAL index must be NOT NULL"
97 --error ER_INDEX_CANNOT_HAVE_NULL
98 create table t1 (a int, b geometry as (a+1) persistent, spatial index (b));
99 create table t1 (a int, b int as (a+1) persistent);
100 --error ER_WRONG_ARGUMENTS
101 alter table t1 add spatial index (b);
102 drop table t1;
103 }
104
105 --echo # FOREIGN KEY
106
107 --echo # Rejected FK options.
108 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
109 create table t1 (a int, b int as (a+1) persistent,
110 foreign key (b) references t2(a) on update set null);
111 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
112 create table t1 (a int, b int as (a+1) persistent,
113 foreign key (b) references t2(a) on update cascade);
114 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
115 create table t1 (a int, b int as (a+1) persistent,
116 foreign key (b) references t2(a) on delete set null);
117
118 create table t1 (a int, b int as (a+1) persistent);
119 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
120 alter table t1 add foreign key (b) references t2(a) on update set null;
121 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
122 alter table t1 add foreign key (b) references t2(a) on update cascade;
123 --error ER_WRONG_FK_OPTION_FOR_GENERATED_COLUMN
124 alter table t1 add foreign key (b) references t2(a) on delete set null;
125 drop table t1;
126
127 if ($with_foreign_keys) {
128 --error ER_CANT_CREATE_TABLE
129 create table t1 (a int, b int as (a+1), foreign key (b) references t2(a));
130
131 create table t1 (a int, b int as (a+1));
132 --error ER_CANT_CREATE_TABLE
133 alter table t1 add foreign key (b) references t2(a);
134 drop table t1;
135 }
136
137 --echo # Allowed FK options.
138 create table t2 (a int primary key, b char(5));
139 create table t1 (a int, b int as (a % 10) persistent,
140 foreign key (b) references t2(a) on update restrict);
141 drop table t1;
142 create table t1 (a int, b int as (a % 10) persistent,
143 foreign key (b) references t2(a) on update no action);
144 drop table t1;
145 create table t1 (a int, b int as (a % 10) persistent,
146 foreign key (b) references t2(a) on delete restrict);
147 drop table t1;
148 create table t1 (a int, b int as (a % 10) persistent,
149 foreign key (b) references t2(a) on delete cascade);
150 drop table t1;
151 create table t1 (a int, b int as (a % 10) persistent,
152 foreign key (b) references t2(a) on delete no action);
153 drop table t1;
154
155 --echo
156 --echo # Testing data manipulation operations involving FOREIGN KEY
157 --echo # on virtual columns can be found in:
158 --echo # - vcol_ins_upd.inc
159 --echo # - vcol_select.inc
160
161 #
162 # Restrictions when indexed:
163 #
164
165 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
166 create table t1 (a int, b timestamp as (now()), key (b));
167 create table t1 (a int, b timestamp as (now()));
168 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
169 alter table t1 add index (b);
170 drop table t1;
171
172 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
173 create table t1 (a int, b varchar(100) as (user()), key (b));
174 create table t1 (a int, b varchar(100) as (user()));
175 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
176 alter table t1 add index (b);
177 drop table t1;
178
179 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
180 create table t1 (a int, b double as (rand()), key (b));
181 create table t1 (a int, b double as (rand()));
182 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
183 alter table t1 add index (b);
184 drop table t1;
185
186 #
187 # MDEV-11598 Assertion `!table || (!table->read_set... failed
188 #
189
190 CREATE OR REPLACE TABLE t1 (
191 f2 DOUBLE NOT NULL DEFAULT '0',
192 f3 DOUBLE NOT NULL DEFAULT '0',
193 f4 DOUBLE,
194 f5 DOUBLE DEFAULT '0',
195 v4 DOUBLE AS (IF(f4,f3,f2)) VIRTUAL,
196 KEY (f5),
197 KEY (v4)
198 );
199
200 INSERT INTO t1 (f2,f3,f4,f5) VALUES (5,4,1,0),(5,7,NULL,0);
201 INSERT INTO t1 (f2,f3,f4,f5) SELECT f2, f3, f5, f3 FROM t1;
202 INSERT INTO t1 (f2,f3,f4,f5) VALUES (5,0,NULL,1);
203 INSERT INTO t1 (f2,f3,f4,f5) SELECT f2, f5, f5, f3 FROM t1;
204 DELETE FROM t1 WHERE f5 = 1 OR v4 = 4 ORDER BY f5,v4 LIMIT 9;
205 SELECT * from t1;
206 DROP TABLE t1;
207
208 # Another similar failure
209
210 CREATE TABLE t1 (
211 d DECIMAL(63,0) NOT NULL DEFAULT 0,
212 c VARCHAR(64) NOT NULL DEFAULT '',
213 vd DECIMAL(63,0) AS (d) VIRTUAL,
214 vc VARCHAR(2048) AS (c) VIRTUAL,
215 pk BIGINT AUTO_INCREMENT,
216 PRIMARY KEY(pk));
217
218 INSERT INTO t1 (d,c) VALUES (0.5,'foo');
219 SELECT * FROM t1 WHERE vc != 'bar' ORDER BY vd;
220 DROP TABLE t1;
221
222 #
223 # MDEV-11729: Crash when using partial indexed virtual fields
224 #
225
226 CREATE TABLE t1 (
227 pk BIGINT,
228 c CHAR(64) NOT NULL DEFAULT '',
229 vc CHAR(64) AS (c) VIRTUAL,
230 PRIMARY KEY(pk),
231 INDEX(vc(32))
232 );
233 DELETE FROM t1 WHERE vc IS NULL ORDER BY pk;
234 DROP TABLE t1;
235
236 #
237 # MDEV-34632 Assertion `table->field[0]->ptr >= table->record[0] &&
238 # table->field[0]->ptr <= table->record[0] + table->s->reclength' failed in
239 # void handler::assert_icp_limitations(uchar*)
240 #
241 SET sql_mode='';
242 CREATE TABLE t1 (a INT GENERATED ALWAYS AS (1) VIRTUAL,KEY(a)) ENGINE=MyISAM;
243 INSERT INTO t1 SELECT 1 FROM seq_1_to_2 ;
244 SELECT * FROM t1;
245 DROP TABLE t1;
246
247 CREATE TABLE t1 (a INT GENERATED ALWAYS AS (1) VIRTUAL,KEY(a)) ENGINE=MyISAM;
248 INSERT INTO t1 SELECT 1 UNION SELECT 1;
249 SELECT * FROM t1;
250 DROP TABLE t1;