Debian Codegraph @

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

raw
1 ##############################################
2 # Test instant ADD/DROP COLUMN for REDUNDANT format
3 ##############################################
4 # ------------------------------------------------------------
5 # Create a table with 3 columns. [c1, c2, c3]
6 # ------------------------------------------------------------
7 CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) ) ROW_FORMAT=REDUNDANT;
8 # Metadata from INFORMATION_SCHEMA.TABLES
9 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
10 test/t1 6 0 0
11 # Metadata from INFORMATION_SCHEMA.COLUMNS
12 NAME POS MTYPE PRTYPE HAS_DEFAULT
13 c1 0 13 16711934 0
14 c2 1 13 16711934 0
15 c3 2 13 16711934 0
16 INSERT INTO t1 values ("r1c1", "r1c2", "r1c3");
17 SELECT * FROM t1 ORDER BY c1;
18 c1 c2 c3
19 r1c1 r1c2 r1c3
20
21 # -----------------------ADD COLUMN TEST----------------------
22
23 # ------------------------------------------------------------
24 # Add a new column at the end. [c1, c2, c3, +c4]
25 # ------------------------------------------------------------
26 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_def", ALGORITHM=INSTANT;
27 # Metadata from INFORMATION_SCHEMA.TABLES
28 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
29 test/t1 7 0 1
30 # Metadata from INFORMATION_SCHEMA.COLUMNS
31 NAME POS MTYPE PRTYPE HAS_DEFAULT
32 c1 0 13 16711934 0
33 c2 1 13 16711934 0
34 c3 2 13 16711934 0
35 c4 3 13 16711934 1
36 INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4");
37 SELECT * FROM t1 ORDER BY c1;
38 c1 c2 c3 c4
39 r1c1 r1c2 r1c3 c4_def
40 r2c1 r2c2 r2c3 r2c4
41 # ------------------------------------------------------------
42 # Add a new column in between. [c1, +c5, c2, c3, c4]
43 # ------------------------------------------------------------
44 ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
45 # Metadata from INFORMATION_SCHEMA.TABLES
46 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
47 test/t1 8 0 2
48 # Metadata from INFORMATION_SCHEMA.COLUMNS
49 NAME POS MTYPE PRTYPE HAS_DEFAULT
50 c1 0 13 16711934 0
51 c5 1 13 16711934 1
52 c2 2 13 16711934 0
53 c3 3 13 16711934 0
54 c4 4 13 16711934 1
55 INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4");
56 SELECT * FROM t1 ORDER BY c1;
57 c1 c5 c2 c3 c4
58 r1c1 NULL r1c2 r1c3 c4_def
59 r2c1 NULL r2c2 r2c3 r2c4
60 r3c1 r3c5 r3c2 r3c3 r3c4
61 # ------------------------------------------------------------
62 # Add a new column at first. [+c6, c1, c5, c2, c3, c4]
63 # ------------------------------------------------------------
64 ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL FIRST, ALGORITHM=INSTANT;
65 # Metadata from INFORMATION_SCHEMA.TABLES
66 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
67 test/t1 9 0 3
68 # Metadata from INFORMATION_SCHEMA.COLUMNS
69 NAME POS MTYPE PRTYPE HAS_DEFAULT
70 c6 0 13 16711934 1
71 c1 1 13 16711934 0
72 c5 2 13 16711934 1
73 c2 3 13 16711934 0
74 c3 4 13 16711934 0
75 c4 5 13 16711934 1
76 INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4");
77 SELECT * FROM t1 ORDER BY c1;
78 c6 c1 c5 c2 c3 c4
79 NULL r1c1 NULL r1c2 r1c3 c4_def
80 NULL r2c1 NULL r2c2 r2c3 r2c4
81 NULL r3c1 r3c5 r3c2 r3c3 r3c4
82 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
83 # ------------------------------------------------------------
84 # Rebuild table. [c6, c1, c5, c2, c3, c4]
85 # ------------------------------------------------------------
86 ALTER TABLE t1 force;
87 # Metadata from INFORMATION_SCHEMA.TABLES
88 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
89 test/t1 9 0 0
90 # Metadata from INFORMATION_SCHEMA.COLUMNS
91 NAME POS MTYPE PRTYPE HAS_DEFAULT
92 c6 0 13 16711934 0
93 c1 1 13 16711934 0
94 c5 2 13 16711934 0
95 c2 3 13 16711934 0
96 c3 4 13 16711934 0
97 c4 5 13 16711934 0
98 SELECT * FROM t1 ORDER BY c1;
99 c6 c1 c5 c2 c3 c4
100 NULL r1c1 NULL r1c2 r1c3 c4_def
101 NULL r2c1 NULL r2c2 r2c3 r2c4
102 NULL r3c1 r3c5 r3c2 r3c3 r3c4
103 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
104
105 # -----------------------DROP COLUMN TEST---------------------
106
107 # ------------------------------------------------------------
108 # Drop a column from the end. [c6, c1, c5, c2, c3, -c4]
109 # ------------------------------------------------------------
110 ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT;
111 # Metadata from INFORMATION_SCHEMA.TABLES
112 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
113 test/t1 8 0 1
114 # Metadata from INFORMATION_SCHEMA.COLUMNS
115 NAME POS MTYPE PRTYPE HAS_DEFAULT
116 c6 0 13 16711934 0
117 c1 1 13 16711934 0
118 c5 2 13 16711934 0
119 c2 3 13 16711934 0
120 c3 4 13 16711934 0
121 SELECT * FROM t1 ORDER BY c1;
122 c6 c1 c5 c2 c3
123 NULL r1c1 NULL r1c2 r1c3
124 NULL r2c1 NULL r2c2 r2c3
125 NULL r3c1 r3c5 r3c2 r3c3
126 r4c6 r4c1 r4c5 r4c2 r4c3
127 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3");
128 SELECT * FROM t1 ORDER BY c1;
129 c6 c1 c5 c2 c3
130 NULL r1c1 NULL r1c2 r1c3
131 NULL r2c1 NULL r2c2 r2c3
132 NULL r3c1 r3c5 r3c2 r3c3
133 r4c6 r4c1 r4c5 r4c2 r4c3
134 r5c6 r5c1 r5c5 r5c2 r5c3
135 # ------------------------------------------------------------
136 # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4]
137 # ------------------------------------------------------------
138 ALTER TABLE t1 DROP COLUMN c5, ALGORITHM=INSTANT;
139 # Metadata from INFORMATION_SCHEMA.TABLES
140 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
141 test/t1 7 0 2
142 # Metadata from INFORMATION_SCHEMA.COLUMNS
143 NAME POS MTYPE PRTYPE HAS_DEFAULT
144 c6 0 13 16711934 0
145 c1 1 13 16711934 0
146 c2 2 13 16711934 0
147 c3 3 13 16711934 0
148 SELECT * FROM t1 ORDER BY c1;
149 c6 c1 c2 c3
150 NULL r1c1 r1c2 r1c3
151 NULL r2c1 r2c2 r2c3
152 NULL r3c1 r3c2 r3c3
153 r4c6 r4c1 r4c2 r4c3
154 r5c6 r5c1 r5c2 r5c3
155 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3");
156 SELECT * FROM t1 ORDER BY c1;
157 c6 c1 c2 c3
158 NULL r1c1 r1c2 r1c3
159 NULL r2c1 r2c2 r2c3
160 NULL r3c1 r3c2 r3c3
161 r4c6 r4c1 r4c2 r4c3
162 r5c6 r5c1 r5c2 r5c3
163 r6c6 r6c1 r6c2 r6c3
164 # ------------------------------------------------------------
165 # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5]
166 # ------------------------------------------------------------
167 ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT;
168 # Metadata from INFORMATION_SCHEMA.TABLES
169 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
170 test/t1 6 0 3
171 # Metadata from INFORMATION_SCHEMA.COLUMNS
172 NAME POS MTYPE PRTYPE HAS_DEFAULT
173 c1 0 13 16711934 0
174 c2 1 13 16711934 0
175 c3 2 13 16711934 0
176 SELECT * FROM t1 ORDER BY c1;
177 c1 c2 c3
178 r1c1 r1c2 r1c3
179 r2c1 r2c2 r2c3
180 r3c1 r3c2 r3c3
181 r4c1 r4c2 r4c3
182 r5c1 r5c2 r5c3
183 r6c1 r6c2 r6c3
184 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3");
185 SELECT * FROM t1 ORDER BY c1;
186 c1 c2 c3
187 r1c1 r1c2 r1c3
188 r2c1 r2c2 r2c3
189 r3c1 r3c2 r3c3
190 r4c1 r4c2 r4c3
191 r5c1 r5c2 r5c3
192 r6c1 r6c2 r6c3
193 r7c1 r7c2 r7c3
194 # ------------------------------------------------------------
195 # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6]
196 # ------------------------------------------------------------
197 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", DROP COLUMN c3, ALGORITHM=INSTANT;
198 # Metadata from INFORMATION_SCHEMA.TABLES
199 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
200 test/t1 6 0 4
201 # Metadata from INFORMATION_SCHEMA.COLUMNS
202 NAME POS MTYPE PRTYPE HAS_DEFAULT
203 c1 0 13 16711934 0
204 c2 1 13 16711934 0
205 c7 2 13 16711934 1
206 SELECT * FROM t1 ORDER BY c1;
207 c1 c2 c7
208 r1c1 r1c2 c7_def
209 r2c1 r2c2 c7_def
210 r3c1 r3c2 c7_def
211 r4c1 r4c2 c7_def
212 r5c1 r5c2 c7_def
213 r6c1 r6c2 c7_def
214 r7c1 r7c2 c7_def
215 INSERT INTO t1 values ("r8c1", "r8c2", "r8c7");
216 SELECT * FROM t1 ORDER BY c1;
217 c1 c2 c7
218 r1c1 r1c2 c7_def
219 r2c1 r2c2 c7_def
220 r3c1 r3c2 c7_def
221 r4c1 r4c2 c7_def
222 r5c1 r5c2 c7_def
223 r6c1 r6c2 c7_def
224 r7c1 r7c2 c7_def
225 r8c1 r8c2 r8c7
226 # --------------------------------------------------------------------------
227 # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3]
228 # --------------------------------------------------------------------------
229 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, ALGORITHM=INSTANT;
230 # Metadata from INFORMATION_SCHEMA.TABLES
231 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
232 test/t1 6 0 5
233 # Metadata from INFORMATION_SCHEMA.COLUMNS
234 NAME POS MTYPE PRTYPE HAS_DEFAULT
235 c1 0 13 16711934 0
236 c2 1 13 16711934 0
237 c7 2 13 16711934 1
238 SELECT * FROM t1 ORDER BY c1;
239 c1 c2 c7
240 r1c1 r1c2 c7_def2
241 r2c1 r2c2 c7_def2
242 r3c1 r3c2 c7_def2
243 r4c1 r4c2 c7_def2
244 r5c1 r5c2 c7_def2
245 r6c1 r6c2 c7_def2
246 r7c1 r7c2 c7_def2
247 r8c1 r8c2 c7_def2
248 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7");
249 SELECT * FROM t1 ORDER BY c1;
250 c1 c2 c7
251 r1c1 r1c2 c7_def2
252 r2c1 r2c2 c7_def2
253 r3c1 r3c2 c7_def2
254 r4c1 r4c2 c7_def2
255 r5c1 r5c2 c7_def2
256 r6c1 r6c2 c7_def2
257 r7c1 r7c2 c7_def2
258 r8c1 r8c2 c7_def2
259 r9c1 r9c2 r9c7
260 # ------------------------------------------------------------
261 # Rebuild table. [c1, c2, c7]
262 # ------------------------------------------------------------
263 ALTER TABLE t1 force;
264 # Metadata from INFORMATION_SCHEMA.TABLES
265 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
266 test/t1 6 0 0
267 # Metadata from INFORMATION_SCHEMA.COLUMNS
268 NAME POS MTYPE PRTYPE HAS_DEFAULT
269 c1 0 13 16711934 0
270 c2 1 13 16711934 0
271 c7 2 13 16711934 0
272 SELECT * FROM t1 ORDER BY c1;
273 c1 c2 c7
274 r1c1 r1c2 c7_def2
275 r2c1 r2c2 c7_def2
276 r3c1 r3c2 c7_def2
277 r4c1 r4c2 c7_def2
278 r5c1 r5c2 c7_def2
279 r6c1 r6c2 c7_def2
280 r7c1 r7c2 c7_def2
281 r8c1 r8c2 c7_def2
282 r9c1 r9c2 r9c7
283 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7");
284 SELECT * FROM t1 ORDER BY c1;
285 c1 c2 c7
286 r10c1 r10c2 r10c7
287 r1c1 r1c2 c7_def2
288 r2c1 r2c2 c7_def2
289 r3c1 r3c2 c7_def2
290 r4c1 r4c2 c7_def2
291 r5c1 r5c2 c7_def2
292 r6c1 r6c2 c7_def2
293 r7c1 r7c2 c7_def2
294 r8c1 r8c2 c7_def2
295 r9c1 r9c2 r9c7
296 ###########
297 # CLEANUP #
298 ###########
299 DROP TABLE t1;
300 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
301 INSERT INTO t2 VALUES ("r1c1", "r1c2");
302 SELECT * FROM t2;
303 c1 c2
304 r1c1 r1c2
305 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
306 INSERT INTO t1 VALUES ("r1c1", "r1c2");
307 SELECT * FROM t1;
308 c1 c2
309 r1c1 r1c2
310 BEGIN;
311 SELECT * FROM t2;
312 c1 c2
313 r1c1 r1c2
314 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
315 ALGORITHM=COPY;
316 UPDATE t1 SET c1 = "r1c11";
317 SELECT * FROM t1;
318 c1 c2 c3
319 r1c11 r1c2 c3_def
320 SELECT * FROM t1;
321 ERROR HY000: Table definition has changed, please retry transaction
322 DROP TABLE t1;
323 DROP TABLE t2;
324 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
325 INSERT INTO t2 VALUES ("r1c1", "r1c2");
326 SELECT * FROM t2;
327 c1 c2
328 r1c1 r1c2
329 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
330 INSERT INTO t1 VALUES ("r1c1", "r1c2");
331 SELECT * FROM t1;
332 c1 c2
333 r1c1 r1c2
334 BEGIN;
335 SELECT * FROM t2;
336 c1 c2
337 r1c1 r1c2
338 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
339 ALGORITHM=INSTANT;
340 UPDATE t1 SET c1 = "r1c11";
341 SELECT * FROM t1;
342 c1 c2 c3
343 r1c11 r1c2 c3_def
344 SELECT * FROM t1;
345 ERROR HY000: Table definition has changed, please retry transaction
346 DROP TABLE t1;
347 DROP TABLE t2;
348 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
349 INSERT INTO t2 VALUES ("r1c1", "r1c2");
350 SELECT * FROM t2;
351 c1 c2
352 r1c1 r1c2
353 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
354 INSERT INTO t1 VALUES ("r1c1", "r1c2");
355 SELECT * FROM t1;
356 c1 c2
357 r1c1 r1c2
358 BEGIN;
359 SELECT * FROM t2;
360 c1 c2
361 r1c1 r1c2
362 ALTER TABLE t1 DROP COLUMN c2,
363 ALGORITHM=COPY;
364 UPDATE t1 SET c1 = "r1c11";
365 SELECT * FROM t1;
366 c1
367 r1c11
368 SELECT * FROM t1;
369 ERROR HY000: Table definition has changed, please retry transaction
370 DROP TABLE t1;
371 DROP TABLE t2;
372 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
373 INSERT INTO t2 VALUES ("r1c1", "r1c2");
374 SELECT * FROM t2;
375 c1 c2
376 r1c1 r1c2
377 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
378 INSERT INTO t1 VALUES ("r1c1", "r1c2");
379 SELECT * FROM t1;
380 c1 c2
381 r1c1 r1c2
382 BEGIN;
383 SELECT * FROM t2;
384 c1 c2
385 r1c1 r1c2
386 ALTER TABLE t1 DROP COLUMN c2,
387 ALGORITHM=INSTANT;
388 UPDATE t1 SET c1 = "r1c11";
389 SELECT * FROM t1;
390 c1
391 r1c11
392 SELECT * FROM t1;
393 ERROR HY000: Table definition has changed, please retry transaction
394 DROP TABLE t1;
395 DROP TABLE t2;
396 # ------------------------------------------------------------
397 # Create a table with 3 columns. [c1, c2, c3]
398 # ------------------------------------------------------------
399 CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) , PRIMARY KEY (c1) ) ROW_FORMAT=REDUNDANT;
400 # Metadata from INFORMATION_SCHEMA.TABLES
401 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
402 test/t1 6 0 0
403 # Metadata from INFORMATION_SCHEMA.COLUMNS
404 NAME POS MTYPE PRTYPE HAS_DEFAULT
405 c1 0 13 16712190 0
406 c2 1 13 16711934 0
407 c3 2 13 16711934 0
408 INSERT INTO t1 values ("r1c1", "r1c2", "r1c3");
409 SELECT * FROM t1 ORDER BY c1;
410 c1 c2 c3
411 r1c1 r1c2 r1c3
412
413 # -----------------------ADD COLUMN TEST----------------------
414
415 # ------------------------------------------------------------
416 # Add a new column at the end. [c1, c2, c3, +c4]
417 # ------------------------------------------------------------
418 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_def", ALGORITHM=INSTANT;
419 # Metadata from INFORMATION_SCHEMA.TABLES
420 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
421 test/t1 7 0 1
422 # Metadata from INFORMATION_SCHEMA.COLUMNS
423 NAME POS MTYPE PRTYPE HAS_DEFAULT
424 c1 0 13 16712190 0
425 c2 1 13 16711934 0
426 c3 2 13 16711934 0
427 c4 3 13 16711934 1
428 INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4");
429 SELECT * FROM t1 ORDER BY c1;
430 c1 c2 c3 c4
431 r1c1 r1c2 r1c3 c4_def
432 r2c1 r2c2 r2c3 r2c4
433 # ------------------------------------------------------------
434 # Add a new column in between. [c1, +c5, c2, c3, c4]
435 # ------------------------------------------------------------
436 ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
437 # Metadata from INFORMATION_SCHEMA.TABLES
438 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
439 test/t1 8 0 2
440 # Metadata from INFORMATION_SCHEMA.COLUMNS
441 NAME POS MTYPE PRTYPE HAS_DEFAULT
442 c1 0 13 16712190 0
443 c5 1 13 16711934 1
444 c2 2 13 16711934 0
445 c3 3 13 16711934 0
446 c4 4 13 16711934 1
447 INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4");
448 SELECT * FROM t1 ORDER BY c1;
449 c1 c5 c2 c3 c4
450 r1c1 NULL r1c2 r1c3 c4_def
451 r2c1 NULL r2c2 r2c3 r2c4
452 r3c1 r3c5 r3c2 r3c3 r3c4
453 # ------------------------------------------------------------
454 # Add a new column at first. [+c6, c1, c5, c2, c3, c4]
455 # ------------------------------------------------------------
456 ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL FIRST, ALGORITHM=INSTANT;
457 # Metadata from INFORMATION_SCHEMA.TABLES
458 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
459 test/t1 9 0 3
460 # Metadata from INFORMATION_SCHEMA.COLUMNS
461 NAME POS MTYPE PRTYPE HAS_DEFAULT
462 c6 0 13 16711934 1
463 c1 1 13 16712190 0
464 c5 2 13 16711934 1
465 c2 3 13 16711934 0
466 c3 4 13 16711934 0
467 c4 5 13 16711934 1
468 INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4");
469 SELECT * FROM t1 ORDER BY c1;
470 c6 c1 c5 c2 c3 c4
471 NULL r1c1 NULL r1c2 r1c3 c4_def
472 NULL r2c1 NULL r2c2 r2c3 r2c4
473 NULL r3c1 r3c5 r3c2 r3c3 r3c4
474 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
475 # ------------------------------------------------------------
476 # Rebuild table. [c6, c1, c5, c2, c3, c4]
477 # ------------------------------------------------------------
478 ALTER TABLE t1 force;
479 # Metadata from INFORMATION_SCHEMA.TABLES
480 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
481 test/t1 9 0 0
482 # Metadata from INFORMATION_SCHEMA.COLUMNS
483 NAME POS MTYPE PRTYPE HAS_DEFAULT
484 c6 0 13 16711934 0
485 c1 1 13 16712190 0
486 c5 2 13 16711934 0
487 c2 3 13 16711934 0
488 c3 4 13 16711934 0
489 c4 5 13 16711934 0
490 SELECT * FROM t1 ORDER BY c1;
491 c6 c1 c5 c2 c3 c4
492 NULL r1c1 NULL r1c2 r1c3 c4_def
493 NULL r2c1 NULL r2c2 r2c3 r2c4
494 NULL r3c1 r3c5 r3c2 r3c3 r3c4
495 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
496
497 # -----------------------DROP COLUMN TEST---------------------
498
499 # ------------------------------------------------------------
500 # Drop a column from the end. [c6, c1, c5, c2, c3, -c4]
501 # ------------------------------------------------------------
502 ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT;
503 # Metadata from INFORMATION_SCHEMA.TABLES
504 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
505 test/t1 8 0 1
506 # Metadata from INFORMATION_SCHEMA.COLUMNS
507 NAME POS MTYPE PRTYPE HAS_DEFAULT
508 c6 0 13 16711934 0
509 c1 1 13 16712190 0
510 c5 2 13 16711934 0
511 c2 3 13 16711934 0
512 c3 4 13 16711934 0
513 SELECT * FROM t1 ORDER BY c1;
514 c6 c1 c5 c2 c3
515 NULL r1c1 NULL r1c2 r1c3
516 NULL r2c1 NULL r2c2 r2c3
517 NULL r3c1 r3c5 r3c2 r3c3
518 r4c6 r4c1 r4c5 r4c2 r4c3
519 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3");
520 SELECT * FROM t1 ORDER BY c1;
521 c6 c1 c5 c2 c3
522 NULL r1c1 NULL r1c2 r1c3
523 NULL r2c1 NULL r2c2 r2c3
524 NULL r3c1 r3c5 r3c2 r3c3
525 r4c6 r4c1 r4c5 r4c2 r4c3
526 r5c6 r5c1 r5c5 r5c2 r5c3
527 # ------------------------------------------------------------
528 # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4]
529 # ------------------------------------------------------------
530 ALTER TABLE t1 DROP COLUMN c5, ALGORITHM=INSTANT;
531 # Metadata from INFORMATION_SCHEMA.TABLES
532 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
533 test/t1 7 0 2
534 # Metadata from INFORMATION_SCHEMA.COLUMNS
535 NAME POS MTYPE PRTYPE HAS_DEFAULT
536 c6 0 13 16711934 0
537 c1 1 13 16712190 0
538 c2 2 13 16711934 0
539 c3 3 13 16711934 0
540 SELECT * FROM t1 ORDER BY c1;
541 c6 c1 c2 c3
542 NULL r1c1 r1c2 r1c3
543 NULL r2c1 r2c2 r2c3
544 NULL r3c1 r3c2 r3c3
545 r4c6 r4c1 r4c2 r4c3
546 r5c6 r5c1 r5c2 r5c3
547 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3");
548 SELECT * FROM t1 ORDER BY c1;
549 c6 c1 c2 c3
550 NULL r1c1 r1c2 r1c3
551 NULL r2c1 r2c2 r2c3
552 NULL r3c1 r3c2 r3c3
553 r4c6 r4c1 r4c2 r4c3
554 r5c6 r5c1 r5c2 r5c3
555 r6c6 r6c1 r6c2 r6c3
556 # ------------------------------------------------------------
557 # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5]
558 # ------------------------------------------------------------
559 ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT;
560 # Metadata from INFORMATION_SCHEMA.TABLES
561 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
562 test/t1 6 0 3
563 # Metadata from INFORMATION_SCHEMA.COLUMNS
564 NAME POS MTYPE PRTYPE HAS_DEFAULT
565 c1 0 13 16712190 0
566 c2 1 13 16711934 0
567 c3 2 13 16711934 0
568 SELECT * FROM t1 ORDER BY c1;
569 c1 c2 c3
570 r1c1 r1c2 r1c3
571 r2c1 r2c2 r2c3
572 r3c1 r3c2 r3c3
573 r4c1 r4c2 r4c3
574 r5c1 r5c2 r5c3
575 r6c1 r6c2 r6c3
576 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3");
577 SELECT * FROM t1 ORDER BY c1;
578 c1 c2 c3
579 r1c1 r1c2 r1c3
580 r2c1 r2c2 r2c3
581 r3c1 r3c2 r3c3
582 r4c1 r4c2 r4c3
583 r5c1 r5c2 r5c3
584 r6c1 r6c2 r6c3
585 r7c1 r7c2 r7c3
586 # ------------------------------------------------------------
587 # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6]
588 # ------------------------------------------------------------
589 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", DROP COLUMN c3, ALGORITHM=INSTANT;
590 # Metadata from INFORMATION_SCHEMA.TABLES
591 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
592 test/t1 6 0 4
593 # Metadata from INFORMATION_SCHEMA.COLUMNS
594 NAME POS MTYPE PRTYPE HAS_DEFAULT
595 c1 0 13 16712190 0
596 c2 1 13 16711934 0
597 c7 2 13 16711934 1
598 SELECT * FROM t1 ORDER BY c1;
599 c1 c2 c7
600 r1c1 r1c2 c7_def
601 r2c1 r2c2 c7_def
602 r3c1 r3c2 c7_def
603 r4c1 r4c2 c7_def
604 r5c1 r5c2 c7_def
605 r6c1 r6c2 c7_def
606 r7c1 r7c2 c7_def
607 INSERT INTO t1 values ("r8c1", "r8c2", "r8c7");
608 SELECT * FROM t1 ORDER BY c1;
609 c1 c2 c7
610 r1c1 r1c2 c7_def
611 r2c1 r2c2 c7_def
612 r3c1 r3c2 c7_def
613 r4c1 r4c2 c7_def
614 r5c1 r5c2 c7_def
615 r6c1 r6c2 c7_def
616 r7c1 r7c2 c7_def
617 r8c1 r8c2 r8c7
618 # --------------------------------------------------------------------------
619 # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3]
620 # --------------------------------------------------------------------------
621 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, ALGORITHM=INSTANT;
622 # Metadata from INFORMATION_SCHEMA.TABLES
623 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
624 test/t1 6 0 5
625 # Metadata from INFORMATION_SCHEMA.COLUMNS
626 NAME POS MTYPE PRTYPE HAS_DEFAULT
627 c1 0 13 16712190 0
628 c2 1 13 16711934 0
629 c7 2 13 16711934 1
630 SELECT * FROM t1 ORDER BY c1;
631 c1 c2 c7
632 r1c1 r1c2 c7_def2
633 r2c1 r2c2 c7_def2
634 r3c1 r3c2 c7_def2
635 r4c1 r4c2 c7_def2
636 r5c1 r5c2 c7_def2
637 r6c1 r6c2 c7_def2
638 r7c1 r7c2 c7_def2
639 r8c1 r8c2 c7_def2
640 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7");
641 SELECT * FROM t1 ORDER BY c1;
642 c1 c2 c7
643 r1c1 r1c2 c7_def2
644 r2c1 r2c2 c7_def2
645 r3c1 r3c2 c7_def2
646 r4c1 r4c2 c7_def2
647 r5c1 r5c2 c7_def2
648 r6c1 r6c2 c7_def2
649 r7c1 r7c2 c7_def2
650 r8c1 r8c2 c7_def2
651 r9c1 r9c2 r9c7
652 # ------------------------------------------------------------
653 # Rebuild table. [c1, c2, c7]
654 # ------------------------------------------------------------
655 ALTER TABLE t1 force;
656 # Metadata from INFORMATION_SCHEMA.TABLES
657 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
658 test/t1 6 0 0
659 # Metadata from INFORMATION_SCHEMA.COLUMNS
660 NAME POS MTYPE PRTYPE HAS_DEFAULT
661 c1 0 13 16712190 0
662 c2 1 13 16711934 0
663 c7 2 13 16711934 0
664 SELECT * FROM t1 ORDER BY c1;
665 c1 c2 c7
666 r1c1 r1c2 c7_def2
667 r2c1 r2c2 c7_def2
668 r3c1 r3c2 c7_def2
669 r4c1 r4c2 c7_def2
670 r5c1 r5c2 c7_def2
671 r6c1 r6c2 c7_def2
672 r7c1 r7c2 c7_def2
673 r8c1 r8c2 c7_def2
674 r9c1 r9c2 r9c7
675 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7");
676 SELECT * FROM t1 ORDER BY c1;
677 c1 c2 c7
678 r10c1 r10c2 r10c7
679 r1c1 r1c2 c7_def2
680 r2c1 r2c2 c7_def2
681 r3c1 r3c2 c7_def2
682 r4c1 r4c2 c7_def2
683 r5c1 r5c2 c7_def2
684 r6c1 r6c2 c7_def2
685 r7c1 r7c2 c7_def2
686 r8c1 r8c2 c7_def2
687 r9c1 r9c2 r9c7
688 ###########
689 # CLEANUP #
690 ###########
691 DROP TABLE t1;
692 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
693 INSERT INTO t2 VALUES ("r1c1", "r1c2");
694 SELECT * FROM t2;
695 c1 c2
696 r1c1 r1c2
697 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
698 INSERT INTO t1 VALUES ("r1c1", "r1c2");
699 SELECT * FROM t1;
700 c1 c2
701 r1c1 r1c2
702 BEGIN;
703 SELECT * FROM t2;
704 c1 c2
705 r1c1 r1c2
706 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
707 ALGORITHM=COPY;
708 UPDATE t1 SET c1 = "r1c11";
709 SELECT * FROM t1;
710 c1 c2 c3
711 r1c11 r1c2 c3_def
712 SELECT * FROM t1;
713 ERROR HY000: Table definition has changed, please retry transaction
714 DROP TABLE t1;
715 DROP TABLE t2;
716 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
717 INSERT INTO t2 VALUES ("r1c1", "r1c2");
718 SELECT * FROM t2;
719 c1 c2
720 r1c1 r1c2
721 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
722 INSERT INTO t1 VALUES ("r1c1", "r1c2");
723 SELECT * FROM t1;
724 c1 c2
725 r1c1 r1c2
726 BEGIN;
727 SELECT * FROM t2;
728 c1 c2
729 r1c1 r1c2
730 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
731 ALGORITHM=INSTANT;
732 UPDATE t1 SET c1 = "r1c11";
733 SELECT * FROM t1;
734 c1 c2 c3
735 r1c11 r1c2 c3_def
736 SELECT * FROM t1;
737 ERROR HY000: Table definition has changed, please retry transaction
738 DROP TABLE t1;
739 DROP TABLE t2;
740 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
741 INSERT INTO t2 VALUES ("r1c1", "r1c2");
742 SELECT * FROM t2;
743 c1 c2
744 r1c1 r1c2
745 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
746 INSERT INTO t1 VALUES ("r1c1", "r1c2");
747 SELECT * FROM t1;
748 c1 c2
749 r1c1 r1c2
750 BEGIN;
751 SELECT * FROM t2;
752 c1 c2
753 r1c1 r1c2
754 ALTER TABLE t1 DROP COLUMN c2,
755 ALGORITHM=COPY;
756 UPDATE t1 SET c1 = "r1c11";
757 SELECT * FROM t1;
758 c1
759 r1c11
760 SELECT * FROM t1;
761 ERROR HY000: Table definition has changed, please retry transaction
762 DROP TABLE t1;
763 DROP TABLE t2;
764 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
765 INSERT INTO t2 VALUES ("r1c1", "r1c2");
766 SELECT * FROM t2;
767 c1 c2
768 r1c1 r1c2
769 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
770 INSERT INTO t1 VALUES ("r1c1", "r1c2");
771 SELECT * FROM t1;
772 c1 c2
773 r1c1 r1c2
774 BEGIN;
775 SELECT * FROM t2;
776 c1 c2
777 r1c1 r1c2
778 ALTER TABLE t1 DROP COLUMN c2,
779 ALGORITHM=INSTANT;
780 UPDATE t1 SET c1 = "r1c11";
781 SELECT * FROM t1;
782 c1
783 r1c11
784 SELECT * FROM t1;
785 ERROR HY000: Table definition has changed, please retry transaction
786 DROP TABLE t1;
787 DROP TABLE t2;
788 # ------------------------------------------------------------
789 # Create a table with 3 columns. [c1, c2, c3]
790 # ------------------------------------------------------------
791 CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) , PRIMARY KEY (c1) , KEY (c2)) ROW_FORMAT=REDUNDANT;
792 # Metadata from INFORMATION_SCHEMA.TABLES
793 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
794 test/t1 6 0 0
795 # Metadata from INFORMATION_SCHEMA.COLUMNS
796 NAME POS MTYPE PRTYPE HAS_DEFAULT
797 c1 0 13 16712190 0
798 c2 1 13 16711934 0
799 c3 2 13 16711934 0
800 INSERT INTO t1 values ("r1c1", "r1c2", "r1c3");
801 SELECT * FROM t1 ORDER BY c1;
802 c1 c2 c3
803 r1c1 r1c2 r1c3
804
805 # -----------------------ADD COLUMN TEST----------------------
806
807 # ------------------------------------------------------------
808 # Add a new column at the end. [c1, c2, c3, +c4]
809 # ------------------------------------------------------------
810 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_def", ALGORITHM=INSTANT;
811 # Metadata from INFORMATION_SCHEMA.TABLES
812 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
813 test/t1 7 0 1
814 # Metadata from INFORMATION_SCHEMA.COLUMNS
815 NAME POS MTYPE PRTYPE HAS_DEFAULT
816 c1 0 13 16712190 0
817 c2 1 13 16711934 0
818 c3 2 13 16711934 0
819 c4 3 13 16711934 1
820 INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4");
821 SELECT * FROM t1 ORDER BY c1;
822 c1 c2 c3 c4
823 r1c1 r1c2 r1c3 c4_def
824 r2c1 r2c2 r2c3 r2c4
825 # ------------------------------------------------------------
826 # Add a new column in between. [c1, +c5, c2, c3, c4]
827 # ------------------------------------------------------------
828 ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
829 # Metadata from INFORMATION_SCHEMA.TABLES
830 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
831 test/t1 8 0 2
832 # Metadata from INFORMATION_SCHEMA.COLUMNS
833 NAME POS MTYPE PRTYPE HAS_DEFAULT
834 c1 0 13 16712190 0
835 c5 1 13 16711934 1
836 c2 2 13 16711934 0
837 c3 3 13 16711934 0
838 c4 4 13 16711934 1
839 INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4");
840 SELECT * FROM t1 ORDER BY c1;
841 c1 c5 c2 c3 c4
842 r1c1 NULL r1c2 r1c3 c4_def
843 r2c1 NULL r2c2 r2c3 r2c4
844 r3c1 r3c5 r3c2 r3c3 r3c4
845 # ------------------------------------------------------------
846 # Add a new column at first. [+c6, c1, c5, c2, c3, c4]
847 # ------------------------------------------------------------
848 ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL FIRST, ALGORITHM=INSTANT;
849 # Metadata from INFORMATION_SCHEMA.TABLES
850 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
851 test/t1 9 0 3
852 # Metadata from INFORMATION_SCHEMA.COLUMNS
853 NAME POS MTYPE PRTYPE HAS_DEFAULT
854 c6 0 13 16711934 1
855 c1 1 13 16712190 0
856 c5 2 13 16711934 1
857 c2 3 13 16711934 0
858 c3 4 13 16711934 0
859 c4 5 13 16711934 1
860 INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4");
861 SELECT * FROM t1 ORDER BY c1;
862 c6 c1 c5 c2 c3 c4
863 NULL r1c1 NULL r1c2 r1c3 c4_def
864 NULL r2c1 NULL r2c2 r2c3 r2c4
865 NULL r3c1 r3c5 r3c2 r3c3 r3c4
866 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
867 # ------------------------------------------------------------
868 # Rebuild table. [c6, c1, c5, c2, c3, c4]
869 # ------------------------------------------------------------
870 ALTER TABLE t1 force;
871 # Metadata from INFORMATION_SCHEMA.TABLES
872 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
873 test/t1 9 0 0
874 # Metadata from INFORMATION_SCHEMA.COLUMNS
875 NAME POS MTYPE PRTYPE HAS_DEFAULT
876 c6 0 13 16711934 0
877 c1 1 13 16712190 0
878 c5 2 13 16711934 0
879 c2 3 13 16711934 0
880 c3 4 13 16711934 0
881 c4 5 13 16711934 0
882 SELECT * FROM t1 ORDER BY c1;
883 c6 c1 c5 c2 c3 c4
884 NULL r1c1 NULL r1c2 r1c3 c4_def
885 NULL r2c1 NULL r2c2 r2c3 r2c4
886 NULL r3c1 r3c5 r3c2 r3c3 r3c4
887 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
888
889 # -----------------------DROP COLUMN TEST---------------------
890
891 # ------------------------------------------------------------
892 # Drop a column from the end. [c6, c1, c5, c2, c3, -c4]
893 # ------------------------------------------------------------
894 ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT;
895 # Metadata from INFORMATION_SCHEMA.TABLES
896 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
897 test/t1 8 0 1
898 # Metadata from INFORMATION_SCHEMA.COLUMNS
899 NAME POS MTYPE PRTYPE HAS_DEFAULT
900 c6 0 13 16711934 0
901 c1 1 13 16712190 0
902 c5 2 13 16711934 0
903 c2 3 13 16711934 0
904 c3 4 13 16711934 0
905 SELECT * FROM t1 ORDER BY c1;
906 c6 c1 c5 c2 c3
907 NULL r1c1 NULL r1c2 r1c3
908 NULL r2c1 NULL r2c2 r2c3
909 NULL r3c1 r3c5 r3c2 r3c3
910 r4c6 r4c1 r4c5 r4c2 r4c3
911 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3");
912 SELECT * FROM t1 ORDER BY c1;
913 c6 c1 c5 c2 c3
914 NULL r1c1 NULL r1c2 r1c3
915 NULL r2c1 NULL r2c2 r2c3
916 NULL r3c1 r3c5 r3c2 r3c3
917 r4c6 r4c1 r4c5 r4c2 r4c3
918 r5c6 r5c1 r5c5 r5c2 r5c3
919 # ------------------------------------------------------------
920 # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4]
921 # ------------------------------------------------------------
922 ALTER TABLE t1 DROP COLUMN c5, ALGORITHM=INSTANT;
923 # Metadata from INFORMATION_SCHEMA.TABLES
924 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
925 test/t1 7 0 2
926 # Metadata from INFORMATION_SCHEMA.COLUMNS
927 NAME POS MTYPE PRTYPE HAS_DEFAULT
928 c6 0 13 16711934 0
929 c1 1 13 16712190 0
930 c2 2 13 16711934 0
931 c3 3 13 16711934 0
932 SELECT * FROM t1 ORDER BY c1;
933 c6 c1 c2 c3
934 NULL r1c1 r1c2 r1c3
935 NULL r2c1 r2c2 r2c3
936 NULL r3c1 r3c2 r3c3
937 r4c6 r4c1 r4c2 r4c3
938 r5c6 r5c1 r5c2 r5c3
939 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3");
940 SELECT * FROM t1 ORDER BY c1;
941 c6 c1 c2 c3
942 NULL r1c1 r1c2 r1c3
943 NULL r2c1 r2c2 r2c3
944 NULL r3c1 r3c2 r3c3
945 r4c6 r4c1 r4c2 r4c3
946 r5c6 r5c1 r5c2 r5c3
947 r6c6 r6c1 r6c2 r6c3
948 # ------------------------------------------------------------
949 # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5]
950 # ------------------------------------------------------------
951 ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT;
952 # Metadata from INFORMATION_SCHEMA.TABLES
953 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
954 test/t1 6 0 3
955 # Metadata from INFORMATION_SCHEMA.COLUMNS
956 NAME POS MTYPE PRTYPE HAS_DEFAULT
957 c1 0 13 16712190 0
958 c2 1 13 16711934 0
959 c3 2 13 16711934 0
960 SELECT * FROM t1 ORDER BY c1;
961 c1 c2 c3
962 r1c1 r1c2 r1c3
963 r2c1 r2c2 r2c3
964 r3c1 r3c2 r3c3
965 r4c1 r4c2 r4c3
966 r5c1 r5c2 r5c3
967 r6c1 r6c2 r6c3
968 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3");
969 SELECT * FROM t1 ORDER BY c1;
970 c1 c2 c3
971 r1c1 r1c2 r1c3
972 r2c1 r2c2 r2c3
973 r3c1 r3c2 r3c3
974 r4c1 r4c2 r4c3
975 r5c1 r5c2 r5c3
976 r6c1 r6c2 r6c3
977 r7c1 r7c2 r7c3
978 # ------------------------------------------------------------
979 # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6]
980 # ------------------------------------------------------------
981 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", DROP COLUMN c3, ALGORITHM=INSTANT;
982 # Metadata from INFORMATION_SCHEMA.TABLES
983 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
984 test/t1 6 0 4
985 # Metadata from INFORMATION_SCHEMA.COLUMNS
986 NAME POS MTYPE PRTYPE HAS_DEFAULT
987 c1 0 13 16712190 0
988 c2 1 13 16711934 0
989 c7 2 13 16711934 1
990 SELECT * FROM t1 ORDER BY c1;
991 c1 c2 c7
992 r1c1 r1c2 c7_def
993 r2c1 r2c2 c7_def
994 r3c1 r3c2 c7_def
995 r4c1 r4c2 c7_def
996 r5c1 r5c2 c7_def
997 r6c1 r6c2 c7_def
998 r7c1 r7c2 c7_def
999 INSERT INTO t1 values ("r8c1", "r8c2", "r8c7");
1000 SELECT * FROM t1 ORDER BY c1;
1001 c1 c2 c7
1002 r1c1 r1c2 c7_def
1003 r2c1 r2c2 c7_def
1004 r3c1 r3c2 c7_def
1005 r4c1 r4c2 c7_def
1006 r5c1 r5c2 c7_def
1007 r6c1 r6c2 c7_def
1008 r7c1 r7c2 c7_def
1009 r8c1 r8c2 r8c7
1010 # --------------------------------------------------------------------------
1011 # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3]
1012 # --------------------------------------------------------------------------
1013 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, ALGORITHM=INSTANT;
1014 # Metadata from INFORMATION_SCHEMA.TABLES
1015 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1016 test/t1 6 0 5
1017 # Metadata from INFORMATION_SCHEMA.COLUMNS
1018 NAME POS MTYPE PRTYPE HAS_DEFAULT
1019 c1 0 13 16712190 0
1020 c2 1 13 16711934 0
1021 c7 2 13 16711934 1
1022 SELECT * FROM t1 ORDER BY c1;
1023 c1 c2 c7
1024 r1c1 r1c2 c7_def2
1025 r2c1 r2c2 c7_def2
1026 r3c1 r3c2 c7_def2
1027 r4c1 r4c2 c7_def2
1028 r5c1 r5c2 c7_def2
1029 r6c1 r6c2 c7_def2
1030 r7c1 r7c2 c7_def2
1031 r8c1 r8c2 c7_def2
1032 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7");
1033 SELECT * FROM t1 ORDER BY c1;
1034 c1 c2 c7
1035 r1c1 r1c2 c7_def2
1036 r2c1 r2c2 c7_def2
1037 r3c1 r3c2 c7_def2
1038 r4c1 r4c2 c7_def2
1039 r5c1 r5c2 c7_def2
1040 r6c1 r6c2 c7_def2
1041 r7c1 r7c2 c7_def2
1042 r8c1 r8c2 c7_def2
1043 r9c1 r9c2 r9c7
1044 # ------------------------------------------------------------
1045 # Rebuild table. [c1, c2, c7]
1046 # ------------------------------------------------------------
1047 ALTER TABLE t1 force;
1048 # Metadata from INFORMATION_SCHEMA.TABLES
1049 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1050 test/t1 6 0 0
1051 # Metadata from INFORMATION_SCHEMA.COLUMNS
1052 NAME POS MTYPE PRTYPE HAS_DEFAULT
1053 c1 0 13 16712190 0
1054 c2 1 13 16711934 0
1055 c7 2 13 16711934 0
1056 SELECT * FROM t1 ORDER BY c1;
1057 c1 c2 c7
1058 r1c1 r1c2 c7_def2
1059 r2c1 r2c2 c7_def2
1060 r3c1 r3c2 c7_def2
1061 r4c1 r4c2 c7_def2
1062 r5c1 r5c2 c7_def2
1063 r6c1 r6c2 c7_def2
1064 r7c1 r7c2 c7_def2
1065 r8c1 r8c2 c7_def2
1066 r9c1 r9c2 r9c7
1067 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7");
1068 SELECT * FROM t1 ORDER BY c1;
1069 c1 c2 c7
1070 r10c1 r10c2 r10c7
1071 r1c1 r1c2 c7_def2
1072 r2c1 r2c2 c7_def2
1073 r3c1 r3c2 c7_def2
1074 r4c1 r4c2 c7_def2
1075 r5c1 r5c2 c7_def2
1076 r6c1 r6c2 c7_def2
1077 r7c1 r7c2 c7_def2
1078 r8c1 r8c2 c7_def2
1079 r9c1 r9c2 r9c7
1080 ###########
1081 # CLEANUP #
1082 ###########
1083 DROP TABLE t1;
1084 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
1085 INSERT INTO t2 VALUES ("r1c1", "r1c2");
1086 SELECT * FROM t2;
1087 c1 c2
1088 r1c1 r1c2
1089 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
1090 INSERT INTO t1 VALUES ("r1c1", "r1c2");
1091 SELECT * FROM t1;
1092 c1 c2
1093 r1c1 r1c2
1094 BEGIN;
1095 SELECT * FROM t2;
1096 c1 c2
1097 r1c1 r1c2
1098 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
1099 ALGORITHM=COPY;
1100 UPDATE t1 SET c1 = "r1c11";
1101 SELECT * FROM t1;
1102 c1 c2 c3
1103 r1c11 r1c2 c3_def
1104 SELECT * FROM t1;
1105 ERROR HY000: Table definition has changed, please retry transaction
1106 DROP TABLE t1;
1107 DROP TABLE t2;
1108 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
1109 INSERT INTO t2 VALUES ("r1c1", "r1c2");
1110 SELECT * FROM t2;
1111 c1 c2
1112 r1c1 r1c2
1113 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
1114 INSERT INTO t1 VALUES ("r1c1", "r1c2");
1115 SELECT * FROM t1;
1116 c1 c2
1117 r1c1 r1c2
1118 BEGIN;
1119 SELECT * FROM t2;
1120 c1 c2
1121 r1c1 r1c2
1122 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
1123 ALGORITHM=INSTANT;
1124 UPDATE t1 SET c1 = "r1c11";
1125 SELECT * FROM t1;
1126 c1 c2 c3
1127 r1c11 r1c2 c3_def
1128 SELECT * FROM t1;
1129 ERROR HY000: Table definition has changed, please retry transaction
1130 DROP TABLE t1;
1131 DROP TABLE t2;
1132 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
1133 INSERT INTO t2 VALUES ("r1c1", "r1c2");
1134 SELECT * FROM t2;
1135 c1 c2
1136 r1c1 r1c2
1137 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
1138 INSERT INTO t1 VALUES ("r1c1", "r1c2");
1139 SELECT * FROM t1;
1140 c1 c2
1141 r1c1 r1c2
1142 BEGIN;
1143 SELECT * FROM t2;
1144 c1 c2
1145 r1c1 r1c2
1146 ALTER TABLE t1 DROP COLUMN c2,
1147 ALGORITHM=COPY;
1148 UPDATE t1 SET c1 = "r1c11";
1149 SELECT * FROM t1;
1150 c1
1151 r1c11
1152 SELECT * FROM t1;
1153 ERROR HY000: Table definition has changed, please retry transaction
1154 DROP TABLE t1;
1155 DROP TABLE t2;
1156 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
1157 INSERT INTO t2 VALUES ("r1c1", "r1c2");
1158 SELECT * FROM t2;
1159 c1 c2
1160 r1c1 r1c2
1161 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=REDUNDANT;
1162 INSERT INTO t1 VALUES ("r1c1", "r1c2");
1163 SELECT * FROM t1;
1164 c1 c2
1165 r1c1 r1c2
1166 BEGIN;
1167 SELECT * FROM t2;
1168 c1 c2
1169 r1c1 r1c2
1170 ALTER TABLE t1 DROP COLUMN c2,
1171 ALGORITHM=INSTANT;
1172 UPDATE t1 SET c1 = "r1c11";
1173 SELECT * FROM t1;
1174 c1
1175 r1c11
1176 SELECT * FROM t1;
1177 ERROR HY000: Table definition has changed, please retry transaction
1178 DROP TABLE t1;
1179 DROP TABLE t2;
1180 ############################################
1181 # Test instant ADD/DROP COLUMN for DYNAMIC format
1182 ############################################
1183 # ------------------------------------------------------------
1184 # Create a table with 3 columns. [c1, c2, c3]
1185 # ------------------------------------------------------------
1186 CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) ) ROW_FORMAT=DYNAMIC;
1187 # Metadata from INFORMATION_SCHEMA.TABLES
1188 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1189 test/t1 6 0 0
1190 # Metadata from INFORMATION_SCHEMA.COLUMNS
1191 NAME POS MTYPE PRTYPE HAS_DEFAULT
1192 c1 0 13 16711934 0
1193 c2 1 13 16711934 0
1194 c3 2 13 16711934 0
1195 INSERT INTO t1 values ("r1c1", "r1c2", "r1c3");
1196 SELECT * FROM t1 ORDER BY c1;
1197 c1 c2 c3
1198 r1c1 r1c2 r1c3
1199
1200 # -----------------------ADD COLUMN TEST----------------------
1201
1202 # ------------------------------------------------------------
1203 # Add a new column at the end. [c1, c2, c3, +c4]
1204 # ------------------------------------------------------------
1205 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_def", ALGORITHM=INSTANT;
1206 # Metadata from INFORMATION_SCHEMA.TABLES
1207 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1208 test/t1 7 0 1
1209 # Metadata from INFORMATION_SCHEMA.COLUMNS
1210 NAME POS MTYPE PRTYPE HAS_DEFAULT
1211 c1 0 13 16711934 0
1212 c2 1 13 16711934 0
1213 c3 2 13 16711934 0
1214 c4 3 13 16711934 1
1215 INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4");
1216 SELECT * FROM t1 ORDER BY c1;
1217 c1 c2 c3 c4
1218 r1c1 r1c2 r1c3 c4_def
1219 r2c1 r2c2 r2c3 r2c4
1220 # ------------------------------------------------------------
1221 # Add a new column in between. [c1, +c5, c2, c3, c4]
1222 # ------------------------------------------------------------
1223 ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
1224 # Metadata from INFORMATION_SCHEMA.TABLES
1225 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1226 test/t1 8 0 2
1227 # Metadata from INFORMATION_SCHEMA.COLUMNS
1228 NAME POS MTYPE PRTYPE HAS_DEFAULT
1229 c1 0 13 16711934 0
1230 c5 1 13 16711934 1
1231 c2 2 13 16711934 0
1232 c3 3 13 16711934 0
1233 c4 4 13 16711934 1
1234 INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4");
1235 SELECT * FROM t1 ORDER BY c1;
1236 c1 c5 c2 c3 c4
1237 r1c1 NULL r1c2 r1c3 c4_def
1238 r2c1 NULL r2c2 r2c3 r2c4
1239 r3c1 r3c5 r3c2 r3c3 r3c4
1240 # ------------------------------------------------------------
1241 # Add a new column at first. [+c6, c1, c5, c2, c3, c4]
1242 # ------------------------------------------------------------
1243 ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL FIRST, ALGORITHM=INSTANT;
1244 # Metadata from INFORMATION_SCHEMA.TABLES
1245 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1246 test/t1 9 0 3
1247 # Metadata from INFORMATION_SCHEMA.COLUMNS
1248 NAME POS MTYPE PRTYPE HAS_DEFAULT
1249 c6 0 13 16711934 1
1250 c1 1 13 16711934 0
1251 c5 2 13 16711934 1
1252 c2 3 13 16711934 0
1253 c3 4 13 16711934 0
1254 c4 5 13 16711934 1
1255 INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4");
1256 SELECT * FROM t1 ORDER BY c1;
1257 c6 c1 c5 c2 c3 c4
1258 NULL r1c1 NULL r1c2 r1c3 c4_def
1259 NULL r2c1 NULL r2c2 r2c3 r2c4
1260 NULL r3c1 r3c5 r3c2 r3c3 r3c4
1261 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
1262 # ------------------------------------------------------------
1263 # Rebuild table. [c6, c1, c5, c2, c3, c4]
1264 # ------------------------------------------------------------
1265 ALTER TABLE t1 force;
1266 # Metadata from INFORMATION_SCHEMA.TABLES
1267 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1268 test/t1 9 0 0
1269 # Metadata from INFORMATION_SCHEMA.COLUMNS
1270 NAME POS MTYPE PRTYPE HAS_DEFAULT
1271 c6 0 13 16711934 0
1272 c1 1 13 16711934 0
1273 c5 2 13 16711934 0
1274 c2 3 13 16711934 0
1275 c3 4 13 16711934 0
1276 c4 5 13 16711934 0
1277 SELECT * FROM t1 ORDER BY c1;
1278 c6 c1 c5 c2 c3 c4
1279 NULL r1c1 NULL r1c2 r1c3 c4_def
1280 NULL r2c1 NULL r2c2 r2c3 r2c4
1281 NULL r3c1 r3c5 r3c2 r3c3 r3c4
1282 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
1283
1284 # -----------------------DROP COLUMN TEST---------------------
1285
1286 # ------------------------------------------------------------
1287 # Drop a column from the end. [c6, c1, c5, c2, c3, -c4]
1288 # ------------------------------------------------------------
1289 ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT;
1290 # Metadata from INFORMATION_SCHEMA.TABLES
1291 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1292 test/t1 8 0 1
1293 # Metadata from INFORMATION_SCHEMA.COLUMNS
1294 NAME POS MTYPE PRTYPE HAS_DEFAULT
1295 c6 0 13 16711934 0
1296 c1 1 13 16711934 0
1297 c5 2 13 16711934 0
1298 c2 3 13 16711934 0
1299 c3 4 13 16711934 0
1300 SELECT * FROM t1 ORDER BY c1;
1301 c6 c1 c5 c2 c3
1302 NULL r1c1 NULL r1c2 r1c3
1303 NULL r2c1 NULL r2c2 r2c3
1304 NULL r3c1 r3c5 r3c2 r3c3
1305 r4c6 r4c1 r4c5 r4c2 r4c3
1306 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3");
1307 SELECT * FROM t1 ORDER BY c1;
1308 c6 c1 c5 c2 c3
1309 NULL r1c1 NULL r1c2 r1c3
1310 NULL r2c1 NULL r2c2 r2c3
1311 NULL r3c1 r3c5 r3c2 r3c3
1312 r4c6 r4c1 r4c5 r4c2 r4c3
1313 r5c6 r5c1 r5c5 r5c2 r5c3
1314 # ------------------------------------------------------------
1315 # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4]
1316 # ------------------------------------------------------------
1317 ALTER TABLE t1 DROP COLUMN c5, ALGORITHM=INSTANT;
1318 # Metadata from INFORMATION_SCHEMA.TABLES
1319 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1320 test/t1 7 0 2
1321 # Metadata from INFORMATION_SCHEMA.COLUMNS
1322 NAME POS MTYPE PRTYPE HAS_DEFAULT
1323 c6 0 13 16711934 0
1324 c1 1 13 16711934 0
1325 c2 2 13 16711934 0
1326 c3 3 13 16711934 0
1327 SELECT * FROM t1 ORDER BY c1;
1328 c6 c1 c2 c3
1329 NULL r1c1 r1c2 r1c3
1330 NULL r2c1 r2c2 r2c3
1331 NULL r3c1 r3c2 r3c3
1332 r4c6 r4c1 r4c2 r4c3
1333 r5c6 r5c1 r5c2 r5c3
1334 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3");
1335 SELECT * FROM t1 ORDER BY c1;
1336 c6 c1 c2 c3
1337 NULL r1c1 r1c2 r1c3
1338 NULL r2c1 r2c2 r2c3
1339 NULL r3c1 r3c2 r3c3
1340 r4c6 r4c1 r4c2 r4c3
1341 r5c6 r5c1 r5c2 r5c3
1342 r6c6 r6c1 r6c2 r6c3
1343 # ------------------------------------------------------------
1344 # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5]
1345 # ------------------------------------------------------------
1346 ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT;
1347 # Metadata from INFORMATION_SCHEMA.TABLES
1348 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1349 test/t1 6 0 3
1350 # Metadata from INFORMATION_SCHEMA.COLUMNS
1351 NAME POS MTYPE PRTYPE HAS_DEFAULT
1352 c1 0 13 16711934 0
1353 c2 1 13 16711934 0
1354 c3 2 13 16711934 0
1355 SELECT * FROM t1 ORDER BY c1;
1356 c1 c2 c3
1357 r1c1 r1c2 r1c3
1358 r2c1 r2c2 r2c3
1359 r3c1 r3c2 r3c3
1360 r4c1 r4c2 r4c3
1361 r5c1 r5c2 r5c3
1362 r6c1 r6c2 r6c3
1363 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3");
1364 SELECT * FROM t1 ORDER BY c1;
1365 c1 c2 c3
1366 r1c1 r1c2 r1c3
1367 r2c1 r2c2 r2c3
1368 r3c1 r3c2 r3c3
1369 r4c1 r4c2 r4c3
1370 r5c1 r5c2 r5c3
1371 r6c1 r6c2 r6c3
1372 r7c1 r7c2 r7c3
1373 # ------------------------------------------------------------
1374 # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6]
1375 # ------------------------------------------------------------
1376 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", DROP COLUMN c3, ALGORITHM=INSTANT;
1377 # Metadata from INFORMATION_SCHEMA.TABLES
1378 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1379 test/t1 6 0 4
1380 # Metadata from INFORMATION_SCHEMA.COLUMNS
1381 NAME POS MTYPE PRTYPE HAS_DEFAULT
1382 c1 0 13 16711934 0
1383 c2 1 13 16711934 0
1384 c7 2 13 16711934 1
1385 SELECT * FROM t1 ORDER BY c1;
1386 c1 c2 c7
1387 r1c1 r1c2 c7_def
1388 r2c1 r2c2 c7_def
1389 r3c1 r3c2 c7_def
1390 r4c1 r4c2 c7_def
1391 r5c1 r5c2 c7_def
1392 r6c1 r6c2 c7_def
1393 r7c1 r7c2 c7_def
1394 INSERT INTO t1 values ("r8c1", "r8c2", "r8c7");
1395 SELECT * FROM t1 ORDER BY c1;
1396 c1 c2 c7
1397 r1c1 r1c2 c7_def
1398 r2c1 r2c2 c7_def
1399 r3c1 r3c2 c7_def
1400 r4c1 r4c2 c7_def
1401 r5c1 r5c2 c7_def
1402 r6c1 r6c2 c7_def
1403 r7c1 r7c2 c7_def
1404 r8c1 r8c2 r8c7
1405 # --------------------------------------------------------------------------
1406 # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3]
1407 # --------------------------------------------------------------------------
1408 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, ALGORITHM=INSTANT;
1409 # Metadata from INFORMATION_SCHEMA.TABLES
1410 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1411 test/t1 6 0 5
1412 # Metadata from INFORMATION_SCHEMA.COLUMNS
1413 NAME POS MTYPE PRTYPE HAS_DEFAULT
1414 c1 0 13 16711934 0
1415 c2 1 13 16711934 0
1416 c7 2 13 16711934 1
1417 SELECT * FROM t1 ORDER BY c1;
1418 c1 c2 c7
1419 r1c1 r1c2 c7_def2
1420 r2c1 r2c2 c7_def2
1421 r3c1 r3c2 c7_def2
1422 r4c1 r4c2 c7_def2
1423 r5c1 r5c2 c7_def2
1424 r6c1 r6c2 c7_def2
1425 r7c1 r7c2 c7_def2
1426 r8c1 r8c2 c7_def2
1427 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7");
1428 SELECT * FROM t1 ORDER BY c1;
1429 c1 c2 c7
1430 r1c1 r1c2 c7_def2
1431 r2c1 r2c2 c7_def2
1432 r3c1 r3c2 c7_def2
1433 r4c1 r4c2 c7_def2
1434 r5c1 r5c2 c7_def2
1435 r6c1 r6c2 c7_def2
1436 r7c1 r7c2 c7_def2
1437 r8c1 r8c2 c7_def2
1438 r9c1 r9c2 r9c7
1439 # ------------------------------------------------------------
1440 # Rebuild table. [c1, c2, c7]
1441 # ------------------------------------------------------------
1442 ALTER TABLE t1 force;
1443 # Metadata from INFORMATION_SCHEMA.TABLES
1444 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1445 test/t1 6 0 0
1446 # Metadata from INFORMATION_SCHEMA.COLUMNS
1447 NAME POS MTYPE PRTYPE HAS_DEFAULT
1448 c1 0 13 16711934 0
1449 c2 1 13 16711934 0
1450 c7 2 13 16711934 0
1451 SELECT * FROM t1 ORDER BY c1;
1452 c1 c2 c7
1453 r1c1 r1c2 c7_def2
1454 r2c1 r2c2 c7_def2
1455 r3c1 r3c2 c7_def2
1456 r4c1 r4c2 c7_def2
1457 r5c1 r5c2 c7_def2
1458 r6c1 r6c2 c7_def2
1459 r7c1 r7c2 c7_def2
1460 r8c1 r8c2 c7_def2
1461 r9c1 r9c2 r9c7
1462 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7");
1463 SELECT * FROM t1 ORDER BY c1;
1464 c1 c2 c7
1465 r10c1 r10c2 r10c7
1466 r1c1 r1c2 c7_def2
1467 r2c1 r2c2 c7_def2
1468 r3c1 r3c2 c7_def2
1469 r4c1 r4c2 c7_def2
1470 r5c1 r5c2 c7_def2
1471 r6c1 r6c2 c7_def2
1472 r7c1 r7c2 c7_def2
1473 r8c1 r8c2 c7_def2
1474 r9c1 r9c2 r9c7
1475 ###########
1476 # CLEANUP #
1477 ###########
1478 DROP TABLE t1;
1479 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
1480 INSERT INTO t2 VALUES ("r1c1", "r1c2");
1481 SELECT * FROM t2;
1482 c1 c2
1483 r1c1 r1c2
1484 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
1485 INSERT INTO t1 VALUES ("r1c1", "r1c2");
1486 SELECT * FROM t1;
1487 c1 c2
1488 r1c1 r1c2
1489 BEGIN;
1490 SELECT * FROM t2;
1491 c1 c2
1492 r1c1 r1c2
1493 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
1494 ALGORITHM=COPY;
1495 UPDATE t1 SET c1 = "r1c11";
1496 SELECT * FROM t1;
1497 c1 c2 c3
1498 r1c11 r1c2 c3_def
1499 SELECT * FROM t1;
1500 ERROR HY000: Table definition has changed, please retry transaction
1501 DROP TABLE t1;
1502 DROP TABLE t2;
1503 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
1504 INSERT INTO t2 VALUES ("r1c1", "r1c2");
1505 SELECT * FROM t2;
1506 c1 c2
1507 r1c1 r1c2
1508 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
1509 INSERT INTO t1 VALUES ("r1c1", "r1c2");
1510 SELECT * FROM t1;
1511 c1 c2
1512 r1c1 r1c2
1513 BEGIN;
1514 SELECT * FROM t2;
1515 c1 c2
1516 r1c1 r1c2
1517 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
1518 ALGORITHM=INSTANT;
1519 UPDATE t1 SET c1 = "r1c11";
1520 SELECT * FROM t1;
1521 c1 c2 c3
1522 r1c11 r1c2 c3_def
1523 SELECT * FROM t1;
1524 ERROR HY000: Table definition has changed, please retry transaction
1525 DROP TABLE t1;
1526 DROP TABLE t2;
1527 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
1528 INSERT INTO t2 VALUES ("r1c1", "r1c2");
1529 SELECT * FROM t2;
1530 c1 c2
1531 r1c1 r1c2
1532 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
1533 INSERT INTO t1 VALUES ("r1c1", "r1c2");
1534 SELECT * FROM t1;
1535 c1 c2
1536 r1c1 r1c2
1537 BEGIN;
1538 SELECT * FROM t2;
1539 c1 c2
1540 r1c1 r1c2
1541 ALTER TABLE t1 DROP COLUMN c2,
1542 ALGORITHM=COPY;
1543 UPDATE t1 SET c1 = "r1c11";
1544 SELECT * FROM t1;
1545 c1
1546 r1c11
1547 SELECT * FROM t1;
1548 ERROR HY000: Table definition has changed, please retry transaction
1549 DROP TABLE t1;
1550 DROP TABLE t2;
1551 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
1552 INSERT INTO t2 VALUES ("r1c1", "r1c2");
1553 SELECT * FROM t2;
1554 c1 c2
1555 r1c1 r1c2
1556 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
1557 INSERT INTO t1 VALUES ("r1c1", "r1c2");
1558 SELECT * FROM t1;
1559 c1 c2
1560 r1c1 r1c2
1561 BEGIN;
1562 SELECT * FROM t2;
1563 c1 c2
1564 r1c1 r1c2
1565 ALTER TABLE t1 DROP COLUMN c2,
1566 ALGORITHM=INSTANT;
1567 UPDATE t1 SET c1 = "r1c11";
1568 SELECT * FROM t1;
1569 c1
1570 r1c11
1571 SELECT * FROM t1;
1572 ERROR HY000: Table definition has changed, please retry transaction
1573 DROP TABLE t1;
1574 DROP TABLE t2;
1575 # ------------------------------------------------------------
1576 # Create a table with 3 columns. [c1, c2, c3]
1577 # ------------------------------------------------------------
1578 CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) , PRIMARY KEY (c1) ) ROW_FORMAT=DYNAMIC;
1579 # Metadata from INFORMATION_SCHEMA.TABLES
1580 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1581 test/t1 6 0 0
1582 # Metadata from INFORMATION_SCHEMA.COLUMNS
1583 NAME POS MTYPE PRTYPE HAS_DEFAULT
1584 c1 0 13 16712190 0
1585 c2 1 13 16711934 0
1586 c3 2 13 16711934 0
1587 INSERT INTO t1 values ("r1c1", "r1c2", "r1c3");
1588 SELECT * FROM t1 ORDER BY c1;
1589 c1 c2 c3
1590 r1c1 r1c2 r1c3
1591
1592 # -----------------------ADD COLUMN TEST----------------------
1593
1594 # ------------------------------------------------------------
1595 # Add a new column at the end. [c1, c2, c3, +c4]
1596 # ------------------------------------------------------------
1597 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_def", ALGORITHM=INSTANT;
1598 # Metadata from INFORMATION_SCHEMA.TABLES
1599 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1600 test/t1 7 0 1
1601 # Metadata from INFORMATION_SCHEMA.COLUMNS
1602 NAME POS MTYPE PRTYPE HAS_DEFAULT
1603 c1 0 13 16712190 0
1604 c2 1 13 16711934 0
1605 c3 2 13 16711934 0
1606 c4 3 13 16711934 1
1607 INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4");
1608 SELECT * FROM t1 ORDER BY c1;
1609 c1 c2 c3 c4
1610 r1c1 r1c2 r1c3 c4_def
1611 r2c1 r2c2 r2c3 r2c4
1612 # ------------------------------------------------------------
1613 # Add a new column in between. [c1, +c5, c2, c3, c4]
1614 # ------------------------------------------------------------
1615 ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
1616 # Metadata from INFORMATION_SCHEMA.TABLES
1617 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1618 test/t1 8 0 2
1619 # Metadata from INFORMATION_SCHEMA.COLUMNS
1620 NAME POS MTYPE PRTYPE HAS_DEFAULT
1621 c1 0 13 16712190 0
1622 c5 1 13 16711934 1
1623 c2 2 13 16711934 0
1624 c3 3 13 16711934 0
1625 c4 4 13 16711934 1
1626 INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4");
1627 SELECT * FROM t1 ORDER BY c1;
1628 c1 c5 c2 c3 c4
1629 r1c1 NULL r1c2 r1c3 c4_def
1630 r2c1 NULL r2c2 r2c3 r2c4
1631 r3c1 r3c5 r3c2 r3c3 r3c4
1632 # ------------------------------------------------------------
1633 # Add a new column at first. [+c6, c1, c5, c2, c3, c4]
1634 # ------------------------------------------------------------
1635 ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL FIRST, ALGORITHM=INSTANT;
1636 # Metadata from INFORMATION_SCHEMA.TABLES
1637 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1638 test/t1 9 0 3
1639 # Metadata from INFORMATION_SCHEMA.COLUMNS
1640 NAME POS MTYPE PRTYPE HAS_DEFAULT
1641 c6 0 13 16711934 1
1642 c1 1 13 16712190 0
1643 c5 2 13 16711934 1
1644 c2 3 13 16711934 0
1645 c3 4 13 16711934 0
1646 c4 5 13 16711934 1
1647 INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4");
1648 SELECT * FROM t1 ORDER BY c1;
1649 c6 c1 c5 c2 c3 c4
1650 NULL r1c1 NULL r1c2 r1c3 c4_def
1651 NULL r2c1 NULL r2c2 r2c3 r2c4
1652 NULL r3c1 r3c5 r3c2 r3c3 r3c4
1653 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
1654 # ------------------------------------------------------------
1655 # Rebuild table. [c6, c1, c5, c2, c3, c4]
1656 # ------------------------------------------------------------
1657 ALTER TABLE t1 force;
1658 # Metadata from INFORMATION_SCHEMA.TABLES
1659 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1660 test/t1 9 0 0
1661 # Metadata from INFORMATION_SCHEMA.COLUMNS
1662 NAME POS MTYPE PRTYPE HAS_DEFAULT
1663 c6 0 13 16711934 0
1664 c1 1 13 16712190 0
1665 c5 2 13 16711934 0
1666 c2 3 13 16711934 0
1667 c3 4 13 16711934 0
1668 c4 5 13 16711934 0
1669 SELECT * FROM t1 ORDER BY c1;
1670 c6 c1 c5 c2 c3 c4
1671 NULL r1c1 NULL r1c2 r1c3 c4_def
1672 NULL r2c1 NULL r2c2 r2c3 r2c4
1673 NULL r3c1 r3c5 r3c2 r3c3 r3c4
1674 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
1675
1676 # -----------------------DROP COLUMN TEST---------------------
1677
1678 # ------------------------------------------------------------
1679 # Drop a column from the end. [c6, c1, c5, c2, c3, -c4]
1680 # ------------------------------------------------------------
1681 ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT;
1682 # Metadata from INFORMATION_SCHEMA.TABLES
1683 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1684 test/t1 8 0 1
1685 # Metadata from INFORMATION_SCHEMA.COLUMNS
1686 NAME POS MTYPE PRTYPE HAS_DEFAULT
1687 c6 0 13 16711934 0
1688 c1 1 13 16712190 0
1689 c5 2 13 16711934 0
1690 c2 3 13 16711934 0
1691 c3 4 13 16711934 0
1692 SELECT * FROM t1 ORDER BY c1;
1693 c6 c1 c5 c2 c3
1694 NULL r1c1 NULL r1c2 r1c3
1695 NULL r2c1 NULL r2c2 r2c3
1696 NULL r3c1 r3c5 r3c2 r3c3
1697 r4c6 r4c1 r4c5 r4c2 r4c3
1698 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3");
1699 SELECT * FROM t1 ORDER BY c1;
1700 c6 c1 c5 c2 c3
1701 NULL r1c1 NULL r1c2 r1c3
1702 NULL r2c1 NULL r2c2 r2c3
1703 NULL r3c1 r3c5 r3c2 r3c3
1704 r4c6 r4c1 r4c5 r4c2 r4c3
1705 r5c6 r5c1 r5c5 r5c2 r5c3
1706 # ------------------------------------------------------------
1707 # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4]
1708 # ------------------------------------------------------------
1709 ALTER TABLE t1 DROP COLUMN c5, ALGORITHM=INSTANT;
1710 # Metadata from INFORMATION_SCHEMA.TABLES
1711 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1712 test/t1 7 0 2
1713 # Metadata from INFORMATION_SCHEMA.COLUMNS
1714 NAME POS MTYPE PRTYPE HAS_DEFAULT
1715 c6 0 13 16711934 0
1716 c1 1 13 16712190 0
1717 c2 2 13 16711934 0
1718 c3 3 13 16711934 0
1719 SELECT * FROM t1 ORDER BY c1;
1720 c6 c1 c2 c3
1721 NULL r1c1 r1c2 r1c3
1722 NULL r2c1 r2c2 r2c3
1723 NULL r3c1 r3c2 r3c3
1724 r4c6 r4c1 r4c2 r4c3
1725 r5c6 r5c1 r5c2 r5c3
1726 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3");
1727 SELECT * FROM t1 ORDER BY c1;
1728 c6 c1 c2 c3
1729 NULL r1c1 r1c2 r1c3
1730 NULL r2c1 r2c2 r2c3
1731 NULL r3c1 r3c2 r3c3
1732 r4c6 r4c1 r4c2 r4c3
1733 r5c6 r5c1 r5c2 r5c3
1734 r6c6 r6c1 r6c2 r6c3
1735 # ------------------------------------------------------------
1736 # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5]
1737 # ------------------------------------------------------------
1738 ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT;
1739 # Metadata from INFORMATION_SCHEMA.TABLES
1740 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1741 test/t1 6 0 3
1742 # Metadata from INFORMATION_SCHEMA.COLUMNS
1743 NAME POS MTYPE PRTYPE HAS_DEFAULT
1744 c1 0 13 16712190 0
1745 c2 1 13 16711934 0
1746 c3 2 13 16711934 0
1747 SELECT * FROM t1 ORDER BY c1;
1748 c1 c2 c3
1749 r1c1 r1c2 r1c3
1750 r2c1 r2c2 r2c3
1751 r3c1 r3c2 r3c3
1752 r4c1 r4c2 r4c3
1753 r5c1 r5c2 r5c3
1754 r6c1 r6c2 r6c3
1755 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3");
1756 SELECT * FROM t1 ORDER BY c1;
1757 c1 c2 c3
1758 r1c1 r1c2 r1c3
1759 r2c1 r2c2 r2c3
1760 r3c1 r3c2 r3c3
1761 r4c1 r4c2 r4c3
1762 r5c1 r5c2 r5c3
1763 r6c1 r6c2 r6c3
1764 r7c1 r7c2 r7c3
1765 # ------------------------------------------------------------
1766 # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6]
1767 # ------------------------------------------------------------
1768 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", DROP COLUMN c3, ALGORITHM=INSTANT;
1769 # Metadata from INFORMATION_SCHEMA.TABLES
1770 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1771 test/t1 6 0 4
1772 # Metadata from INFORMATION_SCHEMA.COLUMNS
1773 NAME POS MTYPE PRTYPE HAS_DEFAULT
1774 c1 0 13 16712190 0
1775 c2 1 13 16711934 0
1776 c7 2 13 16711934 1
1777 SELECT * FROM t1 ORDER BY c1;
1778 c1 c2 c7
1779 r1c1 r1c2 c7_def
1780 r2c1 r2c2 c7_def
1781 r3c1 r3c2 c7_def
1782 r4c1 r4c2 c7_def
1783 r5c1 r5c2 c7_def
1784 r6c1 r6c2 c7_def
1785 r7c1 r7c2 c7_def
1786 INSERT INTO t1 values ("r8c1", "r8c2", "r8c7");
1787 SELECT * FROM t1 ORDER BY c1;
1788 c1 c2 c7
1789 r1c1 r1c2 c7_def
1790 r2c1 r2c2 c7_def
1791 r3c1 r3c2 c7_def
1792 r4c1 r4c2 c7_def
1793 r5c1 r5c2 c7_def
1794 r6c1 r6c2 c7_def
1795 r7c1 r7c2 c7_def
1796 r8c1 r8c2 r8c7
1797 # --------------------------------------------------------------------------
1798 # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3]
1799 # --------------------------------------------------------------------------
1800 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, ALGORITHM=INSTANT;
1801 # Metadata from INFORMATION_SCHEMA.TABLES
1802 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1803 test/t1 6 0 5
1804 # Metadata from INFORMATION_SCHEMA.COLUMNS
1805 NAME POS MTYPE PRTYPE HAS_DEFAULT
1806 c1 0 13 16712190 0
1807 c2 1 13 16711934 0
1808 c7 2 13 16711934 1
1809 SELECT * FROM t1 ORDER BY c1;
1810 c1 c2 c7
1811 r1c1 r1c2 c7_def2
1812 r2c1 r2c2 c7_def2
1813 r3c1 r3c2 c7_def2
1814 r4c1 r4c2 c7_def2
1815 r5c1 r5c2 c7_def2
1816 r6c1 r6c2 c7_def2
1817 r7c1 r7c2 c7_def2
1818 r8c1 r8c2 c7_def2
1819 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7");
1820 SELECT * FROM t1 ORDER BY c1;
1821 c1 c2 c7
1822 r1c1 r1c2 c7_def2
1823 r2c1 r2c2 c7_def2
1824 r3c1 r3c2 c7_def2
1825 r4c1 r4c2 c7_def2
1826 r5c1 r5c2 c7_def2
1827 r6c1 r6c2 c7_def2
1828 r7c1 r7c2 c7_def2
1829 r8c1 r8c2 c7_def2
1830 r9c1 r9c2 r9c7
1831 # ------------------------------------------------------------
1832 # Rebuild table. [c1, c2, c7]
1833 # ------------------------------------------------------------
1834 ALTER TABLE t1 force;
1835 # Metadata from INFORMATION_SCHEMA.TABLES
1836 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1837 test/t1 6 0 0
1838 # Metadata from INFORMATION_SCHEMA.COLUMNS
1839 NAME POS MTYPE PRTYPE HAS_DEFAULT
1840 c1 0 13 16712190 0
1841 c2 1 13 16711934 0
1842 c7 2 13 16711934 0
1843 SELECT * FROM t1 ORDER BY c1;
1844 c1 c2 c7
1845 r1c1 r1c2 c7_def2
1846 r2c1 r2c2 c7_def2
1847 r3c1 r3c2 c7_def2
1848 r4c1 r4c2 c7_def2
1849 r5c1 r5c2 c7_def2
1850 r6c1 r6c2 c7_def2
1851 r7c1 r7c2 c7_def2
1852 r8c1 r8c2 c7_def2
1853 r9c1 r9c2 r9c7
1854 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7");
1855 SELECT * FROM t1 ORDER BY c1;
1856 c1 c2 c7
1857 r10c1 r10c2 r10c7
1858 r1c1 r1c2 c7_def2
1859 r2c1 r2c2 c7_def2
1860 r3c1 r3c2 c7_def2
1861 r4c1 r4c2 c7_def2
1862 r5c1 r5c2 c7_def2
1863 r6c1 r6c2 c7_def2
1864 r7c1 r7c2 c7_def2
1865 r8c1 r8c2 c7_def2
1866 r9c1 r9c2 r9c7
1867 ###########
1868 # CLEANUP #
1869 ###########
1870 DROP TABLE t1;
1871 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
1872 INSERT INTO t2 VALUES ("r1c1", "r1c2");
1873 SELECT * FROM t2;
1874 c1 c2
1875 r1c1 r1c2
1876 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
1877 INSERT INTO t1 VALUES ("r1c1", "r1c2");
1878 SELECT * FROM t1;
1879 c1 c2
1880 r1c1 r1c2
1881 BEGIN;
1882 SELECT * FROM t2;
1883 c1 c2
1884 r1c1 r1c2
1885 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
1886 ALGORITHM=COPY;
1887 UPDATE t1 SET c1 = "r1c11";
1888 SELECT * FROM t1;
1889 c1 c2 c3
1890 r1c11 r1c2 c3_def
1891 SELECT * FROM t1;
1892 ERROR HY000: Table definition has changed, please retry transaction
1893 DROP TABLE t1;
1894 DROP TABLE t2;
1895 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
1896 INSERT INTO t2 VALUES ("r1c1", "r1c2");
1897 SELECT * FROM t2;
1898 c1 c2
1899 r1c1 r1c2
1900 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
1901 INSERT INTO t1 VALUES ("r1c1", "r1c2");
1902 SELECT * FROM t1;
1903 c1 c2
1904 r1c1 r1c2
1905 BEGIN;
1906 SELECT * FROM t2;
1907 c1 c2
1908 r1c1 r1c2
1909 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
1910 ALGORITHM=INSTANT;
1911 UPDATE t1 SET c1 = "r1c11";
1912 SELECT * FROM t1;
1913 c1 c2 c3
1914 r1c11 r1c2 c3_def
1915 SELECT * FROM t1;
1916 ERROR HY000: Table definition has changed, please retry transaction
1917 DROP TABLE t1;
1918 DROP TABLE t2;
1919 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
1920 INSERT INTO t2 VALUES ("r1c1", "r1c2");
1921 SELECT * FROM t2;
1922 c1 c2
1923 r1c1 r1c2
1924 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
1925 INSERT INTO t1 VALUES ("r1c1", "r1c2");
1926 SELECT * FROM t1;
1927 c1 c2
1928 r1c1 r1c2
1929 BEGIN;
1930 SELECT * FROM t2;
1931 c1 c2
1932 r1c1 r1c2
1933 ALTER TABLE t1 DROP COLUMN c2,
1934 ALGORITHM=COPY;
1935 UPDATE t1 SET c1 = "r1c11";
1936 SELECT * FROM t1;
1937 c1
1938 r1c11
1939 SELECT * FROM t1;
1940 ERROR HY000: Table definition has changed, please retry transaction
1941 DROP TABLE t1;
1942 DROP TABLE t2;
1943 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
1944 INSERT INTO t2 VALUES ("r1c1", "r1c2");
1945 SELECT * FROM t2;
1946 c1 c2
1947 r1c1 r1c2
1948 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
1949 INSERT INTO t1 VALUES ("r1c1", "r1c2");
1950 SELECT * FROM t1;
1951 c1 c2
1952 r1c1 r1c2
1953 BEGIN;
1954 SELECT * FROM t2;
1955 c1 c2
1956 r1c1 r1c2
1957 ALTER TABLE t1 DROP COLUMN c2,
1958 ALGORITHM=INSTANT;
1959 UPDATE t1 SET c1 = "r1c11";
1960 SELECT * FROM t1;
1961 c1
1962 r1c11
1963 SELECT * FROM t1;
1964 ERROR HY000: Table definition has changed, please retry transaction
1965 DROP TABLE t1;
1966 DROP TABLE t2;
1967 # ------------------------------------------------------------
1968 # Create a table with 3 columns. [c1, c2, c3]
1969 # ------------------------------------------------------------
1970 CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) , PRIMARY KEY (c1) , KEY (c2)) ROW_FORMAT=DYNAMIC;
1971 # Metadata from INFORMATION_SCHEMA.TABLES
1972 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1973 test/t1 6 0 0
1974 # Metadata from INFORMATION_SCHEMA.COLUMNS
1975 NAME POS MTYPE PRTYPE HAS_DEFAULT
1976 c1 0 13 16712190 0
1977 c2 1 13 16711934 0
1978 c3 2 13 16711934 0
1979 INSERT INTO t1 values ("r1c1", "r1c2", "r1c3");
1980 SELECT * FROM t1 ORDER BY c1;
1981 c1 c2 c3
1982 r1c1 r1c2 r1c3
1983
1984 # -----------------------ADD COLUMN TEST----------------------
1985
1986 # ------------------------------------------------------------
1987 # Add a new column at the end. [c1, c2, c3, +c4]
1988 # ------------------------------------------------------------
1989 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_def", ALGORITHM=INSTANT;
1990 # Metadata from INFORMATION_SCHEMA.TABLES
1991 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1992 test/t1 7 0 1
1993 # Metadata from INFORMATION_SCHEMA.COLUMNS
1994 NAME POS MTYPE PRTYPE HAS_DEFAULT
1995 c1 0 13 16712190 0
1996 c2 1 13 16711934 0
1997 c3 2 13 16711934 0
1998 c4 3 13 16711934 1
1999 INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4");
2000 SELECT * FROM t1 ORDER BY c1;
2001 c1 c2 c3 c4
2002 r1c1 r1c2 r1c3 c4_def
2003 r2c1 r2c2 r2c3 r2c4
2004 # ------------------------------------------------------------
2005 # Add a new column in between. [c1, +c5, c2, c3, c4]
2006 # ------------------------------------------------------------
2007 ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
2008 # Metadata from INFORMATION_SCHEMA.TABLES
2009 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2010 test/t1 8 0 2
2011 # Metadata from INFORMATION_SCHEMA.COLUMNS
2012 NAME POS MTYPE PRTYPE HAS_DEFAULT
2013 c1 0 13 16712190 0
2014 c5 1 13 16711934 1
2015 c2 2 13 16711934 0
2016 c3 3 13 16711934 0
2017 c4 4 13 16711934 1
2018 INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4");
2019 SELECT * FROM t1 ORDER BY c1;
2020 c1 c5 c2 c3 c4
2021 r1c1 NULL r1c2 r1c3 c4_def
2022 r2c1 NULL r2c2 r2c3 r2c4
2023 r3c1 r3c5 r3c2 r3c3 r3c4
2024 # ------------------------------------------------------------
2025 # Add a new column at first. [+c6, c1, c5, c2, c3, c4]
2026 # ------------------------------------------------------------
2027 ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL FIRST, ALGORITHM=INSTANT;
2028 # Metadata from INFORMATION_SCHEMA.TABLES
2029 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2030 test/t1 9 0 3
2031 # Metadata from INFORMATION_SCHEMA.COLUMNS
2032 NAME POS MTYPE PRTYPE HAS_DEFAULT
2033 c6 0 13 16711934 1
2034 c1 1 13 16712190 0
2035 c5 2 13 16711934 1
2036 c2 3 13 16711934 0
2037 c3 4 13 16711934 0
2038 c4 5 13 16711934 1
2039 INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4");
2040 SELECT * FROM t1 ORDER BY c1;
2041 c6 c1 c5 c2 c3 c4
2042 NULL r1c1 NULL r1c2 r1c3 c4_def
2043 NULL r2c1 NULL r2c2 r2c3 r2c4
2044 NULL r3c1 r3c5 r3c2 r3c3 r3c4
2045 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
2046 # ------------------------------------------------------------
2047 # Rebuild table. [c6, c1, c5, c2, c3, c4]
2048 # ------------------------------------------------------------
2049 ALTER TABLE t1 force;
2050 # Metadata from INFORMATION_SCHEMA.TABLES
2051 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2052 test/t1 9 0 0
2053 # Metadata from INFORMATION_SCHEMA.COLUMNS
2054 NAME POS MTYPE PRTYPE HAS_DEFAULT
2055 c6 0 13 16711934 0
2056 c1 1 13 16712190 0
2057 c5 2 13 16711934 0
2058 c2 3 13 16711934 0
2059 c3 4 13 16711934 0
2060 c4 5 13 16711934 0
2061 SELECT * FROM t1 ORDER BY c1;
2062 c6 c1 c5 c2 c3 c4
2063 NULL r1c1 NULL r1c2 r1c3 c4_def
2064 NULL r2c1 NULL r2c2 r2c3 r2c4
2065 NULL r3c1 r3c5 r3c2 r3c3 r3c4
2066 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
2067
2068 # -----------------------DROP COLUMN TEST---------------------
2069
2070 # ------------------------------------------------------------
2071 # Drop a column from the end. [c6, c1, c5, c2, c3, -c4]
2072 # ------------------------------------------------------------
2073 ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT;
2074 # Metadata from INFORMATION_SCHEMA.TABLES
2075 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2076 test/t1 8 0 1
2077 # Metadata from INFORMATION_SCHEMA.COLUMNS
2078 NAME POS MTYPE PRTYPE HAS_DEFAULT
2079 c6 0 13 16711934 0
2080 c1 1 13 16712190 0
2081 c5 2 13 16711934 0
2082 c2 3 13 16711934 0
2083 c3 4 13 16711934 0
2084 SELECT * FROM t1 ORDER BY c1;
2085 c6 c1 c5 c2 c3
2086 NULL r1c1 NULL r1c2 r1c3
2087 NULL r2c1 NULL r2c2 r2c3
2088 NULL r3c1 r3c5 r3c2 r3c3
2089 r4c6 r4c1 r4c5 r4c2 r4c3
2090 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3");
2091 SELECT * FROM t1 ORDER BY c1;
2092 c6 c1 c5 c2 c3
2093 NULL r1c1 NULL r1c2 r1c3
2094 NULL r2c1 NULL r2c2 r2c3
2095 NULL r3c1 r3c5 r3c2 r3c3
2096 r4c6 r4c1 r4c5 r4c2 r4c3
2097 r5c6 r5c1 r5c5 r5c2 r5c3
2098 # ------------------------------------------------------------
2099 # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4]
2100 # ------------------------------------------------------------
2101 ALTER TABLE t1 DROP COLUMN c5, ALGORITHM=INSTANT;
2102 # Metadata from INFORMATION_SCHEMA.TABLES
2103 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2104 test/t1 7 0 2
2105 # Metadata from INFORMATION_SCHEMA.COLUMNS
2106 NAME POS MTYPE PRTYPE HAS_DEFAULT
2107 c6 0 13 16711934 0
2108 c1 1 13 16712190 0
2109 c2 2 13 16711934 0
2110 c3 3 13 16711934 0
2111 SELECT * FROM t1 ORDER BY c1;
2112 c6 c1 c2 c3
2113 NULL r1c1 r1c2 r1c3
2114 NULL r2c1 r2c2 r2c3
2115 NULL r3c1 r3c2 r3c3
2116 r4c6 r4c1 r4c2 r4c3
2117 r5c6 r5c1 r5c2 r5c3
2118 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3");
2119 SELECT * FROM t1 ORDER BY c1;
2120 c6 c1 c2 c3
2121 NULL r1c1 r1c2 r1c3
2122 NULL r2c1 r2c2 r2c3
2123 NULL r3c1 r3c2 r3c3
2124 r4c6 r4c1 r4c2 r4c3
2125 r5c6 r5c1 r5c2 r5c3
2126 r6c6 r6c1 r6c2 r6c3
2127 # ------------------------------------------------------------
2128 # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5]
2129 # ------------------------------------------------------------
2130 ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT;
2131 # Metadata from INFORMATION_SCHEMA.TABLES
2132 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2133 test/t1 6 0 3
2134 # Metadata from INFORMATION_SCHEMA.COLUMNS
2135 NAME POS MTYPE PRTYPE HAS_DEFAULT
2136 c1 0 13 16712190 0
2137 c2 1 13 16711934 0
2138 c3 2 13 16711934 0
2139 SELECT * FROM t1 ORDER BY c1;
2140 c1 c2 c3
2141 r1c1 r1c2 r1c3
2142 r2c1 r2c2 r2c3
2143 r3c1 r3c2 r3c3
2144 r4c1 r4c2 r4c3
2145 r5c1 r5c2 r5c3
2146 r6c1 r6c2 r6c3
2147 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3");
2148 SELECT * FROM t1 ORDER BY c1;
2149 c1 c2 c3
2150 r1c1 r1c2 r1c3
2151 r2c1 r2c2 r2c3
2152 r3c1 r3c2 r3c3
2153 r4c1 r4c2 r4c3
2154 r5c1 r5c2 r5c3
2155 r6c1 r6c2 r6c3
2156 r7c1 r7c2 r7c3
2157 # ------------------------------------------------------------
2158 # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6]
2159 # ------------------------------------------------------------
2160 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", DROP COLUMN c3, ALGORITHM=INSTANT;
2161 # Metadata from INFORMATION_SCHEMA.TABLES
2162 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2163 test/t1 6 0 4
2164 # Metadata from INFORMATION_SCHEMA.COLUMNS
2165 NAME POS MTYPE PRTYPE HAS_DEFAULT
2166 c1 0 13 16712190 0
2167 c2 1 13 16711934 0
2168 c7 2 13 16711934 1
2169 SELECT * FROM t1 ORDER BY c1;
2170 c1 c2 c7
2171 r1c1 r1c2 c7_def
2172 r2c1 r2c2 c7_def
2173 r3c1 r3c2 c7_def
2174 r4c1 r4c2 c7_def
2175 r5c1 r5c2 c7_def
2176 r6c1 r6c2 c7_def
2177 r7c1 r7c2 c7_def
2178 INSERT INTO t1 values ("r8c1", "r8c2", "r8c7");
2179 SELECT * FROM t1 ORDER BY c1;
2180 c1 c2 c7
2181 r1c1 r1c2 c7_def
2182 r2c1 r2c2 c7_def
2183 r3c1 r3c2 c7_def
2184 r4c1 r4c2 c7_def
2185 r5c1 r5c2 c7_def
2186 r6c1 r6c2 c7_def
2187 r7c1 r7c2 c7_def
2188 r8c1 r8c2 r8c7
2189 # --------------------------------------------------------------------------
2190 # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3]
2191 # --------------------------------------------------------------------------
2192 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, ALGORITHM=INSTANT;
2193 # Metadata from INFORMATION_SCHEMA.TABLES
2194 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2195 test/t1 6 0 5
2196 # Metadata from INFORMATION_SCHEMA.COLUMNS
2197 NAME POS MTYPE PRTYPE HAS_DEFAULT
2198 c1 0 13 16712190 0
2199 c2 1 13 16711934 0
2200 c7 2 13 16711934 1
2201 SELECT * FROM t1 ORDER BY c1;
2202 c1 c2 c7
2203 r1c1 r1c2 c7_def2
2204 r2c1 r2c2 c7_def2
2205 r3c1 r3c2 c7_def2
2206 r4c1 r4c2 c7_def2
2207 r5c1 r5c2 c7_def2
2208 r6c1 r6c2 c7_def2
2209 r7c1 r7c2 c7_def2
2210 r8c1 r8c2 c7_def2
2211 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7");
2212 SELECT * FROM t1 ORDER BY c1;
2213 c1 c2 c7
2214 r1c1 r1c2 c7_def2
2215 r2c1 r2c2 c7_def2
2216 r3c1 r3c2 c7_def2
2217 r4c1 r4c2 c7_def2
2218 r5c1 r5c2 c7_def2
2219 r6c1 r6c2 c7_def2
2220 r7c1 r7c2 c7_def2
2221 r8c1 r8c2 c7_def2
2222 r9c1 r9c2 r9c7
2223 # ------------------------------------------------------------
2224 # Rebuild table. [c1, c2, c7]
2225 # ------------------------------------------------------------
2226 ALTER TABLE t1 force;
2227 # Metadata from INFORMATION_SCHEMA.TABLES
2228 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2229 test/t1 6 0 0
2230 # Metadata from INFORMATION_SCHEMA.COLUMNS
2231 NAME POS MTYPE PRTYPE HAS_DEFAULT
2232 c1 0 13 16712190 0
2233 c2 1 13 16711934 0
2234 c7 2 13 16711934 0
2235 SELECT * FROM t1 ORDER BY c1;
2236 c1 c2 c7
2237 r1c1 r1c2 c7_def2
2238 r2c1 r2c2 c7_def2
2239 r3c1 r3c2 c7_def2
2240 r4c1 r4c2 c7_def2
2241 r5c1 r5c2 c7_def2
2242 r6c1 r6c2 c7_def2
2243 r7c1 r7c2 c7_def2
2244 r8c1 r8c2 c7_def2
2245 r9c1 r9c2 r9c7
2246 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7");
2247 SELECT * FROM t1 ORDER BY c1;
2248 c1 c2 c7
2249 r10c1 r10c2 r10c7
2250 r1c1 r1c2 c7_def2
2251 r2c1 r2c2 c7_def2
2252 r3c1 r3c2 c7_def2
2253 r4c1 r4c2 c7_def2
2254 r5c1 r5c2 c7_def2
2255 r6c1 r6c2 c7_def2
2256 r7c1 r7c2 c7_def2
2257 r8c1 r8c2 c7_def2
2258 r9c1 r9c2 r9c7
2259 ###########
2260 # CLEANUP #
2261 ###########
2262 DROP TABLE t1;
2263 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
2264 INSERT INTO t2 VALUES ("r1c1", "r1c2");
2265 SELECT * FROM t2;
2266 c1 c2
2267 r1c1 r1c2
2268 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
2269 INSERT INTO t1 VALUES ("r1c1", "r1c2");
2270 SELECT * FROM t1;
2271 c1 c2
2272 r1c1 r1c2
2273 BEGIN;
2274 SELECT * FROM t2;
2275 c1 c2
2276 r1c1 r1c2
2277 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
2278 ALGORITHM=COPY;
2279 UPDATE t1 SET c1 = "r1c11";
2280 SELECT * FROM t1;
2281 c1 c2 c3
2282 r1c11 r1c2 c3_def
2283 SELECT * FROM t1;
2284 ERROR HY000: Table definition has changed, please retry transaction
2285 DROP TABLE t1;
2286 DROP TABLE t2;
2287 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
2288 INSERT INTO t2 VALUES ("r1c1", "r1c2");
2289 SELECT * FROM t2;
2290 c1 c2
2291 r1c1 r1c2
2292 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
2293 INSERT INTO t1 VALUES ("r1c1", "r1c2");
2294 SELECT * FROM t1;
2295 c1 c2
2296 r1c1 r1c2
2297 BEGIN;
2298 SELECT * FROM t2;
2299 c1 c2
2300 r1c1 r1c2
2301 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
2302 ALGORITHM=INSTANT;
2303 UPDATE t1 SET c1 = "r1c11";
2304 SELECT * FROM t1;
2305 c1 c2 c3
2306 r1c11 r1c2 c3_def
2307 SELECT * FROM t1;
2308 ERROR HY000: Table definition has changed, please retry transaction
2309 DROP TABLE t1;
2310 DROP TABLE t2;
2311 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
2312 INSERT INTO t2 VALUES ("r1c1", "r1c2");
2313 SELECT * FROM t2;
2314 c1 c2
2315 r1c1 r1c2
2316 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
2317 INSERT INTO t1 VALUES ("r1c1", "r1c2");
2318 SELECT * FROM t1;
2319 c1 c2
2320 r1c1 r1c2
2321 BEGIN;
2322 SELECT * FROM t2;
2323 c1 c2
2324 r1c1 r1c2
2325 ALTER TABLE t1 DROP COLUMN c2,
2326 ALGORITHM=COPY;
2327 UPDATE t1 SET c1 = "r1c11";
2328 SELECT * FROM t1;
2329 c1
2330 r1c11
2331 SELECT * FROM t1;
2332 ERROR HY000: Table definition has changed, please retry transaction
2333 DROP TABLE t1;
2334 DROP TABLE t2;
2335 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
2336 INSERT INTO t2 VALUES ("r1c1", "r1c2");
2337 SELECT * FROM t2;
2338 c1 c2
2339 r1c1 r1c2
2340 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=DYNAMIC;
2341 INSERT INTO t1 VALUES ("r1c1", "r1c2");
2342 SELECT * FROM t1;
2343 c1 c2
2344 r1c1 r1c2
2345 BEGIN;
2346 SELECT * FROM t2;
2347 c1 c2
2348 r1c1 r1c2
2349 ALTER TABLE t1 DROP COLUMN c2,
2350 ALGORITHM=INSTANT;
2351 UPDATE t1 SET c1 = "r1c11";
2352 SELECT * FROM t1;
2353 c1
2354 r1c11
2355 SELECT * FROM t1;
2356 ERROR HY000: Table definition has changed, please retry transaction
2357 DROP TABLE t1;
2358 DROP TABLE t2;
2359 ############################################
2360 # Test instant ADD/DROP COLUMN for COMPACT format
2361 ############################################
2362 # ------------------------------------------------------------
2363 # Create a table with 3 columns. [c1, c2, c3]
2364 # ------------------------------------------------------------
2365 CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) ) ROW_FORMAT=COMPACT;
2366 # Metadata from INFORMATION_SCHEMA.TABLES
2367 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2368 test/t1 6 0 0
2369 # Metadata from INFORMATION_SCHEMA.COLUMNS
2370 NAME POS MTYPE PRTYPE HAS_DEFAULT
2371 c1 0 13 16711934 0
2372 c2 1 13 16711934 0
2373 c3 2 13 16711934 0
2374 INSERT INTO t1 values ("r1c1", "r1c2", "r1c3");
2375 SELECT * FROM t1 ORDER BY c1;
2376 c1 c2 c3
2377 r1c1 r1c2 r1c3
2378
2379 # -----------------------ADD COLUMN TEST----------------------
2380
2381 # ------------------------------------------------------------
2382 # Add a new column at the end. [c1, c2, c3, +c4]
2383 # ------------------------------------------------------------
2384 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_def", ALGORITHM=INSTANT;
2385 # Metadata from INFORMATION_SCHEMA.TABLES
2386 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2387 test/t1 7 0 1
2388 # Metadata from INFORMATION_SCHEMA.COLUMNS
2389 NAME POS MTYPE PRTYPE HAS_DEFAULT
2390 c1 0 13 16711934 0
2391 c2 1 13 16711934 0
2392 c3 2 13 16711934 0
2393 c4 3 13 16711934 1
2394 INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4");
2395 SELECT * FROM t1 ORDER BY c1;
2396 c1 c2 c3 c4
2397 r1c1 r1c2 r1c3 c4_def
2398 r2c1 r2c2 r2c3 r2c4
2399 # ------------------------------------------------------------
2400 # Add a new column in between. [c1, +c5, c2, c3, c4]
2401 # ------------------------------------------------------------
2402 ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
2403 # Metadata from INFORMATION_SCHEMA.TABLES
2404 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2405 test/t1 8 0 2
2406 # Metadata from INFORMATION_SCHEMA.COLUMNS
2407 NAME POS MTYPE PRTYPE HAS_DEFAULT
2408 c1 0 13 16711934 0
2409 c5 1 13 16711934 1
2410 c2 2 13 16711934 0
2411 c3 3 13 16711934 0
2412 c4 4 13 16711934 1
2413 INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4");
2414 SELECT * FROM t1 ORDER BY c1;
2415 c1 c5 c2 c3 c4
2416 r1c1 NULL r1c2 r1c3 c4_def
2417 r2c1 NULL r2c2 r2c3 r2c4
2418 r3c1 r3c5 r3c2 r3c3 r3c4
2419 # ------------------------------------------------------------
2420 # Add a new column at first. [+c6, c1, c5, c2, c3, c4]
2421 # ------------------------------------------------------------
2422 ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL FIRST, ALGORITHM=INSTANT;
2423 # Metadata from INFORMATION_SCHEMA.TABLES
2424 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2425 test/t1 9 0 3
2426 # Metadata from INFORMATION_SCHEMA.COLUMNS
2427 NAME POS MTYPE PRTYPE HAS_DEFAULT
2428 c6 0 13 16711934 1
2429 c1 1 13 16711934 0
2430 c5 2 13 16711934 1
2431 c2 3 13 16711934 0
2432 c3 4 13 16711934 0
2433 c4 5 13 16711934 1
2434 INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4");
2435 SELECT * FROM t1 ORDER BY c1;
2436 c6 c1 c5 c2 c3 c4
2437 NULL r1c1 NULL r1c2 r1c3 c4_def
2438 NULL r2c1 NULL r2c2 r2c3 r2c4
2439 NULL r3c1 r3c5 r3c2 r3c3 r3c4
2440 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
2441 # ------------------------------------------------------------
2442 # Rebuild table. [c6, c1, c5, c2, c3, c4]
2443 # ------------------------------------------------------------
2444 ALTER TABLE t1 force;
2445 # Metadata from INFORMATION_SCHEMA.TABLES
2446 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2447 test/t1 9 0 0
2448 # Metadata from INFORMATION_SCHEMA.COLUMNS
2449 NAME POS MTYPE PRTYPE HAS_DEFAULT
2450 c6 0 13 16711934 0
2451 c1 1 13 16711934 0
2452 c5 2 13 16711934 0
2453 c2 3 13 16711934 0
2454 c3 4 13 16711934 0
2455 c4 5 13 16711934 0
2456 SELECT * FROM t1 ORDER BY c1;
2457 c6 c1 c5 c2 c3 c4
2458 NULL r1c1 NULL r1c2 r1c3 c4_def
2459 NULL r2c1 NULL r2c2 r2c3 r2c4
2460 NULL r3c1 r3c5 r3c2 r3c3 r3c4
2461 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
2462
2463 # -----------------------DROP COLUMN TEST---------------------
2464
2465 # ------------------------------------------------------------
2466 # Drop a column from the end. [c6, c1, c5, c2, c3, -c4]
2467 # ------------------------------------------------------------
2468 ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT;
2469 # Metadata from INFORMATION_SCHEMA.TABLES
2470 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2471 test/t1 8 0 1
2472 # Metadata from INFORMATION_SCHEMA.COLUMNS
2473 NAME POS MTYPE PRTYPE HAS_DEFAULT
2474 c6 0 13 16711934 0
2475 c1 1 13 16711934 0
2476 c5 2 13 16711934 0
2477 c2 3 13 16711934 0
2478 c3 4 13 16711934 0
2479 SELECT * FROM t1 ORDER BY c1;
2480 c6 c1 c5 c2 c3
2481 NULL r1c1 NULL r1c2 r1c3
2482 NULL r2c1 NULL r2c2 r2c3
2483 NULL r3c1 r3c5 r3c2 r3c3
2484 r4c6 r4c1 r4c5 r4c2 r4c3
2485 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3");
2486 SELECT * FROM t1 ORDER BY c1;
2487 c6 c1 c5 c2 c3
2488 NULL r1c1 NULL r1c2 r1c3
2489 NULL r2c1 NULL r2c2 r2c3
2490 NULL r3c1 r3c5 r3c2 r3c3
2491 r4c6 r4c1 r4c5 r4c2 r4c3
2492 r5c6 r5c1 r5c5 r5c2 r5c3
2493 # ------------------------------------------------------------
2494 # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4]
2495 # ------------------------------------------------------------
2496 ALTER TABLE t1 DROP COLUMN c5, ALGORITHM=INSTANT;
2497 # Metadata from INFORMATION_SCHEMA.TABLES
2498 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2499 test/t1 7 0 2
2500 # Metadata from INFORMATION_SCHEMA.COLUMNS
2501 NAME POS MTYPE PRTYPE HAS_DEFAULT
2502 c6 0 13 16711934 0
2503 c1 1 13 16711934 0
2504 c2 2 13 16711934 0
2505 c3 3 13 16711934 0
2506 SELECT * FROM t1 ORDER BY c1;
2507 c6 c1 c2 c3
2508 NULL r1c1 r1c2 r1c3
2509 NULL r2c1 r2c2 r2c3
2510 NULL r3c1 r3c2 r3c3
2511 r4c6 r4c1 r4c2 r4c3
2512 r5c6 r5c1 r5c2 r5c3
2513 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3");
2514 SELECT * FROM t1 ORDER BY c1;
2515 c6 c1 c2 c3
2516 NULL r1c1 r1c2 r1c3
2517 NULL r2c1 r2c2 r2c3
2518 NULL r3c1 r3c2 r3c3
2519 r4c6 r4c1 r4c2 r4c3
2520 r5c6 r5c1 r5c2 r5c3
2521 r6c6 r6c1 r6c2 r6c3
2522 # ------------------------------------------------------------
2523 # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5]
2524 # ------------------------------------------------------------
2525 ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT;
2526 # Metadata from INFORMATION_SCHEMA.TABLES
2527 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2528 test/t1 6 0 3
2529 # Metadata from INFORMATION_SCHEMA.COLUMNS
2530 NAME POS MTYPE PRTYPE HAS_DEFAULT
2531 c1 0 13 16711934 0
2532 c2 1 13 16711934 0
2533 c3 2 13 16711934 0
2534 SELECT * FROM t1 ORDER BY c1;
2535 c1 c2 c3
2536 r1c1 r1c2 r1c3
2537 r2c1 r2c2 r2c3
2538 r3c1 r3c2 r3c3
2539 r4c1 r4c2 r4c3
2540 r5c1 r5c2 r5c3
2541 r6c1 r6c2 r6c3
2542 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3");
2543 SELECT * FROM t1 ORDER BY c1;
2544 c1 c2 c3
2545 r1c1 r1c2 r1c3
2546 r2c1 r2c2 r2c3
2547 r3c1 r3c2 r3c3
2548 r4c1 r4c2 r4c3
2549 r5c1 r5c2 r5c3
2550 r6c1 r6c2 r6c3
2551 r7c1 r7c2 r7c3
2552 # ------------------------------------------------------------
2553 # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6]
2554 # ------------------------------------------------------------
2555 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", DROP COLUMN c3, ALGORITHM=INSTANT;
2556 # Metadata from INFORMATION_SCHEMA.TABLES
2557 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2558 test/t1 6 0 4
2559 # Metadata from INFORMATION_SCHEMA.COLUMNS
2560 NAME POS MTYPE PRTYPE HAS_DEFAULT
2561 c1 0 13 16711934 0
2562 c2 1 13 16711934 0
2563 c7 2 13 16711934 1
2564 SELECT * FROM t1 ORDER BY c1;
2565 c1 c2 c7
2566 r1c1 r1c2 c7_def
2567 r2c1 r2c2 c7_def
2568 r3c1 r3c2 c7_def
2569 r4c1 r4c2 c7_def
2570 r5c1 r5c2 c7_def
2571 r6c1 r6c2 c7_def
2572 r7c1 r7c2 c7_def
2573 INSERT INTO t1 values ("r8c1", "r8c2", "r8c7");
2574 SELECT * FROM t1 ORDER BY c1;
2575 c1 c2 c7
2576 r1c1 r1c2 c7_def
2577 r2c1 r2c2 c7_def
2578 r3c1 r3c2 c7_def
2579 r4c1 r4c2 c7_def
2580 r5c1 r5c2 c7_def
2581 r6c1 r6c2 c7_def
2582 r7c1 r7c2 c7_def
2583 r8c1 r8c2 r8c7
2584 # --------------------------------------------------------------------------
2585 # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3]
2586 # --------------------------------------------------------------------------
2587 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, ALGORITHM=INSTANT;
2588 # Metadata from INFORMATION_SCHEMA.TABLES
2589 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2590 test/t1 6 0 5
2591 # Metadata from INFORMATION_SCHEMA.COLUMNS
2592 NAME POS MTYPE PRTYPE HAS_DEFAULT
2593 c1 0 13 16711934 0
2594 c2 1 13 16711934 0
2595 c7 2 13 16711934 1
2596 SELECT * FROM t1 ORDER BY c1;
2597 c1 c2 c7
2598 r1c1 r1c2 c7_def2
2599 r2c1 r2c2 c7_def2
2600 r3c1 r3c2 c7_def2
2601 r4c1 r4c2 c7_def2
2602 r5c1 r5c2 c7_def2
2603 r6c1 r6c2 c7_def2
2604 r7c1 r7c2 c7_def2
2605 r8c1 r8c2 c7_def2
2606 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7");
2607 SELECT * FROM t1 ORDER BY c1;
2608 c1 c2 c7
2609 r1c1 r1c2 c7_def2
2610 r2c1 r2c2 c7_def2
2611 r3c1 r3c2 c7_def2
2612 r4c1 r4c2 c7_def2
2613 r5c1 r5c2 c7_def2
2614 r6c1 r6c2 c7_def2
2615 r7c1 r7c2 c7_def2
2616 r8c1 r8c2 c7_def2
2617 r9c1 r9c2 r9c7
2618 # ------------------------------------------------------------
2619 # Rebuild table. [c1, c2, c7]
2620 # ------------------------------------------------------------
2621 ALTER TABLE t1 force;
2622 # Metadata from INFORMATION_SCHEMA.TABLES
2623 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2624 test/t1 6 0 0
2625 # Metadata from INFORMATION_SCHEMA.COLUMNS
2626 NAME POS MTYPE PRTYPE HAS_DEFAULT
2627 c1 0 13 16711934 0
2628 c2 1 13 16711934 0
2629 c7 2 13 16711934 0
2630 SELECT * FROM t1 ORDER BY c1;
2631 c1 c2 c7
2632 r1c1 r1c2 c7_def2
2633 r2c1 r2c2 c7_def2
2634 r3c1 r3c2 c7_def2
2635 r4c1 r4c2 c7_def2
2636 r5c1 r5c2 c7_def2
2637 r6c1 r6c2 c7_def2
2638 r7c1 r7c2 c7_def2
2639 r8c1 r8c2 c7_def2
2640 r9c1 r9c2 r9c7
2641 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7");
2642 SELECT * FROM t1 ORDER BY c1;
2643 c1 c2 c7
2644 r10c1 r10c2 r10c7
2645 r1c1 r1c2 c7_def2
2646 r2c1 r2c2 c7_def2
2647 r3c1 r3c2 c7_def2
2648 r4c1 r4c2 c7_def2
2649 r5c1 r5c2 c7_def2
2650 r6c1 r6c2 c7_def2
2651 r7c1 r7c2 c7_def2
2652 r8c1 r8c2 c7_def2
2653 r9c1 r9c2 r9c7
2654 ###########
2655 # CLEANUP #
2656 ###########
2657 DROP TABLE t1;
2658 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
2659 INSERT INTO t2 VALUES ("r1c1", "r1c2");
2660 SELECT * FROM t2;
2661 c1 c2
2662 r1c1 r1c2
2663 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
2664 INSERT INTO t1 VALUES ("r1c1", "r1c2");
2665 SELECT * FROM t1;
2666 c1 c2
2667 r1c1 r1c2
2668 BEGIN;
2669 SELECT * FROM t2;
2670 c1 c2
2671 r1c1 r1c2
2672 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
2673 ALGORITHM=COPY;
2674 UPDATE t1 SET c1 = "r1c11";
2675 SELECT * FROM t1;
2676 c1 c2 c3
2677 r1c11 r1c2 c3_def
2678 SELECT * FROM t1;
2679 ERROR HY000: Table definition has changed, please retry transaction
2680 DROP TABLE t1;
2681 DROP TABLE t2;
2682 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
2683 INSERT INTO t2 VALUES ("r1c1", "r1c2");
2684 SELECT * FROM t2;
2685 c1 c2
2686 r1c1 r1c2
2687 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
2688 INSERT INTO t1 VALUES ("r1c1", "r1c2");
2689 SELECT * FROM t1;
2690 c1 c2
2691 r1c1 r1c2
2692 BEGIN;
2693 SELECT * FROM t2;
2694 c1 c2
2695 r1c1 r1c2
2696 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
2697 ALGORITHM=INSTANT;
2698 UPDATE t1 SET c1 = "r1c11";
2699 SELECT * FROM t1;
2700 c1 c2 c3
2701 r1c11 r1c2 c3_def
2702 SELECT * FROM t1;
2703 ERROR HY000: Table definition has changed, please retry transaction
2704 DROP TABLE t1;
2705 DROP TABLE t2;
2706 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
2707 INSERT INTO t2 VALUES ("r1c1", "r1c2");
2708 SELECT * FROM t2;
2709 c1 c2
2710 r1c1 r1c2
2711 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
2712 INSERT INTO t1 VALUES ("r1c1", "r1c2");
2713 SELECT * FROM t1;
2714 c1 c2
2715 r1c1 r1c2
2716 BEGIN;
2717 SELECT * FROM t2;
2718 c1 c2
2719 r1c1 r1c2
2720 ALTER TABLE t1 DROP COLUMN c2,
2721 ALGORITHM=COPY;
2722 UPDATE t1 SET c1 = "r1c11";
2723 SELECT * FROM t1;
2724 c1
2725 r1c11
2726 SELECT * FROM t1;
2727 ERROR HY000: Table definition has changed, please retry transaction
2728 DROP TABLE t1;
2729 DROP TABLE t2;
2730 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
2731 INSERT INTO t2 VALUES ("r1c1", "r1c2");
2732 SELECT * FROM t2;
2733 c1 c2
2734 r1c1 r1c2
2735 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
2736 INSERT INTO t1 VALUES ("r1c1", "r1c2");
2737 SELECT * FROM t1;
2738 c1 c2
2739 r1c1 r1c2
2740 BEGIN;
2741 SELECT * FROM t2;
2742 c1 c2
2743 r1c1 r1c2
2744 ALTER TABLE t1 DROP COLUMN c2,
2745 ALGORITHM=INSTANT;
2746 UPDATE t1 SET c1 = "r1c11";
2747 SELECT * FROM t1;
2748 c1
2749 r1c11
2750 SELECT * FROM t1;
2751 ERROR HY000: Table definition has changed, please retry transaction
2752 DROP TABLE t1;
2753 DROP TABLE t2;
2754 # ------------------------------------------------------------
2755 # Create a table with 3 columns. [c1, c2, c3]
2756 # ------------------------------------------------------------
2757 CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) , PRIMARY KEY (c1) ) ROW_FORMAT=COMPACT;
2758 # Metadata from INFORMATION_SCHEMA.TABLES
2759 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2760 test/t1 6 0 0
2761 # Metadata from INFORMATION_SCHEMA.COLUMNS
2762 NAME POS MTYPE PRTYPE HAS_DEFAULT
2763 c1 0 13 16712190 0
2764 c2 1 13 16711934 0
2765 c3 2 13 16711934 0
2766 INSERT INTO t1 values ("r1c1", "r1c2", "r1c3");
2767 SELECT * FROM t1 ORDER BY c1;
2768 c1 c2 c3
2769 r1c1 r1c2 r1c3
2770
2771 # -----------------------ADD COLUMN TEST----------------------
2772
2773 # ------------------------------------------------------------
2774 # Add a new column at the end. [c1, c2, c3, +c4]
2775 # ------------------------------------------------------------
2776 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_def", ALGORITHM=INSTANT;
2777 # Metadata from INFORMATION_SCHEMA.TABLES
2778 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2779 test/t1 7 0 1
2780 # Metadata from INFORMATION_SCHEMA.COLUMNS
2781 NAME POS MTYPE PRTYPE HAS_DEFAULT
2782 c1 0 13 16712190 0
2783 c2 1 13 16711934 0
2784 c3 2 13 16711934 0
2785 c4 3 13 16711934 1
2786 INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4");
2787 SELECT * FROM t1 ORDER BY c1;
2788 c1 c2 c3 c4
2789 r1c1 r1c2 r1c3 c4_def
2790 r2c1 r2c2 r2c3 r2c4
2791 # ------------------------------------------------------------
2792 # Add a new column in between. [c1, +c5, c2, c3, c4]
2793 # ------------------------------------------------------------
2794 ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
2795 # Metadata from INFORMATION_SCHEMA.TABLES
2796 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2797 test/t1 8 0 2
2798 # Metadata from INFORMATION_SCHEMA.COLUMNS
2799 NAME POS MTYPE PRTYPE HAS_DEFAULT
2800 c1 0 13 16712190 0
2801 c5 1 13 16711934 1
2802 c2 2 13 16711934 0
2803 c3 3 13 16711934 0
2804 c4 4 13 16711934 1
2805 INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4");
2806 SELECT * FROM t1 ORDER BY c1;
2807 c1 c5 c2 c3 c4
2808 r1c1 NULL r1c2 r1c3 c4_def
2809 r2c1 NULL r2c2 r2c3 r2c4
2810 r3c1 r3c5 r3c2 r3c3 r3c4
2811 # ------------------------------------------------------------
2812 # Add a new column at first. [+c6, c1, c5, c2, c3, c4]
2813 # ------------------------------------------------------------
2814 ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL FIRST, ALGORITHM=INSTANT;
2815 # Metadata from INFORMATION_SCHEMA.TABLES
2816 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2817 test/t1 9 0 3
2818 # Metadata from INFORMATION_SCHEMA.COLUMNS
2819 NAME POS MTYPE PRTYPE HAS_DEFAULT
2820 c6 0 13 16711934 1
2821 c1 1 13 16712190 0
2822 c5 2 13 16711934 1
2823 c2 3 13 16711934 0
2824 c3 4 13 16711934 0
2825 c4 5 13 16711934 1
2826 INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4");
2827 SELECT * FROM t1 ORDER BY c1;
2828 c6 c1 c5 c2 c3 c4
2829 NULL r1c1 NULL r1c2 r1c3 c4_def
2830 NULL r2c1 NULL r2c2 r2c3 r2c4
2831 NULL r3c1 r3c5 r3c2 r3c3 r3c4
2832 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
2833 # ------------------------------------------------------------
2834 # Rebuild table. [c6, c1, c5, c2, c3, c4]
2835 # ------------------------------------------------------------
2836 ALTER TABLE t1 force;
2837 # Metadata from INFORMATION_SCHEMA.TABLES
2838 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2839 test/t1 9 0 0
2840 # Metadata from INFORMATION_SCHEMA.COLUMNS
2841 NAME POS MTYPE PRTYPE HAS_DEFAULT
2842 c6 0 13 16711934 0
2843 c1 1 13 16712190 0
2844 c5 2 13 16711934 0
2845 c2 3 13 16711934 0
2846 c3 4 13 16711934 0
2847 c4 5 13 16711934 0
2848 SELECT * FROM t1 ORDER BY c1;
2849 c6 c1 c5 c2 c3 c4
2850 NULL r1c1 NULL r1c2 r1c3 c4_def
2851 NULL r2c1 NULL r2c2 r2c3 r2c4
2852 NULL r3c1 r3c5 r3c2 r3c3 r3c4
2853 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
2854
2855 # -----------------------DROP COLUMN TEST---------------------
2856
2857 # ------------------------------------------------------------
2858 # Drop a column from the end. [c6, c1, c5, c2, c3, -c4]
2859 # ------------------------------------------------------------
2860 ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT;
2861 # Metadata from INFORMATION_SCHEMA.TABLES
2862 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2863 test/t1 8 0 1
2864 # Metadata from INFORMATION_SCHEMA.COLUMNS
2865 NAME POS MTYPE PRTYPE HAS_DEFAULT
2866 c6 0 13 16711934 0
2867 c1 1 13 16712190 0
2868 c5 2 13 16711934 0
2869 c2 3 13 16711934 0
2870 c3 4 13 16711934 0
2871 SELECT * FROM t1 ORDER BY c1;
2872 c6 c1 c5 c2 c3
2873 NULL r1c1 NULL r1c2 r1c3
2874 NULL r2c1 NULL r2c2 r2c3
2875 NULL r3c1 r3c5 r3c2 r3c3
2876 r4c6 r4c1 r4c5 r4c2 r4c3
2877 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3");
2878 SELECT * FROM t1 ORDER BY c1;
2879 c6 c1 c5 c2 c3
2880 NULL r1c1 NULL r1c2 r1c3
2881 NULL r2c1 NULL r2c2 r2c3
2882 NULL r3c1 r3c5 r3c2 r3c3
2883 r4c6 r4c1 r4c5 r4c2 r4c3
2884 r5c6 r5c1 r5c5 r5c2 r5c3
2885 # ------------------------------------------------------------
2886 # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4]
2887 # ------------------------------------------------------------
2888 ALTER TABLE t1 DROP COLUMN c5, ALGORITHM=INSTANT;
2889 # Metadata from INFORMATION_SCHEMA.TABLES
2890 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2891 test/t1 7 0 2
2892 # Metadata from INFORMATION_SCHEMA.COLUMNS
2893 NAME POS MTYPE PRTYPE HAS_DEFAULT
2894 c6 0 13 16711934 0
2895 c1 1 13 16712190 0
2896 c2 2 13 16711934 0
2897 c3 3 13 16711934 0
2898 SELECT * FROM t1 ORDER BY c1;
2899 c6 c1 c2 c3
2900 NULL r1c1 r1c2 r1c3
2901 NULL r2c1 r2c2 r2c3
2902 NULL r3c1 r3c2 r3c3
2903 r4c6 r4c1 r4c2 r4c3
2904 r5c6 r5c1 r5c2 r5c3
2905 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3");
2906 SELECT * FROM t1 ORDER BY c1;
2907 c6 c1 c2 c3
2908 NULL r1c1 r1c2 r1c3
2909 NULL r2c1 r2c2 r2c3
2910 NULL r3c1 r3c2 r3c3
2911 r4c6 r4c1 r4c2 r4c3
2912 r5c6 r5c1 r5c2 r5c3
2913 r6c6 r6c1 r6c2 r6c3
2914 # ------------------------------------------------------------
2915 # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5]
2916 # ------------------------------------------------------------
2917 ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT;
2918 # Metadata from INFORMATION_SCHEMA.TABLES
2919 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2920 test/t1 6 0 3
2921 # Metadata from INFORMATION_SCHEMA.COLUMNS
2922 NAME POS MTYPE PRTYPE HAS_DEFAULT
2923 c1 0 13 16712190 0
2924 c2 1 13 16711934 0
2925 c3 2 13 16711934 0
2926 SELECT * FROM t1 ORDER BY c1;
2927 c1 c2 c3
2928 r1c1 r1c2 r1c3
2929 r2c1 r2c2 r2c3
2930 r3c1 r3c2 r3c3
2931 r4c1 r4c2 r4c3
2932 r5c1 r5c2 r5c3
2933 r6c1 r6c2 r6c3
2934 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3");
2935 SELECT * FROM t1 ORDER BY c1;
2936 c1 c2 c3
2937 r1c1 r1c2 r1c3
2938 r2c1 r2c2 r2c3
2939 r3c1 r3c2 r3c3
2940 r4c1 r4c2 r4c3
2941 r5c1 r5c2 r5c3
2942 r6c1 r6c2 r6c3
2943 r7c1 r7c2 r7c3
2944 # ------------------------------------------------------------
2945 # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6]
2946 # ------------------------------------------------------------
2947 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", DROP COLUMN c3, ALGORITHM=INSTANT;
2948 # Metadata from INFORMATION_SCHEMA.TABLES
2949 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2950 test/t1 6 0 4
2951 # Metadata from INFORMATION_SCHEMA.COLUMNS
2952 NAME POS MTYPE PRTYPE HAS_DEFAULT
2953 c1 0 13 16712190 0
2954 c2 1 13 16711934 0
2955 c7 2 13 16711934 1
2956 SELECT * FROM t1 ORDER BY c1;
2957 c1 c2 c7
2958 r1c1 r1c2 c7_def
2959 r2c1 r2c2 c7_def
2960 r3c1 r3c2 c7_def
2961 r4c1 r4c2 c7_def
2962 r5c1 r5c2 c7_def
2963 r6c1 r6c2 c7_def
2964 r7c1 r7c2 c7_def
2965 INSERT INTO t1 values ("r8c1", "r8c2", "r8c7");
2966 SELECT * FROM t1 ORDER BY c1;
2967 c1 c2 c7
2968 r1c1 r1c2 c7_def
2969 r2c1 r2c2 c7_def
2970 r3c1 r3c2 c7_def
2971 r4c1 r4c2 c7_def
2972 r5c1 r5c2 c7_def
2973 r6c1 r6c2 c7_def
2974 r7c1 r7c2 c7_def
2975 r8c1 r8c2 r8c7
2976 # --------------------------------------------------------------------------
2977 # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3]
2978 # --------------------------------------------------------------------------
2979 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, ALGORITHM=INSTANT;
2980 # Metadata from INFORMATION_SCHEMA.TABLES
2981 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
2982 test/t1 6 0 5
2983 # Metadata from INFORMATION_SCHEMA.COLUMNS
2984 NAME POS MTYPE PRTYPE HAS_DEFAULT
2985 c1 0 13 16712190 0
2986 c2 1 13 16711934 0
2987 c7 2 13 16711934 1
2988 SELECT * FROM t1 ORDER BY c1;
2989 c1 c2 c7
2990 r1c1 r1c2 c7_def2
2991 r2c1 r2c2 c7_def2
2992 r3c1 r3c2 c7_def2
2993 r4c1 r4c2 c7_def2
2994 r5c1 r5c2 c7_def2
2995 r6c1 r6c2 c7_def2
2996 r7c1 r7c2 c7_def2
2997 r8c1 r8c2 c7_def2
2998 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7");
2999 SELECT * FROM t1 ORDER BY c1;
3000 c1 c2 c7
3001 r1c1 r1c2 c7_def2
3002 r2c1 r2c2 c7_def2
3003 r3c1 r3c2 c7_def2
3004 r4c1 r4c2 c7_def2
3005 r5c1 r5c2 c7_def2
3006 r6c1 r6c2 c7_def2
3007 r7c1 r7c2 c7_def2
3008 r8c1 r8c2 c7_def2
3009 r9c1 r9c2 r9c7
3010 # ------------------------------------------------------------
3011 # Rebuild table. [c1, c2, c7]
3012 # ------------------------------------------------------------
3013 ALTER TABLE t1 force;
3014 # Metadata from INFORMATION_SCHEMA.TABLES
3015 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
3016 test/t1 6 0 0
3017 # Metadata from INFORMATION_SCHEMA.COLUMNS
3018 NAME POS MTYPE PRTYPE HAS_DEFAULT
3019 c1 0 13 16712190 0
3020 c2 1 13 16711934 0
3021 c7 2 13 16711934 0
3022 SELECT * FROM t1 ORDER BY c1;
3023 c1 c2 c7
3024 r1c1 r1c2 c7_def2
3025 r2c1 r2c2 c7_def2
3026 r3c1 r3c2 c7_def2
3027 r4c1 r4c2 c7_def2
3028 r5c1 r5c2 c7_def2
3029 r6c1 r6c2 c7_def2
3030 r7c1 r7c2 c7_def2
3031 r8c1 r8c2 c7_def2
3032 r9c1 r9c2 r9c7
3033 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7");
3034 SELECT * FROM t1 ORDER BY c1;
3035 c1 c2 c7
3036 r10c1 r10c2 r10c7
3037 r1c1 r1c2 c7_def2
3038 r2c1 r2c2 c7_def2
3039 r3c1 r3c2 c7_def2
3040 r4c1 r4c2 c7_def2
3041 r5c1 r5c2 c7_def2
3042 r6c1 r6c2 c7_def2
3043 r7c1 r7c2 c7_def2
3044 r8c1 r8c2 c7_def2
3045 r9c1 r9c2 r9c7
3046 ###########
3047 # CLEANUP #
3048 ###########
3049 DROP TABLE t1;
3050 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
3051 INSERT INTO t2 VALUES ("r1c1", "r1c2");
3052 SELECT * FROM t2;
3053 c1 c2
3054 r1c1 r1c2
3055 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
3056 INSERT INTO t1 VALUES ("r1c1", "r1c2");
3057 SELECT * FROM t1;
3058 c1 c2
3059 r1c1 r1c2
3060 BEGIN;
3061 SELECT * FROM t2;
3062 c1 c2
3063 r1c1 r1c2
3064 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
3065 ALGORITHM=COPY;
3066 UPDATE t1 SET c1 = "r1c11";
3067 SELECT * FROM t1;
3068 c1 c2 c3
3069 r1c11 r1c2 c3_def
3070 SELECT * FROM t1;
3071 ERROR HY000: Table definition has changed, please retry transaction
3072 DROP TABLE t1;
3073 DROP TABLE t2;
3074 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
3075 INSERT INTO t2 VALUES ("r1c1", "r1c2");
3076 SELECT * FROM t2;
3077 c1 c2
3078 r1c1 r1c2
3079 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
3080 INSERT INTO t1 VALUES ("r1c1", "r1c2");
3081 SELECT * FROM t1;
3082 c1 c2
3083 r1c1 r1c2
3084 BEGIN;
3085 SELECT * FROM t2;
3086 c1 c2
3087 r1c1 r1c2
3088 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
3089 ALGORITHM=INSTANT;
3090 UPDATE t1 SET c1 = "r1c11";
3091 SELECT * FROM t1;
3092 c1 c2 c3
3093 r1c11 r1c2 c3_def
3094 SELECT * FROM t1;
3095 ERROR HY000: Table definition has changed, please retry transaction
3096 DROP TABLE t1;
3097 DROP TABLE t2;
3098 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
3099 INSERT INTO t2 VALUES ("r1c1", "r1c2");
3100 SELECT * FROM t2;
3101 c1 c2
3102 r1c1 r1c2
3103 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
3104 INSERT INTO t1 VALUES ("r1c1", "r1c2");
3105 SELECT * FROM t1;
3106 c1 c2
3107 r1c1 r1c2
3108 BEGIN;
3109 SELECT * FROM t2;
3110 c1 c2
3111 r1c1 r1c2
3112 ALTER TABLE t1 DROP COLUMN c2,
3113 ALGORITHM=COPY;
3114 UPDATE t1 SET c1 = "r1c11";
3115 SELECT * FROM t1;
3116 c1
3117 r1c11
3118 SELECT * FROM t1;
3119 ERROR HY000: Table definition has changed, please retry transaction
3120 DROP TABLE t1;
3121 DROP TABLE t2;
3122 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
3123 INSERT INTO t2 VALUES ("r1c1", "r1c2");
3124 SELECT * FROM t2;
3125 c1 c2
3126 r1c1 r1c2
3127 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
3128 INSERT INTO t1 VALUES ("r1c1", "r1c2");
3129 SELECT * FROM t1;
3130 c1 c2
3131 r1c1 r1c2
3132 BEGIN;
3133 SELECT * FROM t2;
3134 c1 c2
3135 r1c1 r1c2
3136 ALTER TABLE t1 DROP COLUMN c2,
3137 ALGORITHM=INSTANT;
3138 UPDATE t1 SET c1 = "r1c11";
3139 SELECT * FROM t1;
3140 c1
3141 r1c11
3142 SELECT * FROM t1;
3143 ERROR HY000: Table definition has changed, please retry transaction
3144 DROP TABLE t1;
3145 DROP TABLE t2;
3146 # ------------------------------------------------------------
3147 # Create a table with 3 columns. [c1, c2, c3]
3148 # ------------------------------------------------------------
3149 CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20) , PRIMARY KEY (c1) , KEY (c2)) ROW_FORMAT=COMPACT;
3150 # Metadata from INFORMATION_SCHEMA.TABLES
3151 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
3152 test/t1 6 0 0
3153 # Metadata from INFORMATION_SCHEMA.COLUMNS
3154 NAME POS MTYPE PRTYPE HAS_DEFAULT
3155 c1 0 13 16712190 0
3156 c2 1 13 16711934 0
3157 c3 2 13 16711934 0
3158 INSERT INTO t1 values ("r1c1", "r1c2", "r1c3");
3159 SELECT * FROM t1 ORDER BY c1;
3160 c1 c2 c3
3161 r1c1 r1c2 r1c3
3162
3163 # -----------------------ADD COLUMN TEST----------------------
3164
3165 # ------------------------------------------------------------
3166 # Add a new column at the end. [c1, c2, c3, +c4]
3167 # ------------------------------------------------------------
3168 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT "c4_def", ALGORITHM=INSTANT;
3169 # Metadata from INFORMATION_SCHEMA.TABLES
3170 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
3171 test/t1 7 0 1
3172 # Metadata from INFORMATION_SCHEMA.COLUMNS
3173 NAME POS MTYPE PRTYPE HAS_DEFAULT
3174 c1 0 13 16712190 0
3175 c2 1 13 16711934 0
3176 c3 2 13 16711934 0
3177 c4 3 13 16711934 1
3178 INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4");
3179 SELECT * FROM t1 ORDER BY c1;
3180 c1 c2 c3 c4
3181 r1c1 r1c2 r1c3 c4_def
3182 r2c1 r2c2 r2c3 r2c4
3183 # ------------------------------------------------------------
3184 # Add a new column in between. [c1, +c5, c2, c3, c4]
3185 # ------------------------------------------------------------
3186 ALTER TABLE t1 ADD COLUMN c5 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
3187 # Metadata from INFORMATION_SCHEMA.TABLES
3188 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
3189 test/t1 8 0 2
3190 # Metadata from INFORMATION_SCHEMA.COLUMNS
3191 NAME POS MTYPE PRTYPE HAS_DEFAULT
3192 c1 0 13 16712190 0
3193 c5 1 13 16711934 1
3194 c2 2 13 16711934 0
3195 c3 3 13 16711934 0
3196 c4 4 13 16711934 1
3197 INSERT INTO t1 values ("r3c1", "r3c5", "r3c2", "r3c3", "r3c4");
3198 SELECT * FROM t1 ORDER BY c1;
3199 c1 c5 c2 c3 c4
3200 r1c1 NULL r1c2 r1c3 c4_def
3201 r2c1 NULL r2c2 r2c3 r2c4
3202 r3c1 r3c5 r3c2 r3c3 r3c4
3203 # ------------------------------------------------------------
3204 # Add a new column at first. [+c6, c1, c5, c2, c3, c4]
3205 # ------------------------------------------------------------
3206 ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL FIRST, ALGORITHM=INSTANT;
3207 # Metadata from INFORMATION_SCHEMA.TABLES
3208 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
3209 test/t1 9 0 3
3210 # Metadata from INFORMATION_SCHEMA.COLUMNS
3211 NAME POS MTYPE PRTYPE HAS_DEFAULT
3212 c6 0 13 16711934 1
3213 c1 1 13 16712190 0
3214 c5 2 13 16711934 1
3215 c2 3 13 16711934 0
3216 c3 4 13 16711934 0
3217 c4 5 13 16711934 1
3218 INSERT INTO t1 values ("r4c6", "r4c1", "r4c5", "r4c2", "r4c3", "r4c4");
3219 SELECT * FROM t1 ORDER BY c1;
3220 c6 c1 c5 c2 c3 c4
3221 NULL r1c1 NULL r1c2 r1c3 c4_def
3222 NULL r2c1 NULL r2c2 r2c3 r2c4
3223 NULL r3c1 r3c5 r3c2 r3c3 r3c4
3224 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
3225 # ------------------------------------------------------------
3226 # Rebuild table. [c6, c1, c5, c2, c3, c4]
3227 # ------------------------------------------------------------
3228 ALTER TABLE t1 force;
3229 # Metadata from INFORMATION_SCHEMA.TABLES
3230 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
3231 test/t1 9 0 0
3232 # Metadata from INFORMATION_SCHEMA.COLUMNS
3233 NAME POS MTYPE PRTYPE HAS_DEFAULT
3234 c6 0 13 16711934 0
3235 c1 1 13 16712190 0
3236 c5 2 13 16711934 0
3237 c2 3 13 16711934 0
3238 c3 4 13 16711934 0
3239 c4 5 13 16711934 0
3240 SELECT * FROM t1 ORDER BY c1;
3241 c6 c1 c5 c2 c3 c4
3242 NULL r1c1 NULL r1c2 r1c3 c4_def
3243 NULL r2c1 NULL r2c2 r2c3 r2c4
3244 NULL r3c1 r3c5 r3c2 r3c3 r3c4
3245 r4c6 r4c1 r4c5 r4c2 r4c3 r4c4
3246
3247 # -----------------------DROP COLUMN TEST---------------------
3248
3249 # ------------------------------------------------------------
3250 # Drop a column from the end. [c6, c1, c5, c2, c3, -c4]
3251 # ------------------------------------------------------------
3252 ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT;
3253 # Metadata from INFORMATION_SCHEMA.TABLES
3254 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
3255 test/t1 8 0 1
3256 # Metadata from INFORMATION_SCHEMA.COLUMNS
3257 NAME POS MTYPE PRTYPE HAS_DEFAULT
3258 c6 0 13 16711934 0
3259 c1 1 13 16712190 0
3260 c5 2 13 16711934 0
3261 c2 3 13 16711934 0
3262 c3 4 13 16711934 0
3263 SELECT * FROM t1 ORDER BY c1;
3264 c6 c1 c5 c2 c3
3265 NULL r1c1 NULL r1c2 r1c3
3266 NULL r2c1 NULL r2c2 r2c3
3267 NULL r3c1 r3c5 r3c2 r3c3
3268 r4c6 r4c1 r4c5 r4c2 r4c3
3269 INSERT INTO t1 values ("r5c6", "r5c1", "r5c5", "r5c2", "r5c3");
3270 SELECT * FROM t1 ORDER BY c1;
3271 c6 c1 c5 c2 c3
3272 NULL r1c1 NULL r1c2 r1c3
3273 NULL r2c1 NULL r2c2 r2c3
3274 NULL r3c1 r3c5 r3c2 r3c3
3275 r4c6 r4c1 r4c5 r4c2 r4c3
3276 r5c6 r5c1 r5c5 r5c2 r5c3
3277 # ------------------------------------------------------------
3278 # Drop a column somewhere in between. [c6, c1, -c5, c2, c3] [~c4]
3279 # ------------------------------------------------------------
3280 ALTER TABLE t1 DROP COLUMN c5, ALGORITHM=INSTANT;
3281 # Metadata from INFORMATION_SCHEMA.TABLES
3282 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
3283 test/t1 7 0 2
3284 # Metadata from INFORMATION_SCHEMA.COLUMNS
3285 NAME POS MTYPE PRTYPE HAS_DEFAULT
3286 c6 0 13 16711934 0
3287 c1 1 13 16712190 0
3288 c2 2 13 16711934 0
3289 c3 3 13 16711934 0
3290 SELECT * FROM t1 ORDER BY c1;
3291 c6 c1 c2 c3
3292 NULL r1c1 r1c2 r1c3
3293 NULL r2c1 r2c2 r2c3
3294 NULL r3c1 r3c2 r3c3
3295 r4c6 r4c1 r4c2 r4c3
3296 r5c6 r5c1 r5c2 r5c3
3297 INSERT INTO t1 values ("r6c6", "r6c1", "r6c2", "r6c3");
3298 SELECT * FROM t1 ORDER BY c1;
3299 c6 c1 c2 c3
3300 NULL r1c1 r1c2 r1c3
3301 NULL r2c1 r2c2 r2c3
3302 NULL r3c1 r3c2 r3c3
3303 r4c6 r4c1 r4c2 r4c3
3304 r5c6 r5c1 r5c2 r5c3
3305 r6c6 r6c1 r6c2 r6c3
3306 # ------------------------------------------------------------
3307 # Drop the first column. [-c6, c1, c2, c3] [~c4, ~c5]
3308 # ------------------------------------------------------------
3309 ALTER TABLE t1 DROP COLUMN c6, ALGORITHM=INSTANT;
3310 # Metadata from INFORMATION_SCHEMA.TABLES
3311 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
3312 test/t1 6 0 3
3313 # Metadata from INFORMATION_SCHEMA.COLUMNS
3314 NAME POS MTYPE PRTYPE HAS_DEFAULT
3315 c1 0 13 16712190 0
3316 c2 1 13 16711934 0
3317 c3 2 13 16711934 0
3318 SELECT * FROM t1 ORDER BY c1;
3319 c1 c2 c3
3320 r1c1 r1c2 r1c3
3321 r2c1 r2c2 r2c3
3322 r3c1 r3c2 r3c3
3323 r4c1 r4c2 r4c3
3324 r5c1 r5c2 r5c3
3325 r6c1 r6c2 r6c3
3326 INSERT INTO t1 values ("r7c1", "r7c2", "r7c3");
3327 SELECT * FROM t1 ORDER BY c1;
3328 c1 c2 c3
3329 r1c1 r1c2 r1c3
3330 r2c1 r2c2 r2c3
3331 r3c1 r3c2 r3c3
3332 r4c1 r4c2 r4c3
3333 r5c1 r5c2 r5c3
3334 r6c1 r6c2 r6c3
3335 r7c1 r7c2 r7c3
3336 # ------------------------------------------------------------
3337 # ADD and DROP in same statement. [c1, c2, -c3, +c7] [~c4, ~c5, ~c6]
3338 # ------------------------------------------------------------
3339 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def", DROP COLUMN c3, ALGORITHM=INSTANT;
3340 # Metadata from INFORMATION_SCHEMA.TABLES
3341 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
3342 test/t1 6 0 4
3343 # Metadata from INFORMATION_SCHEMA.COLUMNS
3344 NAME POS MTYPE PRTYPE HAS_DEFAULT
3345 c1 0 13 16712190 0
3346 c2 1 13 16711934 0
3347 c7 2 13 16711934 1
3348 SELECT * FROM t1 ORDER BY c1;
3349 c1 c2 c7
3350 r1c1 r1c2 c7_def
3351 r2c1 r2c2 c7_def
3352 r3c1 r3c2 c7_def
3353 r4c1 r4c2 c7_def
3354 r5c1 r5c2 c7_def
3355 r6c1 r6c2 c7_def
3356 r7c1 r7c2 c7_def
3357 INSERT INTO t1 values ("r8c1", "r8c2", "r8c7");
3358 SELECT * FROM t1 ORDER BY c1;
3359 c1 c2 c7
3360 r1c1 r1c2 c7_def
3361 r2c1 r2c2 c7_def
3362 r3c1 r3c2 c7_def
3363 r4c1 r4c2 c7_def
3364 r5c1 r5c2 c7_def
3365 r6c1 r6c2 c7_def
3366 r7c1 r7c2 c7_def
3367 r8c1 r8c2 r8c7
3368 # --------------------------------------------------------------------------
3369 # ADD and DROP same column in same statement. [c1, c2, -c7, +c7] [~c4, ~c5, ~c6, ~c3]
3370 # --------------------------------------------------------------------------
3371 ALTER TABLE t1 ADD COLUMN c7 char(10) default "c7_def2", DROP COLUMN c7, ALGORITHM=INSTANT;
3372 # Metadata from INFORMATION_SCHEMA.TABLES
3373 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
3374 test/t1 6 0 5
3375 # Metadata from INFORMATION_SCHEMA.COLUMNS
3376 NAME POS MTYPE PRTYPE HAS_DEFAULT
3377 c1 0 13 16712190 0
3378 c2 1 13 16711934 0
3379 c7 2 13 16711934 1
3380 SELECT * FROM t1 ORDER BY c1;
3381 c1 c2 c7
3382 r1c1 r1c2 c7_def2
3383 r2c1 r2c2 c7_def2
3384 r3c1 r3c2 c7_def2
3385 r4c1 r4c2 c7_def2
3386 r5c1 r5c2 c7_def2
3387 r6c1 r6c2 c7_def2
3388 r7c1 r7c2 c7_def2
3389 r8c1 r8c2 c7_def2
3390 INSERT INTO t1 values ("r9c1", "r9c2", "r9c7");
3391 SELECT * FROM t1 ORDER BY c1;
3392 c1 c2 c7
3393 r1c1 r1c2 c7_def2
3394 r2c1 r2c2 c7_def2
3395 r3c1 r3c2 c7_def2
3396 r4c1 r4c2 c7_def2
3397 r5c1 r5c2 c7_def2
3398 r6c1 r6c2 c7_def2
3399 r7c1 r7c2 c7_def2
3400 r8c1 r8c2 c7_def2
3401 r9c1 r9c2 r9c7
3402 # ------------------------------------------------------------
3403 # Rebuild table. [c1, c2, c7]
3404 # ------------------------------------------------------------
3405 ALTER TABLE t1 force;
3406 # Metadata from INFORMATION_SCHEMA.TABLES
3407 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
3408 test/t1 6 0 0
3409 # Metadata from INFORMATION_SCHEMA.COLUMNS
3410 NAME POS MTYPE PRTYPE HAS_DEFAULT
3411 c1 0 13 16712190 0
3412 c2 1 13 16711934 0
3413 c7 2 13 16711934 0
3414 SELECT * FROM t1 ORDER BY c1;
3415 c1 c2 c7
3416 r1c1 r1c2 c7_def2
3417 r2c1 r2c2 c7_def2
3418 r3c1 r3c2 c7_def2
3419 r4c1 r4c2 c7_def2
3420 r5c1 r5c2 c7_def2
3421 r6c1 r6c2 c7_def2
3422 r7c1 r7c2 c7_def2
3423 r8c1 r8c2 c7_def2
3424 r9c1 r9c2 r9c7
3425 INSERT INTO t1 values ("r10c1", "r10c2", "r10c7");
3426 SELECT * FROM t1 ORDER BY c1;
3427 c1 c2 c7
3428 r10c1 r10c2 r10c7
3429 r1c1 r1c2 c7_def2
3430 r2c1 r2c2 c7_def2
3431 r3c1 r3c2 c7_def2
3432 r4c1 r4c2 c7_def2
3433 r5c1 r5c2 c7_def2
3434 r6c1 r6c2 c7_def2
3435 r7c1 r7c2 c7_def2
3436 r8c1 r8c2 c7_def2
3437 r9c1 r9c2 r9c7
3438 ###########
3439 # CLEANUP #
3440 ###########
3441 DROP TABLE t1;
3442 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
3443 INSERT INTO t2 VALUES ("r1c1", "r1c2");
3444 SELECT * FROM t2;
3445 c1 c2
3446 r1c1 r1c2
3447 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
3448 INSERT INTO t1 VALUES ("r1c1", "r1c2");
3449 SELECT * FROM t1;
3450 c1 c2
3451 r1c1 r1c2
3452 BEGIN;
3453 SELECT * FROM t2;
3454 c1 c2
3455 r1c1 r1c2
3456 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
3457 ALGORITHM=COPY;
3458 UPDATE t1 SET c1 = "r1c11";
3459 SELECT * FROM t1;
3460 c1 c2 c3
3461 r1c11 r1c2 c3_def
3462 SELECT * FROM t1;
3463 ERROR HY000: Table definition has changed, please retry transaction
3464 DROP TABLE t1;
3465 DROP TABLE t2;
3466 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
3467 INSERT INTO t2 VALUES ("r1c1", "r1c2");
3468 SELECT * FROM t2;
3469 c1 c2
3470 r1c1 r1c2
3471 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
3472 INSERT INTO t1 VALUES ("r1c1", "r1c2");
3473 SELECT * FROM t1;
3474 c1 c2
3475 r1c1 r1c2
3476 BEGIN;
3477 SELECT * FROM t2;
3478 c1 c2
3479 r1c1 r1c2
3480 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def",
3481 ALGORITHM=INSTANT;
3482 UPDATE t1 SET c1 = "r1c11";
3483 SELECT * FROM t1;
3484 c1 c2 c3
3485 r1c11 r1c2 c3_def
3486 SELECT * FROM t1;
3487 ERROR HY000: Table definition has changed, please retry transaction
3488 DROP TABLE t1;
3489 DROP TABLE t2;
3490 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
3491 INSERT INTO t2 VALUES ("r1c1", "r1c2");
3492 SELECT * FROM t2;
3493 c1 c2
3494 r1c1 r1c2
3495 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
3496 INSERT INTO t1 VALUES ("r1c1", "r1c2");
3497 SELECT * FROM t1;
3498 c1 c2
3499 r1c1 r1c2
3500 BEGIN;
3501 SELECT * FROM t2;
3502 c1 c2
3503 r1c1 r1c2
3504 ALTER TABLE t1 DROP COLUMN c2,
3505 ALGORITHM=COPY;
3506 UPDATE t1 SET c1 = "r1c11";
3507 SELECT * FROM t1;
3508 c1
3509 r1c11
3510 SELECT * FROM t1;
3511 ERROR HY000: Table definition has changed, please retry transaction
3512 DROP TABLE t1;
3513 DROP TABLE t2;
3514 CREATE TABLE t2 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
3515 INSERT INTO t2 VALUES ("r1c1", "r1c2");
3516 SELECT * FROM t2;
3517 c1 c2
3518 r1c1 r1c2
3519 CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=COMPACT;
3520 INSERT INTO t1 VALUES ("r1c1", "r1c2");
3521 SELECT * FROM t1;
3522 c1 c2
3523 r1c1 r1c2
3524 BEGIN;
3525 SELECT * FROM t2;
3526 c1 c2
3527 r1c1 r1c2
3528 ALTER TABLE t1 DROP COLUMN c2,
3529 ALGORITHM=INSTANT;
3530 UPDATE t1 SET c1 = "r1c11";
3531 SELECT * FROM t1;
3532 c1
3533 r1c11
3534 SELECT * FROM t1;
3535 ERROR HY000: Table definition has changed, please retry transaction
3536 DROP TABLE t1;
3537 DROP TABLE t2;