Debian Codegraph @

mysql-test/suite/vcol/r/vcol_keys_innodb.result

raw
1 SET @@session.default_storage_engine = 'InnoDB';
2 # - UNIQUE KEY
3 # - INDEX
4 # - FULLTEXT INDEX
5 # - SPATIAL INDEX (not supported)
6 # - FOREIGN INDEX (partially supported)
7 # - CHECK (allowed but not used)
8 # UNIQUE
9 create table t1 (a int, b int as (a*2) unique);
10 drop table t1;
11 create table t1 (a int, b int as (a*2) persistent unique);
12 show create table t1;
13 Table Create Table
14 t1 CREATE TABLE `t1` (
15 `a` int(11) DEFAULT NULL,
16 `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
17 UNIQUE KEY `b` (`b`)
18 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
19 describe t1;
20 Field Type Null Key Default Extra
21 a int(11) YES NULL
22 b int(11) YES UNI NULL STORED GENERATED
23 drop table t1;
24 create table t1 (a int, b int as (a*2), unique key (b));
25 drop table t1;
26 create table t1 (a int, b int as (a*2) persistent, unique (b));
27 show create table t1;
28 Table Create Table
29 t1 CREATE TABLE `t1` (
30 `a` int(11) DEFAULT NULL,
31 `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
32 UNIQUE KEY `b` (`b`)
33 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
34 describe t1;
35 Field Type Null Key Default Extra
36 a int(11) YES NULL
37 b int(11) YES UNI NULL STORED GENERATED
38 drop table t1;
39 create table t1 (a int, b int as (a*2));
40 alter table t1 add unique key (b);
41 drop table t1;
42 create table t1 (a int, b int as (a*2) persistent);
43 alter table t1 add unique key (b);
44 drop table t1;
45 # Testing data manipulation operations involving UNIQUE keys
46 # on virtual columns can be found in:
47 # - vcol_ins_upd.inc
48 # - vcol_select.inc
49 #
50 # INDEX
51 create table t1 (a int, b int as (a*2), index (b));
52 drop table t1;
53 create table t1 (a int, b int as (a*2), index (a,b));
54 drop table t1;
55 create table t1 (a int, b int as (a*2) persistent, index (b));
56 show create table t1;
57 Table Create Table
58 t1 CREATE TABLE `t1` (
59 `a` int(11) DEFAULT NULL,
60 `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
61 KEY `b` (`b`)
62 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
63 describe t1;
64 Field Type Null Key Default Extra
65 a int(11) YES NULL
66 b int(11) YES MUL NULL STORED GENERATED
67 drop table t1;
68 create table t1 (a int, b int as (a*2) persistent, index (a,b));
69 show create table t1;
70 Table Create Table
71 t1 CREATE TABLE `t1` (
72 `a` int(11) DEFAULT NULL,
73 `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
74 KEY `a` (`a`,`b`)
75 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
76 describe t1;
77 Field Type Null Key Default Extra
78 a int(11) YES MUL NULL
79 b int(11) YES NULL STORED GENERATED
80 drop table t1;
81 create table t1 (a int, b int as (a*2));
82 alter table t1 add index (b);
83 alter table t1 add index (a,b);
84 drop table t1;
85 create table t1 (a int, b int as (a*2) persistent);
86 alter table t1 add index (b);
87 drop table t1;
88 create table t1 (a int, b int as (a*2) persistent);
89 alter table t1 add index (a,b);
90 create table t2 like t1;
91 drop table t2;
92 drop table t1;
93 # Testing data manipulation operations involving INDEX
94 # on virtual columns can be found in:
95 # - vcol_select.inc
96 #
97 # TODO: FULLTEXT INDEX
98 # SPATIAL INDEX
99 # Error "All parts of a SPATIAL index must be NOT NULL"
100 create table t1 (a int, b geometry as (a+1) persistent, spatial index (b));
101 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
102 create table t1 (a int, b int as (a+1) persistent);
103 alter table t1 add spatial index (b);
104 ERROR HY000: Incorrect arguments to SPATIAL INDEX
105 drop table t1;
106 # FOREIGN KEY
107 # Rejected FK options.
108 create table t1 (a int, b int as (a+1) persistent,
109 foreign key (b) references t2(a) on update set null);
110 ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
111 create table t1 (a int, b int as (a+1) persistent,
112 foreign key (b) references t2(a) on update cascade);
113 ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
114 create table t1 (a int, b int as (a+1) persistent,
115 foreign key (b) references t2(a) on delete set null);
116 ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
117 create table t1 (a int, b int as (a+1) persistent);
118 alter table t1 add foreign key (b) references t2(a) on update set null;
119 ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
120 alter table t1 add foreign key (b) references t2(a) on update cascade;
121 ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
122 alter table t1 add foreign key (b) references t2(a) on delete set null;
123 ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
124 drop table t1;
125 create table t1 (a int, b int as (a+1), foreign key (b) references t2(a));
126 ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
127 create table t1 (a int, b int as (a+1));
128 alter table t1 add foreign key (b) references t2(a);
129 ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
130 drop table t1;
131 # Allowed FK options.
132 create table t2 (a int primary key, b char(5));
133 create table t1 (a int, b int as (a % 10) persistent,
134 foreign key (b) references t2(a) on update restrict);
135 drop table t1;
136 create table t1 (a int, b int as (a % 10) persistent,
137 foreign key (b) references t2(a) on update no action);
138 drop table t1;
139 create table t1 (a int, b int as (a % 10) persistent,
140 foreign key (b) references t2(a) on delete 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 delete cascade);
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 no action);
147 drop table t1;
148
149 # Testing data manipulation operations involving FOREIGN KEY
150 # on virtual columns can be found in:
151 # - vcol_ins_upd.inc
152 # - vcol_select.inc
153 create table t1 (a int, b timestamp as (now()), key (b));
154 ERROR HY000: Function or expression 'current_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
155 create table t1 (a int, b timestamp as (now()));
156 alter table t1 add index (b);
157 ERROR HY000: Function or expression 'current_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
158 drop table t1;
159 create table t1 (a int, b varchar(100) as (user()), key (b));
160 ERROR HY000: Function or expression 'user()' cannot be used in the GENERATED ALWAYS AS clause of `b`
161 create table t1 (a int, b varchar(100) as (user()));
162 alter table t1 add index (b);
163 ERROR HY000: Function or expression 'user()' cannot be used in the GENERATED ALWAYS AS clause of `b`
164 drop table t1;
165 create table t1 (a int, b double as (rand()), key (b));
166 ERROR HY000: Function or expression 'rand()' cannot be used in the GENERATED ALWAYS AS clause of `b`
167 create table t1 (a int, b double as (rand()));
168 alter table t1 add index (b);
169 ERROR HY000: Function or expression 'rand()' cannot be used in the GENERATED ALWAYS AS clause of `b`
170 drop table t1;
171 CREATE OR REPLACE TABLE t1 (
172 f2 DOUBLE NOT NULL DEFAULT '0',
173 f3 DOUBLE NOT NULL DEFAULT '0',
174 f4 DOUBLE,
175 f5 DOUBLE DEFAULT '0',
176 v4 DOUBLE AS (IF(f4,f3,f2)) VIRTUAL,
177 KEY (f5),
178 KEY (v4)
179 );
180 INSERT INTO t1 (f2,f3,f4,f5) VALUES (5,4,1,0),(5,7,NULL,0);
181 INSERT INTO t1 (f2,f3,f4,f5) SELECT f2, f3, f5, f3 FROM t1;
182 INSERT INTO t1 (f2,f3,f4,f5) VALUES (5,0,NULL,1);
183 INSERT INTO t1 (f2,f3,f4,f5) SELECT f2, f5, f5, f3 FROM t1;
184 DELETE FROM t1 WHERE f5 = 1 OR v4 = 4 ORDER BY f5,v4 LIMIT 9;
185 SELECT * from t1;
186 f2 f3 f4 f5 v4
187 5 7 NULL 0 5
188 5 4 0 4 5
189 5 7 0 7 5
190 5 0 0 4 5
191 5 0 0 7 5
192 5 7 7 7 7
193 5 1 1 0 1
194 DROP TABLE t1;
195 CREATE TABLE t1 (
196 d DECIMAL(63,0) NOT NULL DEFAULT 0,
197 c VARCHAR(64) NOT NULL DEFAULT '',
198 vd DECIMAL(63,0) AS (d) VIRTUAL,
199 vc VARCHAR(2048) AS (c) VIRTUAL,
200 pk BIGINT AUTO_INCREMENT,
201 PRIMARY KEY(pk));
202 INSERT INTO t1 (d,c) VALUES (0.5,'foo');
203 Warnings:
204 Note 1265 Data truncated for column 'd' at row 1
205 SELECT * FROM t1 WHERE vc != 'bar' ORDER BY vd;
206 d c vd vc pk
207 1 foo 1 foo 1
208 DROP TABLE t1;
209 CREATE TABLE t1 (
210 pk BIGINT,
211 c CHAR(64) NOT NULL DEFAULT '',
212 vc CHAR(64) AS (c) VIRTUAL,
213 PRIMARY KEY(pk),
214 INDEX(vc(32))
215 );
216 DELETE FROM t1 WHERE vc IS NULL ORDER BY pk;
217 DROP TABLE t1;
218 SET sql_mode='';
219 CREATE TABLE t1 (a INT GENERATED ALWAYS AS (1) VIRTUAL,KEY(a)) ENGINE=MyISAM;
220 INSERT INTO t1 SELECT 1 FROM seq_1_to_2 ;
221 Warnings:
222 Warning 1906 The value specified for generated column 'a' in table 't1' has been ignored
223 Warning 1906 The value specified for generated column 'a' in table 't1' has been ignored
224 SELECT * FROM t1;
225 a
226 1
227 1
228 DROP TABLE t1;
229 CREATE TABLE t1 (a INT GENERATED ALWAYS AS (1) VIRTUAL,KEY(a)) ENGINE=MyISAM;
230 INSERT INTO t1 SELECT 1 UNION SELECT 1;
231 Warnings:
232 Warning 1906 The value specified for generated column 'a' in table 't1' has been ignored
233 SELECT * FROM t1;
234 a
235 1
236 DROP TABLE t1;
237 #
238 # MDEV-11737 Failing assertion: block->magic_n == MEM_BLOCK_MAGIC_N
239 #
240 CREATE TABLE t1 (i INT PRIMARY KEY, vi INT AS (i*2) VIRTUAL UNIQUE)
241 ENGINE=InnoDB;
242 CREATE TABLE t2 (i INT) ENGINE=InnoDB;
243 ALTER TABLE t1 ADD COLUMN col INT;
244 SELECT * FROM t1 WHERE vi < 2;
245 i vi col
246 DROP TABLE t1, t2;
247 create table t1 (
248 pk int auto_increment,
249 col_varchar varchar(847) not null default '',
250 col_int bigint(15) unsigned zerofill,
251 col_datetime datetime(3) not null default '1900-01-01 00:00:00',
252 col_time time(5) not null default '00:00:00',
253 col_blob text,
254 col_bit bit(34),
255 col_year year,
256 col_char char(10),
257 col_dec decimal(18,9) not null default 0,
258 col_enum enum('','a','b','c','d','e','f','foo','bar') not null default '',
259 col_date date not null default '1900-01-01',
260 col_timestamp timestamp(3) not null default '1971-01-01 00:00:00',
261 vcol_datetime datetime as (truncate(col_datetime,0)) virtual,
262 vcol_dec decimal(18,9) zerofill as (col_dec) virtual,
263 vcol_bit bit(63) as (col_bit) virtual,
264 vcol_char binary(51) as (col_char) virtual,
265 vcol_timestamp timestamp(5) as (col_timestamp) virtual,
266 vcol_enum enum('','a','b','c','d','e','f','foo','bar') as (col_enum) virtual,
267 vcol_int tinyint(48) zerofill as (col_int) virtual,
268 vcol_time time(4) as (col_time) virtual,
269 vcol_varchar varbinary(3873) as (col_varchar) virtual,
270 vcol_year year as (col_year) virtual,
271 vcol_date date as (col_date) virtual,
272 vcol_blob longtext as (col_blob) virtual,
273 primary key(pk)
274 ) engine=innodb;
275 insert into t1 (col_varchar,col_int,col_datetime,col_time,col_blob,col_bit,col_year,col_char,col_dec,col_enum,col_date,col_timestamp) values
276 ('foo',1,'2010-05-08 13:08:12.034783','18:32:14','foo',b'0111110101001001',1992,'f',0.2,'','1994-12-26','2019-01-11 00:00:00'),
277 ('bar',6,'1900-01-01 00:00:00','00:00:00','bar',b'10011000001101011000101',1985,'b',0.7,'','2028-04-06','1971-01-01 00:00:00');
278 alter table t1 add index(vcol_datetime);
279 drop table t1;
280 create table t1 (
281 pk int,
282 col_blob mediumtext not null default '',
283 vcol_blob tinyblob as (col_blob) virtual,
284 col_char char(22) null,
285 primary key(pk),
286 index(col_char,vcol_blob(64))
287 ) engine=innodb;
288 insert ignore into t1 (pk) values (1),(2);
289 update t1 set col_char = 'foo' where pk = 1;
290 drop table t1;
291 create table t1 (
292 id int not null primary key,
293 a varchar(200),
294 b varchar(200),
295 c int,
296 va char(200) generated always as (ucase(a)) virtual,
297 vb char(200) generated always as (ucase(b)) virtual,
298 key (c,va,vb)
299 ) engine=innodb;
300 insert t1 (id,a,c) select seq,seq,seq from seq_1_to_330;
301 select IF(@@innodb_sort_buffer_size < count(*)*200, 'GOOD', 'WRONG SIZE') from t1;
302 IF(@@innodb_sort_buffer_size < count(*)*200, 'GOOD', 'WRONG SIZE')
303 GOOD
304 alter table t1 drop column va;
305 drop table t1;
306 #
307 # MDEV-39081 InnoDB: tried to purge non-delete-marked record,
308 # assertion fails in row_purge_del_mark_error
309 #
310 CREATE TABLE t1(
311 pk INT AUTO_INCREMENT,
312 f1 INT DEFAULT 0,
313 f2 INT as (1 + 1) VIRTUAL,
314 KEY (f1), PRIMARY KEY (pk), UNIQUE (f2)) ENGINE=InnoDB;
315 START TRANSACTION WITH CONSISTENT SNAPSHOT;
316 CONNECT con1,localhost,root,,;
317 INSERT INTO t1 (pk) VALUES(1);
318 DELETE FROM t1;
319 INSERT INTO t1 (pk) VALUES(1);
320 UPDATE t1 set f1 = 2;
321 disconnect con1;
322 connection default;
323 COMMIT;
324 InnoDB 0 transactions not purged
325 DROP TABLE t1;