Debian Codegraph @

mysql-test/suite/innodb/include/instant_add_column_basic.inc

raw
1 --echo #
2 --echo # Scenario 1:
3 --echo # Create a small table, and add all kinds of new columns and verify
4 --echo #
5
6 SET @saved_innodb_strict_mode=@@SESSION.innodb_strict_mode;
7
8 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
9
10 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
11
12
13 # [1] ADD COLUMN INT
14 let $new_cols = 1;
15 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 INT, ALGORITHM=INSTANT;
16
17 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
18
19 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
20
21 INSERT INTO t1(a, c1) VALUES(0, 1);
22 SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
23 SELECT c1 FROM t1 WHERE c1 = 1;
24
25 # [2] ADD COLUMN INT DEFAULT
26 let $new_cols = 2;
27 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c2 INT NOT NULL DEFAULT 10;
28
29 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
30
31 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c2' AND has_default = 1;
32
33 INSERT INTO t1(a, c2) VALUES(0, 11);
34 SELECT count(*) = max(a) FROM t1 WHERE c2 = 10;
35 SELECT c2 FROM t1 WHERE c2 = 11;
36
37
38 # [3] ADD COLUMN BIGINT
39 let $new_cols = 3;
40 let $instant_add_column = ALTER TABLE t1 ADD COLUMN d1 BIGINT, ALGORITHM=INSTANT;
41
42 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
43
44 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd1' AND has_default = 1;
45
46 INSERT INTO t1(a, d1) VALUES(0, 1);
47 SELECT count(*) = max(a) FROM t1 WHERE d1 IS NULL;
48 SELECT d1 FROM t1 WHERE d1 = 1;
49
50 # [4] ADD COLUMN BIGINT DEFAULT
51 let $new_cols = 4;
52 let $instant_add_column = ALTER TABLE t1 ADD COLUMN d2 BIGINT NOT NULL DEFAULT 1234567890;
53
54 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
55
56 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd2' AND has_default = 1;
57
58 INSERT INTO t1(a, d2) VALUES(0, 1234);
59 SELECT count(*) = max(a) FROM t1 WHERE d2 = 1234567890;
60 SELECT d2 FROM t1 WHERE d2 = 1234;
61
62
63 # [5] ADD COLUMN SMALLINT
64 let $new_cols = 5;
65 let $instant_add_column = ALTER TABLE t1 ADD COLUMN e1 SMALLINT, ALGORITHM=INSTANT;
66
67 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
68
69 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e1' AND has_default = 1;
70
71 INSERT INTO t1(a, e1) VALUES(0, 1);
72 SELECT count(*) = max(a) FROM t1 WHERE e1 IS NULL;
73 SELECT e1 FROM t1 WHERE e1 = 1;
74
75 # [6] ADD COLUMN SMALLINT DEFAULT
76 let $new_cols = 6;
77 let $instant_add_column = ALTER TABLE t1 ADD COLUMN e2 SMALLINT NOT NULL DEFAULT 10;
78
79 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
80
81 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e2' AND has_default = 1;
82
83 INSERT INTO t1(a, e2) VALUES(0, 11);
84 SELECT count(*) = max(a) FROM t1 WHERE e2 = 10;
85 SELECT e2 FROM t1 WHERE e2 = 11;
86
87
88 # [7] ADD COLUMN TINYINT
89 let $new_cols = 7;
90 let $instant_add_column = ALTER TABLE t1 ADD COLUMN f1 TINYINT, ALGORITHM=INSTANT;
91
92 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
93
94 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f1' AND has_default = 1;
95
96 INSERT INTO t1(a, f1) VALUES(0, 1);
97 SELECT count(*) = max(a) FROM t1 WHERE f1 IS NULL;
98 SELECT f1 FROM t1 WHERE f1 = 1;
99
100 # [8] ADD COLUMN TINYINT DEFAULT
101 let $new_cols = 8;
102 let $instant_add_column = ALTER TABLE t1 ADD COLUMN f2 TINYINT NOT NULL DEFAULT 123;
103
104 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
105
106 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f2' AND has_default = 1;
107
108 INSERT INTO t1(a, f2) VALUES(0, 12);
109 SELECT count(*) = max(a) FROM t1 WHERE f2 = 123;
110 SELECT f2 FROM t1 WHERE f2 = 12;
111
112
113 # [9] ADD COLUMN MEDIUMINT
114 let $new_cols = 9;
115 let $instant_add_column = ALTER TABLE t1 ADD COLUMN g1 MEDIUMINT, ALGORITHM=INSTANT;
116
117 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
118
119 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g1' AND has_default = 1;
120
121 INSERT INTO t1(a, g1) VALUES(0, 1);
122 SELECT count(*) = max(a) FROM t1 WHERE g1 IS NULL;
123 SELECT g1 FROM t1 WHERE g1 = 1;
124
125 # ADD COLUMN MEDIUMINT DEFAULT
126 let $new_cols = 10;
127 let $instant_add_column = ALTER TABLE t1 ADD COLUMN g2 MEDIUMINT NOT NULL DEFAULT 12345;
128
129 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
130
131 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g2' AND has_default = 1;
132
133 INSERT INTO t1(a, g2) VALUES(0, 1234);
134 SELECT count(*) = max(a) FROM t1 WHERE g2 = 12345;
135 SELECT g2 FROM t1 WHERE g2 = 1234;
136
137
138 # ADD COLUMN FLOAT
139 let $new_cols = 11;
140 let $instant_add_column = ALTER TABLE t1 ADD COLUMN h1 FLOAT, ALGORITHM=INSTANT;
141
142 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
143
144 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h1' AND has_default = 1;
145
146 INSERT INTO t1(a, h1) VALUES(0, 1.0);
147 SELECT count(*) = max(a) FROM t1 WHERE h1 IS NULL;
148 SELECT h1 FROM t1 WHERE h1 = 1;
149
150 # ADD COLUMN FLOAT DEFAULT
151 let $new_cols = 12;
152 let $instant_add_column = ALTER TABLE t1 ADD COLUMN h2 FLOAT NOT NULL DEFAULT 12.34;
153
154 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
155
156 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h2' AND has_default = 1;
157
158 INSERT INTO t1(a, h2) VALUES(0, 1.234);
159 SELECT count(*) = max(a) FROM t1 WHERE h2 = 12.34;
160 SELECT h2 FROM t1 WHERE h2 = 1.234;
161
162
163 # ADD COLUMN DECIMAL & DOUBLE
164 let $new_cols = 14;
165 let $instant_add_column = ALTER TABLE t1 ADD COLUMN i1 DECIMAL(5, 2), ADD COLUMN i2 double, ALGORITHM=INSTANT;
166
167 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
168
169 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'i1' OR name = 'i2') AND has_default = 1;
170
171 INSERT INTO t1(a, i1, i2) VALUES(0, 10.10, 20.20);
172 SELECT count(*) = max(a) FROM t1 WHERE i1 IS NULL;
173 SELECT count(*) = max(a) FROM t1 WHERE i2 IS NULL;
174 SELECT i1 FROM t1 WHERE i1 = 10.10;
175 SELECT i2 FROM t1 WHERE i2 = 20.20;
176
177 # ADD COLUMN DECIMAL & DOUBLE DEFAULT
178 let $new_cols = 16;
179 let $instant_add_column = ALTER TABLE t1 ADD COLUMN j1 DECIMAL(5, 2) NOT NULL DEFAULT 100.00, ADD COLUMN j2 double NOT NULL DEFAULT 1000.5678;
180
181 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
182
183 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'j1' OR name = 'j2') AND has_default = 1;
184
185 INSERT INTO t1(a, j1, j2) VALUES(0, 90.90, 1000.1234);
186 SELECT count(*) = max(a) FROM t1 WHERE j1 = 100.00;
187 SELECT count(*) = max(a) FROM t1 WHERE j2 = 1000.5678;
188 SELECT j1 FROM t1 WHERE j1 = 90.90;
189 SELECT j2 FROM t1 WHERE j2 = 1000.1234;
190
191
192
193 # ADD COLUMN BIT
194 let $new_cols = 17;
195 let $instant_add_column = ALTER TABLE t1 ADD COLUMN k1 BIT(8), ALGORITHM=INSTANT;
196
197 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
198
199 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'k1' AND has_default = 1;
200
201 INSERT INTO t1(a, k1) VALUES(0, b'010101');
202 SELECT count(*) = max(a) FROM t1 WHERE k1 IS NULL;
203 SELECT hex(k1) FROM t1 WHERE k1 = b'010101';
204
205 # ADD COLUMN BIT DEFAULT
206 let $new_cols = 18;
207 let $instant_add_column = ALTER TABLE t1 ADD COLUMN k2 BIT(8) NOT NULL DEFAULT b'101010';
208
209 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
210
211 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'k2' AND has_default = 1;
212
213 INSERT INTO t1(a, k2) VALUES(0, b'110011');
214 SELECT count(*) = max(a) FROM t1 WHERE k2 = b'101010';
215 SELECT hex(k2) FROM t1 WHERE k2 = b'110011';
216
217
218
219 # ADD COLUMN CHAR & VARCHAR
220 let $new_cols = 20;
221 let $instant_add_column = ALTER TABLE t1 ADD COLUMN l1 CHAR(50), ADD COLUMN l2 VARCHAR(100), ALGORITHM=DEFAULT;
222
223 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
224
225 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'l1' OR name = 'l2') AND has_default = 1;
226
227 INSERT INTO t1(a, l1, l2) VALUES(0, 'ABCD EFGH', 'abcdefg hijklmn ');
228 SELECT count(*) = max(a) FROM t1 WHERE l2 IS NULL;
229 SELECT count(*) = max(a) FROM t1 WHERE l1 IS NULL;
230 SELECT l1 FROM t1 WHERE l1 = 'ABCD EFGH';
231 SELECT l2 FROM t1 WHERE l2 = 'abcdefg hijklmn ';
232
233
234 # ADD COLUMN CHAR & VARCHAR DEFAULT
235 let $new_cols = 22;
236 let $instant_add_column = ALTER TABLE t1 ADD COLUMN m1 CHAR(50) default 'The fox jumps over', ADD COLUMN m2 VARCHAR(50) DEFAULT 'The fox jumps over the lazy dog.';
237
238 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
239
240 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'm1' OR name = 'm2') AND has_default = 1;
241
242 INSERT INTO t1(a, m1, m2) VALUES(0, 'over the lazy dog', 'The lazy dog jumps over the fox.');
243 SELECT count(*) = max(a) FROM t1 WHERE m1 = 'The fox jumps over';
244 SELECT count(*) = max(a) FROM t1 WHERE m2 like 'The fox jumps%';
245 SELECT m1 FROM t1 WHERE m1 = 'over the lazy dog';
246 SELECT m2 FROM t1 WHERE m2 like '%the fox.';
247
248
249
250 # ADD COLUMN BINARY & VARBINARY
251 let $new_cols = 24;
252 let $instant_add_column = ALTER TABLE t1 ADD COLUMN n1 BINARY(10), ADD COLUMN n2 VARBINARY(10), ALGORITHM=DEFAULT;
253
254 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
255
256 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'n1' OR name = 'n2') AND has_default = 1;
257
258 INSERT INTO t1(a, n1, n2) VALUES(0, 0x010203040506070809, 0x102030405060708090);
259 SELECT count(*) = max(a) FROM t1 WHERE n1 IS NULL;
260 SELECT count(*) = max(a) FROM t1 WHERE n2 IS NULL;
261 SELECT hex(n1) FROM t1 WHERE n1 = 0x01020304050607080900;
262 SELECT hex(n2) FROM t1 WHERE n2 = 0x102030405060708090;
263
264
265
266 # ADD COLUMN BINARY & VARBINARY DEFAULT
267 let $new_cols = 26;
268 let $instant_add_column = ALTER TABLE t1 ADD COLUMN o1 BINARY(10) DEFAULT 0x11223344, ADD COLUMN o2 VARBINARY(10) DEFAULT 0x55667788;
269
270 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
271
272 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'o1' OR name = 'o2') AND has_default = 1;
273
274 INSERT INTO t1(a, o1, o2) VALUES(0, 0x44332211, 0x88776655);
275 SELECT count(*) = max(a) FROM t1 WHERE o1 = 0x11223344000000000000;
276 SELECT count(*) = max(a) FROM t1 WHERE o2 = 0x55667788;
277 SELECT hex(o1) FROM t1 WHERE o1 = 0x44332211000000000000;
278 SELECT hex(o2) FROM t1 WHERE o2 = 0x88776655;
279
280
281
282 # ADD COLUMN DATETIME
283 let $new_cols = 27;
284 let $instant_add_column = ALTER TABLE t1 ADD COLUMN p1 DATETIME, ALGORITHM=DEFAULT;
285
286 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
287
288 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'p1' AND has_default = 1;
289
290 INSERT INTO t1(a, p1) VALUES(0, '2017-12-31 00:00:00');
291 SELECT count(*) = max(a) FROM t1 WHERE p1 IS NULL;
292 SELECT p1 FROM t1 WHERE p1 = '2017-12-31 00:00:00';
293
294 # ADD COLUMN DATETIME DEFAULT
295 let $new_cols = 28;
296 let $instant_add_column = ALTER TABLE t1 ADD COLUMN p2 DATETIME NOT NULL DEFAULT '2017-12-31 01:02:03';
297
298 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
299
300 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'p2' AND has_default = 1;
301
302 sleep 1;
303 SELECT count(*) = max(a) FROM t1 GROUP BY p2;
304 INSERT INTO t1(a, p2) VALUES(0, now());
305 SELECT count(*) FROM t1 GROUP BY p2;
306
307
308
309 # ADD COLUMN ENUM
310 let $new_cols = 29;
311 let $instant_add_column = ALTER TABLE t1 ADD COLUMN q1 ENUM ('value1','value2','value3'), ALGORITHM=INSTANT;
312
313 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
314
315 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'q1' AND has_default = 1;
316
317 INSERT INTO t1(a, q1) VALUES(0, 1);
318 SELECT count(*) = max(a) FROM t1 WHERE q1 IS NULL;
319 SELECT q1 FROM t1 WHERE q1 = 1;
320
321
322 # ADD COLUMN SET
323 let $new_cols = 30;
324 let $instant_add_column = ALTER TABLE t1 ADD COLUMN r1 SET ('a','b','c'), ALGORITHM=INSTANT;
325
326 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
327
328 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'r1' AND has_default = 1;
329
330 INSERT INTO t1(a, r1) VALUES(0, 'a');
331 SELECT count(*) = max(a) FROM t1 WHERE r1 IS NULL;
332 SELECT r1 FROM t1 WHERE r1 = 'a';
333
334
335 # ADD COLUMN BLOB & TEXT
336 let $new_cols = 32;
337 let $instant_add_column = ALTER TABLE t1 ADD COLUMN s1 BLOB, ADD COLUMN s2 TEXT, ALGORITHM=INSTANT;
338
339 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
340
341 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 's1' OR name = 's2') AND has_default = 1;
342
343 INSERT INTO t1(a, s1, s2) VALUES(0, 0x0102030405, 'abcd qwerty');
344 SELECT count(*) = max(a) FROM t1 WHERE s1 IS NULL;
345 SELECT count(*) = max(a) FROM t1 WHERE s2 IS NULL;
346 SELECT hex(s1) FROM t1 WHERE s1 = 0x0102030405;
347 SELECT s2 FROM t1 WHERE s2 = 'abcd qwerty';
348
349 # ADD COLUMN BLOB & TEXT NOT NULL
350 let $new_cols = 34;
351 let $instant_add_column = ALTER TABLE t1 ADD COLUMN u1 BLOB NOT NULL, ADD COLUMN u2 TEXT NOT NULL;
352
353 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
354
355 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'u1' OR name = 'u2') AND has_default = 1;
356
357 INSERT INTO t1(a, u1, u2) VALUES(0, 0x0102030405, 'abcd qwerty');
358 SELECT count(*) = max(a) FROM t1 WHERE u1 = '';
359 SELECT count(*) = max(a) FROM t1 WHERE u2 = '';
360 SELECT hex(u1) FROM t1 WHERE u1 = 0x0102030405;
361 SELECT u2 FROM t1 WHERE u2 = 'abcd qwerty';
362
363
364 CHECK TABLE t1;
365
366 SHOW CREATE TABLE t1;
367
368 DROP TABLE t1;
369
370
371
372 --echo #
373 --echo # Scenario 2:
374 --echo # Create a small table, add some columns instantly, along with
375 --echo # virtual columns
376 --echo #
377
378 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
379
380 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
381
382
383 let $new_cols = 1;
384 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL, ADD COLUMN d INT GENERATED ALWAYS AS ((b * 2)) VIRTUAL;
385
386 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
387
388 INSERT INTO t1(a, b, c) VALUES(0, 6, 20);
389 --sorted_result
390 SELECT * FROM t1;
391
392
393 let $new_cols = 2;
394 let $instant_add_column = ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'ABCD EFGH', ADD COLUMN f INT GENERATED ALWAYS AS (LENGTH(e));
395
396 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
397
398 INSERT INTO t1(a, c, e) VALUES(0, 20, 'Hello'), (0, 20, 'World'), (0, 20, 'Hello World');
399 --sorted_result
400 SELECT * FROM t1;
401
402
403 let $new_cols = 3;
404 let $instant_add_column = ALTER TABLE t1 ADD COLUMN g VARCHAR(100) GENERATED ALWAYS AS (e), ADD COLUMN h BIGINT DEFAULT 10000, ADD COLUMN i BIGINT GENERATED ALWAYS AS (h * 2 + b);
405
406 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
407
408 --sorted_result
409 SELECT * FROM t1;
410 INSERT INTO t1(a, b, c, h) VALUES(0, 7, 40, 2000), (0, 7, 40, 20000);
411 --sorted_result
412 SELECT * FROM t1;
413
414
415 CHECK TABLE t1;
416
417 SHOW CREATE TABLE t1;
418
419 DROP TABLE t1;
420
421
422
423 --echo #
424 --echo # Scenario 3:
425 --echo # Create a small table, add some columns instantly, then change
426 --echo # their default values, check original default values are correct
427 --echo #
428
429 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
430
431 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
432
433
434 let $new_cols = 2;
435 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
436
437 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
438
439 --sorted_result
440 SELECT * FROM t1;
441 INSERT INTO t1(a, b, c, e) VALUES(0, 6, 200, 'Good day'), (0, 7, 300, 'Good DAY');
442 --sorted_result
443 SELECT * FROM t1;
444
445 SELECT default_value FROM information_schema.innodb_columns WHERE name = 'c' AND has_default = 1;
446 ALTER TABLE t1 ALTER COLUMN c SET DEFAULT 500;
447 # This should not change
448 SELECT default_value FROM information_schema.innodb_columns WHERE name = 'c' AND
449 has_default = 1;
450
451 --sorted_result
452 SELECT * FROM t1;
453 INSERT INTO t1(a, b) VALUES(0, 8), (0, 9);
454 --sorted_result
455 SELECT * FROM t1;
456
457 SELECT default_value FROM information_schema.innodb_columns WHERE name = 'e' AND has_default = 1;
458 ALTER TABLE t1 ALTER COLUMN e SET DEFAULT 'HELLO MySQL!';
459 # This should not change
460 SELECT default_value FROM information_schema.innodb_columns WHERE name = 'e' AND has_default = 1;
461
462 --sorted_result
463 SELECT * FROM t1;
464 INSERT INTO t1(a, b) VALUES(0, 10), (0, 20);
465 --sorted_result
466 SELECT * FROM t1;
467
468
469 CHECK TABLE t1;
470
471 SHOW CREATE TABLE t1;
472
473 DROP TABLE t1;
474
475
476
477 --echo #
478 --echo # Scenario 4:
479 --echo # Create a small table, add some columns instantly, then do DML
480 --echo # on the table
481 --echo #
482
483 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
484
485 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
486
487 let $new_cols = 2;
488 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
489
490 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
491
492 UPDATE t1 SET c = 200 WHERE a > 3;
493 --sorted_result
494 SELECT distinct(c) FROM t1;
495
496 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
497 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
498 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
499
500 DELETE FROM t1 WHERE c = 100;
501
502 UPDATE t1 SET c = 300;
503 --sorted_result
504 SELECT distinct(c) FROM t1;
505
506 SELECT count(*) FROM t1;
507
508
509 let $new_cols = 3;
510 let $instant_add_column = ALTER TABLE t1 ADD COLUMN t DATETIME DEFAULT CURRENT_TIMESTAMP;
511
512 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
513
514 UPDATE t1 SET e = 'Hello MySQL' WHERE a > 10;
515 UPDATE t1 SET e = 'Hello MySQL!!' WHERE a > 20;
516 --sorted_result
517 SELECT distinct(e) FROM t1;
518
519 UPDATE t1 SET c = 500 WHERE e LIKE '%world%';
520 --sorted_result
521 SELECT c, e FROM t1 GROUP BY c, e;
522
523 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
524 sleep 1;
525 UPDATE t1 SET t = CURRENT_TIMESTAMP WHERE a < 50;
526 SELECT count(t) FROM t1 GROUP BY t;
527 DELETE FROM t1 WHERE a < 50;
528 SELECT count(t) FROM t1 GROUP BY t;
529
530 CHECK TABLE t1;
531
532 SHOW CREATE TABLE t1;
533
534 DROP TABLE t1;
535
536
537
538 --echo #
539 --echo # Scenario 5:
540 --echo # Create a small table, add some columns instantly, then do DDL
541 --echo # to build indexes
542 --echo #
543
544 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
545
546 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
547
548 let $new_cols = 2;
549 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
550
551 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
552
553 ALTER TABLE t1 ADD KEY(c);
554
555 # Replace the numbers in the output with '#' to stablize the result, after all we only care about the index picked.
556 --replace_column 8 # 10 #
557 EXPLAIN SELECT c FROM t1;
558
559 SELECT c FROM t1 WHERE c != 100;
560
561 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
562 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
563 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
564
565 UPDATE t1 SET e = 'Hello MySQL' WHERE a > 30;
566
567 ALTER TABLE t1 ADD KEY(e);
568
569 # Replace the numbers in the output with '#' to stablize the result, after all we only care about the index picked.
570 --replace_column 8 # 10 #
571 EXPLAIN SELECT e FROM t1;
572 SELECT count(e) FROM t1 WHERE e LIKE '%MySQL%';
573 SELECT count(e) FROM t1 WHERE e LIKE '%world%';
574
575 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a, c);
576
577 SELECT a, c, e FROM t1 WHERE a > 25 AND a < 40;
578
579 CHECK TABLE t1;
580
581 SHOW CREATE TABLE t1;
582
583 DROP TABLE t1;
584
585
586
587 --echo #
588 --echo # Scenario 6:
589 --echo # Create a small table, add some columns instantly, then do DML
590 --echo # on the table, and some simple rollback
591 --echo #
592
593 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
594
595 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
596
597 let $new_cols = 2;
598 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
599
600 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
601
602 START TRANSACTION;
603
604 INSERT INTO t1(a, b) VALUES(0, 6);
605
606 --sorted_result
607 SELECT * FROM t1;
608
609 ROLLBACK;
610
611 --sorted_result
612 SELECT * FROM t1;
613
614
615 START TRANSACTION;
616
617 UPDATE t1 SET c = 500 WHERE a = 1;
618
619 UPDATE t1 SET b = 1000 WHERE a = 2;
620
621 SELECT a, b, c FROM t1 WHERE a = 1 OR a = 2;
622
623 ROLLBACK;
624
625 --sorted_result
626 SELECT a, b, c FROM t1;
627
628
629 START TRANSACTION;
630
631 DELETE FROM t1 WHERE a < 5;
632
633 INSERT INTO t1(a, b) VALUES(0, 6);
634
635 --sorted_result
636 SELECT * FROM t1;
637
638 ROLLBACK;
639
640 --sorted_result
641 SELECT * FROM t1;
642
643
644 CHECK TABLE t1;
645
646 SHOW CREATE TABLE t1;
647
648 DROP TABLE t1;
649
650
651 # One interesting pessimistic update rollback
652
653 SET SESSION innodb_strict_mode=OFF;
654 --disable_warnings
655 --eval CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=$row_format
656 --enable_warnings
657 SET SESSION innodb_strict_mode=@saved_innodb_strict_mode;
658
659 INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1));
660
661 SELECT id, length(c1), length(c2), length(c3) FROM t1;
662
663 let $new_cols = 1;
664 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd';
665
666 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
667
668 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
669
670 START TRANSACTION;
671
672 UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
673
674 ROLLBACK;
675
676 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
677
678 START TRANSACTION;
679
680 UPDATE t1 SET c4 = 'x' WHERE id = 1;
681
682 ROLLBACK;
683
684 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
685
686 DROP TABLE t1;
687
688
689
690 --echo #
691 --echo # Scenario 7:
692 --echo # Confirm some ADD COLUMN are instant, some are not
693 --echo #
694
695 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
696
697 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
698
699 # ADD COLUMN after LAST should be INSTANT
700 let $new_cols = 1;
701 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100 AFTER b;
702
703 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
704
705 # ADD COLUMN in the middle should be instant
706 let $new_cols = 2;
707 let $instant_add_column = ALTER TABLE t1 ADD COLUMN d INT NOT NULL DEFAULT 100 AFTER b;
708
709 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
710
711 SELECT count(*) AS `Expect 2` FROM information_schema.innodb_columns WHERE has_default = 1;
712
713 # ADD COLUMN with ADD KEY should not be instant
714 let $new_cols = 0;
715 let $instant_add_column = ALTER TABLE t1 ADD COLUMN e INT NOT NULL DEFAULT 100, ADD KEY(e);
716
717 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
718
719 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
720
721 # FORCE key word should not be instant
722 let $new_cols = 0;
723 let $instant_add_column = ALTER TABLE t1 ADD COLUMN f INT NOT NULL DEFAULT 100, FORCE;
724
725 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
726
727 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
728
729 # ALGORITHM=INPLACE should not be instant, verify again here
730 let $new_cols = 0;
731 let $instant_add_column = ALTER TABLE t1 ADD COLUMN g INT NOT NULL DEFAULT 100, ALGORITHM=INPLACE;
732
733 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
734
735 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
736
737
738 CHECK TABLE t1;
739
740 SHOW CREATE TABLE t1;
741
742 DROP TABLE t1;
743
744
745 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c TEXT, FULLTEXT(c)) ROW_FORMAT=$row_format
746
747 INSERT INTO t1 VALUES(0, 1, 'Hello'), (0, 2, 'HELLO'), (0, 3, 'World'), (0, 4, 'Hello world'), (0, 5, 'HELLO WORLD');
748
749 # ADD COLUMN to FULLTEXT table should not be instant
750 let $new_cols = 0;
751 let $instant_add_column = ALTER TABLE t1 ADD COLUMN d INT NOT NULL DEFAULT 100 AFTER b;
752
753 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
754
755 DROP TABLE t1;
756
757
758 --echo #
759 --echo # Scenario 8:
760 --echo # Check FK constraints on instantly added columns
761 --echo #
762
763 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
764
765 CREATE TABLE t2(a INT NOT NULL PRIMARY KEY, b INT, c INT, KEY(c));
766
767 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
768
769 INSERT INTO t2 VALUES(1, 2, 3), (2, 3, 4), (3, 4, 5);
770
771 let $new_cols = 1;
772 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 3;
773
774 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
775
776 UPDATE t1 SET c = 4 WHERE a = 2;
777 UPDATE t1 SET c = 5 WHERE a = 3;
778
779 ALTER TABLE t1 ADD KEY(c);
780
781 ALTER TABLE t2 ADD FOREIGN KEY (c) REFERENCES t1(c) ON UPDATE CASCADE, ALGORITHM = COPY;
782
783 UPDATE t1 SET c = 10 WHERE a = 1;
784 --sorted_result
785 SELECT c FROM t2;
786
787 UPDATE t1 SET c = 10;
788 --sorted_result
789 SELECT c FROM t2;
790
791 CHECK TABLE t1;
792
793 SHOW CREATE TABLE t1;
794
795 DROP TABLE t2;
796 DROP TABLE t1;
797
798
799
800 --echo #
801 --echo # Scenario 9:
802 --echo # Instant ADD COLUMN on partitioned table, only simple test here
803 --echo #
804
805 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format PARTITION BY HASH(a) PARTITIONS 3;
806
807 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
808
809 let $new_cols = 1;
810 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
811
812 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
813
814 --sorted_result
815 SELECT * FROM t1;
816
817 INSERT INTO t1 VALUES(0, 9, 10), (0, 10, 20);
818
819 --sorted_result
820 SELECT * FROM t1 WHERE b > 8;
821
822 UPDATE t1 SET c = 8 WHERE a = 1 OR a = 3 OR a = 5 OR a = 7;
823
824 --sorted_result
825 SELECT * FROM t1;
826
827 CHECK TABLE t1;
828
829 SHOW CREATE TABLE t1;
830
831 DROP TABLE t1;
832
833
834 # Check instant ADD COLUMN and then common ALTER TABLE and then instant ADD COLUMN
835
836 --eval CREATE TABLE t1 (a INT, b INT) ROW_FORMAT=$row_format PARTITION BY HASH(a) PARTITIONS 2;
837
838 let $new_cols = 1;
839 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
840
841 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
842
843 ALTER TABLE t1 CHANGE COLUMN c c1 INT;
844
845 let $new_cols = 1;
846 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
847
848 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
849
850 DROP TABLE t1;
851
852
853 --echo #
854 --echo # Scenario 10:
855 --echo # EXCHANGE PARTITION is not allowed if either is instant
856 --echo #
857
858 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format;
859
860 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
861
862 let $new_cols = 1;
863 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
864
865 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
866
867 --eval CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c INT NOT NULL DEFAULT 5) ROW_FORMAT=$row_format PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30));
868
869 # t1 is instant table, so should fail
870 --error ER_PARTITION_EXCHANGE_DIFFERENT_OPTION
871 ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
872
873 # Make t2 an instant table
874 ALTER TABLE t2 ADD COLUMN d INT;
875
876 # Make t1 a non-instant table
877 ALTER TABLE t1 ADD COLUMN d INT, ALGORITHM=INPLACE;
878
879 # t2 is instant table, so should fail
880 --error ER_PARTITION_EXCHANGE_DIFFERENT_OPTION
881 ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
882
883 OPTIMIZE TABLE t2;
884 SHOW CREATE TABLE t1;
885 SHOW CREATE TABLE t2;
886 # Now this should succeed
887 ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
888
889 DROP TABLE t1, t2;
890
891
892 --echo #
893 --echo # Scenario 11:
894 --echo # PRIMARY KEY with more than one column, at least to verify it works with REDUNDANT
895 --echo #
896
897 --eval CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a, b)) ROW_FORMAT=$row_format;
898
899 INSERT INTO t1 VALUES(0, 1), (1, 2), (2, 3), (3, 4);
900
901 let $new_cols = 1;
902 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
903
904 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
905
906 --sorted_result
907 SELECT * FROM t1;
908 UPDATE t1 SET c = b WHERE b <= 2;
909
910 CHECK TABLE t1;
911 --sorted_result
912 SELECT * FROM t1;
913
914 DROP TABLE t1;
915
916
917 --echo #
918 --echo # Scenario 12:
919 --echo # Mix ALTER PARTITION and ALTER TABLE ... INPLACE. This is to check if first partition is not
920 --echo # instant after ALTER PARTITION, will the metadata be copied correctly
921 --echo #
922
923 --eval CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 TEXT) ENGINE = InnoDB PARTITION BY RANGE(col1 * 2) ( PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256) , PARTITION p2 VALUES LESS THAN (384) , PARTITION p3 VALUES LESS THAN MAXVALUE);
924
925 INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg');
926
927 let $new_cols = 2;
928 let $instant_add_column = ALTER TABLE t1 ALGORITHM DEFAULT, ADD COLUMN col5 VARCHAR(500), ADD COLUMN col6 TEXT;
929
930 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
931
932 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
933
934 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (64), PARTITION p0_b VALUES LESS THAN (128));
935 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
936 CHECK TABLE t1;
937
938 ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10));
939 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
940 CHECK TABLE t1;
941
942 ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table);
943 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
944 CHECK TABLE t1;
945
946 ALTER TABLE t1 ADD KEY idx3(col3);
947 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
948 CHECK TABLE t1;
949
950 --sorted_result
951 SELECT * FROM t1;
952
953 DROP TABLE t1;
954
955
956 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format PARTITION BY HASH(a) PARTITIONS 3;
957
958 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
959
960 let $new_cols = 1;
961 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
962
963 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
964
965 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
966
967 ALTER TABLE t1 ADD PARTITION PARTITIONS 10;
968 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
969 CHECK TABLE t1;
970
971 ALTER TABLE t1 ADD KEY(b);
972 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
973 CHECK TABLE t1;
974
975 --sorted_result
976 SELECT * FROM t1;
977
978 DROP TABLE t1;
979
980
981
982 --echo #
983 --echo # Scenario 13:
984 --echo # Create a table with a two level clustered index, do instant ADD COLUMN, then the non-leaf node
985 --echo # should be parsed correctly
986 --echo #
987
988 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, d INT NOT NULL, b BLOB NOT NULL, c VARCHAR(87), INDEX(d), INDEX(a ASC), PRIMARY KEY (a, d DESC, c DESC)) ROW_FORMAT=$row_format PARTITION BY LINEAR KEY(c) PARTITIONS 9;
989
990 INSERT INTO t1(d, b, c) VALUES(1, 2, 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhjjjjjjj');
991 INSERT INTO t1(d, b, c) VALUES(2, 3, 'aaaaaaaaaahhhhhhhhhhbbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
992 INSERT INTO t1(d, b, c) VALUES(3, 4, 'bbbbbbbbbbaaaaaaaaaahhhhhhhhhhccccccccccddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
993 INSERT INTO t1(d, b, c) VALUES(4, 5, 'eeeeeeeeeehhhhhhhhhhbbbbbbbbbbccccccccccddddddddddaaaaaaaaaaffffffffffggggggggggjjjjjjj');
994 INSERT INTO t1(d, b, c) VALUES(5, 6, 'aaaaaaaaaahhhhhhhhhhbbbbbbbbbbddddddddddcccccccccceeeeeeeeeeffffffffffggggggggggjjjjjjj');
995 INSERT INTO t1(d, b, c) VALUES(6, 7, 'cccccccccchhhhhhhhhhbbbbbbbbbbaaaaaaaaaaddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
996
997 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
998 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
999 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
1000 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
1001 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
1002 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
1003
1004 ALTER TABLE t1 ADD COLUMN nc086 BIGINT NOT NULL FIRST, ALGORITHM=INPLACE, LOCK=DEFAULT;
1005 CHECK TABLE t1;
1006
1007 ALTER TABLE t1 ADD COLUMN nc082 TINYTEXT;
1008
1009 CHECK TABLE t1;
1010
1011 SELECT COUNT(*) FROM t1;
1012
1013 DROP TABLE t1;
1014
1015
1016 --echo #
1017 --echo # Scenario 14:
1018 --echo # Create a small table, and add GIS kinds of new columns and verify
1019 --echo #
1020
1021 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
1022
1023 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1024
1025 # ADD COLUMN POINT
1026 let $new_cols = 1;
1027 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 POINT, ALGORITHM = INSTANT;
1028
1029 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1030
1031 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1032
1033 INSERT INTO t1(a, c1) VALUES(0, ST_PointFromText('POINT(10 10)'));
1034 SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
1035
1036 # ADD COLUMN LINESTRING
1037 let $new_cols = 2;
1038 let $instant_add_column = ALTER TABLE t1 ADD COLUMN d1 LINESTRING, ALGORITHM = INSTANT;
1039
1040 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1041
1042 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd1' AND has_default = 1;
1043
1044 INSERT INTO t1(a, d1) VALUES(0, ST_LineFromText('LINESTRING(0 0,0 10,10 0)'));
1045 SELECT count(*) = max(a) FROM t1 WHERE d1 IS NULL;
1046
1047 # ADD COLUMN POLYGON
1048 let $new_cols = 3;
1049 let $instant_add_column = ALTER TABLE t1 ADD COLUMN e1 POLYGON, ALGORITHM = INSTANT;
1050
1051 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1052
1053 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e1' AND has_default = 1;
1054
1055 INSERT INTO t1(a, e1) VALUES(0, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))'));
1056 SELECT count(*) = max(a) FROM t1 WHERE e1 IS NULL;
1057
1058 # ADD COLUMN MULTIPOINT
1059 let $new_cols = 4;
1060 let $instant_add_column = ALTER TABLE t1 ADD COLUMN f1 MULTIPOINT, ALGORITHM = INSTANT;
1061
1062 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1063
1064 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f1' AND has_default = 1;
1065
1066 INSERT INTO t1(a, f1) VALUES(0, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)'));
1067 SELECT count(*) = max(a) FROM t1 WHERE f1 IS NULL;
1068
1069 # ADD COLUMN MULTILINESTRING
1070 let $new_cols = 5;
1071 let $instant_add_column = ALTER TABLE t1 ADD COLUMN g1 MULTILINESTRING, ALGORITHM = INSTANT;
1072
1073 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1074
1075 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g1' AND has_default = 1;
1076
1077 INSERT INTO t1(a, g1) VALUES(0, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))'));
1078 SELECT count(*) = max(a) FROM t1 WHERE g1 IS NULL;
1079
1080 # ADD COLUMN MULTIPOLYGON
1081 let $new_cols = 6;
1082 let $instant_add_column = ALTER TABLE t1 ADD COLUMN h1 MULTIPOLYGON, ALGORITHM = INSTANT;
1083
1084 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1085
1086 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h1' AND has_default = 1;
1087
1088 INSERT INTO t1(a, h1) VALUES(0, ST_MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))'));
1089 SELECT count(*) = max(a) FROM t1 WHERE h1 IS NULL;
1090
1091
1092 # ADD COLUMN GEOMETRYCOLLECTION
1093 let $new_cols = 7;
1094 let $instant_add_column = ALTER TABLE t1 ADD COLUMN i1 GEOMETRYCOLLECTION, ALGORITHM = INSTANT;
1095
1096 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1097
1098 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'i1' AND has_default = 1;
1099
1100 INSERT INTO t1(a, i1) VALUES(0, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'));
1101 SELECT count(*) = max(a) FROM t1 WHERE i1 IS NULL;
1102
1103
1104 # ADD COLUMN GEOMETRY
1105 let $new_cols = 8;
1106 let $instant_add_column = ALTER TABLE t1 ADD COLUMN j1 GEOMETRY, ALGORITHM = INSTANT;
1107
1108 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1109
1110 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'j1' AND has_default = 1;
1111
1112 INSERT INTO t1(a, j1) VALUES(0, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'));
1113 SELECT count(*) = max(a) FROM t1 WHERE j1 IS NULL;
1114
1115 CHECK TABLE t1;
1116
1117 SHOW CREATE TABLE t1;
1118
1119 DROP TABLE t1;
1120
1121 --echo #
1122 --echo # Scenario 15:
1123 --echo # Create a small table, and add JSON columns and verify
1124 --echo #
1125
1126 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
1127
1128 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1129
1130 # ADD COLUMN JSON
1131 let $new_cols = 1;
1132 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 JSON, ALGORITHM = INSTANT;
1133
1134 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1135
1136 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1137
1138 INSERT INTO t1(a, c1) VALUES(0, '{"key1": "value1", "key2": "value2"}');
1139 SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
1140
1141 CHECK TABLE t1;
1142
1143 SHOW CREATE TABLE t1;
1144
1145 DROP TABLE t1;
1146
1147
1148 --echo #
1149 --echo # Scenario 16:
1150 --echo # Create a small table, and add INSTANT columns and verify with trigger
1151 --echo #
1152
1153 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
1154 --eval CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
1155
1156 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1157 INSERT INTO t2 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1158
1159 # ADD COLUMN
1160 let $new_cols = 1;
1161 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
1162
1163 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1164
1165 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1166
1167 # TRIGGERS AFTER INSERT INTO ADDED COLUMN
1168 CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
1169 INSERT INTO t2 VALUES(0,6);
1170
1171 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
1172 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
1173 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
1174 DROP TRIGGER t1_ai;
1175
1176
1177 # ADD COLUMN
1178 let $new_cols = 1;
1179 let $instant_add_column = ALTER TABLE t2 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'cccc', ALGORITHM = INSTANT;
1180
1181 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1182
1183 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1184
1185 # INSERT ROW INTO ADDED COLUMN USING TRIGGER
1186 CREATE TRIGGER t2_ai AFTER INSERT ON t2 FOR EACH ROW
1187 INSERT INTO t1(a,c1) VALUES(0,'eeee');
1188
1189 INSERT INTO t2(a, c1) VALUES(0, 'dddd');
1190 SELECT count(*) = max(a) FROM t2 WHERE c1='cccc';
1191 SELECT c1 FROM t2 WHERE c1 = 'dddd';
1192 DROP TRIGGER t2_ai;
1193
1194
1195 CHECK TABLE t1;
1196 CHECK TABLE t2;
1197
1198 SHOW CREATE TABLE t1;
1199 SHOW CREATE TABLE t2;
1200
1201 DROP TABLE t1,t2;
1202
1203
1204 --echo #
1205 --echo # Scenario 17:
1206 --echo # Create a small table, and add INSTANT columns and verify with storedprocedure
1207 --echo #
1208
1209 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
1210
1211 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1212
1213 # ADD COLUMN
1214 let $new_cols = 1;
1215 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
1216
1217 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1218
1219 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1220
1221 # STORED PROCEDURE
1222 CREATE PROCEDURE p1() INSERT INTO t1(a,c1) VALUES(0, 'bbbb');
1223 CALL p1();
1224
1225 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
1226 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
1227 DROP PROCEDURE p1;
1228
1229 CHECK TABLE t1;
1230
1231 SHOW CREATE TABLE t1;
1232
1233 DROP TABLE t1;
1234
1235
1236 --echo #
1237 --echo # Scenario 18:
1238 --echo # Create a small table, and add INSTANT columns and verify with view
1239 --echo #
1240
1241 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
1242
1243 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1244
1245 # ADD COLUMN
1246 let $new_cols = 1;
1247 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
1248
1249 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1250
1251 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1252
1253 # CREATE VIEW
1254 CREATE VIEW v1 AS SELECT * FROM t1;
1255
1256 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
1257 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
1258 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
1259 --sorted_result
1260 SELECT * FROM v1;
1261 DROP VIEW v1;
1262
1263 CHECK TABLE t1;
1264
1265 SHOW CREATE TABLE t1;
1266
1267 DROP TABLE t1;
1268
1269
1270 --echo #
1271 --echo # Scenario 19:
1272 --echo # Create a small table, and add INSTANT columns and drop it and verify
1273 --echo #
1274
1275 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
1276
1277 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1278
1279 # ADD COLUMN
1280 let $new_cols = 1;
1281 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
1282
1283 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1284
1285 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1286
1287 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
1288 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
1289 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
1290
1291 # DROP COLUMN
1292 let $new_cols = 0;
1293 let $instant_add_column = ALTER TABLE t1 DROP COLUMN c1;
1294
1295 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1296
1297 CHECK TABLE t1;
1298
1299 SHOW CREATE TABLE t1;
1300
1301 DROP TABLE t1;
1302
1303
1304 --echo #
1305 --echo # Scenario 20:
1306 --echo # Create a small table, and add INSTANT columns and rename table
1307 --echo #
1308
1309 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
1310
1311 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1312
1313 # ADD COLUMN
1314 let $new_cols = 1;
1315 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
1316
1317 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1318
1319 ALTER TABLE t1 RENAME t2;
1320
1321 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1322 INSERT INTO t2(a, c1) VALUES(0, 'bbbb');
1323 SELECT count(*) = max(a) FROM t2 WHERE c1='aaaa';
1324 SELECT c1 FROM t2 WHERE c1 = 'bbbb';
1325
1326 CHECK TABLE t2;
1327
1328 SHOW CREATE TABLE t2;
1329
1330 DROP TABLE t2;
1331
1332
1333 --echo #
1334 --echo # Scenario 21:
1335 --echo # Create a small table, and add INSTANT columns and change its data type INSTANTly won't work
1336 --echo #
1337
1338 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
1339
1340 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1341
1342 # ADD COLUMN
1343 let $new_cols = 1;
1344 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
1345
1346 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1347
1348 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1349 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
1350 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
1351 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
1352
1353 # change added column datatype should not be instant
1354 let $new_cols = 0;
1355 let $instant_add_column = ALTER TABLE t1 CHANGE c1 c2 CHAR(10) NOT NULL DEFAULT 'cccc';
1356
1357 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1358
1359 CHECK TABLE t1;
1360
1361 SHOW CREATE TABLE t1;
1362
1363 DROP TABLE t1;
1364
1365
1366 --echo #
1367 --echo # Scenario 22:
1368 --echo # Create a small table, and add INSTANT columns and create hash,btree multi column index and verify
1369 --echo #
1370
1371 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
1372
1373 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1374
1375 # ADD COLUMN
1376 let $new_cols = 1;
1377 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
1378
1379 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1380
1381 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1382
1383 # CREATE INDEX
1384 CREATE INDEX id1 ON t1(c1) USING BTREE;
1385 CREATE INDEX id2 ON t1(c1) USING HASH;
1386
1387 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
1388 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
1389 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
1390
1391 DROP INDEX id1 ON t1;
1392 DROP INDEX id2 ON t1;
1393
1394 # ADD ANOTHER COLUMN
1395 let $new_cols = 2;
1396 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c2 VARCHAR(10) NOT NULL DEFAULT 'cccc', ALGORITHM = INSTANT;
1397
1398 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1399
1400 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c2' AND has_default = 1;
1401
1402 # CREATE INDEX
1403 CREATE INDEX id1 ON t1(c1 ASC,c2 DESC) USING BTREE;
1404
1405 INSERT INTO t1(a, c2) VALUES(0, 'dddd');
1406 SELECT count(*) = max(a) FROM t1 WHERE c1='cccc';
1407 SELECT c1 FROM t1 WHERE c1 = 'dddd';
1408
1409 # RENAME INDEX
1410 ALTER TABLE t1 RENAME INDEX id1 TO id2;
1411
1412 DROP INDEX id2 ON t1;
1413
1414 CHECK TABLE t1;
1415
1416 SHOW CREATE TABLE t1;
1417
1418 DROP TABLE t1;
1419
1420
1421 --echo #
1422 --echo # Scenario 23:
1423 --echo # Create a small table, and add INSTANT columns and perform table join operation
1424 --echo #
1425
1426 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
1427 --eval CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
1428
1429 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1430 INSERT INTO t2 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1431
1432 # ADD COLUMN TO TABLE t1
1433 let $new_cols = 1;
1434 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
1435
1436 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1437
1438 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1439
1440 INSERT INTO t1(a, c1) VALUES(0, 'cccc');
1441 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
1442 SELECT c1 FROM t1 WHERE c1 = 'cccc';
1443
1444 # ADD COLUMN TO TABLE t2
1445 let $new_cols = 1;
1446 let $instant_add_column = ALTER TABLE t2 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'bbbb', ALGORITHM = INSTANT;
1447
1448 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1449
1450 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1451
1452 INSERT INTO t2(a, c1) VALUES(0, 'cccc');
1453 SELECT count(*) = max(a) FROM t2 WHERE c1='bbbb';
1454 SELECT c1 FROM t2 WHERE c1 = 'cccc';
1455
1456 --sorted_result
1457 SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1;
1458
1459 CHECK TABLE t1;
1460 CHECK TABLE t2;
1461
1462 SHOW CREATE TABLE t1;
1463 SHOW CREATE TABLE t2;
1464
1465 DROP TABLE t1;
1466 DROP TABLE t2;
1467
1468
1469 --echo #
1470 --echo # Scenario 24:
1471 --echo # Create a small table, and add stored and(or) virtual columns
1472 --echo # after last stored column in the table
1473 --echo #
1474
1475 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c INT, d INT GENERATED ALWAYS AS (b + c) VIRTUAL, e INT GENERATED ALWAYS AS (b * c) VIRTUAL) ROW_FORMAT=$row_format
1476
1477 INSERT INTO t1(a, b, c) VALUES(0, 1, 2), (0, 2, 3), (0, 3, 4), (0, 4, 5), (0, 5, 6);
1478
1479
1480 let $new_cols = 1;
1481 let $instant_add_column = ALTER TABLE t1 ADD COLUMN h INT NOT NULL AFTER c;
1482
1483 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1484
1485 INSERT INTO t1(a, b, c, h) VALUES(0, 6, 20, 40);
1486 --sorted_result
1487 SELECT * FROM t1;
1488
1489
1490 let $new_cols = 2;
1491 let $instant_add_column = ALTER TABLE t1 ADD COLUMN i VARCHAR(100) DEFAULT 'ABCD EFGH' AFTER h, ADD COLUMN f INT GENERATED ALWAYS AS (LENGTH(i)) AFTER i;
1492
1493 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
1494
1495 INSERT INTO t1(a, b, c, h, i) VALUES(0, 20, 30, 50, 'qwerty');
1496 --sorted_result
1497 SELECT * FROM t1;
1498
1499 CHECK TABLE t1;
1500
1501 SHOW CREATE TABLE t1;
1502
1503 DROP TABLE t1;