Debian Codegraph @

mysql-test/suite/gcol/r/innodb_virtual_basic.result

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