Debian Codegraph @

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

raw
1 ##############################################
2 # Test instant ADD COLUMN for REDUNDANT format
3 ##############################################
4 #
5 # Scenario 1:
6 # Create a small table, and add all kinds of new columns and verify
7 #
8 SET @saved_innodb_strict_mode=@@SESSION.innodb_strict_mode;
9 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
10 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
11 ALTER TABLE t1 ADD COLUMN c1 INT, ALGORITHM=INSTANT;
12 Table id did not change
13 count(*) = 1
14 1
15 CHECK TABLE t1;
16 Table Op Msg_type Msg_text
17 test.t1 check status OK
18 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
19 name default_value
20 c1 NULL
21 INSERT INTO t1(a, c1) VALUES(0, 1);
22 SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
23 count(*) = max(a)
24 1
25 SELECT c1 FROM t1 WHERE c1 = 1;
26 c1
27 1
28 ALTER TABLE t1 ADD COLUMN c2 INT NOT NULL DEFAULT 10;
29 Table id did not change
30 count(*) = 2
31 1
32 CHECK TABLE t1;
33 Table Op Msg_type Msg_text
34 test.t1 check status OK
35 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c2' AND has_default = 1;
36 name default_value
37 c2 8000000a
38 INSERT INTO t1(a, c2) VALUES(0, 11);
39 SELECT count(*) = max(a) FROM t1 WHERE c2 = 10;
40 count(*) = max(a)
41 1
42 SELECT c2 FROM t1 WHERE c2 = 11;
43 c2
44 11
45 ALTER TABLE t1 ADD COLUMN d1 BIGINT, ALGORITHM=INSTANT;
46 Table id did not change
47 count(*) = 3
48 1
49 CHECK TABLE t1;
50 Table Op Msg_type Msg_text
51 test.t1 check status OK
52 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd1' AND has_default = 1;
53 name default_value
54 d1 NULL
55 INSERT INTO t1(a, d1) VALUES(0, 1);
56 SELECT count(*) = max(a) FROM t1 WHERE d1 IS NULL;
57 count(*) = max(a)
58 1
59 SELECT d1 FROM t1 WHERE d1 = 1;
60 d1
61 1
62 ALTER TABLE t1 ADD COLUMN d2 BIGINT NOT NULL DEFAULT 1234567890;
63 Table id did not change
64 count(*) = 4
65 1
66 CHECK TABLE t1;
67 Table Op Msg_type Msg_text
68 test.t1 check status OK
69 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd2' AND has_default = 1;
70 name default_value
71 d2 80000000499602d2
72 INSERT INTO t1(a, d2) VALUES(0, 1234);
73 SELECT count(*) = max(a) FROM t1 WHERE d2 = 1234567890;
74 count(*) = max(a)
75 1
76 SELECT d2 FROM t1 WHERE d2 = 1234;
77 d2
78 1234
79 ALTER TABLE t1 ADD COLUMN e1 SMALLINT, ALGORITHM=INSTANT;
80 Table id did not change
81 count(*) = 5
82 1
83 CHECK TABLE t1;
84 Table Op Msg_type Msg_text
85 test.t1 check status OK
86 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e1' AND has_default = 1;
87 name default_value
88 e1 NULL
89 INSERT INTO t1(a, e1) VALUES(0, 1);
90 SELECT count(*) = max(a) FROM t1 WHERE e1 IS NULL;
91 count(*) = max(a)
92 1
93 SELECT e1 FROM t1 WHERE e1 = 1;
94 e1
95 1
96 ALTER TABLE t1 ADD COLUMN e2 SMALLINT NOT NULL DEFAULT 10;
97 Table id did not change
98 count(*) = 6
99 1
100 CHECK TABLE t1;
101 Table Op Msg_type Msg_text
102 test.t1 check status OK
103 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e2' AND has_default = 1;
104 name default_value
105 e2 800a
106 INSERT INTO t1(a, e2) VALUES(0, 11);
107 SELECT count(*) = max(a) FROM t1 WHERE e2 = 10;
108 count(*) = max(a)
109 1
110 SELECT e2 FROM t1 WHERE e2 = 11;
111 e2
112 11
113 ALTER TABLE t1 ADD COLUMN f1 TINYINT, ALGORITHM=INSTANT;
114 Table id did not change
115 count(*) = 7
116 1
117 CHECK TABLE t1;
118 Table Op Msg_type Msg_text
119 test.t1 check status OK
120 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f1' AND has_default = 1;
121 name default_value
122 f1 NULL
123 INSERT INTO t1(a, f1) VALUES(0, 1);
124 SELECT count(*) = max(a) FROM t1 WHERE f1 IS NULL;
125 count(*) = max(a)
126 1
127 SELECT f1 FROM t1 WHERE f1 = 1;
128 f1
129 1
130 ALTER TABLE t1 ADD COLUMN f2 TINYINT NOT NULL DEFAULT 123;
131 Table id did not change
132 count(*) = 8
133 1
134 CHECK TABLE t1;
135 Table Op Msg_type Msg_text
136 test.t1 check status OK
137 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f2' AND has_default = 1;
138 name default_value
139 f2 fb
140 INSERT INTO t1(a, f2) VALUES(0, 12);
141 SELECT count(*) = max(a) FROM t1 WHERE f2 = 123;
142 count(*) = max(a)
143 1
144 SELECT f2 FROM t1 WHERE f2 = 12;
145 f2
146 12
147 ALTER TABLE t1 ADD COLUMN g1 MEDIUMINT, ALGORITHM=INSTANT;
148 Table id did not change
149 count(*) = 9
150 1
151 CHECK TABLE t1;
152 Table Op Msg_type Msg_text
153 test.t1 check status OK
154 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g1' AND has_default = 1;
155 name default_value
156 g1 NULL
157 INSERT INTO t1(a, g1) VALUES(0, 1);
158 SELECT count(*) = max(a) FROM t1 WHERE g1 IS NULL;
159 count(*) = max(a)
160 1
161 SELECT g1 FROM t1 WHERE g1 = 1;
162 g1
163 1
164 ALTER TABLE t1 ADD COLUMN g2 MEDIUMINT NOT NULL DEFAULT 12345;
165 Table id did not change
166 count(*) = 10
167 1
168 CHECK TABLE t1;
169 Table Op Msg_type Msg_text
170 test.t1 check status OK
171 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g2' AND has_default = 1;
172 name default_value
173 g2 803039
174 INSERT INTO t1(a, g2) VALUES(0, 1234);
175 SELECT count(*) = max(a) FROM t1 WHERE g2 = 12345;
176 count(*) = max(a)
177 1
178 SELECT g2 FROM t1 WHERE g2 = 1234;
179 g2
180 1234
181 ALTER TABLE t1 ADD COLUMN h1 FLOAT, ALGORITHM=INSTANT;
182 Table id did not change
183 count(*) = 11
184 1
185 CHECK TABLE t1;
186 Table Op Msg_type Msg_text
187 test.t1 check status OK
188 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h1' AND has_default = 1;
189 name default_value
190 h1 NULL
191 INSERT INTO t1(a, h1) VALUES(0, 1.0);
192 SELECT count(*) = max(a) FROM t1 WHERE h1 IS NULL;
193 count(*) = max(a)
194 1
195 SELECT h1 FROM t1 WHERE h1 = 1;
196 h1
197 1
198 ALTER TABLE t1 ADD COLUMN h2 FLOAT NOT NULL DEFAULT 12.34;
199 Table id did not change
200 count(*) = 12
201 1
202 CHECK TABLE t1;
203 Table Op Msg_type Msg_text
204 test.t1 check status OK
205 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h2' AND has_default = 1;
206 name default_value
207 h2 a4704541
208 INSERT INTO t1(a, h2) VALUES(0, 1.234);
209 SELECT count(*) = max(a) FROM t1 WHERE h2 = 12.34;
210 count(*) = max(a)
211 NULL
212 SELECT h2 FROM t1 WHERE h2 = 1.234;
213 h2
214 ALTER TABLE t1 ADD COLUMN i1 DECIMAL(5, 2), ADD COLUMN i2 double, ALGORITHM=INSTANT;
215 Table id did not change
216 count(*) = 14
217 1
218 CHECK TABLE t1;
219 Table Op Msg_type Msg_text
220 test.t1 check status OK
221 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'i1' OR name = 'i2') AND has_default = 1;
222 name default_value
223 i1 NULL
224 i2 NULL
225 INSERT INTO t1(a, i1, i2) VALUES(0, 10.10, 20.20);
226 SELECT count(*) = max(a) FROM t1 WHERE i1 IS NULL;
227 count(*) = max(a)
228 1
229 SELECT count(*) = max(a) FROM t1 WHERE i2 IS NULL;
230 count(*) = max(a)
231 1
232 SELECT i1 FROM t1 WHERE i1 = 10.10;
233 i1
234 10.10
235 SELECT i2 FROM t1 WHERE i2 = 20.20;
236 i2
237 20.2
238 ALTER TABLE t1 ADD COLUMN j1 DECIMAL(5, 2) NOT NULL DEFAULT 100.00, ADD COLUMN j2 double NOT NULL DEFAULT 1000.5678;
239 Table id did not change
240 count(*) = 16
241 1
242 CHECK TABLE t1;
243 Table Op Msg_type Msg_text
244 test.t1 check status OK
245 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'j1' OR name = 'j2') AND has_default = 1;
246 name default_value
247 j1 806400
248 j2 5af5b9da8a448f40
249 INSERT INTO t1(a, j1, j2) VALUES(0, 90.90, 1000.1234);
250 SELECT count(*) = max(a) FROM t1 WHERE j1 = 100.00;
251 count(*) = max(a)
252 1
253 SELECT count(*) = max(a) FROM t1 WHERE j2 = 1000.5678;
254 count(*) = max(a)
255 1
256 SELECT j1 FROM t1 WHERE j1 = 90.90;
257 j1
258 90.90
259 SELECT j2 FROM t1 WHERE j2 = 1000.1234;
260 j2
261 1000.1234
262 ALTER TABLE t1 ADD COLUMN k1 BIT(8), ALGORITHM=INSTANT;
263 Table id did not change
264 count(*) = 17
265 1
266 CHECK TABLE t1;
267 Table Op Msg_type Msg_text
268 test.t1 check status OK
269 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'k1' AND has_default = 1;
270 name default_value
271 k1 NULL
272 INSERT INTO t1(a, k1) VALUES(0, b'010101');
273 SELECT count(*) = max(a) FROM t1 WHERE k1 IS NULL;
274 count(*) = max(a)
275 1
276 SELECT hex(k1) FROM t1 WHERE k1 = b'010101';
277 hex(k1)
278 15
279 ALTER TABLE t1 ADD COLUMN k2 BIT(8) NOT NULL DEFAULT b'101010';
280 Table id did not change
281 count(*) = 18
282 1
283 CHECK TABLE t1;
284 Table Op Msg_type Msg_text
285 test.t1 check status OK
286 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'k2' AND has_default = 1;
287 name default_value
288 k2 2a
289 INSERT INTO t1(a, k2) VALUES(0, b'110011');
290 SELECT count(*) = max(a) FROM t1 WHERE k2 = b'101010';
291 count(*) = max(a)
292 1
293 SELECT hex(k2) FROM t1 WHERE k2 = b'110011';
294 hex(k2)
295 33
296 ALTER TABLE t1 ADD COLUMN l1 CHAR(50), ADD COLUMN l2 VARCHAR(100), ALGORITHM=DEFAULT;
297 Table id did not change
298 count(*) = 20
299 1
300 CHECK TABLE t1;
301 Table Op Msg_type Msg_text
302 test.t1 check status OK
303 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'l1' OR name = 'l2') AND has_default = 1;
304 name default_value
305 l1 NULL
306 l2 NULL
307 INSERT INTO t1(a, l1, l2) VALUES(0, 'ABCD EFGH', 'abcdefg hijklmn ');
308 SELECT count(*) = max(a) FROM t1 WHERE l2 IS NULL;
309 count(*) = max(a)
310 1
311 SELECT count(*) = max(a) FROM t1 WHERE l1 IS NULL;
312 count(*) = max(a)
313 1
314 SELECT l1 FROM t1 WHERE l1 = 'ABCD EFGH';
315 l1
316 ABCD EFGH
317 SELECT l2 FROM t1 WHERE l2 = 'abcdefg hijklmn ';
318 l2
319 abcdefg hijklmn
320 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.';
321 Table id did not change
322 count(*) = 22
323 1
324 CHECK TABLE t1;
325 Table Op Msg_type Msg_text
326 test.t1 check status OK
327 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'm1' OR name = 'm2') AND has_default = 1;
328 name default_value
329 m1 54686520666f78206a756d7073206f7665722020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020
330 m2 54686520666f78206a756d7073206f76657220746865206c617a7920646f672e
331 INSERT INTO t1(a, m1, m2) VALUES(0, 'over the lazy dog', 'The lazy dog jumps over the fox.');
332 SELECT count(*) = max(a) FROM t1 WHERE m1 = 'The fox jumps over';
333 count(*) = max(a)
334 1
335 SELECT count(*) = max(a) FROM t1 WHERE m2 like 'The fox jumps%';
336 count(*) = max(a)
337 1
338 SELECT m1 FROM t1 WHERE m1 = 'over the lazy dog';
339 m1
340 over the lazy dog
341 SELECT m2 FROM t1 WHERE m2 like '%the fox.';
342 m2
343 The lazy dog jumps over the fox.
344 ALTER TABLE t1 ADD COLUMN n1 BINARY(10), ADD COLUMN n2 VARBINARY(10), ALGORITHM=DEFAULT;
345 Table id did not change
346 count(*) = 24
347 1
348 CHECK TABLE t1;
349 Table Op Msg_type Msg_text
350 test.t1 check status OK
351 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'n1' OR name = 'n2') AND has_default = 1;
352 name default_value
353 n1 NULL
354 n2 NULL
355 INSERT INTO t1(a, n1, n2) VALUES(0, 0x010203040506070809, 0x102030405060708090);
356 SELECT count(*) = max(a) FROM t1 WHERE n1 IS NULL;
357 count(*) = max(a)
358 1
359 SELECT count(*) = max(a) FROM t1 WHERE n2 IS NULL;
360 count(*) = max(a)
361 1
362 SELECT hex(n1) FROM t1 WHERE n1 = 0x01020304050607080900;
363 hex(n1)
364 01020304050607080900
365 SELECT hex(n2) FROM t1 WHERE n2 = 0x102030405060708090;
366 hex(n2)
367 102030405060708090
368 ALTER TABLE t1 ADD COLUMN o1 BINARY(10) DEFAULT 0x11223344, ADD COLUMN o2 VARBINARY(10) DEFAULT 0x55667788;
369 Table id did not change
370 count(*) = 26
371 1
372 CHECK TABLE t1;
373 Table Op Msg_type Msg_text
374 test.t1 check status OK
375 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'o1' OR name = 'o2') AND has_default = 1;
376 name default_value
377 o1 11223344000000000000
378 o2 55667788
379 INSERT INTO t1(a, o1, o2) VALUES(0, 0x44332211, 0x88776655);
380 SELECT count(*) = max(a) FROM t1 WHERE o1 = 0x11223344000000000000;
381 count(*) = max(a)
382 1
383 SELECT count(*) = max(a) FROM t1 WHERE o2 = 0x55667788;
384 count(*) = max(a)
385 1
386 SELECT hex(o1) FROM t1 WHERE o1 = 0x44332211000000000000;
387 hex(o1)
388 44332211000000000000
389 SELECT hex(o2) FROM t1 WHERE o2 = 0x88776655;
390 hex(o2)
391 88776655
392 ALTER TABLE t1 ADD COLUMN p1 DATETIME, ALGORITHM=DEFAULT;
393 Table id did not change
394 count(*) = 27
395 1
396 CHECK TABLE t1;
397 Table Op Msg_type Msg_text
398 test.t1 check status OK
399 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'p1' AND has_default = 1;
400 name default_value
401 p1 NULL
402 INSERT INTO t1(a, p1) VALUES(0, '2017-12-31 00:00:00');
403 SELECT count(*) = max(a) FROM t1 WHERE p1 IS NULL;
404 count(*) = max(a)
405 1
406 SELECT p1 FROM t1 WHERE p1 = '2017-12-31 00:00:00';
407 p1
408 2017-12-31 00:00:00
409 ALTER TABLE t1 ADD COLUMN p2 DATETIME NOT NULL DEFAULT '2017-12-31 01:02:03';
410 Table id did not change
411 count(*) = 28
412 1
413 CHECK TABLE t1;
414 Table Op Msg_type Msg_text
415 test.t1 check status OK
416 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'p2' AND has_default = 1;
417 name default_value
418 p2 999e7e1083
419 SELECT count(*) = max(a) FROM t1 GROUP BY p2;
420 count(*) = max(a)
421 1
422 INSERT INTO t1(a, p2) VALUES(0, now());
423 SELECT count(*) FROM t1 GROUP BY p2;
424 count(*)
425 26
426 1
427 ALTER TABLE t1 ADD COLUMN q1 ENUM ('value1','value2','value3'), ALGORITHM=INSTANT;
428 Table id did not change
429 count(*) = 29
430 1
431 CHECK TABLE t1;
432 Table Op Msg_type Msg_text
433 test.t1 check status OK
434 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'q1' AND has_default = 1;
435 name default_value
436 q1 NULL
437 INSERT INTO t1(a, q1) VALUES(0, 1);
438 SELECT count(*) = max(a) FROM t1 WHERE q1 IS NULL;
439 count(*) = max(a)
440 1
441 SELECT q1 FROM t1 WHERE q1 = 1;
442 q1
443 value1
444 ALTER TABLE t1 ADD COLUMN r1 SET ('a','b','c'), ALGORITHM=INSTANT;
445 Table id did not change
446 count(*) = 30
447 1
448 CHECK TABLE t1;
449 Table Op Msg_type Msg_text
450 test.t1 check status OK
451 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'r1' AND has_default = 1;
452 name default_value
453 r1 NULL
454 INSERT INTO t1(a, r1) VALUES(0, 'a');
455 SELECT count(*) = max(a) FROM t1 WHERE r1 IS NULL;
456 count(*) = max(a)
457 1
458 SELECT r1 FROM t1 WHERE r1 = 'a';
459 r1
460 a
461 ALTER TABLE t1 ADD COLUMN s1 BLOB, ADD COLUMN s2 TEXT, ALGORITHM=INSTANT;
462 Table id did not change
463 count(*) = 32
464 1
465 CHECK TABLE t1;
466 Table Op Msg_type Msg_text
467 test.t1 check status OK
468 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 's1' OR name = 's2') AND has_default = 1;
469 name default_value
470 s1 NULL
471 s2 NULL
472 INSERT INTO t1(a, s1, s2) VALUES(0, 0x0102030405, 'abcd qwerty');
473 SELECT count(*) = max(a) FROM t1 WHERE s1 IS NULL;
474 count(*) = max(a)
475 1
476 SELECT count(*) = max(a) FROM t1 WHERE s2 IS NULL;
477 count(*) = max(a)
478 1
479 SELECT hex(s1) FROM t1 WHERE s1 = 0x0102030405;
480 hex(s1)
481 0102030405
482 SELECT s2 FROM t1 WHERE s2 = 'abcd qwerty';
483 s2
484 abcd qwerty
485 ALTER TABLE t1 ADD COLUMN u1 BLOB NOT NULL, ADD COLUMN u2 TEXT NOT NULL;
486 Table id did not change
487 count(*) = 34
488 1
489 CHECK TABLE t1;
490 Table Op Msg_type Msg_text
491 test.t1 check status OK
492 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'u1' OR name = 'u2') AND has_default = 1;
493 name default_value
494 u1
495 u2
496 INSERT INTO t1(a, u1, u2) VALUES(0, 0x0102030405, 'abcd qwerty');
497 SELECT count(*) = max(a) FROM t1 WHERE u1 = '';
498 count(*) = max(a)
499 1
500 SELECT count(*) = max(a) FROM t1 WHERE u2 = '';
501 count(*) = max(a)
502 1
503 SELECT hex(u1) FROM t1 WHERE u1 = 0x0102030405;
504 hex(u1)
505 0102030405
506 SELECT u2 FROM t1 WHERE u2 = 'abcd qwerty';
507 u2
508 abcd qwerty
509 CHECK TABLE t1;
510 Table Op Msg_type Msg_text
511 test.t1 check status OK
512 SHOW CREATE TABLE t1;
513 Table Create Table
514 t1 CREATE TABLE `t1` (
515 `a` int NOT NULL AUTO_INCREMENT,
516 `b` int DEFAULT NULL,
517 `c1` int DEFAULT NULL,
518 `c2` int NOT NULL DEFAULT '10',
519 `d1` bigint DEFAULT NULL,
520 `d2` bigint NOT NULL DEFAULT '1234567890',
521 `e1` smallint DEFAULT NULL,
522 `e2` smallint NOT NULL DEFAULT '10',
523 `f1` tinyint DEFAULT NULL,
524 `f2` tinyint NOT NULL DEFAULT '123',
525 `g1` mediumint DEFAULT NULL,
526 `g2` mediumint NOT NULL DEFAULT '12345',
527 `h1` float DEFAULT NULL,
528 `h2` float NOT NULL DEFAULT '12.34',
529 `i1` decimal(5,2) DEFAULT NULL,
530 `i2` double DEFAULT NULL,
531 `j1` decimal(5,2) NOT NULL DEFAULT '100.00',
532 `j2` double NOT NULL DEFAULT '1000.5678',
533 `k1` bit(8) DEFAULT NULL,
534 `k2` bit(8) NOT NULL DEFAULT b'101010',
535 `l1` char(50) DEFAULT NULL,
536 `l2` varchar(100) DEFAULT NULL,
537 `m1` char(50) DEFAULT 'The fox jumps over',
538 `m2` varchar(50) DEFAULT 'The fox jumps over the lazy dog.',
539 `n1` binary(10) DEFAULT NULL,
540 `n2` varbinary(10) DEFAULT NULL,
541 `o1` binary(10) DEFAULT '"3D\0\0\0\0\0\0',
542 `o2` varbinary(10) DEFAULT 0x55667788,
543 `p1` datetime DEFAULT NULL,
544 `p2` datetime NOT NULL DEFAULT '2017-12-31 01:02:03',
545 `q1` enum('value1','value2','value3') DEFAULT NULL,
546 `r1` set('a','b','c') DEFAULT NULL,
547 `s1` blob,
548 `s2` text,
549 `u1` blob NOT NULL,
550 `u2` text NOT NULL,
551 PRIMARY KEY (`a`)
552 ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
553 DROP TABLE t1;
554 #
555 # Scenario 2:
556 # Create a small table, add some columns instantly, along with
557 # virtual columns
558 #
559 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
560 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
561 ALTER TABLE t1 ADD COLUMN c INT NOT NULL, ADD COLUMN d INT GENERATED ALWAYS AS ((b * 2)) VIRTUAL;
562 Table id did not change
563 count(*) = 1
564 1
565 CHECK TABLE t1;
566 Table Op Msg_type Msg_text
567 test.t1 check status OK
568 INSERT INTO t1(a, b, c) VALUES(0, 6, 20);
569 SELECT * FROM t1;
570 a b c d
571 1 1 0 2
572 2 2 0 4
573 3 3 0 6
574 4 4 0 8
575 5 5 0 10
576 6 6 20 12
577 ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'ABCD EFGH', ADD COLUMN f INT GENERATED ALWAYS AS (LENGTH(e));
578 Table id did not change
579 count(*) = 2
580 1
581 CHECK TABLE t1;
582 Table Op Msg_type Msg_text
583 test.t1 check status OK
584 INSERT INTO t1(a, c, e) VALUES(0, 20, 'Hello'), (0, 20, 'World'), (0, 20, 'Hello World');
585 SELECT * FROM t1;
586 a b c d e f
587 1 1 0 2 ABCD EFGH 9
588 2 2 0 4 ABCD EFGH 9
589 3 3 0 6 ABCD EFGH 9
590 4 4 0 8 ABCD EFGH 9
591 5 5 0 10 ABCD EFGH 9
592 6 6 20 12 ABCD EFGH 9
593 7 NULL 20 NULL Hello 5
594 8 NULL 20 NULL World 5
595 9 NULL 20 NULL Hello World 11
596 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);
597 Table id did not change
598 count(*) = 3
599 1
600 CHECK TABLE t1;
601 Table Op Msg_type Msg_text
602 test.t1 check status OK
603 SELECT * FROM t1;
604 a b c d e f g h i
605 1 1 0 2 ABCD EFGH 9 ABCD EFGH 10000 20001
606 2 2 0 4 ABCD EFGH 9 ABCD EFGH 10000 20002
607 3 3 0 6 ABCD EFGH 9 ABCD EFGH 10000 20003
608 4 4 0 8 ABCD EFGH 9 ABCD EFGH 10000 20004
609 5 5 0 10 ABCD EFGH 9 ABCD EFGH 10000 20005
610 6 6 20 12 ABCD EFGH 9 ABCD EFGH 10000 20006
611 7 NULL 20 NULL Hello 5 Hello 10000 NULL
612 8 NULL 20 NULL World 5 World 10000 NULL
613 9 NULL 20 NULL Hello World 11 Hello World 10000 NULL
614 INSERT INTO t1(a, b, c, h) VALUES(0, 7, 40, 2000), (0, 7, 40, 20000);
615 SELECT * FROM t1;
616 a b c d e f g h i
617 1 1 0 2 ABCD EFGH 9 ABCD EFGH 10000 20001
618 10 7 40 14 ABCD EFGH 9 ABCD EFGH 2000 4007
619 11 7 40 14 ABCD EFGH 9 ABCD EFGH 20000 40007
620 2 2 0 4 ABCD EFGH 9 ABCD EFGH 10000 20002
621 3 3 0 6 ABCD EFGH 9 ABCD EFGH 10000 20003
622 4 4 0 8 ABCD EFGH 9 ABCD EFGH 10000 20004
623 5 5 0 10 ABCD EFGH 9 ABCD EFGH 10000 20005
624 6 6 20 12 ABCD EFGH 9 ABCD EFGH 10000 20006
625 7 NULL 20 NULL Hello 5 Hello 10000 NULL
626 8 NULL 20 NULL World 5 World 10000 NULL
627 9 NULL 20 NULL Hello World 11 Hello World 10000 NULL
628 CHECK TABLE t1;
629 Table Op Msg_type Msg_text
630 test.t1 check status OK
631 SHOW CREATE TABLE t1;
632 Table Create Table
633 t1 CREATE TABLE `t1` (
634 `a` int NOT NULL AUTO_INCREMENT,
635 `b` int DEFAULT NULL,
636 `c` int NOT NULL,
637 `d` int GENERATED ALWAYS AS ((`b` * 2)) VIRTUAL,
638 `e` varchar(100) DEFAULT 'ABCD EFGH',
639 `f` int GENERATED ALWAYS AS (length(`e`)) VIRTUAL,
640 `g` varchar(100) GENERATED ALWAYS AS (`e`) VIRTUAL,
641 `h` bigint DEFAULT '10000',
642 `i` bigint GENERATED ALWAYS AS (((`h` * 2) + `b`)) VIRTUAL,
643 PRIMARY KEY (`a`)
644 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
645 DROP TABLE t1;
646 #
647 # Scenario 3:
648 # Create a small table, add some columns instantly, then change
649 # their default values, check original default values are correct
650 #
651 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
652 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
653 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';
654 Table id did not change
655 count(*) = 2
656 1
657 CHECK TABLE t1;
658 Table Op Msg_type Msg_text
659 test.t1 check status OK
660 SELECT * FROM t1;
661 a b c d e
662 1 1 100 201 Hello world
663 2 2 100 202 Hello world
664 3 3 100 203 Hello world
665 4 4 100 204 Hello world
666 5 5 100 205 Hello world
667 INSERT INTO t1(a, b, c, e) VALUES(0, 6, 200, 'Good day'), (0, 7, 300, 'Good DAY');
668 SELECT * FROM t1;
669 a b c d e
670 1 1 100 201 Hello world
671 2 2 100 202 Hello world
672 3 3 100 203 Hello world
673 4 4 100 204 Hello world
674 5 5 100 205 Hello world
675 6 6 200 406 Good day
676 7 7 300 607 Good DAY
677 SELECT default_value FROM information_schema.innodb_columns WHERE name = 'c' AND has_default = 1;
678 default_value
679 80000064
680 ALTER TABLE t1 ALTER COLUMN c SET DEFAULT 500;
681 SELECT default_value FROM information_schema.innodb_columns WHERE name = 'c' AND
682 has_default = 1;
683 default_value
684 80000064
685 SELECT * FROM t1;
686 a b c d e
687 1 1 100 201 Hello world
688 2 2 100 202 Hello world
689 3 3 100 203 Hello world
690 4 4 100 204 Hello world
691 5 5 100 205 Hello world
692 6 6 200 406 Good day
693 7 7 300 607 Good DAY
694 INSERT INTO t1(a, b) VALUES(0, 8), (0, 9);
695 SELECT * FROM t1;
696 a b c d e
697 1 1 100 201 Hello world
698 2 2 100 202 Hello world
699 3 3 100 203 Hello world
700 4 4 100 204 Hello world
701 5 5 100 205 Hello world
702 6 6 200 406 Good day
703 7 7 300 607 Good DAY
704 8 8 500 1008 Hello world
705 9 9 500 1009 Hello world
706 SELECT default_value FROM information_schema.innodb_columns WHERE name = 'e' AND has_default = 1;
707 default_value
708 48656c6c6f20776f726c64
709 ALTER TABLE t1 ALTER COLUMN e SET DEFAULT 'HELLO MySQL!';
710 SELECT default_value FROM information_schema.innodb_columns WHERE name = 'e' AND has_default = 1;
711 default_value
712 48656c6c6f20776f726c64
713 SELECT * FROM t1;
714 a b c d e
715 1 1 100 201 Hello world
716 2 2 100 202 Hello world
717 3 3 100 203 Hello world
718 4 4 100 204 Hello world
719 5 5 100 205 Hello world
720 6 6 200 406 Good day
721 7 7 300 607 Good DAY
722 8 8 500 1008 Hello world
723 9 9 500 1009 Hello world
724 INSERT INTO t1(a, b) VALUES(0, 10), (0, 20);
725 SELECT * FROM t1;
726 a b c d e
727 1 1 100 201 Hello world
728 10 10 500 1010 HELLO MySQL!
729 11 20 500 1020 HELLO MySQL!
730 2 2 100 202 Hello world
731 3 3 100 203 Hello world
732 4 4 100 204 Hello world
733 5 5 100 205 Hello world
734 6 6 200 406 Good day
735 7 7 300 607 Good DAY
736 8 8 500 1008 Hello world
737 9 9 500 1009 Hello world
738 CHECK TABLE t1;
739 Table Op Msg_type Msg_text
740 test.t1 check status OK
741 SHOW CREATE TABLE t1;
742 Table Create Table
743 t1 CREATE TABLE `t1` (
744 `a` int NOT NULL AUTO_INCREMENT,
745 `b` int DEFAULT NULL,
746 `c` int NOT NULL DEFAULT '500',
747 `d` int GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
748 `e` varchar(100) DEFAULT 'HELLO MySQL!',
749 PRIMARY KEY (`a`)
750 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
751 DROP TABLE t1;
752 #
753 # Scenario 4:
754 # Create a small table, add some columns instantly, then do DML
755 # on the table
756 #
757 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
758 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
759 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';
760 Table id did not change
761 count(*) = 2
762 1
763 CHECK TABLE t1;
764 Table Op Msg_type Msg_text
765 test.t1 check status OK
766 UPDATE t1 SET c = 200 WHERE a > 3;
767 SELECT distinct(c) FROM t1;
768 c
769 100
770 200
771 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
772 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
773 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
774 DELETE FROM t1 WHERE c = 100;
775 UPDATE t1 SET c = 300;
776 SELECT distinct(c) FROM t1;
777 c
778 300
779 SELECT count(*) FROM t1;
780 count(*)
781 16
782 ALTER TABLE t1 ADD COLUMN t DATETIME DEFAULT CURRENT_TIMESTAMP;
783 Table id did not change
784 count(*) = 3
785 1
786 CHECK TABLE t1;
787 Table Op Msg_type Msg_text
788 test.t1 check status OK
789 UPDATE t1 SET e = 'Hello MySQL' WHERE a > 10;
790 UPDATE t1 SET e = 'Hello MySQL!!' WHERE a > 20;
791 SELECT distinct(e) FROM t1;
792 e
793 Hello MySQL
794 Hello MySQL!!
795 Hello world
796 UPDATE t1 SET c = 500 WHERE e LIKE '%world%';
797 SELECT c, e FROM t1 GROUP BY c, e;
798 c e
799 300 Hello MySQL
800 300 Hello MySQL!!
801 500 Hello world
802 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
803 UPDATE t1 SET t = CURRENT_TIMESTAMP WHERE a < 50;
804 SELECT count(t) FROM t1 GROUP BY t;
805 count(t)
806 16
807 16
808 DELETE FROM t1 WHERE a < 50;
809 SELECT count(t) FROM t1 GROUP BY t;
810 count(t)
811 16
812 CHECK TABLE t1;
813 Table Op Msg_type Msg_text
814 test.t1 check status OK
815 SHOW CREATE TABLE t1;
816 Table Create Table
817 t1 CREATE TABLE `t1` (
818 `a` int NOT NULL AUTO_INCREMENT,
819 `b` int DEFAULT NULL,
820 `c` int NOT NULL DEFAULT '100',
821 `d` int GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
822 `e` varchar(100) DEFAULT 'Hello world',
823 `t` datetime DEFAULT CURRENT_TIMESTAMP,
824 PRIMARY KEY (`a`)
825 ) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
826 DROP TABLE t1;
827 #
828 # Scenario 5:
829 # Create a small table, add some columns instantly, then do DDL
830 # to build indexes
831 #
832 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
833 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
834 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';
835 Table id did not change
836 count(*) = 2
837 1
838 CHECK TABLE t1;
839 Table Op Msg_type Msg_text
840 test.t1 check status OK
841 ALTER TABLE t1 ADD KEY(c);
842 EXPLAIN SELECT c FROM t1;
843 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
844 1 SIMPLE t1 NULL index NULL c # NULL # 100.00 Using index
845 Warnings:
846 Note 1003 /* select#1 */ select `test`.`t1`.`c` AS `c` from `test`.`t1`
847 SELECT c FROM t1 WHERE c != 100;
848 c
849 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
850 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
851 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
852 UPDATE t1 SET e = 'Hello MySQL' WHERE a > 30;
853 ALTER TABLE t1 ADD KEY(e);
854 EXPLAIN SELECT e FROM t1;
855 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
856 1 SIMPLE t1 NULL index NULL e # NULL # 100.00 Using index
857 Warnings:
858 Note 1003 /* select#1 */ select `test`.`t1`.`e` AS `e` from `test`.`t1`
859 SELECT count(e) FROM t1 WHERE e LIKE '%MySQL%';
860 count(e)
861 17
862 SELECT count(e) FROM t1 WHERE e LIKE '%world%';
863 count(e)
864 23
865 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a, c);
866 SELECT a, c, e FROM t1 WHERE a > 25 AND a < 40;
867 a c e
868 28 100 Hello world
869 29 100 Hello world
870 30 100 Hello world
871 31 100 Hello MySQL
872 32 100 Hello MySQL
873 33 100 Hello MySQL
874 34 100 Hello MySQL
875 35 100 Hello MySQL
876 36 100 Hello MySQL
877 37 100 Hello MySQL
878 38 100 Hello MySQL
879 39 100 Hello MySQL
880 CHECK TABLE t1;
881 Table Op Msg_type Msg_text
882 test.t1 check status OK
883 SHOW CREATE TABLE t1;
884 Table Create Table
885 t1 CREATE TABLE `t1` (
886 `a` int NOT NULL AUTO_INCREMENT,
887 `b` int DEFAULT NULL,
888 `c` int NOT NULL DEFAULT '100',
889 `d` int GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
890 `e` varchar(100) DEFAULT 'Hello world',
891 PRIMARY KEY (`a`,`c`),
892 KEY `c` (`c`),
893 KEY `e` (`e`)
894 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
895 DROP TABLE t1;
896 #
897 # Scenario 6:
898 # Create a small table, add some columns instantly, then do DML
899 # on the table, and some simple rollback
900 #
901 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
902 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
903 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';
904 Table id did not change
905 count(*) = 2
906 1
907 CHECK TABLE t1;
908 Table Op Msg_type Msg_text
909 test.t1 check status OK
910 START TRANSACTION;
911 INSERT INTO t1(a, b) VALUES(0, 6);
912 SELECT * FROM t1;
913 a b c d e
914 1 1 100 201 Hello world
915 2 2 100 202 Hello world
916 3 3 100 203 Hello world
917 4 4 100 204 Hello world
918 5 5 100 205 Hello world
919 6 6 100 206 Hello world
920 ROLLBACK;
921 SELECT * FROM t1;
922 a b c d e
923 1 1 100 201 Hello world
924 2 2 100 202 Hello world
925 3 3 100 203 Hello world
926 4 4 100 204 Hello world
927 5 5 100 205 Hello world
928 START TRANSACTION;
929 UPDATE t1 SET c = 500 WHERE a = 1;
930 UPDATE t1 SET b = 1000 WHERE a = 2;
931 SELECT a, b, c FROM t1 WHERE a = 1 OR a = 2;
932 a b c
933 1 1 500
934 2 1000 100
935 ROLLBACK;
936 SELECT a, b, c FROM t1;
937 a b c
938 1 1 100
939 2 2 100
940 3 3 100
941 4 4 100
942 5 5 100
943 START TRANSACTION;
944 DELETE FROM t1 WHERE a < 5;
945 INSERT INTO t1(a, b) VALUES(0, 6);
946 SELECT * FROM t1;
947 a b c d e
948 5 5 100 205 Hello world
949 7 6 100 206 Hello world
950 ROLLBACK;
951 SELECT * FROM t1;
952 a b c d e
953 1 1 100 201 Hello world
954 2 2 100 202 Hello world
955 3 3 100 203 Hello world
956 4 4 100 204 Hello world
957 5 5 100 205 Hello world
958 CHECK TABLE t1;
959 Table Op Msg_type Msg_text
960 test.t1 check status OK
961 SHOW CREATE TABLE t1;
962 Table Create Table
963 t1 CREATE TABLE `t1` (
964 `a` int NOT NULL AUTO_INCREMENT,
965 `b` int DEFAULT NULL,
966 `c` int NOT NULL DEFAULT '100',
967 `d` int GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
968 `e` varchar(100) DEFAULT 'Hello world',
969 PRIMARY KEY (`a`)
970 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
971 DROP TABLE t1;
972 SET SESSION innodb_strict_mode=OFF;
973 CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=REDUNDANT;
974 SET SESSION innodb_strict_mode=@saved_innodb_strict_mode;
975 INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1));
976 SELECT id, length(c1), length(c2), length(c3) FROM t1;
977 id length(c1) length(c2) length(c3)
978 1 4000 4000 1
979 ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd';
980 Table id did not change
981 count(*) = 1
982 1
983 CHECK TABLE t1;
984 Table Op Msg_type Msg_text
985 test.t1 check status OK
986 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
987 id length(c1) length(c2) length(c3) length(c4)
988 1 4000 4000 1 500
989 START TRANSACTION;
990 UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
991 ROLLBACK;
992 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
993 id length(c1) length(c2) length(c3) length(c4)
994 1 4000 4000 1 500
995 START TRANSACTION;
996 UPDATE t1 SET c4 = 'x' WHERE id = 1;
997 ROLLBACK;
998 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
999 id length(c1) length(c2) length(c3) length(c4)
1000 1 4000 4000 1 500
1001 DROP TABLE t1;
1002 #
1003 # Scenario 7:
1004 # Confirm some ADD COLUMN are instant, some are not
1005 #
1006 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
1007 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1008 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100 AFTER b;
1009 Table id did not change
1010 count(*) = 1
1011 1
1012 CHECK TABLE t1;
1013 Table Op Msg_type Msg_text
1014 test.t1 check status OK
1015 ALTER TABLE t1 ADD COLUMN d INT NOT NULL DEFAULT 100 AFTER b;
1016 Table id did not change
1017 count(*) = 2
1018 1
1019 CHECK TABLE t1;
1020 Table Op Msg_type Msg_text
1021 test.t1 check status OK
1022 SELECT count(*) AS `Expect 2` FROM information_schema.innodb_columns WHERE has_default = 1;
1023 Expect 2
1024 2
1025 ALTER TABLE t1 ADD COLUMN e INT NOT NULL DEFAULT 100, ADD KEY(e);
1026 Table ID differed
1027 count(*) = 0
1028 1
1029 CHECK TABLE t1;
1030 Table Op Msg_type Msg_text
1031 test.t1 check status OK
1032 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
1033 Expect 0
1034 0
1035 ALTER TABLE t1 ADD COLUMN f INT NOT NULL DEFAULT 100, FORCE;
1036 Table ID differed
1037 count(*) = 0
1038 1
1039 CHECK TABLE t1;
1040 Table Op Msg_type Msg_text
1041 test.t1 check status OK
1042 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
1043 Expect 0
1044 0
1045 ALTER TABLE t1 ADD COLUMN g INT NOT NULL DEFAULT 100, ALGORITHM=INPLACE;
1046 Table ID differed
1047 count(*) = 0
1048 1
1049 CHECK TABLE t1;
1050 Table Op Msg_type Msg_text
1051 test.t1 check status OK
1052 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
1053 Expect 0
1054 0
1055 CHECK TABLE t1;
1056 Table Op Msg_type Msg_text
1057 test.t1 check status OK
1058 SHOW CREATE TABLE t1;
1059 Table Create Table
1060 t1 CREATE TABLE `t1` (
1061 `a` int NOT NULL AUTO_INCREMENT,
1062 `b` int DEFAULT NULL,
1063 `d` int NOT NULL DEFAULT '100',
1064 `c` int NOT NULL DEFAULT '100',
1065 `e` int NOT NULL DEFAULT '100',
1066 `f` int NOT NULL DEFAULT '100',
1067 `g` int NOT NULL DEFAULT '100',
1068 PRIMARY KEY (`a`),
1069 KEY `e` (`e`)
1070 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1071 DROP TABLE t1;
1072 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c TEXT, FULLTEXT(c)) ROW_FORMAT=REDUNDANT;
1073 INSERT INTO t1 VALUES(0, 1, 'Hello'), (0, 2, 'HELLO'), (0, 3, 'World'), (0, 4, 'Hello world'), (0, 5, 'HELLO WORLD');
1074 ALTER TABLE t1 ADD COLUMN d INT NOT NULL DEFAULT 100 AFTER b;
1075 Table ID differed
1076 count(*) = 0
1077 1
1078 CHECK TABLE t1;
1079 Table Op Msg_type Msg_text
1080 test.t1 check status OK
1081 DROP TABLE t1;
1082 #
1083 # Scenario 8:
1084 # Check FK constraints on instantly added columns
1085 #
1086 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
1087 CREATE TABLE t2(a INT NOT NULL PRIMARY KEY, b INT, c INT, KEY(c));
1088 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1089 INSERT INTO t2 VALUES(1, 2, 3), (2, 3, 4), (3, 4, 5);
1090 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 3;
1091 Table id did not change
1092 count(*) = 1
1093 1
1094 CHECK TABLE t1;
1095 Table Op Msg_type Msg_text
1096 test.t1 check status OK
1097 UPDATE t1 SET c = 4 WHERE a = 2;
1098 UPDATE t1 SET c = 5 WHERE a = 3;
1099 ALTER TABLE t1 ADD KEY(c);
1100 ALTER TABLE t2 ADD FOREIGN KEY (c) REFERENCES t1(c) ON UPDATE CASCADE, ALGORITHM = COPY;
1101 UPDATE t1 SET c = 10 WHERE a = 1;
1102 SELECT c FROM t2;
1103 c
1104 10
1105 4
1106 5
1107 UPDATE t1 SET c = 10;
1108 SELECT c FROM t2;
1109 c
1110 10
1111 10
1112 10
1113 CHECK TABLE t1;
1114 Table Op Msg_type Msg_text
1115 test.t1 check status OK
1116 SHOW CREATE TABLE t1;
1117 Table Create Table
1118 t1 CREATE TABLE `t1` (
1119 `a` int NOT NULL AUTO_INCREMENT,
1120 `b` int DEFAULT NULL,
1121 `c` int NOT NULL DEFAULT '3',
1122 PRIMARY KEY (`a`),
1123 KEY `c` (`c`)
1124 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1125 DROP TABLE t2;
1126 DROP TABLE t1;
1127 #
1128 # Scenario 9:
1129 # Instant ADD COLUMN on partitioned table, only simple test here
1130 #
1131 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT PARTITION BY HASH(a) PARTITIONS 3;;
1132 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
1133 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
1134 Table id did not change
1135 count(*) = 1
1136 1
1137 CHECK TABLE t1;
1138 Table Op Msg_type Msg_text
1139 test.t1 check status OK
1140 SELECT * FROM t1;
1141 a b c
1142 1 1 5
1143 2 2 5
1144 3 3 5
1145 4 4 5
1146 5 5 5
1147 6 6 5
1148 7 7 5
1149 8 8 5
1150 INSERT INTO t1 VALUES(0, 9, 10), (0, 10, 20);
1151 SELECT * FROM t1 WHERE b > 8;
1152 a b c
1153 10 10 20
1154 9 9 10
1155 UPDATE t1 SET c = 8 WHERE a = 1 OR a = 3 OR a = 5 OR a = 7;
1156 SELECT * FROM t1;
1157 a b c
1158 1 1 8
1159 10 10 20
1160 2 2 5
1161 3 3 8
1162 4 4 5
1163 5 5 8
1164 6 6 5
1165 7 7 8
1166 8 8 5
1167 9 9 10
1168 CHECK TABLE t1;
1169 Table Op Msg_type Msg_text
1170 test.t1 check status OK
1171 SHOW CREATE TABLE t1;
1172 Table Create Table
1173 t1 CREATE TABLE `t1` (
1174 `a` int NOT NULL AUTO_INCREMENT,
1175 `b` int DEFAULT NULL,
1176 `c` int NOT NULL DEFAULT '5',
1177 PRIMARY KEY (`a`)
1178 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1179 /*!50100 PARTITION BY HASH (`a`)
1180 PARTITIONS 3 */
1181 DROP TABLE t1;
1182 CREATE TABLE t1 (a INT, b INT) ROW_FORMAT=REDUNDANT PARTITION BY HASH(a) PARTITIONS 2;;
1183 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
1184 Table id did not change
1185 count(*) = 1
1186 1
1187 CHECK TABLE t1;
1188 Table Op Msg_type Msg_text
1189 test.t1 check status OK
1190 ALTER TABLE t1 CHANGE COLUMN c c1 INT;
1191 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
1192 Table id did not change
1193 count(*) = 1
1194 1
1195 CHECK TABLE t1;
1196 Table Op Msg_type Msg_text
1197 test.t1 check status OK
1198 DROP TABLE t1;
1199 #
1200 # Scenario 10:
1201 # EXCHANGE PARTITION is not allowed if either is instant
1202 #
1203 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;;
1204 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
1205 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
1206 Table id did not change
1207 count(*) = 1
1208 1
1209 CHECK TABLE t1;
1210 Table Op Msg_type Msg_text
1211 test.t1 check status OK
1212 CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c INT NOT NULL DEFAULT 5) ROW_FORMAT=REDUNDANT PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30));;
1213 ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
1214 ERROR HY000: Non matching attribute 'INSTANT COLUMN(s)' between partition and table
1215 ALTER TABLE t2 ADD COLUMN d INT;
1216 ALTER TABLE t1 ADD COLUMN d INT, ALGORITHM=INPLACE;
1217 ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
1218 ERROR HY000: Non matching attribute 'INSTANT COLUMN(s)' between partition and table
1219 OPTIMIZE TABLE t2;
1220 Table Op Msg_type Msg_text
1221 test.t2 optimize note Table does not support optimize, doing recreate + analyze instead
1222 test.t2 optimize status OK
1223 SHOW CREATE TABLE t1;
1224 Table Create Table
1225 t1 CREATE TABLE `t1` (
1226 `a` int NOT NULL AUTO_INCREMENT,
1227 `b` int DEFAULT NULL,
1228 `c` int NOT NULL DEFAULT '5',
1229 `d` int DEFAULT NULL,
1230 PRIMARY KEY (`a`)
1231 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1232 SHOW CREATE TABLE t2;
1233 Table Create Table
1234 t2 CREATE TABLE `t2` (
1235 `a` int NOT NULL AUTO_INCREMENT,
1236 `b` int DEFAULT NULL,
1237 `c` int NOT NULL DEFAULT '5',
1238 `d` int DEFAULT NULL,
1239 PRIMARY KEY (`a`)
1240 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1241 /*!50100 PARTITION BY RANGE (`a`)
1242 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
1243 PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
1244 PARTITION p3 VALUES LESS THAN (30) ENGINE = InnoDB) */
1245 ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
1246 DROP TABLE t1, t2;
1247 #
1248 # Scenario 11:
1249 # PRIMARY KEY with more than one column, at least to verify it works with REDUNDANT
1250 #
1251 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a, b)) ROW_FORMAT=REDUNDANT;;
1252 INSERT INTO t1 VALUES(0, 1), (1, 2), (2, 3), (3, 4);
1253 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
1254 Table id did not change
1255 count(*) = 1
1256 1
1257 CHECK TABLE t1;
1258 Table Op Msg_type Msg_text
1259 test.t1 check status OK
1260 SELECT * FROM t1;
1261 a b c
1262 0 1 5
1263 1 2 5
1264 2 3 5
1265 3 4 5
1266 UPDATE t1 SET c = b WHERE b <= 2;
1267 CHECK TABLE t1;
1268 Table Op Msg_type Msg_text
1269 test.t1 check status OK
1270 SELECT * FROM t1;
1271 a b c
1272 0 1 1
1273 1 2 2
1274 2 3 5
1275 3 4 5
1276 DROP TABLE t1;
1277 #
1278 # Scenario 12:
1279 # Mix ALTER PARTITION and ALTER TABLE ... INPLACE. This is to check if first partition is not
1280 # instant after ALTER PARTITION, will the metadata be copied correctly
1281 #
1282 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);;
1283 INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg');
1284 ALTER TABLE t1 ALGORITHM DEFAULT, ADD COLUMN col5 VARCHAR(500), ADD COLUMN col6 TEXT;
1285 Table id did not change
1286 count(*) = 2
1287 1
1288 CHECK TABLE t1;
1289 Table Op Msg_type Msg_text
1290 test.t1 check status OK
1291 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
1292 Expect 0
1293 0
1294 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (64), PARTITION p0_b VALUES LESS THAN (128));
1295 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
1296 Expect 0
1297 0
1298 CHECK TABLE t1;
1299 Table Op Msg_type Msg_text
1300 test.t1 check status OK
1301 ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10));
1302 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
1303 Expect 0
1304 0
1305 CHECK TABLE t1;
1306 Table Op Msg_type Msg_text
1307 test.t1 check status OK
1308 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);
1309 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
1310 Expect 0
1311 0
1312 CHECK TABLE t1;
1313 Table Op Msg_type Msg_text
1314 test.t1 check status OK
1315 ALTER TABLE t1 ADD KEY idx3(col3);
1316 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
1317 Expect 0
1318 0
1319 CHECK TABLE t1;
1320 Table Op Msg_type Msg_text
1321 test.t1 check status OK
1322 SELECT * FROM t1;
1323 col1 col2 col3 col4 col5 col6
1324 1 2 3 abcdefg NULL NULL
1325 100 200 300 qwerty NULL NULL
1326 200 300 400 asdfg NULL NULL
1327 DROP TABLE t1;
1328 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT PARTITION BY HASH(a) PARTITIONS 3;;
1329 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
1330 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
1331 Table id did not change
1332 count(*) = 1
1333 1
1334 CHECK TABLE t1;
1335 Table Op Msg_type Msg_text
1336 test.t1 check status OK
1337 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
1338 Expect 3
1339 0
1340 ALTER TABLE t1 ADD PARTITION PARTITIONS 10;
1341 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
1342 Expect 0
1343 0
1344 CHECK TABLE t1;
1345 Table Op Msg_type Msg_text
1346 test.t1 check status OK
1347 ALTER TABLE t1 ADD KEY(b);
1348 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
1349 Expect 0
1350 0
1351 CHECK TABLE t1;
1352 Table Op Msg_type Msg_text
1353 test.t1 check status OK
1354 SELECT * FROM t1;
1355 a b c
1356 1 1 5
1357 2 2 5
1358 3 3 5
1359 4 4 5
1360 5 5 5
1361 6 6 5
1362 7 7 5
1363 8 8 5
1364 DROP TABLE t1;
1365 #
1366 # Scenario 13:
1367 # Create a table with a two level clustered index, do instant ADD COLUMN, then the non-leaf node
1368 # should be parsed correctly
1369 #
1370 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=REDUNDANT PARTITION BY LINEAR KEY(c) PARTITIONS 9;;
1371 INSERT INTO t1(d, b, c) VALUES(1, 2, 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhjjjjjjj');
1372 INSERT INTO t1(d, b, c) VALUES(2, 3, 'aaaaaaaaaahhhhhhhhhhbbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
1373 INSERT INTO t1(d, b, c) VALUES(3, 4, 'bbbbbbbbbbaaaaaaaaaahhhhhhhhhhccccccccccddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
1374 INSERT INTO t1(d, b, c) VALUES(4, 5, 'eeeeeeeeeehhhhhhhhhhbbbbbbbbbbccccccccccddddddddddaaaaaaaaaaffffffffffggggggggggjjjjjjj');
1375 INSERT INTO t1(d, b, c) VALUES(5, 6, 'aaaaaaaaaahhhhhhhhhhbbbbbbbbbbddddddddddcccccccccceeeeeeeeeeffffffffffggggggggggjjjjjjj');
1376 INSERT INTO t1(d, b, c) VALUES(6, 7, 'cccccccccchhhhhhhhhhbbbbbbbbbbaaaaaaaaaaddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
1377 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
1378 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
1379 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
1380 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
1381 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
1382 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
1383 ALTER TABLE t1 ADD COLUMN nc086 BIGINT NOT NULL FIRST, ALGORITHM=INPLACE, LOCK=DEFAULT;
1384 CHECK TABLE t1;
1385 Table Op Msg_type Msg_text
1386 test.t1 check status OK
1387 ALTER TABLE t1 ADD COLUMN nc082 TINYTEXT;
1388 CHECK TABLE t1;
1389 Table Op Msg_type Msg_text
1390 test.t1 check status OK
1391 SELECT COUNT(*) FROM t1;
1392 COUNT(*)
1393 384
1394 DROP TABLE t1;
1395 #
1396 # Scenario 14:
1397 # Create a small table, and add GIS kinds of new columns and verify
1398 #
1399 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
1400 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1401 ALTER TABLE t1 ADD COLUMN c1 POINT, ALGORITHM = INSTANT;
1402 Table id did not change
1403 count(*) = 1
1404 1
1405 CHECK TABLE t1;
1406 Table Op Msg_type Msg_text
1407 test.t1 check status OK
1408 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1409 name default_value
1410 c1 NULL
1411 INSERT INTO t1(a, c1) VALUES(0, ST_PointFromText('POINT(10 10)'));
1412 SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
1413 count(*) = max(a)
1414 1
1415 ALTER TABLE t1 ADD COLUMN d1 LINESTRING, ALGORITHM = INSTANT;
1416 Table id did not change
1417 count(*) = 2
1418 1
1419 CHECK TABLE t1;
1420 Table Op Msg_type Msg_text
1421 test.t1 check status OK
1422 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd1' AND has_default = 1;
1423 name default_value
1424 d1 NULL
1425 INSERT INTO t1(a, d1) VALUES(0, ST_LineFromText('LINESTRING(0 0,0 10,10 0)'));
1426 SELECT count(*) = max(a) FROM t1 WHERE d1 IS NULL;
1427 count(*) = max(a)
1428 1
1429 ALTER TABLE t1 ADD COLUMN e1 POLYGON, ALGORITHM = INSTANT;
1430 Table id did not change
1431 count(*) = 3
1432 1
1433 CHECK TABLE t1;
1434 Table Op Msg_type Msg_text
1435 test.t1 check status OK
1436 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e1' AND has_default = 1;
1437 name default_value
1438 e1 NULL
1439 INSERT INTO t1(a, e1) VALUES(0, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))'));
1440 SELECT count(*) = max(a) FROM t1 WHERE e1 IS NULL;
1441 count(*) = max(a)
1442 1
1443 ALTER TABLE t1 ADD COLUMN f1 MULTIPOINT, ALGORITHM = INSTANT;
1444 Table id did not change
1445 count(*) = 4
1446 1
1447 CHECK TABLE t1;
1448 Table Op Msg_type Msg_text
1449 test.t1 check status OK
1450 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f1' AND has_default = 1;
1451 name default_value
1452 f1 NULL
1453 INSERT INTO t1(a, f1) VALUES(0, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)'));
1454 SELECT count(*) = max(a) FROM t1 WHERE f1 IS NULL;
1455 count(*) = max(a)
1456 1
1457 ALTER TABLE t1 ADD COLUMN g1 MULTILINESTRING, ALGORITHM = INSTANT;
1458 Table id did not change
1459 count(*) = 5
1460 1
1461 CHECK TABLE t1;
1462 Table Op Msg_type Msg_text
1463 test.t1 check status OK
1464 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g1' AND has_default = 1;
1465 name default_value
1466 g1 NULL
1467 INSERT INTO t1(a, g1) VALUES(0, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))'));
1468 SELECT count(*) = max(a) FROM t1 WHERE g1 IS NULL;
1469 count(*) = max(a)
1470 1
1471 ALTER TABLE t1 ADD COLUMN h1 MULTIPOLYGON, ALGORITHM = INSTANT;
1472 Table id did not change
1473 count(*) = 6
1474 1
1475 CHECK TABLE t1;
1476 Table Op Msg_type Msg_text
1477 test.t1 check status OK
1478 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h1' AND has_default = 1;
1479 name default_value
1480 h1 NULL
1481 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)))'));
1482 SELECT count(*) = max(a) FROM t1 WHERE h1 IS NULL;
1483 count(*) = max(a)
1484 1
1485 ALTER TABLE t1 ADD COLUMN i1 GEOMETRYCOLLECTION, ALGORITHM = INSTANT;
1486 Table id did not change
1487 count(*) = 7
1488 1
1489 CHECK TABLE t1;
1490 Table Op Msg_type Msg_text
1491 test.t1 check status OK
1492 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'i1' AND has_default = 1;
1493 name default_value
1494 i1 NULL
1495 INSERT INTO t1(a, i1) VALUES(0, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'));
1496 SELECT count(*) = max(a) FROM t1 WHERE i1 IS NULL;
1497 count(*) = max(a)
1498 1
1499 ALTER TABLE t1 ADD COLUMN j1 GEOMETRY, ALGORITHM = INSTANT;
1500 Table id did not change
1501 count(*) = 8
1502 1
1503 CHECK TABLE t1;
1504 Table Op Msg_type Msg_text
1505 test.t1 check status OK
1506 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'j1' AND has_default = 1;
1507 name default_value
1508 j1 NULL
1509 INSERT INTO t1(a, j1) VALUES(0, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'));
1510 SELECT count(*) = max(a) FROM t1 WHERE j1 IS NULL;
1511 count(*) = max(a)
1512 1
1513 CHECK TABLE t1;
1514 Table Op Msg_type Msg_text
1515 test.t1 check status OK
1516 SHOW CREATE TABLE t1;
1517 Table Create Table
1518 t1 CREATE TABLE `t1` (
1519 `a` int NOT NULL AUTO_INCREMENT,
1520 `b` int DEFAULT NULL,
1521 `c1` point DEFAULT NULL,
1522 `d1` linestring DEFAULT NULL,
1523 `e1` polygon DEFAULT NULL,
1524 `f1` multipoint DEFAULT NULL,
1525 `g1` multilinestring DEFAULT NULL,
1526 `h1` multipolygon DEFAULT NULL,
1527 `i1` geomcollection DEFAULT NULL,
1528 `j1` geometry DEFAULT NULL,
1529 PRIMARY KEY (`a`)
1530 ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1531 DROP TABLE t1;
1532 #
1533 # Scenario 15:
1534 # Create a small table, and add JSON columns and verify
1535 #
1536 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
1537 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1538 ALTER TABLE t1 ADD COLUMN c1 JSON, ALGORITHM = INSTANT;
1539 Table id did not change
1540 count(*) = 1
1541 1
1542 CHECK TABLE t1;
1543 Table Op Msg_type Msg_text
1544 test.t1 check status OK
1545 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1546 name default_value
1547 c1 NULL
1548 INSERT INTO t1(a, c1) VALUES(0, '{"key1": "value1", "key2": "value2"}');
1549 SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
1550 count(*) = max(a)
1551 1
1552 CHECK TABLE t1;
1553 Table Op Msg_type Msg_text
1554 test.t1 check status OK
1555 SHOW CREATE TABLE t1;
1556 Table Create Table
1557 t1 CREATE TABLE `t1` (
1558 `a` int NOT NULL AUTO_INCREMENT,
1559 `b` int DEFAULT NULL,
1560 `c1` json DEFAULT NULL,
1561 PRIMARY KEY (`a`)
1562 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1563 DROP TABLE t1;
1564 #
1565 # Scenario 16:
1566 # Create a small table, and add INSTANT columns and verify with trigger
1567 #
1568 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
1569 CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
1570 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1571 INSERT INTO t2 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1572 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
1573 Table id did not change
1574 count(*) = 1
1575 1
1576 CHECK TABLE t1;
1577 Table Op Msg_type Msg_text
1578 test.t1 check status OK
1579 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1580 name default_value
1581 c1 61616161
1582 CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
1583 INSERT INTO t2 VALUES(0,6);
1584 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
1585 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
1586 count(*) = max(a)
1587 1
1588 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
1589 c1
1590 bbbb
1591 DROP TRIGGER t1_ai;
1592 ALTER TABLE t2 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'cccc', ALGORITHM = INSTANT;
1593 Table id did not change
1594 count(*) = 1
1595 1
1596 CHECK TABLE t1;
1597 Table Op Msg_type Msg_text
1598 test.t1 check status OK
1599 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1600 name default_value
1601 c1 61616161
1602 c1 63636363
1603 CREATE TRIGGER t2_ai AFTER INSERT ON t2 FOR EACH ROW
1604 INSERT INTO t1(a,c1) VALUES(0,'eeee');
1605 INSERT INTO t2(a, c1) VALUES(0, 'dddd');
1606 SELECT count(*) = max(a) FROM t2 WHERE c1='cccc';
1607 count(*) = max(a)
1608 1
1609 SELECT c1 FROM t2 WHERE c1 = 'dddd';
1610 c1
1611 dddd
1612 DROP TRIGGER t2_ai;
1613 CHECK TABLE t1;
1614 Table Op Msg_type Msg_text
1615 test.t1 check status OK
1616 CHECK TABLE t2;
1617 Table Op Msg_type Msg_text
1618 test.t2 check status OK
1619 SHOW CREATE TABLE t1;
1620 Table Create Table
1621 t1 CREATE TABLE `t1` (
1622 `a` int NOT NULL AUTO_INCREMENT,
1623 `b` int DEFAULT NULL,
1624 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
1625 PRIMARY KEY (`a`)
1626 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1627 SHOW CREATE TABLE t2;
1628 Table Create Table
1629 t2 CREATE TABLE `t2` (
1630 `a` int NOT NULL AUTO_INCREMENT,
1631 `b` int DEFAULT NULL,
1632 `c1` varchar(10) NOT NULL DEFAULT 'cccc',
1633 PRIMARY KEY (`a`)
1634 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1635 DROP TABLE t1,t2;
1636 #
1637 # Scenario 17:
1638 # Create a small table, and add INSTANT columns and verify with storedprocedure
1639 #
1640 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
1641 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1642 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
1643 Table id did not change
1644 count(*) = 1
1645 1
1646 CHECK TABLE t1;
1647 Table Op Msg_type Msg_text
1648 test.t1 check status OK
1649 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1650 name default_value
1651 c1 61616161
1652 CREATE PROCEDURE p1() INSERT INTO t1(a,c1) VALUES(0, 'bbbb');
1653 CALL p1();
1654 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
1655 count(*) = max(a)
1656 1
1657 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
1658 c1
1659 bbbb
1660 DROP PROCEDURE p1;
1661 CHECK TABLE t1;
1662 Table Op Msg_type Msg_text
1663 test.t1 check status OK
1664 SHOW CREATE TABLE t1;
1665 Table Create Table
1666 t1 CREATE TABLE `t1` (
1667 `a` int NOT NULL AUTO_INCREMENT,
1668 `b` int DEFAULT NULL,
1669 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
1670 PRIMARY KEY (`a`)
1671 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1672 DROP TABLE t1;
1673 #
1674 # Scenario 18:
1675 # Create a small table, and add INSTANT columns and verify with view
1676 #
1677 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
1678 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1679 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
1680 Table id did not change
1681 count(*) = 1
1682 1
1683 CHECK TABLE t1;
1684 Table Op Msg_type Msg_text
1685 test.t1 check status OK
1686 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1687 name default_value
1688 c1 61616161
1689 CREATE VIEW v1 AS SELECT * FROM t1;
1690 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
1691 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
1692 count(*) = max(a)
1693 1
1694 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
1695 c1
1696 bbbb
1697 SELECT * FROM v1;
1698 a b c1
1699 1 1 aaaa
1700 2 2 aaaa
1701 3 3 aaaa
1702 4 4 aaaa
1703 5 5 aaaa
1704 6 NULL bbbb
1705 DROP VIEW v1;
1706 CHECK TABLE t1;
1707 Table Op Msg_type Msg_text
1708 test.t1 check status OK
1709 SHOW CREATE TABLE t1;
1710 Table Create Table
1711 t1 CREATE TABLE `t1` (
1712 `a` int NOT NULL AUTO_INCREMENT,
1713 `b` int DEFAULT NULL,
1714 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
1715 PRIMARY KEY (`a`)
1716 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1717 DROP TABLE t1;
1718 #
1719 # Scenario 19:
1720 # Create a small table, and add INSTANT columns and drop it and verify
1721 #
1722 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
1723 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1724 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
1725 Table id did not change
1726 count(*) = 1
1727 1
1728 CHECK TABLE t1;
1729 Table Op Msg_type Msg_text
1730 test.t1 check status OK
1731 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1732 name default_value
1733 c1 61616161
1734 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
1735 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
1736 count(*) = max(a)
1737 1
1738 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
1739 c1
1740 bbbb
1741 ALTER TABLE t1 DROP COLUMN c1;
1742 Table id did not change
1743 count(*) = 0
1744 1
1745 CHECK TABLE t1;
1746 Table Op Msg_type Msg_text
1747 test.t1 check status OK
1748 CHECK TABLE t1;
1749 Table Op Msg_type Msg_text
1750 test.t1 check status OK
1751 SHOW CREATE TABLE t1;
1752 Table Create Table
1753 t1 CREATE TABLE `t1` (
1754 `a` int NOT NULL AUTO_INCREMENT,
1755 `b` int DEFAULT NULL,
1756 PRIMARY KEY (`a`)
1757 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1758 DROP TABLE t1;
1759 #
1760 # Scenario 20:
1761 # Create a small table, and add INSTANT columns and rename table
1762 #
1763 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
1764 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1765 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
1766 Table id did not change
1767 count(*) = 1
1768 1
1769 CHECK TABLE t1;
1770 Table Op Msg_type Msg_text
1771 test.t1 check status OK
1772 ALTER TABLE t1 RENAME t2;
1773 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1774 name default_value
1775 c1 61616161
1776 INSERT INTO t2(a, c1) VALUES(0, 'bbbb');
1777 SELECT count(*) = max(a) FROM t2 WHERE c1='aaaa';
1778 count(*) = max(a)
1779 1
1780 SELECT c1 FROM t2 WHERE c1 = 'bbbb';
1781 c1
1782 bbbb
1783 CHECK TABLE t2;
1784 Table Op Msg_type Msg_text
1785 test.t2 check status OK
1786 SHOW CREATE TABLE t2;
1787 Table Create Table
1788 t2 CREATE TABLE `t2` (
1789 `a` int NOT NULL AUTO_INCREMENT,
1790 `b` int DEFAULT NULL,
1791 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
1792 PRIMARY KEY (`a`)
1793 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1794 DROP TABLE t2;
1795 #
1796 # Scenario 21:
1797 # Create a small table, and add INSTANT columns and change its data type INSTANTly won't work
1798 #
1799 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
1800 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1801 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
1802 Table id did not change
1803 count(*) = 1
1804 1
1805 CHECK TABLE t1;
1806 Table Op Msg_type Msg_text
1807 test.t1 check status OK
1808 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1809 name default_value
1810 c1 61616161
1811 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
1812 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
1813 count(*) = max(a)
1814 1
1815 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
1816 c1
1817 bbbb
1818 ALTER TABLE t1 CHANGE c1 c2 CHAR(10) NOT NULL DEFAULT 'cccc';
1819 Table ID differed
1820 count(*) = 0
1821 1
1822 CHECK TABLE t1;
1823 Table Op Msg_type Msg_text
1824 test.t1 check status OK
1825 CHECK TABLE t1;
1826 Table Op Msg_type Msg_text
1827 test.t1 check status OK
1828 SHOW CREATE TABLE t1;
1829 Table Create Table
1830 t1 CREATE TABLE `t1` (
1831 `a` int NOT NULL AUTO_INCREMENT,
1832 `b` int DEFAULT NULL,
1833 `c2` char(10) NOT NULL DEFAULT 'cccc',
1834 PRIMARY KEY (`a`)
1835 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1836 DROP TABLE t1;
1837 #
1838 # Scenario 22:
1839 # Create a small table, and add INSTANT columns and create hash,btree multi column index and verify
1840 #
1841 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
1842 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1843 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
1844 Table id did not change
1845 count(*) = 1
1846 1
1847 CHECK TABLE t1;
1848 Table Op Msg_type Msg_text
1849 test.t1 check status OK
1850 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1851 name default_value
1852 c1 61616161
1853 CREATE INDEX id1 ON t1(c1) USING BTREE;
1854 CREATE INDEX id2 ON t1(c1) USING HASH;
1855 Warnings:
1856 Note 3502 This storage engine does not support the HASH index algorithm, storage engine default was used instead.
1857 Warning 1831 Duplicate index 'id2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
1858 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
1859 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
1860 count(*) = max(a)
1861 1
1862 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
1863 c1
1864 bbbb
1865 DROP INDEX id1 ON t1;
1866 DROP INDEX id2 ON t1;
1867 ALTER TABLE t1 ADD COLUMN c2 VARCHAR(10) NOT NULL DEFAULT 'cccc', ALGORITHM = INSTANT;
1868 Table id did not change
1869 count(*) = 2
1870 1
1871 CHECK TABLE t1;
1872 Table Op Msg_type Msg_text
1873 test.t1 check status OK
1874 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c2' AND has_default = 1;
1875 name default_value
1876 c2 63636363
1877 CREATE INDEX id1 ON t1(c1 ASC,c2 DESC) USING BTREE;
1878 INSERT INTO t1(a, c2) VALUES(0, 'dddd');
1879 SELECT count(*) = max(a) FROM t1 WHERE c1='cccc';
1880 count(*) = max(a)
1881 NULL
1882 SELECT c1 FROM t1 WHERE c1 = 'dddd';
1883 c1
1884 ALTER TABLE t1 RENAME INDEX id1 TO id2;
1885 DROP INDEX id2 ON t1;
1886 CHECK TABLE t1;
1887 Table Op Msg_type Msg_text
1888 test.t1 check status OK
1889 SHOW CREATE TABLE t1;
1890 Table Create Table
1891 t1 CREATE TABLE `t1` (
1892 `a` int NOT NULL AUTO_INCREMENT,
1893 `b` int DEFAULT NULL,
1894 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
1895 `c2` varchar(10) NOT NULL DEFAULT 'cccc',
1896 PRIMARY KEY (`a`)
1897 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1898 DROP TABLE t1;
1899 #
1900 # Scenario 23:
1901 # Create a small table, and add INSTANT columns and perform table join operation
1902 #
1903 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
1904 CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT;
1905 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1906 INSERT INTO t2 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
1907 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
1908 Table id did not change
1909 count(*) = 1
1910 1
1911 CHECK TABLE t1;
1912 Table Op Msg_type Msg_text
1913 test.t1 check status OK
1914 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1915 name default_value
1916 c1 61616161
1917 INSERT INTO t1(a, c1) VALUES(0, 'cccc');
1918 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
1919 count(*) = max(a)
1920 1
1921 SELECT c1 FROM t1 WHERE c1 = 'cccc';
1922 c1
1923 cccc
1924 ALTER TABLE t2 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'bbbb', ALGORITHM = INSTANT;
1925 Table id did not change
1926 count(*) = 1
1927 1
1928 CHECK TABLE t1;
1929 Table Op Msg_type Msg_text
1930 test.t1 check status OK
1931 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
1932 name default_value
1933 c1 61616161
1934 c1 62626262
1935 INSERT INTO t2(a, c1) VALUES(0, 'cccc');
1936 SELECT count(*) = max(a) FROM t2 WHERE c1='bbbb';
1937 count(*) = max(a)
1938 1
1939 SELECT c1 FROM t2 WHERE c1 = 'cccc';
1940 c1
1941 cccc
1942 SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1;
1943 a b c1 a b c1
1944 6 NULL cccc 6 NULL cccc
1945 CHECK TABLE t1;
1946 Table Op Msg_type Msg_text
1947 test.t1 check status OK
1948 CHECK TABLE t2;
1949 Table Op Msg_type Msg_text
1950 test.t2 check status OK
1951 SHOW CREATE TABLE t1;
1952 Table Create Table
1953 t1 CREATE TABLE `t1` (
1954 `a` int NOT NULL AUTO_INCREMENT,
1955 `b` int DEFAULT NULL,
1956 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
1957 PRIMARY KEY (`a`)
1958 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1959 SHOW CREATE TABLE t2;
1960 Table Create Table
1961 t2 CREATE TABLE `t2` (
1962 `a` int NOT NULL AUTO_INCREMENT,
1963 `b` int DEFAULT NULL,
1964 `c1` varchar(10) NOT NULL DEFAULT 'bbbb',
1965 PRIMARY KEY (`a`)
1966 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
1967 DROP TABLE t1;
1968 DROP TABLE t2;
1969 #
1970 # Scenario 24:
1971 # Create a small table, and add stored and(or) virtual columns
1972 # after last stored column in the table
1973 #
1974 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=REDUNDANT;
1975 INSERT INTO t1(a, b, c) VALUES(0, 1, 2), (0, 2, 3), (0, 3, 4), (0, 4, 5), (0, 5, 6);
1976 ALTER TABLE t1 ADD COLUMN h INT NOT NULL AFTER c;
1977 Table id did not change
1978 count(*) = 1
1979 1
1980 CHECK TABLE t1;
1981 Table Op Msg_type Msg_text
1982 test.t1 check status OK
1983 INSERT INTO t1(a, b, c, h) VALUES(0, 6, 20, 40);
1984 SELECT * FROM t1;
1985 a b c h d e
1986 1 1 2 0 3 2
1987 2 2 3 0 5 6
1988 3 3 4 0 7 12
1989 4 4 5 0 9 20
1990 5 5 6 0 11 30
1991 6 6 20 40 26 120
1992 ALTER TABLE t1 ADD COLUMN i VARCHAR(100) DEFAULT 'ABCD EFGH' AFTER h, ADD COLUMN f INT GENERATED ALWAYS AS (LENGTH(i)) AFTER i;
1993 Table id did not change
1994 count(*) = 2
1995 1
1996 CHECK TABLE t1;
1997 Table Op Msg_type Msg_text
1998 test.t1 check status OK
1999 INSERT INTO t1(a, b, c, h, i) VALUES(0, 20, 30, 50, 'qwerty');
2000 SELECT * FROM t1;
2001 a b c h i f d e
2002 1 1 2 0 ABCD EFGH 9 3 2
2003 2 2 3 0 ABCD EFGH 9 5 6
2004 3 3 4 0 ABCD EFGH 9 7 12
2005 4 4 5 0 ABCD EFGH 9 9 20
2006 5 5 6 0 ABCD EFGH 9 11 30
2007 6 6 20 40 ABCD EFGH 9 26 120
2008 7 20 30 50 qwerty 6 50 600
2009 CHECK TABLE t1;
2010 Table Op Msg_type Msg_text
2011 test.t1 check status OK
2012 SHOW CREATE TABLE t1;
2013 Table Create Table
2014 t1 CREATE TABLE `t1` (
2015 `a` int NOT NULL AUTO_INCREMENT,
2016 `b` int DEFAULT NULL,
2017 `c` int DEFAULT NULL,
2018 `h` int NOT NULL,
2019 `i` varchar(100) DEFAULT 'ABCD EFGH',
2020 `f` int GENERATED ALWAYS AS (length(`i`)) VIRTUAL,
2021 `d` int GENERATED ALWAYS AS ((`b` + `c`)) VIRTUAL,
2022 `e` int GENERATED ALWAYS AS ((`b` * `c`)) VIRTUAL,
2023 PRIMARY KEY (`a`)
2024 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
2025 DROP TABLE t1;
2026 ############################################
2027 # Test instant ADD COLUMN for DYNAMIC format
2028 ############################################
2029 #
2030 # Scenario 1:
2031 # Create a small table, and add all kinds of new columns and verify
2032 #
2033 SET @saved_innodb_strict_mode=@@SESSION.innodb_strict_mode;
2034 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
2035 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
2036 ALTER TABLE t1 ADD COLUMN c1 INT, ALGORITHM=INSTANT;
2037 Table id did not change
2038 count(*) = 1
2039 1
2040 CHECK TABLE t1;
2041 Table Op Msg_type Msg_text
2042 test.t1 check status OK
2043 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
2044 name default_value
2045 c1 NULL
2046 INSERT INTO t1(a, c1) VALUES(0, 1);
2047 SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
2048 count(*) = max(a)
2049 1
2050 SELECT c1 FROM t1 WHERE c1 = 1;
2051 c1
2052 1
2053 ALTER TABLE t1 ADD COLUMN c2 INT NOT NULL DEFAULT 10;
2054 Table id did not change
2055 count(*) = 2
2056 1
2057 CHECK TABLE t1;
2058 Table Op Msg_type Msg_text
2059 test.t1 check status OK
2060 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c2' AND has_default = 1;
2061 name default_value
2062 c2 8000000a
2063 INSERT INTO t1(a, c2) VALUES(0, 11);
2064 SELECT count(*) = max(a) FROM t1 WHERE c2 = 10;
2065 count(*) = max(a)
2066 1
2067 SELECT c2 FROM t1 WHERE c2 = 11;
2068 c2
2069 11
2070 ALTER TABLE t1 ADD COLUMN d1 BIGINT, ALGORITHM=INSTANT;
2071 Table id did not change
2072 count(*) = 3
2073 1
2074 CHECK TABLE t1;
2075 Table Op Msg_type Msg_text
2076 test.t1 check status OK
2077 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd1' AND has_default = 1;
2078 name default_value
2079 d1 NULL
2080 INSERT INTO t1(a, d1) VALUES(0, 1);
2081 SELECT count(*) = max(a) FROM t1 WHERE d1 IS NULL;
2082 count(*) = max(a)
2083 1
2084 SELECT d1 FROM t1 WHERE d1 = 1;
2085 d1
2086 1
2087 ALTER TABLE t1 ADD COLUMN d2 BIGINT NOT NULL DEFAULT 1234567890;
2088 Table id did not change
2089 count(*) = 4
2090 1
2091 CHECK TABLE t1;
2092 Table Op Msg_type Msg_text
2093 test.t1 check status OK
2094 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd2' AND has_default = 1;
2095 name default_value
2096 d2 80000000499602d2
2097 INSERT INTO t1(a, d2) VALUES(0, 1234);
2098 SELECT count(*) = max(a) FROM t1 WHERE d2 = 1234567890;
2099 count(*) = max(a)
2100 1
2101 SELECT d2 FROM t1 WHERE d2 = 1234;
2102 d2
2103 1234
2104 ALTER TABLE t1 ADD COLUMN e1 SMALLINT, ALGORITHM=INSTANT;
2105 Table id did not change
2106 count(*) = 5
2107 1
2108 CHECK TABLE t1;
2109 Table Op Msg_type Msg_text
2110 test.t1 check status OK
2111 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e1' AND has_default = 1;
2112 name default_value
2113 e1 NULL
2114 INSERT INTO t1(a, e1) VALUES(0, 1);
2115 SELECT count(*) = max(a) FROM t1 WHERE e1 IS NULL;
2116 count(*) = max(a)
2117 1
2118 SELECT e1 FROM t1 WHERE e1 = 1;
2119 e1
2120 1
2121 ALTER TABLE t1 ADD COLUMN e2 SMALLINT NOT NULL DEFAULT 10;
2122 Table id did not change
2123 count(*) = 6
2124 1
2125 CHECK TABLE t1;
2126 Table Op Msg_type Msg_text
2127 test.t1 check status OK
2128 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e2' AND has_default = 1;
2129 name default_value
2130 e2 800a
2131 INSERT INTO t1(a, e2) VALUES(0, 11);
2132 SELECT count(*) = max(a) FROM t1 WHERE e2 = 10;
2133 count(*) = max(a)
2134 1
2135 SELECT e2 FROM t1 WHERE e2 = 11;
2136 e2
2137 11
2138 ALTER TABLE t1 ADD COLUMN f1 TINYINT, ALGORITHM=INSTANT;
2139 Table id did not change
2140 count(*) = 7
2141 1
2142 CHECK TABLE t1;
2143 Table Op Msg_type Msg_text
2144 test.t1 check status OK
2145 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f1' AND has_default = 1;
2146 name default_value
2147 f1 NULL
2148 INSERT INTO t1(a, f1) VALUES(0, 1);
2149 SELECT count(*) = max(a) FROM t1 WHERE f1 IS NULL;
2150 count(*) = max(a)
2151 1
2152 SELECT f1 FROM t1 WHERE f1 = 1;
2153 f1
2154 1
2155 ALTER TABLE t1 ADD COLUMN f2 TINYINT NOT NULL DEFAULT 123;
2156 Table id did not change
2157 count(*) = 8
2158 1
2159 CHECK TABLE t1;
2160 Table Op Msg_type Msg_text
2161 test.t1 check status OK
2162 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f2' AND has_default = 1;
2163 name default_value
2164 f2 fb
2165 INSERT INTO t1(a, f2) VALUES(0, 12);
2166 SELECT count(*) = max(a) FROM t1 WHERE f2 = 123;
2167 count(*) = max(a)
2168 1
2169 SELECT f2 FROM t1 WHERE f2 = 12;
2170 f2
2171 12
2172 ALTER TABLE t1 ADD COLUMN g1 MEDIUMINT, ALGORITHM=INSTANT;
2173 Table id did not change
2174 count(*) = 9
2175 1
2176 CHECK TABLE t1;
2177 Table Op Msg_type Msg_text
2178 test.t1 check status OK
2179 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g1' AND has_default = 1;
2180 name default_value
2181 g1 NULL
2182 INSERT INTO t1(a, g1) VALUES(0, 1);
2183 SELECT count(*) = max(a) FROM t1 WHERE g1 IS NULL;
2184 count(*) = max(a)
2185 1
2186 SELECT g1 FROM t1 WHERE g1 = 1;
2187 g1
2188 1
2189 ALTER TABLE t1 ADD COLUMN g2 MEDIUMINT NOT NULL DEFAULT 12345;
2190 Table id did not change
2191 count(*) = 10
2192 1
2193 CHECK TABLE t1;
2194 Table Op Msg_type Msg_text
2195 test.t1 check status OK
2196 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g2' AND has_default = 1;
2197 name default_value
2198 g2 803039
2199 INSERT INTO t1(a, g2) VALUES(0, 1234);
2200 SELECT count(*) = max(a) FROM t1 WHERE g2 = 12345;
2201 count(*) = max(a)
2202 1
2203 SELECT g2 FROM t1 WHERE g2 = 1234;
2204 g2
2205 1234
2206 ALTER TABLE t1 ADD COLUMN h1 FLOAT, ALGORITHM=INSTANT;
2207 Table id did not change
2208 count(*) = 11
2209 1
2210 CHECK TABLE t1;
2211 Table Op Msg_type Msg_text
2212 test.t1 check status OK
2213 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h1' AND has_default = 1;
2214 name default_value
2215 h1 NULL
2216 INSERT INTO t1(a, h1) VALUES(0, 1.0);
2217 SELECT count(*) = max(a) FROM t1 WHERE h1 IS NULL;
2218 count(*) = max(a)
2219 1
2220 SELECT h1 FROM t1 WHERE h1 = 1;
2221 h1
2222 1
2223 ALTER TABLE t1 ADD COLUMN h2 FLOAT NOT NULL DEFAULT 12.34;
2224 Table id did not change
2225 count(*) = 12
2226 1
2227 CHECK TABLE t1;
2228 Table Op Msg_type Msg_text
2229 test.t1 check status OK
2230 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h2' AND has_default = 1;
2231 name default_value
2232 h2 a4704541
2233 INSERT INTO t1(a, h2) VALUES(0, 1.234);
2234 SELECT count(*) = max(a) FROM t1 WHERE h2 = 12.34;
2235 count(*) = max(a)
2236 NULL
2237 SELECT h2 FROM t1 WHERE h2 = 1.234;
2238 h2
2239 ALTER TABLE t1 ADD COLUMN i1 DECIMAL(5, 2), ADD COLUMN i2 double, ALGORITHM=INSTANT;
2240 Table id did not change
2241 count(*) = 14
2242 1
2243 CHECK TABLE t1;
2244 Table Op Msg_type Msg_text
2245 test.t1 check status OK
2246 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'i1' OR name = 'i2') AND has_default = 1;
2247 name default_value
2248 i1 NULL
2249 i2 NULL
2250 INSERT INTO t1(a, i1, i2) VALUES(0, 10.10, 20.20);
2251 SELECT count(*) = max(a) FROM t1 WHERE i1 IS NULL;
2252 count(*) = max(a)
2253 1
2254 SELECT count(*) = max(a) FROM t1 WHERE i2 IS NULL;
2255 count(*) = max(a)
2256 1
2257 SELECT i1 FROM t1 WHERE i1 = 10.10;
2258 i1
2259 10.10
2260 SELECT i2 FROM t1 WHERE i2 = 20.20;
2261 i2
2262 20.2
2263 ALTER TABLE t1 ADD COLUMN j1 DECIMAL(5, 2) NOT NULL DEFAULT 100.00, ADD COLUMN j2 double NOT NULL DEFAULT 1000.5678;
2264 Table id did not change
2265 count(*) = 16
2266 1
2267 CHECK TABLE t1;
2268 Table Op Msg_type Msg_text
2269 test.t1 check status OK
2270 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'j1' OR name = 'j2') AND has_default = 1;
2271 name default_value
2272 j1 806400
2273 j2 5af5b9da8a448f40
2274 INSERT INTO t1(a, j1, j2) VALUES(0, 90.90, 1000.1234);
2275 SELECT count(*) = max(a) FROM t1 WHERE j1 = 100.00;
2276 count(*) = max(a)
2277 1
2278 SELECT count(*) = max(a) FROM t1 WHERE j2 = 1000.5678;
2279 count(*) = max(a)
2280 1
2281 SELECT j1 FROM t1 WHERE j1 = 90.90;
2282 j1
2283 90.90
2284 SELECT j2 FROM t1 WHERE j2 = 1000.1234;
2285 j2
2286 1000.1234
2287 ALTER TABLE t1 ADD COLUMN k1 BIT(8), ALGORITHM=INSTANT;
2288 Table id did not change
2289 count(*) = 17
2290 1
2291 CHECK TABLE t1;
2292 Table Op Msg_type Msg_text
2293 test.t1 check status OK
2294 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'k1' AND has_default = 1;
2295 name default_value
2296 k1 NULL
2297 INSERT INTO t1(a, k1) VALUES(0, b'010101');
2298 SELECT count(*) = max(a) FROM t1 WHERE k1 IS NULL;
2299 count(*) = max(a)
2300 1
2301 SELECT hex(k1) FROM t1 WHERE k1 = b'010101';
2302 hex(k1)
2303 15
2304 ALTER TABLE t1 ADD COLUMN k2 BIT(8) NOT NULL DEFAULT b'101010';
2305 Table id did not change
2306 count(*) = 18
2307 1
2308 CHECK TABLE t1;
2309 Table Op Msg_type Msg_text
2310 test.t1 check status OK
2311 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'k2' AND has_default = 1;
2312 name default_value
2313 k2 2a
2314 INSERT INTO t1(a, k2) VALUES(0, b'110011');
2315 SELECT count(*) = max(a) FROM t1 WHERE k2 = b'101010';
2316 count(*) = max(a)
2317 1
2318 SELECT hex(k2) FROM t1 WHERE k2 = b'110011';
2319 hex(k2)
2320 33
2321 ALTER TABLE t1 ADD COLUMN l1 CHAR(50), ADD COLUMN l2 VARCHAR(100), ALGORITHM=DEFAULT;
2322 Table id did not change
2323 count(*) = 20
2324 1
2325 CHECK TABLE t1;
2326 Table Op Msg_type Msg_text
2327 test.t1 check status OK
2328 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'l1' OR name = 'l2') AND has_default = 1;
2329 name default_value
2330 l1 NULL
2331 l2 NULL
2332 INSERT INTO t1(a, l1, l2) VALUES(0, 'ABCD EFGH', 'abcdefg hijklmn ');
2333 SELECT count(*) = max(a) FROM t1 WHERE l2 IS NULL;
2334 count(*) = max(a)
2335 1
2336 SELECT count(*) = max(a) FROM t1 WHERE l1 IS NULL;
2337 count(*) = max(a)
2338 1
2339 SELECT l1 FROM t1 WHERE l1 = 'ABCD EFGH';
2340 l1
2341 ABCD EFGH
2342 SELECT l2 FROM t1 WHERE l2 = 'abcdefg hijklmn ';
2343 l2
2344 abcdefg hijklmn
2345 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.';
2346 Table id did not change
2347 count(*) = 22
2348 1
2349 CHECK TABLE t1;
2350 Table Op Msg_type Msg_text
2351 test.t1 check status OK
2352 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'm1' OR name = 'm2') AND has_default = 1;
2353 name default_value
2354 m1 54686520666f78206a756d7073206f7665722020202020202020202020202020202020202020202020202020202020202020
2355 m2 54686520666f78206a756d7073206f76657220746865206c617a7920646f672e
2356 INSERT INTO t1(a, m1, m2) VALUES(0, 'over the lazy dog', 'The lazy dog jumps over the fox.');
2357 SELECT count(*) = max(a) FROM t1 WHERE m1 = 'The fox jumps over';
2358 count(*) = max(a)
2359 1
2360 SELECT count(*) = max(a) FROM t1 WHERE m2 like 'The fox jumps%';
2361 count(*) = max(a)
2362 1
2363 SELECT m1 FROM t1 WHERE m1 = 'over the lazy dog';
2364 m1
2365 over the lazy dog
2366 SELECT m2 FROM t1 WHERE m2 like '%the fox.';
2367 m2
2368 The lazy dog jumps over the fox.
2369 ALTER TABLE t1 ADD COLUMN n1 BINARY(10), ADD COLUMN n2 VARBINARY(10), ALGORITHM=DEFAULT;
2370 Table id did not change
2371 count(*) = 24
2372 1
2373 CHECK TABLE t1;
2374 Table Op Msg_type Msg_text
2375 test.t1 check status OK
2376 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'n1' OR name = 'n2') AND has_default = 1;
2377 name default_value
2378 n1 NULL
2379 n2 NULL
2380 INSERT INTO t1(a, n1, n2) VALUES(0, 0x010203040506070809, 0x102030405060708090);
2381 SELECT count(*) = max(a) FROM t1 WHERE n1 IS NULL;
2382 count(*) = max(a)
2383 1
2384 SELECT count(*) = max(a) FROM t1 WHERE n2 IS NULL;
2385 count(*) = max(a)
2386 1
2387 SELECT hex(n1) FROM t1 WHERE n1 = 0x01020304050607080900;
2388 hex(n1)
2389 01020304050607080900
2390 SELECT hex(n2) FROM t1 WHERE n2 = 0x102030405060708090;
2391 hex(n2)
2392 102030405060708090
2393 ALTER TABLE t1 ADD COLUMN o1 BINARY(10) DEFAULT 0x11223344, ADD COLUMN o2 VARBINARY(10) DEFAULT 0x55667788;
2394 Table id did not change
2395 count(*) = 26
2396 1
2397 CHECK TABLE t1;
2398 Table Op Msg_type Msg_text
2399 test.t1 check status OK
2400 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'o1' OR name = 'o2') AND has_default = 1;
2401 name default_value
2402 o1 11223344000000000000
2403 o2 55667788
2404 INSERT INTO t1(a, o1, o2) VALUES(0, 0x44332211, 0x88776655);
2405 SELECT count(*) = max(a) FROM t1 WHERE o1 = 0x11223344000000000000;
2406 count(*) = max(a)
2407 1
2408 SELECT count(*) = max(a) FROM t1 WHERE o2 = 0x55667788;
2409 count(*) = max(a)
2410 1
2411 SELECT hex(o1) FROM t1 WHERE o1 = 0x44332211000000000000;
2412 hex(o1)
2413 44332211000000000000
2414 SELECT hex(o2) FROM t1 WHERE o2 = 0x88776655;
2415 hex(o2)
2416 88776655
2417 ALTER TABLE t1 ADD COLUMN p1 DATETIME, ALGORITHM=DEFAULT;
2418 Table id did not change
2419 count(*) = 27
2420 1
2421 CHECK TABLE t1;
2422 Table Op Msg_type Msg_text
2423 test.t1 check status OK
2424 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'p1' AND has_default = 1;
2425 name default_value
2426 p1 NULL
2427 INSERT INTO t1(a, p1) VALUES(0, '2017-12-31 00:00:00');
2428 SELECT count(*) = max(a) FROM t1 WHERE p1 IS NULL;
2429 count(*) = max(a)
2430 1
2431 SELECT p1 FROM t1 WHERE p1 = '2017-12-31 00:00:00';
2432 p1
2433 2017-12-31 00:00:00
2434 ALTER TABLE t1 ADD COLUMN p2 DATETIME NOT NULL DEFAULT '2017-12-31 01:02:03';
2435 Table id did not change
2436 count(*) = 28
2437 1
2438 CHECK TABLE t1;
2439 Table Op Msg_type Msg_text
2440 test.t1 check status OK
2441 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'p2' AND has_default = 1;
2442 name default_value
2443 p2 999e7e1083
2444 SELECT count(*) = max(a) FROM t1 GROUP BY p2;
2445 count(*) = max(a)
2446 1
2447 INSERT INTO t1(a, p2) VALUES(0, now());
2448 SELECT count(*) FROM t1 GROUP BY p2;
2449 count(*)
2450 26
2451 1
2452 ALTER TABLE t1 ADD COLUMN q1 ENUM ('value1','value2','value3'), ALGORITHM=INSTANT;
2453 Table id did not change
2454 count(*) = 29
2455 1
2456 CHECK TABLE t1;
2457 Table Op Msg_type Msg_text
2458 test.t1 check status OK
2459 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'q1' AND has_default = 1;
2460 name default_value
2461 q1 NULL
2462 INSERT INTO t1(a, q1) VALUES(0, 1);
2463 SELECT count(*) = max(a) FROM t1 WHERE q1 IS NULL;
2464 count(*) = max(a)
2465 1
2466 SELECT q1 FROM t1 WHERE q1 = 1;
2467 q1
2468 value1
2469 ALTER TABLE t1 ADD COLUMN r1 SET ('a','b','c'), ALGORITHM=INSTANT;
2470 Table id did not change
2471 count(*) = 30
2472 1
2473 CHECK TABLE t1;
2474 Table Op Msg_type Msg_text
2475 test.t1 check status OK
2476 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'r1' AND has_default = 1;
2477 name default_value
2478 r1 NULL
2479 INSERT INTO t1(a, r1) VALUES(0, 'a');
2480 SELECT count(*) = max(a) FROM t1 WHERE r1 IS NULL;
2481 count(*) = max(a)
2482 1
2483 SELECT r1 FROM t1 WHERE r1 = 'a';
2484 r1
2485 a
2486 ALTER TABLE t1 ADD COLUMN s1 BLOB, ADD COLUMN s2 TEXT, ALGORITHM=INSTANT;
2487 Table id did not change
2488 count(*) = 32
2489 1
2490 CHECK TABLE t1;
2491 Table Op Msg_type Msg_text
2492 test.t1 check status OK
2493 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 's1' OR name = 's2') AND has_default = 1;
2494 name default_value
2495 s1 NULL
2496 s2 NULL
2497 INSERT INTO t1(a, s1, s2) VALUES(0, 0x0102030405, 'abcd qwerty');
2498 SELECT count(*) = max(a) FROM t1 WHERE s1 IS NULL;
2499 count(*) = max(a)
2500 1
2501 SELECT count(*) = max(a) FROM t1 WHERE s2 IS NULL;
2502 count(*) = max(a)
2503 1
2504 SELECT hex(s1) FROM t1 WHERE s1 = 0x0102030405;
2505 hex(s1)
2506 0102030405
2507 SELECT s2 FROM t1 WHERE s2 = 'abcd qwerty';
2508 s2
2509 abcd qwerty
2510 ALTER TABLE t1 ADD COLUMN u1 BLOB NOT NULL, ADD COLUMN u2 TEXT NOT NULL;
2511 Table id did not change
2512 count(*) = 34
2513 1
2514 CHECK TABLE t1;
2515 Table Op Msg_type Msg_text
2516 test.t1 check status OK
2517 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'u1' OR name = 'u2') AND has_default = 1;
2518 name default_value
2519 u1
2520 u2
2521 INSERT INTO t1(a, u1, u2) VALUES(0, 0x0102030405, 'abcd qwerty');
2522 SELECT count(*) = max(a) FROM t1 WHERE u1 = '';
2523 count(*) = max(a)
2524 1
2525 SELECT count(*) = max(a) FROM t1 WHERE u2 = '';
2526 count(*) = max(a)
2527 1
2528 SELECT hex(u1) FROM t1 WHERE u1 = 0x0102030405;
2529 hex(u1)
2530 0102030405
2531 SELECT u2 FROM t1 WHERE u2 = 'abcd qwerty';
2532 u2
2533 abcd qwerty
2534 CHECK TABLE t1;
2535 Table Op Msg_type Msg_text
2536 test.t1 check status OK
2537 SHOW CREATE TABLE t1;
2538 Table Create Table
2539 t1 CREATE TABLE `t1` (
2540 `a` int NOT NULL AUTO_INCREMENT,
2541 `b` int DEFAULT NULL,
2542 `c1` int DEFAULT NULL,
2543 `c2` int NOT NULL DEFAULT '10',
2544 `d1` bigint DEFAULT NULL,
2545 `d2` bigint NOT NULL DEFAULT '1234567890',
2546 `e1` smallint DEFAULT NULL,
2547 `e2` smallint NOT NULL DEFAULT '10',
2548 `f1` tinyint DEFAULT NULL,
2549 `f2` tinyint NOT NULL DEFAULT '123',
2550 `g1` mediumint DEFAULT NULL,
2551 `g2` mediumint NOT NULL DEFAULT '12345',
2552 `h1` float DEFAULT NULL,
2553 `h2` float NOT NULL DEFAULT '12.34',
2554 `i1` decimal(5,2) DEFAULT NULL,
2555 `i2` double DEFAULT NULL,
2556 `j1` decimal(5,2) NOT NULL DEFAULT '100.00',
2557 `j2` double NOT NULL DEFAULT '1000.5678',
2558 `k1` bit(8) DEFAULT NULL,
2559 `k2` bit(8) NOT NULL DEFAULT b'101010',
2560 `l1` char(50) DEFAULT NULL,
2561 `l2` varchar(100) DEFAULT NULL,
2562 `m1` char(50) DEFAULT 'The fox jumps over',
2563 `m2` varchar(50) DEFAULT 'The fox jumps over the lazy dog.',
2564 `n1` binary(10) DEFAULT NULL,
2565 `n2` varbinary(10) DEFAULT NULL,
2566 `o1` binary(10) DEFAULT '"3D\0\0\0\0\0\0',
2567 `o2` varbinary(10) DEFAULT 0x55667788,
2568 `p1` datetime DEFAULT NULL,
2569 `p2` datetime NOT NULL DEFAULT '2017-12-31 01:02:03',
2570 `q1` enum('value1','value2','value3') DEFAULT NULL,
2571 `r1` set('a','b','c') DEFAULT NULL,
2572 `s1` blob,
2573 `s2` text,
2574 `u1` blob NOT NULL,
2575 `u2` text NOT NULL,
2576 PRIMARY KEY (`a`)
2577 ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
2578 DROP TABLE t1;
2579 #
2580 # Scenario 2:
2581 # Create a small table, add some columns instantly, along with
2582 # virtual columns
2583 #
2584 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
2585 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
2586 ALTER TABLE t1 ADD COLUMN c INT NOT NULL, ADD COLUMN d INT GENERATED ALWAYS AS ((b * 2)) VIRTUAL;
2587 Table id did not change
2588 count(*) = 1
2589 1
2590 CHECK TABLE t1;
2591 Table Op Msg_type Msg_text
2592 test.t1 check status OK
2593 INSERT INTO t1(a, b, c) VALUES(0, 6, 20);
2594 SELECT * FROM t1;
2595 a b c d
2596 1 1 0 2
2597 2 2 0 4
2598 3 3 0 6
2599 4 4 0 8
2600 5 5 0 10
2601 6 6 20 12
2602 ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'ABCD EFGH', ADD COLUMN f INT GENERATED ALWAYS AS (LENGTH(e));
2603 Table id did not change
2604 count(*) = 2
2605 1
2606 CHECK TABLE t1;
2607 Table Op Msg_type Msg_text
2608 test.t1 check status OK
2609 INSERT INTO t1(a, c, e) VALUES(0, 20, 'Hello'), (0, 20, 'World'), (0, 20, 'Hello World');
2610 SELECT * FROM t1;
2611 a b c d e f
2612 1 1 0 2 ABCD EFGH 9
2613 2 2 0 4 ABCD EFGH 9
2614 3 3 0 6 ABCD EFGH 9
2615 4 4 0 8 ABCD EFGH 9
2616 5 5 0 10 ABCD EFGH 9
2617 6 6 20 12 ABCD EFGH 9
2618 7 NULL 20 NULL Hello 5
2619 8 NULL 20 NULL World 5
2620 9 NULL 20 NULL Hello World 11
2621 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);
2622 Table id did not change
2623 count(*) = 3
2624 1
2625 CHECK TABLE t1;
2626 Table Op Msg_type Msg_text
2627 test.t1 check status OK
2628 SELECT * FROM t1;
2629 a b c d e f g h i
2630 1 1 0 2 ABCD EFGH 9 ABCD EFGH 10000 20001
2631 2 2 0 4 ABCD EFGH 9 ABCD EFGH 10000 20002
2632 3 3 0 6 ABCD EFGH 9 ABCD EFGH 10000 20003
2633 4 4 0 8 ABCD EFGH 9 ABCD EFGH 10000 20004
2634 5 5 0 10 ABCD EFGH 9 ABCD EFGH 10000 20005
2635 6 6 20 12 ABCD EFGH 9 ABCD EFGH 10000 20006
2636 7 NULL 20 NULL Hello 5 Hello 10000 NULL
2637 8 NULL 20 NULL World 5 World 10000 NULL
2638 9 NULL 20 NULL Hello World 11 Hello World 10000 NULL
2639 INSERT INTO t1(a, b, c, h) VALUES(0, 7, 40, 2000), (0, 7, 40, 20000);
2640 SELECT * FROM t1;
2641 a b c d e f g h i
2642 1 1 0 2 ABCD EFGH 9 ABCD EFGH 10000 20001
2643 10 7 40 14 ABCD EFGH 9 ABCD EFGH 2000 4007
2644 11 7 40 14 ABCD EFGH 9 ABCD EFGH 20000 40007
2645 2 2 0 4 ABCD EFGH 9 ABCD EFGH 10000 20002
2646 3 3 0 6 ABCD EFGH 9 ABCD EFGH 10000 20003
2647 4 4 0 8 ABCD EFGH 9 ABCD EFGH 10000 20004
2648 5 5 0 10 ABCD EFGH 9 ABCD EFGH 10000 20005
2649 6 6 20 12 ABCD EFGH 9 ABCD EFGH 10000 20006
2650 7 NULL 20 NULL Hello 5 Hello 10000 NULL
2651 8 NULL 20 NULL World 5 World 10000 NULL
2652 9 NULL 20 NULL Hello World 11 Hello World 10000 NULL
2653 CHECK TABLE t1;
2654 Table Op Msg_type Msg_text
2655 test.t1 check status OK
2656 SHOW CREATE TABLE t1;
2657 Table Create Table
2658 t1 CREATE TABLE `t1` (
2659 `a` int NOT NULL AUTO_INCREMENT,
2660 `b` int DEFAULT NULL,
2661 `c` int NOT NULL,
2662 `d` int GENERATED ALWAYS AS ((`b` * 2)) VIRTUAL,
2663 `e` varchar(100) DEFAULT 'ABCD EFGH',
2664 `f` int GENERATED ALWAYS AS (length(`e`)) VIRTUAL,
2665 `g` varchar(100) GENERATED ALWAYS AS (`e`) VIRTUAL,
2666 `h` bigint DEFAULT '10000',
2667 `i` bigint GENERATED ALWAYS AS (((`h` * 2) + `b`)) VIRTUAL,
2668 PRIMARY KEY (`a`)
2669 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
2670 DROP TABLE t1;
2671 #
2672 # Scenario 3:
2673 # Create a small table, add some columns instantly, then change
2674 # their default values, check original default values are correct
2675 #
2676 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
2677 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
2678 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';
2679 Table id did not change
2680 count(*) = 2
2681 1
2682 CHECK TABLE t1;
2683 Table Op Msg_type Msg_text
2684 test.t1 check status OK
2685 SELECT * FROM t1;
2686 a b c d e
2687 1 1 100 201 Hello world
2688 2 2 100 202 Hello world
2689 3 3 100 203 Hello world
2690 4 4 100 204 Hello world
2691 5 5 100 205 Hello world
2692 INSERT INTO t1(a, b, c, e) VALUES(0, 6, 200, 'Good day'), (0, 7, 300, 'Good DAY');
2693 SELECT * FROM t1;
2694 a b c d e
2695 1 1 100 201 Hello world
2696 2 2 100 202 Hello world
2697 3 3 100 203 Hello world
2698 4 4 100 204 Hello world
2699 5 5 100 205 Hello world
2700 6 6 200 406 Good day
2701 7 7 300 607 Good DAY
2702 SELECT default_value FROM information_schema.innodb_columns WHERE name = 'c' AND has_default = 1;
2703 default_value
2704 80000064
2705 ALTER TABLE t1 ALTER COLUMN c SET DEFAULT 500;
2706 SELECT default_value FROM information_schema.innodb_columns WHERE name = 'c' AND
2707 has_default = 1;
2708 default_value
2709 80000064
2710 SELECT * FROM t1;
2711 a b c d e
2712 1 1 100 201 Hello world
2713 2 2 100 202 Hello world
2714 3 3 100 203 Hello world
2715 4 4 100 204 Hello world
2716 5 5 100 205 Hello world
2717 6 6 200 406 Good day
2718 7 7 300 607 Good DAY
2719 INSERT INTO t1(a, b) VALUES(0, 8), (0, 9);
2720 SELECT * FROM t1;
2721 a b c d e
2722 1 1 100 201 Hello world
2723 2 2 100 202 Hello world
2724 3 3 100 203 Hello world
2725 4 4 100 204 Hello world
2726 5 5 100 205 Hello world
2727 6 6 200 406 Good day
2728 7 7 300 607 Good DAY
2729 8 8 500 1008 Hello world
2730 9 9 500 1009 Hello world
2731 SELECT default_value FROM information_schema.innodb_columns WHERE name = 'e' AND has_default = 1;
2732 default_value
2733 48656c6c6f20776f726c64
2734 ALTER TABLE t1 ALTER COLUMN e SET DEFAULT 'HELLO MySQL!';
2735 SELECT default_value FROM information_schema.innodb_columns WHERE name = 'e' AND has_default = 1;
2736 default_value
2737 48656c6c6f20776f726c64
2738 SELECT * FROM t1;
2739 a b c d e
2740 1 1 100 201 Hello world
2741 2 2 100 202 Hello world
2742 3 3 100 203 Hello world
2743 4 4 100 204 Hello world
2744 5 5 100 205 Hello world
2745 6 6 200 406 Good day
2746 7 7 300 607 Good DAY
2747 8 8 500 1008 Hello world
2748 9 9 500 1009 Hello world
2749 INSERT INTO t1(a, b) VALUES(0, 10), (0, 20);
2750 SELECT * FROM t1;
2751 a b c d e
2752 1 1 100 201 Hello world
2753 10 10 500 1010 HELLO MySQL!
2754 11 20 500 1020 HELLO MySQL!
2755 2 2 100 202 Hello world
2756 3 3 100 203 Hello world
2757 4 4 100 204 Hello world
2758 5 5 100 205 Hello world
2759 6 6 200 406 Good day
2760 7 7 300 607 Good DAY
2761 8 8 500 1008 Hello world
2762 9 9 500 1009 Hello world
2763 CHECK TABLE t1;
2764 Table Op Msg_type Msg_text
2765 test.t1 check status OK
2766 SHOW CREATE TABLE t1;
2767 Table Create Table
2768 t1 CREATE TABLE `t1` (
2769 `a` int NOT NULL AUTO_INCREMENT,
2770 `b` int DEFAULT NULL,
2771 `c` int NOT NULL DEFAULT '500',
2772 `d` int GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
2773 `e` varchar(100) DEFAULT 'HELLO MySQL!',
2774 PRIMARY KEY (`a`)
2775 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
2776 DROP TABLE t1;
2777 #
2778 # Scenario 4:
2779 # Create a small table, add some columns instantly, then do DML
2780 # on the table
2781 #
2782 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
2783 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
2784 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';
2785 Table id did not change
2786 count(*) = 2
2787 1
2788 CHECK TABLE t1;
2789 Table Op Msg_type Msg_text
2790 test.t1 check status OK
2791 UPDATE t1 SET c = 200 WHERE a > 3;
2792 SELECT distinct(c) FROM t1;
2793 c
2794 100
2795 200
2796 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
2797 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
2798 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
2799 DELETE FROM t1 WHERE c = 100;
2800 UPDATE t1 SET c = 300;
2801 SELECT distinct(c) FROM t1;
2802 c
2803 300
2804 SELECT count(*) FROM t1;
2805 count(*)
2806 16
2807 ALTER TABLE t1 ADD COLUMN t DATETIME DEFAULT CURRENT_TIMESTAMP;
2808 Table id did not change
2809 count(*) = 3
2810 1
2811 CHECK TABLE t1;
2812 Table Op Msg_type Msg_text
2813 test.t1 check status OK
2814 UPDATE t1 SET e = 'Hello MySQL' WHERE a > 10;
2815 UPDATE t1 SET e = 'Hello MySQL!!' WHERE a > 20;
2816 SELECT distinct(e) FROM t1;
2817 e
2818 Hello MySQL
2819 Hello MySQL!!
2820 Hello world
2821 UPDATE t1 SET c = 500 WHERE e LIKE '%world%';
2822 SELECT c, e FROM t1 GROUP BY c, e;
2823 c e
2824 300 Hello MySQL
2825 300 Hello MySQL!!
2826 500 Hello world
2827 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
2828 UPDATE t1 SET t = CURRENT_TIMESTAMP WHERE a < 50;
2829 SELECT count(t) FROM t1 GROUP BY t;
2830 count(t)
2831 16
2832 16
2833 DELETE FROM t1 WHERE a < 50;
2834 SELECT count(t) FROM t1 GROUP BY t;
2835 count(t)
2836 16
2837 CHECK TABLE t1;
2838 Table Op Msg_type Msg_text
2839 test.t1 check status OK
2840 SHOW CREATE TABLE t1;
2841 Table Create Table
2842 t1 CREATE TABLE `t1` (
2843 `a` int NOT NULL AUTO_INCREMENT,
2844 `b` int DEFAULT NULL,
2845 `c` int NOT NULL DEFAULT '100',
2846 `d` int GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
2847 `e` varchar(100) DEFAULT 'Hello world',
2848 `t` datetime DEFAULT CURRENT_TIMESTAMP,
2849 PRIMARY KEY (`a`)
2850 ) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
2851 DROP TABLE t1;
2852 #
2853 # Scenario 5:
2854 # Create a small table, add some columns instantly, then do DDL
2855 # to build indexes
2856 #
2857 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
2858 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
2859 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';
2860 Table id did not change
2861 count(*) = 2
2862 1
2863 CHECK TABLE t1;
2864 Table Op Msg_type Msg_text
2865 test.t1 check status OK
2866 ALTER TABLE t1 ADD KEY(c);
2867 EXPLAIN SELECT c FROM t1;
2868 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2869 1 SIMPLE t1 NULL index NULL c # NULL # 100.00 Using index
2870 Warnings:
2871 Note 1003 /* select#1 */ select `test`.`t1`.`c` AS `c` from `test`.`t1`
2872 SELECT c FROM t1 WHERE c != 100;
2873 c
2874 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
2875 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
2876 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
2877 UPDATE t1 SET e = 'Hello MySQL' WHERE a > 30;
2878 ALTER TABLE t1 ADD KEY(e);
2879 EXPLAIN SELECT e FROM t1;
2880 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
2881 1 SIMPLE t1 NULL index NULL e # NULL # 100.00 Using index
2882 Warnings:
2883 Note 1003 /* select#1 */ select `test`.`t1`.`e` AS `e` from `test`.`t1`
2884 SELECT count(e) FROM t1 WHERE e LIKE '%MySQL%';
2885 count(e)
2886 17
2887 SELECT count(e) FROM t1 WHERE e LIKE '%world%';
2888 count(e)
2889 23
2890 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a, c);
2891 SELECT a, c, e FROM t1 WHERE a > 25 AND a < 40;
2892 a c e
2893 28 100 Hello world
2894 29 100 Hello world
2895 30 100 Hello world
2896 31 100 Hello MySQL
2897 32 100 Hello MySQL
2898 33 100 Hello MySQL
2899 34 100 Hello MySQL
2900 35 100 Hello MySQL
2901 36 100 Hello MySQL
2902 37 100 Hello MySQL
2903 38 100 Hello MySQL
2904 39 100 Hello MySQL
2905 CHECK TABLE t1;
2906 Table Op Msg_type Msg_text
2907 test.t1 check status OK
2908 SHOW CREATE TABLE t1;
2909 Table Create Table
2910 t1 CREATE TABLE `t1` (
2911 `a` int NOT NULL AUTO_INCREMENT,
2912 `b` int DEFAULT NULL,
2913 `c` int NOT NULL DEFAULT '100',
2914 `d` int GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
2915 `e` varchar(100) DEFAULT 'Hello world',
2916 PRIMARY KEY (`a`,`c`),
2917 KEY `c` (`c`),
2918 KEY `e` (`e`)
2919 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
2920 DROP TABLE t1;
2921 #
2922 # Scenario 6:
2923 # Create a small table, add some columns instantly, then do DML
2924 # on the table, and some simple rollback
2925 #
2926 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
2927 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
2928 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';
2929 Table id did not change
2930 count(*) = 2
2931 1
2932 CHECK TABLE t1;
2933 Table Op Msg_type Msg_text
2934 test.t1 check status OK
2935 START TRANSACTION;
2936 INSERT INTO t1(a, b) VALUES(0, 6);
2937 SELECT * FROM t1;
2938 a b c d e
2939 1 1 100 201 Hello world
2940 2 2 100 202 Hello world
2941 3 3 100 203 Hello world
2942 4 4 100 204 Hello world
2943 5 5 100 205 Hello world
2944 6 6 100 206 Hello world
2945 ROLLBACK;
2946 SELECT * FROM t1;
2947 a b c d e
2948 1 1 100 201 Hello world
2949 2 2 100 202 Hello world
2950 3 3 100 203 Hello world
2951 4 4 100 204 Hello world
2952 5 5 100 205 Hello world
2953 START TRANSACTION;
2954 UPDATE t1 SET c = 500 WHERE a = 1;
2955 UPDATE t1 SET b = 1000 WHERE a = 2;
2956 SELECT a, b, c FROM t1 WHERE a = 1 OR a = 2;
2957 a b c
2958 1 1 500
2959 2 1000 100
2960 ROLLBACK;
2961 SELECT a, b, c FROM t1;
2962 a b c
2963 1 1 100
2964 2 2 100
2965 3 3 100
2966 4 4 100
2967 5 5 100
2968 START TRANSACTION;
2969 DELETE FROM t1 WHERE a < 5;
2970 INSERT INTO t1(a, b) VALUES(0, 6);
2971 SELECT * FROM t1;
2972 a b c d e
2973 5 5 100 205 Hello world
2974 7 6 100 206 Hello world
2975 ROLLBACK;
2976 SELECT * FROM t1;
2977 a b c d e
2978 1 1 100 201 Hello world
2979 2 2 100 202 Hello world
2980 3 3 100 203 Hello world
2981 4 4 100 204 Hello world
2982 5 5 100 205 Hello world
2983 CHECK TABLE t1;
2984 Table Op Msg_type Msg_text
2985 test.t1 check status OK
2986 SHOW CREATE TABLE t1;
2987 Table Create Table
2988 t1 CREATE TABLE `t1` (
2989 `a` int NOT NULL AUTO_INCREMENT,
2990 `b` int DEFAULT NULL,
2991 `c` int NOT NULL DEFAULT '100',
2992 `d` int GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
2993 `e` varchar(100) DEFAULT 'Hello world',
2994 PRIMARY KEY (`a`)
2995 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
2996 DROP TABLE t1;
2997 SET SESSION innodb_strict_mode=OFF;
2998 CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=DYNAMIC;
2999 SET SESSION innodb_strict_mode=@saved_innodb_strict_mode;
3000 INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1));
3001 SELECT id, length(c1), length(c2), length(c3) FROM t1;
3002 id length(c1) length(c2) length(c3)
3003 1 4000 4000 1
3004 ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd';
3005 Table id did not change
3006 count(*) = 1
3007 1
3008 CHECK TABLE t1;
3009 Table Op Msg_type Msg_text
3010 test.t1 check status OK
3011 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
3012 id length(c1) length(c2) length(c3) length(c4)
3013 1 4000 4000 1 500
3014 START TRANSACTION;
3015 UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
3016 ROLLBACK;
3017 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
3018 id length(c1) length(c2) length(c3) length(c4)
3019 1 4000 4000 1 500
3020 START TRANSACTION;
3021 UPDATE t1 SET c4 = 'x' WHERE id = 1;
3022 ROLLBACK;
3023 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
3024 id length(c1) length(c2) length(c3) length(c4)
3025 1 4000 4000 1 500
3026 DROP TABLE t1;
3027 #
3028 # Scenario 7:
3029 # Confirm some ADD COLUMN are instant, some are not
3030 #
3031 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
3032 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
3033 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100 AFTER b;
3034 Table id did not change
3035 count(*) = 1
3036 1
3037 CHECK TABLE t1;
3038 Table Op Msg_type Msg_text
3039 test.t1 check status OK
3040 ALTER TABLE t1 ADD COLUMN d INT NOT NULL DEFAULT 100 AFTER b;
3041 Table id did not change
3042 count(*) = 2
3043 1
3044 CHECK TABLE t1;
3045 Table Op Msg_type Msg_text
3046 test.t1 check status OK
3047 SELECT count(*) AS `Expect 2` FROM information_schema.innodb_columns WHERE has_default = 1;
3048 Expect 2
3049 2
3050 ALTER TABLE t1 ADD COLUMN e INT NOT NULL DEFAULT 100, ADD KEY(e);
3051 Table ID differed
3052 count(*) = 0
3053 1
3054 CHECK TABLE t1;
3055 Table Op Msg_type Msg_text
3056 test.t1 check status OK
3057 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
3058 Expect 0
3059 0
3060 ALTER TABLE t1 ADD COLUMN f INT NOT NULL DEFAULT 100, FORCE;
3061 Table ID differed
3062 count(*) = 0
3063 1
3064 CHECK TABLE t1;
3065 Table Op Msg_type Msg_text
3066 test.t1 check status OK
3067 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
3068 Expect 0
3069 0
3070 ALTER TABLE t1 ADD COLUMN g INT NOT NULL DEFAULT 100, ALGORITHM=INPLACE;
3071 Table ID differed
3072 count(*) = 0
3073 1
3074 CHECK TABLE t1;
3075 Table Op Msg_type Msg_text
3076 test.t1 check status OK
3077 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
3078 Expect 0
3079 0
3080 CHECK TABLE t1;
3081 Table Op Msg_type Msg_text
3082 test.t1 check status OK
3083 SHOW CREATE TABLE t1;
3084 Table Create Table
3085 t1 CREATE TABLE `t1` (
3086 `a` int NOT NULL AUTO_INCREMENT,
3087 `b` int DEFAULT NULL,
3088 `d` int NOT NULL DEFAULT '100',
3089 `c` int NOT NULL DEFAULT '100',
3090 `e` int NOT NULL DEFAULT '100',
3091 `f` int NOT NULL DEFAULT '100',
3092 `g` int NOT NULL DEFAULT '100',
3093 PRIMARY KEY (`a`),
3094 KEY `e` (`e`)
3095 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3096 DROP TABLE t1;
3097 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c TEXT, FULLTEXT(c)) ROW_FORMAT=DYNAMIC;
3098 INSERT INTO t1 VALUES(0, 1, 'Hello'), (0, 2, 'HELLO'), (0, 3, 'World'), (0, 4, 'Hello world'), (0, 5, 'HELLO WORLD');
3099 ALTER TABLE t1 ADD COLUMN d INT NOT NULL DEFAULT 100 AFTER b;
3100 Table ID differed
3101 count(*) = 0
3102 1
3103 CHECK TABLE t1;
3104 Table Op Msg_type Msg_text
3105 test.t1 check status OK
3106 DROP TABLE t1;
3107 #
3108 # Scenario 8:
3109 # Check FK constraints on instantly added columns
3110 #
3111 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
3112 CREATE TABLE t2(a INT NOT NULL PRIMARY KEY, b INT, c INT, KEY(c));
3113 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
3114 INSERT INTO t2 VALUES(1, 2, 3), (2, 3, 4), (3, 4, 5);
3115 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 3;
3116 Table id did not change
3117 count(*) = 1
3118 1
3119 CHECK TABLE t1;
3120 Table Op Msg_type Msg_text
3121 test.t1 check status OK
3122 UPDATE t1 SET c = 4 WHERE a = 2;
3123 UPDATE t1 SET c = 5 WHERE a = 3;
3124 ALTER TABLE t1 ADD KEY(c);
3125 ALTER TABLE t2 ADD FOREIGN KEY (c) REFERENCES t1(c) ON UPDATE CASCADE, ALGORITHM = COPY;
3126 UPDATE t1 SET c = 10 WHERE a = 1;
3127 SELECT c FROM t2;
3128 c
3129 10
3130 4
3131 5
3132 UPDATE t1 SET c = 10;
3133 SELECT c FROM t2;
3134 c
3135 10
3136 10
3137 10
3138 CHECK TABLE t1;
3139 Table Op Msg_type Msg_text
3140 test.t1 check status OK
3141 SHOW CREATE TABLE t1;
3142 Table Create Table
3143 t1 CREATE TABLE `t1` (
3144 `a` int NOT NULL AUTO_INCREMENT,
3145 `b` int DEFAULT NULL,
3146 `c` int NOT NULL DEFAULT '3',
3147 PRIMARY KEY (`a`),
3148 KEY `c` (`c`)
3149 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3150 DROP TABLE t2;
3151 DROP TABLE t1;
3152 #
3153 # Scenario 9:
3154 # Instant ADD COLUMN on partitioned table, only simple test here
3155 #
3156 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC PARTITION BY HASH(a) PARTITIONS 3;;
3157 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
3158 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
3159 Table id did not change
3160 count(*) = 1
3161 1
3162 CHECK TABLE t1;
3163 Table Op Msg_type Msg_text
3164 test.t1 check status OK
3165 SELECT * FROM t1;
3166 a b c
3167 1 1 5
3168 2 2 5
3169 3 3 5
3170 4 4 5
3171 5 5 5
3172 6 6 5
3173 7 7 5
3174 8 8 5
3175 INSERT INTO t1 VALUES(0, 9, 10), (0, 10, 20);
3176 SELECT * FROM t1 WHERE b > 8;
3177 a b c
3178 10 10 20
3179 9 9 10
3180 UPDATE t1 SET c = 8 WHERE a = 1 OR a = 3 OR a = 5 OR a = 7;
3181 SELECT * FROM t1;
3182 a b c
3183 1 1 8
3184 10 10 20
3185 2 2 5
3186 3 3 8
3187 4 4 5
3188 5 5 8
3189 6 6 5
3190 7 7 8
3191 8 8 5
3192 9 9 10
3193 CHECK TABLE t1;
3194 Table Op Msg_type Msg_text
3195 test.t1 check status OK
3196 SHOW CREATE TABLE t1;
3197 Table Create Table
3198 t1 CREATE TABLE `t1` (
3199 `a` int NOT NULL AUTO_INCREMENT,
3200 `b` int DEFAULT NULL,
3201 `c` int NOT NULL DEFAULT '5',
3202 PRIMARY KEY (`a`)
3203 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3204 /*!50100 PARTITION BY HASH (`a`)
3205 PARTITIONS 3 */
3206 DROP TABLE t1;
3207 CREATE TABLE t1 (a INT, b INT) ROW_FORMAT=DYNAMIC PARTITION BY HASH(a) PARTITIONS 2;;
3208 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
3209 Table id did not change
3210 count(*) = 1
3211 1
3212 CHECK TABLE t1;
3213 Table Op Msg_type Msg_text
3214 test.t1 check status OK
3215 ALTER TABLE t1 CHANGE COLUMN c c1 INT;
3216 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
3217 Table id did not change
3218 count(*) = 1
3219 1
3220 CHECK TABLE t1;
3221 Table Op Msg_type Msg_text
3222 test.t1 check status OK
3223 DROP TABLE t1;
3224 #
3225 # Scenario 10:
3226 # EXCHANGE PARTITION is not allowed if either is instant
3227 #
3228 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;;
3229 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
3230 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
3231 Table id did not change
3232 count(*) = 1
3233 1
3234 CHECK TABLE t1;
3235 Table Op Msg_type Msg_text
3236 test.t1 check status OK
3237 CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c INT NOT NULL DEFAULT 5) ROW_FORMAT=DYNAMIC PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30));;
3238 ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
3239 ERROR HY000: Non matching attribute 'INSTANT COLUMN(s)' between partition and table
3240 ALTER TABLE t2 ADD COLUMN d INT;
3241 ALTER TABLE t1 ADD COLUMN d INT, ALGORITHM=INPLACE;
3242 ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
3243 ERROR HY000: Non matching attribute 'INSTANT COLUMN(s)' between partition and table
3244 OPTIMIZE TABLE t2;
3245 Table Op Msg_type Msg_text
3246 test.t2 optimize note Table does not support optimize, doing recreate + analyze instead
3247 test.t2 optimize status OK
3248 SHOW CREATE TABLE t1;
3249 Table Create Table
3250 t1 CREATE TABLE `t1` (
3251 `a` int NOT NULL AUTO_INCREMENT,
3252 `b` int DEFAULT NULL,
3253 `c` int NOT NULL DEFAULT '5',
3254 `d` int DEFAULT NULL,
3255 PRIMARY KEY (`a`)
3256 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3257 SHOW CREATE TABLE t2;
3258 Table Create Table
3259 t2 CREATE TABLE `t2` (
3260 `a` int NOT NULL AUTO_INCREMENT,
3261 `b` int DEFAULT NULL,
3262 `c` int NOT NULL DEFAULT '5',
3263 `d` int DEFAULT NULL,
3264 PRIMARY KEY (`a`)
3265 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3266 /*!50100 PARTITION BY RANGE (`a`)
3267 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
3268 PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
3269 PARTITION p3 VALUES LESS THAN (30) ENGINE = InnoDB) */
3270 ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
3271 DROP TABLE t1, t2;
3272 #
3273 # Scenario 11:
3274 # PRIMARY KEY with more than one column, at least to verify it works with REDUNDANT
3275 #
3276 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a, b)) ROW_FORMAT=DYNAMIC;;
3277 INSERT INTO t1 VALUES(0, 1), (1, 2), (2, 3), (3, 4);
3278 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
3279 Table id did not change
3280 count(*) = 1
3281 1
3282 CHECK TABLE t1;
3283 Table Op Msg_type Msg_text
3284 test.t1 check status OK
3285 SELECT * FROM t1;
3286 a b c
3287 0 1 5
3288 1 2 5
3289 2 3 5
3290 3 4 5
3291 UPDATE t1 SET c = b WHERE b <= 2;
3292 CHECK TABLE t1;
3293 Table Op Msg_type Msg_text
3294 test.t1 check status OK
3295 SELECT * FROM t1;
3296 a b c
3297 0 1 1
3298 1 2 2
3299 2 3 5
3300 3 4 5
3301 DROP TABLE t1;
3302 #
3303 # Scenario 12:
3304 # Mix ALTER PARTITION and ALTER TABLE ... INPLACE. This is to check if first partition is not
3305 # instant after ALTER PARTITION, will the metadata be copied correctly
3306 #
3307 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);;
3308 INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg');
3309 ALTER TABLE t1 ALGORITHM DEFAULT, ADD COLUMN col5 VARCHAR(500), ADD COLUMN col6 TEXT;
3310 Table id did not change
3311 count(*) = 2
3312 1
3313 CHECK TABLE t1;
3314 Table Op Msg_type Msg_text
3315 test.t1 check status OK
3316 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
3317 Expect 0
3318 0
3319 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (64), PARTITION p0_b VALUES LESS THAN (128));
3320 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
3321 Expect 0
3322 0
3323 CHECK TABLE t1;
3324 Table Op Msg_type Msg_text
3325 test.t1 check status OK
3326 ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10));
3327 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
3328 Expect 0
3329 0
3330 CHECK TABLE t1;
3331 Table Op Msg_type Msg_text
3332 test.t1 check status OK
3333 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);
3334 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
3335 Expect 0
3336 0
3337 CHECK TABLE t1;
3338 Table Op Msg_type Msg_text
3339 test.t1 check status OK
3340 ALTER TABLE t1 ADD KEY idx3(col3);
3341 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
3342 Expect 0
3343 0
3344 CHECK TABLE t1;
3345 Table Op Msg_type Msg_text
3346 test.t1 check status OK
3347 SELECT * FROM t1;
3348 col1 col2 col3 col4 col5 col6
3349 1 2 3 abcdefg NULL NULL
3350 100 200 300 qwerty NULL NULL
3351 200 300 400 asdfg NULL NULL
3352 DROP TABLE t1;
3353 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC PARTITION BY HASH(a) PARTITIONS 3;;
3354 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
3355 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
3356 Table id did not change
3357 count(*) = 1
3358 1
3359 CHECK TABLE t1;
3360 Table Op Msg_type Msg_text
3361 test.t1 check status OK
3362 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
3363 Expect 3
3364 0
3365 ALTER TABLE t1 ADD PARTITION PARTITIONS 10;
3366 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
3367 Expect 0
3368 0
3369 CHECK TABLE t1;
3370 Table Op Msg_type Msg_text
3371 test.t1 check status OK
3372 ALTER TABLE t1 ADD KEY(b);
3373 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
3374 Expect 0
3375 0
3376 CHECK TABLE t1;
3377 Table Op Msg_type Msg_text
3378 test.t1 check status OK
3379 SELECT * FROM t1;
3380 a b c
3381 1 1 5
3382 2 2 5
3383 3 3 5
3384 4 4 5
3385 5 5 5
3386 6 6 5
3387 7 7 5
3388 8 8 5
3389 DROP TABLE t1;
3390 #
3391 # Scenario 13:
3392 # Create a table with a two level clustered index, do instant ADD COLUMN, then the non-leaf node
3393 # should be parsed correctly
3394 #
3395 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=DYNAMIC PARTITION BY LINEAR KEY(c) PARTITIONS 9;;
3396 INSERT INTO t1(d, b, c) VALUES(1, 2, 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhjjjjjjj');
3397 INSERT INTO t1(d, b, c) VALUES(2, 3, 'aaaaaaaaaahhhhhhhhhhbbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
3398 INSERT INTO t1(d, b, c) VALUES(3, 4, 'bbbbbbbbbbaaaaaaaaaahhhhhhhhhhccccccccccddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
3399 INSERT INTO t1(d, b, c) VALUES(4, 5, 'eeeeeeeeeehhhhhhhhhhbbbbbbbbbbccccccccccddddddddddaaaaaaaaaaffffffffffggggggggggjjjjjjj');
3400 INSERT INTO t1(d, b, c) VALUES(5, 6, 'aaaaaaaaaahhhhhhhhhhbbbbbbbbbbddddddddddcccccccccceeeeeeeeeeffffffffffggggggggggjjjjjjj');
3401 INSERT INTO t1(d, b, c) VALUES(6, 7, 'cccccccccchhhhhhhhhhbbbbbbbbbbaaaaaaaaaaddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
3402 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
3403 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
3404 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
3405 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
3406 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
3407 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
3408 ALTER TABLE t1 ADD COLUMN nc086 BIGINT NOT NULL FIRST, ALGORITHM=INPLACE, LOCK=DEFAULT;
3409 CHECK TABLE t1;
3410 Table Op Msg_type Msg_text
3411 test.t1 check status OK
3412 ALTER TABLE t1 ADD COLUMN nc082 TINYTEXT;
3413 CHECK TABLE t1;
3414 Table Op Msg_type Msg_text
3415 test.t1 check status OK
3416 SELECT COUNT(*) FROM t1;
3417 COUNT(*)
3418 384
3419 DROP TABLE t1;
3420 #
3421 # Scenario 14:
3422 # Create a small table, and add GIS kinds of new columns and verify
3423 #
3424 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
3425 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
3426 ALTER TABLE t1 ADD COLUMN c1 POINT, ALGORITHM = INSTANT;
3427 Table id did not change
3428 count(*) = 1
3429 1
3430 CHECK TABLE t1;
3431 Table Op Msg_type Msg_text
3432 test.t1 check status OK
3433 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
3434 name default_value
3435 c1 NULL
3436 INSERT INTO t1(a, c1) VALUES(0, ST_PointFromText('POINT(10 10)'));
3437 SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
3438 count(*) = max(a)
3439 1
3440 ALTER TABLE t1 ADD COLUMN d1 LINESTRING, ALGORITHM = INSTANT;
3441 Table id did not change
3442 count(*) = 2
3443 1
3444 CHECK TABLE t1;
3445 Table Op Msg_type Msg_text
3446 test.t1 check status OK
3447 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd1' AND has_default = 1;
3448 name default_value
3449 d1 NULL
3450 INSERT INTO t1(a, d1) VALUES(0, ST_LineFromText('LINESTRING(0 0,0 10,10 0)'));
3451 SELECT count(*) = max(a) FROM t1 WHERE d1 IS NULL;
3452 count(*) = max(a)
3453 1
3454 ALTER TABLE t1 ADD COLUMN e1 POLYGON, ALGORITHM = INSTANT;
3455 Table id did not change
3456 count(*) = 3
3457 1
3458 CHECK TABLE t1;
3459 Table Op Msg_type Msg_text
3460 test.t1 check status OK
3461 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e1' AND has_default = 1;
3462 name default_value
3463 e1 NULL
3464 INSERT INTO t1(a, e1) VALUES(0, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))'));
3465 SELECT count(*) = max(a) FROM t1 WHERE e1 IS NULL;
3466 count(*) = max(a)
3467 1
3468 ALTER TABLE t1 ADD COLUMN f1 MULTIPOINT, ALGORITHM = INSTANT;
3469 Table id did not change
3470 count(*) = 4
3471 1
3472 CHECK TABLE t1;
3473 Table Op Msg_type Msg_text
3474 test.t1 check status OK
3475 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f1' AND has_default = 1;
3476 name default_value
3477 f1 NULL
3478 INSERT INTO t1(a, f1) VALUES(0, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)'));
3479 SELECT count(*) = max(a) FROM t1 WHERE f1 IS NULL;
3480 count(*) = max(a)
3481 1
3482 ALTER TABLE t1 ADD COLUMN g1 MULTILINESTRING, ALGORITHM = INSTANT;
3483 Table id did not change
3484 count(*) = 5
3485 1
3486 CHECK TABLE t1;
3487 Table Op Msg_type Msg_text
3488 test.t1 check status OK
3489 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g1' AND has_default = 1;
3490 name default_value
3491 g1 NULL
3492 INSERT INTO t1(a, g1) VALUES(0, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))'));
3493 SELECT count(*) = max(a) FROM t1 WHERE g1 IS NULL;
3494 count(*) = max(a)
3495 1
3496 ALTER TABLE t1 ADD COLUMN h1 MULTIPOLYGON, ALGORITHM = INSTANT;
3497 Table id did not change
3498 count(*) = 6
3499 1
3500 CHECK TABLE t1;
3501 Table Op Msg_type Msg_text
3502 test.t1 check status OK
3503 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h1' AND has_default = 1;
3504 name default_value
3505 h1 NULL
3506 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)))'));
3507 SELECT count(*) = max(a) FROM t1 WHERE h1 IS NULL;
3508 count(*) = max(a)
3509 1
3510 ALTER TABLE t1 ADD COLUMN i1 GEOMETRYCOLLECTION, ALGORITHM = INSTANT;
3511 Table id did not change
3512 count(*) = 7
3513 1
3514 CHECK TABLE t1;
3515 Table Op Msg_type Msg_text
3516 test.t1 check status OK
3517 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'i1' AND has_default = 1;
3518 name default_value
3519 i1 NULL
3520 INSERT INTO t1(a, i1) VALUES(0, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'));
3521 SELECT count(*) = max(a) FROM t1 WHERE i1 IS NULL;
3522 count(*) = max(a)
3523 1
3524 ALTER TABLE t1 ADD COLUMN j1 GEOMETRY, ALGORITHM = INSTANT;
3525 Table id did not change
3526 count(*) = 8
3527 1
3528 CHECK TABLE t1;
3529 Table Op Msg_type Msg_text
3530 test.t1 check status OK
3531 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'j1' AND has_default = 1;
3532 name default_value
3533 j1 NULL
3534 INSERT INTO t1(a, j1) VALUES(0, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'));
3535 SELECT count(*) = max(a) FROM t1 WHERE j1 IS NULL;
3536 count(*) = max(a)
3537 1
3538 CHECK TABLE t1;
3539 Table Op Msg_type Msg_text
3540 test.t1 check status OK
3541 SHOW CREATE TABLE t1;
3542 Table Create Table
3543 t1 CREATE TABLE `t1` (
3544 `a` int NOT NULL AUTO_INCREMENT,
3545 `b` int DEFAULT NULL,
3546 `c1` point DEFAULT NULL,
3547 `d1` linestring DEFAULT NULL,
3548 `e1` polygon DEFAULT NULL,
3549 `f1` multipoint DEFAULT NULL,
3550 `g1` multilinestring DEFAULT NULL,
3551 `h1` multipolygon DEFAULT NULL,
3552 `i1` geomcollection DEFAULT NULL,
3553 `j1` geometry DEFAULT NULL,
3554 PRIMARY KEY (`a`)
3555 ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3556 DROP TABLE t1;
3557 #
3558 # Scenario 15:
3559 # Create a small table, and add JSON columns and verify
3560 #
3561 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
3562 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
3563 ALTER TABLE t1 ADD COLUMN c1 JSON, ALGORITHM = INSTANT;
3564 Table id did not change
3565 count(*) = 1
3566 1
3567 CHECK TABLE t1;
3568 Table Op Msg_type Msg_text
3569 test.t1 check status OK
3570 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
3571 name default_value
3572 c1 NULL
3573 INSERT INTO t1(a, c1) VALUES(0, '{"key1": "value1", "key2": "value2"}');
3574 SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
3575 count(*) = max(a)
3576 1
3577 CHECK TABLE t1;
3578 Table Op Msg_type Msg_text
3579 test.t1 check status OK
3580 SHOW CREATE TABLE t1;
3581 Table Create Table
3582 t1 CREATE TABLE `t1` (
3583 `a` int NOT NULL AUTO_INCREMENT,
3584 `b` int DEFAULT NULL,
3585 `c1` json DEFAULT NULL,
3586 PRIMARY KEY (`a`)
3587 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3588 DROP TABLE t1;
3589 #
3590 # Scenario 16:
3591 # Create a small table, and add INSTANT columns and verify with trigger
3592 #
3593 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
3594 CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
3595 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
3596 INSERT INTO t2 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
3597 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
3598 Table id did not change
3599 count(*) = 1
3600 1
3601 CHECK TABLE t1;
3602 Table Op Msg_type Msg_text
3603 test.t1 check status OK
3604 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
3605 name default_value
3606 c1 61616161
3607 CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
3608 INSERT INTO t2 VALUES(0,6);
3609 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
3610 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
3611 count(*) = max(a)
3612 1
3613 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
3614 c1
3615 bbbb
3616 DROP TRIGGER t1_ai;
3617 ALTER TABLE t2 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'cccc', ALGORITHM = INSTANT;
3618 Table id did not change
3619 count(*) = 1
3620 1
3621 CHECK TABLE t1;
3622 Table Op Msg_type Msg_text
3623 test.t1 check status OK
3624 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
3625 name default_value
3626 c1 61616161
3627 c1 63636363
3628 CREATE TRIGGER t2_ai AFTER INSERT ON t2 FOR EACH ROW
3629 INSERT INTO t1(a,c1) VALUES(0,'eeee');
3630 INSERT INTO t2(a, c1) VALUES(0, 'dddd');
3631 SELECT count(*) = max(a) FROM t2 WHERE c1='cccc';
3632 count(*) = max(a)
3633 1
3634 SELECT c1 FROM t2 WHERE c1 = 'dddd';
3635 c1
3636 dddd
3637 DROP TRIGGER t2_ai;
3638 CHECK TABLE t1;
3639 Table Op Msg_type Msg_text
3640 test.t1 check status OK
3641 CHECK TABLE t2;
3642 Table Op Msg_type Msg_text
3643 test.t2 check status OK
3644 SHOW CREATE TABLE t1;
3645 Table Create Table
3646 t1 CREATE TABLE `t1` (
3647 `a` int NOT NULL AUTO_INCREMENT,
3648 `b` int DEFAULT NULL,
3649 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
3650 PRIMARY KEY (`a`)
3651 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3652 SHOW CREATE TABLE t2;
3653 Table Create Table
3654 t2 CREATE TABLE `t2` (
3655 `a` int NOT NULL AUTO_INCREMENT,
3656 `b` int DEFAULT NULL,
3657 `c1` varchar(10) NOT NULL DEFAULT 'cccc',
3658 PRIMARY KEY (`a`)
3659 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3660 DROP TABLE t1,t2;
3661 #
3662 # Scenario 17:
3663 # Create a small table, and add INSTANT columns and verify with storedprocedure
3664 #
3665 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
3666 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
3667 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
3668 Table id did not change
3669 count(*) = 1
3670 1
3671 CHECK TABLE t1;
3672 Table Op Msg_type Msg_text
3673 test.t1 check status OK
3674 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
3675 name default_value
3676 c1 61616161
3677 CREATE PROCEDURE p1() INSERT INTO t1(a,c1) VALUES(0, 'bbbb');
3678 CALL p1();
3679 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
3680 count(*) = max(a)
3681 1
3682 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
3683 c1
3684 bbbb
3685 DROP PROCEDURE p1;
3686 CHECK TABLE t1;
3687 Table Op Msg_type Msg_text
3688 test.t1 check status OK
3689 SHOW CREATE TABLE t1;
3690 Table Create Table
3691 t1 CREATE TABLE `t1` (
3692 `a` int NOT NULL AUTO_INCREMENT,
3693 `b` int DEFAULT NULL,
3694 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
3695 PRIMARY KEY (`a`)
3696 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3697 DROP TABLE t1;
3698 #
3699 # Scenario 18:
3700 # Create a small table, and add INSTANT columns and verify with view
3701 #
3702 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
3703 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
3704 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
3705 Table id did not change
3706 count(*) = 1
3707 1
3708 CHECK TABLE t1;
3709 Table Op Msg_type Msg_text
3710 test.t1 check status OK
3711 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
3712 name default_value
3713 c1 61616161
3714 CREATE VIEW v1 AS SELECT * FROM t1;
3715 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
3716 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
3717 count(*) = max(a)
3718 1
3719 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
3720 c1
3721 bbbb
3722 SELECT * FROM v1;
3723 a b c1
3724 1 1 aaaa
3725 2 2 aaaa
3726 3 3 aaaa
3727 4 4 aaaa
3728 5 5 aaaa
3729 6 NULL bbbb
3730 DROP VIEW v1;
3731 CHECK TABLE t1;
3732 Table Op Msg_type Msg_text
3733 test.t1 check status OK
3734 SHOW CREATE TABLE t1;
3735 Table Create Table
3736 t1 CREATE TABLE `t1` (
3737 `a` int NOT NULL AUTO_INCREMENT,
3738 `b` int DEFAULT NULL,
3739 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
3740 PRIMARY KEY (`a`)
3741 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3742 DROP TABLE t1;
3743 #
3744 # Scenario 19:
3745 # Create a small table, and add INSTANT columns and drop it and verify
3746 #
3747 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
3748 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
3749 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
3750 Table id did not change
3751 count(*) = 1
3752 1
3753 CHECK TABLE t1;
3754 Table Op Msg_type Msg_text
3755 test.t1 check status OK
3756 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
3757 name default_value
3758 c1 61616161
3759 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
3760 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
3761 count(*) = max(a)
3762 1
3763 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
3764 c1
3765 bbbb
3766 ALTER TABLE t1 DROP COLUMN c1;
3767 Table id did not change
3768 count(*) = 0
3769 1
3770 CHECK TABLE t1;
3771 Table Op Msg_type Msg_text
3772 test.t1 check status OK
3773 CHECK TABLE t1;
3774 Table Op Msg_type Msg_text
3775 test.t1 check status OK
3776 SHOW CREATE TABLE t1;
3777 Table Create Table
3778 t1 CREATE TABLE `t1` (
3779 `a` int NOT NULL AUTO_INCREMENT,
3780 `b` int DEFAULT NULL,
3781 PRIMARY KEY (`a`)
3782 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3783 DROP TABLE t1;
3784 #
3785 # Scenario 20:
3786 # Create a small table, and add INSTANT columns and rename table
3787 #
3788 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
3789 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
3790 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
3791 Table id did not change
3792 count(*) = 1
3793 1
3794 CHECK TABLE t1;
3795 Table Op Msg_type Msg_text
3796 test.t1 check status OK
3797 ALTER TABLE t1 RENAME t2;
3798 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
3799 name default_value
3800 c1 61616161
3801 INSERT INTO t2(a, c1) VALUES(0, 'bbbb');
3802 SELECT count(*) = max(a) FROM t2 WHERE c1='aaaa';
3803 count(*) = max(a)
3804 1
3805 SELECT c1 FROM t2 WHERE c1 = 'bbbb';
3806 c1
3807 bbbb
3808 CHECK TABLE t2;
3809 Table Op Msg_type Msg_text
3810 test.t2 check status OK
3811 SHOW CREATE TABLE t2;
3812 Table Create Table
3813 t2 CREATE TABLE `t2` (
3814 `a` int NOT NULL AUTO_INCREMENT,
3815 `b` int DEFAULT NULL,
3816 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
3817 PRIMARY KEY (`a`)
3818 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3819 DROP TABLE t2;
3820 #
3821 # Scenario 21:
3822 # Create a small table, and add INSTANT columns and change its data type INSTANTly won't work
3823 #
3824 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
3825 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
3826 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
3827 Table id did not change
3828 count(*) = 1
3829 1
3830 CHECK TABLE t1;
3831 Table Op Msg_type Msg_text
3832 test.t1 check status OK
3833 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
3834 name default_value
3835 c1 61616161
3836 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
3837 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
3838 count(*) = max(a)
3839 1
3840 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
3841 c1
3842 bbbb
3843 ALTER TABLE t1 CHANGE c1 c2 CHAR(10) NOT NULL DEFAULT 'cccc';
3844 Table ID differed
3845 count(*) = 0
3846 1
3847 CHECK TABLE t1;
3848 Table Op Msg_type Msg_text
3849 test.t1 check status OK
3850 CHECK TABLE t1;
3851 Table Op Msg_type Msg_text
3852 test.t1 check status OK
3853 SHOW CREATE TABLE t1;
3854 Table Create Table
3855 t1 CREATE TABLE `t1` (
3856 `a` int NOT NULL AUTO_INCREMENT,
3857 `b` int DEFAULT NULL,
3858 `c2` char(10) NOT NULL DEFAULT 'cccc',
3859 PRIMARY KEY (`a`)
3860 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3861 DROP TABLE t1;
3862 #
3863 # Scenario 22:
3864 # Create a small table, and add INSTANT columns and create hash,btree multi column index and verify
3865 #
3866 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
3867 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
3868 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
3869 Table id did not change
3870 count(*) = 1
3871 1
3872 CHECK TABLE t1;
3873 Table Op Msg_type Msg_text
3874 test.t1 check status OK
3875 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
3876 name default_value
3877 c1 61616161
3878 CREATE INDEX id1 ON t1(c1) USING BTREE;
3879 CREATE INDEX id2 ON t1(c1) USING HASH;
3880 Warnings:
3881 Note 3502 This storage engine does not support the HASH index algorithm, storage engine default was used instead.
3882 Warning 1831 Duplicate index 'id2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
3883 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
3884 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
3885 count(*) = max(a)
3886 1
3887 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
3888 c1
3889 bbbb
3890 DROP INDEX id1 ON t1;
3891 DROP INDEX id2 ON t1;
3892 ALTER TABLE t1 ADD COLUMN c2 VARCHAR(10) NOT NULL DEFAULT 'cccc', ALGORITHM = INSTANT;
3893 Table id did not change
3894 count(*) = 2
3895 1
3896 CHECK TABLE t1;
3897 Table Op Msg_type Msg_text
3898 test.t1 check status OK
3899 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c2' AND has_default = 1;
3900 name default_value
3901 c2 63636363
3902 CREATE INDEX id1 ON t1(c1 ASC,c2 DESC) USING BTREE;
3903 INSERT INTO t1(a, c2) VALUES(0, 'dddd');
3904 SELECT count(*) = max(a) FROM t1 WHERE c1='cccc';
3905 count(*) = max(a)
3906 NULL
3907 SELECT c1 FROM t1 WHERE c1 = 'dddd';
3908 c1
3909 ALTER TABLE t1 RENAME INDEX id1 TO id2;
3910 DROP INDEX id2 ON t1;
3911 CHECK TABLE t1;
3912 Table Op Msg_type Msg_text
3913 test.t1 check status OK
3914 SHOW CREATE TABLE t1;
3915 Table Create Table
3916 t1 CREATE TABLE `t1` (
3917 `a` int NOT NULL AUTO_INCREMENT,
3918 `b` int DEFAULT NULL,
3919 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
3920 `c2` varchar(10) NOT NULL DEFAULT 'cccc',
3921 PRIMARY KEY (`a`)
3922 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3923 DROP TABLE t1;
3924 #
3925 # Scenario 23:
3926 # Create a small table, and add INSTANT columns and perform table join operation
3927 #
3928 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
3929 CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
3930 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
3931 INSERT INTO t2 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
3932 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
3933 Table id did not change
3934 count(*) = 1
3935 1
3936 CHECK TABLE t1;
3937 Table Op Msg_type Msg_text
3938 test.t1 check status OK
3939 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
3940 name default_value
3941 c1 61616161
3942 INSERT INTO t1(a, c1) VALUES(0, 'cccc');
3943 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
3944 count(*) = max(a)
3945 1
3946 SELECT c1 FROM t1 WHERE c1 = 'cccc';
3947 c1
3948 cccc
3949 ALTER TABLE t2 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'bbbb', ALGORITHM = INSTANT;
3950 Table id did not change
3951 count(*) = 1
3952 1
3953 CHECK TABLE t1;
3954 Table Op Msg_type Msg_text
3955 test.t1 check status OK
3956 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
3957 name default_value
3958 c1 61616161
3959 c1 62626262
3960 INSERT INTO t2(a, c1) VALUES(0, 'cccc');
3961 SELECT count(*) = max(a) FROM t2 WHERE c1='bbbb';
3962 count(*) = max(a)
3963 1
3964 SELECT c1 FROM t2 WHERE c1 = 'cccc';
3965 c1
3966 cccc
3967 SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1;
3968 a b c1 a b c1
3969 6 NULL cccc 6 NULL cccc
3970 CHECK TABLE t1;
3971 Table Op Msg_type Msg_text
3972 test.t1 check status OK
3973 CHECK TABLE t2;
3974 Table Op Msg_type Msg_text
3975 test.t2 check status OK
3976 SHOW CREATE TABLE t1;
3977 Table Create Table
3978 t1 CREATE TABLE `t1` (
3979 `a` int NOT NULL AUTO_INCREMENT,
3980 `b` int DEFAULT NULL,
3981 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
3982 PRIMARY KEY (`a`)
3983 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3984 SHOW CREATE TABLE t2;
3985 Table Create Table
3986 t2 CREATE TABLE `t2` (
3987 `a` int NOT NULL AUTO_INCREMENT,
3988 `b` int DEFAULT NULL,
3989 `c1` varchar(10) NOT NULL DEFAULT 'bbbb',
3990 PRIMARY KEY (`a`)
3991 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
3992 DROP TABLE t1;
3993 DROP TABLE t2;
3994 #
3995 # Scenario 24:
3996 # Create a small table, and add stored and(or) virtual columns
3997 # after last stored column in the table
3998 #
3999 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=DYNAMIC;
4000 INSERT INTO t1(a, b, c) VALUES(0, 1, 2), (0, 2, 3), (0, 3, 4), (0, 4, 5), (0, 5, 6);
4001 ALTER TABLE t1 ADD COLUMN h INT NOT NULL AFTER c;
4002 Table id did not change
4003 count(*) = 1
4004 1
4005 CHECK TABLE t1;
4006 Table Op Msg_type Msg_text
4007 test.t1 check status OK
4008 INSERT INTO t1(a, b, c, h) VALUES(0, 6, 20, 40);
4009 SELECT * FROM t1;
4010 a b c h d e
4011 1 1 2 0 3 2
4012 2 2 3 0 5 6
4013 3 3 4 0 7 12
4014 4 4 5 0 9 20
4015 5 5 6 0 11 30
4016 6 6 20 40 26 120
4017 ALTER TABLE t1 ADD COLUMN i VARCHAR(100) DEFAULT 'ABCD EFGH' AFTER h, ADD COLUMN f INT GENERATED ALWAYS AS (LENGTH(i)) AFTER i;
4018 Table id did not change
4019 count(*) = 2
4020 1
4021 CHECK TABLE t1;
4022 Table Op Msg_type Msg_text
4023 test.t1 check status OK
4024 INSERT INTO t1(a, b, c, h, i) VALUES(0, 20, 30, 50, 'qwerty');
4025 SELECT * FROM t1;
4026 a b c h i f d e
4027 1 1 2 0 ABCD EFGH 9 3 2
4028 2 2 3 0 ABCD EFGH 9 5 6
4029 3 3 4 0 ABCD EFGH 9 7 12
4030 4 4 5 0 ABCD EFGH 9 9 20
4031 5 5 6 0 ABCD EFGH 9 11 30
4032 6 6 20 40 ABCD EFGH 9 26 120
4033 7 20 30 50 qwerty 6 50 600
4034 CHECK TABLE t1;
4035 Table Op Msg_type Msg_text
4036 test.t1 check status OK
4037 SHOW CREATE TABLE t1;
4038 Table Create Table
4039 t1 CREATE TABLE `t1` (
4040 `a` int NOT NULL AUTO_INCREMENT,
4041 `b` int DEFAULT NULL,
4042 `c` int DEFAULT NULL,
4043 `h` int NOT NULL,
4044 `i` varchar(100) DEFAULT 'ABCD EFGH',
4045 `f` int GENERATED ALWAYS AS (length(`i`)) VIRTUAL,
4046 `d` int GENERATED ALWAYS AS ((`b` + `c`)) VIRTUAL,
4047 `e` int GENERATED ALWAYS AS ((`b` * `c`)) VIRTUAL,
4048 PRIMARY KEY (`a`)
4049 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
4050 DROP TABLE t1;
4051 ############################################
4052 # Test instant ADD COLUMN for COMPACT format
4053 ############################################
4054 #
4055 # Scenario 1:
4056 # Create a small table, and add all kinds of new columns and verify
4057 #
4058 SET @saved_innodb_strict_mode=@@SESSION.innodb_strict_mode;
4059 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
4060 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
4061 ALTER TABLE t1 ADD COLUMN c1 INT, ALGORITHM=INSTANT;
4062 Table id did not change
4063 count(*) = 1
4064 1
4065 CHECK TABLE t1;
4066 Table Op Msg_type Msg_text
4067 test.t1 check status OK
4068 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
4069 name default_value
4070 c1 NULL
4071 INSERT INTO t1(a, c1) VALUES(0, 1);
4072 SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
4073 count(*) = max(a)
4074 1
4075 SELECT c1 FROM t1 WHERE c1 = 1;
4076 c1
4077 1
4078 ALTER TABLE t1 ADD COLUMN c2 INT NOT NULL DEFAULT 10;
4079 Table id did not change
4080 count(*) = 2
4081 1
4082 CHECK TABLE t1;
4083 Table Op Msg_type Msg_text
4084 test.t1 check status OK
4085 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c2' AND has_default = 1;
4086 name default_value
4087 c2 8000000a
4088 INSERT INTO t1(a, c2) VALUES(0, 11);
4089 SELECT count(*) = max(a) FROM t1 WHERE c2 = 10;
4090 count(*) = max(a)
4091 1
4092 SELECT c2 FROM t1 WHERE c2 = 11;
4093 c2
4094 11
4095 ALTER TABLE t1 ADD COLUMN d1 BIGINT, ALGORITHM=INSTANT;
4096 Table id did not change
4097 count(*) = 3
4098 1
4099 CHECK TABLE t1;
4100 Table Op Msg_type Msg_text
4101 test.t1 check status OK
4102 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd1' AND has_default = 1;
4103 name default_value
4104 d1 NULL
4105 INSERT INTO t1(a, d1) VALUES(0, 1);
4106 SELECT count(*) = max(a) FROM t1 WHERE d1 IS NULL;
4107 count(*) = max(a)
4108 1
4109 SELECT d1 FROM t1 WHERE d1 = 1;
4110 d1
4111 1
4112 ALTER TABLE t1 ADD COLUMN d2 BIGINT NOT NULL DEFAULT 1234567890;
4113 Table id did not change
4114 count(*) = 4
4115 1
4116 CHECK TABLE t1;
4117 Table Op Msg_type Msg_text
4118 test.t1 check status OK
4119 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd2' AND has_default = 1;
4120 name default_value
4121 d2 80000000499602d2
4122 INSERT INTO t1(a, d2) VALUES(0, 1234);
4123 SELECT count(*) = max(a) FROM t1 WHERE d2 = 1234567890;
4124 count(*) = max(a)
4125 1
4126 SELECT d2 FROM t1 WHERE d2 = 1234;
4127 d2
4128 1234
4129 ALTER TABLE t1 ADD COLUMN e1 SMALLINT, ALGORITHM=INSTANT;
4130 Table id did not change
4131 count(*) = 5
4132 1
4133 CHECK TABLE t1;
4134 Table Op Msg_type Msg_text
4135 test.t1 check status OK
4136 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e1' AND has_default = 1;
4137 name default_value
4138 e1 NULL
4139 INSERT INTO t1(a, e1) VALUES(0, 1);
4140 SELECT count(*) = max(a) FROM t1 WHERE e1 IS NULL;
4141 count(*) = max(a)
4142 1
4143 SELECT e1 FROM t1 WHERE e1 = 1;
4144 e1
4145 1
4146 ALTER TABLE t1 ADD COLUMN e2 SMALLINT NOT NULL DEFAULT 10;
4147 Table id did not change
4148 count(*) = 6
4149 1
4150 CHECK TABLE t1;
4151 Table Op Msg_type Msg_text
4152 test.t1 check status OK
4153 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e2' AND has_default = 1;
4154 name default_value
4155 e2 800a
4156 INSERT INTO t1(a, e2) VALUES(0, 11);
4157 SELECT count(*) = max(a) FROM t1 WHERE e2 = 10;
4158 count(*) = max(a)
4159 1
4160 SELECT e2 FROM t1 WHERE e2 = 11;
4161 e2
4162 11
4163 ALTER TABLE t1 ADD COLUMN f1 TINYINT, ALGORITHM=INSTANT;
4164 Table id did not change
4165 count(*) = 7
4166 1
4167 CHECK TABLE t1;
4168 Table Op Msg_type Msg_text
4169 test.t1 check status OK
4170 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f1' AND has_default = 1;
4171 name default_value
4172 f1 NULL
4173 INSERT INTO t1(a, f1) VALUES(0, 1);
4174 SELECT count(*) = max(a) FROM t1 WHERE f1 IS NULL;
4175 count(*) = max(a)
4176 1
4177 SELECT f1 FROM t1 WHERE f1 = 1;
4178 f1
4179 1
4180 ALTER TABLE t1 ADD COLUMN f2 TINYINT NOT NULL DEFAULT 123;
4181 Table id did not change
4182 count(*) = 8
4183 1
4184 CHECK TABLE t1;
4185 Table Op Msg_type Msg_text
4186 test.t1 check status OK
4187 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f2' AND has_default = 1;
4188 name default_value
4189 f2 fb
4190 INSERT INTO t1(a, f2) VALUES(0, 12);
4191 SELECT count(*) = max(a) FROM t1 WHERE f2 = 123;
4192 count(*) = max(a)
4193 1
4194 SELECT f2 FROM t1 WHERE f2 = 12;
4195 f2
4196 12
4197 ALTER TABLE t1 ADD COLUMN g1 MEDIUMINT, ALGORITHM=INSTANT;
4198 Table id did not change
4199 count(*) = 9
4200 1
4201 CHECK TABLE t1;
4202 Table Op Msg_type Msg_text
4203 test.t1 check status OK
4204 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g1' AND has_default = 1;
4205 name default_value
4206 g1 NULL
4207 INSERT INTO t1(a, g1) VALUES(0, 1);
4208 SELECT count(*) = max(a) FROM t1 WHERE g1 IS NULL;
4209 count(*) = max(a)
4210 1
4211 SELECT g1 FROM t1 WHERE g1 = 1;
4212 g1
4213 1
4214 ALTER TABLE t1 ADD COLUMN g2 MEDIUMINT NOT NULL DEFAULT 12345;
4215 Table id did not change
4216 count(*) = 10
4217 1
4218 CHECK TABLE t1;
4219 Table Op Msg_type Msg_text
4220 test.t1 check status OK
4221 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g2' AND has_default = 1;
4222 name default_value
4223 g2 803039
4224 INSERT INTO t1(a, g2) VALUES(0, 1234);
4225 SELECT count(*) = max(a) FROM t1 WHERE g2 = 12345;
4226 count(*) = max(a)
4227 1
4228 SELECT g2 FROM t1 WHERE g2 = 1234;
4229 g2
4230 1234
4231 ALTER TABLE t1 ADD COLUMN h1 FLOAT, ALGORITHM=INSTANT;
4232 Table id did not change
4233 count(*) = 11
4234 1
4235 CHECK TABLE t1;
4236 Table Op Msg_type Msg_text
4237 test.t1 check status OK
4238 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h1' AND has_default = 1;
4239 name default_value
4240 h1 NULL
4241 INSERT INTO t1(a, h1) VALUES(0, 1.0);
4242 SELECT count(*) = max(a) FROM t1 WHERE h1 IS NULL;
4243 count(*) = max(a)
4244 1
4245 SELECT h1 FROM t1 WHERE h1 = 1;
4246 h1
4247 1
4248 ALTER TABLE t1 ADD COLUMN h2 FLOAT NOT NULL DEFAULT 12.34;
4249 Table id did not change
4250 count(*) = 12
4251 1
4252 CHECK TABLE t1;
4253 Table Op Msg_type Msg_text
4254 test.t1 check status OK
4255 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h2' AND has_default = 1;
4256 name default_value
4257 h2 a4704541
4258 INSERT INTO t1(a, h2) VALUES(0, 1.234);
4259 SELECT count(*) = max(a) FROM t1 WHERE h2 = 12.34;
4260 count(*) = max(a)
4261 NULL
4262 SELECT h2 FROM t1 WHERE h2 = 1.234;
4263 h2
4264 ALTER TABLE t1 ADD COLUMN i1 DECIMAL(5, 2), ADD COLUMN i2 double, ALGORITHM=INSTANT;
4265 Table id did not change
4266 count(*) = 14
4267 1
4268 CHECK TABLE t1;
4269 Table Op Msg_type Msg_text
4270 test.t1 check status OK
4271 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'i1' OR name = 'i2') AND has_default = 1;
4272 name default_value
4273 i1 NULL
4274 i2 NULL
4275 INSERT INTO t1(a, i1, i2) VALUES(0, 10.10, 20.20);
4276 SELECT count(*) = max(a) FROM t1 WHERE i1 IS NULL;
4277 count(*) = max(a)
4278 1
4279 SELECT count(*) = max(a) FROM t1 WHERE i2 IS NULL;
4280 count(*) = max(a)
4281 1
4282 SELECT i1 FROM t1 WHERE i1 = 10.10;
4283 i1
4284 10.10
4285 SELECT i2 FROM t1 WHERE i2 = 20.20;
4286 i2
4287 20.2
4288 ALTER TABLE t1 ADD COLUMN j1 DECIMAL(5, 2) NOT NULL DEFAULT 100.00, ADD COLUMN j2 double NOT NULL DEFAULT 1000.5678;
4289 Table id did not change
4290 count(*) = 16
4291 1
4292 CHECK TABLE t1;
4293 Table Op Msg_type Msg_text
4294 test.t1 check status OK
4295 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'j1' OR name = 'j2') AND has_default = 1;
4296 name default_value
4297 j1 806400
4298 j2 5af5b9da8a448f40
4299 INSERT INTO t1(a, j1, j2) VALUES(0, 90.90, 1000.1234);
4300 SELECT count(*) = max(a) FROM t1 WHERE j1 = 100.00;
4301 count(*) = max(a)
4302 1
4303 SELECT count(*) = max(a) FROM t1 WHERE j2 = 1000.5678;
4304 count(*) = max(a)
4305 1
4306 SELECT j1 FROM t1 WHERE j1 = 90.90;
4307 j1
4308 90.90
4309 SELECT j2 FROM t1 WHERE j2 = 1000.1234;
4310 j2
4311 1000.1234
4312 ALTER TABLE t1 ADD COLUMN k1 BIT(8), ALGORITHM=INSTANT;
4313 Table id did not change
4314 count(*) = 17
4315 1
4316 CHECK TABLE t1;
4317 Table Op Msg_type Msg_text
4318 test.t1 check status OK
4319 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'k1' AND has_default = 1;
4320 name default_value
4321 k1 NULL
4322 INSERT INTO t1(a, k1) VALUES(0, b'010101');
4323 SELECT count(*) = max(a) FROM t1 WHERE k1 IS NULL;
4324 count(*) = max(a)
4325 1
4326 SELECT hex(k1) FROM t1 WHERE k1 = b'010101';
4327 hex(k1)
4328 15
4329 ALTER TABLE t1 ADD COLUMN k2 BIT(8) NOT NULL DEFAULT b'101010';
4330 Table id did not change
4331 count(*) = 18
4332 1
4333 CHECK TABLE t1;
4334 Table Op Msg_type Msg_text
4335 test.t1 check status OK
4336 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'k2' AND has_default = 1;
4337 name default_value
4338 k2 2a
4339 INSERT INTO t1(a, k2) VALUES(0, b'110011');
4340 SELECT count(*) = max(a) FROM t1 WHERE k2 = b'101010';
4341 count(*) = max(a)
4342 1
4343 SELECT hex(k2) FROM t1 WHERE k2 = b'110011';
4344 hex(k2)
4345 33
4346 ALTER TABLE t1 ADD COLUMN l1 CHAR(50), ADD COLUMN l2 VARCHAR(100), ALGORITHM=DEFAULT;
4347 Table id did not change
4348 count(*) = 20
4349 1
4350 CHECK TABLE t1;
4351 Table Op Msg_type Msg_text
4352 test.t1 check status OK
4353 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'l1' OR name = 'l2') AND has_default = 1;
4354 name default_value
4355 l1 NULL
4356 l2 NULL
4357 INSERT INTO t1(a, l1, l2) VALUES(0, 'ABCD EFGH', 'abcdefg hijklmn ');
4358 SELECT count(*) = max(a) FROM t1 WHERE l2 IS NULL;
4359 count(*) = max(a)
4360 1
4361 SELECT count(*) = max(a) FROM t1 WHERE l1 IS NULL;
4362 count(*) = max(a)
4363 1
4364 SELECT l1 FROM t1 WHERE l1 = 'ABCD EFGH';
4365 l1
4366 ABCD EFGH
4367 SELECT l2 FROM t1 WHERE l2 = 'abcdefg hijklmn ';
4368 l2
4369 abcdefg hijklmn
4370 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.';
4371 Table id did not change
4372 count(*) = 22
4373 1
4374 CHECK TABLE t1;
4375 Table Op Msg_type Msg_text
4376 test.t1 check status OK
4377 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'm1' OR name = 'm2') AND has_default = 1;
4378 name default_value
4379 m1 54686520666f78206a756d7073206f7665722020202020202020202020202020202020202020202020202020202020202020
4380 m2 54686520666f78206a756d7073206f76657220746865206c617a7920646f672e
4381 INSERT INTO t1(a, m1, m2) VALUES(0, 'over the lazy dog', 'The lazy dog jumps over the fox.');
4382 SELECT count(*) = max(a) FROM t1 WHERE m1 = 'The fox jumps over';
4383 count(*) = max(a)
4384 1
4385 SELECT count(*) = max(a) FROM t1 WHERE m2 like 'The fox jumps%';
4386 count(*) = max(a)
4387 1
4388 SELECT m1 FROM t1 WHERE m1 = 'over the lazy dog';
4389 m1
4390 over the lazy dog
4391 SELECT m2 FROM t1 WHERE m2 like '%the fox.';
4392 m2
4393 The lazy dog jumps over the fox.
4394 ALTER TABLE t1 ADD COLUMN n1 BINARY(10), ADD COLUMN n2 VARBINARY(10), ALGORITHM=DEFAULT;
4395 Table id did not change
4396 count(*) = 24
4397 1
4398 CHECK TABLE t1;
4399 Table Op Msg_type Msg_text
4400 test.t1 check status OK
4401 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'n1' OR name = 'n2') AND has_default = 1;
4402 name default_value
4403 n1 NULL
4404 n2 NULL
4405 INSERT INTO t1(a, n1, n2) VALUES(0, 0x010203040506070809, 0x102030405060708090);
4406 SELECT count(*) = max(a) FROM t1 WHERE n1 IS NULL;
4407 count(*) = max(a)
4408 1
4409 SELECT count(*) = max(a) FROM t1 WHERE n2 IS NULL;
4410 count(*) = max(a)
4411 1
4412 SELECT hex(n1) FROM t1 WHERE n1 = 0x01020304050607080900;
4413 hex(n1)
4414 01020304050607080900
4415 SELECT hex(n2) FROM t1 WHERE n2 = 0x102030405060708090;
4416 hex(n2)
4417 102030405060708090
4418 ALTER TABLE t1 ADD COLUMN o1 BINARY(10) DEFAULT 0x11223344, ADD COLUMN o2 VARBINARY(10) DEFAULT 0x55667788;
4419 Table id did not change
4420 count(*) = 26
4421 1
4422 CHECK TABLE t1;
4423 Table Op Msg_type Msg_text
4424 test.t1 check status OK
4425 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'o1' OR name = 'o2') AND has_default = 1;
4426 name default_value
4427 o1 11223344000000000000
4428 o2 55667788
4429 INSERT INTO t1(a, o1, o2) VALUES(0, 0x44332211, 0x88776655);
4430 SELECT count(*) = max(a) FROM t1 WHERE o1 = 0x11223344000000000000;
4431 count(*) = max(a)
4432 1
4433 SELECT count(*) = max(a) FROM t1 WHERE o2 = 0x55667788;
4434 count(*) = max(a)
4435 1
4436 SELECT hex(o1) FROM t1 WHERE o1 = 0x44332211000000000000;
4437 hex(o1)
4438 44332211000000000000
4439 SELECT hex(o2) FROM t1 WHERE o2 = 0x88776655;
4440 hex(o2)
4441 88776655
4442 ALTER TABLE t1 ADD COLUMN p1 DATETIME, ALGORITHM=DEFAULT;
4443 Table id did not change
4444 count(*) = 27
4445 1
4446 CHECK TABLE t1;
4447 Table Op Msg_type Msg_text
4448 test.t1 check status OK
4449 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'p1' AND has_default = 1;
4450 name default_value
4451 p1 NULL
4452 INSERT INTO t1(a, p1) VALUES(0, '2017-12-31 00:00:00');
4453 SELECT count(*) = max(a) FROM t1 WHERE p1 IS NULL;
4454 count(*) = max(a)
4455 1
4456 SELECT p1 FROM t1 WHERE p1 = '2017-12-31 00:00:00';
4457 p1
4458 2017-12-31 00:00:00
4459 ALTER TABLE t1 ADD COLUMN p2 DATETIME NOT NULL DEFAULT '2017-12-31 01:02:03';
4460 Table id did not change
4461 count(*) = 28
4462 1
4463 CHECK TABLE t1;
4464 Table Op Msg_type Msg_text
4465 test.t1 check status OK
4466 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'p2' AND has_default = 1;
4467 name default_value
4468 p2 999e7e1083
4469 SELECT count(*) = max(a) FROM t1 GROUP BY p2;
4470 count(*) = max(a)
4471 1
4472 INSERT INTO t1(a, p2) VALUES(0, now());
4473 SELECT count(*) FROM t1 GROUP BY p2;
4474 count(*)
4475 26
4476 1
4477 ALTER TABLE t1 ADD COLUMN q1 ENUM ('value1','value2','value3'), ALGORITHM=INSTANT;
4478 Table id did not change
4479 count(*) = 29
4480 1
4481 CHECK TABLE t1;
4482 Table Op Msg_type Msg_text
4483 test.t1 check status OK
4484 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'q1' AND has_default = 1;
4485 name default_value
4486 q1 NULL
4487 INSERT INTO t1(a, q1) VALUES(0, 1);
4488 SELECT count(*) = max(a) FROM t1 WHERE q1 IS NULL;
4489 count(*) = max(a)
4490 1
4491 SELECT q1 FROM t1 WHERE q1 = 1;
4492 q1
4493 value1
4494 ALTER TABLE t1 ADD COLUMN r1 SET ('a','b','c'), ALGORITHM=INSTANT;
4495 Table id did not change
4496 count(*) = 30
4497 1
4498 CHECK TABLE t1;
4499 Table Op Msg_type Msg_text
4500 test.t1 check status OK
4501 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'r1' AND has_default = 1;
4502 name default_value
4503 r1 NULL
4504 INSERT INTO t1(a, r1) VALUES(0, 'a');
4505 SELECT count(*) = max(a) FROM t1 WHERE r1 IS NULL;
4506 count(*) = max(a)
4507 1
4508 SELECT r1 FROM t1 WHERE r1 = 'a';
4509 r1
4510 a
4511 ALTER TABLE t1 ADD COLUMN s1 BLOB, ADD COLUMN s2 TEXT, ALGORITHM=INSTANT;
4512 Table id did not change
4513 count(*) = 32
4514 1
4515 CHECK TABLE t1;
4516 Table Op Msg_type Msg_text
4517 test.t1 check status OK
4518 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 's1' OR name = 's2') AND has_default = 1;
4519 name default_value
4520 s1 NULL
4521 s2 NULL
4522 INSERT INTO t1(a, s1, s2) VALUES(0, 0x0102030405, 'abcd qwerty');
4523 SELECT count(*) = max(a) FROM t1 WHERE s1 IS NULL;
4524 count(*) = max(a)
4525 1
4526 SELECT count(*) = max(a) FROM t1 WHERE s2 IS NULL;
4527 count(*) = max(a)
4528 1
4529 SELECT hex(s1) FROM t1 WHERE s1 = 0x0102030405;
4530 hex(s1)
4531 0102030405
4532 SELECT s2 FROM t1 WHERE s2 = 'abcd qwerty';
4533 s2
4534 abcd qwerty
4535 ALTER TABLE t1 ADD COLUMN u1 BLOB NOT NULL, ADD COLUMN u2 TEXT NOT NULL;
4536 Table id did not change
4537 count(*) = 34
4538 1
4539 CHECK TABLE t1;
4540 Table Op Msg_type Msg_text
4541 test.t1 check status OK
4542 SELECT name, default_value FROM information_schema.innodb_columns WHERE (name = 'u1' OR name = 'u2') AND has_default = 1;
4543 name default_value
4544 u1
4545 u2
4546 INSERT INTO t1(a, u1, u2) VALUES(0, 0x0102030405, 'abcd qwerty');
4547 SELECT count(*) = max(a) FROM t1 WHERE u1 = '';
4548 count(*) = max(a)
4549 1
4550 SELECT count(*) = max(a) FROM t1 WHERE u2 = '';
4551 count(*) = max(a)
4552 1
4553 SELECT hex(u1) FROM t1 WHERE u1 = 0x0102030405;
4554 hex(u1)
4555 0102030405
4556 SELECT u2 FROM t1 WHERE u2 = 'abcd qwerty';
4557 u2
4558 abcd qwerty
4559 CHECK TABLE t1;
4560 Table Op Msg_type Msg_text
4561 test.t1 check status OK
4562 SHOW CREATE TABLE t1;
4563 Table Create Table
4564 t1 CREATE TABLE `t1` (
4565 `a` int NOT NULL AUTO_INCREMENT,
4566 `b` int DEFAULT NULL,
4567 `c1` int DEFAULT NULL,
4568 `c2` int NOT NULL DEFAULT '10',
4569 `d1` bigint DEFAULT NULL,
4570 `d2` bigint NOT NULL DEFAULT '1234567890',
4571 `e1` smallint DEFAULT NULL,
4572 `e2` smallint NOT NULL DEFAULT '10',
4573 `f1` tinyint DEFAULT NULL,
4574 `f2` tinyint NOT NULL DEFAULT '123',
4575 `g1` mediumint DEFAULT NULL,
4576 `g2` mediumint NOT NULL DEFAULT '12345',
4577 `h1` float DEFAULT NULL,
4578 `h2` float NOT NULL DEFAULT '12.34',
4579 `i1` decimal(5,2) DEFAULT NULL,
4580 `i2` double DEFAULT NULL,
4581 `j1` decimal(5,2) NOT NULL DEFAULT '100.00',
4582 `j2` double NOT NULL DEFAULT '1000.5678',
4583 `k1` bit(8) DEFAULT NULL,
4584 `k2` bit(8) NOT NULL DEFAULT b'101010',
4585 `l1` char(50) DEFAULT NULL,
4586 `l2` varchar(100) DEFAULT NULL,
4587 `m1` char(50) DEFAULT 'The fox jumps over',
4588 `m2` varchar(50) DEFAULT 'The fox jumps over the lazy dog.',
4589 `n1` binary(10) DEFAULT NULL,
4590 `n2` varbinary(10) DEFAULT NULL,
4591 `o1` binary(10) DEFAULT '"3D\0\0\0\0\0\0',
4592 `o2` varbinary(10) DEFAULT 0x55667788,
4593 `p1` datetime DEFAULT NULL,
4594 `p2` datetime NOT NULL DEFAULT '2017-12-31 01:02:03',
4595 `q1` enum('value1','value2','value3') DEFAULT NULL,
4596 `r1` set('a','b','c') DEFAULT NULL,
4597 `s1` blob,
4598 `s2` text,
4599 `u1` blob NOT NULL,
4600 `u2` text NOT NULL,
4601 PRIMARY KEY (`a`)
4602 ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
4603 DROP TABLE t1;
4604 #
4605 # Scenario 2:
4606 # Create a small table, add some columns instantly, along with
4607 # virtual columns
4608 #
4609 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
4610 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
4611 ALTER TABLE t1 ADD COLUMN c INT NOT NULL, ADD COLUMN d INT GENERATED ALWAYS AS ((b * 2)) VIRTUAL;
4612 Table id did not change
4613 count(*) = 1
4614 1
4615 CHECK TABLE t1;
4616 Table Op Msg_type Msg_text
4617 test.t1 check status OK
4618 INSERT INTO t1(a, b, c) VALUES(0, 6, 20);
4619 SELECT * FROM t1;
4620 a b c d
4621 1 1 0 2
4622 2 2 0 4
4623 3 3 0 6
4624 4 4 0 8
4625 5 5 0 10
4626 6 6 20 12
4627 ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'ABCD EFGH', ADD COLUMN f INT GENERATED ALWAYS AS (LENGTH(e));
4628 Table id did not change
4629 count(*) = 2
4630 1
4631 CHECK TABLE t1;
4632 Table Op Msg_type Msg_text
4633 test.t1 check status OK
4634 INSERT INTO t1(a, c, e) VALUES(0, 20, 'Hello'), (0, 20, 'World'), (0, 20, 'Hello World');
4635 SELECT * FROM t1;
4636 a b c d e f
4637 1 1 0 2 ABCD EFGH 9
4638 2 2 0 4 ABCD EFGH 9
4639 3 3 0 6 ABCD EFGH 9
4640 4 4 0 8 ABCD EFGH 9
4641 5 5 0 10 ABCD EFGH 9
4642 6 6 20 12 ABCD EFGH 9
4643 7 NULL 20 NULL Hello 5
4644 8 NULL 20 NULL World 5
4645 9 NULL 20 NULL Hello World 11
4646 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);
4647 Table id did not change
4648 count(*) = 3
4649 1
4650 CHECK TABLE t1;
4651 Table Op Msg_type Msg_text
4652 test.t1 check status OK
4653 SELECT * FROM t1;
4654 a b c d e f g h i
4655 1 1 0 2 ABCD EFGH 9 ABCD EFGH 10000 20001
4656 2 2 0 4 ABCD EFGH 9 ABCD EFGH 10000 20002
4657 3 3 0 6 ABCD EFGH 9 ABCD EFGH 10000 20003
4658 4 4 0 8 ABCD EFGH 9 ABCD EFGH 10000 20004
4659 5 5 0 10 ABCD EFGH 9 ABCD EFGH 10000 20005
4660 6 6 20 12 ABCD EFGH 9 ABCD EFGH 10000 20006
4661 7 NULL 20 NULL Hello 5 Hello 10000 NULL
4662 8 NULL 20 NULL World 5 World 10000 NULL
4663 9 NULL 20 NULL Hello World 11 Hello World 10000 NULL
4664 INSERT INTO t1(a, b, c, h) VALUES(0, 7, 40, 2000), (0, 7, 40, 20000);
4665 SELECT * FROM t1;
4666 a b c d e f g h i
4667 1 1 0 2 ABCD EFGH 9 ABCD EFGH 10000 20001
4668 10 7 40 14 ABCD EFGH 9 ABCD EFGH 2000 4007
4669 11 7 40 14 ABCD EFGH 9 ABCD EFGH 20000 40007
4670 2 2 0 4 ABCD EFGH 9 ABCD EFGH 10000 20002
4671 3 3 0 6 ABCD EFGH 9 ABCD EFGH 10000 20003
4672 4 4 0 8 ABCD EFGH 9 ABCD EFGH 10000 20004
4673 5 5 0 10 ABCD EFGH 9 ABCD EFGH 10000 20005
4674 6 6 20 12 ABCD EFGH 9 ABCD EFGH 10000 20006
4675 7 NULL 20 NULL Hello 5 Hello 10000 NULL
4676 8 NULL 20 NULL World 5 World 10000 NULL
4677 9 NULL 20 NULL Hello World 11 Hello World 10000 NULL
4678 CHECK TABLE t1;
4679 Table Op Msg_type Msg_text
4680 test.t1 check status OK
4681 SHOW CREATE TABLE t1;
4682 Table Create Table
4683 t1 CREATE TABLE `t1` (
4684 `a` int NOT NULL AUTO_INCREMENT,
4685 `b` int DEFAULT NULL,
4686 `c` int NOT NULL,
4687 `d` int GENERATED ALWAYS AS ((`b` * 2)) VIRTUAL,
4688 `e` varchar(100) DEFAULT 'ABCD EFGH',
4689 `f` int GENERATED ALWAYS AS (length(`e`)) VIRTUAL,
4690 `g` varchar(100) GENERATED ALWAYS AS (`e`) VIRTUAL,
4691 `h` bigint DEFAULT '10000',
4692 `i` bigint GENERATED ALWAYS AS (((`h` * 2) + `b`)) VIRTUAL,
4693 PRIMARY KEY (`a`)
4694 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
4695 DROP TABLE t1;
4696 #
4697 # Scenario 3:
4698 # Create a small table, add some columns instantly, then change
4699 # their default values, check original default values are correct
4700 #
4701 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
4702 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
4703 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';
4704 Table id did not change
4705 count(*) = 2
4706 1
4707 CHECK TABLE t1;
4708 Table Op Msg_type Msg_text
4709 test.t1 check status OK
4710 SELECT * FROM t1;
4711 a b c d e
4712 1 1 100 201 Hello world
4713 2 2 100 202 Hello world
4714 3 3 100 203 Hello world
4715 4 4 100 204 Hello world
4716 5 5 100 205 Hello world
4717 INSERT INTO t1(a, b, c, e) VALUES(0, 6, 200, 'Good day'), (0, 7, 300, 'Good DAY');
4718 SELECT * FROM t1;
4719 a b c d e
4720 1 1 100 201 Hello world
4721 2 2 100 202 Hello world
4722 3 3 100 203 Hello world
4723 4 4 100 204 Hello world
4724 5 5 100 205 Hello world
4725 6 6 200 406 Good day
4726 7 7 300 607 Good DAY
4727 SELECT default_value FROM information_schema.innodb_columns WHERE name = 'c' AND has_default = 1;
4728 default_value
4729 80000064
4730 ALTER TABLE t1 ALTER COLUMN c SET DEFAULT 500;
4731 SELECT default_value FROM information_schema.innodb_columns WHERE name = 'c' AND
4732 has_default = 1;
4733 default_value
4734 80000064
4735 SELECT * FROM t1;
4736 a b c d e
4737 1 1 100 201 Hello world
4738 2 2 100 202 Hello world
4739 3 3 100 203 Hello world
4740 4 4 100 204 Hello world
4741 5 5 100 205 Hello world
4742 6 6 200 406 Good day
4743 7 7 300 607 Good DAY
4744 INSERT INTO t1(a, b) VALUES(0, 8), (0, 9);
4745 SELECT * FROM t1;
4746 a b c d e
4747 1 1 100 201 Hello world
4748 2 2 100 202 Hello world
4749 3 3 100 203 Hello world
4750 4 4 100 204 Hello world
4751 5 5 100 205 Hello world
4752 6 6 200 406 Good day
4753 7 7 300 607 Good DAY
4754 8 8 500 1008 Hello world
4755 9 9 500 1009 Hello world
4756 SELECT default_value FROM information_schema.innodb_columns WHERE name = 'e' AND has_default = 1;
4757 default_value
4758 48656c6c6f20776f726c64
4759 ALTER TABLE t1 ALTER COLUMN e SET DEFAULT 'HELLO MySQL!';
4760 SELECT default_value FROM information_schema.innodb_columns WHERE name = 'e' AND has_default = 1;
4761 default_value
4762 48656c6c6f20776f726c64
4763 SELECT * FROM t1;
4764 a b c d e
4765 1 1 100 201 Hello world
4766 2 2 100 202 Hello world
4767 3 3 100 203 Hello world
4768 4 4 100 204 Hello world
4769 5 5 100 205 Hello world
4770 6 6 200 406 Good day
4771 7 7 300 607 Good DAY
4772 8 8 500 1008 Hello world
4773 9 9 500 1009 Hello world
4774 INSERT INTO t1(a, b) VALUES(0, 10), (0, 20);
4775 SELECT * FROM t1;
4776 a b c d e
4777 1 1 100 201 Hello world
4778 10 10 500 1010 HELLO MySQL!
4779 11 20 500 1020 HELLO MySQL!
4780 2 2 100 202 Hello world
4781 3 3 100 203 Hello world
4782 4 4 100 204 Hello world
4783 5 5 100 205 Hello world
4784 6 6 200 406 Good day
4785 7 7 300 607 Good DAY
4786 8 8 500 1008 Hello world
4787 9 9 500 1009 Hello world
4788 CHECK TABLE t1;
4789 Table Op Msg_type Msg_text
4790 test.t1 check status OK
4791 SHOW CREATE TABLE t1;
4792 Table Create Table
4793 t1 CREATE TABLE `t1` (
4794 `a` int NOT NULL AUTO_INCREMENT,
4795 `b` int DEFAULT NULL,
4796 `c` int NOT NULL DEFAULT '500',
4797 `d` int GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
4798 `e` varchar(100) DEFAULT 'HELLO MySQL!',
4799 PRIMARY KEY (`a`)
4800 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
4801 DROP TABLE t1;
4802 #
4803 # Scenario 4:
4804 # Create a small table, add some columns instantly, then do DML
4805 # on the table
4806 #
4807 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
4808 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
4809 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';
4810 Table id did not change
4811 count(*) = 2
4812 1
4813 CHECK TABLE t1;
4814 Table Op Msg_type Msg_text
4815 test.t1 check status OK
4816 UPDATE t1 SET c = 200 WHERE a > 3;
4817 SELECT distinct(c) FROM t1;
4818 c
4819 100
4820 200
4821 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
4822 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
4823 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
4824 DELETE FROM t1 WHERE c = 100;
4825 UPDATE t1 SET c = 300;
4826 SELECT distinct(c) FROM t1;
4827 c
4828 300
4829 SELECT count(*) FROM t1;
4830 count(*)
4831 16
4832 ALTER TABLE t1 ADD COLUMN t DATETIME DEFAULT CURRENT_TIMESTAMP;
4833 Table id did not change
4834 count(*) = 3
4835 1
4836 CHECK TABLE t1;
4837 Table Op Msg_type Msg_text
4838 test.t1 check status OK
4839 UPDATE t1 SET e = 'Hello MySQL' WHERE a > 10;
4840 UPDATE t1 SET e = 'Hello MySQL!!' WHERE a > 20;
4841 SELECT distinct(e) FROM t1;
4842 e
4843 Hello MySQL
4844 Hello MySQL!!
4845 Hello world
4846 UPDATE t1 SET c = 500 WHERE e LIKE '%world%';
4847 SELECT c, e FROM t1 GROUP BY c, e;
4848 c e
4849 300 Hello MySQL
4850 300 Hello MySQL!!
4851 500 Hello world
4852 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
4853 UPDATE t1 SET t = CURRENT_TIMESTAMP WHERE a < 50;
4854 SELECT count(t) FROM t1 GROUP BY t;
4855 count(t)
4856 16
4857 16
4858 DELETE FROM t1 WHERE a < 50;
4859 SELECT count(t) FROM t1 GROUP BY t;
4860 count(t)
4861 16
4862 CHECK TABLE t1;
4863 Table Op Msg_type Msg_text
4864 test.t1 check status OK
4865 SHOW CREATE TABLE t1;
4866 Table Create Table
4867 t1 CREATE TABLE `t1` (
4868 `a` int NOT NULL AUTO_INCREMENT,
4869 `b` int DEFAULT NULL,
4870 `c` int NOT NULL DEFAULT '100',
4871 `d` int GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
4872 `e` varchar(100) DEFAULT 'Hello world',
4873 `t` datetime DEFAULT CURRENT_TIMESTAMP,
4874 PRIMARY KEY (`a`)
4875 ) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
4876 DROP TABLE t1;
4877 #
4878 # Scenario 5:
4879 # Create a small table, add some columns instantly, then do DDL
4880 # to build indexes
4881 #
4882 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
4883 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
4884 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';
4885 Table id did not change
4886 count(*) = 2
4887 1
4888 CHECK TABLE t1;
4889 Table Op Msg_type Msg_text
4890 test.t1 check status OK
4891 ALTER TABLE t1 ADD KEY(c);
4892 EXPLAIN SELECT c FROM t1;
4893 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
4894 1 SIMPLE t1 NULL index NULL c # NULL # 100.00 Using index
4895 Warnings:
4896 Note 1003 /* select#1 */ select `test`.`t1`.`c` AS `c` from `test`.`t1`
4897 SELECT c FROM t1 WHERE c != 100;
4898 c
4899 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
4900 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
4901 INSERT INTO t1(b, c, e) SELECT b, c, e FROM t1;
4902 UPDATE t1 SET e = 'Hello MySQL' WHERE a > 30;
4903 ALTER TABLE t1 ADD KEY(e);
4904 EXPLAIN SELECT e FROM t1;
4905 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
4906 1 SIMPLE t1 NULL index NULL e # NULL # 100.00 Using index
4907 Warnings:
4908 Note 1003 /* select#1 */ select `test`.`t1`.`e` AS `e` from `test`.`t1`
4909 SELECT count(e) FROM t1 WHERE e LIKE '%MySQL%';
4910 count(e)
4911 17
4912 SELECT count(e) FROM t1 WHERE e LIKE '%world%';
4913 count(e)
4914 23
4915 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(a, c);
4916 SELECT a, c, e FROM t1 WHERE a > 25 AND a < 40;
4917 a c e
4918 28 100 Hello world
4919 29 100 Hello world
4920 30 100 Hello world
4921 31 100 Hello MySQL
4922 32 100 Hello MySQL
4923 33 100 Hello MySQL
4924 34 100 Hello MySQL
4925 35 100 Hello MySQL
4926 36 100 Hello MySQL
4927 37 100 Hello MySQL
4928 38 100 Hello MySQL
4929 39 100 Hello MySQL
4930 CHECK TABLE t1;
4931 Table Op Msg_type Msg_text
4932 test.t1 check status OK
4933 SHOW CREATE TABLE t1;
4934 Table Create Table
4935 t1 CREATE TABLE `t1` (
4936 `a` int NOT NULL AUTO_INCREMENT,
4937 `b` int DEFAULT NULL,
4938 `c` int NOT NULL DEFAULT '100',
4939 `d` int GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
4940 `e` varchar(100) DEFAULT 'Hello world',
4941 PRIMARY KEY (`a`,`c`),
4942 KEY `c` (`c`),
4943 KEY `e` (`e`)
4944 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
4945 DROP TABLE t1;
4946 #
4947 # Scenario 6:
4948 # Create a small table, add some columns instantly, then do DML
4949 # on the table, and some simple rollback
4950 #
4951 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
4952 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
4953 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';
4954 Table id did not change
4955 count(*) = 2
4956 1
4957 CHECK TABLE t1;
4958 Table Op Msg_type Msg_text
4959 test.t1 check status OK
4960 START TRANSACTION;
4961 INSERT INTO t1(a, b) VALUES(0, 6);
4962 SELECT * FROM t1;
4963 a b c d e
4964 1 1 100 201 Hello world
4965 2 2 100 202 Hello world
4966 3 3 100 203 Hello world
4967 4 4 100 204 Hello world
4968 5 5 100 205 Hello world
4969 6 6 100 206 Hello world
4970 ROLLBACK;
4971 SELECT * FROM t1;
4972 a b c d e
4973 1 1 100 201 Hello world
4974 2 2 100 202 Hello world
4975 3 3 100 203 Hello world
4976 4 4 100 204 Hello world
4977 5 5 100 205 Hello world
4978 START TRANSACTION;
4979 UPDATE t1 SET c = 500 WHERE a = 1;
4980 UPDATE t1 SET b = 1000 WHERE a = 2;
4981 SELECT a, b, c FROM t1 WHERE a = 1 OR a = 2;
4982 a b c
4983 1 1 500
4984 2 1000 100
4985 ROLLBACK;
4986 SELECT a, b, c FROM t1;
4987 a b c
4988 1 1 100
4989 2 2 100
4990 3 3 100
4991 4 4 100
4992 5 5 100
4993 START TRANSACTION;
4994 DELETE FROM t1 WHERE a < 5;
4995 INSERT INTO t1(a, b) VALUES(0, 6);
4996 SELECT * FROM t1;
4997 a b c d e
4998 5 5 100 205 Hello world
4999 7 6 100 206 Hello world
5000 ROLLBACK;
5001 SELECT * FROM t1;
5002 a b c d e
5003 1 1 100 201 Hello world
5004 2 2 100 202 Hello world
5005 3 3 100 203 Hello world
5006 4 4 100 204 Hello world
5007 5 5 100 205 Hello world
5008 CHECK TABLE t1;
5009 Table Op Msg_type Msg_text
5010 test.t1 check status OK
5011 SHOW CREATE TABLE t1;
5012 Table Create Table
5013 t1 CREATE TABLE `t1` (
5014 `a` int NOT NULL AUTO_INCREMENT,
5015 `b` int DEFAULT NULL,
5016 `c` int NOT NULL DEFAULT '100',
5017 `d` int GENERATED ALWAYS AS (((`c` * 2) + `b`)) VIRTUAL,
5018 `e` varchar(100) DEFAULT 'Hello world',
5019 PRIMARY KEY (`a`)
5020 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
5021 DROP TABLE t1;
5022 SET SESSION innodb_strict_mode=OFF;
5023 CREATE TABLE t1(id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000)) ROW_FORMAT=COMPACT;
5024 SET SESSION innodb_strict_mode=@saved_innodb_strict_mode;
5025 INSERT INTO t1 VALUES(1, repeat('a', 4000), repeat('b', 4000), repeat('c', 1));
5026 SELECT id, length(c1), length(c2), length(c3) FROM t1;
5027 id length(c1) length(c2) length(c3)
5028 1 4000 4000 1
5029 ALTER TABLE t1 ADD COLUMN c4 VARCHAR(500) NOT NULL DEFAULT 'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd';
5030 Table id did not change
5031 count(*) = 1
5032 1
5033 CHECK TABLE t1;
5034 Table Op Msg_type Msg_text
5035 test.t1 check status OK
5036 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
5037 id length(c1) length(c2) length(c3) length(c4)
5038 1 4000 4000 1 500
5039 START TRANSACTION;
5040 UPDATE t1 SET c1 = repeat('x', 200) WHERE id = 1;
5041 ROLLBACK;
5042 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
5043 id length(c1) length(c2) length(c3) length(c4)
5044 1 4000 4000 1 500
5045 START TRANSACTION;
5046 UPDATE t1 SET c4 = 'x' WHERE id = 1;
5047 ROLLBACK;
5048 SELECT id, length(c1), length(c2), length(c3), length(c4) FROM t1;
5049 id length(c1) length(c2) length(c3) length(c4)
5050 1 4000 4000 1 500
5051 DROP TABLE t1;
5052 #
5053 # Scenario 7:
5054 # Confirm some ADD COLUMN are instant, some are not
5055 #
5056 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
5057 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
5058 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100 AFTER b;
5059 Table id did not change
5060 count(*) = 1
5061 1
5062 CHECK TABLE t1;
5063 Table Op Msg_type Msg_text
5064 test.t1 check status OK
5065 ALTER TABLE t1 ADD COLUMN d INT NOT NULL DEFAULT 100 AFTER b;
5066 Table id did not change
5067 count(*) = 2
5068 1
5069 CHECK TABLE t1;
5070 Table Op Msg_type Msg_text
5071 test.t1 check status OK
5072 SELECT count(*) AS `Expect 2` FROM information_schema.innodb_columns WHERE has_default = 1;
5073 Expect 2
5074 2
5075 ALTER TABLE t1 ADD COLUMN e INT NOT NULL DEFAULT 100, ADD KEY(e);
5076 Table ID differed
5077 count(*) = 0
5078 1
5079 CHECK TABLE t1;
5080 Table Op Msg_type Msg_text
5081 test.t1 check status OK
5082 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
5083 Expect 0
5084 0
5085 ALTER TABLE t1 ADD COLUMN f INT NOT NULL DEFAULT 100, FORCE;
5086 Table ID differed
5087 count(*) = 0
5088 1
5089 CHECK TABLE t1;
5090 Table Op Msg_type Msg_text
5091 test.t1 check status OK
5092 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
5093 Expect 0
5094 0
5095 ALTER TABLE t1 ADD COLUMN g INT NOT NULL DEFAULT 100, ALGORITHM=INPLACE;
5096 Table ID differed
5097 count(*) = 0
5098 1
5099 CHECK TABLE t1;
5100 Table Op Msg_type Msg_text
5101 test.t1 check status OK
5102 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
5103 Expect 0
5104 0
5105 CHECK TABLE t1;
5106 Table Op Msg_type Msg_text
5107 test.t1 check status OK
5108 SHOW CREATE TABLE t1;
5109 Table Create Table
5110 t1 CREATE TABLE `t1` (
5111 `a` int NOT NULL AUTO_INCREMENT,
5112 `b` int DEFAULT NULL,
5113 `d` int NOT NULL DEFAULT '100',
5114 `c` int NOT NULL DEFAULT '100',
5115 `e` int NOT NULL DEFAULT '100',
5116 `f` int NOT NULL DEFAULT '100',
5117 `g` int NOT NULL DEFAULT '100',
5118 PRIMARY KEY (`a`),
5119 KEY `e` (`e`)
5120 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
5121 DROP TABLE t1;
5122 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c TEXT, FULLTEXT(c)) ROW_FORMAT=COMPACT;
5123 INSERT INTO t1 VALUES(0, 1, 'Hello'), (0, 2, 'HELLO'), (0, 3, 'World'), (0, 4, 'Hello world'), (0, 5, 'HELLO WORLD');
5124 ALTER TABLE t1 ADD COLUMN d INT NOT NULL DEFAULT 100 AFTER b;
5125 Table ID differed
5126 count(*) = 0
5127 1
5128 CHECK TABLE t1;
5129 Table Op Msg_type Msg_text
5130 test.t1 check status OK
5131 DROP TABLE t1;
5132 #
5133 # Scenario 8:
5134 # Check FK constraints on instantly added columns
5135 #
5136 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
5137 CREATE TABLE t2(a INT NOT NULL PRIMARY KEY, b INT, c INT, KEY(c));
5138 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
5139 INSERT INTO t2 VALUES(1, 2, 3), (2, 3, 4), (3, 4, 5);
5140 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 3;
5141 Table id did not change
5142 count(*) = 1
5143 1
5144 CHECK TABLE t1;
5145 Table Op Msg_type Msg_text
5146 test.t1 check status OK
5147 UPDATE t1 SET c = 4 WHERE a = 2;
5148 UPDATE t1 SET c = 5 WHERE a = 3;
5149 ALTER TABLE t1 ADD KEY(c);
5150 ALTER TABLE t2 ADD FOREIGN KEY (c) REFERENCES t1(c) ON UPDATE CASCADE, ALGORITHM = COPY;
5151 UPDATE t1 SET c = 10 WHERE a = 1;
5152 SELECT c FROM t2;
5153 c
5154 10
5155 4
5156 5
5157 UPDATE t1 SET c = 10;
5158 SELECT c FROM t2;
5159 c
5160 10
5161 10
5162 10
5163 CHECK TABLE t1;
5164 Table Op Msg_type Msg_text
5165 test.t1 check status OK
5166 SHOW CREATE TABLE t1;
5167 Table Create Table
5168 t1 CREATE TABLE `t1` (
5169 `a` int NOT NULL AUTO_INCREMENT,
5170 `b` int DEFAULT NULL,
5171 `c` int NOT NULL DEFAULT '3',
5172 PRIMARY KEY (`a`),
5173 KEY `c` (`c`)
5174 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
5175 DROP TABLE t2;
5176 DROP TABLE t1;
5177 #
5178 # Scenario 9:
5179 # Instant ADD COLUMN on partitioned table, only simple test here
5180 #
5181 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT PARTITION BY HASH(a) PARTITIONS 3;;
5182 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
5183 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
5184 Table id did not change
5185 count(*) = 1
5186 1
5187 CHECK TABLE t1;
5188 Table Op Msg_type Msg_text
5189 test.t1 check status OK
5190 SELECT * FROM t1;
5191 a b c
5192 1 1 5
5193 2 2 5
5194 3 3 5
5195 4 4 5
5196 5 5 5
5197 6 6 5
5198 7 7 5
5199 8 8 5
5200 INSERT INTO t1 VALUES(0, 9, 10), (0, 10, 20);
5201 SELECT * FROM t1 WHERE b > 8;
5202 a b c
5203 10 10 20
5204 9 9 10
5205 UPDATE t1 SET c = 8 WHERE a = 1 OR a = 3 OR a = 5 OR a = 7;
5206 SELECT * FROM t1;
5207 a b c
5208 1 1 8
5209 10 10 20
5210 2 2 5
5211 3 3 8
5212 4 4 5
5213 5 5 8
5214 6 6 5
5215 7 7 8
5216 8 8 5
5217 9 9 10
5218 CHECK TABLE t1;
5219 Table Op Msg_type Msg_text
5220 test.t1 check status OK
5221 SHOW CREATE TABLE t1;
5222 Table Create Table
5223 t1 CREATE TABLE `t1` (
5224 `a` int NOT NULL AUTO_INCREMENT,
5225 `b` int DEFAULT NULL,
5226 `c` int NOT NULL DEFAULT '5',
5227 PRIMARY KEY (`a`)
5228 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
5229 /*!50100 PARTITION BY HASH (`a`)
5230 PARTITIONS 3 */
5231 DROP TABLE t1;
5232 CREATE TABLE t1 (a INT, b INT) ROW_FORMAT=COMPACT PARTITION BY HASH(a) PARTITIONS 2;;
5233 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
5234 Table id did not change
5235 count(*) = 1
5236 1
5237 CHECK TABLE t1;
5238 Table Op Msg_type Msg_text
5239 test.t1 check status OK
5240 ALTER TABLE t1 CHANGE COLUMN c c1 INT;
5241 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
5242 Table id did not change
5243 count(*) = 1
5244 1
5245 CHECK TABLE t1;
5246 Table Op Msg_type Msg_text
5247 test.t1 check status OK
5248 DROP TABLE t1;
5249 #
5250 # Scenario 10:
5251 # EXCHANGE PARTITION is not allowed if either is instant
5252 #
5253 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;;
5254 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
5255 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
5256 Table id did not change
5257 count(*) = 1
5258 1
5259 CHECK TABLE t1;
5260 Table Op Msg_type Msg_text
5261 test.t1 check status OK
5262 CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c INT NOT NULL DEFAULT 5) ROW_FORMAT=COMPACT PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30));;
5263 ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
5264 ERROR HY000: Non matching attribute 'INSTANT COLUMN(s)' between partition and table
5265 ALTER TABLE t2 ADD COLUMN d INT;
5266 ALTER TABLE t1 ADD COLUMN d INT, ALGORITHM=INPLACE;
5267 ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
5268 ERROR HY000: Non matching attribute 'INSTANT COLUMN(s)' between partition and table
5269 OPTIMIZE TABLE t2;
5270 Table Op Msg_type Msg_text
5271 test.t2 optimize note Table does not support optimize, doing recreate + analyze instead
5272 test.t2 optimize status OK
5273 SHOW CREATE TABLE t1;
5274 Table Create Table
5275 t1 CREATE TABLE `t1` (
5276 `a` int NOT NULL AUTO_INCREMENT,
5277 `b` int DEFAULT NULL,
5278 `c` int NOT NULL DEFAULT '5',
5279 `d` int DEFAULT NULL,
5280 PRIMARY KEY (`a`)
5281 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
5282 SHOW CREATE TABLE t2;
5283 Table Create Table
5284 t2 CREATE TABLE `t2` (
5285 `a` int NOT NULL AUTO_INCREMENT,
5286 `b` int DEFAULT NULL,
5287 `c` int NOT NULL DEFAULT '5',
5288 `d` int DEFAULT NULL,
5289 PRIMARY KEY (`a`)
5290 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
5291 /*!50100 PARTITION BY RANGE (`a`)
5292 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
5293 PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
5294 PARTITION p3 VALUES LESS THAN (30) ENGINE = InnoDB) */
5295 ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
5296 DROP TABLE t1, t2;
5297 #
5298 # Scenario 11:
5299 # PRIMARY KEY with more than one column, at least to verify it works with REDUNDANT
5300 #
5301 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a, b)) ROW_FORMAT=COMPACT;;
5302 INSERT INTO t1 VALUES(0, 1), (1, 2), (2, 3), (3, 4);
5303 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
5304 Table id did not change
5305 count(*) = 1
5306 1
5307 CHECK TABLE t1;
5308 Table Op Msg_type Msg_text
5309 test.t1 check status OK
5310 SELECT * FROM t1;
5311 a b c
5312 0 1 5
5313 1 2 5
5314 2 3 5
5315 3 4 5
5316 UPDATE t1 SET c = b WHERE b <= 2;
5317 CHECK TABLE t1;
5318 Table Op Msg_type Msg_text
5319 test.t1 check status OK
5320 SELECT * FROM t1;
5321 a b c
5322 0 1 1
5323 1 2 2
5324 2 3 5
5325 3 4 5
5326 DROP TABLE t1;
5327 #
5328 # Scenario 12:
5329 # Mix ALTER PARTITION and ALTER TABLE ... INPLACE. This is to check if first partition is not
5330 # instant after ALTER PARTITION, will the metadata be copied correctly
5331 #
5332 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);;
5333 INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg');
5334 ALTER TABLE t1 ALGORITHM DEFAULT, ADD COLUMN col5 VARCHAR(500), ADD COLUMN col6 TEXT;
5335 Table id did not change
5336 count(*) = 2
5337 1
5338 CHECK TABLE t1;
5339 Table Op Msg_type Msg_text
5340 test.t1 check status OK
5341 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
5342 Expect 0
5343 0
5344 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (64), PARTITION p0_b VALUES LESS THAN (128));
5345 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
5346 Expect 0
5347 0
5348 CHECK TABLE t1;
5349 Table Op Msg_type Msg_text
5350 test.t1 check status OK
5351 ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10));
5352 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
5353 Expect 0
5354 0
5355 CHECK TABLE t1;
5356 Table Op Msg_type Msg_text
5357 test.t1 check status OK
5358 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);
5359 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
5360 Expect 0
5361 0
5362 CHECK TABLE t1;
5363 Table Op Msg_type Msg_text
5364 test.t1 check status OK
5365 ALTER TABLE t1 ADD KEY idx3(col3);
5366 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
5367 Expect 0
5368 0
5369 CHECK TABLE t1;
5370 Table Op Msg_type Msg_text
5371 test.t1 check status OK
5372 SELECT * FROM t1;
5373 col1 col2 col3 col4 col5 col6
5374 1 2 3 abcdefg NULL NULL
5375 100 200 300 qwerty NULL NULL
5376 200 300 400 asdfg NULL NULL
5377 DROP TABLE t1;
5378 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT PARTITION BY HASH(a) PARTITIONS 3;;
5379 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
5380 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
5381 Table id did not change
5382 count(*) = 1
5383 1
5384 CHECK TABLE t1;
5385 Table Op Msg_type Msg_text
5386 test.t1 check status OK
5387 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
5388 Expect 3
5389 0
5390 ALTER TABLE t1 ADD PARTITION PARTITIONS 10;
5391 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
5392 Expect 0
5393 0
5394 CHECK TABLE t1;
5395 Table Op Msg_type Msg_text
5396 test.t1 check status OK
5397 ALTER TABLE t1 ADD KEY(b);
5398 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
5399 Expect 0
5400 0
5401 CHECK TABLE t1;
5402 Table Op Msg_type Msg_text
5403 test.t1 check status OK
5404 SELECT * FROM t1;
5405 a b c
5406 1 1 5
5407 2 2 5
5408 3 3 5
5409 4 4 5
5410 5 5 5
5411 6 6 5
5412 7 7 5
5413 8 8 5
5414 DROP TABLE t1;
5415 #
5416 # Scenario 13:
5417 # Create a table with a two level clustered index, do instant ADD COLUMN, then the non-leaf node
5418 # should be parsed correctly
5419 #
5420 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=COMPACT PARTITION BY LINEAR KEY(c) PARTITIONS 9;;
5421 INSERT INTO t1(d, b, c) VALUES(1, 2, 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhjjjjjjj');
5422 INSERT INTO t1(d, b, c) VALUES(2, 3, 'aaaaaaaaaahhhhhhhhhhbbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
5423 INSERT INTO t1(d, b, c) VALUES(3, 4, 'bbbbbbbbbbaaaaaaaaaahhhhhhhhhhccccccccccddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
5424 INSERT INTO t1(d, b, c) VALUES(4, 5, 'eeeeeeeeeehhhhhhhhhhbbbbbbbbbbccccccccccddddddddddaaaaaaaaaaffffffffffggggggggggjjjjjjj');
5425 INSERT INTO t1(d, b, c) VALUES(5, 6, 'aaaaaaaaaahhhhhhhhhhbbbbbbbbbbddddddddddcccccccccceeeeeeeeeeffffffffffggggggggggjjjjjjj');
5426 INSERT INTO t1(d, b, c) VALUES(6, 7, 'cccccccccchhhhhhhhhhbbbbbbbbbbaaaaaaaaaaddddddddddeeeeeeeeeeffffffffffggggggggggjjjjjjj');
5427 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
5428 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
5429 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
5430 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
5431 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
5432 INSERT INTO t1(d, b, c) SELECT d, b, c FROM t1;
5433 ALTER TABLE t1 ADD COLUMN nc086 BIGINT NOT NULL FIRST, ALGORITHM=INPLACE, LOCK=DEFAULT;
5434 CHECK TABLE t1;
5435 Table Op Msg_type Msg_text
5436 test.t1 check status OK
5437 ALTER TABLE t1 ADD COLUMN nc082 TINYTEXT;
5438 CHECK TABLE t1;
5439 Table Op Msg_type Msg_text
5440 test.t1 check status OK
5441 SELECT COUNT(*) FROM t1;
5442 COUNT(*)
5443 384
5444 DROP TABLE t1;
5445 #
5446 # Scenario 14:
5447 # Create a small table, and add GIS kinds of new columns and verify
5448 #
5449 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
5450 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
5451 ALTER TABLE t1 ADD COLUMN c1 POINT, ALGORITHM = INSTANT;
5452 Table id did not change
5453 count(*) = 1
5454 1
5455 CHECK TABLE t1;
5456 Table Op Msg_type Msg_text
5457 test.t1 check status OK
5458 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
5459 name default_value
5460 c1 NULL
5461 INSERT INTO t1(a, c1) VALUES(0, ST_PointFromText('POINT(10 10)'));
5462 SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
5463 count(*) = max(a)
5464 1
5465 ALTER TABLE t1 ADD COLUMN d1 LINESTRING, ALGORITHM = INSTANT;
5466 Table id did not change
5467 count(*) = 2
5468 1
5469 CHECK TABLE t1;
5470 Table Op Msg_type Msg_text
5471 test.t1 check status OK
5472 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'd1' AND has_default = 1;
5473 name default_value
5474 d1 NULL
5475 INSERT INTO t1(a, d1) VALUES(0, ST_LineFromText('LINESTRING(0 0,0 10,10 0)'));
5476 SELECT count(*) = max(a) FROM t1 WHERE d1 IS NULL;
5477 count(*) = max(a)
5478 1
5479 ALTER TABLE t1 ADD COLUMN e1 POLYGON, ALGORITHM = INSTANT;
5480 Table id did not change
5481 count(*) = 3
5482 1
5483 CHECK TABLE t1;
5484 Table Op Msg_type Msg_text
5485 test.t1 check status OK
5486 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'e1' AND has_default = 1;
5487 name default_value
5488 e1 NULL
5489 INSERT INTO t1(a, e1) VALUES(0, ST_PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))'));
5490 SELECT count(*) = max(a) FROM t1 WHERE e1 IS NULL;
5491 count(*) = max(a)
5492 1
5493 ALTER TABLE t1 ADD COLUMN f1 MULTIPOINT, ALGORITHM = INSTANT;
5494 Table id did not change
5495 count(*) = 4
5496 1
5497 CHECK TABLE t1;
5498 Table Op Msg_type Msg_text
5499 test.t1 check status OK
5500 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'f1' AND has_default = 1;
5501 name default_value
5502 f1 NULL
5503 INSERT INTO t1(a, f1) VALUES(0, ST_MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)'));
5504 SELECT count(*) = max(a) FROM t1 WHERE f1 IS NULL;
5505 count(*) = max(a)
5506 1
5507 ALTER TABLE t1 ADD COLUMN g1 MULTILINESTRING, ALGORITHM = INSTANT;
5508 Table id did not change
5509 count(*) = 5
5510 1
5511 CHECK TABLE t1;
5512 Table Op Msg_type Msg_text
5513 test.t1 check status OK
5514 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'g1' AND has_default = 1;
5515 name default_value
5516 g1 NULL
5517 INSERT INTO t1(a, g1) VALUES(0, ST_MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))'));
5518 SELECT count(*) = max(a) FROM t1 WHERE g1 IS NULL;
5519 count(*) = max(a)
5520 1
5521 ALTER TABLE t1 ADD COLUMN h1 MULTIPOLYGON, ALGORITHM = INSTANT;
5522 Table id did not change
5523 count(*) = 6
5524 1
5525 CHECK TABLE t1;
5526 Table Op Msg_type Msg_text
5527 test.t1 check status OK
5528 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'h1' AND has_default = 1;
5529 name default_value
5530 h1 NULL
5531 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)))'));
5532 SELECT count(*) = max(a) FROM t1 WHERE h1 IS NULL;
5533 count(*) = max(a)
5534 1
5535 ALTER TABLE t1 ADD COLUMN i1 GEOMETRYCOLLECTION, ALGORITHM = INSTANT;
5536 Table id did not change
5537 count(*) = 7
5538 1
5539 CHECK TABLE t1;
5540 Table Op Msg_type Msg_text
5541 test.t1 check status OK
5542 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'i1' AND has_default = 1;
5543 name default_value
5544 i1 NULL
5545 INSERT INTO t1(a, i1) VALUES(0, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'));
5546 SELECT count(*) = max(a) FROM t1 WHERE i1 IS NULL;
5547 count(*) = max(a)
5548 1
5549 ALTER TABLE t1 ADD COLUMN j1 GEOMETRY, ALGORITHM = INSTANT;
5550 Table id did not change
5551 count(*) = 8
5552 1
5553 CHECK TABLE t1;
5554 Table Op Msg_type Msg_text
5555 test.t1 check status OK
5556 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'j1' AND has_default = 1;
5557 name default_value
5558 j1 NULL
5559 INSERT INTO t1(a, j1) VALUES(0, ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'));
5560 SELECT count(*) = max(a) FROM t1 WHERE j1 IS NULL;
5561 count(*) = max(a)
5562 1
5563 CHECK TABLE t1;
5564 Table Op Msg_type Msg_text
5565 test.t1 check status OK
5566 SHOW CREATE TABLE t1;
5567 Table Create Table
5568 t1 CREATE TABLE `t1` (
5569 `a` int NOT NULL AUTO_INCREMENT,
5570 `b` int DEFAULT NULL,
5571 `c1` point DEFAULT NULL,
5572 `d1` linestring DEFAULT NULL,
5573 `e1` polygon DEFAULT NULL,
5574 `f1` multipoint DEFAULT NULL,
5575 `g1` multilinestring DEFAULT NULL,
5576 `h1` multipolygon DEFAULT NULL,
5577 `i1` geomcollection DEFAULT NULL,
5578 `j1` geometry DEFAULT NULL,
5579 PRIMARY KEY (`a`)
5580 ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
5581 DROP TABLE t1;
5582 #
5583 # Scenario 15:
5584 # Create a small table, and add JSON columns and verify
5585 #
5586 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
5587 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
5588 ALTER TABLE t1 ADD COLUMN c1 JSON, ALGORITHM = INSTANT;
5589 Table id did not change
5590 count(*) = 1
5591 1
5592 CHECK TABLE t1;
5593 Table Op Msg_type Msg_text
5594 test.t1 check status OK
5595 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
5596 name default_value
5597 c1 NULL
5598 INSERT INTO t1(a, c1) VALUES(0, '{"key1": "value1", "key2": "value2"}');
5599 SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
5600 count(*) = max(a)
5601 1
5602 CHECK TABLE t1;
5603 Table Op Msg_type Msg_text
5604 test.t1 check status OK
5605 SHOW CREATE TABLE t1;
5606 Table Create Table
5607 t1 CREATE TABLE `t1` (
5608 `a` int NOT NULL AUTO_INCREMENT,
5609 `b` int DEFAULT NULL,
5610 `c1` json DEFAULT NULL,
5611 PRIMARY KEY (`a`)
5612 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
5613 DROP TABLE t1;
5614 #
5615 # Scenario 16:
5616 # Create a small table, and add INSTANT columns and verify with trigger
5617 #
5618 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
5619 CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
5620 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
5621 INSERT INTO t2 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
5622 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
5623 Table id did not change
5624 count(*) = 1
5625 1
5626 CHECK TABLE t1;
5627 Table Op Msg_type Msg_text
5628 test.t1 check status OK
5629 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
5630 name default_value
5631 c1 61616161
5632 CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
5633 INSERT INTO t2 VALUES(0,6);
5634 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
5635 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
5636 count(*) = max(a)
5637 1
5638 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
5639 c1
5640 bbbb
5641 DROP TRIGGER t1_ai;
5642 ALTER TABLE t2 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'cccc', ALGORITHM = INSTANT;
5643 Table id did not change
5644 count(*) = 1
5645 1
5646 CHECK TABLE t1;
5647 Table Op Msg_type Msg_text
5648 test.t1 check status OK
5649 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
5650 name default_value
5651 c1 61616161
5652 c1 63636363
5653 CREATE TRIGGER t2_ai AFTER INSERT ON t2 FOR EACH ROW
5654 INSERT INTO t1(a,c1) VALUES(0,'eeee');
5655 INSERT INTO t2(a, c1) VALUES(0, 'dddd');
5656 SELECT count(*) = max(a) FROM t2 WHERE c1='cccc';
5657 count(*) = max(a)
5658 1
5659 SELECT c1 FROM t2 WHERE c1 = 'dddd';
5660 c1
5661 dddd
5662 DROP TRIGGER t2_ai;
5663 CHECK TABLE t1;
5664 Table Op Msg_type Msg_text
5665 test.t1 check status OK
5666 CHECK TABLE t2;
5667 Table Op Msg_type Msg_text
5668 test.t2 check status OK
5669 SHOW CREATE TABLE t1;
5670 Table Create Table
5671 t1 CREATE TABLE `t1` (
5672 `a` int NOT NULL AUTO_INCREMENT,
5673 `b` int DEFAULT NULL,
5674 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
5675 PRIMARY KEY (`a`)
5676 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
5677 SHOW CREATE TABLE t2;
5678 Table Create Table
5679 t2 CREATE TABLE `t2` (
5680 `a` int NOT NULL AUTO_INCREMENT,
5681 `b` int DEFAULT NULL,
5682 `c1` varchar(10) NOT NULL DEFAULT 'cccc',
5683 PRIMARY KEY (`a`)
5684 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
5685 DROP TABLE t1,t2;
5686 #
5687 # Scenario 17:
5688 # Create a small table, and add INSTANT columns and verify with storedprocedure
5689 #
5690 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
5691 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
5692 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
5693 Table id did not change
5694 count(*) = 1
5695 1
5696 CHECK TABLE t1;
5697 Table Op Msg_type Msg_text
5698 test.t1 check status OK
5699 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
5700 name default_value
5701 c1 61616161
5702 CREATE PROCEDURE p1() INSERT INTO t1(a,c1) VALUES(0, 'bbbb');
5703 CALL p1();
5704 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
5705 count(*) = max(a)
5706 1
5707 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
5708 c1
5709 bbbb
5710 DROP PROCEDURE p1;
5711 CHECK TABLE t1;
5712 Table Op Msg_type Msg_text
5713 test.t1 check status OK
5714 SHOW CREATE TABLE t1;
5715 Table Create Table
5716 t1 CREATE TABLE `t1` (
5717 `a` int NOT NULL AUTO_INCREMENT,
5718 `b` int DEFAULT NULL,
5719 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
5720 PRIMARY KEY (`a`)
5721 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
5722 DROP TABLE t1;
5723 #
5724 # Scenario 18:
5725 # Create a small table, and add INSTANT columns and verify with view
5726 #
5727 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
5728 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
5729 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
5730 Table id did not change
5731 count(*) = 1
5732 1
5733 CHECK TABLE t1;
5734 Table Op Msg_type Msg_text
5735 test.t1 check status OK
5736 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
5737 name default_value
5738 c1 61616161
5739 CREATE VIEW v1 AS SELECT * FROM t1;
5740 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
5741 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
5742 count(*) = max(a)
5743 1
5744 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
5745 c1
5746 bbbb
5747 SELECT * FROM v1;
5748 a b c1
5749 1 1 aaaa
5750 2 2 aaaa
5751 3 3 aaaa
5752 4 4 aaaa
5753 5 5 aaaa
5754 6 NULL bbbb
5755 DROP VIEW v1;
5756 CHECK TABLE t1;
5757 Table Op Msg_type Msg_text
5758 test.t1 check status OK
5759 SHOW CREATE TABLE t1;
5760 Table Create Table
5761 t1 CREATE TABLE `t1` (
5762 `a` int NOT NULL AUTO_INCREMENT,
5763 `b` int DEFAULT NULL,
5764 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
5765 PRIMARY KEY (`a`)
5766 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
5767 DROP TABLE t1;
5768 #
5769 # Scenario 19:
5770 # Create a small table, and add INSTANT columns and drop it and verify
5771 #
5772 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
5773 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
5774 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
5775 Table id did not change
5776 count(*) = 1
5777 1
5778 CHECK TABLE t1;
5779 Table Op Msg_type Msg_text
5780 test.t1 check status OK
5781 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
5782 name default_value
5783 c1 61616161
5784 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
5785 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
5786 count(*) = max(a)
5787 1
5788 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
5789 c1
5790 bbbb
5791 ALTER TABLE t1 DROP COLUMN c1;
5792 Table id did not change
5793 count(*) = 0
5794 1
5795 CHECK TABLE t1;
5796 Table Op Msg_type Msg_text
5797 test.t1 check status OK
5798 CHECK TABLE t1;
5799 Table Op Msg_type Msg_text
5800 test.t1 check status OK
5801 SHOW CREATE TABLE t1;
5802 Table Create Table
5803 t1 CREATE TABLE `t1` (
5804 `a` int NOT NULL AUTO_INCREMENT,
5805 `b` int DEFAULT NULL,
5806 PRIMARY KEY (`a`)
5807 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
5808 DROP TABLE t1;
5809 #
5810 # Scenario 20:
5811 # Create a small table, and add INSTANT columns and rename table
5812 #
5813 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
5814 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
5815 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
5816 Table id did not change
5817 count(*) = 1
5818 1
5819 CHECK TABLE t1;
5820 Table Op Msg_type Msg_text
5821 test.t1 check status OK
5822 ALTER TABLE t1 RENAME t2;
5823 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
5824 name default_value
5825 c1 61616161
5826 INSERT INTO t2(a, c1) VALUES(0, 'bbbb');
5827 SELECT count(*) = max(a) FROM t2 WHERE c1='aaaa';
5828 count(*) = max(a)
5829 1
5830 SELECT c1 FROM t2 WHERE c1 = 'bbbb';
5831 c1
5832 bbbb
5833 CHECK TABLE t2;
5834 Table Op Msg_type Msg_text
5835 test.t2 check status OK
5836 SHOW CREATE TABLE t2;
5837 Table Create Table
5838 t2 CREATE TABLE `t2` (
5839 `a` int NOT NULL AUTO_INCREMENT,
5840 `b` int DEFAULT NULL,
5841 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
5842 PRIMARY KEY (`a`)
5843 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
5844 DROP TABLE t2;
5845 #
5846 # Scenario 21:
5847 # Create a small table, and add INSTANT columns and change its data type INSTANTly won't work
5848 #
5849 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
5850 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
5851 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
5852 Table id did not change
5853 count(*) = 1
5854 1
5855 CHECK TABLE t1;
5856 Table Op Msg_type Msg_text
5857 test.t1 check status OK
5858 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
5859 name default_value
5860 c1 61616161
5861 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
5862 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
5863 count(*) = max(a)
5864 1
5865 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
5866 c1
5867 bbbb
5868 ALTER TABLE t1 CHANGE c1 c2 CHAR(10) NOT NULL DEFAULT 'cccc';
5869 Table ID differed
5870 count(*) = 0
5871 1
5872 CHECK TABLE t1;
5873 Table Op Msg_type Msg_text
5874 test.t1 check status OK
5875 CHECK TABLE t1;
5876 Table Op Msg_type Msg_text
5877 test.t1 check status OK
5878 SHOW CREATE TABLE t1;
5879 Table Create Table
5880 t1 CREATE TABLE `t1` (
5881 `a` int NOT NULL AUTO_INCREMENT,
5882 `b` int DEFAULT NULL,
5883 `c2` char(10) NOT NULL DEFAULT 'cccc',
5884 PRIMARY KEY (`a`)
5885 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
5886 DROP TABLE t1;
5887 #
5888 # Scenario 22:
5889 # Create a small table, and add INSTANT columns and create hash,btree multi column index and verify
5890 #
5891 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
5892 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
5893 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
5894 Table id did not change
5895 count(*) = 1
5896 1
5897 CHECK TABLE t1;
5898 Table Op Msg_type Msg_text
5899 test.t1 check status OK
5900 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
5901 name default_value
5902 c1 61616161
5903 CREATE INDEX id1 ON t1(c1) USING BTREE;
5904 CREATE INDEX id2 ON t1(c1) USING HASH;
5905 Warnings:
5906 Note 3502 This storage engine does not support the HASH index algorithm, storage engine default was used instead.
5907 Warning 1831 Duplicate index 'id2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
5908 INSERT INTO t1(a, c1) VALUES(0, 'bbbb');
5909 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
5910 count(*) = max(a)
5911 1
5912 SELECT c1 FROM t1 WHERE c1 = 'bbbb';
5913 c1
5914 bbbb
5915 DROP INDEX id1 ON t1;
5916 DROP INDEX id2 ON t1;
5917 ALTER TABLE t1 ADD COLUMN c2 VARCHAR(10) NOT NULL DEFAULT 'cccc', ALGORITHM = INSTANT;
5918 Table id did not change
5919 count(*) = 2
5920 1
5921 CHECK TABLE t1;
5922 Table Op Msg_type Msg_text
5923 test.t1 check status OK
5924 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c2' AND has_default = 1;
5925 name default_value
5926 c2 63636363
5927 CREATE INDEX id1 ON t1(c1 ASC,c2 DESC) USING BTREE;
5928 INSERT INTO t1(a, c2) VALUES(0, 'dddd');
5929 SELECT count(*) = max(a) FROM t1 WHERE c1='cccc';
5930 count(*) = max(a)
5931 NULL
5932 SELECT c1 FROM t1 WHERE c1 = 'dddd';
5933 c1
5934 ALTER TABLE t1 RENAME INDEX id1 TO id2;
5935 DROP INDEX id2 ON t1;
5936 CHECK TABLE t1;
5937 Table Op Msg_type Msg_text
5938 test.t1 check status OK
5939 SHOW CREATE TABLE t1;
5940 Table Create Table
5941 t1 CREATE TABLE `t1` (
5942 `a` int NOT NULL AUTO_INCREMENT,
5943 `b` int DEFAULT NULL,
5944 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
5945 `c2` varchar(10) NOT NULL DEFAULT 'cccc',
5946 PRIMARY KEY (`a`)
5947 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
5948 DROP TABLE t1;
5949 #
5950 # Scenario 23:
5951 # Create a small table, and add INSTANT columns and perform table join operation
5952 #
5953 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
5954 CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
5955 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
5956 INSERT INTO t2 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
5957 ALTER TABLE t1 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'aaaa', ALGORITHM = INSTANT;
5958 Table id did not change
5959 count(*) = 1
5960 1
5961 CHECK TABLE t1;
5962 Table Op Msg_type Msg_text
5963 test.t1 check status OK
5964 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
5965 name default_value
5966 c1 61616161
5967 INSERT INTO t1(a, c1) VALUES(0, 'cccc');
5968 SELECT count(*) = max(a) FROM t1 WHERE c1='aaaa';
5969 count(*) = max(a)
5970 1
5971 SELECT c1 FROM t1 WHERE c1 = 'cccc';
5972 c1
5973 cccc
5974 ALTER TABLE t2 ADD COLUMN c1 VARCHAR(10) NOT NULL DEFAULT 'bbbb', ALGORITHM = INSTANT;
5975 Table id did not change
5976 count(*) = 1
5977 1
5978 CHECK TABLE t1;
5979 Table Op Msg_type Msg_text
5980 test.t1 check status OK
5981 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
5982 name default_value
5983 c1 61616161
5984 c1 62626262
5985 INSERT INTO t2(a, c1) VALUES(0, 'cccc');
5986 SELECT count(*) = max(a) FROM t2 WHERE c1='bbbb';
5987 count(*) = max(a)
5988 1
5989 SELECT c1 FROM t2 WHERE c1 = 'cccc';
5990 c1
5991 cccc
5992 SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1;
5993 a b c1 a b c1
5994 6 NULL cccc 6 NULL cccc
5995 CHECK TABLE t1;
5996 Table Op Msg_type Msg_text
5997 test.t1 check status OK
5998 CHECK TABLE t2;
5999 Table Op Msg_type Msg_text
6000 test.t2 check status OK
6001 SHOW CREATE TABLE t1;
6002 Table Create Table
6003 t1 CREATE TABLE `t1` (
6004 `a` int NOT NULL AUTO_INCREMENT,
6005 `b` int DEFAULT NULL,
6006 `c1` varchar(10) NOT NULL DEFAULT 'aaaa',
6007 PRIMARY KEY (`a`)
6008 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
6009 SHOW CREATE TABLE t2;
6010 Table Create Table
6011 t2 CREATE TABLE `t2` (
6012 `a` int NOT NULL AUTO_INCREMENT,
6013 `b` int DEFAULT NULL,
6014 `c1` varchar(10) NOT NULL DEFAULT 'bbbb',
6015 PRIMARY KEY (`a`)
6016 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
6017 DROP TABLE t1;
6018 DROP TABLE t2;
6019 #
6020 # Scenario 24:
6021 # Create a small table, and add stored and(or) virtual columns
6022 # after last stored column in the table
6023 #
6024 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=COMPACT;
6025 INSERT INTO t1(a, b, c) VALUES(0, 1, 2), (0, 2, 3), (0, 3, 4), (0, 4, 5), (0, 5, 6);
6026 ALTER TABLE t1 ADD COLUMN h INT NOT NULL AFTER c;
6027 Table id did not change
6028 count(*) = 1
6029 1
6030 CHECK TABLE t1;
6031 Table Op Msg_type Msg_text
6032 test.t1 check status OK
6033 INSERT INTO t1(a, b, c, h) VALUES(0, 6, 20, 40);
6034 SELECT * FROM t1;
6035 a b c h d e
6036 1 1 2 0 3 2
6037 2 2 3 0 5 6
6038 3 3 4 0 7 12
6039 4 4 5 0 9 20
6040 5 5 6 0 11 30
6041 6 6 20 40 26 120
6042 ALTER TABLE t1 ADD COLUMN i VARCHAR(100) DEFAULT 'ABCD EFGH' AFTER h, ADD COLUMN f INT GENERATED ALWAYS AS (LENGTH(i)) AFTER i;
6043 Table id did not change
6044 count(*) = 2
6045 1
6046 CHECK TABLE t1;
6047 Table Op Msg_type Msg_text
6048 test.t1 check status OK
6049 INSERT INTO t1(a, b, c, h, i) VALUES(0, 20, 30, 50, 'qwerty');
6050 SELECT * FROM t1;
6051 a b c h i f d e
6052 1 1 2 0 ABCD EFGH 9 3 2
6053 2 2 3 0 ABCD EFGH 9 5 6
6054 3 3 4 0 ABCD EFGH 9 7 12
6055 4 4 5 0 ABCD EFGH 9 9 20
6056 5 5 6 0 ABCD EFGH 9 11 30
6057 6 6 20 40 ABCD EFGH 9 26 120
6058 7 20 30 50 qwerty 6 50 600
6059 CHECK TABLE t1;
6060 Table Op Msg_type Msg_text
6061 test.t1 check status OK
6062 SHOW CREATE TABLE t1;
6063 Table Create Table
6064 t1 CREATE TABLE `t1` (
6065 `a` int NOT NULL AUTO_INCREMENT,
6066 `b` int DEFAULT NULL,
6067 `c` int DEFAULT NULL,
6068 `h` int NOT NULL,
6069 `i` varchar(100) DEFAULT 'ABCD EFGH',
6070 `f` int GENERATED ALWAYS AS (length(`i`)) VIRTUAL,
6071 `d` int GENERATED ALWAYS AS ((`b` + `c`)) VIRTUAL,
6072 `e` int GENERATED ALWAYS AS ((`b` * `c`)) VIRTUAL,
6073 PRIMARY KEY (`a`)
6074 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
6075 DROP TABLE t1;
6076 #
6077 # Test cases which don't care about row format start here
6078 #
6079 #
6080 # Bug #28040201 ADDING A NEW COLUMN TO A TABLE HAVING A GENRATED COLUMN
6081 # CRASHES THE SERVER
6082 #
6083 CREATE TABLE t1 (a TEXT GENERATED ALWAYS AS (lpad(1, 1, 1)) VIRTUAL);
6084 ALTER TABLE t1 ADD COLUMN (b INT);
6085 SHOW CREATE TABLE t1;
6086 Table Create Table
6087 t1 CREATE TABLE `t1` (
6088 `a` text GENERATED ALWAYS AS (lpad(1,1,_utf8mb4'1')) VIRTUAL,
6089 `b` int DEFAULT NULL
6090 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
6091 DROP TABLE t1;
6092 #
6093 # BUG#27784462 - DD::COLUMN::SE_PRIVATE_DATA[TABLE_ID] IS NOT MAINTAINED PROPERLY
6094 #
6095 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c INT NOT NULL DEFAULT 5, e INT GENERATED ALWAYS AS ((c + 2)) VIRTUAL) PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30));
6096 ALTER TABLE t1 REORGANIZE PARTITION p1 INTO (PARTITION p1_a VALUES LESS THAN (5), PARTITION p1_b VALUES LESS THAN (10));
6097 ALTER TABLE t1 ADD COLUMN d INT GENERATED ALWAYS AS ((b * 2)) VIRTUAL, ALGORITHM = INSTANT;
6098 DROP TABLE t1;
6099 #
6100 # Check adding a stored columns(along with virtual columns)
6101 # after the last stored column of a table with functional index
6102 # should be fine
6103 #
6104 CREATE TABLE t1 (col1 INT, col2 INT AS (col1 + col1), INDEX (col2));
6105 INSERT INTO t1(col1) VALUES(10), (20), (30), (40);
6106 ALTER TABLE t1 ADD COLUMN new_col INT AFTER col1, ALGORITHM=INSTANT;
6107 SELECT * FROM t1;
6108 col1 new_col col2
6109 10 NULL 20
6110 20 NULL 40
6111 30 NULL 60
6112 40 NULL 80
6113 ALTER TABLE t1 ADD COLUMN new_col_1 INT AFTER new_col, ADD COLUMN new_col_2 INT GENERATED ALWAYS AS (new_col * 3), ALGORITHM=INSTANT;
6114 INSERT INTO t1(col1, new_col, new_col_1) VALUES(100, 200, 300);
6115 SELECT * FROM t1;
6116 col1 new_col new_col_1 col2 new_col_2
6117 10 NULL NULL 20 NULL
6118 100 200 300 200 600
6119 20 NULL NULL 40 NULL
6120 30 NULL NULL 60 NULL
6121 40 NULL NULL 80 NULL
6122 SHOW CREATE TABLE t1;
6123 Table Create Table
6124 t1 CREATE TABLE `t1` (
6125 `col1` int DEFAULT NULL,
6126 `new_col` int DEFAULT NULL,
6127 `new_col_1` int DEFAULT NULL,
6128 `col2` int GENERATED ALWAYS AS ((`col1` + `col1`)) VIRTUAL,
6129 `new_col_2` int GENERATED ALWAYS AS ((`new_col` * 3)) VIRTUAL,
6130 KEY `col2` (`col2`)
6131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
6132 CHECK TABLE t1;
6133 Table Op Msg_type Msg_text
6134 test.t1 check status OK
6135 DROP TABLE t1;
6136 # Bug #33673961: data is incorrect after instant add column
6137 CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a char(10));
6138 INSERT INTO t1 VALUES(1, 'aa');
6139 INSERT INTO t1 VALUES(2, 'bb');
6140 ALTER TABLE t1 ADD b VARCHAR(20), ALGORITHM=INSTANT;
6141 INSERT INTO t1 VALUES(3, 'cc', 'dd');
6142 DROP TABLE t1;
6143 CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a char(10));
6144 INSERT INTO t1 VALUES(1, 'aa');
6145 INSERT INTO t1 VALUES(2, 'bb');
6146 ALTER TABLE t1 ADD b VARCHAR(20), ALGORITHM=COPY;
6147 INSERT INTO t1 VALUES(3, 'cc', 'dd');
6148 CHECKSUM is same
6149 DROP TABLE t1;