summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
authorSergei Golubchik <serg@mariadb.org>2022-11-27 14:09:01 +0100
committerSergei Golubchik <serg@mariadb.org>2022-12-02 16:19:13 +0100
commitae53f684d3d02c1ef342891087fa0326b601c2fd (patch)
tree407a49b65d41ffaf50847717bcb51fa38fb55b61 /mysql-test
parenta6b327e90a8d7c7fe7f64cd2e3ed8535282ff7ff (diff)
downloadmariadb-git-ae53f684d3d02c1ef342891087fa0326b601c2fd.tar.gz
MDEV-30016 Virtual columns do not support autoincrement columns
change vcol_upgrade test to use stored gcols
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/std_data/vcol_autoinc.MYIbin1024 -> 1024 bytes
-rw-r--r--mysql-test/std_data/vcol_autoinc.frmbin951 -> 8587 bytes
-rw-r--r--mysql-test/suite/gcol/inc/gcol_column_def_options.inc17
-rw-r--r--mysql-test/suite/gcol/r/gcol_column_def_options_innodb.result18
-rw-r--r--mysql-test/suite/gcol/r/gcol_column_def_options_myisam.result18
-rw-r--r--mysql-test/suite/vcol/r/upgrade.result2
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
Binary files differ
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
Binary files differ
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;