Debian Codegraph @

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

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