Debian Codegraph @

mysql-test/suite/gcol/r/gcol_column_def_options_innodb.result

raw
1 CALL mtr.add_suppression("Incorrect information in file");
2 SET @@session.default_storage_engine = 'InnoDB';
3 #
4 # Section 1. Wrong column definition options
5 # - DEFAULT <value>
6 # - AUTO_INCREMENT
7 # - SERIAL DEFAULT VALUE
8 # - ON UPDATE NOW ...
9 # NOT NULL
10 create table t1 (a int, b int generated always as (a+1) virtual not null);
11 insert into t1(a) values(null);
12 ERROR 23000: Column 'b' cannot be null
13 insert into t1(a) values(1);
14 select * from t1;
15 a b
16 1 2
17 drop table t1;
18 create table t1 (a int, b int generated always as (a+1) stored not null);
19 insert into t1(a) values(null);
20 ERROR 23000: Column 'b' cannot be null
21 insert into t1(a) values(1);
22 select * from t1;
23 a b
24 1 2
25 drop table t1;
26 create table t1 (a int);
27 alter table t1 add column b int generated always as (a+1) virtual not null;
28 drop table t1;
29 # NULL
30 create table t1 (a int, b int generated always as (a+1) virtual null);
31 drop table t1;
32 create table t1 (a int);
33 alter table t1 add column b int generated always as (a+1) virtual null;
34 drop table t1;
35 # Added columns mixed with virtual GC and other columns
36 create table t1 (a int);
37 insert into t1 values(1);
38 alter table t1 add column (b int generated always as (a+1) virtual, c int);
39 affected rows: 0
40 info: Records: 0 Duplicates: 0 Warnings: 0
41 alter table t1 add column (d int, e int generated always as (a+1) virtual);
42 affected rows: 0
43 info: Records: 0 Duplicates: 0 Warnings: 0
44 alter table t1 add column (f int generated always as (a+1) virtual, g int as(5) stored);
45 affected rows: 1
46 info: Records: 1 Duplicates: 0 Warnings: 0
47 alter table t1 add column (h int generated always as (a+1) virtual, i int as(5) virtual);
48 affected rows: 0
49 info: Records: 0 Duplicates: 0 Warnings: 0
50 drop table t1;
51 # DEFAULT
52 create table t1 (a int, b int generated always as (a+1) virtual default 0);
53 ERROR HY000: Incorrect usage of DEFAULT and generated column
54 create table t1 (a int);
55 alter table t1 add column b int generated always as (a+1) virtual default 0;
56 ERROR HY000: Incorrect usage of DEFAULT and generated column
57 drop table t1;
58 # AUTO_INCREMENT
59 create table t1 (a int, b int generated always as (a+1) virtual AUTO_INCREMENT);
60 ERROR HY000: Incorrect usage of AUTO_INCREMENT and generated column
61 create table t1 (a int);
62 alter table t1 add column b int generated always as (a+1) virtual AUTO_INCREMENT;
63 ERROR HY000: Incorrect usage of AUTO_INCREMENT and generated column
64 drop table t1;
65 # SERIAL DEFAULT VALUE
66 create table t1 (a int, b int generated always as (a+1) virtual SERIAL DEFAULT VALUE);
67 ERROR HY000: Incorrect usage of SERIAL DEFAULT VALUE and generated column
68 create table t1 (a int);
69 alter table t1 add column b int generated always as (a+1) virtual SERIAL DEFAULT VALUE;
70 ERROR HY000: Incorrect usage of SERIAL DEFAULT VALUE and generated column
71 drop table t1;
72 # ON UPDATE
73 create table t1 (a int, b int generated always as (a+1) virtual ON UPDATE NOW());
74 ERROR HY000: Incorrect usage of ON UPDATE and generated column
75 create table t1 (a int);
76 alter table t1 add column b int generated always as (a+1) virtual ON UPDATE NOW();
77 ERROR HY000: Incorrect usage of ON UPDATE and generated column
78 drop table t1;
79 # [PRIMARY] KEY
80 create table t1 (a int, b int generated always as (a+1) virtual key);
81 ERROR HY000: 'Defining a virtual generated column as primary key' is not supported for generated columns.
82 create table t1 (a int, b int generated always as (a+1) stored key);
83 insert into t1 (a) values (3),(1),(2);
84 select * from t1;
85 a b
86 1 2
87 2 3
88 3 4
89 drop table t1;
90 create table t1 (a int, b int generated always as (a+1) virtual primary key);
91 ERROR HY000: 'Defining a virtual generated column as primary key' is not supported for generated columns.
92 create table t1 (a int, b int generated always as (a+1) stored primary key);
93 insert into t1 (a) values (3),(1),(2);
94 select * from t1 order by b;
95 a b
96 1 2
97 2 3
98 3 4
99 drop table t1;
100 create table t1 (a int);
101 alter table t1 add column b int generated always as (a+1) virtual key;
102 ERROR HY000: 'Defining a virtual generated column as primary key' is not supported for generated columns.
103 alter table t1 add column b int generated always as (a+1) stored key;
104 alter table t1 add column c int generated always as (a+2) virtual primary key;
105 ERROR 42000: Multiple primary key defined
106 show create table t1;
107 Table Create Table
108 t1 CREATE TABLE `t1` (
109 `a` int DEFAULT NULL,
110 `b` int GENERATED ALWAYS AS ((`a` + 1)) STORED NOT NULL,
111 PRIMARY KEY (`b`)
112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
113 alter table t1 add column c int generated always as (a+2) stored primary key;
114 ERROR 42000: Multiple primary key defined
115 drop table t1;
116 # Section 2. Other column definition options
117 # - COMMENT
118 # - REFERENCES (only syntax testing here)
119 # - STORED (only systax testing here)
120 create table t1 (a int, b int generated always as (a % 2) virtual comment 'my comment');
121 show create table t1;
122 Table Create Table
123 t1 CREATE TABLE `t1` (
124 `a` int DEFAULT NULL,
125 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL COMMENT 'my comment'
126 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
127 describe t1;
128 Field Type Null Key Default Extra
129 a int YES NULL
130 b int YES NULL VIRTUAL GENERATED
131 drop table t1;
132 create table t1 (a int, b int generated always as (a % 2) virtual);
133 alter table t1 modify b int generated always as (a % 2) virtual comment 'my comment';
134 show create table t1;
135 Table Create Table
136 t1 CREATE TABLE `t1` (
137 `a` int DEFAULT NULL,
138 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL COMMENT 'my comment'
139 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
140 describe t1;
141 Field Type Null Key Default Extra
142 a int YES NULL
143 b int YES NULL VIRTUAL GENERATED
144 insert into t1 (a) values (1);
145 select * from t1;
146 a b
147 1 1
148 insert into t1 values (2,default);
149 select a,b from t1 order by a;
150 a b
151 1 1
152 2 0
153 create table t2 like t1;
154 show create table t2;
155 Table Create Table
156 t2 CREATE TABLE `t2` (
157 `a` int DEFAULT NULL,
158 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL COMMENT 'my comment'
159 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
160 describe t2;
161 Field Type Null Key Default Extra
162 a int YES NULL
163 b int YES NULL VIRTUAL GENERATED
164 insert into t2 (a) values (1);
165 select * from t2;
166 a b
167 1 1
168 insert into t2 values (2,default);
169 select a,b from t2 order by a;
170 a b
171 1 1
172 2 0
173 drop table t2;
174 drop table t1;
175 create table t1 (a int, b int generated always as (a % 2) stored);
176 show create table t1;
177 Table Create Table
178 t1 CREATE TABLE `t1` (
179 `a` int DEFAULT NULL,
180 `b` int GENERATED ALWAYS AS ((`a` % 2)) STORED
181 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
182 describe t1;
183 Field Type Null Key Default Extra
184 a int YES NULL
185 b int YES NULL STORED GENERATED
186 insert into t1 (a) values (1);
187 select * from t1;
188 a b
189 1 1
190 insert into t1 values (2,default);
191 select a,b from t1 order by a;
192 a b
193 1 1
194 2 0
195 drop table t1;
196 create table t2 (a int);
197 create table t1 (a int, b int generated always as (a % 2) stored references t2(a));
198 show create table t1;
199 Table Create Table
200 t1 CREATE TABLE `t1` (
201 `a` int DEFAULT NULL,
202 `b` int GENERATED ALWAYS AS ((`a` % 2)) STORED
203 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
204 drop table t1;
205 create table t1 (a int, b int generated always as (a % 2) virtual);
206 alter table t1 modify b int generated always as (a % 2) stored references t2(a);
207 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'references t2(a)' at line 1
208 show create table t1;
209 Table Create Table
210 t1 CREATE TABLE `t1` (
211 `a` int DEFAULT NULL,
212 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL
213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
214 drop table t1;
215 drop table t2;
216 FK options
217 create table t1(a int, b int as (a % 2), c int as (a) stored);
218 create table t2 (a int primary key);
219 alter table t1 add constraint foreign key fk(d) references t2(a);
220 ERROR 42000: Key column 'd' doesn't exist in table
221 alter table t1 add constraint fk foreign key (b) references t2(a);
222 ERROR HY000: Foreign key 'fk' uses virtual column 'b' which is not supported.
223 alter table t1 add constraint foreign key fk(c) references t2(a) on delete set null;
224 ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column.
225 alter table t1 add constraint foreign key fk(c) references t2(a) on update set null;
226 ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column.
227 alter table t1 add constraint foreign key fk(c) references t2(a) on update cascade;
228 ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column.
229 drop table t1;
230 drop table t2;
231 Generated alwasy is optional
232 create table t1 (a int, b int as (a % 2) virtual);
233 show create table t1;
234 Table Create Table
235 t1 CREATE TABLE `t1` (
236 `a` int DEFAULT NULL,
237 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL
238 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
239 describe t1;
240 Field Type Null Key Default Extra
241 a int YES NULL
242 b int YES NULL VIRTUAL GENERATED
243 drop table t1;
244 create table t1 (a int, b int as (a % 2) stored);
245 show create table t1;
246 Table Create Table
247 t1 CREATE TABLE `t1` (
248 `a` int DEFAULT NULL,
249 `b` int GENERATED ALWAYS AS ((`a` % 2)) STORED
250 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
251 describe t1;
252 Field Type Null Key Default Extra
253 a int YES NULL
254 b int YES NULL STORED GENERATED
255 drop table t1;
256 Default should be non-stored column
257 create table t1 (a int, b int as (a % 2));
258 show create table t1;
259 Table Create Table
260 t1 CREATE TABLE `t1` (
261 `a` int DEFAULT NULL,
262 `b` int GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL
263 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
264 describe t1;
265 Field Type Null Key Default Extra
266 a int YES NULL
267 b int YES NULL VIRTUAL GENERATED
268 drop table t1;
269 Expression can be constant
270 create table t1 (a int, b int as (5 * 2));
271 show create table t1;
272 Table Create Table
273 t1 CREATE TABLE `t1` (
274 `a` int DEFAULT NULL,
275 `b` int GENERATED ALWAYS AS ((5 * 2)) VIRTUAL
276 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
277 describe t1;
278 Field Type Null Key Default Extra
279 a int YES NULL
280 b int YES NULL VIRTUAL GENERATED
281 drop table t1;
282 Test generated columns referencing other generated columns
283 create table t1 (a int unique, b int generated always as(-a) virtual, c int generated always as (b + 1) virtual);
284 insert into t1 (a) values (1), (2);
285 select * from t1;
286 a b c
287 1 -1 0
288 2 -2 -1
289 insert into t1(a) values (1) on duplicate key update a=3;
290 select * from t1;
291 a b c
292 2 -2 -1
293 3 -3 -2
294 update t1 set a=4 where a=2;
295 select * from t1;
296 a b c
297 3 -3 -2
298 4 -4 -3
299 drop table t1;
300 create table t1 (a int, b int generated always as(-b) virtual, c int generated always as (b + 1) virtual);
301 ERROR HY000: Generated column can refer only to generated columns defined prior to it.
302 create table t1 (a int, b int generated always as(-c) virtual, c int generated always as (b + 1) virtual);
303 ERROR HY000: Generated column can refer only to generated columns defined prior to it.
304 CREATE TABLE t1 (pk INTEGER AUTO_INCREMENT, col_int_nokey INTEGER GENERATED ALWAYS AS (pk + col_int_key) STORED, col_int_key INTEGER, PRIMARY KEY (pk));
305 ERROR HY000: Generated column 'col_int_nokey' cannot refer to auto-increment column.
306 # Bug#20339347: FAIL TO USE CREATE ....SELECT STATEMENT TO CREATE A NEW TABLE
307 create table t1 (a int, b int generated always as(-a) virtual, c int generated always as (b + 1) stored);
308 insert into t1(a) values(1),(2);
309 create table tt as select * from t1;
310 select * from t1 order by a;
311 a b c
312 1 -1 0
313 2 -2 -1
314 select * from tt order by a;
315 a b c
316 1 -1 0
317 2 -2 -1
318 drop table t1,tt;
319 # Bug#20745142: GENERATED COLUMNS: ASSERTION FAILED:
320 # THD->CHANGE_LIST.IS_EMPTY()
321 #
322 CREATE TABLE t1(a bigint AS (a between 1 and 1));
323 ERROR HY000: Generated column can refer only to generated columns defined prior to it.
324 # Bug#20757211: GENERATED COLUMNS: ALTER TABLE CRASHES
325 # IN FIND_FIELD_IN_TABLE
326 #
327 CREATE TABLE t1(a int);
328 ALTER TABLE t1 ADD COLUMN z int GENERATED ALWAYS AS
329 ( 1 NOT IN (SELECT 1 FROM t1 WHERE c0006) ) virtual;
330 ERROR HY000: Expression of generated column 'z' contains a disallowed function.
331 DROP TABLE t1;
332 # Bug#20566243: ERROR WHILE DOING CREATE TABLE T1 SELECT (QUERY ON GC COLUMNS)
333 CREATE TABLE t1(a int, b int as (a + 1),
334 c varchar(12) as ("aaaabb") stored, d blob as (c));
335 INSERT INTO t1(a) VALUES(1),(3);
336 SHOW CREATE TABLE t1;
337 Table Create Table
338 t1 CREATE TABLE `t1` (
339 `a` int DEFAULT NULL,
340 `b` int GENERATED ALWAYS AS ((`a` + 1)) VIRTUAL,
341 `c` varchar(12) GENERATED ALWAYS AS (_utf8mb4'aaaabb') STORED,
342 `d` blob GENERATED ALWAYS AS (`c`) VIRTUAL
343 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
344 SELECT * FROM t1 order by a;
345 a b c d
346 1 2 aaaabb aaaabb
347 3 4 aaaabb aaaabb
348 CREATE TABLE t2 LIKE t1;
349 SHOW CREATE TABLE t2;
350 Table Create Table
351 t2 CREATE TABLE `t2` (
352 `a` int DEFAULT NULL,
353 `b` int GENERATED ALWAYS AS ((`a` + 1)) VIRTUAL,
354 `c` varchar(12) GENERATED ALWAYS AS (_utf8mb4'aaaabb') STORED,
355 `d` blob GENERATED ALWAYS AS (`c`) VIRTUAL
356 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
357 CREATE TABLE t3 AS SELECT * FROM t1;
358 SHOW CREATE TABLE t3;
359 Table Create Table
360 t3 CREATE TABLE `t3` (
361 `a` int DEFAULT NULL,
362 `b` int DEFAULT NULL,
363 `c` varchar(12) DEFAULT NULL,
364 `d` blob
365 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
366 SELECT * FROM t3 order by a;
367 a b c d
368 1 2 aaaabb aaaabb
369 3 4 aaaabb aaaabb
370 CREATE TABLE t4 AS SELECT b,c,d FROM t1;
371 SHOW CREATE TABLE t4;
372 Table Create Table
373 t4 CREATE TABLE `t4` (
374 `b` int DEFAULT NULL,
375 `c` varchar(12) DEFAULT NULL,
376 `d` blob
377 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
378 SELECT * FROM t4 order by b;
379 b c d
380 2 aaaabb aaaabb
381 4 aaaabb aaaabb
382 DROP TABLE t1,t2,t3,t4;
383 # Bug#21025003:WL8149:ASSERTION `CTX->NUM_TO_DROP_FK
384 # == HA_ALTER_INFO->ALTER_INFO-> FAILED
385 #
386 CREATE TABLE t1 (
387 col1 int(11) DEFAULT NULL,
388 col2 int(11) DEFAULT NULL,
389 col3 int(11) DEFAULT NULL,
390 col4 int(11) DEFAULT NULL,
391 col5 int(11) GENERATED ALWAYS AS (col4 / col2) VIRTUAL,
392 col6 text
393 );
394 Warnings:
395 Warning 1681 Integer display width is deprecated and will be removed in a future release.
396 Warning 1681 Integer display width is deprecated and will be removed in a future release.
397 Warning 1681 Integer display width is deprecated and will be removed in a future release.
398 Warning 1681 Integer display width is deprecated and will be removed in a future release.
399 Warning 1681 Integer display width is deprecated and will be removed in a future release.
400 INSERT INTO t1(col1,col2,col3,col4,col6) VALUES(NULL,1,4,0,REPEAT(2,1000));
401 ALTER TABLE t1 DROP PRIMARY KEY , ADD KEY idx ( col5, col2 );
402 ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists
403 DROP TABLE t1;
404 # Bug#20949226:i CAN ASSIGN NON-DEFAULT() VALUE TO GENERATED COLUMN
405 #
406 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, 5 AS c2;
407 ERROR HY000: The value specified for generated column 'c2' in table 't1' is not allowed.
408 CREATE TABLE t2 (a int);
409 INSERT INTO t2 values(1);
410 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, a AS c2 from t2;
411 ERROR HY000: The value specified for generated column 'c2' in table 't1' is not allowed.
412 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, 5;
413 SELECT * FROM t1;
414 c2 c1 5
415 2 1 5
416 DROP TABLE t1, t2;
417 # Bug#21074624:i WL8149:SIG 11 INNOBASE_GET_COMPUTED_VALUE |
418 # INNOBASE/HANDLER/HA_INNODB.CC:19082
419 CREATE TABLE t1 (
420 col1 int(11) NOT NULL,
421 col2 int(11) DEFAULT NULL,
422 col3 int(11) NOT NULL,
423 col4 int(11) DEFAULT NULL,
424 col5 int(11) GENERATED ALWAYS AS (col2 % col4) VIRTUAL,
425 col6 int(11) GENERATED ALWAYS AS (col3 + col3) VIRTUAL,
426 col7 int(11) GENERATED ALWAYS AS (col5 / col5) VIRTUAL,
427 col8 int(11) GENERATED ALWAYS AS (col6 / col5) VIRTUAL,
428 col9 text,
429 extra int(11) DEFAULT NULL,
430 KEY idx (col5)
431 );
432 Warnings:
433 Warning 1681 Integer display width is deprecated and will be removed in a future release.
434 Warning 1681 Integer display width is deprecated and will be removed in a future release.
435 Warning 1681 Integer display width is deprecated and will be removed in a future release.
436 Warning 1681 Integer display width is deprecated and will be removed in a future release.
437 Warning 1681 Integer display width is deprecated and will be removed in a future release.
438 Warning 1681 Integer display width is deprecated and will be removed in a future release.
439 Warning 1681 Integer display width is deprecated and will be removed in a future release.
440 Warning 1681 Integer display width is deprecated and will be removed in a future release.
441 Warning 1681 Integer display width is deprecated and will be removed in a future release.
442 INSERT INTO t1(col1,col2,col3,col4,col9,extra)
443 VALUES(0,6,3,4,REPEAT(4,1000),0);
444 ALTER TABLE t1 DROP COLUMN col1;
445 DROP TABLE t1;
446 # Bug#21390605:VALGRIND ERROR ON DELETE FROM TABLE CONTAINING
447 # AN INDEXED VIRTUAL COLUMN
448 CREATE TABLE t1 (
449 a INTEGER,
450 b INTEGER GENERATED ALWAYS AS (a) VIRTUAL,
451 c INTEGER GENERATED ALWAYS AS (b) VIRTUAL,
452 INDEX idx (b,c)
453 );
454 INSERT INTO t1 (a) VALUES (42);
455 DELETE FROM t1 WHERE c = 42;
456 DROP TABLE t1;
457 # Bug#20757211: GENERATED COLUMNS: ALTER TABLE CRASHES
458 # IN FIND_FIELD_IN_TABLE
459 #
460 CREATE TABLE t1(a int);
461 ALTER TABLE t1 ADD COLUMN z int GENERATED ALWAYS AS
462 ( 1 NOT IN (SELECT 1 FROM t1 WHERE c0006) ) virtual;
463 ERROR HY000: Expression of generated column 'z' contains a disallowed function.
464 CREATE TABLE t2(a int, b int as (1 NOT IN (SELECT 1 FROM t1 WHERE not_exist_col)));
465 ERROR HY000: Expression of generated column 'b' contains a disallowed function.
466 CREATE TABLE t2(a int, b int as (1 NOT IN (SELECT 1 FROM dual)));
467 ERROR HY000: Expression of generated column 'b' contains a disallowed function.
468 DROP TABLE t1;
469 # Bug#21142905: PARTITIONED GENERATED COLS -
470 # !TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET
471 #
472 CREATE TABLE t1 (
473 a int,
474 b int generated always as (a) virtual,
475 c int generated always as (b+a) virtual,
476 d int generated always as (b+a) virtual
477 ) PARTITION BY LINEAR HASH (b);
478 INSERT INTO t1(a) VALUES(0);
479 DELETE FROM t1 WHERE c=1;
480 DROP TABLE t1;
481 # Bug #20709487: COLLATE OPTION NOT ACCEPTED IN GENERATED COLUMN
482 # DEFINITION
483 #
484 # Check for a valid syntax:
485 CREATE TABLE t1 (c CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS ("foo bar"));
486 Warnings:
487 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
488 Warning 3778 'utf8mb3_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
489 SHOW CREATE TABLE t1;
490 Table Create Table
491 t1 CREATE TABLE `t1` (
492 `c` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin GENERATED ALWAYS AS (_utf8mb4'foo bar') VIRTUAL
493 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
494 DROP TABLE t1;
495 CREATE TABLE t1 (i INT);
496 ALTER TABLE t1 ADD COLUMN c CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS ("foo bar");
497 Warnings:
498 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
499 Warning 3778 'utf8mb3_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
500 SHOW CREATE TABLE t1;
501 Table Create Table
502 t1 CREATE TABLE `t1` (
503 `i` int DEFAULT NULL,
504 `c` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin GENERATED ALWAYS AS (_utf8mb4'foo bar') VIRTUAL
505 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
506 DROP TABLE t1;
507 CREATE TABLE t1 (i INT COLLATE utf8_bin, c INT COLLATE utf8_bin GENERATED ALWAYS AS (10));
508 Warnings:
509 Warning 3778 'utf8mb3_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
510 Warning 3778 'utf8mb3_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
511 SHOW CREATE TABLE t1;
512 Table Create Table
513 t1 CREATE TABLE `t1` (
514 `i` int DEFAULT NULL,
515 `c` int GENERATED ALWAYS AS (10) VIRTUAL
516 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
517 DROP TABLE t1;
518 # Check for a charset mismatch processing:
519 CREATE TABLE t1 (t CHAR(10) CHARACTER SET utf8 COLLATE ascii_bin GENERATED ALWAYS AS ("foo bar"));
520 ERROR 42000: COLLATION 'ascii_bin' is not valid for CHARACTER SET 'utf8mb3'
521 # Check for a sorting order support:
522 CREATE TABLE t1 (c CHAR(10) CHARSET latin1 COLLATE latin1_bin,
523 c_ci CHAR(10) CHARSET latin1 COLLATE latin1_general_ci GENERATED ALWAYS AS (c),
524 c_cs CHAR(10) CHARSET latin1 COLLATE latin1_general_cs GENERATED ALWAYS AS (c));
525 INSERT INTO t1 (c) VALUES ('a'), ('A'), ('b');
526 SELECT * FROM t1 ORDER BY c;
527 c c_ci c_cs
528 A A A
529 a a a
530 b b b
531 SELECT * FROM t1 ORDER BY c_ci, c;
532 c c_ci c_cs
533 A A A
534 a a a
535 b b b
536 SELECT * FROM t1 ORDER BY c_cs;
537 c c_ci c_cs
538 A A A
539 a a a
540 b b b
541 SELECT c, c_ci REGEXP 'A', c_cs REGEXP 'A' FROM t1;
542 c c_ci REGEXP 'A' c_cs REGEXP 'A'
543 A 1 1
544 a 1 0
545 b 0 0
546 DROP TABLE t1;
547 # Bug #21469535: VALGRIND ERROR (CONDITIONAL JUMP) WHEN INSERT
548 # ROWS INTO A PARTITIONED TABLE
549 #
550 CREATE TABLE t1 (
551 id INT NOT NULL,
552 store_id INT NOT NULL,
553 x INT GENERATED ALWAYS AS (id + store_id)
554 )
555 PARTITION BY RANGE (store_id) (
556 PARTITION p0 VALUES LESS THAN (6),
557 PARTITION p1 VALUES LESS THAN (11),
558 PARTITION p2 VALUES LESS THAN (16),
559 PARTITION p3 VALUES LESS THAN (21)
560 );
561 INSERT INTO t1 VALUES(1, 2, default);
562 DROP TABLE t1;
563 # Bug#21465626:ASSERT/CRASH ON DROPPING/ADDING VIRTUAL COLUMN
564 CREATE TABLE t (a int(11), b int(11),
565 c int(11) GENERATED ALWAYS AS (a+b) VIRTUAL,
566 d int(11) GENERATED ALWAYS AS (a+b) VIRTUAL);
567 Warnings:
568 Warning 1681 Integer display width is deprecated and will be removed in a future release.
569 Warning 1681 Integer display width is deprecated and will be removed in a future release.
570 Warning 1681 Integer display width is deprecated and will be removed in a future release.
571 Warning 1681 Integer display width is deprecated and will be removed in a future release.
572 INSERT INTO t(a,b) VALUES(1,2);
573 # Mixed drop/add/rename virtual with non-virtual columns,
574 # ALGORITHM=INPLACE is not supported for InnoDB
575 ALTER TABLE t DROP d, ADD e varchar(10);
576 affected rows: 1
577 info: Records: 1 Duplicates: 0 Warnings: 0
578 ALTER TABLE t ADD d int, ADD f char(10) AS ('aaa');
579 affected rows: 0
580 info: Records: 0 Duplicates: 0 Warnings: 0
581 ALTER TABLE t CHANGE d dd int, CHANGE f ff varchar(10) AS ('bbb');
582 affected rows: 1
583 info: Records: 1 Duplicates: 0 Warnings: 0
584 # Only drop/add/change virtual, inplace is supported for Innodb
585 ALTER TABLE t DROP c, DROP ff;
586 affected rows: 0
587 info: Records: 0 Duplicates: 0 Warnings: 0
588 ALTER TABLE t ADD c int(11) as (a+b), ADD f varchar(10) as ('aaa');
589 affected rows: 0
590 info: Records: 0 Duplicates: 0 Warnings: 1
591 Warnings:
592 Warning 1681 Integer display width is deprecated and will be removed in a future release.
593 ALTER TABLE t CHANGE c c int(11) as (a), CHANGE f f varchar(10) as('bbb');
594 affected rows: 1
595 info: Records: 1 Duplicates: 0 Warnings: 1
596 Warnings:
597 Warning 1681 Integer display width is deprecated and will be removed in a future release.
598 # Change order should be ALGORITHM=INPLACE on Innodb
599 ALTER TABLE t CHANGE c c int(11) as (a) after f;
600 affected rows: 1
601 info: Records: 1 Duplicates: 0 Warnings: 1
602 Warnings:
603 Warning 1681 Integer display width is deprecated and will be removed in a future release.
604 ALTER TABLE t CHANGE b b int(11) after c;
605 affected rows: 1
606 info: Records: 1 Duplicates: 0 Warnings: 1
607 Warnings:
608 Warning 1681 Integer display width is deprecated and will be removed in a future release.
609 # TODO: Changing virtual column type should be ALGORITHM=INPLACE on InnoDB, current it goes only with COPY method
610 ALTER TABLE t CHANGE c c varchar(10) as ('a');
611 affected rows: 1
612 info: Records: 1 Duplicates: 0 Warnings: 0
613 # Changing stored column type is ALGORITHM=COPY
614 ALTER TABLE t CHANGE dd d varchar(10);
615 affected rows: 1
616 info: Records: 1 Duplicates: 0 Warnings: 0
617 ALTER TABLE t ADD INDEX idx(a), ADD INDEX idx1(c);
618 affected rows: 0
619 info: Records: 0 Duplicates: 0 Warnings: 0
620 ALTER TABLE t RENAME INDEX idx TO idx2, RENAME INDEX idx1 TO idx3;
621 affected rows: 0
622 info: Records: 0 Duplicates: 0 Warnings: 0
623 ALTER TABLE t DROP INDEX idx2, DROP INDEX idx3;
624 affected rows: 0
625 info: Records: 0 Duplicates: 0 Warnings: 0
626 ALTER TABLE t ADD INDEX idx(c), ADD INDEX idx1(d);
627 affected rows: 0
628 info: Records: 0 Duplicates: 0 Warnings: 0
629 ALTER TABLE t DROP INDEX idx, DROP INDEX idx1;
630 affected rows: 0
631 info: Records: 0 Duplicates: 0 Warnings: 0
632 DROP TABLE t;
633 #
634 # Bug#21900170: CAN'T CREATE TABLE WITH NULLABLE GENERATED COLUMNS
635 #
636 CREATE TABLE t1 (
637 a INT,
638 b INT GENERATED ALWAYS AS (-a) VIRTUAL NULL,
639 c INT GENERATED ALWAYS AS (a + b) STORED NULL);
640 SHOW FULL COLUMNS FROM t1;
641 Field Type Collation Null Key Default Extra Privileges Comment
642 a int NULL YES NULL #
643 b int NULL YES NULL VIRTUAL GENERATED #
644 c int NULL YES NULL STORED GENERATED #
645 INSERT INTO t1 (a) VALUES (NULL), (1);
646 SELECT * FROM t1;
647 a b c
648 1 -1 0
649 NULL NULL NULL
650 DROP TABLE t1;
651 # Bug#21854004: GCOLS:INNODB: FAILING ASSERTION: I < TABLE->N_DEF
652 CREATE TABLE t1(
653 col1 INTEGER PRIMARY KEY,
654 col2 INTEGER,
655 col3 INTEGER,
656 col4 INTEGER,
657 vgc1 INTEGER AS (col2 + col3) VIRTUAL,
658 sgc1 INTEGER AS (col2 - col3) STORED
659 );
660 INSERT INTO t1(col1, col2, col3) VALUES
661 (1, 10, 100), (2, 20, 200);
662 SELECT * FROM t1 order by col1;
663 col1 col2 col3 col4 vgc1 sgc1
664 1 10 100 NULL 110 -90
665 2 20 200 NULL 220 -180
666 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL;
667 SELECT * FROM t1 order by col1;
668 col1 col2 col3 col4 vgc1 sgc1
669 1 10 100 NULL 1000 -90
670 2 20 200 NULL 4000 -180
671 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
672 SELECT * FROM t1 order by col1;
673 col1 col2 col3 col4 vgc1 sgc1
674 1 10 100 NULL 1000 0
675 2 20 200 NULL 4000 0
676 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 + col3) VIRTUAL;
677 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 - col3) STORED;
678 ALTER TABLE t1 ADD INDEX vgc1 (vgc1);
679 ALTER TABLE t1 ADD INDEX sgc1 (sgc1);
680 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL;
681 SELECT * FROM t1 order by col1;
682 col1 col2 col3 col4 vgc1 sgc1
683 1 10 100 NULL 1000 -90
684 2 20 200 NULL 4000 -180
685 SELECT vgc1 FROM t1 order by vgc1;
686 vgc1
687 1000
688 4000
689 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
690 SELECT * FROM t1 order by col1;
691 col1 col2 col3 col4 vgc1 sgc1
692 1 10 100 NULL 1000 0
693 2 20 200 NULL 4000 0
694 SELECT sgc1 FROM t1 order by sgc1;
695 sgc1
696 0
697 0
698 ALTER TABLE t1 DROP INDEX vgc1;
699 ALTER TABLE t1 DROP INDEX sgc1;
700 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 + col3) VIRTUAL;
701 ALTER TABLE t1 ADD UNIQUE INDEX vgc1 (vgc1);
702 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 - col3) STORED;
703 ALTER TABLE t1 ADD UNIQUE INDEX sgc1 (sgc1);
704 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 / col3) VIRTUAL;
705 ERROR 23000: Duplicate entry '0' for key 't1.vgc1'
706 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
707 ERROR 23000: Duplicate entry '0' for key 't1.sgc1'
708 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL;
709 SELECT * FROM t1 order by col1;
710 col1 col2 col3 col4 vgc1 sgc1
711 1 10 100 NULL 1000 -90
712 2 20 200 NULL 4000 -180
713 SELECT vgc1 FROM t1 order by col1;
714 vgc1
715 1000
716 4000
717 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 * col3) STORED;
718 SELECT * FROM t1 order by col1;
719 col1 col2 col3 col4 vgc1 sgc1
720 1 10 100 NULL 1000 1000
721 2 20 200 NULL 4000 4000
722 SELECT sgc1 FROM t1 order by sgc1;
723 sgc1
724 1000
725 4000
726 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) STORED;
727 ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
728 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) VIRTUAL;
729 ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
730 ALTER TABLE t1 MODIFY COLUMN col4 INTEGER AS (col1 + col2 + col3) STORED;
731 SELECT * FROM t1 order by col1;
732 col1 col2 col3 col4 vgc1 sgc1
733 1 10 100 111 1000 1000
734 2 20 200 222 4000 4000
735 ALTER TABLE t1 MODIFY COLUMN col4 INTEGER;
736 SELECT * FROM t1 order by col1;
737 col1 col2 col3 col4 vgc1 sgc1
738 1 10 100 111 1000 1000
739 2 20 200 222 4000 4000
740 DROP TABLE t1;
741 #
742 # bug#22018979: RECORD NOT FOUND ON UPDATE,
743 # VIRTUAL COLUMN, ASSERTION 0
744 SET @sql_mode_save= @@sql_mode;
745 SET sql_mode= 'ANSI';
746 CREATE TABLE t1 (
747 a INT,
748 b VARCHAR(10),
749 c CHAR(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL,
750 PRIMARY KEY (a),
751 KEY c(c)
752 );
753 INSERT INTO t1(a, b) values(1, 'bbbb'), (2, 'cc');
754 SHOW CREATE TABLE t1;
755 Table Create Table
756 t1 CREATE TABLE "t1" (
757 "a" int NOT NULL,
758 "b" varchar(10) DEFAULT NULL,
759 "c" char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL,
760 PRIMARY KEY ("a"),
761 KEY "c" ("c")
762 )
763 SELECT * FROM t1 order by a;
764 a b c
765 1 bbbb bbb
766 2 cc cc
767 SET sql_mode= '';
768 FLUSH TABLE t1;
769 SHOW CREATE TABLE t1;
770 Table Create Table
771 t1 CREATE TABLE `t1` (
772 `a` int NOT NULL,
773 `b` varchar(10) DEFAULT NULL,
774 `c` char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL,
775 PRIMARY KEY (`a`),
776 KEY `c` (`c`)
777 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
778 SELECT * FROM t1 order by a;
779 a b c
780 1 bbbb bbb
781 2 cc cc
782 DELETE FROM t1 where a= 2;
783 SET sql_mode= @sql_mode_save;
784 DROP TABLE t1;
785 #
786 # Bug#22680839: DEFAULT IS NOT DETERMINISTIC AND SHOULD NOT BE
787 # ALLOWED IN GENERATED COLUMNS
788 #
789 CREATE TABLE tzz(a INT DEFAULT 5,
790 gc1 INT AS (a+DEFAULT(a)) VIRTUAL,
791 gc2 INT AS (a+DEFAULT(a)) STORED,
792 KEY k1(gc1));
793 INSERT INTO tzz(A) VALUES (1);
794 SELECT * FROM tzz;
795 a gc1 gc2
796 1 6 6
797 SELECT gc1 FROM tzz;
798 gc1
799 6
800 ALTER TABLE tzz MODIFY COLUMN a INT DEFAULT 6;
801 SELECT * FROM tzz;
802 a gc1 gc2
803 1 7 7
804 SELECT gc1 FROM tzz;
805 gc1
806 7
807 DROP TABLE tzz;
808 # Test 1: ALTER DEFAULT
809 #
810 CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 5,
811 b INT AS (1 + DEFAULT(a)) STORED,
812 c INT AS (1 + DEFAULT(a)) VIRTUAL);
813 INSERT INTO t1 VALUES ();
814 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7, ALGORITHM = INPLACE;
815 Got one of the listed errors
816 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7;
817 affected rows: 1
818 info: Records: 1 Duplicates: 0 Warnings: 0
819 ALTER TABLE t1 MODIFY COLUMN a INT DEFAULT 8;
820 affected rows: 1
821 info: Records: 1 Duplicates: 0 Warnings: 0
822 ALTER TABLE t1 CHANGE COLUMN a a DOUBLE DEFAULT 5;
823 affected rows: 1
824 info: Records: 1 Duplicates: 0 Warnings: 0
825 DROP TABLE t1;
826 # Test 2: ALTER DEFAULT + ADD GCOL
827 #
828 CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 5);
829 INSERT INTO t1 VALUES();
830 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7,
831 ADD COLUMN b INT AS (1 + DEFAULT(a)) STORED,
832 ALGORITHM = INPLACE;
833 Got one of the listed errors
834 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7,
835 ADD COLUMN b INT AS (1 + DEFAULT(a)) STORED,
836 ADD COLUMN c INT AS (1 + DEFAULT(a)) VIRTUAL;
837 affected rows: 1
838 info: Records: 1 Duplicates: 0 Warnings: 0
839 DROP TABLE t1;
840 #
841 # Bug#28836543 "DUPLICATE HANDLING OF GENERATION EXPRESSION CHANGE IN
842 # FILL_ALTER_INPLACE_INFO".
843 #
844 # For ALTER TABLE statements which do not modify generated column type,
845 # expression and storage type INPLACE algorithm should be supported.
846 # ALTER TABLE statements which modify generated column expression or
847 # type should require COPY algorithm. Change of storage type of
848 # generated column is not supported.
849 CREATE TABLE t1 (a INT, g INT GENERATED ALWAYS AS (a + 1) VIRTUAL);
850 ALTER TABLE t1 MODIFY g INT GENERATED ALWAYS AS (a + 1) VIRTUAL COMMENT 'Test', ALGORITHM=INPLACE;
851 ALTER TABLE t1 MODIFY g INT GENERATED ALWAYS AS (a + 2) VIRTUAL COMMENT 'Test', ALGORITHM=INPLACE;
852 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
853 ALTER TABLE t1 MODIFY g BIGINT GENERATED ALWAYS AS (a + 1) VIRTUAL COMMENT 'Test', ALGORITHM=INPLACE;
854 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
855 ALTER TABLE t1 MODIFY g INT GENERATED ALWAYS AS (a + 1) STORED COMMENT 'Test', ALGORITHM=INPLACE;
856 ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
857 DROP TABLE t1;
858 CREATE TABLE t2 (a INT, g INT GENERATED ALWAYS AS (a + 1) STORED);
859 ALTER TABLE t2 MODIFY g INT GENERATED ALWAYS AS (a + 1) STORED COMMENT 'Test', ALGORITHM=INPLACE;
860 ALTER TABLE t2 MODIFY g INT GENERATED ALWAYS AS (a + 2) STORED COMMENT 'Test', ALGORITHM=INPLACE;
861 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
862 # InnoDB has special error message for change of column type
863 ALTER TABLE t2 MODIFY g BIGINT GENERATED ALWAYS AS (a + 1) STORED COMMENT 'Test', ALGORITHM=INPLACE;
864 Got one of the listed errors
865 ALTER TABLE t2 MODIFY g INT GENERATED ALWAYS AS (a + 1) VIRTUAL COMMENT 'Test', ALGORITHM=INPLACE;
866 ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
867 DROP TABLE t2;
868 #
869 # Bug #28848265 "CHANGING DEFAULT BY MAKING COLUMN NULLABLE DOESN'T
870 # UPDATE GENERATED COLUMN".
871 #
872 # Test for original issue.
873 CREATE TABLE t1(a INT NOT NULL DEFAULT 4, gc INT AS (a + DEFAULT(a)) STORED);
874 INSERT INTO t1 (a) VALUES (1);
875 ALTER TABLE t1 MODIFY COLUMN a INT NULL;
876 INSERT INTO t1 (a) VALUES (2);
877 # Generated stored column value must reflect new default (i.e. NULL).
878 SELECT *, a + DEFAULT(a) FROM t1;
879 a gc a + DEFAULT(a)
880 1 NULL NULL
881 2 NULL NULL
882 # Check that change of default on which generated column depends
883 # can't be done in-place.
884 ALTER TABLE t1 MODIFY COLUMN a INT NULL DEFAULT 0, ALGORITHM=INPLACE;
885 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
886 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 0, ALGORITHM=INPLACE;
887 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
888 # However, changing default of some other column can be done
889 # in-place.
890 ALTER TABLE t1 ADD b INT;
891 ALTER TABLE t1 MODIFY COLUMN b INT DEFAULT 0, ALGORITHM=INPLACE;
892 ALTER TABLE t1 ALTER COLUMN b SET DEFAULT 1, ALGORITHM=INPLACE;
893 DROP TABLE t1;
894 # Same tests for virtual indexed column.
895 CREATE TABLE t1(a INT NOT NULL DEFAULT 4, gc INT AS (a + DEFAULT(a)) VIRTUAL, KEY k(gc));
896 INSERT INTO t1 (a) VALUES (1);
897 ALTER TABLE t1 MODIFY COLUMN a INT NULL;
898 INSERT INTO t1 (a) VALUES (2);
899 # Select only virtual column value so its index is used.
900 SELECT gc, a + DEFAULT(a) FROM t1;
901 gc a + DEFAULT(a)
902 NULL NULL
903 NULL NULL
904 ALTER TABLE t1 MODIFY COLUMN a INT NULL DEFAULT 0, ALGORITHM=INPLACE;
905 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
906 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 0, ALGORITHM=INPLACE;
907 ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
908 ALTER TABLE t1 ADD b INT;
909 ALTER TABLE t1 MODIFY COLUMN b INT DEFAULT 0, ALGORITHM=INPLACE;
910 ALTER TABLE t1 ALTER COLUMN b SET DEFAULT 1, ALGORITHM=INPLACE;
911 DROP TABLE t1;
912 DROP VIEW IF EXISTS v1,v2;
913 DROP TABLE IF EXISTS t1,t2,t3;
914 DROP PROCEDURE IF EXISTS p1;
915 DROP FUNCTION IF EXISTS f1;
916 DROP TRIGGER IF EXISTS trg1;
917 DROP TRIGGER IF EXISTS trg2;
918 set sql_warnings = 0;