Debian Codegraph @

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

raw
1 SET @@session.default_storage_engine = 'InnoDB';
2 #
3 # Section 1. Wrong column definition options
4 # - DEFAULT <value>
5 # - AUTO_INCREMENT
6 # NOT NULL
7 create table t1 (a int, b int generated always as (a+1) virtual not null);
8 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'not null)' at line 1
9 create table t1 (a int, b int generated always as (a+1) stored not null);
10 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'not null)' at line 1
11 create table t1 (a int);
12 alter table t1 add column b int generated always as (a+1) virtual not null;
13 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'not null' at line 1
14 drop table t1;
15 create table t1 (a int, b int generated always as (a+1) virtual null);
16 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'null)' at line 1
17 create table t1 (a int);
18 alter table t1 add column b int generated always as (a+1) virtual null;
19 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'null' at line 1
20 drop table t1;
21 # Added columns mixed with virtual GC and other columns
22 create table t1 (a int);
23 insert into t1 values(1);
24 alter table t1 add column (b int generated always as (a+1) virtual, c int);
25 affected rows: 1
26 info: Records: 1 Duplicates: 0 Warnings: 0
27 alter table t1 add column (d int, e int generated always as (a+1) virtual);
28 affected rows: 1
29 info: Records: 1 Duplicates: 0 Warnings: 0
30 alter table t1 add column (f int generated always as (a+1) virtual, g int as(5) stored);
31 affected rows: 1
32 info: Records: 1 Duplicates: 0 Warnings: 0
33 alter table t1 add column (h int generated always as (a+1) virtual, i int as(5) virtual);
34 affected rows: 0
35 info: Records: 0 Duplicates: 0 Warnings: 0
36 drop table t1;
37 # DEFAULT
38 create table t1 (a int, b int generated always as (a+1) virtual default 0);
39 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'default 0)' at line 1
40 create table t1 (a int);
41 alter table t1 add column b int generated always as (a+1) virtual default 0;
42 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'default 0' at line 1
43 drop table t1;
44 # AUTO_INCREMENT
45 create table t1 (a int, b int generated always as (a+1) virtual AUTO_INCREMENT);
46 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AUTO_INCREMENT)' at line 1
47 create table t1 (a int);
48 alter table t1 add column b int generated always as (a+1) virtual AUTO_INCREMENT;
49 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AUTO_INCREMENT' at line 1
50 drop table t1;
51 # [PRIMARY] KEY
52 create table t1 (a int, b int generated always as (a+1) virtual key);
53 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key)' at line 1
54 create table t1 (a int, b int generated always as (a+1) stored key);
55 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key)' at line 1
56 create table t1 (a int, b int generated always as (a+1) virtual primary key);
57 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'primary key)' at line 1
58 create table t1 (a int, b int generated always as (a+1) stored primary key);
59 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'primary key)' at line 1
60 create table t1 (a int);
61 alter table t1 add column b int generated always as (a+1) virtual key;
62 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key' at line 1
63 alter table t1 add column b int generated always as (a+1) stored key;
64 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key' at line 1
65 alter table t1 add column c int generated always as (a+2) virtual primary key;
66 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'primary key' at line 1
67 show create table t1;
68 Table Create Table
69 t1 CREATE TABLE `t1` (
70 `a` int(11) DEFAULT NULL
71 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
72 alter table t1 add column c int generated always as (a+2) stored primary key;
73 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'primary key' at line 1
74 drop table t1;
75 # Section 2. Other column definition options
76 # - COMMENT
77 # - REFERENCES (only syntax testing here)
78 # - STORED (only systax testing here)
79 create table t1 (a int, b int generated always as (a % 2) virtual comment 'my comment');
80 show create table t1;
81 Table Create Table
82 t1 CREATE TABLE `t1` (
83 `a` int(11) DEFAULT NULL,
84 `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL COMMENT 'my comment'
85 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
86 describe t1;
87 Field Type Null Key Default Extra
88 a int(11) YES NULL
89 b int(11) YES NULL VIRTUAL GENERATED
90 drop table t1;
91 create table t1 (a int, b int generated always as (a % 2) virtual);
92 alter table t1 modify b int generated always as (a % 2) virtual comment 'my comment';
93 show create table t1;
94 Table Create Table
95 t1 CREATE TABLE `t1` (
96 `a` int(11) DEFAULT NULL,
97 `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL COMMENT 'my comment'
98 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
99 describe t1;
100 Field Type Null Key Default Extra
101 a int(11) YES NULL
102 b int(11) YES NULL VIRTUAL GENERATED
103 insert into t1 (a) values (1);
104 select * from t1;
105 a b
106 1 1
107 insert into t1 values (2,default);
108 select a,b from t1 order by a;
109 a b
110 1 1
111 2 0
112 create table t2 like t1;
113 show create table t2;
114 Table Create Table
115 t2 CREATE TABLE `t2` (
116 `a` int(11) DEFAULT NULL,
117 `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL COMMENT 'my comment'
118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
119 describe t2;
120 Field Type Null Key Default Extra
121 a int(11) YES NULL
122 b int(11) YES NULL VIRTUAL GENERATED
123 insert into t2 (a) values (1);
124 select * from t2;
125 a b
126 1 1
127 insert into t2 values (2,default);
128 select a,b from t2 order by a;
129 a b
130 1 1
131 2 0
132 drop table t2;
133 drop table t1;
134 create table t1 (a int, b int generated always as (a % 2) stored);
135 show create table t1;
136 Table Create Table
137 t1 CREATE TABLE `t1` (
138 `a` int(11) DEFAULT NULL,
139 `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) STORED
140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
141 describe t1;
142 Field Type Null Key Default Extra
143 a int(11) YES NULL
144 b int(11) YES NULL STORED GENERATED
145 insert into t1 (a) values (1);
146 select * from t1;
147 a b
148 1 1
149 insert into t1 values (2,default);
150 select a,b from t1 order by a;
151 a b
152 1 1
153 2 0
154 drop table t1;
155 create table t2 (a int);
156 create table t1 (a int, b int generated always as (a % 2) stored references t2(a));
157 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
158 create table t1 (a int, b int generated always as (a % 2) stored);
159 show create table t1;
160 Table Create Table
161 t1 CREATE TABLE `t1` (
162 `a` int(11) DEFAULT NULL,
163 `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) STORED
164 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
165 drop table t1;
166 create table t1 (a int, b int generated always as (a % 2) virtual);
167 alter table t1 modify b int generated always as (a % 2) stored references t2(a);
168 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'references t2(a)' at line 1
169 alter table t1 modify b int generated always as (a % 2) stored;
170 ERROR HY000: This is not yet supported for generated columns
171 show create table t1;
172 Table Create Table
173 t1 CREATE TABLE `t1` (
174 `a` int(11) DEFAULT NULL,
175 `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL
176 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
177 drop table t1;
178 drop table t2;
179 FK options
180 create table t1(a int, b int as (a % 2), c int as (a) stored);
181 create table t2 (a int);
182 alter table t1 add constraint foreign key fk(d) references t2(a);
183 ERROR 42000: Key column 'd' doesn't exist in table
184 alter table t1 add constraint foreign key fk(c) references t2(a) on delete set null;
185 ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
186 alter table t1 add constraint foreign key fk(c) references t2(a) on update set null;
187 ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
188 alter table t1 add constraint foreign key fk(c) references t2(a) on update cascade;
189 ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
190 drop table t1;
191 drop table t2;
192 Generated always is optional
193 create table t1 (a int, b int as (a % 2) virtual);
194 show create table t1;
195 Table Create Table
196 t1 CREATE TABLE `t1` (
197 `a` int(11) DEFAULT NULL,
198 `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL
199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
200 describe t1;
201 Field Type Null Key Default Extra
202 a int(11) YES NULL
203 b int(11) YES NULL VIRTUAL GENERATED
204 drop table t1;
205 create table t1 (a int, b int as (a % 2) stored);
206 show create table t1;
207 Table Create Table
208 t1 CREATE TABLE `t1` (
209 `a` int(11) DEFAULT NULL,
210 `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) STORED
211 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
212 describe t1;
213 Field Type Null Key Default Extra
214 a int(11) YES NULL
215 b int(11) YES NULL STORED GENERATED
216 drop table t1;
217 Default should be non-stored column
218 create table t1 (a int, b int as (a % 2));
219 show create table t1;
220 Table Create Table
221 t1 CREATE TABLE `t1` (
222 `a` int(11) DEFAULT NULL,
223 `b` int(11) GENERATED ALWAYS AS (`a` MOD 2) VIRTUAL
224 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
225 describe t1;
226 Field Type Null Key Default Extra
227 a int(11) YES NULL
228 b int(11) YES NULL VIRTUAL GENERATED
229 drop table t1;
230 Expression can be constant
231 create table t1 (a int, b int as (5 * 2));
232 show create table t1;
233 Table Create Table
234 t1 CREATE TABLE `t1` (
235 `a` int(11) DEFAULT NULL,
236 `b` int(11) GENERATED ALWAYS AS (5 * 2) VIRTUAL
237 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
238 describe t1;
239 Field Type Null Key Default Extra
240 a int(11) YES NULL
241 b int(11) YES NULL VIRTUAL GENERATED
242 drop table t1;
243 Test generated columns referencing other generated columns
244 create table t1 (a int unique, b int generated always as(-a) virtual, c int generated always as (b + 1) virtual);
245 insert into t1 (a) values (1), (2);
246 select * from t1;
247 a b c
248 1 -1 0
249 2 -2 -1
250 insert into t1(a) values (1) on duplicate key update a=3;
251 select * from t1;
252 a b c
253 2 -2 -1
254 3 -3 -2
255 update t1 set a=4 where a=2;
256 select * from t1;
257 a b c
258 3 -3 -2
259 4 -4 -3
260 drop table t1;
261 create table t1 (a int, b int generated always as(-b) virtual, c int generated always as (b + 1) virtual);
262 ERROR 01000: Expression for field `b` is referring to uninitialized field `b`
263 create table t1 (a int, b int generated always as(-c) virtual, c int generated always as (b + 1) virtual);
264 ERROR 01000: Expression for field `b` is referring to uninitialized field `c`
265 create table t1 (pk int auto_increment primary key, col_int_nokey int generated always as (pk + col_int_key) stored, col_int_key int);
266 ERROR HY000: Function or expression 'AUTO_INCREMENT' cannot be used in the GENERATED ALWAYS AS clause of `pk`
267 create table t1 (pk int auto_increment primary key, col_int_nokey int generated always as (pk + col_int_key) unique, col_int_key int);
268 ERROR HY000: Function or expression 'AUTO_INCREMENT' cannot be used in the GENERATED ALWAYS AS clause of `pk`
269 create table t1 (pk int auto_increment primary key, col_int_nokey int generated always as (pk + col_int_key), col_int_key int);
270 show create table t1;
271 Table Create Table
272 t1 CREATE TABLE `t1` (
273 `pk` int(11) NOT NULL AUTO_INCREMENT,
274 `col_int_nokey` int(11) GENERATED ALWAYS AS (`pk` + `col_int_key`) VIRTUAL,
275 `col_int_key` int(11) DEFAULT NULL,
276 PRIMARY KEY (`pk`)
277 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
278 insert t1 (col_int_key) values (10),(20),(30);
279 select * from t1;
280 pk col_int_nokey col_int_key
281 1 11 10
282 2 22 20
283 3 33 30
284 drop table t1;
285 # Bug#20339347: FAIL TO USE CREATE ....SELECT STATEMENT TO CREATE A NEW TABLE
286 create table t1 (a int, b int generated always as(-a) virtual, c int generated always as (b + 1) stored);
287 insert into t1(a) values(1),(2);
288 create table tt as select * from t1;
289 select * from t1 order by a;
290 a b c
291 1 -1 0
292 2 -2 -1
293 select * from tt order by a;
294 a b c
295 1 -1 0
296 2 -2 -1
297 drop table t1,tt;
298 # Bug#20745142: GENERATED COLUMNS: ASSERTION FAILED:
299 # THD->CHANGE_LIST.IS_EMPTY()
300 #
301 CREATE TABLE t1(a bigint AS (a between 1 and 1));
302 ERROR 01000: Expression for field `a` is referring to uninitialized field `a`
303 # Bug#20757211: GENERATED COLUMNS: ALTER TABLE CRASHES
304 # IN FIND_FIELD_IN_TABLE
305 #
306 CREATE TABLE t1(a int);
307 ALTER TABLE t1 ADD COLUMN z int GENERATED ALWAYS AS
308 ( 1 NOT IN (SELECT 1 FROM t1 WHERE c0006) ) virtual;
309 ERROR HY000: Function or expression 'select ...' cannot be used in the GENERATED ALWAYS AS clause of `z`
310 DROP TABLE t1;
311 # Bug#20566243: ERROR WHILE DOING CREATE TABLE T1 SELECT (QUERY ON GC COLUMNS)
312 CREATE TABLE t1(a int, b int as (a + 1),
313 c varchar(12) as ("aaaabb") stored, d blob as (c));
314 INSERT INTO t1(a) VALUES(1),(3);
315 SHOW CREATE TABLE t1;
316 Table Create Table
317 t1 CREATE TABLE `t1` (
318 `a` int(11) DEFAULT NULL,
319 `b` int(11) GENERATED ALWAYS AS (`a` + 1) VIRTUAL,
320 `c` varchar(12) GENERATED ALWAYS AS ('aaaabb') STORED,
321 `d` blob GENERATED ALWAYS AS (`c`) VIRTUAL
322 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
323 SELECT * FROM t1 order by a;
324 a b c d
325 1 2 aaaabb aaaabb
326 3 4 aaaabb aaaabb
327 CREATE TABLE t2 LIKE t1;
328 SHOW CREATE TABLE t2;
329 Table Create Table
330 t2 CREATE TABLE `t2` (
331 `a` int(11) DEFAULT NULL,
332 `b` int(11) GENERATED ALWAYS AS (`a` + 1) VIRTUAL,
333 `c` varchar(12) GENERATED ALWAYS AS ('aaaabb') STORED,
334 `d` blob GENERATED ALWAYS AS (`c`) VIRTUAL
335 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
336 CREATE TABLE t3 AS SELECT * FROM t1;
337 SHOW CREATE TABLE t3;
338 Table Create Table
339 t3 CREATE TABLE `t3` (
340 `a` int(11) DEFAULT NULL,
341 `b` int(11) DEFAULT NULL,
342 `c` varchar(12) DEFAULT NULL,
343 `d` blob DEFAULT NULL
344 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
345 SELECT * FROM t3 order by a;
346 a b c d
347 1 2 aaaabb aaaabb
348 3 4 aaaabb aaaabb
349 CREATE TABLE t4 AS SELECT b,c,d FROM t1;
350 SHOW CREATE TABLE t4;
351 Table Create Table
352 t4 CREATE TABLE `t4` (
353 `b` int(11) DEFAULT NULL,
354 `c` varchar(12) DEFAULT NULL,
355 `d` blob DEFAULT NULL
356 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
357 SELECT * FROM t4 order by b;
358 b c d
359 2 aaaabb aaaabb
360 4 aaaabb aaaabb
361 DROP TABLE t1,t2,t3,t4;
362 # Bug#21025003:WL8149:ASSERTION `CTX->NUM_TO_DROP_FK
363 # == HA_ALTER_INFO->ALTER_INFO-> FAILED
364 #
365 CREATE TABLE t1 (
366 col1 int(11) DEFAULT NULL,
367 col2 int(11) DEFAULT NULL,
368 col3 int(11) DEFAULT NULL,
369 col4 int(11) DEFAULT NULL,
370 col5 int(11) GENERATED ALWAYS AS (col4 / col2) VIRTUAL,
371 col6 text
372 );
373 INSERT INTO t1(col1,col2,col3,col4,col6) VALUES(NULL,1,4,0,REPEAT(2,1000));
374 ALTER TABLE t1 DROP PRIMARY KEY , ADD KEY idx ( col5, col2 );
375 ERROR 42000: Can't DROP INDEX `PRIMARY`; check that it exists
376 DROP TABLE t1;
377 # Bug#20949226:i CAN ASSIGN NON-DEFAULT() VALUE TO GENERATED COLUMN
378 #
379 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
380 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, 5 AS c2;
381 Warnings:
382 Warning 1906 The value specified for generated column 'c2' in table 't1' has been ignored
383 CREATE TABLE t2 (a int);
384 INSERT INTO t2 values(1);
385 DROP TABLE t1;
386 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
387 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, a AS c2 from t2;
388 Warnings:
389 Warning 1906 The value specified for generated column 'c2' in table 't1' has been ignored
390 DROP TABLE t1;
391 CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, 5;
392 SELECT * FROM t1;
393 c2 c1 5
394 2 1 5
395 DROP TABLE t1, t2;
396 # Bug#21074624:i WL8149:SIG 11 INNOBASE_GET_COMPUTED_VALUE |
397 # INNOBASE/HANDLER/HA_INNODB.CC:19082
398 CREATE TABLE t1 (
399 col1 int(11) NOT NULL,
400 col2 int(11) DEFAULT NULL,
401 col3 int(11) NOT NULL,
402 col4 int(11) DEFAULT NULL,
403 col5 int(11) GENERATED ALWAYS AS (col2 % col4) VIRTUAL,
404 col6 int(11) GENERATED ALWAYS AS (col3 + col3) VIRTUAL,
405 col7 int(11) GENERATED ALWAYS AS (col5 / col5) VIRTUAL,
406 col8 int(11) GENERATED ALWAYS AS (col6 / col5) VIRTUAL,
407 col9 text,
408 extra int(11) DEFAULT NULL,
409 KEY idx (col5)
410 );
411 INSERT INTO t1(col1,col2,col3,col4,col9,extra)
412 VALUES(0,6,3,4,REPEAT(4,1000),0);
413 ALTER TABLE t1 DROP COLUMN col1;
414 DROP TABLE t1;
415 # Bug#21390605:VALGRIND ERROR ON DELETE FROM TABLE CONTAINING
416 # AN INDEXED VIRTUAL COLUMN
417 CREATE TABLE t1 (
418 a INTEGER,
419 b INTEGER GENERATED ALWAYS AS (a) VIRTUAL,
420 c INTEGER GENERATED ALWAYS AS (b) VIRTUAL,
421 INDEX idx (b,c)
422 );
423 INSERT INTO t1 (a) VALUES (42);
424 DELETE FROM t1 WHERE c = 42;
425 DROP TABLE t1;
426 # Bug#20757211: GENERATED COLUMNS: ALTER TABLE CRASHES
427 # IN FIND_FIELD_IN_TABLE
428 #
429 CREATE TABLE t1(a int);
430 ALTER TABLE t1 ADD COLUMN z int GENERATED ALWAYS AS
431 ( 1 NOT IN (SELECT 1 FROM t1 WHERE c0006) ) virtual;
432 ERROR HY000: Function or expression 'select ...' cannot be used in the GENERATED ALWAYS AS clause of `z`
433 CREATE TABLE t2(a int, b int as (1 NOT IN (SELECT 1 FROM t1 WHERE not_exist_col)));
434 ERROR HY000: Function or expression 'select ...' cannot be used in the GENERATED ALWAYS AS clause of `b`
435 CREATE TABLE t2(a int, b int as (1 NOT IN (SELECT 1 FROM dual)));
436 ERROR HY000: Function or expression 'select ...' cannot be used in the GENERATED ALWAYS AS clause of `b`
437 DROP TABLE t1;
438 # Bug#21142905: PARTITIONED GENERATED COLS -
439 # !TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET
440 #
441 CREATE TABLE t1 (
442 a int,
443 b int generated always as (a) virtual,
444 c int generated always as (b+a) virtual,
445 d int generated always as (b+a) virtual
446 ) PARTITION BY LINEAR HASH (b);
447 INSERT INTO t1(a) VALUES(0);
448 DELETE FROM t1 WHERE c=1;
449 DROP TABLE t1;
450 CREATE TABLE t1 (c CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS ("foo bar"));
451 SHOW CREATE TABLE t1;
452 Table Create Table
453 t1 CREATE TABLE `t1` (
454 `c` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin GENERATED ALWAYS AS ('foo bar') VIRTUAL
455 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
456 DROP TABLE t1;
457 CREATE TABLE t1 (i INT);
458 ALTER TABLE t1 ADD COLUMN c CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS ("foo bar");
459 SHOW CREATE TABLE t1;
460 Table Create Table
461 t1 CREATE TABLE `t1` (
462 `i` int(11) DEFAULT NULL,
463 `c` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin GENERATED ALWAYS AS ('foo bar') VIRTUAL
464 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
465 DROP TABLE t1;
466 CREATE TABLE t1 (i INT COLLATE utf8_bin, c INT COLLATE utf8_bin GENERATED ALWAYS AS (10));
467 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'GENERATED ALWAYS AS (10))' at line 1
468 # Check for a charset mismatch processing:
469 # Bug #21469535: VALGRIND ERROR (CONDITIONAL JUMP) WHEN INSERT
470 # ROWS INTO A PARTITIONED TABLE
471 #
472 CREATE TABLE t1 (
473 id INT NOT NULL,
474 store_id INT NOT NULL,
475 x INT GENERATED ALWAYS AS (id + store_id)
476 )
477 PARTITION BY RANGE (store_id) (
478 PARTITION p0 VALUES LESS THAN (6),
479 PARTITION p1 VALUES LESS THAN (11),
480 PARTITION p2 VALUES LESS THAN (16),
481 PARTITION p3 VALUES LESS THAN (21)
482 );
483 INSERT INTO t1 VALUES(1, 2, default);
484 DROP TABLE t1;
485 # Bug#21465626:ASSERT/CRASH ON DROPPING/ADDING VIRTUAL COLUMN
486 CREATE TABLE t (a int(11), b int(11),
487 c int(11) GENERATED ALWAYS AS (a+b) VIRTUAL,
488 d int(11) GENERATED ALWAYS AS (a+b) VIRTUAL);
489 INSERT INTO t(a,b) VALUES(1,2);
490 # Mixed drop/add/rename virtual with non-virtual columns,
491 # ALGORITHM=INPLACE is not supported for InnoDB
492 ALTER TABLE t DROP d, ADD e varchar(10);
493 affected rows: 1
494 info: Records: 1 Duplicates: 0 Warnings: 0
495 ALTER TABLE t ADD d int, ADD f char(10) AS ('aaa');
496 affected rows: 1
497 info: Records: 1 Duplicates: 0 Warnings: 0
498 ALTER TABLE t CHANGE d dd int, CHANGE f ff varchar(10) AS ('bbb');
499 affected rows: 1
500 info: Records: 1 Duplicates: 0 Warnings: 0
501 # Only drop/add/change virtual, inplace is supported for Innodb
502 ALTER TABLE t DROP c, DROP ff;
503 affected rows: 0
504 info: Records: 0 Duplicates: 0 Warnings: 0
505 ALTER TABLE t ADD c int(11) as (a+b), ADD f varchar(10) as ('aaa');
506 affected rows: 0
507 info: Records: 0 Duplicates: 0 Warnings: 0
508 ALTER TABLE t CHANGE c c int(11) as (a), CHANGE f f varchar(10) as('bbb');
509 affected rows: 0
510 info: Records: 0 Duplicates: 0 Warnings: 0
511 # Change order should be ALGORITHM=INPLACE on Innodb
512 ALTER TABLE t CHANGE c c int(11) as (a) after f;
513 affected rows: 1
514 info: Records: 1 Duplicates: 0 Warnings: 0
515 ALTER TABLE t CHANGE b b int(11) after c;
516 affected rows: 1
517 info: Records: 1 Duplicates: 0 Warnings: 0
518 # TODO: Changing virtual column type should be ALGORITHM=INPLACE on InnoDB, current it goes only with COPY method
519 ALTER TABLE t CHANGE c c varchar(10) as ('a');
520 affected rows: 1
521 info: Records: 1 Duplicates: 0 Warnings: 0
522 # Changing stored column type is ALGORITHM=COPY
523 ALTER TABLE t CHANGE dd d varchar(10);
524 affected rows: 1
525 info: Records: 1 Duplicates: 0 Warnings: 0
526 ALTER TABLE t ADD INDEX idx(c), ADD INDEX idx1(d);
527 affected rows: 0
528 info: Records: 0 Duplicates: 0 Warnings: 0
529 ALTER TABLE t DROP INDEX idx, DROP INDEX idx1;
530 affected rows: 0
531 info: Records: 0 Duplicates: 0 Warnings: 0
532 DROP TABLE t;
533 # Bug#21854004: GCOLS:INNODB: FAILING ASSERTION: I < TABLE->N_DEF
534 CREATE TABLE t1(
535 col1 INTEGER PRIMARY KEY,
536 col2 INTEGER,
537 col3 INTEGER,
538 col4 INTEGER,
539 vgc1 INTEGER AS (col2 + col3) VIRTUAL,
540 sgc1 INTEGER AS (col2 - col3) STORED
541 );
542 INSERT INTO t1(col1, col2, col3) VALUES
543 (1, 10, 100), (2, 20, 200);
544 SELECT * FROM t1 order by col1;
545 col1 col2 col3 col4 vgc1 sgc1
546 1 10 100 NULL 110 -90
547 2 20 200 NULL 220 -180
548 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL;
549 SELECT * FROM t1 order by col1;
550 col1 col2 col3 col4 vgc1 sgc1
551 1 10 100 NULL 1000 -90
552 2 20 200 NULL 4000 -180
553 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
554 SELECT * FROM t1 order by col1;
555 col1 col2 col3 col4 vgc1 sgc1
556 1 10 100 NULL 1000 0
557 2 20 200 NULL 4000 0
558 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 + col3) VIRTUAL;
559 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 - col3) STORED;
560 ALTER TABLE t1 ADD INDEX vgc1 (vgc1);
561 ALTER TABLE t1 ADD INDEX sgc1 (sgc1);
562 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL;
563 SELECT * FROM t1 order by col1;
564 col1 col2 col3 col4 vgc1 sgc1
565 1 10 100 NULL 1000 -90
566 2 20 200 NULL 4000 -180
567 SELECT vgc1 FROM t1 order by vgc1;
568 vgc1
569 1000
570 4000
571 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
572 SELECT * FROM t1 order by col1;
573 col1 col2 col3 col4 vgc1 sgc1
574 1 10 100 NULL 1000 0
575 2 20 200 NULL 4000 0
576 SELECT sgc1 FROM t1 order by sgc1;
577 sgc1
578 0
579 0
580 ALTER TABLE t1 DROP INDEX vgc1;
581 ALTER TABLE t1 DROP INDEX sgc1;
582 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 + col3) VIRTUAL;
583 ALTER TABLE t1 ADD UNIQUE INDEX vgc1 (vgc1);
584 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 - col3) STORED;
585 ALTER TABLE t1 ADD UNIQUE INDEX sgc1 (sgc1);
586 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 / col3) VIRTUAL;
587 ERROR 23000: Duplicate entry '0' for key 'vgc1'
588 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
589 ERROR 23000: Duplicate entry '0' for key 'sgc1'
590 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL;
591 SELECT * FROM t1 order by col1;
592 col1 col2 col3 col4 vgc1 sgc1
593 1 10 100 NULL 1000 -90
594 2 20 200 NULL 4000 -180
595 SELECT vgc1 FROM t1 order by col1;
596 vgc1
597 1000
598 4000
599 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 * col3) STORED;
600 SELECT * FROM t1 order by col1;
601 col1 col2 col3 col4 vgc1 sgc1
602 1 10 100 NULL 1000 1000
603 2 20 200 NULL 4000 4000
604 SELECT sgc1 FROM t1 order by sgc1;
605 sgc1
606 1000
607 4000
608 ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) STORED;
609 ERROR HY000: This is not yet supported for generated columns
610 ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) VIRTUAL;
611 ERROR HY000: This is not yet supported for generated columns
612 ALTER TABLE t1 MODIFY COLUMN col4 INTEGER AS (col1 + col2 + col3) STORED;
613 SELECT * FROM t1 order by col1;
614 col1 col2 col3 col4 vgc1 sgc1
615 1 10 100 111 1000 1000
616 2 20 200 222 4000 4000
617 ALTER TABLE t1 MODIFY COLUMN col4 INTEGER;
618 SELECT * FROM t1 order by col1;
619 col1 col2 col3 col4 vgc1 sgc1
620 1 10 100 111 1000 1000
621 2 20 200 222 4000 4000
622 DROP TABLE t1;
623 #
624 # bug#22018979: RECORD NOT FOUND ON UPDATE,
625 # VIRTUAL COLUMN, ASSERTION 0
626 SET @sql_mode_save= @@sql_mode;
627 SET sql_mode= 'ANSI';
628 CREATE TABLE t1 (
629 a INT,
630 b VARCHAR(10),
631 c CHAR(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL,
632 PRIMARY KEY (a),
633 KEY c(c)
634 );
635 INSERT INTO t1(a, b) values(1, 'bbbb'), (2, 'cc');
636 SHOW CREATE TABLE t1;
637 Table Create Table
638 t1 CREATE TABLE "t1" (
639 "a" int(11) NOT NULL,
640 "b" varchar(10) DEFAULT NULL,
641 "c" char(3) GENERATED ALWAYS AS (substr("b",1,3)) VIRTUAL,
642 PRIMARY KEY ("a"),
643 KEY "c" ("c")
644 )
645 SELECT * FROM t1 order by a;
646 a b c
647 1 bbbb bbb
648 2 cc cc
649 SET sql_mode= '';
650 FLUSH TABLE t1;
651 SHOW CREATE TABLE t1;
652 Table Create Table
653 t1 CREATE TABLE `t1` (
654 `a` int(11) NOT NULL,
655 `b` varchar(10) DEFAULT NULL,
656 `c` char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL,
657 PRIMARY KEY (`a`),
658 KEY `c` (`c`)
659 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
660 SELECT * FROM t1 order by a;
661 a b c
662 1 bbbb bbb
663 2 cc cc
664 DELETE FROM t1 where a= 2;
665 SET sql_mode= @sql_mode_save;
666 DROP TABLE t1;
667 #
668 # Bug#22680839: DEFAULT IS NOT DETERMINISTIC AND SHOULD NOT BE
669 # ALLOWED IN GENERATED COLUMNS
670 #
671 CREATE TABLE tzz(a INT DEFAULT 5,
672 gc1 INT AS (a+DEFAULT(a)) VIRTUAL,
673 gc2 INT AS (a+DEFAULT(a)) STORED,
674 KEY k1(gc1));
675 INSERT INTO tzz(A) VALUES (1);
676 SELECT * FROM tzz;
677 a gc1 gc2
678 1 6 6
679 SELECT gc1 FROM tzz;
680 gc1
681 6
682 ALTER TABLE tzz MODIFY COLUMN a INT DEFAULT 6;
683 SELECT * FROM tzz;
684 a gc1 gc2
685 1 7 7
686 SELECT gc1 FROM tzz;
687 gc1
688 7
689 DROP TABLE tzz;
690 # Test 1: ALTER DEFAULT
691 #
692 CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 5,
693 b INT AS (1 + DEFAULT(a)) STORED,
694 c INT AS (1 + DEFAULT(a)) VIRTUAL);
695 INSERT INTO t1 VALUES ();
696 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7;
697 affected rows: 1
698 info: Records: 1 Duplicates: 0 Warnings: 0
699 ALTER TABLE t1 MODIFY COLUMN a INT DEFAULT 8;
700 affected rows: 1
701 info: Records: 1 Duplicates: 0 Warnings: 0
702 ALTER TABLE t1 CHANGE COLUMN a a DOUBLE DEFAULT 5;
703 affected rows: 1
704 info: Records: 1 Duplicates: 0 Warnings: 0
705 DROP TABLE t1;
706 # Test 2: ALTER DEFAULT + ADD GCOL
707 #
708 CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 5);
709 INSERT INTO t1 VALUES();
710 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7,
711 ADD COLUMN b1 INT AS (1 + DEFAULT(a)) STORED;
712 affected rows: 1
713 info: Records: 1 Duplicates: 0 Warnings: 0
714 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7,
715 ADD COLUMN c1 INT AS (1 + DEFAULT(a)) VIRTUAL;
716 affected rows: 1
717 info: Records: 1 Duplicates: 0 Warnings: 0
718 ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7,
719 ADD COLUMN b INT AS (1 + DEFAULT(a)) STORED,
720 ADD COLUMN c INT AS (1 + DEFAULT(a)) VIRTUAL;
721 affected rows: 1
722 info: Records: 1 Duplicates: 0 Warnings: 0
723 DROP TABLE t1;
724 #
725 # MDEV-26262 frm is corrupted after ER_EXPRESSION_REFERS_TO_UNINIT_FIELD
726 #
727 CREATE TABLE MDEV_26262 (a INT,b INT AS (b) VIRTUAL);
728 ERROR 01000: Expression for field `b` is referring to uninitialized field `b`
729 NOT FOUND /Incorrect information in file: './test/MDEV_26262.frm'/ in mysqld.1.err
730 DROP VIEW IF EXISTS v1,v2;
731 DROP TABLE IF EXISTS t1,t2,t3;
732 DROP PROCEDURE IF EXISTS p1;
733 DROP FUNCTION IF EXISTS f1;
734 DROP TRIGGER IF EXISTS trg1;
735 DROP TRIGGER IF EXISTS trg2;
736 set sql_warnings = 0;