Debian Codegraph @

mysql-test/suite/gcol/t/innodb_virtual_basic.test

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