Debian Codegraph @

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

raw
1 ##############################################
2 # Test instant ADD COLUMN for REDUNDANT format
3 ##############################################
4 #
5 # Scenario 1:
6 # Create a normal table, rebuild and truncate will clear the instant
7 # information
8 #
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 DEFAULT 10;
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 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 20, ADD KEY(c2);
19 Table ID differed
20 count(*) = 0
21 1
22 CHECK TABLE t1;
23 Table Op Msg_type Msg_text
24 test.t1 check status OK
25 SELECT count(*) FROM information_schema.innodb_tables WHERE instant_cols != 0;
26 count(*)
27 0
28 SELECT count(*) FROM information_schema.innodb_columns WHERE has_default = 1;
29 count(*)
30 0
31 INSERT INTO t1(a, b) VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
32 ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 10;
33 Table id did not change
34 count(*) = 1
35 1
36 CHECK TABLE t1;
37 Table Op Msg_type Msg_text
38 test.t1 check status OK
39 TRUNCATE TABLE t1;
40 Table ID differed
41 count(*) = 0
42 1
43 CHECK TABLE t1;
44 Table Op Msg_type Msg_text
45 test.t1 check status OK
46 SELECT count(*) FROM information_schema.innodb_tables WHERE instant_cols != 0;
47 count(*)
48 0
49 SELECT count(*) FROM information_schema.innodb_columns WHERE has_default = 1;
50 count(*)
51 0
52 CHECK TABLE t1;
53 Table Op Msg_type Msg_text
54 test.t1 check status OK
55 SHOW CREATE TABLE t1;
56 Table Create Table
57 t1 CREATE TABLE `t1` (
58 `a` int NOT NULL AUTO_INCREMENT,
59 `b` int DEFAULT NULL,
60 `c1` int DEFAULT '10',
61 `c2` int DEFAULT '20',
62 `c3` int DEFAULT '10',
63 PRIMARY KEY (`a`),
64 KEY `c2` (`c2`)
65 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
66 DROP TABLE t1;
67 #
68 # Scenario 2:
69 # Create a partitioned table, rebuild and truncate will clear the instant
70 # information
71 #
72 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT PARTITION BY HASH(a) PARTITIONS 3;;
73 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
74 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
75 Expect 0
76 0
77 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
78 Expect 0
79 0
80 ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 10;
81 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
82 Expect 0
83 0
84 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
85 Expect 3
86 3
87 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 20, ALGORITHM = COPY;
88 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
89 Expect 0
90 0
91 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
92 Expect 0
93 0
94 INSERT INTO t1(b, c1, c2) SELECT b, c1, c2 FROM t1;
95 ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 30;
96 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
97 Expect 0
98 0
99 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
100 Expect 3
101 3
102 TRUNCATE TABLE t1;
103 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
104 Expect 0
105 0
106 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
107 Expect 0
108 0
109 CHECK TABLE t1;
110 Table Op Msg_type Msg_text
111 test.t1 check status OK
112 SHOW CREATE TABLE t1;
113 Table Create Table
114 t1 CREATE TABLE `t1` (
115 `a` int NOT NULL AUTO_INCREMENT,
116 `b` int DEFAULT NULL,
117 `c1` int DEFAULT '10',
118 `c2` int DEFAULT '20',
119 `c3` int DEFAULT '30',
120 PRIMARY KEY (`a`)
121 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
122 /*!50100 PARTITION BY HASH (`a`)
123 PARTITIONS 3 */
124 DROP TABLE t1;
125 #
126 # Scenario 3:
127 # Create a partitioned table, ALTER TABLE ... PARTITION will clear the
128 # instant information
129 #
130 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT) 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));;
131 INSERT INTO t1 VALUES(1, 1), (2, 2), (11, 11), (12, 12), (21, 21), (22, 22), (26, 26), (27, 27);
132 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
133 Expect 0
134 0
135 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
136 Expect 0
137 0
138 ALTER TABLE t1 ADD COLUMN c INT DEFAULT 100;
139 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
140 Expect 0
141 0
142 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
143 Expect 3
144 3
145 ALTER TABLE t1 ADD PARTITION (PARTITION p4 VALUES LESS THAN(40));
146 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
147 Expect 0
148 0
149 SELECT count(*) AS `Expect 4` FROM information_schema.innodb_columns WHERE has_default = 1;
150 Expect 4
151 4
152 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%p4%';
153 instant_cols
154 0
155 ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (PARTITION p31 VALUES LESS THAN(25), PARTITION p32 VALUES LESS THAN(30));
156 SELECT * FROM t1 WHERE a > 20 AND a < 30;
157 a b c
158 21 21 100
159 22 22 100
160 26 26 100
161 27 27 100
162 SELECT * FROM t1 WHERE a > 10 AND a < 20;
163 a b c
164 11 11 100
165 12 12 100
166 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
167 Expect 0
168 0
169 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
170 Expect 5
171 5
172 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%p3%';
173 instant_cols
174 0
175 0
176 ALTER TABLE t1 TRUNCATE PARTITION p1;
177 SELECT * FROM t1 WHERE a < 10;
178 a b c
179 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
180 Expect 0
181 0
182 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
183 Expect 5
184 5
185 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%p1%';
186 instant_cols
187 0
188 ALTER TABLE t1 TRUNCATE PARTITION p1, p2, p31, p32, p4;
189 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
190 Expect 0
191 0
192 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
193 Expect 0
194 0
195 INSERT INTO t1(a, b) VALUES(1, 1), (2, 2), (11, 11), (12, 12), (21, 21), (22, 22), (26, 26), (27, 27);
196 SELECT c FROM t1 GROUP BY c;
197 c
198 100
199 ALTER TABLE t1 ADD COLUMN d INT DEFAULT 100;
200 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
201 Expect 0
202 0
203 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
204 Expect 5
205 5
206 ALTER TABLE t1 TRUNCATE PARTITION p1;
207 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
208 Expect 0
209 0
210 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
211 Expect 5
212 5
213 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%p1%';
214 instant_cols
215 0
216 ALTER TABLE t1 DROP PARTITION p2, p4;
217 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
218 Expect 0
219 0
220 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
221 Expect 3
222 3
223 ALTER TABLE t1 DROP PARTITION p31, p32;
224 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
225 Expect 0
226 0
227 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_columns WHERE has_default = 1;
228 Expect 1
229 1
230 CHECK TABLE t1;
231 Table Op Msg_type Msg_text
232 test.t1 check status OK
233 SHOW CREATE TABLE t1;
234 Table Create Table
235 t1 CREATE TABLE `t1` (
236 `a` int NOT NULL,
237 `b` int DEFAULT NULL,
238 `c` int DEFAULT '100',
239 `d` int DEFAULT '100',
240 PRIMARY KEY (`a`)
241 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
242 /*!50100 PARTITION BY RANGE (`a`)
243 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */
244 DROP TABLE t1;
245 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=REDUNDANT PARTITION BY HASH(a) PARTITIONS 3;;
246 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
247 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
248 Expect 0
249 0
250 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
251 Expect 0
252 0
253 ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 10;
254 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
255 Expect 0
256 0
257 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
258 Expect 3
259 3
260 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
261 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
262 Expect 0
263 0
264 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
265 Expect 5
266 5
267 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 20;
268 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
269 Expect 0
270 0
271 SELECT count(*) AS `Expect 10` FROM information_schema.innodb_columns WHERE has_default = 1;
272 Expect 10
273 10
274 ALTER TABLE t1 COALESCE PARTITION 2;
275 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
276 Expect 0
277 0
278 SELECT count(*) AS `Expect 6` FROM information_schema.innodb_columns WHERE has_default = 1;
279 Expect 6
280 6
281 ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 30;
282 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
283 Expect 0
284 0
285 SELECT count(*) AS `Expect 9` FROM information_schema.innodb_columns WHERE has_default = 1;
286 Expect 9
287 9
288 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
289 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
290 Expect 0
291 0
292 SELECT count(*) AS `Expect 15` FROM information_schema.innodb_columns WHERE has_default = 1;
293 Expect 15
294 15
295 SELECT * FROM t1;
296 a b c1 c2 c3
297 5 5 10 20 30
298 1 1 10 20 30
299 6 6 10 20 30
300 2 2 10 20 30
301 7 7 10 20 30
302 3 3 10 20 30
303 8 8 10 20 30
304 4 4 10 20 30
305 CHECK TABLE t1;
306 Table Op Msg_type Msg_text
307 test.t1 check status OK
308 SHOW CREATE TABLE t1;
309 Table Create Table
310 t1 CREATE TABLE `t1` (
311 `a` int NOT NULL AUTO_INCREMENT,
312 `b` int DEFAULT NULL,
313 `c1` int DEFAULT '10',
314 `c2` int DEFAULT '20',
315 `c3` int DEFAULT '30',
316 PRIMARY KEY (`a`)
317 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
318 /*!50100 PARTITION BY HASH (`a`)
319 PARTITIONS 5 */
320 DROP TABLE t1;
321 ############################################
322 # Test instant ADD COLUMN for DYNAMIC format
323 ############################################
324 #
325 # Scenario 1:
326 # Create a normal table, rebuild and truncate will clear the instant
327 # information
328 #
329 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC;
330 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
331 ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 10;
332 Table id did not change
333 count(*) = 1
334 1
335 CHECK TABLE t1;
336 Table Op Msg_type Msg_text
337 test.t1 check status OK
338 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 20, ADD KEY(c2);
339 Table ID differed
340 count(*) = 0
341 1
342 CHECK TABLE t1;
343 Table Op Msg_type Msg_text
344 test.t1 check status OK
345 SELECT count(*) FROM information_schema.innodb_tables WHERE instant_cols != 0;
346 count(*)
347 0
348 SELECT count(*) FROM information_schema.innodb_columns WHERE has_default = 1;
349 count(*)
350 0
351 INSERT INTO t1(a, b) VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
352 ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 10;
353 Table id did not change
354 count(*) = 1
355 1
356 CHECK TABLE t1;
357 Table Op Msg_type Msg_text
358 test.t1 check status OK
359 TRUNCATE TABLE t1;
360 Table ID differed
361 count(*) = 0
362 1
363 CHECK TABLE t1;
364 Table Op Msg_type Msg_text
365 test.t1 check status OK
366 SELECT count(*) FROM information_schema.innodb_tables WHERE instant_cols != 0;
367 count(*)
368 0
369 SELECT count(*) FROM information_schema.innodb_columns WHERE has_default = 1;
370 count(*)
371 0
372 CHECK TABLE t1;
373 Table Op Msg_type Msg_text
374 test.t1 check status OK
375 SHOW CREATE TABLE t1;
376 Table Create Table
377 t1 CREATE TABLE `t1` (
378 `a` int NOT NULL AUTO_INCREMENT,
379 `b` int DEFAULT NULL,
380 `c1` int DEFAULT '10',
381 `c2` int DEFAULT '20',
382 `c3` int DEFAULT '10',
383 PRIMARY KEY (`a`),
384 KEY `c2` (`c2`)
385 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
386 DROP TABLE t1;
387 #
388 # Scenario 2:
389 # Create a partitioned table, rebuild and truncate will clear the instant
390 # information
391 #
392 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC PARTITION BY HASH(a) PARTITIONS 3;;
393 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
394 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
395 Expect 0
396 0
397 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
398 Expect 0
399 0
400 ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 10;
401 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
402 Expect 0
403 0
404 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
405 Expect 3
406 3
407 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 20, ALGORITHM = COPY;
408 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
409 Expect 0
410 0
411 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
412 Expect 0
413 0
414 INSERT INTO t1(b, c1, c2) SELECT b, c1, c2 FROM t1;
415 ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 30;
416 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
417 Expect 0
418 0
419 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
420 Expect 3
421 3
422 TRUNCATE TABLE t1;
423 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
424 Expect 0
425 0
426 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
427 Expect 0
428 0
429 CHECK TABLE t1;
430 Table Op Msg_type Msg_text
431 test.t1 check status OK
432 SHOW CREATE TABLE t1;
433 Table Create Table
434 t1 CREATE TABLE `t1` (
435 `a` int NOT NULL AUTO_INCREMENT,
436 `b` int DEFAULT NULL,
437 `c1` int DEFAULT '10',
438 `c2` int DEFAULT '20',
439 `c3` int DEFAULT '30',
440 PRIMARY KEY (`a`)
441 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
442 /*!50100 PARTITION BY HASH (`a`)
443 PARTITIONS 3 */
444 DROP TABLE t1;
445 #
446 # Scenario 3:
447 # Create a partitioned table, ALTER TABLE ... PARTITION will clear the
448 # instant information
449 #
450 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT) 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));;
451 INSERT INTO t1 VALUES(1, 1), (2, 2), (11, 11), (12, 12), (21, 21), (22, 22), (26, 26), (27, 27);
452 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
453 Expect 0
454 0
455 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
456 Expect 0
457 0
458 ALTER TABLE t1 ADD COLUMN c INT DEFAULT 100;
459 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
460 Expect 0
461 0
462 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
463 Expect 3
464 3
465 ALTER TABLE t1 ADD PARTITION (PARTITION p4 VALUES LESS THAN(40));
466 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
467 Expect 0
468 0
469 SELECT count(*) AS `Expect 4` FROM information_schema.innodb_columns WHERE has_default = 1;
470 Expect 4
471 4
472 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%p4%';
473 instant_cols
474 0
475 ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (PARTITION p31 VALUES LESS THAN(25), PARTITION p32 VALUES LESS THAN(30));
476 SELECT * FROM t1 WHERE a > 20 AND a < 30;
477 a b c
478 21 21 100
479 22 22 100
480 26 26 100
481 27 27 100
482 SELECT * FROM t1 WHERE a > 10 AND a < 20;
483 a b c
484 11 11 100
485 12 12 100
486 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
487 Expect 0
488 0
489 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
490 Expect 5
491 5
492 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%p3%';
493 instant_cols
494 0
495 0
496 ALTER TABLE t1 TRUNCATE PARTITION p1;
497 SELECT * FROM t1 WHERE a < 10;
498 a b c
499 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
500 Expect 0
501 0
502 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
503 Expect 5
504 5
505 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%p1%';
506 instant_cols
507 0
508 ALTER TABLE t1 TRUNCATE PARTITION p1, p2, p31, p32, p4;
509 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
510 Expect 0
511 0
512 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
513 Expect 0
514 0
515 INSERT INTO t1(a, b) VALUES(1, 1), (2, 2), (11, 11), (12, 12), (21, 21), (22, 22), (26, 26), (27, 27);
516 SELECT c FROM t1 GROUP BY c;
517 c
518 100
519 ALTER TABLE t1 ADD COLUMN d INT DEFAULT 100;
520 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
521 Expect 0
522 0
523 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
524 Expect 5
525 5
526 ALTER TABLE t1 TRUNCATE PARTITION p1;
527 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
528 Expect 0
529 0
530 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
531 Expect 5
532 5
533 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%p1%';
534 instant_cols
535 0
536 ALTER TABLE t1 DROP PARTITION p2, p4;
537 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
538 Expect 0
539 0
540 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
541 Expect 3
542 3
543 ALTER TABLE t1 DROP PARTITION p31, p32;
544 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
545 Expect 0
546 0
547 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_columns WHERE has_default = 1;
548 Expect 1
549 1
550 CHECK TABLE t1;
551 Table Op Msg_type Msg_text
552 test.t1 check status OK
553 SHOW CREATE TABLE t1;
554 Table Create Table
555 t1 CREATE TABLE `t1` (
556 `a` int NOT NULL,
557 `b` int DEFAULT NULL,
558 `c` int DEFAULT '100',
559 `d` int DEFAULT '100',
560 PRIMARY KEY (`a`)
561 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
562 /*!50100 PARTITION BY RANGE (`a`)
563 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */
564 DROP TABLE t1;
565 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=DYNAMIC PARTITION BY HASH(a) PARTITIONS 3;;
566 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
567 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
568 Expect 0
569 0
570 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
571 Expect 0
572 0
573 ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 10;
574 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
575 Expect 0
576 0
577 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
578 Expect 3
579 3
580 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
581 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
582 Expect 0
583 0
584 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
585 Expect 5
586 5
587 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 20;
588 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
589 Expect 0
590 0
591 SELECT count(*) AS `Expect 10` FROM information_schema.innodb_columns WHERE has_default = 1;
592 Expect 10
593 10
594 ALTER TABLE t1 COALESCE PARTITION 2;
595 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
596 Expect 0
597 0
598 SELECT count(*) AS `Expect 6` FROM information_schema.innodb_columns WHERE has_default = 1;
599 Expect 6
600 6
601 ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 30;
602 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
603 Expect 0
604 0
605 SELECT count(*) AS `Expect 9` FROM information_schema.innodb_columns WHERE has_default = 1;
606 Expect 9
607 9
608 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
609 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
610 Expect 0
611 0
612 SELECT count(*) AS `Expect 15` FROM information_schema.innodb_columns WHERE has_default = 1;
613 Expect 15
614 15
615 SELECT * FROM t1;
616 a b c1 c2 c3
617 5 5 10 20 30
618 1 1 10 20 30
619 6 6 10 20 30
620 2 2 10 20 30
621 7 7 10 20 30
622 3 3 10 20 30
623 8 8 10 20 30
624 4 4 10 20 30
625 CHECK TABLE t1;
626 Table Op Msg_type Msg_text
627 test.t1 check status OK
628 SHOW CREATE TABLE t1;
629 Table Create Table
630 t1 CREATE TABLE `t1` (
631 `a` int NOT NULL AUTO_INCREMENT,
632 `b` int DEFAULT NULL,
633 `c1` int DEFAULT '10',
634 `c2` int DEFAULT '20',
635 `c3` int DEFAULT '30',
636 PRIMARY KEY (`a`)
637 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
638 /*!50100 PARTITION BY HASH (`a`)
639 PARTITIONS 5 */
640 DROP TABLE t1;
641 ############################################
642 # Test instant ADD COLUMN for COMPACT format
643 ############################################
644 #
645 # Scenario 1:
646 # Create a normal table, rebuild and truncate will clear the instant
647 # information
648 #
649 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT;
650 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
651 ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 10;
652 Table id did not change
653 count(*) = 1
654 1
655 CHECK TABLE t1;
656 Table Op Msg_type Msg_text
657 test.t1 check status OK
658 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 20, ADD KEY(c2);
659 Table ID differed
660 count(*) = 0
661 1
662 CHECK TABLE t1;
663 Table Op Msg_type Msg_text
664 test.t1 check status OK
665 SELECT count(*) FROM information_schema.innodb_tables WHERE instant_cols != 0;
666 count(*)
667 0
668 SELECT count(*) FROM information_schema.innodb_columns WHERE has_default = 1;
669 count(*)
670 0
671 INSERT INTO t1(a, b) VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
672 ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 10;
673 Table id did not change
674 count(*) = 1
675 1
676 CHECK TABLE t1;
677 Table Op Msg_type Msg_text
678 test.t1 check status OK
679 TRUNCATE TABLE t1;
680 Table ID differed
681 count(*) = 0
682 1
683 CHECK TABLE t1;
684 Table Op Msg_type Msg_text
685 test.t1 check status OK
686 SELECT count(*) FROM information_schema.innodb_tables WHERE instant_cols != 0;
687 count(*)
688 0
689 SELECT count(*) FROM information_schema.innodb_columns WHERE has_default = 1;
690 count(*)
691 0
692 CHECK TABLE t1;
693 Table Op Msg_type Msg_text
694 test.t1 check status OK
695 SHOW CREATE TABLE t1;
696 Table Create Table
697 t1 CREATE TABLE `t1` (
698 `a` int NOT NULL AUTO_INCREMENT,
699 `b` int DEFAULT NULL,
700 `c1` int DEFAULT '10',
701 `c2` int DEFAULT '20',
702 `c3` int DEFAULT '10',
703 PRIMARY KEY (`a`),
704 KEY `c2` (`c2`)
705 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
706 DROP TABLE t1;
707 #
708 # Scenario 2:
709 # Create a partitioned table, rebuild and truncate will clear the instant
710 # information
711 #
712 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT PARTITION BY HASH(a) PARTITIONS 3;;
713 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
714 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
715 Expect 0
716 0
717 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
718 Expect 0
719 0
720 ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 10;
721 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
722 Expect 0
723 0
724 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
725 Expect 3
726 3
727 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 20, ALGORITHM = COPY;
728 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
729 Expect 0
730 0
731 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
732 Expect 0
733 0
734 INSERT INTO t1(b, c1, c2) SELECT b, c1, c2 FROM t1;
735 ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 30;
736 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
737 Expect 0
738 0
739 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
740 Expect 3
741 3
742 TRUNCATE TABLE t1;
743 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
744 Expect 0
745 0
746 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
747 Expect 0
748 0
749 CHECK TABLE t1;
750 Table Op Msg_type Msg_text
751 test.t1 check status OK
752 SHOW CREATE TABLE t1;
753 Table Create Table
754 t1 CREATE TABLE `t1` (
755 `a` int NOT NULL AUTO_INCREMENT,
756 `b` int DEFAULT NULL,
757 `c1` int DEFAULT '10',
758 `c2` int DEFAULT '20',
759 `c3` int DEFAULT '30',
760 PRIMARY KEY (`a`)
761 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
762 /*!50100 PARTITION BY HASH (`a`)
763 PARTITIONS 3 */
764 DROP TABLE t1;
765 #
766 # Scenario 3:
767 # Create a partitioned table, ALTER TABLE ... PARTITION will clear the
768 # instant information
769 #
770 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT) 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));;
771 INSERT INTO t1 VALUES(1, 1), (2, 2), (11, 11), (12, 12), (21, 21), (22, 22), (26, 26), (27, 27);
772 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
773 Expect 0
774 0
775 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
776 Expect 0
777 0
778 ALTER TABLE t1 ADD COLUMN c INT DEFAULT 100;
779 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
780 Expect 0
781 0
782 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
783 Expect 3
784 3
785 ALTER TABLE t1 ADD PARTITION (PARTITION p4 VALUES LESS THAN(40));
786 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
787 Expect 0
788 0
789 SELECT count(*) AS `Expect 4` FROM information_schema.innodb_columns WHERE has_default = 1;
790 Expect 4
791 4
792 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%p4%';
793 instant_cols
794 0
795 ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (PARTITION p31 VALUES LESS THAN(25), PARTITION p32 VALUES LESS THAN(30));
796 SELECT * FROM t1 WHERE a > 20 AND a < 30;
797 a b c
798 21 21 100
799 22 22 100
800 26 26 100
801 27 27 100
802 SELECT * FROM t1 WHERE a > 10 AND a < 20;
803 a b c
804 11 11 100
805 12 12 100
806 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
807 Expect 0
808 0
809 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
810 Expect 5
811 5
812 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%p3%';
813 instant_cols
814 0
815 0
816 ALTER TABLE t1 TRUNCATE PARTITION p1;
817 SELECT * FROM t1 WHERE a < 10;
818 a b c
819 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
820 Expect 0
821 0
822 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
823 Expect 5
824 5
825 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%p1%';
826 instant_cols
827 0
828 ALTER TABLE t1 TRUNCATE PARTITION p1, p2, p31, p32, p4;
829 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
830 Expect 0
831 0
832 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
833 Expect 0
834 0
835 INSERT INTO t1(a, b) VALUES(1, 1), (2, 2), (11, 11), (12, 12), (21, 21), (22, 22), (26, 26), (27, 27);
836 SELECT c FROM t1 GROUP BY c;
837 c
838 100
839 ALTER TABLE t1 ADD COLUMN d INT DEFAULT 100;
840 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
841 Expect 0
842 0
843 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
844 Expect 5
845 5
846 ALTER TABLE t1 TRUNCATE PARTITION p1;
847 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
848 Expect 0
849 0
850 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
851 Expect 5
852 5
853 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%p1%';
854 instant_cols
855 0
856 ALTER TABLE t1 DROP PARTITION p2, p4;
857 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
858 Expect 0
859 0
860 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
861 Expect 3
862 3
863 ALTER TABLE t1 DROP PARTITION p31, p32;
864 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
865 Expect 0
866 0
867 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_columns WHERE has_default = 1;
868 Expect 1
869 1
870 CHECK TABLE t1;
871 Table Op Msg_type Msg_text
872 test.t1 check status OK
873 SHOW CREATE TABLE t1;
874 Table Create Table
875 t1 CREATE TABLE `t1` (
876 `a` int NOT NULL,
877 `b` int DEFAULT NULL,
878 `c` int DEFAULT '100',
879 `d` int DEFAULT '100',
880 PRIMARY KEY (`a`)
881 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
882 /*!50100 PARTITION BY RANGE (`a`)
883 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */
884 DROP TABLE t1;
885 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT PARTITION BY HASH(a) PARTITIONS 3;;
886 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
887 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
888 Expect 0
889 0
890 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
891 Expect 0
892 0
893 ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 10;
894 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
895 Expect 0
896 0
897 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
898 Expect 3
899 3
900 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
901 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
902 Expect 0
903 0
904 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
905 Expect 5
906 5
907 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 20;
908 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
909 Expect 0
910 0
911 SELECT count(*) AS `Expect 10` FROM information_schema.innodb_columns WHERE has_default = 1;
912 Expect 10
913 10
914 ALTER TABLE t1 COALESCE PARTITION 2;
915 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
916 Expect 0
917 0
918 SELECT count(*) AS `Expect 6` FROM information_schema.innodb_columns WHERE has_default = 1;
919 Expect 6
920 6
921 ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 30;
922 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
923 Expect 0
924 0
925 SELECT count(*) AS `Expect 9` FROM information_schema.innodb_columns WHERE has_default = 1;
926 Expect 9
927 9
928 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
929 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
930 Expect 0
931 0
932 SELECT count(*) AS `Expect 15` FROM information_schema.innodb_columns WHERE has_default = 1;
933 Expect 15
934 15
935 SELECT * FROM t1;
936 a b c1 c2 c3
937 5 5 10 20 30
938 1 1 10 20 30
939 6 6 10 20 30
940 2 2 10 20 30
941 7 7 10 20 30
942 3 3 10 20 30
943 8 8 10 20 30
944 4 4 10 20 30
945 CHECK TABLE t1;
946 Table Op Msg_type Msg_text
947 test.t1 check status OK
948 SHOW CREATE TABLE t1;
949 Table Create Table
950 t1 CREATE TABLE `t1` (
951 `a` int NOT NULL AUTO_INCREMENT,
952 `b` int DEFAULT NULL,
953 `c1` int DEFAULT '10',
954 `c2` int DEFAULT '20',
955 `c3` int DEFAULT '30',
956 PRIMARY KEY (`a`)
957 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
958 /*!50100 PARTITION BY HASH (`a`)
959 PARTITIONS 5 */
960 DROP TABLE t1;