summaryrefslogtreecommitdiff
path: root/mysql-test/suite/vcol/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/vcol/r')
-rw-r--r--mysql-test/suite/vcol/r/alter_inplace-9045.result44
-rw-r--r--mysql-test/suite/vcol/r/charsets.result31
-rw-r--r--mysql-test/suite/vcol/r/innodb_autoinc_vcol.result16
-rw-r--r--mysql-test/suite/vcol/r/load_data.result20
-rw-r--r--mysql-test/suite/vcol/r/vcol_merge.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_non_stored_columns_innodb.result16
-rw-r--r--mysql-test/suite/vcol/r/vcol_non_stored_columns_myisam.result16
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;
View Lorry Controller Status