diff options
Diffstat (limited to 'mysql-test/suite/vcol/r')
-rw-r--r-- | mysql-test/suite/vcol/r/alter_inplace-9045.result | 44 | ||||
-rw-r--r-- | mysql-test/suite/vcol/r/charsets.result | 31 | ||||
-rw-r--r-- | mysql-test/suite/vcol/r/innodb_autoinc_vcol.result | 16 | ||||
-rw-r--r-- | mysql-test/suite/vcol/r/load_data.result | 20 | ||||
-rw-r--r-- | mysql-test/suite/vcol/r/vcol_merge.result | 2 | ||||
-rw-r--r-- | mysql-test/suite/vcol/r/vcol_non_stored_columns_innodb.result | 16 | ||||
-rw-r--r-- | mysql-test/suite/vcol/r/vcol_non_stored_columns_myisam.result | 16 |
7 files changed, 144 insertions, 1 deletions
diff --git a/mysql-test/suite/vcol/r/alter_inplace-9045.result b/mysql-test/suite/vcol/r/alter_inplace-9045.result new file mode 100644 index 00000000000..1560d830f42 --- /dev/null +++ b/mysql-test/suite/vcol/r/alter_inplace-9045.result @@ -0,0 +1,44 @@ +create table t1(id int auto_increment primary key, handle int, data bigint not null default 0) engine = innodb; +insert into t1(handle) values(12),(54),(NULL); +select *, md5(handle) from t1; +id handle data md5(handle) +1 12 0 c20ad4d76fe97759aa27a0c99bff6710 +2 54 0 a684eceee76fc522773286a895bc8436 +3 NULL 0 NULL +alter table t1 add index handle(handle), algorithm=inplace; +alter table t1 add column hash varchar(32) as (md5(handle)) persistent, algorithm=inplace; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +alter table t1 add column hash varchar(32) as (md5(handle)) persistent, add unique index hash(hash), algorithm=inplace; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +alter table t1 add column hash varchar(32) as (md5(handle)) persistent, add unique index hash(hash), algorithm=copy; +select * from t1; +id handle data hash +1 12 0 c20ad4d76fe97759aa27a0c99bff6710 +2 54 0 a684eceee76fc522773286a895bc8436 +3 NULL 0 NULL +alter table t1 modify column hash varchar(32) as (md5(handle+1)) persistent, algorithm=inplace; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +alter table t1 modify column hash varchar(32) as (md5(handle+1)) persistent, algorithm=copy; +select * from t1; +id handle data hash +1 12 0 c51ce410c124a10e0db5e4b97fc2af39 +2 54 0 b53b3a3d6ab90ce0268229151c9bde11 +3 NULL 0 NULL +alter table t1 modify column handle int not null, algorithm=inplace; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +alter table t1 modify column handle int not null, algorithm=copy; +Warnings: +Warning 1265 Data truncated for column 'handle' at row 3 +select * from t1; +id handle data hash +1 12 0 c51ce410c124a10e0db5e4b97fc2af39 +2 54 0 b53b3a3d6ab90ce0268229151c9bde11 +3 0 0 c4ca4238a0b923820dcc509a6f75849b +alter table t1 drop index handle, algorithm=inplace; +create index data on t1(data) algorithm=inplace; +alter table t1 drop column data, algorithm=inplace; +alter table t1 add column sha varchar(32) as (sha1(handle)) persistent, algorithm=inplace; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. +alter table t1 add column sha varchar(32), algorithm=inplace; +alter table t1 drop column hash, algorithm=inplace; +drop table t1; diff --git a/mysql-test/suite/vcol/r/charsets.result b/mysql-test/suite/vcol/r/charsets.result new file mode 100644 index 00000000000..d06edc544d9 --- /dev/null +++ b/mysql-test/suite/vcol/r/charsets.result @@ -0,0 +1,31 @@ +set names utf8; +create table t1 ( +a int, +b varchar(100) as (if(a,collation('й'),hex('ю'))) +) character set koi8r; +insert t1 (a) values (0),(1); +select * from t1; +a b +0 D18E +1 utf8_general_ci +set names latin1; +select * from t1; +a b +0 D18E +1 utf8_general_ci +flush tables; +select * from t1; +a b +0 D18E +1 latin1_swedish_ci +set names koi8r; +select * from t1; +a b +0 D18E +1 latin1_swedish_ci +flush tables; +select * from t1; +a b +0 D18E +1 koi8r_general_ci +drop table t1; diff --git a/mysql-test/suite/vcol/r/innodb_autoinc_vcol.result b/mysql-test/suite/vcol/r/innodb_autoinc_vcol.result new file mode 100644 index 00000000000..f2d6b4105ff --- /dev/null +++ b/mysql-test/suite/vcol/r/innodb_autoinc_vcol.result @@ -0,0 +1,16 @@ +create table t1 (c2 int as (-c1), c1 int primary key auto_increment) engine=innodb; +insert into t1(c1) values (null),(null),(null); +select * from t1; +c2 c1 +-1 1 +-2 2 +-3 3 +alter table t1 auto_increment = 3; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c2` int(11) AS (-c1) VIRTUAL, + `c1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 +drop table t1; diff --git a/mysql-test/suite/vcol/r/load_data.result b/mysql-test/suite/vcol/r/load_data.result new file mode 100644 index 00000000000..9769b55ac9a --- /dev/null +++ b/mysql-test/suite/vcol/r/load_data.result @@ -0,0 +1,20 @@ +create table t1 ( c1 varchar(10), c2 varchar(10), c3 int ); +insert into t1 values ("a" , "b", 1), ("a" , "b", 2); +create table t2 like t1 ; +alter table t2 add column c4 bigint unsigned as (CONV(LEFT(MD5(concat(c1,c2,c3)), 16), 16, 10)) persistent unique key; +select * into outfile 't1.csv' from t1; +load data infile 't1.csv' into table t2 ; +Warnings: +Warning 1261 Row 1 doesn't contain data for all columns +Warning 1261 Row 2 doesn't contain data for all columns +select * from t2; +c1 c2 c3 c4 +a b 1 7545402351885872315 +a b 2 6048842355806993119 +insert into t2 (c1,c2,c3) values ("a" , "b", 4); +select * from t2; +c1 c2 c3 c4 +a b 1 7545402351885872315 +a b 2 6048842355806993119 +a b 4 15541743660496249717 +drop table t1, t2; diff --git a/mysql-test/suite/vcol/r/vcol_merge.result b/mysql-test/suite/vcol/r/vcol_merge.result index 4b5ed838c3a..e127ec35e8c 100644 --- a/mysql-test/suite/vcol/r/vcol_merge.result +++ b/mysql-test/suite/vcol/r/vcol_merge.result @@ -4,5 +4,5 @@ create table t2 (a int, b int as (a % 10)); insert into t1 values (1,default); insert into t2 values (2,default); create table t3 (a int, b int as (a % 10)) engine=MERGE UNION=(t1,t2); -ERROR HY000: MRG_MYISAM storage engine does not support computed columns +ERROR HY000: MRG_MyISAM storage engine does not support computed columns drop table t1,t2; diff --git a/mysql-test/suite/vcol/r/vcol_non_stored_columns_innodb.result b/mysql-test/suite/vcol/r/vcol_non_stored_columns_innodb.result index 0379a71922d..85e89f57090 100644 --- a/mysql-test/suite/vcol/r/vcol_non_stored_columns_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_non_stored_columns_innodb.result @@ -240,3 +240,19 @@ t1 CREATE TABLE `t1` ( `c` int(11) AS (week(b,1)) VIRTUAL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1; +# +# MDEV-6103 - Adding/removing non-materialized virtual column triggers +# table recreation +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1); +ALTER TABLE t1 ADD COLUMN b INT AS (a + 1) VIRTUAL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 DROP COLUMN b; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; diff --git a/mysql-test/suite/vcol/r/vcol_non_stored_columns_myisam.result b/mysql-test/suite/vcol/r/vcol_non_stored_columns_myisam.result index de9a962ac2c..d73ea923a11 100644 --- a/mysql-test/suite/vcol/r/vcol_non_stored_columns_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_non_stored_columns_myisam.result @@ -240,3 +240,19 @@ t1 CREATE TABLE `t1` ( `c` int(11) AS (week(b,1)) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +# +# MDEV-6103 - Adding/removing non-materialized virtual column triggers +# table recreation +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1); +ALTER TABLE t1 ADD COLUMN b INT AS (a + 1) VIRTUAL; +affected rows: 10 +info: Records: 10 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 DROP COLUMN b; +affected rows: 10 +info: Records: 10 Duplicates: 0 Warnings: 0 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; |