MDEV-30016 Virtual columns do not support autoincrement columns

change vcol_upgrade test to use stored gcols

6 files changed, 49 insertions, 6 deletions

diff --git a/mysql-test/std_data/vcol_autoinc.MYI b/mysql-test/std_data/vcol_autoinc.MYI
index ddb3f2e0748..9b174844f9f 100644
--- a/mysql-test/std_data/vcol_autoinc.MYI
+++ b/mysql-test/std_data/vcol_autoinc.MYI
diff --git a/mysql-test/std_data/vcol_autoinc.frm b/mysql-test/std_data/vcol_autoinc.frm
index bff7983735c..ee43f878856 100644
--- a/mysql-test/std_data/vcol_autoinc.frm
+++ b/mysql-test/std_data/vcol_autoinc.frm
diff --git a/mysql-test/suite/gcol/inc/gcol_column_def_options.inc b/mysql-test/suite/gcol/inc/gcol_column_def_options.inc
index 17e926758ee..6f4a8ab7240 100644
--- a/mysql-test/suite/gcol/inc/gcol_column_def_options.inc
+++ b/mysql-test/suite/gcol/inc/gcol_column_def_options.inc
@@ -49,18 +49,18 @@ alter table t1 add column (h int generated always as (a+1) virtual, i int as(5)
 drop table t1;
 
 --echo # DEFAULT
---error 1064
+--error ER_PARSE_ERROR
 create table t1 (a int, b int generated always as (a+1) virtual default 0);
 create table t1 (a int);
---error 1064
+--error ER_PARSE_ERROR
 alter table t1 add column b int generated always as (a+1) virtual default 0;
 drop table t1;
 
 --echo # AUTO_INCREMENT
---error 1064
+--error ER_PARSE_ERROR
 create table t1 (a int, b int generated always as (a+1) virtual AUTO_INCREMENT);
 create table t1 (a int);
---error 1064
+--error ER_PARSE_ERROR
 alter table t1 add column b int generated always as (a+1) virtual AUTO_INCREMENT;
 drop table t1;
 
@@ -138,7 +138,7 @@ create table t1 (a int, b int generated always as (a % 2) stored references t2(a
 show create table t1;
 drop table t1;
 create table t1 (a int, b int generated always as (a % 2) virtual);
---error 1064
+--error ER_PARSE_ERROR
 alter table t1 modify b int generated always as (a % 2) stored references t2(a);
 show create table t1;
 drop table t1;
@@ -199,6 +199,13 @@ create table t1 (a int, b int generated always as(-b) virtual, c int generated a
 create table t1 (a int, b int generated always as(-c) virtual, c int generated always as (b + 1) virtual);
 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
 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);
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+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);
+create table t1 (pk int auto_increment primary key, col_int_nokey int generated always as (pk + col_int_key), col_int_key int);
+show create table t1;
+insert t1 (col_int_key) values (10),(20),(30);
+select * from t1;
+drop table t1;
 
 --echo # 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);
diff --git a/mysql-test/suite/gcol/r/gcol_column_def_options_innodb.result b/mysql-test/suite/gcol/r/gcol_column_def_options_innodb.result
index daa372fac8b..2422bdca363 100644
--- a/mysql-test/suite/gcol/r/gcol_column_def_options_innodb.result
+++ b/mysql-test/suite/gcol/r/gcol_column_def_options_innodb.result
@@ -260,6 +260,24 @@ create table t1 (a int, b int generated always as(-c) virtual, c int generated a
 ERROR 01000: Expression for field `b` is referring to uninitialized field `c`
 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);
 ERROR HY000: Function or expression 'AUTO_INCREMENT' cannot be used in the GENERATED ALWAYS AS clause of `pk`
+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);
+ERROR HY000: Function or expression 'AUTO_INCREMENT' cannot be used in the GENERATED ALWAYS AS clause of `pk`
+create table t1 (pk int auto_increment primary key, col_int_nokey int generated always as (pk + col_int_key), col_int_key int);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `pk` int(11) NOT NULL AUTO_INCREMENT,
+  `col_int_nokey` int(11) GENERATED ALWAYS AS (`pk` + `col_int_key`) VIRTUAL,
+  `col_int_key` int(11) DEFAULT NULL,
+  PRIMARY KEY (`pk`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+insert t1 (col_int_key) values (10),(20),(30);
+select * from t1;
+pk	col_int_nokey	col_int_key
+1	11	10
+2	22	20
+3	33	30
+drop table t1;
 # 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);
diff --git a/mysql-test/suite/gcol/r/gcol_column_def_options_myisam.result b/mysql-test/suite/gcol/r/gcol_column_def_options_myisam.result
index 59215426b31..82c4b65512f 100644
--- a/mysql-test/suite/gcol/r/gcol_column_def_options_myisam.result
+++ b/mysql-test/suite/gcol/r/gcol_column_def_options_myisam.result
@@ -260,6 +260,24 @@ create table t1 (a int, b int generated always as(-c) virtual, c int generated a
 ERROR 01000: Expression for field `b` is referring to uninitialized field `c`
 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);
 ERROR HY000: Function or expression 'AUTO_INCREMENT' cannot be used in the GENERATED ALWAYS AS clause of `pk`
+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);
+ERROR HY000: Function or expression 'AUTO_INCREMENT' cannot be used in the GENERATED ALWAYS AS clause of `pk`
+create table t1 (pk int auto_increment primary key, col_int_nokey int generated always as (pk + col_int_key), col_int_key int);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `pk` int(11) NOT NULL AUTO_INCREMENT,
+  `col_int_nokey` int(11) GENERATED ALWAYS AS (`pk` + `col_int_key`) VIRTUAL,
+  `col_int_key` int(11) DEFAULT NULL,
+  PRIMARY KEY (`pk`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+insert t1 (col_int_key) values (10),(20),(30);
+select * from t1;
+pk	col_int_nokey	col_int_key
+1	11	10
+2	22	20
+3	33	30
+drop table t1;
 # 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);
diff --git a/mysql-test/suite/vcol/r/upgrade.result b/mysql-test/suite/vcol/r/upgrade.result
index fea6588a5f3..5393a3543dc 100644
--- a/mysql-test/suite/vcol/r/upgrade.result
+++ b/mysql-test/suite/vcol/r/upgrade.result
@@ -6,7 +6,7 @@ show create table vcol_autoinc;
 Table	Create Table
 vcol_autoinc	CREATE TABLE `vcol_autoinc` (
   `pk` int(11) NOT NULL AUTO_INCREMENT,
-  `v3` int(11) GENERATED ALWAYS AS (`pk`) VIRTUAL,
+  `v3` int(11) GENERATED ALWAYS AS (`pk`) STORED,
   PRIMARY KEY (`pk`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 select * from vcol_autoinc;