Debian Codegraph @

mysql-test/suite/innodb/r/virtual_basic.result

raw
1 CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a), c CHAR(10), d CHAR(20), e CHAR(10) GENERATED ALWAYS AS (c), g INT);
2 INSERT INTO t VALUES(10, DEFAULT, "aa", "bb", DEFAULT, 20);
3 INSERT INTO t VALUES(11, DEFAULT, "jj", "kk", DEFAULT, 21);
4 CREATE INDEX idx ON t(e) algorithm=inplace;
5 INSERT INTO t VALUES(12, DEFAULT, 'mm', "nn", DEFAULT, 22);
6 SELECT e FROM t;
7 e
8 aa
9 jj
10 mm
11 DROP TABLE t;
12 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
13 INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
14 INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
15 INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
16 INSERT INTO t VALUES (null, null, DEFAULT, 'mm');
17 CREATE INDEX idx ON t(c);
18 SELECT c FROM t;
19 c
20 14
21 19
22 29
23 NULL
24 UPDATE t SET a = 10 WHERE a = 11;
25 SELECT c FROM t;
26 c
27 13
28 19
29 29
30 NULL
31 SELECT * FROM t;
32 a b c h
33 10 3 13 mm
34 18 1 19 mm
35 28 1 29 mm
36 NULL NULL NULL mm
37 DELETE FROM t WHERE a = 18;
38 SELECT c FROM t;
39 c
40 13
41 29
42 NULL
43 START TRANSACTION;
44 INSERT INTO t VALUES (128, 22, DEFAULT, "xx");
45 INSERT INTO t VALUES (1290, 212, DEFAULT, "xmx");
46 ROLLBACK;
47 SELECT c FROM t;
48 c
49 13
50 29
51 NULL
52 SELECT * FROM t;
53 a b c h
54 10 3 13 mm
55 28 1 29 mm
56 NULL NULL NULL mm
57 DROP TABLE t;
58 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p));
59 INSERT INTO t VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT);
60 INSERT INTO t VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT);
61 INSERT INTO t VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT);
62 SELECT c FROM t;
63 c
64 3
65 33
66 7
67 SELECT m FROM t;
68 m
69 11
70 30
71 5
72 SELECT p FROM t;
73 p
74 XXXAAA
75 ummuooo
76 uuuuu
77 SELECT * FROM t;
78 a b c h j m n p
79 1 2 3 uuu 9 11 uu uuuuu
80 11 22 33 AAA 8 30 XXX XXXAAA
81 3 4 7 uooo 1 5 umm ummuooo
82 update t set a = 13 where a =11;
83 delete from t where a =13;
84 DROP INDEX idx1 ON t;
85 DROP INDEX idx2 ON t;
86 DROP TABLE t;
87 /* Test large BLOB data */
88 CREATE TABLE `t` (
89 `a` BLOB,
90 `b` BLOB,
91 `c` BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
92 `h` VARCHAR(10) DEFAULT NULL
93 ) ENGINE=InnoDB;
94 INSERT INTO t VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, "kk");
95 CREATE INDEX idx ON t(c(100));
96 SELECT length(c) FROM t;
97 length(c)
98 32000
99 START TRANSACTION;
100 INSERT INTO t VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm');
101 ROLLBACK;
102 INSERT INTO t VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm');
103 START TRANSACTION;
104 UPDATE t SET a = REPEAT('m', 16000) WHERE a like "aaa%";
105 ROLLBACK;
106 SELECT COUNT(*) FROM t WHERE c like "aaa%";
107 COUNT(*)
108 1
109 DROP TABLE t;
110 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
111 INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
112 INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
113 INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
114 CREATE INDEX idx ON t(c);
115 START TRANSACTION;
116 UPDATE t SET a = 100 WHERE a = 11;
117 UPDATE t SET a =22 WHERE a = 18;
118 UPDATE t SET a = 33 WHERE a = 22;
119 SELECT c FROM t;
120 c
121 103
122 29
123 34
124 ROLLBACK;
125 SELECT c FROM t;
126 c
127 14
128 19
129 29
130 DROP TABLE t;
131 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
132 INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
133 INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
134 INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
135 CREATE INDEX idx ON t(c);
136 SELECT c FROM t;
137 c
138 14
139 19
140 29
141 START TRANSACTION;
142 SELECT c FROM t;
143 c
144 14
145 19
146 29
147 UPDATE t SET a = 19 WHERE a = 11;
148 SELECT c FROM t;
149 c
150 14
151 19
152 29
153 ROLLBACK;
154 SELECT c FROM t;
155 c
156 19
157 22
158 29
159 DROP TABLE t;
160 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + x), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, y)), x INT, y CHAR(20), z INT, INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p));
161 INSERT INTO t VALUES(1, 2, DEFAULT, "hhh", 3, DEFAULT, "nnn", DEFAULT, 4, "yyy", 5);
162 INSERT INTO t VALUES(2, 3, DEFAULT, "hhha", 4, DEFAULT, "nnna", DEFAULT, 5, "yyya", 6);
163 INSERT INTO t VALUES(12, 13, DEFAULT, "hhhb", 14, DEFAULT, "nnnb", DEFAULT, 15, "yyyb", 16);
164 CREATE INDEX idx6 ON t(p, c);
165 SELECT p, c FROM t;
166 p c
167 nnnayyya 5
168 nnnbyyyb 25
169 nnnyyy 3
170 START TRANSACTION;
171 INSERT INTO t VALUES(32, 33, DEFAULT, "hhhb", 34, DEFAULT, "nnnb", DEFAULT, 35, "yyyb", 36);
172 ROLLBACK;
173 UPDATE t SET a = 100 WHERE a = 1;
174 START TRANSACTION;
175 UPDATE t SET a = 1 WHERE a = 100;
176 ROLLBACK;
177 Drop TABLE t;
178 CREATE TABLE t1(a INT);
179 ALTER TABLE t1 add COLUMN (b INT generated always as (a+1) virtual, c INT as(5) virtual);
180 ALTER TABLE t1 add COLUMN (d INT generated always as (a+1) virtual, e INT as(5) virtual);
181 SELECT pos, base_pos FROM informatiON_schema.innodb_virtual order by pos;
182 pos base_pos
183 65537 0
184 65540 0
185 65542 2
186 196611 0
187 ALTER TABLE t1 add COLUMN (f INT generated always as (a+1) virtual, g INT as(5) virtual), DROP COLUMN e;
188 SELECT pos, base_pos FROM informatiON_schema.innodb_virtual order by pos;
189 pos base_pos
190 65537 0
191 65540 0
192 65542 2
193 196611 0
194 262148 0
195 DROP TABLE t1;
196 CREATE TABLE t1(a INT);
197 INSERT INTO t1 VALUES(1);
198 ALTER TABLE t1 add COLUMN (f INT generated always as (a+1) virtual, g INT );
199 ALTER TABLE t1 add COLUMN (h INT generated always as (a+1) virtual), add INDEX idx (h), algorithm=inplace;
200 ALTER TABLE t1 add COLUMN (h1 INT generated always as (a+1) virtual), add INDEX idx1 (h1);
201 affected rows: 0
202 info: Records: 0 Duplicates: 0 Warnings: 0
203 ALTER TABLE t1 DROP COLUMN h1, DROP INDEX idx;
204 DROP TABLE t1;
205 CREATE TABLE t1(a INT);
206 CREATE INDEX idx ON t1(a);
207 CREATE TABLE t3(a INT, b INT , INDEX(b), CONSTRAINT x FOREIGN KEY(b) REFERENCES t1(a));
208 CREATE TABLE t2(a INT, b INT generated always as (a+1) virtual, INDEX(b), CONSTRAINT y FOREIGN KEY(b) REFERENCES t1(a));
209 ERROR HY000: Foreign key 'y' uses virtual column 'b' which is not supported.
210 CREATE TABLE t2(a INT, b INT generated always as (a+1) virtual, INDEX(b));
211 DROP TABLE t3;
212 DROP TABLE t2;
213 DROP TABLE t1;
214 CREATE TABLE t1(a INT);
215 ALTER TABLE t1 add COLUMN (b INT generated always as (a+1) virtual, c INT as(5) virtual);
216 ALTER TABLE t1 change b x INT generated always as (a+1) virtual;
217 SELECT pos, base_pos FROM informatiON_schema.innodb_virtual order by pos;
218 pos base_pos
219 65537 0
220 65540 0
221 65542 2
222 DROP TABLE t1;
223 CREATE TABLE t (a TEXT, b TEXT GENERATED ALWAYS AS (a), fulltext INDEX idx (b));
224 ERROR HY000: 'Fulltext index on virtual generated column' is not supported for generated columns.
225 CREATE TABLE t (a TEXT, b TEXT GENERATED ALWAYS AS (a));
226 ALTER TABLE t ADD FULLTEXT INDEX (b);
227 ERROR HY000: 'Fulltext index on virtual generated column' is not supported for generated columns.
228 DROP TABLE t;
229 CREATE TABLE t (a geometry not null, b geometry GENERATED ALWAYS AS (a) not null, spatial INDEX idx (b));
230 ERROR HY000: 'Spatial index on virtual generated column' is not supported for generated columns.
231 CREATE TABLE t (a geometry not null, b geometry GENERATED ALWAYS AS (a) not null);
232 ALTER TABLE t ADD SPATIAL INDEX (b);
233 ERROR HY000: 'Spatial index on virtual generated column' is not supported for generated columns.
234 DROP TABLE t;
235 CREATE TABLE t (a INT DEFAULT 1, b INT DEFAULT 2, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
236 CREATE INDEX idx ON t(c);
237 INSERT INTO t(h)VALUES ('mm');
238 SELECT c FROM t;
239 c
240 3
241 CREATE unique INDEX idx1 ON t(c);
242 INSERT INTO t(h)VALUES ('mm');
243 ERROR 23000: Duplicate entry '3' for key 't.idx1'
244 DROP TABLE t;
245 CREATE TABLE `t1` ( `a` INT(11) DEFAULT NULL, `b` INT(11) DEFAULT NULL, `c` INT(11) GENERATED ALWAYS AS (a+b) VIRTUAL, `x` INT(11) NOT NULL, `h` VARCHAR(10) DEFAULT NULL, PRIMARY KEY (`x`), KEY `idx` (`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
246 Warnings:
247 Warning 1681 Integer display width is deprecated and will be removed in a future release.
248 Warning 1681 Integer display width is deprecated and will be removed in a future release.
249 Warning 1681 Integer display width is deprecated and will be removed in a future release.
250 Warning 1681 Integer display width is deprecated and will be removed in a future release.
251 INSERT INTO t1 VALUES (1, 2, DEFAULT, 3, 'mm');
252 INSERT INTO t1 VALUES (11, 22, DEFAULT, 23, 'mm');
253 UPDATE t1 SET x = 4 WHERE x =3;
254 DROP TABLE t1;
255 CREATE TABLE `t1` ( `a` INT(11) DEFAULT NULL, `b` INT(11) DEFAULT NULL, `c` INT(11) GENERATED ALWAYS AS (a+b) VIRTUAL, `x` INT(11) NOT NULL, `h` VARCHAR(10) DEFAULT NULL, KEY `idx` (`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
256 Warnings:
257 Warning 1681 Integer display width is deprecated and will be removed in a future release.
258 Warning 1681 Integer display width is deprecated and will be removed in a future release.
259 Warning 1681 Integer display width is deprecated and will be removed in a future release.
260 Warning 1681 Integer display width is deprecated and will be removed in a future release.
261 INSERT INTO t1 VALUES (1, 2, DEFAULT, 3, 'mm');
262 INSERT INTO t1 VALUES (11, 22, DEFAULT, 23, 'mm');
263 START TRANSACTION;
264 SELECT * FROM t1;
265 a b c x h
266 1 2 3 3 mm
267 11 22 33 23 mm
268 START TRANSACTION;
269 UPDATE t1 SET x = 15 WHERE x = 3;
270 UPDATE t1 SET b = 10 WHERE b=2;
271 ROLLBACK;
272 SELECT c FROM t1;
273 c
274 3
275 33
276 DROP TABLE t1;
277 CREATE TABLE `t` (
278 `a` INT(11) DEFAULT NULL,
279 `b` INT(11) DEFAULT NULL,
280 `c` INT(11) GENERATED ALWAYS AS (a+b) VIRTUAL,
281 `d` INT(11) GENERATED ALWAYS AS (a) VIRTUAL,
282 `h` INT(11) NOT NULL,
283 PRIMARY KEY (`h`),
284 KEY `idx` (`c`)
285 ) ENGINE=InnoDB;
286 Warnings:
287 Warning 1681 Integer display width is deprecated and will be removed in a future release.
288 Warning 1681 Integer display width is deprecated and will be removed in a future release.
289 Warning 1681 Integer display width is deprecated and will be removed in a future release.
290 Warning 1681 Integer display width is deprecated and will be removed in a future release.
291 Warning 1681 Integer display width is deprecated and will be removed in a future release.
292 INSERT INTO t VALUES (11, 3, DEFAULT, DEFAULT, 1);
293 INSERT INTO t VALUES (18, 1, DEFAULT, DEFAULT, 2);
294 INSERT INTO t VALUES (28, 1, DEFAULT, DEFAULT, 3);
295 INSERT INTO t VALUES (null, null, DEFAULT, DEFAULT, 4);
296 CREATE PROCEDURE UPDATE_t()
297 begin
298 DECLARE i INT DEFAULT 1;
299 WHILE (i <= 2000) DO
300 UPDATE t SET a = 100 + i WHERE h = 1;
301 SET i = i + 1;
302 END WHILE;
303 END|
304 CREATE PROCEDURE DELETE_insert_t()
305 begin
306 DECLARE i INT DEFAULT 1;
307 WHILE (i <= 2000) DO
308 UPDATE t SET a = 100 + i WHERE h = 1;
309 SET i = i + 1;
310 END WHILE;
311 END|
312 CALL UPDATE_t();
313 SELECT c FROM t;
314 c
315 19
316 2103
317 29
318 NULL
319 CALL DELETE_insert_t();
320 SELECT c FROM t;
321 c
322 19
323 2103
324 29
325 NULL
326 DROP INDEX idx ON t;
327 CALL UPDATE_t();
328 SELECT c FROM t;
329 c
330 19
331 2103
332 29
333 NULL
334 DROP PROCEDURE DELETE_insert_t;
335 DROP PROCEDURE UPDATE_t;
336 DROP TABLE t;
337 # Bug#20767937: WL8149:ASSERTION FAILED IN ROW_UPD_SEC_INDEX_ENTRY
338 CREATE TABLE b (
339 col_INT_nokey INTEGER NOT NULL,
340 col_INT_key INTEGER GENERATED ALWAYS AS (col_INT_nokey) VIRTUAL,
341 col_date_nokey DATE,
342 col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,
343 INTerval 30 day)) VIRTUAL,
344 col_datetime_nokey DATETIME NOT NULL,
345 col_time_nokey TIME NOT NULL,
346 col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey,
347 col_time_nokey)),
348 col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey,
349 col_time_nokey)),
350 col_VARCHAR_nokey VARCHAR(1) NOT NULL,
351 col_VARCHAR_key VARCHAR(2) GENERATED ALWAYS AS(CONCAT(col_VARCHAR_nokey,
352 col_VARCHAR_nokey)),
353 KEY (col_INT_key),
354 KEY (col_VARCHAR_key),
355 KEY (col_date_key),
356 KEY (col_time_key),
357 KEY (col_datetime_key),
358 KEY (col_INT_key, col_VARCHAR_key),
359 KEY (col_INT_key, col_VARCHAR_key, col_date_key,
360 col_time_key, col_datetime_key)
361 );
362 INSERT INTO b (
363 col_INT_nokey,
364 col_date_nokey,
365 col_time_nokey,
366 col_datetime_nokey,
367 col_VARCHAR_nokey
368 ) VALUES
369 (0, NULL, '21:22:34.025509', '2002-02-13 17:30:06.013935', 'j'),
370 (8, '2004-09-18', '10:50:38.059966', '2008-09-27
371 00:34:58.026613', 'v');
372 Warnings:
373 Warning 4095 Delimiter '\n' in position 10 in datetime value '2008-09-27
374 00:34:58.026613' at row 2 is deprecated. Prefer the standard ' '.
375 EXPLAIN SELECT col_INT_key FROM b;
376 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
377 1 SIMPLE b NULL index NULL col_INT_key 5 NULL 2 100.00 Using index
378 Warnings:
379 Note 1003 /* select#1 */ select `test`.`b`.`col_INT_key` AS `col_INT_key` from `test`.`b`
380 SELECT col_INT_key FROM b;
381 col_INT_key
382 0
383 8
384 SELECT col_INT_nokey, col_INT_key FROM b;
385 col_INT_nokey col_INT_key
386 0 0
387 8 8
388 DELETE FROM b;
389 DROP TABLE b;
390 CREATE TABLE `t` (
391 `a` VARCHAR(10000), `b` VARCHAR(3000),
392 `c` VARCHAR(14000) GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
393 `d` VARCHAR(5000) GENERATED ALWAYS AS (b) VIRTUAL,
394 `e` INT(11) GENERATED ALWAYS AS (10) VIRTUAL,
395 `h` INT(11) NOT NULL,
396 PRIMARY KEY (`h`) ) ROW_FORMAT=COMPACT ENGINE=InnoDB charset latin1;
397 Warnings:
398 Warning 1681 Integer display width is deprecated and will be removed in a future release.
399 Warning 1681 Integer display width is deprecated and will be removed in a future release.
400 SHOW CREATE TABLE t;
401 Table Create Table
402 t CREATE TABLE `t` (
403 `a` varchar(10000) DEFAULT NULL,
404 `b` varchar(3000) DEFAULT NULL,
405 `c` varchar(14000) GENERATED ALWAYS AS (concat(`a`,`b`)) VIRTUAL,
406 `d` varchar(5000) GENERATED ALWAYS AS (`b`) VIRTUAL,
407 `e` int GENERATED ALWAYS AS (10) VIRTUAL,
408 `h` int NOT NULL,
409 PRIMARY KEY (`h`)
410 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
411 INSERT INTO t VALUES (REPEAT('g', 10000), REPEAT('x', 2800), DEFAULT, DEFAULT, DEFAULT, 1);
412 INSERT INTO t VALUES (REPEAT('a', 9000), REPEAT('b', 2000), DEFAULT, DEFAULT, DEFAULT, 2);
413 INSERT INTO t VALUES (REPEAT('m', 8000), REPEAT('n', 3000), DEFAULT, DEFAULT, DEFAULT, 3);
414 CREATE INDEX idx ON t(c(100), d(20));
415 UPDATE t SET a = REPEAT(CAST(1 AS CHAR), 2000) WHERE h = 1;
416 CREATE PROCEDURE UPDATE_t()
417 begin
418 DECLARE i INT DEFAULT 1;
419 WHILE (i <= 100) DO
420 UPDATE t SET a = REPEAT(CAST(i AS CHAR), 2000) WHERE h = 1;
421 SET i = i + 1;
422 END WHILE;
423 END|
424 CREATE PROCEDURE DELETE_insert_t()
425 begin
426 DECLARE i INT DEFAULT 1;
427 WHILE (i <= 100) DO
428 DELETE FROM t WHERE h = 1;
429 INSERT INTO t VALUES (REPEAT(CAST(i AS CHAR), 2000) , REPEAT('b', 2000), DEFAULT, DEFAULT, DEFAULT, 1);
430 SET i = i + 1;
431 END WHILE;
432 END|
433 CALL UPDATE_t();
434 CALL DELETE_insert_t();
435 UPDATE t SET a = NULL WHERE h=1;
436 START TRANSACTION;
437 CALL UPDATE_t();
438 ROLLBACK;
439 DROP PROCEDURE DELETE_insert_t;
440 DROP PROCEDURE UPDATE_t;
441 DROP TABLE t;
442 CREATE TABLE `t` (
443 `a` BLOB,
444 `b` BLOB,
445 `c` BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
446 `d` BLOB GENERATED ALWAYS AS (b) VIRTUAL,
447 `e` INT(11) GENERATED ALWAYS AS (10) VIRTUAL,
448 `h` INT(11) NOT NULL,
449 PRIMARY KEY (`h`)
450 ) ENGINE=InnoDB;
451 Warnings:
452 Warning 1681 Integer display width is deprecated and will be removed in a future release.
453 Warning 1681 Integer display width is deprecated and will be removed in a future release.
454 INSERT INTO t VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, DEFAULT, DEFAULT, 1);
455 INSERT INTO t VALUES (REPEAT('a', 32000), REPEAT('b', 11000), DEFAULT, DEFAULT, DEFAULT, 2);
456 INSERT INTO t VALUES (REPEAT('m', 18000), REPEAT('n', 46000), DEFAULT, DEFAULT, DEFAULT, 3);
457 CREATE INDEX idx ON t(c(100), d(20));
458 UPDATE t SET a = NULL WHERE h=1;
459 UPDATE t SET a = REPEAT(CAST(1 AS CHAR), 2000) WHERE h = 1;
460 UPDATE t SET a = REPEAT(CAST(1 AS CHAR), 1000) WHERE h = 1;
461 CREATE PROCEDURE UPDATE_t()
462 begin
463 DECLARE i INT DEFAULT 1;
464 WHILE (i <= 200) DO
465 UPDATE t SET a = REPEAT(CAST(i AS CHAR), 2000) WHERE h = 1;
466 SET i = i + 1;
467 END WHILE;
468 END|
469 CREATE PROCEDURE DELETE_insert_t()
470 begin
471 DECLARE i INT DEFAULT 1;
472 WHILE (i <= 200) DO
473 DELETE FROM t WHERE h = 1;
474 INSERT INTO t VALUES (REPEAT(CAST(i AS CHAR), 2000) , REPEAT('b', 2000), DEFAULT, DEFAULT, DEFAULT, 1);
475 SET i = i + 1;
476 END WHILE;
477 END|
478 CALL UPDATE_t();
479 CALL DELETE_insert_t();
480 UPDATE t SET a = NULL WHERE h=1;
481 DROP PROCEDURE DELETE_insert_t;
482 DROP PROCEDURE UPDATE_t;
483 DROP TABLE t;
484 CREATE TABLE `t` (
485 `m1` INT(11) DEFAULT NULL,
486 `m2` INT(11) DEFAULT NULL,
487 `m3` INT(11) GENERATED ALWAYS AS (m1 + m2) VIRTUAL,
488 `m4` INT(11) DEFAULT NULL,
489 `m5` CHAR(10) DEFAULT NULL,
490 `m6` CHAR(12) GENERATED ALWAYS AS (m5) VIRTUAL,
491 `a` VARCHAR(10000) DEFAULT NULL,
492 `b` VARCHAR(3000) DEFAULT NULL,
493 `c` VARCHAR(14000) GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
494 `d` VARCHAR(5000) GENERATED ALWAYS AS (b) VIRTUAL,
495 `e` INT(11) GENERATED ALWAYS AS (10) VIRTUAL,
496 `h` INT(11) NOT NULL,
497 PRIMARY KEY (`h`),
498 KEY `m3` (`m3`),
499 KEY `c` (`c`(100)),
500 KEY `e` (`e`,`d`(20))
501 ) ENGINE=InnoDB charset latin1;
502 Warnings:
503 Warning 1681 Integer display width is deprecated and will be removed in a future release.
504 Warning 1681 Integer display width is deprecated and will be removed in a future release.
505 Warning 1681 Integer display width is deprecated and will be removed in a future release.
506 Warning 1681 Integer display width is deprecated and will be removed in a future release.
507 Warning 1681 Integer display width is deprecated and will be removed in a future release.
508 Warning 1681 Integer display width is deprecated and will be removed in a future release.
509 INSERT INTO t VALUES (1, 2, DEFAULT, 3, "aaa", DEFAULT, REPEAT('g', 10000), REPEAT('x', 2800), DEFAULT, DEFAULT, DEFAULT, 1);
510 INSERT INTO t VALUES (11, 21, DEFAULT, 31, "bbb", DEFAULT, REPEAT('a', 9000), REPEAT('b', 2000), DEFAULT, DEFAULT, DEFAULT, 2);
511 INSERT INTO t VALUES (21, 31, DEFAULT, 41, "zzz", DEFAULT, REPEAT('m', 8000), REPEAT('n', 3000), DEFAULT, DEFAULT, DEFAULT, 3);
512 ALTER TABLE t DROP COLUMN c;
513 DELETE FROM t;
514 DROP TABLE t;
515 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
516 INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
517 INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
518 INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
519 INSERT INTO t VALUES (null, null, DEFAULT, 'mm');
520 CREATE INDEX idx ON t(a, c);
521 SELECT a, c FROM t;
522 a c
523 11 14
524 18 19
525 28 29
526 NULL NULL
527 START TRANSACTION;
528 UPDATE t SET a = 13 where a = 11;
529 ROLLBACK;
530 DELETE FROM t;
531 DROP TABLE t;
532 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), m int);
533 INSERT INTO t VALUES (11, 3, DEFAULT, "a", 1);
534 INSERT INTO t VALUES (18, 1, DEFAULT, "b", 2);
535 INSERT INTO t VALUES (28, 1, DEFAULT, "c", 3 );
536 INSERT INTO t VALUES (null, null, DEFAULT, "d", 4);
537 CREATE INDEX idx ON t(a, c, h);
538 SELECT a, c FROM t;
539 a c
540 11 14
541 18 19
542 28 29
543 NULL NULL
544 START TRANSACTION;
545 UPDATE t SET m =10 WHERE m = 1;
546 UPDATE t SET h = "e" WHERE h="a";
547 ROLLBACK;
548 SELECT a, c, h FROM t;
549 a c h
550 11 14 a
551 18 19 b
552 28 29 c
553 NULL NULL d
554 DROP TABLE t;
555 CREATE TABLE `t1` (
556 `col1` int(11) NOT NULL,
557 `col2` int(11) NOT NULL,
558 `col3` int(11) NOT NULL,
559 `col4` int(11) DEFAULT NULL,
560 `col5` int(11) GENERATED ALWAYS AS (col2 % col3) VIRTUAL,
561 `col7` int(11) GENERATED ALWAYS AS (col5 * col5) VIRTUAL,
562 `col8` int(11) GENERATED ALWAYS AS (col5 % col5) VIRTUAL,
563 `col9` text,
564 `extra` int(11) DEFAULT NULL,
565 UNIQUE KEY `uidx` (`col5`)
566 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
567 Warnings:
568 Warning 1681 Integer display width is deprecated and will be removed in a future release.
569 Warning 1681 Integer display width is deprecated and will be removed in a future release.
570 Warning 1681 Integer display width is deprecated and will be removed in a future release.
571 Warning 1681 Integer display width is deprecated and will be removed in a future release.
572 Warning 1681 Integer display width is deprecated and will be removed in a future release.
573 Warning 1681 Integer display width is deprecated and will be removed in a future release.
574 Warning 1681 Integer display width is deprecated and will be removed in a future release.
575 Warning 1681 Integer display width is deprecated and will be removed in a future release.
576 ALTER TABLE t1 CHANGE COLUMN extra col6 INT;
577 SHOW CREATE TABLE t1;
578 Table Create Table
579 t1 CREATE TABLE `t1` (
580 `col1` int NOT NULL,
581 `col2` int NOT NULL,
582 `col3` int NOT NULL,
583 `col4` int DEFAULT NULL,
584 `col5` int GENERATED ALWAYS AS ((`col2` % `col3`)) VIRTUAL,
585 `col7` int GENERATED ALWAYS AS ((`col5` * `col5`)) VIRTUAL,
586 `col8` int GENERATED ALWAYS AS ((`col5` % `col5`)) VIRTUAL,
587 `col9` text,
588 `col6` int DEFAULT NULL,
589 UNIQUE KEY `uidx` (`col5`)
590 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
591 DROP TABLE t1;
592 CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a), c point, d point GENERATED ALWAYS AS (c), spatial index idx (d));
593 ERROR HY000: 'Spatial index on virtual generated column' is not supported for generated columns.
594 CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a), c CHAR(10), d char(20) GENERATED ALWAYS AS (c), fulltext index idx (d));
595 ERROR HY000: 'Fulltext index on virtual generated column' is not supported for generated columns.
596 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p));
597 INSERT INTO t VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT);
598 INSERT INTO t VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT);
599 INSERT INTO t VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT);
600 alter table t add x int, add xx int generated ALWAYS AS(x);
601 DROP TABLE t;
602 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p));
603 INSERT INTO t VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT);
604 INSERT INTO t VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT);
605 INSERT INTO t VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT);
606 ALTER TABLE t DROP COLUMN c, algorithm=inplace;
607 ALTER TABLE t DROP COLUMN p, ADD COLUMN s VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), algorithm=inplace;
608 SELECT s FROM t;
609 s
610 XXXAAA
611 ummuooo
612 uuuuu
613 ALTER TABLE t ADD x VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), ADD INDEX idx (x), algorithm=inplace;
614 DROP TABLE t;
615 CREATE TABLE `t1` (
616 `col1` int(11) DEFAULT NULL,
617 `col2` int(11) DEFAULT NULL,
618 `col3` int(11) DEFAULT NULL,
619 `col4` int(11) DEFAULT NULL,
620 `col5` int(11) GENERATED ALWAYS AS (col4 * col2) VIRTUAL,
621 `col6` int(11) GENERATED ALWAYS AS (col2 % col4) VIRTUAL,
622 `col7` int(11) GENERATED ALWAYS AS (col5 / col6) VIRTUAL,
623 `col8` int(11) GENERATED ALWAYS AS (col5 + col5) VIRTUAL,
624 `col9` text,
625 `extra` int(11) DEFAULT NULL
626 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
627 Warnings:
628 Warning 1681 Integer display width is deprecated and will be removed in a future release.
629 Warning 1681 Integer display width is deprecated and will be removed in a future release.
630 Warning 1681 Integer display width is deprecated and will be removed in a future release.
631 Warning 1681 Integer display width is deprecated and will be removed in a future release.
632 Warning 1681 Integer display width is deprecated and will be removed in a future release.
633 Warning 1681 Integer display width is deprecated and will be removed in a future release.
634 Warning 1681 Integer display width is deprecated and will be removed in a future release.
635 Warning 1681 Integer display width is deprecated and will be removed in a future release.
636 Warning 1681 Integer display width is deprecated and will be removed in a future release.
637 ALTER TABLE t1 DROP COLUMN col7;
638 DROP TABLE t1;
639 CREATE TABLE t1 (
640 col1 INTEGER NOT NULL,
641 gv_col INTEGER GENERATED ALWAYS AS (col1) VIRTUAL,
642 txt1 TEXT,
643 FULLTEXT INDEX fi(txt1)
644 );
645 select * from t1;
646 col1 gv_col txt1
647 DROP TABLE t1;
648 CREATE TABLE t1 (
649 col1 INTEGER NOT NULL,
650 col2 INTEGER NOT NULL,
651 col3 INTEGER DEFAULT NULL,
652 col4 INTEGER DEFAULT NULL,
653 col5 INTEGER DEFAULT NULL,
654 col6 INTEGER DEFAULT NULL,
655 col7 INTEGER DEFAULT NULL,
656 col8 INTEGER DEFAULT NULL,
657 col9 INTEGER DEFAULT NULL,
658 col10 INTEGER DEFAULT NULL,
659 col11 INTEGER DEFAULT NULL,
660 col12 INTEGER DEFAULT NULL,
661 col13 INTEGER DEFAULT NULL,
662 col14 INTEGER DEFAULT NULL,
663 col15 INTEGER DEFAULT NULL,
664 col16 INTEGER DEFAULT NULL,
665 col17 INTEGER DEFAULT NULL,
666 col18 INTEGER DEFAULT NULL,
667 col19 INTEGER DEFAULT NULL,
668 col20 INTEGER DEFAULT NULL,
669 col21 INTEGER DEFAULT NULL,
670 col22 INTEGER DEFAULT NULL,
671 col23 INTEGER DEFAULT NULL,
672 col24 INTEGER DEFAULT NULL,
673 col25 INTEGER DEFAULT NULL,
674 col26 INTEGER DEFAULT NULL,
675 col27 INTEGER DEFAULT NULL,
676 col28 INTEGER DEFAULT NULL,
677 col29 INTEGER DEFAULT NULL,
678 col30 INTEGER DEFAULT NULL,
679 col31 INTEGER DEFAULT NULL,
680 col32 INTEGER DEFAULT NULL,
681 col33 INTEGER DEFAULT NULL,
682 gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL,
683 KEY idx1 (gcol1)
684 );
685 INSERT INTO t1 (col1, col2)
686 VALUES (0,1), (1,2), (2,3), (3,4), (4,5);
687 SELECT gcol1 FROM t1 FORCE INDEX(idx1);
688 gcol1
689 1
690 3
691 5
692 7
693 9
694 ALTER TABLE t1 ADD COLUMN extra INTEGER;
695 SELECT gcol1 FROM t1 FORCE INDEX(idx1);
696 gcol1
697 1
698 3
699 5
700 7
701 9
702 DROP TABLE t1;
703 CREATE TABLE t1 (
704 id INT NOT NULL,
705 store_id INT NOT NULL,
706 x INT GENERATED ALWAYS AS (id + store_id)
707 )
708 PARTITION BY RANGE (store_id) (
709 PARTITION p0 VALUES LESS THAN (6),
710 PARTITION p1 VALUES LESS THAN (11),
711 PARTITION p2 VALUES LESS THAN (16),
712 PARTITION p3 VALUES LESS THAN (21)
713 );
714 INSERT INTO t1 VALUES(1, 2, default);
715 INSERT INTO t1 VALUES(3, 4, default);
716 INSERT INTO t1 VALUES(3, 12, default);
717 INSERT INTO t1 VALUES(4, 18, default);
718 CREATE INDEX idx ON t1(x);
719 SELECT x FROM t1;
720 x
721 15
722 22
723 3
724 7
725 DROP TABLE t1;
726 CREATE TABLE t1 (
727 id INT NOT NULL,
728 store_id INT NOT NULL,
729 x INT GENERATED ALWAYS AS (id + store_id)
730 )
731 PARTITION BY RANGE (x) (
732 PARTITION p0 VALUES LESS THAN (6),
733 PARTITION p1 VALUES LESS THAN (11),
734 PARTITION p2 VALUES LESS THAN (16),
735 PARTITION p3 VALUES LESS THAN (21)
736 );
737 insert into t1 values(1, 2, default);
738 insert into t1 values(3, 4, default);
739 insert into t1 values(3, 12, default);
740 insert into t1 values(4, 18, default);
741 ERROR HY000: Table has no partition for value 22
742 CREATE INDEX idx ON t1(x);
743 SELECT x FROM t1;
744 x
745 15
746 3
747 7
748 DROP TABLE t1;
749 CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a+1) ,c int) PARTITION BY RANGE (b) (
750 PARTITION p0 VALUES LESS THAN (6),
751 PARTITION p1 VALUES LESS THAN (11),
752 PARTITION p2 VALUES LESS THAN (16),
753 PARTITION p3 VALUES LESS THAN (21) );
754 INSERT INTO t1 VALUES (10,DEFAULT,2);
755 INSERT INTO t1 VALUES (19,DEFAULT,8);
756 CREATE INDEX idx ON t1 (b);
757 INSERT INTO t1 VALUES (5,DEFAULT,9);
758 SELECT * FROM t1;
759 a b c
760 10 11 2
761 19 20 8
762 5 6 9
763 ALTER TABLE t1 REMOVE PARTITIONING;
764 DROP TABLE t1;
765 CREATE TABLE `t#P#1` (a INT, bt INT GENERATED ALWAYS AS (a+1) ,c int)
766 PARTITION BY RANGE (bt)
767 subpartition by hash (bt)
768 (
769 PARTITION p0 VALUES LESS THAN (6) (
770 SUBPARTITION s0,
771 SUBPARTITION s1),
772 PARTITION p1 VALUES LESS THAN (11) (
773 SUBPARTITION s2,
774 SUBPARTITION s3),
775 PARTITION p2 VALUES LESS THAN (16) (
776 SUBPARTITION s4,
777 SUBPARTITION s5),
778 PARTITION p3 VALUES LESS THAN (21) (
779 SUBPARTITION s6,
780 SUBPARTITION s7)
781 );
782 insert into `t#P#1` values (10,DEFAULT,2);
783 insert into `t#P#1` values (19,DEFAULT,8);
784 create index idx on `t#P#1` (bt);
785 insert into `t#P#1` values (5,DEFAULT,9);
786 select * from `t#P#1`;
787 a bt c
788 5 6 9
789 10 11 2
790 19 20 8
791 alter table `t#P#1` remove partitioning;
792 drop table `t#P#1`;
793 CREATE TABLE `t` (
794 `a` VARCHAR(10000), `b` VARCHAR(3000),
795 `c` VARCHAR(14000) GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
796 `d` VARCHAR(5000) GENERATED ALWAYS AS (b) VIRTUAL,
797 `e` INT(11) GENERATED ALWAYS AS (10) VIRTUAL,
798 `h` INT(11) NOT NULL,
799 PRIMARY KEY (`h`) ) ROW_FORMAT=DYNAMIC ENGINE=InnoDB charset latin1;
800 Warnings:
801 Warning 1681 Integer display width is deprecated and will be removed in a future release.
802 Warning 1681 Integer display width is deprecated and will be removed in a future release.
803 SHOW CREATE TABLE t;
804 Table Create Table
805 t CREATE TABLE `t` (
806 `a` varchar(10000) DEFAULT NULL,
807 `b` varchar(3000) DEFAULT NULL,
808 `c` varchar(14000) GENERATED ALWAYS AS (concat(`a`,`b`)) VIRTUAL,
809 `d` varchar(5000) GENERATED ALWAYS AS (`b`) VIRTUAL,
810 `e` int GENERATED ALWAYS AS (10) VIRTUAL,
811 `h` int NOT NULL,
812 PRIMARY KEY (`h`)
813 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
814 INSERT INTO t VALUES (REPEAT('g', 10000), REPEAT('x', 2800), DEFAULT, DEFAULT, DEFAULT, 1);
815 INSERT INTO t VALUES (REPEAT('a', 9000), REPEAT('b', 2000), DEFAULT, DEFAULT, DEFAULT, 2);
816 INSERT INTO t VALUES (REPEAT('m', 8000), REPEAT('n', 3000), DEFAULT, DEFAULT, DEFAULT, 3);
817 CREATE INDEX idx ON t(c(100), d(20));
818 UPDATE t SET a = REPEAT(CAST(1 AS CHAR), 2000) WHERE h = 1;
819 CREATE PROCEDURE UPDATE_t()
820 begin
821 DECLARE i INT DEFAULT 1;
822 WHILE (i <= 100) DO
823 UPDATE t SET a = REPEAT(CAST(i AS CHAR), 2000) WHERE h = 1;
824 SET i = i + 1;
825 END WHILE;
826 END|
827 CREATE PROCEDURE DELETE_insert_t()
828 begin
829 DECLARE i INT DEFAULT 1;
830 WHILE (i <= 100) DO
831 DELETE FROM t WHERE h = 1;
832 INSERT INTO t VALUES (REPEAT(CAST(i AS CHAR), 2000) , REPEAT('b', 2000), DEFAULT, DEFAULT, DEFAULT, 1);
833 SET i = i + 1;
834 END WHILE;
835 END|
836 CALL UPDATE_t();
837 CALL DELETE_insert_t();
838 UPDATE t SET a = NULL WHERE h=1;
839 START TRANSACTION;
840 CALL UPDATE_t();
841 ROLLBACK;
842 DROP PROCEDURE DELETE_insert_t;
843 DROP PROCEDURE UPDATE_t;
844 DROP TABLE t;
845 CREATE TABLE `t` (
846 `a` VARCHAR(10000), `b` VARCHAR(3000),
847 `c` VARCHAR(14000) GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
848 `d` VARCHAR(5000) GENERATED ALWAYS AS (b) VIRTUAL,
849 `e` INT(11) GENERATED ALWAYS AS (10) VIRTUAL,
850 `h` INT(11) NOT NULL,
851 PRIMARY KEY (`h`) ) ROW_FORMAT=REDUNDANT ENGINE=InnoDB charset latin1;
852 Warnings:
853 Warning 1681 Integer display width is deprecated and will be removed in a future release.
854 Warning 1681 Integer display width is deprecated and will be removed in a future release.
855 SHOW CREATE TABLE t;
856 Table Create Table
857 t CREATE TABLE `t` (
858 `a` varchar(10000) DEFAULT NULL,
859 `b` varchar(3000) DEFAULT NULL,
860 `c` varchar(14000) GENERATED ALWAYS AS (concat(`a`,`b`)) VIRTUAL,
861 `d` varchar(5000) GENERATED ALWAYS AS (`b`) VIRTUAL,
862 `e` int GENERATED ALWAYS AS (10) VIRTUAL,
863 `h` int NOT NULL,
864 PRIMARY KEY (`h`)
865 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT
866 INSERT INTO t VALUES (REPEAT('g', 10000), REPEAT('x', 2800), DEFAULT, DEFAULT, DEFAULT, 1);
867 INSERT INTO t VALUES (REPEAT('a', 9000), REPEAT('b', 2000), DEFAULT, DEFAULT, DEFAULT, 2);
868 INSERT INTO t VALUES (REPEAT('m', 8000), REPEAT('n', 3000), DEFAULT, DEFAULT, DEFAULT, 3);
869 CREATE INDEX idx ON t(c(100), d(20));
870 UPDATE t SET a = REPEAT(CAST(1 AS CHAR), 2000) WHERE h = 1;
871 CREATE PROCEDURE UPDATE_t()
872 begin
873 DECLARE i INT DEFAULT 1;
874 WHILE (i <= 100) DO
875 UPDATE t SET a = REPEAT(CAST(i AS CHAR), 2000) WHERE h = 1;
876 SET i = i + 1;
877 END WHILE;
878 END|
879 CREATE PROCEDURE DELETE_insert_t()
880 begin
881 DECLARE i INT DEFAULT 1;
882 WHILE (i <= 100) DO
883 DELETE FROM t WHERE h = 1;
884 INSERT INTO t VALUES (REPEAT(CAST(i AS CHAR), 2000) , REPEAT('b', 2000), DEFAULT, DEFAULT, DEFAULT, 1);
885 SET i = i + 1;
886 END WHILE;
887 END|
888 CALL UPDATE_t();
889 CALL DELETE_insert_t();
890 UPDATE t SET a = NULL WHERE h=1;
891 START TRANSACTION;
892 CALL UPDATE_t();
893 ROLLBACK;
894 DROP PROCEDURE DELETE_insert_t;
895 DROP PROCEDURE UPDATE_t;
896 DROP TABLE t;
897 CREATE TABLE `t` (
898 `a` VARCHAR(10000), `b` VARCHAR(3000),
899 `c` VARCHAR(14000) GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
900 `d` VARCHAR(5000) GENERATED ALWAYS AS (b) VIRTUAL,
901 `e` INT(11) GENERATED ALWAYS AS (10) VIRTUAL,
902 `h` INT(11) NOT NULL,
903 PRIMARY KEY (`h`) ) ROW_FORMAT=COMPRESSED ENGINE=InnoDB charset latin1;
904 Warnings:
905 Warning 1681 Integer display width is deprecated and will be removed in a future release.
906 Warning 1681 Integer display width is deprecated and will be removed in a future release.
907 SHOW CREATE TABLE t;
908 Table Create Table
909 t CREATE TABLE `t` (
910 `a` varchar(10000) DEFAULT NULL,
911 `b` varchar(3000) DEFAULT NULL,
912 `c` varchar(14000) GENERATED ALWAYS AS (concat(`a`,`b`)) VIRTUAL,
913 `d` varchar(5000) GENERATED ALWAYS AS (`b`) VIRTUAL,
914 `e` int GENERATED ALWAYS AS (10) VIRTUAL,
915 `h` int NOT NULL,
916 PRIMARY KEY (`h`)
917 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
918 INSERT INTO t VALUES (REPEAT('g', 10000), REPEAT('x', 2800), DEFAULT, DEFAULT, DEFAULT, 1);
919 INSERT INTO t VALUES (REPEAT('a', 9000), REPEAT('b', 2000), DEFAULT, DEFAULT, DEFAULT, 2);
920 INSERT INTO t VALUES (REPEAT('m', 8000), REPEAT('n', 3000), DEFAULT, DEFAULT, DEFAULT, 3);
921 CREATE INDEX idx ON t(c(100), d(20));
922 UPDATE t SET a = REPEAT(CAST(1 AS CHAR), 2000) WHERE h = 1;
923 CREATE PROCEDURE UPDATE_t()
924 begin
925 DECLARE i INT DEFAULT 1;
926 WHILE (i <= 100) DO
927 UPDATE t SET a = REPEAT(CAST(i AS CHAR), 2000) WHERE h = 1;
928 SET i = i + 1;
929 END WHILE;
930 END|
931 CREATE PROCEDURE DELETE_insert_t()
932 begin
933 DECLARE i INT DEFAULT 1;
934 WHILE (i <= 100) DO
935 DELETE FROM t WHERE h = 1;
936 INSERT INTO t VALUES (REPEAT(CAST(i AS CHAR), 2000) , REPEAT('b', 2000), DEFAULT, DEFAULT, DEFAULT, 1);
937 SET i = i + 1;
938 END WHILE;
939 END|
940 CALL UPDATE_t();
941 CALL DELETE_insert_t();
942 UPDATE t SET a = NULL WHERE h=1;
943 START TRANSACTION;
944 CALL UPDATE_t();
945 ROLLBACK;
946 DROP PROCEDURE DELETE_insert_t;
947 DROP PROCEDURE UPDATE_t;
948 DROP TABLE t;
949 CREATE TABLE t(a TEXT CHARSET UTF8)ENGINE=INNODB;
950 Warnings:
951 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
952 ALTER TABLE t ADD COLUMN b BLOB GENERATED ALWAYS AS (a) VIRTUAL ;
953 ALTER TABLE t ADD FULLTEXT INDEX (a) ;
954 Warnings:
955 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
956 ALTER TABLE t ADD INDEX (b(1)) ;
957 DROP TABLE t;
958 CREATE TABLE t(a TEXT CHARSET UTF8, FULLTEXT INDEX(a))ENGINE=INNODB;
959 Warnings:
960 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
961 ALTER TABLE t ADD COLUMN b BLOB GENERATED ALWAYS AS (a) VIRTUAL ;
962 ALTER TABLE t ADD INDEX (b(1)) ;
963 DROP TABLE t;
964 CREATE TABLE t(a TEXT CHARSET UTF8)ENGINE=INNODB;
965 Warnings:
966 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
967 ALTER TABLE t ADD COLUMN FTS_DOC_ID BLOB GENERATED ALWAYS AS (a) VIRTUAL ;
968 ALTER TABLE t ADD FULLTEXT INDEX (a) ;
969 ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
970 DROP TABLE t;
971 create table t (a int,b int,c int,d int,e int,
972 f int generated always as (a+b) virtual,
973 g int,h blob,i int,unique key (d,h(25))) engine=innodb;
974 select h from t where d is null;
975 h
976 drop table t;
977 create table t(a blob not null) engine=innodb;
978 alter table t add column b int;
979 alter table t add column c varbinary (1000) generated always as (a) virtual;
980 alter table t add unique index (c(39));
981 replace into t set a = 'a',b =1;
982 replace into t set a = 'a',b =1;
983 drop table t;
984 CREATE TABLE t1 (doc JSON);
985 ALTER TABLE t1
986 ADD COLUMN `$col_varchar` VARCHAR(10) GENERATED ALWAYS AS
987 (JSON_UNQUOTE(JSON_EXTRACT(doc, '$.col_varchar'))),
988 ADD COLUMN `$col_int` INT GENERATED ALWAYS AS (JSON_EXTRACT(doc,
989 '$.col_int'));
990 ALTER TABLE t1 ADD COLUMN `$col_1` INT GENERATED ALWAYS AS (JSON_EXTRACT(doc,
991 '$.col_1'));
992 ALTER TABLE t1 DROP COLUMN `$col_varchar`, DROP COLUMN `$col_int`;
993 DROP TABLE t1;
994 CREATE TABLE t (a INT, b INT, h VARCHAR(10));
995 INSERT INTO t VALUES (12, 3, "ss");
996 INSERT INTO t VALUES (13, 4, "ss");
997 INSERT INTO t VALUES (14, 0, "ss");
998 alter table t add c INT GENERATED ALWAYS AS(a/b);
999 create index idx on t(c);
1000 ERROR 22012: Division by 0
1001 DROP TABLE t;
1002 CREATE TABLE t (
1003 pk INTEGER AUTO_INCREMENT,
1004 col_int_nokey INTEGER /*! NULL */,
1005 col_int INT GENERATED ALWAYS AS (col_int_nokey +
1006 col_int_nokey) STORED not null,
1007 col_int_key INTEGER GENERATED ALWAYS AS (col_int +
1008 col_int_nokey) VIRTUAL not null,
1009 col_date_nokey DATE /*! NULL */,
1010 col_date DATE GENERATED ALWAYS AS
1011 (DATE_ADD(col_date_nokey,interval 30 day)) STORED not null,
1012 col_date_key DATE GENERATED ALWAYS AS
1013 (DATE_ADD(col_date,interval 30 day)) VIRTUAL not null,
1014 col_datetime_nokey DATETIME /*! NULL */,
1015 col_time_nokey TIME /*! NULL */,
1016 col_datetime DATETIME GENERATED ALWAYS AS
1017 (ADDTIME(col_datetime_nokey, col_time_nokey)) STORED not null,
1018 col_time TIME GENERATED ALWAYS AS
1019 (ADDTIME(col_datetime_nokey, col_time_nokey)) STORED not null,
1020 col_datetime_key DATETIME GENERATED ALWAYS AS
1021 (ADDTIME(col_datetime, col_time_nokey)) VIRTUAL not null,
1022 col_time_key TIME GENERATED ALWAYS AS
1023 (ADDTIME(col_datetime_nokey, col_time)) VIRTUAL not null,
1024 col_varchar_nokey VARCHAR(1) /*! NULL */,
1025 col_varchar VARCHAR(2) GENERATED ALWAYS AS
1026 (CONCAT(col_varchar_nokey,col_varchar_nokey)) STORED not null,
1027 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
1028 (CONCAT(col_varchar, 'x')) VIRTUAL not null,
1029 unique KEY (pk,col_int_key),
1030 KEY(col_int),
1031 KEY(col_date),
1032 KEY(col_datetime),
1033 KEY(col_time),
1034 KEY(col_varchar),
1035 UNIQUE KEY (col_int_key),
1036 KEY (col_time_key),
1037 KEY (col_datetime_key),
1038 UNIQUE KEY (col_int_key, col_varchar_key),
1039 KEY (col_int_key, col_int_nokey),
1040 KEY(col_int_key,col_date_key),
1041 KEY(col_int_key, col_time_key),
1042 KEY(col_int_key, col_datetime_key),
1043 KEY(col_date_key,col_time_key,col_datetime_key),
1044 KEY (col_varchar_key, col_varchar_nokey),
1045 UNIQUE KEY (col_int_key, col_varchar_key,
1046 col_date_key, col_time_key, col_datetime_key)
1047 ) AUTO_INCREMENT=10 ENGINE=INNODB PARTITION BY
1048 KEY(col_int_key) PARTITIONS 3;
1049 ALTER TABLE t DROP COLUMN `pk`;
1050 Warnings:
1051 Warning 1831 Duplicate index 'pk' defined on the table 'test.t'. This is deprecated and will be disallowed in a future release.
1052 SHOW CREATE TABLE t;
1053 Table Create Table
1054 t CREATE TABLE `t` (
1055 `col_int_nokey` int DEFAULT NULL,
1056 `col_int` int GENERATED ALWAYS AS ((`col_int_nokey` + `col_int_nokey`)) STORED NOT NULL,
1057 `col_int_key` int GENERATED ALWAYS AS ((`col_int` + `col_int_nokey`)) VIRTUAL NOT NULL,
1058 `col_date_nokey` date DEFAULT NULL,
1059 `col_date` date GENERATED ALWAYS AS ((`col_date_nokey` + interval 30 day)) STORED NOT NULL,
1060 `col_date_key` date GENERATED ALWAYS AS ((`col_date` + interval 30 day)) VIRTUAL NOT NULL,
1061 `col_datetime_nokey` datetime DEFAULT NULL,
1062 `col_time_nokey` time DEFAULT NULL,
1063 `col_datetime` datetime GENERATED ALWAYS AS (addtime(`col_datetime_nokey`,`col_time_nokey`)) STORED NOT NULL,
1064 `col_time` time GENERATED ALWAYS AS (addtime(`col_datetime_nokey`,`col_time_nokey`)) STORED NOT NULL,
1065 `col_datetime_key` datetime GENERATED ALWAYS AS (addtime(`col_datetime`,`col_time_nokey`)) VIRTUAL NOT NULL,
1066 `col_time_key` time GENERATED ALWAYS AS (addtime(`col_datetime_nokey`,`col_time`)) VIRTUAL NOT NULL,
1067 `col_varchar_nokey` varchar(1) DEFAULT NULL,
1068 `col_varchar` varchar(2) GENERATED ALWAYS AS (concat(`col_varchar_nokey`,`col_varchar_nokey`)) STORED NOT NULL,
1069 `col_varchar_key` varchar(2) GENERATED ALWAYS AS (concat(`col_varchar`,_utf8mb4'x')) VIRTUAL NOT NULL,
1070 UNIQUE KEY `pk` (`col_int_key`),
1071 UNIQUE KEY `col_int_key` (`col_int_key`),
1072 UNIQUE KEY `col_int_key_2` (`col_int_key`,`col_varchar_key`),
1073 UNIQUE KEY `col_int_key_7` (`col_int_key`,`col_varchar_key`,`col_date_key`,`col_time_key`,`col_datetime_key`),
1074 KEY `col_int` (`col_int`),
1075 KEY `col_date` (`col_date`),
1076 KEY `col_datetime` (`col_datetime`),
1077 KEY `col_time` (`col_time`),
1078 KEY `col_varchar` (`col_varchar`),
1079 KEY `col_time_key` (`col_time_key`),
1080 KEY `col_datetime_key` (`col_datetime_key`),
1081 KEY `col_int_key_3` (`col_int_key`,`col_int_nokey`),
1082 KEY `col_int_key_4` (`col_int_key`,`col_date_key`),
1083 KEY `col_int_key_5` (`col_int_key`,`col_time_key`),
1084 KEY `col_int_key_6` (`col_int_key`,`col_datetime_key`),
1085 KEY `col_date_key` (`col_date_key`,`col_time_key`,`col_datetime_key`),
1086 KEY `col_varchar_key` (`col_varchar_key`,`col_varchar_nokey`)
1087 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1088 /*!50100 PARTITION BY KEY (col_int_key)
1089 PARTITIONS 3 */
1090 DROP TABLE t;
1091 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
1092 INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
1093 INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
1094 INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
1095 INSERT INTO t VALUES (null, null, DEFAULT, 'mm');
1096 ALTER TABLE t ADD COLUMN xs INT GENERATED ALWAYS AS(a+b), ADD COLUMN mm INT
1097 GENERATED ALWAYS AS(a+b) STORED, ALGORITHM = INPLACE;
1098 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
1099 ALTER TABLE t ADD COLUMN x INT GENERATED ALWAYS AS(a+b), ALGORITHM = INPLACE;
1100 ALTER TABLE t DROP COLUMN x, ALGORITHM = INPLACE;
1101 ALTER TABLE t ADD COLUMN x1 INT GENERATED ALWAYS AS(a+b), DROP COLUMN c,
1102 ALGORITHM = INPLACE;
1103 DROP TABLE t;
1104 CREATE TABLE `t` (
1105 `a` INT GENERATED ALWAYS AS (1) VIRTUAL,
1106 `b` INT GENERATED ALWAYS AS (1) VIRTUAL,
1107 `c` INT GENERATED ALWAYS AS (1) VIRTUAL,
1108 `d` INT GENERATED ALWAYS AS (1) VIRTUAL,
1109 `e` POINT GENERATED ALWAYS AS (1) STORED NOT NULL SRID 0
1110 ) ENGINE=INNODB;
1111 ALTER TABLE t ADD SPATIAL INDEX (`e`);
1112 DROP TABLE t;
1113 CREATE TABLE `t` (
1114 `a` INT GENERATED ALWAYS AS (1) VIRTUAL,
1115 `b` INT GENERATED ALWAYS AS (1) VIRTUAL,
1116 `c` INT GENERATED ALWAYS AS (1) VIRTUAL,
1117 `d` INT GENERATED ALWAYS AS (1) VIRTUAL,
1118 `e` POINT GENERATED ALWAYS AS (1) STORED NOT NULL SRID 0,
1119 SPATIAL INDEX (`e`)
1120 ) ENGINE=INNODB;
1121 DROP TABLE t;
1122 CREATE TABLE `t` (
1123 `a` INT GENERATED ALWAYS AS (1) VIRTUAL,
1124 `b` INT GENERATED ALWAYS AS (1) VIRTUAL,
1125 `c` INT GENERATED ALWAYS AS (1) VIRTUAL,
1126 `d` INT GENERATED ALWAYS AS (1) VIRTUAL,
1127 `e2` POINT GENERATED ALWAYS AS (1) STORED NOT NULL,
1128 `e` POINT GENERATED ALWAYS AS (1) STORED NOT NULL SRID 0
1129 ) ENGINE=INNODB;
1130 ALTER TABLE t ADD SPATIAL INDEX (`e`);
1131 DROP TABLE t;
1132 CREATE TABLE `t` (
1133 `a` INT GENERATED ALWAYS AS (1) VIRTUAL,
1134 `b` INT GENERATED ALWAYS AS (1) VIRTUAL,
1135 `c` INT GENERATED ALWAYS AS (1) VIRTUAL,
1136 `d` INT GENERATED ALWAYS AS (1) VIRTUAL,
1137 `e2` POINT GENERATED ALWAYS AS (1) STORED NOT NULL,
1138 `d2` INT GENERATED ALWAYS AS (1) VIRTUAL,
1139 `e` POINT GENERATED ALWAYS AS (1) STORED NOT NULL SRID 0
1140 ) ENGINE=INNODB;
1141 ALTER TABLE t ADD SPATIAL INDEX (`e`);
1142 DROP TABLE t;
1143 CREATE TABLE `t` (
1144 `a` INT GENERATED ALWAYS AS (1) VIRTUAL,
1145 `b` INT GENERATED ALWAYS AS (1) VIRTUAL,
1146 `c` INT GENERATED ALWAYS AS (1) VIRTUAL,
1147 `d` INT GENERATED ALWAYS AS (1) VIRTUAL,
1148 `e2` POINT GENERATED ALWAYS AS (1) STORED NOT NULL,
1149 `d2` INT GENERATED ALWAYS AS (1) VIRTUAL,
1150 `e` int
1151 ) ENGINE=INNODB;
1152 ALTER TABLE t ADD INDEX (`e`);
1153 DROP TABLE t;
1154 CREATE TABLE t (a INT GENERATED ALWAYS AS(1) VIRTUAL,KEY(a)) ENGINE=INNODB;
1155 INSERT INTO t VALUES(default);
1156 SELECT a FROM t FOR UPDATE;
1157 a
1158 1
1159 DROP TABLE t;
1160 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
1161 INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
1162 INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
1163 INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
1164 INSERT INTO t VALUES (null, null, DEFAULT, 'mm');
1165 ALTER TABLE t ADD COLUMN x INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (x);
1166 affected rows: 0
1167 info: Records: 0 Duplicates: 0 Warnings: 0
1168 SELECT x FROM t;
1169 x
1170 14
1171 19
1172 29
1173 NULL
1174 DROP TABLE t;
1175 CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
1176 INSERT INTO t1 VALUES (11, 3, DEFAULT, 'mm');
1177 INSERT INTO t1 VALUES (18, 1, DEFAULT, 'mm');
1178 INSERT INTO t1 VALUES (28, 1, DEFAULT, 'mm');
1179 ALTER TABLE t1 ADD INDEX idx12 (c) , FORCE, LOCK=NONE;
1180 ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c) , FORCE, LOCK=NONE;
1181 Warnings:
1182 Warning 1831 Duplicate index 'idx' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
1183 DROP TABLE t1 ;
1184 CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), d INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
1185 INSERT INTO t1 VALUES (11, 3, DEFAULT, DEFAULT, 'mm');
1186 INSERT INTO t1 VALUES (18, 1, DEFAULT, DEFAULT, 'mm');
1187 INSERT INTO t1 VALUES (28, 1, DEFAULT, DEFAULT, 'mm');
1188 ALTER TABLE t1 CHANGE d d INT GENERATED ALWAYS AS(a+b) FIRST, ALGORITHM = INPLACE;
1189 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY.
1190 ALTER TABLE t1 CHANGE d d VARCHAR(10) GENERATED ALWAYS AS(h), ALGORITHM = INPLACE;
1191 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
1192 ALTER TABLE t1 CHANGE d d INT GENERATED ALWAYS AS(a+b) FIRST;
1193 SHOW CREATE TABLE t1;
1194 Table Create Table
1195 t1 CREATE TABLE `t1` (
1196 `d` int GENERATED ALWAYS AS ((`a` + `b`)) VIRTUAL,
1197 `a` int DEFAULT NULL,
1198 `b` int DEFAULT NULL,
1199 `c` int GENERATED ALWAYS AS ((`a` + `b`)) VIRTUAL,
1200 `h` varchar(10) DEFAULT NULL
1201 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1202 DROP TABLE t1;
1203 CREATE TABLE parent (id INT PRIMARY KEY) ENGINE=INNODB;
1204 CREATE TABLE child (
1205 id INT,
1206 parent_id INT,
1207 x int(11) GENERATED ALWAYS AS (parent_id+1),
1208 INDEX par_ind (parent_id),
1209 FOREIGN KEY (parent_id)
1210 REFERENCES parent(id)
1211 ON DELETE CASCADE
1212 ) ENGINE=INNODB;
1213 Warnings:
1214 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1215 ALTER TABLE child ADD INDEX `i1` (x);
1216 CREATE TABLE child_1 (
1217 id INT,
1218 parent_id INT,
1219 x int(11) GENERATED ALWAYS AS (parent_id+1),
1220 INDEX par_ind (parent_id),
1221 FOREIGN KEY (parent_id)
1222 REFERENCES parent(id)
1223 ) ENGINE=INNODB;
1224 Warnings:
1225 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1226 ALTER TABLE child_1 ADD INDEX `i1` (x);
1227 DROP TABLE child_1;
1228 DROP TABLE child;
1229 CREATE TABLE child (
1230 id INT,
1231 parent_id INT,
1232 x int(11) GENERATED ALWAYS AS (parent_id+1),
1233 INDEX par_ind (parent_id),
1234 INDEX i1 (x),
1235 FOREIGN KEY (parent_id)
1236 REFERENCES parent(id)
1237 ON DELETE CASCADE
1238 ) ENGINE=INNODB;
1239 Warnings:
1240 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1241 DROP TABLE child;
1242 CREATE TABLE child (
1243 id INT,
1244 parent_id INT,
1245 x int(11) GENERATED ALWAYS AS (parent_id+1),
1246 INDEX par_ind (parent_id),
1247 INDEX `i1` (x)
1248 ) ENGINE=INNODB;
1249 Warnings:
1250 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1251 ALTER TABLE child ADD FOREIGN KEY (parent_id)
1252 REFERENCES parent(id)
1253 ON DELETE CASCADE;
1254 SET foreign_key_checks = 0;
1255 ALTER TABLE child ADD FOREIGN KEY (parent_id)
1256 REFERENCES parent(id)
1257 ON DELETE CASCADE;
1258 ALTER TABLE child ADD FOREIGN KEY (parent_id)
1259 REFERENCES parent(id)
1260 ON DELETE SET NULL;
1261 ALTER TABLE child ADD FOREIGN KEY (parent_id)
1262 REFERENCES parent(id)
1263 ON UPDATE CASCADE;
1264 ALTER TABLE child ADD FOREIGN KEY (parent_id)
1265 REFERENCES parent(id);
1266 SET foreign_key_checks = 1;
1267 DROP TABLE child;
1268 DROP TABLE parent;
1269 CREATE TABLE `ibstd_16` (
1270 `a` int(11) DEFAULT NULL,
1271 `d` int(11) DEFAULT NULL,
1272 `b` varchar(198) DEFAULT NULL,
1273 `c` char(179) DEFAULT NULL,
1274 `vadcol` int(11) GENERATED ALWAYS AS (a+length(d)) STORED,
1275 `vbcol` char(2) GENERATED ALWAYS AS (substr(b,2,2)) VIRTUAL,
1276 `vbidxcol` char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL,
1277 UNIQUE KEY `b` (`b`(10),`d`),
1278 KEY `d` (`d`),
1279 KEY `a` (`a`),
1280 KEY `c` (`c`(99),`b`(33)),
1281 KEY `b_2` (`b`(5),`c`(10),`a`),
1282 KEY `vbidxcol` (`vbidxcol`),
1283 KEY `a_2` (`a`,`vbidxcol`),
1284 KEY `vbidxcol_2` (`vbidxcol`,`d`)
1285 ) ENGINE=INNODB;
1286 Warnings:
1287 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1288 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1289 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1290 CREATE TABLE `ibstd_16_fk` (
1291 `a` int(11) DEFAULT NULL,
1292 `d` int(11) DEFAULT NULL,
1293 `b` varchar(198) DEFAULT NULL,
1294 `c` char(179) DEFAULT NULL,
1295 `vadcol` int(11) GENERATED ALWAYS AS (a+length(d)) STORED,
1296 `vbcol` char(2) GENERATED ALWAYS AS (substr(b,2,2)) VIRTUAL,
1297 `vbidxcol` char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL,
1298 UNIQUE KEY `b` (`b`(10),`a`,`d`),
1299 KEY `d` (`d`),
1300 KEY `a` (`a`),
1301 KEY `c` (`c`(99),`b`(33)),
1302 KEY `b_2` (`b`(5),`c`(10),`a`),
1303 KEY `vbidxcol` (`vbidxcol`),
1304 KEY `a_2` (`a`,`vbidxcol`),
1305 KEY `vbidxcol_2` (`vbidxcol`,`d`),
1306 CONSTRAINT `fk_16` FOREIGN KEY (`a`) REFERENCES `ibstd_16` (`a`) ON DELETE SET NULL
1307 ) ENGINE=InnoDB;
1308 ERROR HY000: Cannot add foreign key constraint
1309 CREATE TABLE `ibstd_16_fk` (
1310 `a` int(11) DEFAULT NULL,
1311 `d` int(11) DEFAULT NULL,
1312 `b` varchar(198) DEFAULT NULL,
1313 `c` char(179) DEFAULT NULL,
1314 `vbcol` char(2) GENERATED ALWAYS AS (substr(b,2,2)) VIRTUAL,
1315 `vbidxcol` char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL,
1316 UNIQUE KEY `b` (`b`(10),`a`,`d`),
1317 KEY `d` (`d`),
1318 KEY `a` (`a`),
1319 KEY `c` (`c`(99),`b`(33)),
1320 KEY `b_2` (`b`(5),`c`(10),`a`),
1321 KEY `vbidxcol` (`vbidxcol`),
1322 KEY `vbidxcol_2` (`vbidxcol`,`d`),
1323 CONSTRAINT `fk_16` FOREIGN KEY (`a`) REFERENCES `ibstd_16` (`a`) ON DELETE SET NULL
1324 ) ENGINE=InnoDB;
1325 Warnings:
1326 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1327 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1328 ALTER TABLE ibstd_16_fk ADD INDEX `a_2` (`a`,`vbidxcol`);
1329 DROP TABLE ibstd_16_fk;
1330 CREATE TABLE `ibstd_16_fk` (
1331 `a` int(11) DEFAULT NULL,
1332 `d` int(11) DEFAULT NULL,
1333 `b` varchar(198) DEFAULT NULL,
1334 `c` char(179) DEFAULT NULL,
1335 `vbcol` char(2) GENERATED ALWAYS AS (substr(b,2,2)) VIRTUAL,
1336 `vbidxcol` char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL,
1337 UNIQUE KEY `b` (`b`(10),`a`,`d`),
1338 KEY `d` (`d`),
1339 KEY `a` (`a`),
1340 KEY `c` (`c`(99),`b`(33)),
1341 KEY `b_2` (`b`(5),`c`(10),`a`),
1342 KEY `vbidxcol` (`vbidxcol`),
1343 KEY `a_2` (`a`,`vbidxcol`),
1344 KEY `vbidxcol_2` (`vbidxcol`,`d`)
1345 ) ENGINE=InnoDB;
1346 Warnings:
1347 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1348 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1349 ALTER TABLE `ibstd_16_fk` ADD CONSTRAINT `fk_16` FOREIGN KEY (`a`) REFERENCES `ibstd_16` (`a`) ON DELETE SET NULL;
1350 DROP INDEX a_2 ON ibstd_16_fk;
1351 INSERT INTO ibstd_16 VALUES (1, 2, "aaa", "bbb", default, default, default);
1352 INSERT INTO ibstd_16_fk VALUES(1, 3, "mmm", "SSS", default, default);
1353 DELETE FROM ibstd_16 WHERE a = 1;
1354 DROP TABLE ibstd_16_fk;
1355 DROP TABLE ibstd_16;
1356 create table t(a int) engine=innodb;
1357 insert into t set a=1;
1358 alter table t add column c int generated always as (1) virtual;
1359 insert into t set a=2;
1360 alter table t add unique index(c);
1361 ERROR 23000: Duplicate entry '1' for key 't.c'
1362 insert into t set a=1;
1363 drop table t;
1364 create table t (
1365 x int,
1366 a int generated always as (x) virtual not null,
1367 b int generated always as (1) stored,
1368 c int not null,
1369 unique (b),
1370 unique (a,b)
1371 ) engine=innodb;
1372 insert into t(x, c) values(1, 3);
1373 replace into t(x, c) values(1, 0);
1374 drop table t;
1375 CREATE TABLE t(
1376 c7c CHAR(1)GENERATED ALWAYS AS (c3) VIRTUAL,
1377 c1 int(1),
1378 c2 int(1),
1379 c3 int(1),
1380 c4 int(1),
1381 c5 int(1)GENERATED ALWAYS AS ((c2 - c4)) VIRTUAL,
1382 UNIQUE KEY c5_9(c5)
1383 )ENGINE=InnoDB DEFAULT CHARSET=latin1;
1384 Warnings:
1385 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1386 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1387 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1388 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1389 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1390 ALTER TABLE t CHANGE COLUMN c5 c5 INT(1) GENERATED ALWAYS AS(c2 -
1391 c4)VIRTUAL AFTER c3,ALGORITHM=INPLACE;
1392 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY.
1393 ALTER TABLE t CHANGE COLUMN c7c c7c INT(1) GENERATED ALWAYS AS(c3)
1394 VIRTUAL AFTER c5,ALGORITHM=INPLACE;
1395 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
1396 ALTER TABLE t DROP COLUMN c7c,ADD COLUMN c5c INT GENERATED ALWAYS AS(c4/
1397 c3)VIRTUAL AFTER c3,ALGORITHM=INPLACE;
1398 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY.
1399 DROP TABLE t;
1400 CREATE TABLE `t` (
1401 `col1` int(11) DEFAULT NULL,
1402 `col2` int(11) DEFAULT NULL,
1403 `col4` int(11) DEFAULT NULL,
1404 `col5` int(11) GENERATED ALWAYS AS ((`col2` % `col4`)) VIRTUAL,
1405 `col6` int(11) GENERATED ALWAYS AS ((`col2` - `col2`)) VIRTUAL,
1406 `col5x` int(11) GENERATED ALWAYS AS ((`col1` / `col1`)) VIRTUAL,
1407 `col6x` int(11) GENERATED ALWAYS AS ((`col2` / `col4`)) VIRTUAL,
1408 `col7x` int(11) GENERATED ALWAYS AS ((`col6` % `col6x`)) VIRTUAL,
1409 `col8x` int(11) GENERATED ALWAYS AS ((`col6` / `col6`)) VIRTUAL,
1410 `col9` text,
1411 `col7c` int(11) GENERATED ALWAYS AS ((`col6x` % `col6x`)) VIRTUAL,
1412 `col1b` varchar(20) GENERATED ALWAYS AS (`col1`) VIRTUAL,
1413 `col3` int(11) DEFAULT NULL,
1414 `col7` int(11) DEFAULT NULL,
1415 `col5c` int(11) GENERATED ALWAYS AS ((`col5x` * `col6`)) VIRTUAL,
1416 `col6c` varchar(20) GENERATED ALWAYS AS (`col5x`) VIRTUAL,
1417 `col3b` bigint(20) GENERATED ALWAYS AS ((`col6x` * `col6`)) VIRTUAL,
1418 `col1a` varchar(20) GENERATED ALWAYS AS (`col1`) VIRTUAL,
1419 `col8` int(11) DEFAULT NULL,
1420 UNIQUE KEY `col5` (`col5`),
1421 UNIQUE KEY `col6x` (`col6x`),
1422 UNIQUE KEY `col5_2` (`col5`),
1423 KEY `idx2` (`col9`(10)),
1424 KEY `idx4` (`col2`),
1425 KEY `idx8` (`col9`(10),`col5`),
1426 KEY `idx9` (`col6`),
1427 KEY `idx6` (`col6`)
1428 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1429 Warnings:
1430 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1431 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1432 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1433 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1434 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1435 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1436 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1437 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1438 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1439 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1440 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1441 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1442 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1443 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1444 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1445 Warning 1831 Duplicate index 'col5_2' defined on the table 'test.t'. This is deprecated and will be disallowed in a future release.
1446 Warning 1831 Duplicate index 'idx6' defined on the table 'test.t'. This is deprecated and will be disallowed in a future release.
1447 ALTER TABLE t CHANGE COLUMN col3b col8a BIGINT GENERATED ALWAYS AS
1448 (col6x * col6) VIRTUAL, ADD UNIQUE KEY uidx ( col8a );
1449 DROP TABLE t;
1450 #
1451 # Bug 22141031 - GCOLS: PURGED THREAD DIES: TRIED TO PURGE
1452 # NON-DELETE-MARKED RECORD IN INDEX
1453 #
1454 create table t (
1455 a int,b int,c text,d int,e int,f int,g int,
1456 h text generated always as ('1') virtual,
1457 i int,j int,k int,l int,m int,
1458 primary key (c(1)),unique key (c(1)),
1459 key (i),key (h(1))
1460 ) engine=innodb default charset latin1;
1461 replace into t(c) values ('');
1462 replace into t(c) values ('');
1463 alter table t drop column d ;
1464 drop table t;
1465 #
1466 # Bug 22139917 - ASSERTION: DICT_TABLE_GET_NTH_COL(USER_TABLE, NTH_COL)
1467 # ->LEN < NEW_LEN
1468 #
1469 create table t (
1470 a int generated always as (1) virtual,
1471 b varbinary(1),
1472 c varbinary(1) generated always as (b) virtual
1473 ) engine=innodb;
1474 alter table t change column b b varbinary(2), algorithm=inplace;
1475 alter table t change column c c varbinary(2) generated always as (b) virtual, algorithm=inplace;
1476 drop table t;
1477 SET @@SESSION.sql_mode=0;
1478 CREATE TABLE t(
1479 c1 INT AUTO_INCREMENT,
1480 c2 INT,
1481 c3 INT GENERATED ALWAYS AS(c2 + c2)VIRTUAL,
1482 c3k INT GENERATED ALWAYS AS(c2 + c3)VIRTUAL,
1483 c4 DATE,
1484 c5 DATE GENERATED ALWAYS AS(DATE_ADD(c4,interval 30 day)) VIRTUAL,
1485 c5k DATE GENERATED ALWAYS AS(DATE_ADD(c4,interval 30 day)) VIRTUAL,
1486 c5time_gckey DATE,
1487 c6 TIME,
1488 c5time DATE GENERATED ALWAYS AS(ADDTIME(c5time_gckey,c6)) VIRTUAL,
1489 c7 TIME GENERATED ALWAYS AS(ADDTIME(c5time_gckey,c6)) VIRTUAL,
1490 c5timek DATE GENERATED ALWAYS AS(ADDTIME(c5time_gckey,c7)) VIRTUAL,
1491 c7k TIME GENERATED ALWAYS AS(ADDTIME(c5time,c6)) VIRTUAL,
1492 c8 CHAR(10),
1493 c9 CHAR(20)GENERATED ALWAYS AS (CONCAT(c8,c8)) VIRTUAL,
1494 c9k CHAR(15)GENERATED ALWAYS AS (CONCAT(c8,0)) VIRTUAL,
1495 PRIMARY KEY(c1),
1496 KEY(c3),
1497 KEY(c9(10)),
1498 UNIQUE KEY(c9k),
1499 UNIQUE KEY(c3k,c9k(5),c5k,c7k,c5timek,c3,c9(5),c5,c7,c5time)
1500 )ENGINE=INNODB;
1501 INSERT INTO
1502 t(c2,c4,c6,c5time_gckey,c8)VALUES(1,0,0,0,0),(0,0,0,0,'ityzg'),(0,0,1,0,'tyzgk
1503 t'),(0,1,0,1,'yzgktb'),(0,0,0,0,'zgktb'),(0,0,0,0,'gktbkj'),(0,0,0,0,0),(0,0,1
1504 ,0,1),(0,0,0,0,1),(0,0,0,0,'tbkjrkm'),(0,0,0,0,'bkjr'),(0,0,0,0,0),(0,0,0,0,0)
1505 ,(0,0,0,0,'rk'),(0,0,0,0,'kmqmknbtoe'),(1,0,0,0,'mqmknbt'),(0,1,0,0,'qmknb'),(
1506 0,0,0,0,'mkn'),(0,0,0,0,'knbtoervql'),(0,0,1,0,1),(0,0,0,0,'nbtoerv'),(0,0,0,0
1507 ,'btoerv'),(0,0,1,0,'toer'),(1,0,0,0,0),(0,0,0,0,'ervq'),(0,0,0,0,'rvqlzsvasu'
1508 ),(0,0,0,0,'vqlzs'),(0,0,0,0,0),(0,1,0,0,'lzsvasu'),(0,0,0,0,'zsvasurq');
1509 ERROR 23000: Duplicate entry '00' for key 't.c9k'
1510 SELECT
1511 DISTINCT * FROM t
1512 FORCE KEY(PRIMARY,c3k,c3,c9k,c9)
1513 WHERE
1514 (c9 IS NULL AND (c9=0))
1515 OR(
1516 (c9k NOT IN ('ixfq','xfq','New Mexico','fq')OR c9 IS NULL)
1517 )
1518 OR(c9 BETWEEN 'hwstqua' AND 'wstquadcji' OR (c9k=0))
1519 AND(c3 IS NULL OR c3 IN (0,0,0));
1520 c1 c2 c3 c3k c4 c5 c5k c5time_gckey c6 c5time c7 c5timek c7k c8 c9 c9k
1521 drop table t;
1522 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), d INT
1523 GENERATED ALWAYS AS(a+b+b), e INT GENERATED ALWAYS AS(a), h VARCHAR(10));
1524 INSERT INTO t VALUES (11, 3, DEFAULT, DEFAULT, DEFAULT, 'mm');
1525 INSERT INTO t VALUES (18, 1, DEFAULT, DEFAULT, DEFAULT, 'mm');
1526 INSERT INTO t VALUES (28, 1, DEFAULT, DEFAULT, DEFAULT, 'mm');
1527 INSERT INTO t VALUES (null, null, DEFAULT, DEFAULT, DEFAULT, 'mm');
1528 CREATE INDEX idx ON t(c, d);
1529 CREATE INDEX idx1 ON t(c);
1530 CREATE INDEX idx2 ON t(e, c, d);
1531 ALTER TABLE t DROP COLUMN c, ALGORITHM=INPLACE;
1532 SELECT d FROM t;
1533 d
1534 17
1535 20
1536 30
1537 NULL
1538 SHOW CREATE TABLE t;
1539 Table Create Table
1540 t CREATE TABLE `t` (
1541 `a` int DEFAULT NULL,
1542 `b` int DEFAULT NULL,
1543 `d` int GENERATED ALWAYS AS (((`a` + `b`) + `b`)) VIRTUAL,
1544 `e` int GENERATED ALWAYS AS (`a`) VIRTUAL,
1545 `h` varchar(10) DEFAULT NULL,
1546 KEY `idx` (`d`),
1547 KEY `idx2` (`e`,`d`)
1548 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1549 ALTER TABLE t DROP COLUMN d, ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (c), ALGORITHM=INPLACE;
1550 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY.
1551 ALTER TABLE t DROP COLUMN d, ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (e), ALGORITHM=INPLACE, LOCK=NONE;
1552 Warnings:
1553 Warning 1831 Duplicate index 'idx2' defined on the table 'test.t'. This is deprecated and will be disallowed in a future release.
1554 Warning 1831 Duplicate index 'idx' defined on the table 'test.t'. This is deprecated and will be disallowed in a future release.
1555 SHOW CREATE TABLE t;
1556 Table Create Table
1557 t CREATE TABLE `t` (
1558 `a` int DEFAULT NULL,
1559 `b` int DEFAULT NULL,
1560 `e` int GENERATED ALWAYS AS (`a`) VIRTUAL,
1561 `h` varchar(10) DEFAULT NULL,
1562 `c` int GENERATED ALWAYS AS ((`a` + `b`)) VIRTUAL,
1563 KEY `idx2` (`e`),
1564 KEY `idx` (`e`)
1565 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1566 ALTER TABLE t ADD INDEX idx4(c, e), ADD COLUMN x VARCHAR(10) GENERATED ALWAYS AS(h), DROP INDEX idx, ALGORITHM=INPLACE, LOCK=NONE;
1567 SHOW CREATE TABLE t;
1568 Table Create Table
1569 t CREATE TABLE `t` (
1570 `a` int DEFAULT NULL,
1571 `b` int DEFAULT NULL,
1572 `e` int GENERATED ALWAYS AS (`a`) VIRTUAL,
1573 `h` varchar(10) DEFAULT NULL,
1574 `c` int GENERATED ALWAYS AS ((`a` + `b`)) VIRTUAL,
1575 `x` varchar(10) GENERATED ALWAYS AS (`h`) VIRTUAL,
1576 KEY `idx2` (`e`),
1577 KEY `idx4` (`c`,`e`)
1578 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1579 ALTER TABLE t ADD COLUMN i INT GENERATED ALWAYS AS(a+a+b), ADD COLUMN j INT, ALGORITHM=INPLACE;
1580 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY.
1581 ALTER TABLE t ADD COLUMN i INT GENERATED ALWAYS AS(a+a+b), ADD COLUMN j INT, ALGORITHM=INSTANT;
1582 ALTER TABLE t ADD INDEX (x), ADD COLUMN k INT, ALGORITHM=INPLACE, LOCK=NONE;
1583 SHOW CREATE TABLE t;
1584 Table Create Table
1585 t CREATE TABLE `t` (
1586 `a` int DEFAULT NULL,
1587 `b` int DEFAULT NULL,
1588 `e` int GENERATED ALWAYS AS (`a`) VIRTUAL,
1589 `h` varchar(10) DEFAULT NULL,
1590 `c` int GENERATED ALWAYS AS ((`a` + `b`)) VIRTUAL,
1591 `x` varchar(10) GENERATED ALWAYS AS (`h`) VIRTUAL,
1592 `i` int GENERATED ALWAYS AS (((`a` + `a`) + `b`)) VIRTUAL,
1593 `j` int DEFAULT NULL,
1594 `k` int DEFAULT NULL,
1595 KEY `idx2` (`e`),
1596 KEY `idx4` (`c`,`e`),
1597 KEY `x` (`x`)
1598 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1599 ALTER TABLE t ADD COLUMN l INT GENERATED ALWAYS AS(a+a+b), ADD INDEX (l), ALGORITHM=INPLACE, LOCK=NONE;
1600 ERROR 0A000: LOCK=NONE is not supported. Reason: ADD COLUMN col...VIRTUAL, ADD INDEX(col). Try LOCK=SHARED.
1601 ALTER TABLE t ADD COLUMN l INT GENERATED ALWAYS AS(a+a+b), ADD INDEX (l), ALGORITHM=INPLACE, LOCK=SHARED;
1602 SHOW CREATE TABLE t;
1603 Table Create Table
1604 t CREATE TABLE `t` (
1605 `a` int DEFAULT NULL,
1606 `b` int DEFAULT NULL,
1607 `e` int GENERATED ALWAYS AS (`a`) VIRTUAL,
1608 `h` varchar(10) DEFAULT NULL,
1609 `c` int GENERATED ALWAYS AS ((`a` + `b`)) VIRTUAL,
1610 `x` varchar(10) GENERATED ALWAYS AS (`h`) VIRTUAL,
1611 `i` int GENERATED ALWAYS AS (((`a` + `a`) + `b`)) VIRTUAL,
1612 `j` int DEFAULT NULL,
1613 `k` int DEFAULT NULL,
1614 `l` int GENERATED ALWAYS AS (((`a` + `a`) + `b`)) VIRTUAL,
1615 KEY `idx2` (`e`),
1616 KEY `idx4` (`c`,`e`),
1617 KEY `x` (`x`),
1618 KEY `l` (`l`)
1619 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1620 SELECT l FROM t;
1621 l
1622 25
1623 37
1624 57
1625 NULL
1626 SELECT * FROM t;
1627 a b e h c x i j k l
1628 11 3 11 mm 14 mm 25 NULL NULL 25
1629 18 1 18 mm 19 mm 37 NULL NULL 37
1630 28 1 28 mm 29 mm 57 NULL NULL 57
1631 NULL NULL NULL mm NULL mm NULL NULL NULL NULL
1632 DROP TABLE t;
1633 CREATE TABLE t (
1634 a INT,
1635 b INT,
1636 c INT GENERATED ALWAYS AS(a+b),
1637 d INT GENERATED ALWAYS AS(a+b+b),
1638 KEY vidx (c, d)
1639 )ENGINE=INNODB;
1640 INSERT INTO t (a,b) VALUES (0, 0), (1, NULL), (NULL, 2), (NULL, NULL);
1641 SELECT c, d FROM t;
1642 c d
1643 0 0
1644 NULL NULL
1645 NULL NULL
1646 NULL NULL
1647 SELECT * FROM t;
1648 a b c d
1649 0 0 0 0
1650 1 NULL NULL NULL
1651 NULL 2 NULL NULL
1652 NULL NULL NULL NULL
1653 ALTER TABLE t DROP COLUMN c, ALGORITHM=INPLACE;
1654 SELECT d FROM t;
1655 d
1656 0
1657 NULL
1658 NULL
1659 NULL
1660 SELECT * FROM t;
1661 a b d
1662 0 0 0
1663 1 NULL NULL
1664 NULL 2 NULL
1665 NULL NULL NULL
1666 DROP TABLE t;
1667 CREATE TABLE t (
1668 a INT,
1669 b INT,
1670 c INT GENERATED ALWAYS AS(a+b),
1671 d INT GENERATED ALWAYS AS(a+b+b)
1672 )ENGINE=INNODB;
1673 INSERT INTO t (a,b) VALUES (0, 0), (1, NULL), (NULL, 2), (NULL, NULL);
1674 SELECT * FROM t;
1675 a b c d
1676 0 0 0 0
1677 1 NULL NULL NULL
1678 NULL 2 NULL NULL
1679 NULL NULL NULL NULL
1680 ALTER TABLE t DROP COLUMN c, ADD INDEX vidx(d), ALGORITHM=INPLACE;
1681 SELECT d FROM t;
1682 d
1683 0
1684 NULL
1685 NULL
1686 NULL
1687 SELECT * FROM t;
1688 a b d
1689 0 0 0
1690 1 NULL NULL
1691 NULL 2 NULL
1692 NULL NULL NULL
1693 DROP TABLE t;
1694 #
1695 # Bug #22162200 MEMORY LEAK IN HA_INNOPART_SHARE
1696 # ::SET_V_TEMPL PARTITIONED ON VIRTUAL COLUMN
1697 #
1698 create table t (
1699 c tinyint,
1700 d longblob generated always as (c) virtual
1701 ) engine=innodb partition by key (c) partitions 2;
1702 select d in(select d from t)from t group by d;
1703 d in(select d from t)
1704 drop table t;
1705 #
1706 # BUG#23052231 - ASSERTION FAILURE: ROW0MERGE.CC:2100:ADD_AUTOINC
1707 # < DICT_TABLE_GET_N_USER_COLS
1708 #
1709 CREATE TABLE `t` (
1710 `a` int(11) NOT NULL,
1711 `d` int(11) NOT NULL,
1712 `b` varchar(198) NOT NULL,
1713 `c` char(177) DEFAULT NULL,
1714 `vadcol` int(11) GENERATED ALWAYS AS ((`a` + length(`d`))) STORED,
1715 `vbcol` char(2) GENERATED ALWAYS AS (substr(`b`,2,2)) VIRTUAL,
1716 `vbidxcol` char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL,
1717 PRIMARY KEY (`b`(10),`a`,`d`),
1718 KEY `d` (`d`),
1719 KEY `a` (`a`),
1720 KEY `c_renamed` (`c`(99),`b`(35)),
1721 KEY `b` (`b`(5),`c`(10),`a`),
1722 KEY `vbidxcol` (`vbidxcol`),
1723 KEY `a_2` (`a`,`vbidxcol`),
1724 KEY `vbidxcol_2` (`vbidxcol`,`d`)
1725 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1726 Warnings:
1727 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1728 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1729 Warning 1681 Integer display width is deprecated and will be removed in a future release.
1730 INSERT INTO t values (11, 1, "11", "aa", default, default, default);
1731 ALTER TABLE t ADD COLUMN nc01128 BIGINT AUTO_INCREMENT NOT NULL, ADD KEY auto_nc01128(nc01128);
1732 DROP TABLE t;
1733 #
1734 #Bug #22965271 NEEDS REBUILD FOR COLUMN LENGTH CHANGE THAT IS
1735 #PART OF VIRTUAL INDEX.
1736 #
1737 CREATE TABLE t1(
1738 a VARCHAR(45) CHARACTER SET LATIN1,
1739 b VARCHAR(115) CHARACTER SET UTF8 GENERATED ALWAYS AS ('f1') VIRTUAL,
1740 UNIQUE KEY (b,a))ENGINE=INNODB;
1741 Warnings:
1742 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
1743 INSERT INTO t1(a) VALUES ('');
1744 ALTER TABLE t1 CHANGE COLUMN a a VARCHAR(85);
1745 SELECT * FROM t1;
1746 a b
1747 f1
1748 DROP TABLE t1;
1749 #
1750 # BUG#27712812 - ALTER TABLE TO ADD AND(OR) DROP VIRTUAL COLUMNS SHOULD BE INSTANT
1751 #
1752 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
1753 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1754 ALTER TABLE t1 ADD COLUMN c INT NOT NULL, ADD COLUMN d INT GENERATED ALWAYS AS ((b * 2)) VIRTUAL, ALGORITHM = INSTANT;
1755 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%';
1756 instant_cols
1757 0
1758 ALTER TABLE t1 ADD COLUMN e INT GENERATED ALWAYS AS ((b * 2)) VIRTUAL, ALGORITHM = INSTANT;
1759 DROP TABLE t1;
1760 #
1761 # BUG#27755892 - CRASH IN ROW_SEL_FIELD_STORE_IN_MYSQL_FORMAT_FUNC
1762 #
1763 CREATE TABLE t1 (b DATE, c CHAR(100) CHARACTER SET utf32 GENERATED ALWAYS AS (concat(1, b, 1)) VIRTUAL, UNIQUE KEY(c(16)));
1764 INSERT INTO t1(b) VALUES('2018-04-01');
1765 SELECT c LIKE 1 FROM t1;
1766 c LIKE 1
1767 0
1768 DROP TABLE t1;