919 lines
34 KiB
Plaintext
919 lines
34 KiB
Plaintext
CALL mtr.add_suppression("Incorrect information in file");
|
|
SET @@session.default_storage_engine = 'InnoDB';
|
|
#
|
|
# Section 1. Wrong column definition options
|
|
# - DEFAULT <value>
|
|
# - AUTO_INCREMENT
|
|
# - SERIAL DEFAULT VALUE
|
|
# - ON UPDATE NOW ...
|
|
# NOT NULL
|
|
create table t1 (a int, b int generated always as (a+1) virtual not null);
|
|
insert into t1(a) values(null);
|
|
ERROR 23000: Column 'b' cannot be null
|
|
insert into t1(a) values(1);
|
|
select * from t1;
|
|
a b
|
|
1 2
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a+1) stored not null);
|
|
insert into t1(a) values(null);
|
|
ERROR 23000: Column 'b' cannot be null
|
|
insert into t1(a) values(1);
|
|
select * from t1;
|
|
a b
|
|
1 2
|
|
drop table t1;
|
|
create table t1 (a int);
|
|
alter table t1 add column b int generated always as (a+1) virtual not null;
|
|
drop table t1;
|
|
# NULL
|
|
create table t1 (a int, b int generated always as (a+1) virtual null);
|
|
drop table t1;
|
|
create table t1 (a int);
|
|
alter table t1 add column b int generated always as (a+1) virtual null;
|
|
drop table t1;
|
|
# Added columns mixed with virtual GC and other columns
|
|
create table t1 (a int);
|
|
insert into t1 values(1);
|
|
alter table t1 add column (b int generated always as (a+1) virtual, c int);
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
alter table t1 add column (d int, e int generated always as (a+1) virtual);
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
alter table t1 add column (f int generated always as (a+1) virtual, g int as(5) stored);
|
|
affected rows: 1
|
|
info: Records: 1 Duplicates: 0 Warnings: 0
|
|
alter table t1 add column (h int generated always as (a+1) virtual, i int as(5) virtual);
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
drop table t1;
|
|
# DEFAULT
|
|
create table t1 (a int, b int generated always as (a+1) virtual default 0);
|
|
ERROR HY000: Incorrect usage of DEFAULT and generated column
|
|
create table t1 (a int);
|
|
alter table t1 add column b int generated always as (a+1) virtual default 0;
|
|
ERROR HY000: Incorrect usage of DEFAULT and generated column
|
|
drop table t1;
|
|
# AUTO_INCREMENT
|
|
create table t1 (a int, b int generated always as (a+1) virtual AUTO_INCREMENT);
|
|
ERROR HY000: Incorrect usage of AUTO_INCREMENT and generated column
|
|
create table t1 (a int);
|
|
alter table t1 add column b int generated always as (a+1) virtual AUTO_INCREMENT;
|
|
ERROR HY000: Incorrect usage of AUTO_INCREMENT and generated column
|
|
drop table t1;
|
|
# SERIAL DEFAULT VALUE
|
|
create table t1 (a int, b int generated always as (a+1) virtual SERIAL DEFAULT VALUE);
|
|
ERROR HY000: Incorrect usage of SERIAL DEFAULT VALUE and generated column
|
|
create table t1 (a int);
|
|
alter table t1 add column b int generated always as (a+1) virtual SERIAL DEFAULT VALUE;
|
|
ERROR HY000: Incorrect usage of SERIAL DEFAULT VALUE and generated column
|
|
drop table t1;
|
|
# ON UPDATE
|
|
create table t1 (a int, b int generated always as (a+1) virtual ON UPDATE NOW());
|
|
ERROR HY000: Incorrect usage of ON UPDATE and generated column
|
|
create table t1 (a int);
|
|
alter table t1 add column b int generated always as (a+1) virtual ON UPDATE NOW();
|
|
ERROR HY000: Incorrect usage of ON UPDATE and generated column
|
|
drop table t1;
|
|
# [PRIMARY] KEY
|
|
create table t1 (a int, b int generated always as (a+1) virtual key);
|
|
ERROR HY000: 'Defining a virtual generated column as primary key' is not supported for generated columns.
|
|
create table t1 (a int, b int generated always as (a+1) stored key);
|
|
insert into t1 (a) values (3),(1),(2);
|
|
select * from t1;
|
|
a b
|
|
1 2
|
|
2 3
|
|
3 4
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a+1) virtual primary key);
|
|
ERROR HY000: 'Defining a virtual generated column as primary key' is not supported for generated columns.
|
|
create table t1 (a int, b int generated always as (a+1) stored primary key);
|
|
insert into t1 (a) values (3),(1),(2);
|
|
select * from t1 order by b;
|
|
a b
|
|
1 2
|
|
2 3
|
|
3 4
|
|
drop table t1;
|
|
create table t1 (a int);
|
|
alter table t1 add column b int generated always as (a+1) virtual key;
|
|
ERROR HY000: 'Defining a virtual generated column as primary key' is not supported for generated columns.
|
|
alter table t1 add column b int generated always as (a+1) stored key;
|
|
alter table t1 add column c int generated always as (a+2) virtual primary key;
|
|
ERROR 42000: Multiple primary key defined
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS ((`a` + 1)) STORED NOT NULL,
|
|
PRIMARY KEY (`b`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
alter table t1 add column c int generated always as (a+2) stored primary key;
|
|
ERROR 42000: Multiple primary key defined
|
|
drop table t1;
|
|
# Section 2. Other column definition options
|
|
# - COMMENT
|
|
# - REFERENCES (only syntax testing here)
|
|
# - STORED (only systax testing here)
|
|
create table t1 (a int, b int generated always as (a % 2) virtual comment 'my comment');
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL COMMENT 'my comment'
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
describe t1;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES NULL
|
|
b int(11) YES NULL VIRTUAL GENERATED
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a % 2) virtual);
|
|
alter table t1 modify b int generated always as (a % 2) virtual comment 'my comment';
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL COMMENT 'my comment'
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
describe t1;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES NULL
|
|
b int(11) YES NULL VIRTUAL GENERATED
|
|
insert into t1 (a) values (1);
|
|
select * from t1;
|
|
a b
|
|
1 1
|
|
insert into t1 values (2,default);
|
|
select a,b from t1 order by a;
|
|
a b
|
|
1 1
|
|
2 0
|
|
create table t2 like t1;
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL COMMENT 'my comment'
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
describe t2;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES NULL
|
|
b int(11) YES NULL VIRTUAL GENERATED
|
|
insert into t2 (a) values (1);
|
|
select * from t2;
|
|
a b
|
|
1 1
|
|
insert into t2 values (2,default);
|
|
select a,b from t2 order by a;
|
|
a b
|
|
1 1
|
|
2 0
|
|
drop table t2;
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a % 2) stored);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS ((`a` % 2)) STORED
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
describe t1;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES NULL
|
|
b int(11) YES NULL STORED GENERATED
|
|
insert into t1 (a) values (1);
|
|
select * from t1;
|
|
a b
|
|
1 1
|
|
insert into t1 values (2,default);
|
|
select a,b from t1 order by a;
|
|
a b
|
|
1 1
|
|
2 0
|
|
drop table t1;
|
|
create table t2 (a int);
|
|
create table t1 (a int, b int generated always as (a % 2) stored references t2(a));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS ((`a` % 2)) STORED
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as (a % 2) virtual);
|
|
alter table t1 modify b int generated always as (a % 2) stored references t2(a);
|
|
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
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
drop table t1;
|
|
drop table t2;
|
|
FK options
|
|
create table t1(a int, b int as (a % 2), c int as (a) stored);
|
|
create table t2 (a int primary key);
|
|
alter table t1 add constraint foreign key fk(d) references t2(a);
|
|
ERROR 42000: Key column 'd' doesn't exist in table
|
|
alter table t1 add constraint fk foreign key (b) references t2(a);
|
|
ERROR HY000: Foreign key 'fk' uses virtual column 'b' which is not supported.
|
|
alter table t1 add constraint foreign key fk(c) references t2(a) on delete set null;
|
|
ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column.
|
|
alter table t1 add constraint foreign key fk(c) references t2(a) on update set null;
|
|
ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column.
|
|
alter table t1 add constraint foreign key fk(c) references t2(a) on update cascade;
|
|
ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column.
|
|
drop table t1;
|
|
drop table t2;
|
|
Generated alwasy is optional
|
|
create table t1 (a int, b int as (a % 2) virtual);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
describe t1;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES NULL
|
|
b int(11) YES NULL VIRTUAL GENERATED
|
|
drop table t1;
|
|
create table t1 (a int, b int as (a % 2) stored);
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS ((`a` % 2)) STORED
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
describe t1;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES NULL
|
|
b int(11) YES NULL STORED GENERATED
|
|
drop table t1;
|
|
Default should be non-stored column
|
|
create table t1 (a int, b int as (a % 2));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS ((`a` % 2)) VIRTUAL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
describe t1;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES NULL
|
|
b int(11) YES NULL VIRTUAL GENERATED
|
|
drop table t1;
|
|
Expression can be constant
|
|
create table t1 (a int, b int as (5 * 2));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS ((5 * 2)) VIRTUAL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
describe t1;
|
|
Field Type Null Key Default Extra
|
|
a int(11) YES NULL
|
|
b int(11) YES NULL VIRTUAL GENERATED
|
|
drop table t1;
|
|
Test generated columns referencing other generated columns
|
|
create table t1 (a int unique, b int generated always as(-a) virtual, c int generated always as (b + 1) virtual);
|
|
insert into t1 (a) values (1), (2);
|
|
select * from t1;
|
|
a b c
|
|
1 -1 0
|
|
2 -2 -1
|
|
insert into t1(a) values (1) on duplicate key update a=3;
|
|
select * from t1;
|
|
a b c
|
|
2 -2 -1
|
|
3 -3 -2
|
|
update t1 set a=4 where a=2;
|
|
select * from t1;
|
|
a b c
|
|
3 -3 -2
|
|
4 -4 -3
|
|
drop table t1;
|
|
create table t1 (a int, b int generated always as(-b) virtual, c int generated always as (b + 1) virtual);
|
|
ERROR HY000: Generated column can refer only to generated columns defined prior to it.
|
|
create table t1 (a int, b int generated always as(-c) virtual, c int generated always as (b + 1) virtual);
|
|
ERROR HY000: Generated column can refer only to generated columns defined prior to it.
|
|
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));
|
|
ERROR HY000: Generated column 'col_int_nokey' cannot refer to auto-increment column.
|
|
# Bug#20339347: FAIL TO USE CREATE ....SELECT STATEMENT TO CREATE A NEW TABLE
|
|
create table t1 (a int, b int generated always as(-a) virtual, c int generated always as (b + 1) stored);
|
|
insert into t1(a) values(1),(2);
|
|
create table tt as select * from t1;
|
|
select * from t1 order by a;
|
|
a b c
|
|
1 -1 0
|
|
2 -2 -1
|
|
select * from tt order by a;
|
|
a b c
|
|
1 -1 0
|
|
2 -2 -1
|
|
drop table t1,tt;
|
|
# Bug#20745142: GENERATED COLUMNS: ASSERTION FAILED:
|
|
# THD->CHANGE_LIST.IS_EMPTY()
|
|
#
|
|
CREATE TABLE t1(a bigint AS (a between 1 and 1));
|
|
ERROR HY000: Generated column can refer only to generated columns defined prior to it.
|
|
# Bug#20757211: GENERATED COLUMNS: ALTER TABLE CRASHES
|
|
# IN FIND_FIELD_IN_TABLE
|
|
#
|
|
CREATE TABLE t1(a int);
|
|
ALTER TABLE t1 ADD COLUMN z int GENERATED ALWAYS AS
|
|
( 1 NOT IN (SELECT 1 FROM t1 WHERE c0006) ) virtual;
|
|
ERROR HY000: Expression of generated column 'z' contains a disallowed function.
|
|
DROP TABLE t1;
|
|
# Bug#20566243: ERROR WHILE DOING CREATE TABLE T1 SELECT (QUERY ON GC COLUMNS)
|
|
CREATE TABLE t1(a int, b int as (a + 1),
|
|
c varchar(12) as ("aaaabb") stored, d blob as (c));
|
|
INSERT INTO t1(a) VALUES(1),(3);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS ((`a` + 1)) VIRTUAL,
|
|
`c` varchar(12) GENERATED ALWAYS AS (_utf8mb4'aaaabb') STORED,
|
|
`d` blob GENERATED ALWAYS AS (`c`) VIRTUAL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SELECT * FROM t1 order by a;
|
|
a b c d
|
|
1 2 aaaabb aaaabb
|
|
3 4 aaaabb aaaabb
|
|
CREATE TABLE t2 LIKE t1;
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) GENERATED ALWAYS AS ((`a` + 1)) VIRTUAL,
|
|
`c` varchar(12) GENERATED ALWAYS AS (_utf8mb4'aaaabb') STORED,
|
|
`d` blob GENERATED ALWAYS AS (`c`) VIRTUAL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
CREATE TABLE t3 AS SELECT * FROM t1;
|
|
SHOW CREATE TABLE t3;
|
|
Table Create Table
|
|
t3 CREATE TABLE `t3` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` varchar(12) DEFAULT NULL,
|
|
`d` blob
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SELECT * FROM t3 order by a;
|
|
a b c d
|
|
1 2 aaaabb aaaabb
|
|
3 4 aaaabb aaaabb
|
|
CREATE TABLE t4 AS SELECT b,c,d FROM t1;
|
|
SHOW CREATE TABLE t4;
|
|
Table Create Table
|
|
t4 CREATE TABLE `t4` (
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` varchar(12) DEFAULT NULL,
|
|
`d` blob
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SELECT * FROM t4 order by b;
|
|
b c d
|
|
2 aaaabb aaaabb
|
|
4 aaaabb aaaabb
|
|
DROP TABLE t1,t2,t3,t4;
|
|
# Bug#21025003:WL8149:ASSERTION `CTX->NUM_TO_DROP_FK
|
|
# == HA_ALTER_INFO->ALTER_INFO-> FAILED
|
|
#
|
|
CREATE TABLE t1 (
|
|
col1 int(11) DEFAULT NULL,
|
|
col2 int(11) DEFAULT NULL,
|
|
col3 int(11) DEFAULT NULL,
|
|
col4 int(11) DEFAULT NULL,
|
|
col5 int(11) GENERATED ALWAYS AS (col4 / col2) VIRTUAL,
|
|
col6 text
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t1(col1,col2,col3,col4,col6) VALUES(NULL,1,4,0,REPEAT(2,1000));
|
|
ALTER TABLE t1 DROP PRIMARY KEY , ADD KEY idx ( col5, col2 );
|
|
ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists
|
|
DROP TABLE t1;
|
|
# Bug#20949226:i CAN ASSIGN NON-DEFAULT() VALUE TO GENERATED COLUMN
|
|
#
|
|
CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, 5 AS c2;
|
|
ERROR HY000: The value specified for generated column 'c2' in table 't1' is not allowed.
|
|
CREATE TABLE t2 (a int);
|
|
INSERT INTO t2 values(1);
|
|
CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, a AS c2 from t2;
|
|
ERROR HY000: The value specified for generated column 'c2' in table 't1' is not allowed.
|
|
CREATE TABLE t1 (c1 INT, c2 INT AS (c1 * 2)) SELECT 1 AS c1, 5;
|
|
SELECT * FROM t1;
|
|
c2 c1 5
|
|
2 1 5
|
|
DROP TABLE t1, t2;
|
|
# Bug#21074624:i WL8149:SIG 11 INNOBASE_GET_COMPUTED_VALUE |
|
|
# INNOBASE/HANDLER/HA_INNODB.CC:19082
|
|
CREATE TABLE t1 (
|
|
col1 int(11) NOT NULL,
|
|
col2 int(11) DEFAULT NULL,
|
|
col3 int(11) NOT NULL,
|
|
col4 int(11) DEFAULT NULL,
|
|
col5 int(11) GENERATED ALWAYS AS (col2 % col4) VIRTUAL,
|
|
col6 int(11) GENERATED ALWAYS AS (col3 + col3) VIRTUAL,
|
|
col7 int(11) GENERATED ALWAYS AS (col5 / col5) VIRTUAL,
|
|
col8 int(11) GENERATED ALWAYS AS (col6 / col5) VIRTUAL,
|
|
col9 text,
|
|
extra int(11) DEFAULT NULL,
|
|
KEY idx (col5)
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t1(col1,col2,col3,col4,col9,extra)
|
|
VALUES(0,6,3,4,REPEAT(4,1000),0);
|
|
ALTER TABLE t1 DROP COLUMN col1;
|
|
DROP TABLE t1;
|
|
# Bug#21390605:VALGRIND ERROR ON DELETE FROM TABLE CONTAINING
|
|
# AN INDEXED VIRTUAL COLUMN
|
|
CREATE TABLE t1 (
|
|
a INTEGER,
|
|
b INTEGER GENERATED ALWAYS AS (a) VIRTUAL,
|
|
c INTEGER GENERATED ALWAYS AS (b) VIRTUAL,
|
|
INDEX idx (b,c)
|
|
);
|
|
INSERT INTO t1 (a) VALUES (42);
|
|
DELETE FROM t1 WHERE c = 42;
|
|
DROP TABLE t1;
|
|
# Bug#20757211: GENERATED COLUMNS: ALTER TABLE CRASHES
|
|
# IN FIND_FIELD_IN_TABLE
|
|
#
|
|
CREATE TABLE t1(a int);
|
|
ALTER TABLE t1 ADD COLUMN z int GENERATED ALWAYS AS
|
|
( 1 NOT IN (SELECT 1 FROM t1 WHERE c0006) ) virtual;
|
|
ERROR HY000: Expression of generated column 'z' contains a disallowed function.
|
|
CREATE TABLE t2(a int, b int as (1 NOT IN (SELECT 1 FROM t1 WHERE not_exist_col)));
|
|
ERROR HY000: Expression of generated column 'b' contains a disallowed function.
|
|
CREATE TABLE t2(a int, b int as (1 NOT IN (SELECT 1 FROM dual)));
|
|
ERROR HY000: Expression of generated column 'b' contains a disallowed function.
|
|
DROP TABLE t1;
|
|
# Bug#21142905: PARTITIONED GENERATED COLS -
|
|
# !TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET
|
|
#
|
|
CREATE TABLE t1 (
|
|
a int,
|
|
b int generated always as (a) virtual,
|
|
c int generated always as (b+a) virtual,
|
|
d int generated always as (b+a) virtual
|
|
) PARTITION BY LINEAR HASH (b);
|
|
INSERT INTO t1(a) VALUES(0);
|
|
DELETE FROM t1 WHERE c=1;
|
|
DROP TABLE t1;
|
|
# Bug #20709487: COLLATE OPTION NOT ACCEPTED IN GENERATED COLUMN
|
|
# DEFINITION
|
|
#
|
|
# Check for a valid syntax:
|
|
CREATE TABLE t1 (c CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS ("foo bar"));
|
|
Warnings:
|
|
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.
|
|
Warning 3778 'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c` char(10) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS (_utf8mb4'foo bar') VIRTUAL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (i INT);
|
|
ALTER TABLE t1 ADD COLUMN c CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS ("foo bar");
|
|
Warnings:
|
|
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.
|
|
Warning 3778 'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`i` int(11) DEFAULT NULL,
|
|
`c` char(10) CHARACTER SET utf8 COLLATE utf8_bin GENERATED ALWAYS AS (_utf8mb4'foo bar') VIRTUAL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (i INT COLLATE utf8_bin, c INT COLLATE utf8_bin GENERATED ALWAYS AS (10));
|
|
Warnings:
|
|
Warning 3778 'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
|
|
Warning 3778 'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`i` int(11) DEFAULT NULL,
|
|
`c` int(11) GENERATED ALWAYS AS (10) VIRTUAL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DROP TABLE t1;
|
|
# Check for a charset mismatch processing:
|
|
CREATE TABLE t1 (t CHAR(10) CHARACTER SET utf8 COLLATE ascii_bin GENERATED ALWAYS AS ("foo bar"));
|
|
ERROR 42000: COLLATION 'ascii_bin' is not valid for CHARACTER SET 'utf8'
|
|
# Check for a sorting order support:
|
|
CREATE TABLE t1 (c CHAR(10) CHARSET latin1 COLLATE latin1_bin,
|
|
c_ci CHAR(10) CHARSET latin1 COLLATE latin1_general_ci GENERATED ALWAYS AS (c),
|
|
c_cs CHAR(10) CHARSET latin1 COLLATE latin1_general_cs GENERATED ALWAYS AS (c));
|
|
INSERT INTO t1 (c) VALUES ('a'), ('A'), ('b');
|
|
SELECT * FROM t1 ORDER BY c;
|
|
c c_ci c_cs
|
|
A A A
|
|
a a a
|
|
b b b
|
|
SELECT * FROM t1 ORDER BY c_ci, c;
|
|
c c_ci c_cs
|
|
A A A
|
|
a a a
|
|
b b b
|
|
SELECT * FROM t1 ORDER BY c_cs;
|
|
c c_ci c_cs
|
|
A A A
|
|
a a a
|
|
b b b
|
|
SELECT c, c_ci REGEXP 'A', c_cs REGEXP 'A' FROM t1;
|
|
c c_ci REGEXP 'A' c_cs REGEXP 'A'
|
|
A 1 1
|
|
a 1 0
|
|
b 0 0
|
|
DROP TABLE t1;
|
|
# Bug #21469535: VALGRIND ERROR (CONDITIONAL JUMP) WHEN INSERT
|
|
# ROWS INTO A PARTITIONED TABLE
|
|
#
|
|
CREATE TABLE t1 (
|
|
id INT NOT NULL,
|
|
store_id INT NOT NULL,
|
|
x INT GENERATED ALWAYS AS (id + store_id)
|
|
)
|
|
PARTITION BY RANGE (store_id) (
|
|
PARTITION p0 VALUES LESS THAN (6),
|
|
PARTITION p1 VALUES LESS THAN (11),
|
|
PARTITION p2 VALUES LESS THAN (16),
|
|
PARTITION p3 VALUES LESS THAN (21)
|
|
);
|
|
INSERT INTO t1 VALUES(1, 2, default);
|
|
DROP TABLE t1;
|
|
# Bug#21465626:ASSERT/CRASH ON DROPPING/ADDING VIRTUAL COLUMN
|
|
CREATE TABLE t (a int(11), b int(11),
|
|
c int(11) GENERATED ALWAYS AS (a+b) VIRTUAL,
|
|
d int(11) GENERATED ALWAYS AS (a+b) VIRTUAL);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t(a,b) VALUES(1,2);
|
|
# Mixed drop/add/rename virtual with non-virtual columns,
|
|
# ALGORITHM=INPLACE is not supported for InnoDB
|
|
ALTER TABLE t DROP d, ADD e varchar(10);
|
|
affected rows: 1
|
|
info: Records: 1 Duplicates: 0 Warnings: 0
|
|
ALTER TABLE t ADD d int, ADD f char(10) AS ('aaa');
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
ALTER TABLE t CHANGE d dd int, CHANGE f ff varchar(10) AS ('bbb');
|
|
affected rows: 1
|
|
info: Records: 1 Duplicates: 0 Warnings: 0
|
|
# Only drop/add/change virtual, inplace is supported for Innodb
|
|
ALTER TABLE t DROP c, DROP ff;
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
ALTER TABLE t ADD c int(11) as (a+b), ADD f varchar(10) as ('aaa');
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 1
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
ALTER TABLE t CHANGE c c int(11) as (a), CHANGE f f varchar(10) as('bbb');
|
|
affected rows: 1
|
|
info: Records: 1 Duplicates: 0 Warnings: 1
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
# Change order should be ALGORITHM=INPLACE on Innodb
|
|
ALTER TABLE t CHANGE c c int(11) as (a) after f;
|
|
affected rows: 1
|
|
info: Records: 1 Duplicates: 0 Warnings: 1
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
ALTER TABLE t CHANGE b b int(11) after c;
|
|
affected rows: 1
|
|
info: Records: 1 Duplicates: 0 Warnings: 1
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
# TODO: Changing virtual column type should be ALGORITHM=INPLACE on InnoDB, current it goes only with COPY method
|
|
ALTER TABLE t CHANGE c c varchar(10) as ('a');
|
|
affected rows: 1
|
|
info: Records: 1 Duplicates: 0 Warnings: 0
|
|
# Changing stored column type is ALGORITHM=COPY
|
|
ALTER TABLE t CHANGE dd d varchar(10);
|
|
affected rows: 1
|
|
info: Records: 1 Duplicates: 0 Warnings: 0
|
|
ALTER TABLE t ADD INDEX idx(a), ADD INDEX idx1(c);
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
ALTER TABLE t RENAME INDEX idx TO idx2, RENAME INDEX idx1 TO idx3;
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
ALTER TABLE t DROP INDEX idx2, DROP INDEX idx3;
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
ALTER TABLE t ADD INDEX idx(c), ADD INDEX idx1(d);
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
ALTER TABLE t DROP INDEX idx, DROP INDEX idx1;
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
DROP TABLE t;
|
|
#
|
|
# Bug#21900170: CAN'T CREATE TABLE WITH NULLABLE GENERATED COLUMNS
|
|
#
|
|
CREATE TABLE t1 (
|
|
a INT,
|
|
b INT GENERATED ALWAYS AS (-a) VIRTUAL NULL,
|
|
c INT GENERATED ALWAYS AS (a + b) STORED NULL);
|
|
SHOW FULL COLUMNS FROM t1;
|
|
Field Type Collation Null Key Default Extra Privileges Comment
|
|
a int(11) NULL YES NULL #
|
|
b int(11) NULL YES NULL VIRTUAL GENERATED #
|
|
c int(11) NULL YES NULL STORED GENERATED #
|
|
INSERT INTO t1 (a) VALUES (NULL), (1);
|
|
SELECT * FROM t1;
|
|
a b c
|
|
1 -1 0
|
|
NULL NULL NULL
|
|
DROP TABLE t1;
|
|
# Bug#21854004: GCOLS:INNODB: FAILING ASSERTION: I < TABLE->N_DEF
|
|
CREATE TABLE t1(
|
|
col1 INTEGER PRIMARY KEY,
|
|
col2 INTEGER,
|
|
col3 INTEGER,
|
|
col4 INTEGER,
|
|
vgc1 INTEGER AS (col2 + col3) VIRTUAL,
|
|
sgc1 INTEGER AS (col2 - col3) STORED
|
|
);
|
|
INSERT INTO t1(col1, col2, col3) VALUES
|
|
(1, 10, 100), (2, 20, 200);
|
|
SELECT * FROM t1 order by col1;
|
|
col1 col2 col3 col4 vgc1 sgc1
|
|
1 10 100 NULL 110 -90
|
|
2 20 200 NULL 220 -180
|
|
ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL;
|
|
SELECT * FROM t1 order by col1;
|
|
col1 col2 col3 col4 vgc1 sgc1
|
|
1 10 100 NULL 1000 -90
|
|
2 20 200 NULL 4000 -180
|
|
ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
|
|
SELECT * FROM t1 order by col1;
|
|
col1 col2 col3 col4 vgc1 sgc1
|
|
1 10 100 NULL 1000 0
|
|
2 20 200 NULL 4000 0
|
|
ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 + col3) VIRTUAL;
|
|
ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 - col3) STORED;
|
|
ALTER TABLE t1 ADD INDEX vgc1 (vgc1);
|
|
ALTER TABLE t1 ADD INDEX sgc1 (sgc1);
|
|
ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL;
|
|
SELECT * FROM t1 order by col1;
|
|
col1 col2 col3 col4 vgc1 sgc1
|
|
1 10 100 NULL 1000 -90
|
|
2 20 200 NULL 4000 -180
|
|
SELECT vgc1 FROM t1 order by vgc1;
|
|
vgc1
|
|
1000
|
|
4000
|
|
ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
|
|
SELECT * FROM t1 order by col1;
|
|
col1 col2 col3 col4 vgc1 sgc1
|
|
1 10 100 NULL 1000 0
|
|
2 20 200 NULL 4000 0
|
|
SELECT sgc1 FROM t1 order by sgc1;
|
|
sgc1
|
|
0
|
|
0
|
|
ALTER TABLE t1 DROP INDEX vgc1;
|
|
ALTER TABLE t1 DROP INDEX sgc1;
|
|
ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 + col3) VIRTUAL;
|
|
ALTER TABLE t1 ADD UNIQUE INDEX vgc1 (vgc1);
|
|
ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 - col3) STORED;
|
|
ALTER TABLE t1 ADD UNIQUE INDEX sgc1 (sgc1);
|
|
ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 / col3) VIRTUAL;
|
|
ERROR 23000: Duplicate entry '0' for key 'vgc1'
|
|
ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) STORED;
|
|
ERROR 23000: Duplicate entry '0' for key 'sgc1'
|
|
ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) VIRTUAL;
|
|
SELECT * FROM t1 order by col1;
|
|
col1 col2 col3 col4 vgc1 sgc1
|
|
1 10 100 NULL 1000 -90
|
|
2 20 200 NULL 4000 -180
|
|
SELECT vgc1 FROM t1 order by col1;
|
|
vgc1
|
|
1000
|
|
4000
|
|
ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 * col3) STORED;
|
|
SELECT * FROM t1 order by col1;
|
|
col1 col2 col3 col4 vgc1 sgc1
|
|
1 10 100 NULL 1000 1000
|
|
2 20 200 NULL 4000 4000
|
|
SELECT sgc1 FROM t1 order by sgc1;
|
|
sgc1
|
|
1000
|
|
4000
|
|
ALTER TABLE t1 MODIFY COLUMN vgc1 INTEGER AS (col2 * col3) STORED;
|
|
ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
|
|
ALTER TABLE t1 MODIFY COLUMN sgc1 INTEGER AS (col2 / col3) VIRTUAL;
|
|
ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
|
|
ALTER TABLE t1 MODIFY COLUMN col4 INTEGER AS (col1 + col2 + col3) STORED;
|
|
SELECT * FROM t1 order by col1;
|
|
col1 col2 col3 col4 vgc1 sgc1
|
|
1 10 100 111 1000 1000
|
|
2 20 200 222 4000 4000
|
|
ALTER TABLE t1 MODIFY COLUMN col4 INTEGER;
|
|
SELECT * FROM t1 order by col1;
|
|
col1 col2 col3 col4 vgc1 sgc1
|
|
1 10 100 111 1000 1000
|
|
2 20 200 222 4000 4000
|
|
DROP TABLE t1;
|
|
#
|
|
# bug#22018979: RECORD NOT FOUND ON UPDATE,
|
|
# VIRTUAL COLUMN, ASSERTION 0
|
|
SET @sql_mode_save= @@sql_mode;
|
|
SET sql_mode= 'ANSI';
|
|
CREATE TABLE t1 (
|
|
a INT,
|
|
b VARCHAR(10),
|
|
c CHAR(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL,
|
|
PRIMARY KEY (a),
|
|
KEY c(c)
|
|
);
|
|
INSERT INTO t1(a, b) values(1, 'bbbb'), (2, 'cc');
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE "t1" (
|
|
"a" int(11) NOT NULL,
|
|
"b" varchar(10) DEFAULT NULL,
|
|
"c" char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL,
|
|
PRIMARY KEY ("a"),
|
|
KEY "c" ("c")
|
|
)
|
|
SELECT * FROM t1 order by a;
|
|
a b c
|
|
1 bbbb bbb
|
|
2 cc cc
|
|
SET sql_mode= '';
|
|
FLUSH TABLE t1;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(10) DEFAULT NULL,
|
|
`c` char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `c` (`c`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SELECT * FROM t1 order by a;
|
|
a b c
|
|
1 bbbb bbb
|
|
2 cc cc
|
|
DELETE FROM t1 where a= 2;
|
|
SET sql_mode= @sql_mode_save;
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#22680839: DEFAULT IS NOT DETERMINISTIC AND SHOULD NOT BE
|
|
# ALLOWED IN GENERATED COLUMNS
|
|
#
|
|
CREATE TABLE tzz(a INT DEFAULT 5,
|
|
gc1 INT AS (a+DEFAULT(a)) VIRTUAL,
|
|
gc2 INT AS (a+DEFAULT(a)) STORED,
|
|
KEY k1(gc1));
|
|
INSERT INTO tzz(A) VALUES (1);
|
|
SELECT * FROM tzz;
|
|
a gc1 gc2
|
|
1 6 6
|
|
SELECT gc1 FROM tzz;
|
|
gc1
|
|
6
|
|
ALTER TABLE tzz MODIFY COLUMN a INT DEFAULT 6;
|
|
SELECT * FROM tzz;
|
|
a gc1 gc2
|
|
1 7 7
|
|
SELECT gc1 FROM tzz;
|
|
gc1
|
|
7
|
|
DROP TABLE tzz;
|
|
# Test 1: ALTER DEFAULT
|
|
#
|
|
CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 5,
|
|
b INT AS (1 + DEFAULT(a)) STORED,
|
|
c INT AS (1 + DEFAULT(a)) VIRTUAL);
|
|
INSERT INTO t1 VALUES ();
|
|
ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7, ALGORITHM = INPLACE;
|
|
Got one of the listed errors
|
|
ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7;
|
|
affected rows: 1
|
|
info: Records: 1 Duplicates: 0 Warnings: 0
|
|
ALTER TABLE t1 MODIFY COLUMN a INT DEFAULT 8;
|
|
affected rows: 1
|
|
info: Records: 1 Duplicates: 0 Warnings: 0
|
|
ALTER TABLE t1 CHANGE COLUMN a a DOUBLE DEFAULT 5;
|
|
affected rows: 1
|
|
info: Records: 1 Duplicates: 0 Warnings: 0
|
|
DROP TABLE t1;
|
|
# Test 2: ALTER DEFAULT + ADD GCOL
|
|
#
|
|
CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 5);
|
|
INSERT INTO t1 VALUES();
|
|
ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7,
|
|
ADD COLUMN b INT AS (1 + DEFAULT(a)) STORED,
|
|
ALGORITHM = INPLACE;
|
|
Got one of the listed errors
|
|
ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 7,
|
|
ADD COLUMN b INT AS (1 + DEFAULT(a)) STORED,
|
|
ADD COLUMN c INT AS (1 + DEFAULT(a)) VIRTUAL;
|
|
affected rows: 1
|
|
info: Records: 1 Duplicates: 0 Warnings: 0
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#28836543 "DUPLICATE HANDLING OF GENERATION EXPRESSION CHANGE IN
|
|
# FILL_ALTER_INPLACE_INFO".
|
|
#
|
|
# For ALTER TABLE statements which do not modify generated column type,
|
|
# expression and storage type INPLACE algorithm should be supported.
|
|
# ALTER TABLE statements which modify generated column expression or
|
|
# type should require COPY algorithm. Change of storage type of
|
|
# generated column is not supported.
|
|
CREATE TABLE t1 (a INT, g INT GENERATED ALWAYS AS (a + 1) VIRTUAL);
|
|
ALTER TABLE t1 MODIFY g INT GENERATED ALWAYS AS (a + 1) VIRTUAL COMMENT 'Test', ALGORITHM=INPLACE;
|
|
ALTER TABLE t1 MODIFY g INT GENERATED ALWAYS AS (a + 2) VIRTUAL COMMENT 'Test', ALGORITHM=INPLACE;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
|
|
ALTER TABLE t1 MODIFY g BIGINT GENERATED ALWAYS AS (a + 1) VIRTUAL COMMENT 'Test', ALGORITHM=INPLACE;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
|
|
ALTER TABLE t1 MODIFY g INT GENERATED ALWAYS AS (a + 1) STORED COMMENT 'Test', ALGORITHM=INPLACE;
|
|
ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
|
|
DROP TABLE t1;
|
|
CREATE TABLE t2 (a INT, g INT GENERATED ALWAYS AS (a + 1) STORED);
|
|
ALTER TABLE t2 MODIFY g INT GENERATED ALWAYS AS (a + 1) STORED COMMENT 'Test', ALGORITHM=INPLACE;
|
|
ALTER TABLE t2 MODIFY g INT GENERATED ALWAYS AS (a + 2) STORED COMMENT 'Test', ALGORITHM=INPLACE;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
|
|
# InnoDB has special error message for change of column type
|
|
ALTER TABLE t2 MODIFY g BIGINT GENERATED ALWAYS AS (a + 1) STORED COMMENT 'Test', ALGORITHM=INPLACE;
|
|
Got one of the listed errors
|
|
ALTER TABLE t2 MODIFY g INT GENERATED ALWAYS AS (a + 1) VIRTUAL COMMENT 'Test', ALGORITHM=INPLACE;
|
|
ERROR HY000: 'Changing the STORED status' is not supported for generated columns.
|
|
DROP TABLE t2;
|
|
#
|
|
# Bug #28848265 "CHANGING DEFAULT BY MAKING COLUMN NULLABLE DOESN'T
|
|
# UPDATE GENERATED COLUMN".
|
|
#
|
|
# Test for original issue.
|
|
CREATE TABLE t1(a INT NOT NULL DEFAULT 4, gc INT AS (a + DEFAULT(a)) STORED);
|
|
INSERT INTO t1 (a) VALUES (1);
|
|
ALTER TABLE t1 MODIFY COLUMN a INT NULL;
|
|
INSERT INTO t1 (a) VALUES (2);
|
|
# Generated stored column value must reflect new default (i.e. NULL).
|
|
SELECT *, a + DEFAULT(a) FROM t1;
|
|
a gc a + DEFAULT(a)
|
|
1 NULL NULL
|
|
2 NULL NULL
|
|
# Check that change of default on which generated column depends
|
|
# can't be done in-place.
|
|
ALTER TABLE t1 MODIFY COLUMN a INT NULL DEFAULT 0, ALGORITHM=INPLACE;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
|
|
ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 0, ALGORITHM=INPLACE;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
|
|
# However, changing default of some other column can be done
|
|
# in-place.
|
|
ALTER TABLE t1 ADD b INT;
|
|
ALTER TABLE t1 MODIFY COLUMN b INT DEFAULT 0, ALGORITHM=INPLACE;
|
|
ALTER TABLE t1 ALTER COLUMN b SET DEFAULT 1, ALGORITHM=INPLACE;
|
|
DROP TABLE t1;
|
|
# Same tests for virtual indexed column.
|
|
CREATE TABLE t1(a INT NOT NULL DEFAULT 4, gc INT AS (a + DEFAULT(a)) VIRTUAL, KEY k(gc));
|
|
INSERT INTO t1 (a) VALUES (1);
|
|
ALTER TABLE t1 MODIFY COLUMN a INT NULL;
|
|
INSERT INTO t1 (a) VALUES (2);
|
|
# Select only virtual column value so its index is used.
|
|
SELECT gc, a + DEFAULT(a) FROM t1;
|
|
gc a + DEFAULT(a)
|
|
NULL NULL
|
|
NULL NULL
|
|
ALTER TABLE t1 MODIFY COLUMN a INT NULL DEFAULT 0, ALGORITHM=INPLACE;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
|
|
ALTER TABLE t1 ALTER COLUMN a SET DEFAULT 0, ALGORITHM=INPLACE;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
|
|
ALTER TABLE t1 ADD b INT;
|
|
ALTER TABLE t1 MODIFY COLUMN b INT DEFAULT 0, ALGORITHM=INPLACE;
|
|
ALTER TABLE t1 ALTER COLUMN b SET DEFAULT 1, ALGORITHM=INPLACE;
|
|
DROP TABLE t1;
|
|
DROP VIEW IF EXISTS v1,v2;
|
|
DROP TABLE IF EXISTS t1,t2,t3;
|
|
DROP PROCEDURE IF EXISTS p1;
|
|
DROP FUNCTION IF EXISTS f1;
|
|
DROP TRIGGER IF EXISTS trg1;
|
|
DROP TRIGGER IF EXISTS trg2;
|
|
set sql_warnings = 0;
|