Debian Codegraph @

mysql-test/suite/innodb/t/virtual_basic.test

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