diff options
Diffstat (limited to 'mysql-test/suite/vcol')
66 files changed, 215 insertions, 447 deletions
diff --git a/mysql-test/suite/vcol/inc/vcol_column_def_options.inc b/mysql-test/suite/vcol/inc/vcol_column_def_options.inc index 2f28136ad63..61eac0014bc 100644 --- a/mysql-test/suite/vcol/inc/vcol_column_def_options.inc +++ b/mysql-test/suite/vcol/inc/vcol_column_def_options.inc @@ -103,11 +103,11 @@ drop table t1; create table t2 (a int); -create table t1 (a int, b int as (a % 2) persistent references t2(a)); +create table t1 (a int, b int as (a % 2) persistent); show create table t1; drop table t1; create table t1 (a int, b int as (a % 2)); ---error ER_PARSE_ERROR -alter table t1 modify b int as (a % 2) persistent references t2(a); +--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN +alter table t1 modify b int as (a % 2) persistent; show create table t1; drop table t1; diff --git a/mysql-test/suite/vcol/inc/vcol_ins_upd.inc b/mysql-test/suite/vcol/inc/vcol_ins_upd.inc index 945884b3cd5..8cf0fb9ed6c 100644 --- a/mysql-test/suite/vcol/inc/vcol_ins_upd.inc +++ b/mysql-test/suite/vcol/inc/vcol_ins_upd.inc @@ -225,7 +225,7 @@ select * from t1; delete from t1; drop table t1; -let $innodb_engine = `SELECT @@session.storage_engine='innodb'`; +let $innodb_engine = `SELECT @@session.default_storage_engine='innodb'`; if ($innodb_engine) { --echo # diff --git a/mysql-test/suite/vcol/inc/vcol_select.inc b/mysql-test/suite/vcol/inc/vcol_select.inc index cbd1f2cdd26..56e1fc7ad16 100644 --- a/mysql-test/suite/vcol/inc/vcol_select.inc +++ b/mysql-test/suite/vcol/inc/vcol_select.inc @@ -146,7 +146,7 @@ eval explain $s; #### This bug is presumably in base SQL routines as the same happens #### with this table: #### create table t4 (a int primary key, b int, c int unique); -let $myisam_engine = `SELECT @@session.storage_engine='myisam'`; +let $myisam_engine = `SELECT @@session.default_storage_engine='myisam'`; if (!$myisam_engine) { --echo # SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <non-indexed vcol> @@ -171,7 +171,7 @@ eval explain $s; #### This bug is presumably in base SQL routines as the same happens #### with this table: #### create table t4 (a int primary key, b int, c int unique); -let $innodb_engine = `SELECT @@session.storage_engine='innodb'`; +let $innodb_engine = `SELECT @@session.default_storage_engine='innodb'`; if (!$innodb_engine) { --echo # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-vcol> diff --git a/mysql-test/suite/vcol/r/binlog.result b/mysql-test/suite/vcol/r/binlog.result index 463928b97b8..519877e9bc0 100644 --- a/mysql-test/suite/vcol/r/binlog.result +++ b/mysql-test/suite/vcol/r/binlog.result @@ -88,7 +88,7 @@ CREATE TEMPORARY TABLE t1 SELECT UUID(); show create table t1; Table Create Table t1 CREATE TEMPORARY TABLE `t1` ( - `UUID()` varchar(36) CHARACTER SET utf8 DEFAULT NULL + `UUID()` varchar(36) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE t2 (a INT PRIMARY KEY, b TEXT, c INT GENERATED ALWAYS AS(b)); INSERT INTO t2 (a,b) VALUES (1,1); diff --git a/mysql-test/suite/vcol/r/rpl_vcol.result b/mysql-test/suite/vcol/r/rpl_vcol.result index b3f764da22c..d95ca8b1f75 100644 --- a/mysql-test/suite/vcol/r/rpl_vcol.result +++ b/mysql-test/suite/vcol/r/rpl_vcol.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'InnoDB'; +SET @@session.default_storage_engine = 'InnoDB'; include/master-slave.inc [connection master] connection master; diff --git a/mysql-test/suite/vcol/r/vcol_archive.result b/mysql-test/suite/vcol/r/vcol_archive.result index ec743088ec2..5c11e8bb9e1 100644 --- a/mysql-test/suite/vcol/r/vcol_archive.result +++ b/mysql-test/suite/vcol/r/vcol_archive.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'archive'; +SET @@session.default_storage_engine = 'archive'; create table t1 (a int, b int as (a+1)); ERROR HY000: ARCHIVE storage engine does not support generated columns create table t1 (a int not null); diff --git a/mysql-test/suite/vcol/r/vcol_blackhole.result b/mysql-test/suite/vcol/r/vcol_blackhole.result index 52d40e9a2b8..0bbe439fcc5 100644 --- a/mysql-test/suite/vcol/r/vcol_blackhole.result +++ b/mysql-test/suite/vcol/r/vcol_blackhole.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'blackhole'; +SET @@session.default_storage_engine = 'blackhole'; create table t1 (a int, b int as (a+1)); ERROR HY000: BLACKHOLE storage engine does not support generated columns create table t1 (a int not null); diff --git a/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result b/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result index 19e8efb06ad..a0f33091297 100644 --- a/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result +++ b/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'MyISAM'; +SET @@session.default_storage_engine = 'MyISAM'; # RAND() create or replace table t1 (b double as (rand())); create or replace table t1 (b double as (rand()) PERSISTENT); diff --git a/mysql-test/suite/vcol/r/vcol_column_def_options_innodb.result b/mysql-test/suite/vcol/r/vcol_column_def_options_innodb.result index 0f80365f6a7..c0008f8f179 100644 --- a/mysql-test/suite/vcol/r/vcol_column_def_options_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_column_def_options_innodb.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'InnoDB'; +SET @@session.default_storage_engine = 'InnoDB'; # # Section 1. Wrong column definition options # - NOT NULL @@ -126,7 +126,7 @@ a b 2 0 drop table t1; create table t2 (a int); -create table t1 (a int, b int as (a % 2) persistent references t2(a)); +create table t1 (a int, b int as (a % 2) persistent); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -135,8 +135,8 @@ t1 CREATE TABLE `t1` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int, b int as (a % 2)); -alter table t1 modify b int as (a % 2) persistent references t2(a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'references t2(a)' at line 1 +alter table t1 modify b int as (a % 2) persistent; +ERROR HY000: This is not yet supported for generated columns show create table t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/mysql-test/suite/vcol/r/vcol_column_def_options_myisam.result b/mysql-test/suite/vcol/r/vcol_column_def_options_myisam.result index e53a7594663..0aea337b074 100644 --- a/mysql-test/suite/vcol/r/vcol_column_def_options_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_column_def_options_myisam.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'MyISAM'; +SET @@session.default_storage_engine = 'MyISAM'; # # Section 1. Wrong column definition options # - NOT NULL @@ -126,7 +126,7 @@ a b 2 0 drop table t1; create table t2 (a int); -create table t1 (a int, b int as (a % 2) persistent references t2(a)); +create table t1 (a int, b int as (a % 2) persistent); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -135,8 +135,8 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int, b int as (a % 2)); -alter table t1 modify b int as (a % 2) persistent references t2(a); -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'references t2(a)' at line 1 +alter table t1 modify b int as (a % 2) persistent; +ERROR HY000: This is not yet supported for generated columns show create table t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/mysql-test/suite/vcol/r/vcol_csv.result b/mysql-test/suite/vcol/r/vcol_csv.result index 9a0a76576f6..f8a0941c020 100644 --- a/mysql-test/suite/vcol/r/vcol_csv.result +++ b/mysql-test/suite/vcol/r/vcol_csv.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'CSV'; +SET @@session.default_storage_engine = 'CSV'; create table t1 (a int, b int as (a+1)); ERROR HY000: CSV storage engine does not support generated columns create table t1 (a int not null); diff --git a/mysql-test/suite/vcol/r/vcol_handler_aria.result b/mysql-test/suite/vcol/r/vcol_handler_aria.result index df36d0fbbf3..d2e6affc4b4 100644 --- a/mysql-test/suite/vcol/r/vcol_handler_aria.result +++ b/mysql-test/suite/vcol/r/vcol_handler_aria.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'maria'; +SET @@session.default_storage_engine = 'maria'; create table t1 (a int, b int as (-a), c int as (-a) persistent, diff --git a/mysql-test/suite/vcol/r/vcol_handler_innodb.result b/mysql-test/suite/vcol/r/vcol_handler_innodb.result index f7576299e9a..c3358acdbb7 100644 --- a/mysql-test/suite/vcol/r/vcol_handler_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_handler_innodb.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'InnoDB'; +SET @@session.default_storage_engine = 'InnoDB'; create table t1 (a int, b int as (-a), c int as (-a) persistent, diff --git a/mysql-test/suite/vcol/r/vcol_handler_myisam.result b/mysql-test/suite/vcol/r/vcol_handler_myisam.result index ff93756adc5..3596ab1f4ca 100644 --- a/mysql-test/suite/vcol/r/vcol_handler_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_handler_myisam.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'MyISAM'; +SET @@session.default_storage_engine = 'MyISAM'; create table t1 (a int, b int as (-a), c int as (-a) persistent, diff --git a/mysql-test/suite/vcol/r/vcol_ins_upd_innodb.result b/mysql-test/suite/vcol/r/vcol_ins_upd_innodb.result index 3629307a54d..6807f89fdbe 100644 --- a/mysql-test/suite/vcol/r/vcol_ins_upd_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_ins_upd_innodb.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'InnoDB'; +SET @@session.default_storage_engine = 'InnoDB'; create table t1 (a int, b int as (-a), c int as (-a) persistent); diff --git a/mysql-test/suite/vcol/r/vcol_ins_upd_myisam.result b/mysql-test/suite/vcol/r/vcol_ins_upd_myisam.result index 8eb59ca32ff..43206dba31b 100644 --- a/mysql-test/suite/vcol/r/vcol_ins_upd_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_ins_upd_myisam.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'MyISAM'; +SET @@session.default_storage_engine = 'MyISAM'; create table t1 (a int, b int as (-a), c int as (-a) persistent); diff --git a/mysql-test/suite/vcol/r/vcol_keys_innodb.result b/mysql-test/suite/vcol/r/vcol_keys_innodb.result index 9419c35bb38..759de6fbb23 100644 --- a/mysql-test/suite/vcol/r/vcol_keys_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_keys_innodb.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'InnoDB'; +SET @@session.default_storage_engine = 'InnoDB'; # - UNIQUE KEY # - INDEX # - FULLTEXT INDEX @@ -239,7 +239,7 @@ col_dec decimal(18,9) not null default 0, col_enum enum('','a','b','c','d','e','f','foo','bar') not null default '', col_date date not null default '1900-01-01', col_timestamp timestamp(3) not null default '1971-01-01 00:00:00', -vcol_datetime datetime as (col_datetime) virtual, +vcol_datetime datetime as (truncate(col_datetime,0)) virtual, vcol_dec decimal(18,9) zerofill as (col_dec) virtual, vcol_bit bit(63) as (col_bit) virtual, vcol_char binary(51) as (col_char) virtual, @@ -257,11 +257,6 @@ insert into t1 (col_varchar,col_int,col_datetime,col_time,col_blob,col_bit,col_y ('foo',1,'2010-05-08 13:08:12.034783','18:32:14','foo',b'0111110101001001',1992,'f',0.2,'','1994-12-26','2019-01-11 00:00:00'), ('bar',6,'1900-01-01 00:00:00','00:00:00','bar',b'10011000001101011000101',1985,'b',0.7,'','2028-04-06','1971-01-01 00:00:00'); alter table t1 add index(vcol_datetime); -Warnings: -Warning 1901 Function or expression '`col_datetime`' cannot be used in the GENERATED ALWAYS AS clause of `vcol_datetime` -Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL -Warning 1901 Function or expression '`col_datetime`' cannot be used in the GENERATED ALWAYS AS clause of `vcol_datetime` -Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL drop table t1; create table t1 ( pk int, diff --git a/mysql-test/suite/vcol/r/vcol_keys_myisam.result b/mysql-test/suite/vcol/r/vcol_keys_myisam.result index 803f7446810..1e6c854e0d9 100644 --- a/mysql-test/suite/vcol/r/vcol_keys_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_keys_myisam.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'MyISAM'; +SET @@session.default_storage_engine = 'MyISAM'; # - UNIQUE KEY # - INDEX # - FULLTEXT INDEX diff --git a/mysql-test/suite/vcol/r/vcol_memory.result b/mysql-test/suite/vcol/r/vcol_memory.result index 1324be82101..3284d97398e 100644 --- a/mysql-test/suite/vcol/r/vcol_memory.result +++ b/mysql-test/suite/vcol/r/vcol_memory.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'memory'; +SET @@session.default_storage_engine = 'memory'; create table t1 (a int, b int as (a+1)); ERROR HY000: MEMORY storage engine does not support generated columns create table t1 (a int not null); 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 84817469fa2..058c389e885 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 @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'InnoDB'; +SET @@session.default_storage_engine = 'InnoDB'; # Case 1. All non-stored columns. # This scenario is currently impossible due to the fact that virtual columns # with a constant expression are not allowed. 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 b4b41635110..bc1ba990596 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 @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'MyISAM'; +SET @@session.default_storage_engine = 'MyISAM'; # Case 1. All non-stored columns. # This scenario is currently impossible due to the fact that virtual columns # with a constant expression are not allowed. diff --git a/mysql-test/suite/vcol/r/vcol_partition_innodb.result b/mysql-test/suite/vcol/r/vcol_partition_innodb.result index 4c869a0a37d..c25fc7b2490 100644 --- a/mysql-test/suite/vcol/r/vcol_partition_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_partition_innodb.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'InnoDB'; +SET @@session.default_storage_engine = 'InnoDB'; drop table if exists t1; # Case 1. Partitioning by RANGE based on a non-stored virtual column. CREATE TABLE t1 ( diff --git a/mysql-test/suite/vcol/r/vcol_partition_myisam.result b/mysql-test/suite/vcol/r/vcol_partition_myisam.result index aeeaec2ac22..a7733ddf2d6 100644 --- a/mysql-test/suite/vcol/r/vcol_partition_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_partition_myisam.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'MyISAM'; +SET @@session.default_storage_engine = 'MyISAM'; drop table if exists t1; # Case 1. Partitioning by RANGE based on a non-stored virtual column. CREATE TABLE t1 ( diff --git a/mysql-test/suite/vcol/r/vcol_select_innodb.result b/mysql-test/suite/vcol/r/vcol_select_innodb.result index 0f6cdf918f9..e275a66091c 100644 --- a/mysql-test/suite/vcol/r/vcol_select_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_select_innodb.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'InnoDB'; +SET @@session.default_storage_engine = 'InnoDB'; create table t1 (a int, b int as (-a), c int as (-a) persistent, @@ -73,8 +73,8 @@ a b c 1 -1 -1 explain select * from t1 where c in (select c from t3 where c between -2 and -1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 6 Using where; Using index -1 PRIMARY t1 ALL c NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 range c c 5 NULL 2 Using where; Using index +1 PRIMARY t1 ref c c 5 test.t3.c 1 # select_type=UNION, type=system # select_type=UNION RESULT, type=<union1,2> select * from t1 union select * from t2; @@ -133,7 +133,7 @@ count(distinct c) 3 explain select count(distinct c) from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL c 5 NULL 6 Using index for group-by (scanning) +1 SIMPLE t1 range NULL c 5 NULL 6 Using index for group-by ### ### filesort & range-based utils ### diff --git a/mysql-test/suite/vcol/r/vcol_select_myisam.result b/mysql-test/suite/vcol/r/vcol_select_myisam.result index 017531f31eb..05f86347706 100644 --- a/mysql-test/suite/vcol/r/vcol_select_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_select_myisam.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'MyISAM'; +SET @@session.default_storage_engine = 'MyISAM'; create table t1 (a int, b int as (-a), c int as (-a) persistent, @@ -73,8 +73,8 @@ a b c 1 -1 -1 explain select * from t1 where c in (select c from t3 where c between -2 and -1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range c c 5 NULL 3 Using index condition -1 PRIMARY t3 eq_ref c c 5 test.t1.c 1 Using index +1 PRIMARY t3 range c c 5 NULL 2 Using where; Using index +1 PRIMARY t1 ref c c 5 test.t3.c 2 # select_type=UNION, type=system # select_type=UNION RESULT, type=<union1,2> select * from t1 union select * from t2; @@ -133,7 +133,7 @@ count(distinct c) 3 explain select count(distinct c) from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL c 5 NULL 6 Using index for group-by (scanning) +1 SIMPLE t1 range NULL c 5 NULL 6 Using index for group-by ### ### filesort & range-based utils ### diff --git a/mysql-test/suite/vcol/r/vcol_sql_mode.result b/mysql-test/suite/vcol/r/vcol_sql_mode.result index ecbafc87b84..ba75aeb0e6c 100644 --- a/mysql-test/suite/vcol/r/vcol_sql_mode.result +++ b/mysql-test/suite/vcol/r/vcol_sql_mode.result @@ -26,46 +26,31 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (a CHAR(5), v TIME AS (a) VIRTUAL, KEY(v)); -Warnings: -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL -DROP TABLE t1; +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` CREATE TABLE t1 (c CHAR(8), v BINARY(8) AS (c), KEY(v)); -Warnings: -Warning 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; CREATE TABLE t1 (a CHAR(5), v BIT(64) AS (a) VIRTUAL, KEY(v)); -Warnings: -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) VIRTUAL, KEY(v)); -Warnings: -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; CREATE TABLE t1 (a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(v(100))); -Warnings: -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; # PAD_CHAR_TO_FULL_LENGTH + TRIM resolving dependency CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) VIRTUAL, KEY(v)); SHOW CREATE TABLE t1; @@ -141,94 +126,67 @@ t1 CREATE TABLE `t1` ( DROP TABLE t1; # PAD_CHAR_TO_FULL_LENGTH + TRIM not resolving dependency CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v)); -Warnings: -Warning 1901 Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v(100))); -Warnings: -Warning 1901 Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING '' FROM a)) VIRTUAL, KEY(v)); -Warnings: -Warning 1901 Function or expression 'trim(trailing '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression 'trim(trailing '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression 'trim(trailing '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression 'trim(trailing '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH '' FROM a)) VIRTUAL, KEY(v)); -Warnings: -Warning 1901 Function or expression 'trim(both '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression 'trim(both '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression 'trim(both '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression 'trim(both '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING 'x' FROM a)) VIRTUAL, KEY(v)); -Warnings: -Warning 1901 Function or expression 'trim(trailing 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression 'trim(trailing 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression 'trim(trailing 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression 'trim(trailing 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH 'x' FROM a)) VIRTUAL, KEY(v)); -Warnings: -Warning 1901 Function or expression 'trim(both 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression 'trim(both 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression 'trim(both 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression 'trim(both 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; CREATE TABLE t1 ( a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v)); -Warnings: -Warning 1901 Function or expression 'trim(trailing ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression 'trim(trailing ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression 'trim(trailing ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression 'trim(trailing ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; CREATE TABLE t1 ( a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v)); -Warnings: -Warning 1901 Function or expression 'trim(both ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression 'trim(both ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression 'trim(both ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression 'trim(both ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; # PAD_CHAR_TO_FULL_LENGTH + TRIM(... non_constant FROM a) CREATE TABLE t1 ( a CHAR(5), b CHAR(5), v TEXT AS (TRIM(TRAILING b FROM a)) VIRTUAL, KEY(v(100))); -Warnings: -Warning 1901 Function or expression 'trim(trailing `b` from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression 'trim(trailing `b` from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression 'trim(trailing `b` from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression 'trim(trailing `b` from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; # PAD_CHAR_TO_FULL_LENGTH + RPAD resolving dependency CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,5,' ')) VIRTUAL, KEY(v)); SHOW CREATE TABLE t1; @@ -268,28 +226,22 @@ t1 CREATE TABLE `t1` ( DROP TABLE t1; # PAD_CHAR_TO_FULL_LENGTH + RPAD not resolving dependency CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,4,' ')) VIRTUAL, KEY(v)); -Warnings: -Warning 1901 Function or expression 'rpad(`a`,4,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression 'rpad(`a`,4,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression 'rpad(`a`,4,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression 'rpad(`a`,4,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; CREATE TABLE t1 ( a CHAR(5), b CHAR(5), v VARCHAR(5) AS (RPAD(a,NULL,b)) VIRTUAL, KEY(v) ); -Warnings: -Warning 1901 Function or expression 'rpad(`a`,NULL,`b`)' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression 'rpad(`a`,NULL,`b`)' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression 'rpad(`a`,NULL,`b`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression 'rpad(`a`,NULL,`b`)' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; # PAD_CHAR_TO_FULL_LENGTH + comparison CREATE TABLE t1 (a CHAR(5), v INT AS (a='a') VIRTUAL, KEY(v)); SHOW CREATE TABLE t1; @@ -304,14 +256,11 @@ CREATE TABLE t1 ( a CHAR(5) CHARACTER SET latin1 COLLATE latin1_nopad_bin, v INT AS (a='a') VIRTUAL, KEY(v) ); -Warnings: -Warning 1901 Function or expression '`a` = 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression '`a` = 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression '`a` = 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression '`a` = 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; # PAD_CHAR_TO_FULL_LENGTH + LIKE CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a%') VIRTUAL, KEY(v)); SHOW CREATE TABLE t1; @@ -332,24 +281,18 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a') VIRTUAL, KEY(v)); -Warnings: -Warning 1901 Function or expression '`a` like 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression '`a` like 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression '`a` like 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression '`a` like 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; # PAD_CHAR_TO_FULL_LENGTH + LENGTH(char_column) = hard dependency CREATE TABLE t1 (a CHAR(5), v INT AS (LENGTH(a)) VIRTUAL, KEY(v)); -Warnings: -Warning 1901 Function or expression 'octet_length(`a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression 'octet_length(`a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression 'octet_length(`a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression 'octet_length(`a`)' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; # # Testing NO_UNSIGNED_SUBTRACTION # @@ -359,42 +302,33 @@ b INT UNSIGNED, c INT GENERATED ALWAYS AS (a-b) VIRTUAL, KEY (c) ); -Warnings: -Warning 1901 Function or expression '`a` - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c` -Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION +ERROR HY000: Function or expression '`a` - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression '`a` - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c` +Error 1901 Function or expression '`a` - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c` Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION -DROP TABLE t1; CREATE TABLE t1 ( a INT UNSIGNED, b INT UNSIGNED, c INT GENERATED ALWAYS AS (CAST(a AS SIGNED)-b) VIRTUAL, KEY (c) ); -Warnings: -Warning 1901 Function or expression 'cast(`a` as signed) - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c` -Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION +ERROR HY000: Function or expression 'cast(`a` as signed) - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression 'cast(`a` as signed) - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c` +Error 1901 Function or expression 'cast(`a` as signed) - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c` Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION -DROP TABLE t1; CREATE TABLE t1 ( a INT UNSIGNED, b INT UNSIGNED, c INT GENERATED ALWAYS AS (a-CAST(b AS SIGNED)) VIRTUAL, KEY (c) ); -Warnings: -Warning 1901 Function or expression '`a` - cast(`b` as signed)' cannot be used in the GENERATED ALWAYS AS clause of `c` -Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION +ERROR HY000: Function or expression '`a` - cast(`b` as signed)' cannot be used in the GENERATED ALWAYS AS clause of `c` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression '`a` - cast(`b` as signed)' cannot be used in the GENERATED ALWAYS AS clause of `c` +Error 1901 Function or expression '`a` - cast(`b` as signed)' cannot be used in the GENERATED ALWAYS AS clause of `c` Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION -DROP TABLE t1; CREATE TABLE t1 ( a INT UNSIGNED, b INT UNSIGNED, @@ -435,16 +369,12 @@ c CHAR(5), v VARCHAR(5) GENERATED ALWAYS AS (RPAD(c,a-b,' ')) VIRTUAL, KEY (v) ); -Warnings: -Warning 1901 Function or expression 'rpad(`c`,`a` - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression 'rpad(`c`,`a` - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression 'rpad(`c`,`a` - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression 'rpad(`c`,`a` - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; CREATE TABLE t1 ( a INT UNSIGNED, b INT UNSIGNED, @@ -452,14 +382,11 @@ c CHAR(5), v VARCHAR(5) GENERATED ALWAYS AS (RPAD(c,CAST(a AS DECIMAL(20,1))-b,' ')) VIRTUAL, KEY (v) ); -Warnings: -Warning 1901 Function or expression 'rpad(`c`,cast(`a` as decimal(20,1)) - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression 'rpad(`c`,cast(`a` as decimal(20,1)) - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression 'rpad(`c`,cast(`a` as decimal(20,1)) - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression 'rpad(`c`,cast(`a` as decimal(20,1)) - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; # ALTER TABLE ADD KEY(vcol_depending_on_sql_mode) --> error CREATE TABLE t1 ( a INT UNSIGNED, @@ -468,62 +395,32 @@ c CHAR(5), v VARCHAR(5) GENERATED ALWAYS AS (c) VIRTUAL ); ALTER TABLE t1 ADD KEY(v); -Warnings: -Warning 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -ALTER TABLE t1 DROP KEY v; -Warnings: -Warning 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH CREATE INDEX v ON t1 (v); -Warnings: -Warning 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH DROP TABLE t1; # A virtual column on the second position in an index - cannot depend on sql_mode CREATE TABLE t1 (id int, a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(id, v(100))); -Warnings: -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; # A persisten virtual column cannot depend on sql_mode CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) PERSISTENT); -Warnings: -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW WARNINGS; Level Code Message -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t1; CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) PERSISTENT); SHOW CREATE TABLE t1; Table Create Table diff --git a/mysql-test/suite/vcol/r/vcol_sql_mode_datetime.result b/mysql-test/suite/vcol/r/vcol_sql_mode_datetime.result index 151b95095a8..3033ffb66b4 100644 --- a/mysql-test/suite/vcol/r/vcol_sql_mode_datetime.result +++ b/mysql-test/suite/vcol/r/vcol_sql_mode_datetime.result @@ -40,30 +40,21 @@ d DATETIME, v DATETIME(3) AS (t) VIRTUAL, KEY(v,d) ); -Warnings: -Warning 1901 Function or expression '`t`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL -DROP TABLE t1; +ERROR HY000: Function or expression '`t`' cannot be used in the GENERATED ALWAYS AS clause of `v` CREATE TABLE t1 ( t DATETIME(4), d DATETIME, v DATETIME(3) AS (COALESCE(t)) VIRTUAL, KEY(v,d) ); -Warnings: -Warning 1901 Function or expression 'coalesce(`t`)' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL -DROP TABLE t1; +ERROR HY000: Function or expression 'coalesce(`t`)' cannot be used in the GENERATED ALWAYS AS clause of `v` CREATE TABLE t1 ( t DATETIME(4), d DATETIME, v DATETIME(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL, KEY(v,d) ); -Warnings: -Warning 1901 Function or expression ''2001-01-01 10:20:30.1234'' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL -DROP TABLE t1; +ERROR HY000: Function or expression ''2001-01-01 10:20:30.1234'' cannot be used in the GENERATED ALWAYS AS clause of `v` # OK: lower FSP + ROUND + virtual index SET sql_mode=DEFAULT; CREATE TABLE t1 ( @@ -104,10 +95,7 @@ a DATETIME(6), v VARCHAR(30) GENERATED ALWAYS AS (CAST(a AS DATETIME(3))) VIRTUAL, KEY (v) ); -Warnings: -Warning 1901 Function or expression 'cast(`a` as datetime(3))' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL -DROP TABLE t1; +ERROR HY000: Function or expression 'cast(`a` as datetime(3))' cannot be used in the GENERATED ALWAYS AS clause of `v` CREATE TABLE t1 ( a DATETIME(6), v VARCHAR(30) GENERATED ALWAYS AS (TRUNCATE(a,3)) VIRTUAL, diff --git a/mysql-test/suite/vcol/r/vcol_sql_mode_time.result b/mysql-test/suite/vcol/r/vcol_sql_mode_time.result index adf650fff9f..300b91726d1 100644 --- a/mysql-test/suite/vcol/r/vcol_sql_mode_time.result +++ b/mysql-test/suite/vcol/r/vcol_sql_mode_time.result @@ -40,30 +40,21 @@ d TIME, v TIME(3) AS (t) VIRTUAL, KEY(v,d) ); -Warnings: -Warning 1901 Function or expression '`t`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL -DROP TABLE t1; +ERROR HY000: Function or expression '`t`' cannot be used in the GENERATED ALWAYS AS clause of `v` CREATE TABLE t1 ( t TIME(4), d TIME, v TIME(3) AS (COALESCE(t)) VIRTUAL, KEY(v,d) ); -Warnings: -Warning 1901 Function or expression 'coalesce(`t`)' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL -DROP TABLE t1; +ERROR HY000: Function or expression 'coalesce(`t`)' cannot be used in the GENERATED ALWAYS AS clause of `v` CREATE TABLE t1 ( t TIME(4), d TIME, v TIME(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL, KEY(v,d) ); -Warnings: -Warning 1901 Function or expression ''2001-01-01 10:20:30.1234'' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL -DROP TABLE t1; +ERROR HY000: Function or expression ''2001-01-01 10:20:30.1234'' cannot be used in the GENERATED ALWAYS AS clause of `v` # OK: lower FSP + ROUND + virtual index SET sql_mode=DEFAULT; CREATE TABLE t1 ( @@ -104,10 +95,7 @@ a TIME(6), v VARCHAR(30) GENERATED ALWAYS AS (CAST(a AS TIME(3))) VIRTUAL, KEY (v) ); -Warnings: -Warning 1901 Function or expression 'cast(`a` as time(3))' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL -DROP TABLE t1; +ERROR HY000: Function or expression 'cast(`a` as time(3))' cannot be used in the GENERATED ALWAYS AS clause of `v` CREATE TABLE t1 ( a TIME(6), v VARCHAR(30) GENERATED ALWAYS AS (TRUNCATE(a,3)) VIRTUAL, diff --git a/mysql-test/suite/vcol/r/vcol_sql_mode_timestamp.result b/mysql-test/suite/vcol/r/vcol_sql_mode_timestamp.result index 7c82856717d..df1cee04542 100644 --- a/mysql-test/suite/vcol/r/vcol_sql_mode_timestamp.result +++ b/mysql-test/suite/vcol/r/vcol_sql_mode_timestamp.result @@ -40,30 +40,21 @@ d DATETIME, v TIMESTAMP(3) AS (t) VIRTUAL, KEY(v,d) ); -Warnings: -Warning 1901 Function or expression '`t`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL -DROP TABLE t1; +ERROR HY000: Function or expression '`t`' cannot be used in the GENERATED ALWAYS AS clause of `v` CREATE TABLE t1 ( t TIMESTAMP(4), d DATETIME, v TIMESTAMP(3) AS (COALESCE(t)) VIRTUAL, KEY(v,d) ); -Warnings: -Warning 1901 Function or expression 'coalesce(`t`)' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL -DROP TABLE t1; +ERROR HY000: Function or expression 'coalesce(`t`)' cannot be used in the GENERATED ALWAYS AS clause of `v` CREATE TABLE t1 ( t TIMESTAMP(4), d DATETIME, v TIMESTAMP(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL, KEY(v,d) ); -Warnings: -Warning 1901 Function or expression ''2001-01-01 10:20:30.1234'' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL -DROP TABLE t1; +ERROR HY000: Function or expression ''2001-01-01 10:20:30.1234'' cannot be used in the GENERATED ALWAYS AS clause of `v` # OK: lower FSP + ROUND + virtual index SET sql_mode=DEFAULT; CREATE TABLE t1 ( @@ -105,12 +96,10 @@ b TIMESTAMP AS (a) VIRTUAL, KEY (b) ); ALTER TABLE t1 MODIFY a BLOB FIRST; -Warnings: -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `b` -Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `b` -Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `b` +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `b` +SHOW WARNINGS; +Level Code Message +Error 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `b` Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL DROP TABLE t1; # diff --git a/mysql-test/suite/vcol/r/vcol_sql_mode_upgrade.result b/mysql-test/suite/vcol/r/vcol_sql_mode_upgrade.result index fe1cb7b5779..d3954770c7b 100644 --- a/mysql-test/suite/vcol/r/vcol_sql_mode_upgrade.result +++ b/mysql-test/suite/vcol/r/vcol_sql_mode_upgrade.result @@ -52,66 +52,40 @@ Warnings: Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH CREATE TABLE t2 LIKE t1; -Warnings: -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t2; +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` FLUSH TABLES; CREATE TABLE t2 LIKE t1; -Warnings: -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t2; +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(5) DEFAULT NULL, `v` varchar(5) GENERATED ALWAYS AS (`a`) STORED ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -ALTER TABLE t1 ADD b INT DEFAULT a; Warnings: Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ALTER TABLE t1 ADD b INT DEFAULT a; +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(5) DEFAULT NULL, - `v` varchar(5) GENERATED ALWAYS AS (`a`) STORED, - `b` int(11) DEFAULT `a` + `v` varchar(5) GENERATED ALWAYS AS (`a`) STORED ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -Warnings: -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH SELECT * FROM t1; -a v b -1 1 1 -2 2 2 -3 3 3 +a v +1 1 +2 2 +3 3 FLUSH TABLES; ALTER TABLE t1 ADD c INT DEFAULT a; -Warnings: -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` SELECT * FROM t1; -a v b c -1 1 1 1 -2 2 2 2 -3 3 3 3 -Warnings: -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +a v +1 1 +2 2 +3 3 DROP TABLE t1; # # Fixing a Maria-10.2.26 table with a stored VARCHAR column @@ -203,18 +177,10 @@ Warnings: Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH CREATE TABLE t2 LIKE t1; -Warnings: -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t2; +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` FLUSH TABLES; CREATE TABLE t2 LIKE t1; -Warnings: -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -DROP TABLE t2; +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -222,33 +188,19 @@ t1 CREATE TABLE `t1` ( `v` varchar(5) GENERATED ALWAYS AS (`a`) VIRTUAL, KEY `v` (`v`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -ALTER TABLE t1 ADD b INT DEFAULT a; Warnings: Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ALTER TABLE t1 ADD b INT DEFAULT a; +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` FLUSH TABLES; ALTER TABLE t1 ADD c INT DEFAULT a; -Warnings: -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` SELECT * FROM t1; -a v b c -1 1 1 1 -2 2 2 2 -3 3 3 3 -Warnings: -Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` -Warning 1105 Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH +a v +1 1 +2 2 +3 3 DROP TABLE t1; # # Fixing a Maria-10.2.26 table with a virtual VARCHAR column diff --git a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result index 10ab32f9486..587ac9c150d 100644 --- a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result +++ b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'MyISAM'; +SET @@session.default_storage_engine = 'MyISAM'; # # NUMERIC FUNCTIONS # diff --git a/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result b/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result index 403515482f0..c4f6e0c5125 100644 --- a/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'InnoDB'; +SET @@session.default_storage_engine = 'InnoDB'; create table t1 (a int, b int as (a/10), c int as (a/10) persistent); diff --git a/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result b/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result index de605bf8722..467e7f8bb4e 100644 --- a/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'MyISAM'; +SET @@session.default_storage_engine = 'MyISAM'; create table t1 (a int, b int as (a/10), c int as (a/10) persistent); diff --git a/mysql-test/suite/vcol/r/vcol_view_innodb.result b/mysql-test/suite/vcol/r/vcol_view_innodb.result index b5d722f4d58..e6895243371 100644 --- a/mysql-test/suite/vcol/r/vcol_view_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_view_innodb.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'InnoDB'; +SET @@session.default_storage_engine = 'InnoDB'; create table t1 (a int not null, b int as (-a), c int as (-a) persistent); diff --git a/mysql-test/suite/vcol/r/vcol_view_myisam.result b/mysql-test/suite/vcol/r/vcol_view_myisam.result index 8f86774c418..9c0dcba2128 100644 --- a/mysql-test/suite/vcol/r/vcol_view_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_view_myisam.result @@ -1,4 +1,4 @@ -SET @@session.storage_engine = 'MyISAM'; +SET @@session.default_storage_engine = 'MyISAM'; create table t1 (a int not null, b int as (-a), c int as (-a) persistent); diff --git a/mysql-test/suite/vcol/t/rpl_vcol.test b/mysql-test/suite/vcol/t/rpl_vcol.test index 03837df1b99..d893ebac855 100644 --- a/mysql-test/suite/vcol/t/rpl_vcol.test +++ b/mysql-test/suite/vcol/t/rpl_vcol.test @@ -28,7 +28,7 @@ ##### Storage engine to be tested # Set the session storage engine --source include/have_innodb.inc -SET @@session.storage_engine = 'InnoDB'; +SET @@session.default_storage_engine = 'InnoDB'; #------------------------------------------------------------------------------# # Cleanup diff --git a/mysql-test/suite/vcol/t/vcol_archive.test b/mysql-test/suite/vcol/t/vcol_archive.test index c0c9ecc445d..b708b3a451a 100644 --- a/mysql-test/suite/vcol/t/vcol_archive.test +++ b/mysql-test/suite/vcol/t/vcol_archive.test @@ -32,7 +32,7 @@ ##### Storage engine to be tested # Set the session storage engine --source include/have_archive.inc -SET @@session.storage_engine = 'archive'; +SET @@session.default_storage_engine = 'archive'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_blackhole.test b/mysql-test/suite/vcol/t/vcol_blackhole.test index eac074d9595..6cbc68a994f 100644 --- a/mysql-test/suite/vcol/t/vcol_blackhole.test +++ b/mysql-test/suite/vcol/t/vcol_blackhole.test @@ -32,7 +32,7 @@ ##### Storage engine to be tested # Set the session storage engine --source include/have_blackhole.inc -SET @@session.storage_engine = 'blackhole'; +SET @@session.default_storage_engine = 'blackhole'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_blocked_sql_funcs.test b/mysql-test/suite/vcol/t/vcol_blocked_sql_funcs.test index 3e9e5936476..d00ab8276e7 100644 --- a/mysql-test/suite/vcol/t/vcol_blocked_sql_funcs.test +++ b/mysql-test/suite/vcol/t/vcol_blocked_sql_funcs.test @@ -32,7 +32,7 @@ ##### Storage engine to be tested # Set the session storage engine -eval SET @@session.storage_engine = 'MyISAM'; +eval SET @@session.default_storage_engine = 'MyISAM'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_column_def_options_innodb.test b/mysql-test/suite/vcol/t/vcol_column_def_options_innodb.test index 38baa2b3024..6fb6a5040b1 100644 --- a/mysql-test/suite/vcol/t/vcol_column_def_options_innodb.test +++ b/mysql-test/suite/vcol/t/vcol_column_def_options_innodb.test @@ -34,7 +34,7 @@ ##### Storage engine to be tested # Set the session storage engine --source include/have_innodb.inc -eval SET @@session.storage_engine = 'InnoDB'; +eval SET @@session.default_storage_engine = 'InnoDB'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_column_def_options_myisam.test b/mysql-test/suite/vcol/t/vcol_column_def_options_myisam.test index 5c3a4619834..63081429ada 100644 --- a/mysql-test/suite/vcol/t/vcol_column_def_options_myisam.test +++ b/mysql-test/suite/vcol/t/vcol_column_def_options_myisam.test @@ -33,7 +33,7 @@ ##### Storage engine to be tested # Set the session storage engine -eval SET @@session.storage_engine = 'MyISAM'; +eval SET @@session.default_storage_engine = 'MyISAM'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_csv.test b/mysql-test/suite/vcol/t/vcol_csv.test index 75ddf819818..0c218df6388 100644 --- a/mysql-test/suite/vcol/t/vcol_csv.test +++ b/mysql-test/suite/vcol/t/vcol_csv.test @@ -32,7 +32,7 @@ ##### Storage engine to be tested # Set the session storage engine --source include/have_csv.inc -SET @@session.storage_engine = 'CSV'; +SET @@session.default_storage_engine = 'CSV'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_handler_aria.test b/mysql-test/suite/vcol/t/vcol_handler_aria.test index 9b69e600767..0787321e295 100644 --- a/mysql-test/suite/vcol/t/vcol_handler_aria.test +++ b/mysql-test/suite/vcol/t/vcol_handler_aria.test @@ -35,7 +35,7 @@ ##### Storage engine to be tested # Set the session storage engine -eval SET @@session.storage_engine = 'maria'; +eval SET @@session.default_storage_engine = 'maria'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_handler_innodb.test b/mysql-test/suite/vcol/t/vcol_handler_innodb.test index bf443c6bbd3..c86cbd0daae 100644 --- a/mysql-test/suite/vcol/t/vcol_handler_innodb.test +++ b/mysql-test/suite/vcol/t/vcol_handler_innodb.test @@ -34,7 +34,7 @@ ##### Storage engine to be tested # Set the session storage engine --source include/have_innodb.inc -eval SET @@session.storage_engine = 'InnoDB'; +eval SET @@session.default_storage_engine = 'InnoDB'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_handler_myisam.test b/mysql-test/suite/vcol/t/vcol_handler_myisam.test index 5aa16da180a..424752d9d23 100644 --- a/mysql-test/suite/vcol/t/vcol_handler_myisam.test +++ b/mysql-test/suite/vcol/t/vcol_handler_myisam.test @@ -33,7 +33,7 @@ ##### Storage engine to be tested # Set the session storage engine -eval SET @@session.storage_engine = 'MyISAM'; +eval SET @@session.default_storage_engine = 'MyISAM'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_ins_upd_innodb.test b/mysql-test/suite/vcol/t/vcol_ins_upd_innodb.test index 5d9ac12e930..fd1af0abd9e 100644 --- a/mysql-test/suite/vcol/t/vcol_ins_upd_innodb.test +++ b/mysql-test/suite/vcol/t/vcol_ins_upd_innodb.test @@ -34,7 +34,7 @@ ##### Storage engine to be tested # Set the session storage engine --source include/have_innodb.inc -eval SET @@session.storage_engine = 'InnoDB'; +eval SET @@session.default_storage_engine = 'InnoDB'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_ins_upd_myisam.test b/mysql-test/suite/vcol/t/vcol_ins_upd_myisam.test index 7840c191194..d73d0aa613c 100644 --- a/mysql-test/suite/vcol/t/vcol_ins_upd_myisam.test +++ b/mysql-test/suite/vcol/t/vcol_ins_upd_myisam.test @@ -33,7 +33,7 @@ ##### Storage engine to be tested # Set the session storage engine -eval SET @@session.storage_engine = 'MyISAM'; +eval SET @@session.default_storage_engine = 'MyISAM'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_keys_innodb.test b/mysql-test/suite/vcol/t/vcol_keys_innodb.test index c475dd71a69..488a2b936d0 100644 --- a/mysql-test/suite/vcol/t/vcol_keys_innodb.test +++ b/mysql-test/suite/vcol/t/vcol_keys_innodb.test @@ -34,7 +34,7 @@ ##### Storage engine to be tested # Set the session storage engine --source include/have_innodb.inc -SET @@session.storage_engine = 'InnoDB'; +SET @@session.default_storage_engine = 'InnoDB'; ##### Workarounds for known open engine specific bugs # none @@ -80,7 +80,7 @@ create table t1 ( col_enum enum('','a','b','c','d','e','f','foo','bar') not null default '', col_date date not null default '1900-01-01', col_timestamp timestamp(3) not null default '1971-01-01 00:00:00', - vcol_datetime datetime as (col_datetime) virtual, + vcol_datetime datetime as (truncate(col_datetime,0)) virtual, vcol_dec decimal(18,9) zerofill as (col_dec) virtual, vcol_bit bit(63) as (col_bit) virtual, vcol_char binary(51) as (col_char) virtual, diff --git a/mysql-test/suite/vcol/t/vcol_keys_myisam.test b/mysql-test/suite/vcol/t/vcol_keys_myisam.test index 6c3a94d2086..b8cbf9ef705 100644 --- a/mysql-test/suite/vcol/t/vcol_keys_myisam.test +++ b/mysql-test/suite/vcol/t/vcol_keys_myisam.test @@ -35,7 +35,7 @@ ##### Storage engine to be tested # Set the session storage engine -SET @@session.storage_engine = 'MyISAM'; +SET @@session.default_storage_engine = 'MyISAM'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_memory.test b/mysql-test/suite/vcol/t/vcol_memory.test index f481427fa93..b78850ede89 100644 --- a/mysql-test/suite/vcol/t/vcol_memory.test +++ b/mysql-test/suite/vcol/t/vcol_memory.test @@ -31,7 +31,7 @@ ##### Storage engine to be tested # Set the session storage engine -SET @@session.storage_engine = 'memory'; +SET @@session.default_storage_engine = 'memory'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_non_stored_columns_innodb.test b/mysql-test/suite/vcol/t/vcol_non_stored_columns_innodb.test index 88ed6157294..8f7a467136d 100644 --- a/mysql-test/suite/vcol/t/vcol_non_stored_columns_innodb.test +++ b/mysql-test/suite/vcol/t/vcol_non_stored_columns_innodb.test @@ -36,7 +36,7 @@ ##### Storage engine to be tested # Set the session storage engine --source include/have_innodb.inc -eval SET @@session.storage_engine = 'InnoDB'; +eval SET @@session.default_storage_engine = 'InnoDB'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_non_stored_columns_myisam.test b/mysql-test/suite/vcol/t/vcol_non_stored_columns_myisam.test index 32481e792ec..74c11ba8b7d 100644 --- a/mysql-test/suite/vcol/t/vcol_non_stored_columns_myisam.test +++ b/mysql-test/suite/vcol/t/vcol_non_stored_columns_myisam.test @@ -35,7 +35,7 @@ ##### Storage engine to be tested # Set the session storage engine -eval SET @@session.storage_engine = 'MyISAM'; +eval SET @@session.default_storage_engine = 'MyISAM'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_partition_innodb.test b/mysql-test/suite/vcol/t/vcol_partition_innodb.test index 7790a82800c..7b8e8de25d4 100644 --- a/mysql-test/suite/vcol/t/vcol_partition_innodb.test +++ b/mysql-test/suite/vcol/t/vcol_partition_innodb.test @@ -34,7 +34,7 @@ ##### Storage engine to be tested # Set the session storage engine --source include/have_innodb.inc -eval SET @@session.storage_engine = 'InnoDB'; +eval SET @@session.default_storage_engine = 'InnoDB'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_partition_myisam.test b/mysql-test/suite/vcol/t/vcol_partition_myisam.test index 25f0d42c300..b41a2482fac 100644 --- a/mysql-test/suite/vcol/t/vcol_partition_myisam.test +++ b/mysql-test/suite/vcol/t/vcol_partition_myisam.test @@ -34,7 +34,7 @@ ##### Storage engine to be tested # Set the session storage engine -eval SET @@session.storage_engine = 'MyISAM'; +eval SET @@session.default_storage_engine = 'MyISAM'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_select_innodb.test b/mysql-test/suite/vcol/t/vcol_select_innodb.test index 8b85d04bfb5..606801a213a 100644 --- a/mysql-test/suite/vcol/t/vcol_select_innodb.test +++ b/mysql-test/suite/vcol/t/vcol_select_innodb.test @@ -35,7 +35,7 @@ ##### Storage engine to be tested # Set the session storage engine --source include/have_innodb.inc -eval SET @@session.storage_engine = 'InnoDB'; +eval SET @@session.default_storage_engine = 'InnoDB'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_select_myisam.test b/mysql-test/suite/vcol/t/vcol_select_myisam.test index d60288d45ed..1e1adf29b3c 100644 --- a/mysql-test/suite/vcol/t/vcol_select_myisam.test +++ b/mysql-test/suite/vcol/t/vcol_select_myisam.test @@ -34,7 +34,7 @@ ##### Storage engine to be tested # Set the session storage engine -eval SET @@session.storage_engine = 'MyISAM'; +eval SET @@session.default_storage_engine = 'MyISAM'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_sql_mode.test b/mysql-test/suite/vcol/t/vcol_sql_mode.test index 44d3686ac01..f52345c7ffb 100644 --- a/mysql-test/suite/vcol/t/vcol_sql_mode.test +++ b/mysql-test/suite/vcol/t/vcol_sql_mode.test @@ -18,28 +18,24 @@ CREATE TABLE t1 (a CHAR(5), v INT AS (a) VIRTUAL, KEY(v)); SHOW CREATE TABLE t1; DROP TABLE t1; +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v TIME AS (a) VIRTUAL, KEY(v)); -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (c CHAR(8), v BINARY(8) AS (c), KEY(v)); SHOW WARNINGS; -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v BIT(64) AS (a) VIRTUAL, KEY(v)); SHOW WARNINGS; -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) VIRTUAL, KEY(v)); SHOW WARNINGS; -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(v(100))); SHOW WARNINGS; -DROP TABLE t1; --echo # PAD_CHAR_TO_FULL_LENGTH + TRIM resolving dependency @@ -79,61 +75,52 @@ DROP TABLE t1; --echo # PAD_CHAR_TO_FULL_LENGTH + TRIM not resolving dependency -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v)); SHOW WARNINGS; -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v(100))); SHOW WARNINGS; -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING '' FROM a)) VIRTUAL, KEY(v)); SHOW WARNINGS; -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH '' FROM a)) VIRTUAL, KEY(v)); SHOW WARNINGS; -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING 'x' FROM a)) VIRTUAL, KEY(v)); SHOW WARNINGS; -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH 'x' FROM a)) VIRTUAL, KEY(v)); SHOW WARNINGS; -DROP TABLE t1; # more than one space -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v)); SHOW WARNINGS; -DROP TABLE t1; # more than one space -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v)); SHOW WARNINGS; -DROP TABLE t1; --echo # PAD_CHAR_TO_FULL_LENGTH + TRIM(... non_constant FROM a) -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a CHAR(5), b CHAR(5), v TEXT AS (TRIM(TRAILING b FROM a)) VIRTUAL, KEY(v(100))); SHOW WARNINGS; -DROP TABLE t1; --echo # PAD_CHAR_TO_FULL_LENGTH + RPAD resolving dependency @@ -157,12 +144,11 @@ DROP TABLE t1; --echo # PAD_CHAR_TO_FULL_LENGTH + RPAD not resolving dependency -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,4,' ')) VIRTUAL, KEY(v)); SHOW WARNINGS; -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a CHAR(5), b CHAR(5), @@ -170,7 +156,6 @@ CREATE TABLE t1 ( KEY(v) ); SHOW WARNINGS; -DROP TABLE t1; --echo # PAD_CHAR_TO_FULL_LENGTH + comparison @@ -179,13 +164,12 @@ CREATE TABLE t1 (a CHAR(5), v INT AS (a='a') VIRTUAL, KEY(v)); SHOW CREATE TABLE t1; DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a CHAR(5) CHARACTER SET latin1 COLLATE latin1_nopad_bin, v INT AS (a='a') VIRTUAL, KEY(v) ); SHOW WARNINGS; -DROP TABLE t1; --echo # PAD_CHAR_TO_FULL_LENGTH + LIKE @@ -198,25 +182,23 @@ CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE NULL) VIRTUAL, KEY(v)); SHOW CREATE TABLE t1; DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a') VIRTUAL, KEY(v)); SHOW WARNINGS; -DROP TABLE t1; --echo # PAD_CHAR_TO_FULL_LENGTH + LENGTH(char_column) = hard dependency -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v INT AS (LENGTH(a)) VIRTUAL, KEY(v)); SHOW WARNINGS; -DROP TABLE t1; --echo # --echo # Testing NO_UNSIGNED_SUBTRACTION --echo # -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a INT UNSIGNED, b INT UNSIGNED, @@ -224,9 +206,8 @@ CREATE TABLE t1 ( KEY (c) ); SHOW WARNINGS; -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a INT UNSIGNED, b INT UNSIGNED, @@ -234,9 +215,8 @@ CREATE TABLE t1 ( KEY (c) ); SHOW WARNINGS; -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a INT UNSIGNED, b INT UNSIGNED, @@ -244,7 +224,6 @@ CREATE TABLE t1 ( KEY (c) ); SHOW WARNINGS; -DROP TABLE t1; CREATE TABLE t1 ( a INT UNSIGNED, @@ -271,7 +250,7 @@ DROP TABLE t1; --echo # Comnination: PAD_CHAR_TO_FULL_LENGTH + NO_UNSIGNED_SUBTRACTION --echo # -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a INT UNSIGNED, b INT UNSIGNED, @@ -280,14 +259,13 @@ CREATE TABLE t1 ( KEY (v) ); SHOW WARNINGS; -DROP TABLE t1; # The below solves the dependency on NO_UNSIGNED_SUBTRACTION # but does not solve the dependency on PAD_CHAR_TO_FULL_LENGTH, # because the 'length' argument to RPAD() is not a constant. -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a INT UNSIGNED, b INT UNSIGNED, @@ -296,7 +274,6 @@ CREATE TABLE t1 ( KEY (v) ); SHOW WARNINGS; -DROP TABLE t1; @@ -314,12 +291,10 @@ CREATE TABLE t1 ( c CHAR(5), v VARCHAR(5) GENERATED ALWAYS AS (c) VIRTUAL ); -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED ALTER TABLE t1 ADD KEY(v); SHOW WARNINGS; -ALTER TABLE t1 DROP KEY v; - -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE INDEX v ON t1 (v); SHOW WARNINGS; DROP TABLE t1; @@ -327,18 +302,16 @@ DROP TABLE t1; --echo # A virtual column on the second position in an index - cannot depend on sql_mode -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (id int, a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(id, v(100))); SHOW WARNINGS; -DROP TABLE t1; --echo # A persisten virtual column cannot depend on sql_mode -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) PERSISTENT); SHOW WARNINGS; -DROP TABLE t1; CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) PERSISTENT); SHOW CREATE TABLE t1; diff --git a/mysql-test/suite/vcol/t/vcol_sql_mode_datetime.test b/mysql-test/suite/vcol/t/vcol_sql_mode_datetime.test index bef1e09b719..19ebd9648a7 100644 --- a/mysql-test/suite/vcol/t/vcol_sql_mode_datetime.test +++ b/mysql-test/suite/vcol/t/vcol_sql_mode_datetime.test @@ -47,32 +47,29 @@ DROP TABLE t1; --echo # NOT OK: lower FSP + virtual index -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( t DATETIME(4), d DATETIME, v DATETIME(3) AS (t) VIRTUAL, KEY(v,d) ); -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( t DATETIME(4), d DATETIME, v DATETIME(3) AS (COALESCE(t)) VIRTUAL, KEY(v,d) ); -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( t DATETIME(4), d DATETIME, v DATETIME(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL, KEY(v,d) ); -DROP TABLE t1; --echo # OK: lower FSP + ROUND + virtual index @@ -110,13 +107,12 @@ SET sql_mode=DEFAULT; --echo # MDEV-20423 Assertion `0' failed or `btr_validate_index(index, 0, false)' in row_upd_sec_index_entry or error code 126: Index is corrupted upon DELETE with TIME_ROUND_FRACTIONAL --echo # -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a DATETIME(6), v VARCHAR(30) GENERATED ALWAYS AS (CAST(a AS DATETIME(3))) VIRTUAL, KEY (v) ); -DROP TABLE t1; CREATE TABLE t1 ( a DATETIME(6), diff --git a/mysql-test/suite/vcol/t/vcol_sql_mode_time.test b/mysql-test/suite/vcol/t/vcol_sql_mode_time.test index 05160a43ebb..2ce2a9eb5d7 100644 --- a/mysql-test/suite/vcol/t/vcol_sql_mode_time.test +++ b/mysql-test/suite/vcol/t/vcol_sql_mode_time.test @@ -47,32 +47,29 @@ DROP TABLE t1; --echo # NOT OK: lower FSP + virtual index -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( t TIME(4), d TIME, v TIME(3) AS (t) VIRTUAL, KEY(v,d) ); -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( t TIME(4), d TIME, v TIME(3) AS (COALESCE(t)) VIRTUAL, KEY(v,d) ); -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( t TIME(4), d TIME, v TIME(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL, KEY(v,d) ); -DROP TABLE t1; --echo # OK: lower FSP + ROUND + virtual index @@ -111,13 +108,12 @@ SET sql_mode=DEFAULT; --echo # MDEV-20423 Assertion `0' failed or `btr_validate_index(index, 0, false)' in row_upd_sec_index_entry or error code 126: Index is corrupted upon DELETE with TIME_ROUND_FRACTIONAL --echo # -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a TIME(6), v VARCHAR(30) GENERATED ALWAYS AS (CAST(a AS TIME(3))) VIRTUAL, KEY (v) ); -DROP TABLE t1; CREATE TABLE t1 ( a TIME(6), diff --git a/mysql-test/suite/vcol/t/vcol_sql_mode_timestamp.test b/mysql-test/suite/vcol/t/vcol_sql_mode_timestamp.test index 4f38da75948..e3dee5828f0 100644 --- a/mysql-test/suite/vcol/t/vcol_sql_mode_timestamp.test +++ b/mysql-test/suite/vcol/t/vcol_sql_mode_timestamp.test @@ -47,32 +47,29 @@ DROP TABLE t1; --echo # NOT OK: lower FSP + virtual index -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( t TIMESTAMP(4), d DATETIME, v TIMESTAMP(3) AS (t) VIRTUAL, KEY(v,d) ); -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( t TIMESTAMP(4), d DATETIME, v TIMESTAMP(3) AS (COALESCE(t)) VIRTUAL, KEY(v,d) ); -DROP TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( t TIMESTAMP(4), d DATETIME, v TIMESTAMP(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL, KEY(v,d) ); -DROP TABLE t1; --echo # OK: lower FSP + ROUND + virtual index @@ -115,10 +112,11 @@ CREATE TABLE t1 ( b TIMESTAMP AS (a) VIRTUAL, KEY (b) ); +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED ALTER TABLE t1 MODIFY a BLOB FIRST; +SHOW WARNINGS; DROP TABLE t1; - --echo # --echo # End of 10.4 tests --echo # diff --git a/mysql-test/suite/vcol/t/vcol_sql_mode_upgrade.test b/mysql-test/suite/vcol/t/vcol_sql_mode_upgrade.test index 26b76fbcfe1..71e34231ac1 100644 --- a/mysql-test/suite/vcol/t/vcol_sql_mode_upgrade.test +++ b/mysql-test/suite/vcol/t/vcol_sql_mode_upgrade.test @@ -25,21 +25,19 @@ SELECT * FROM t1; FLUSH TABLES; SELECT * FROM t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t2 LIKE t1; -DROP TABLE t2; FLUSH TABLES; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t2 LIKE t1; -DROP TABLE t2; SHOW CREATE TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED ALTER TABLE t1 ADD b INT DEFAULT a; SHOW CREATE TABLE t1; SELECT * FROM t1; FLUSH TABLES; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED ALTER TABLE t1 ADD c INT DEFAULT a; SELECT * FROM t1; DROP TABLE t1; @@ -92,20 +90,18 @@ SELECT * FROM t1; FLUSH TABLES; SELECT * FROM t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t2 LIKE t1; -DROP TABLE t2; FLUSH TABLES; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t2 LIKE t1; -DROP TABLE t2; SHOW CREATE TABLE t1; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED ALTER TABLE t1 ADD b INT DEFAULT a; FLUSH TABLES; -#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED ALTER TABLE t1 ADD c INT DEFAULT a; SELECT * FROM t1; DROP TABLE t1; diff --git a/mysql-test/suite/vcol/t/vcol_supported_sql_funcs.test b/mysql-test/suite/vcol/t/vcol_supported_sql_funcs.test index d1e21af059b..36c1ba09692 100644 --- a/mysql-test/suite/vcol/t/vcol_supported_sql_funcs.test +++ b/mysql-test/suite/vcol/t/vcol_supported_sql_funcs.test @@ -32,7 +32,7 @@ ##### Storage engine to be tested # Set the session storage engine -eval SET @@session.storage_engine = 'MyISAM'; +eval SET @@session.default_storage_engine = 'MyISAM'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_trigger_sp_innodb.test b/mysql-test/suite/vcol/t/vcol_trigger_sp_innodb.test index 5a36fb1c06d..c35a1279629 100644 --- a/mysql-test/suite/vcol/t/vcol_trigger_sp_innodb.test +++ b/mysql-test/suite/vcol/t/vcol_trigger_sp_innodb.test @@ -35,7 +35,7 @@ ##### Storage engine to be tested # Set the session storage engine --source include/have_innodb.inc -eval SET @@session.storage_engine = 'InnoDB'; +eval SET @@session.default_storage_engine = 'InnoDB'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_trigger_sp_myisam.test b/mysql-test/suite/vcol/t/vcol_trigger_sp_myisam.test index c475a31eaa6..d5f36d805f1 100644 --- a/mysql-test/suite/vcol/t/vcol_trigger_sp_myisam.test +++ b/mysql-test/suite/vcol/t/vcol_trigger_sp_myisam.test @@ -34,7 +34,7 @@ ##### Storage engine to be tested # Set the session storage engine -eval SET @@session.storage_engine = 'MyISAM'; +eval SET @@session.default_storage_engine = 'MyISAM'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_view_innodb.test b/mysql-test/suite/vcol/t/vcol_view_innodb.test index 01fced8e4c3..fd77489f5dc 100644 --- a/mysql-test/suite/vcol/t/vcol_view_innodb.test +++ b/mysql-test/suite/vcol/t/vcol_view_innodb.test @@ -34,7 +34,7 @@ ##### Storage engine to be tested # Set the session storage engine --source include/have_innodb.inc -eval SET @@session.storage_engine = 'InnoDB'; +eval SET @@session.default_storage_engine = 'InnoDB'; ##### Workarounds for known open engine specific bugs # none diff --git a/mysql-test/suite/vcol/t/vcol_view_myisam.test b/mysql-test/suite/vcol/t/vcol_view_myisam.test index 2ebd36431bc..1b4a387a1ae 100644 --- a/mysql-test/suite/vcol/t/vcol_view_myisam.test +++ b/mysql-test/suite/vcol/t/vcol_view_myisam.test @@ -33,7 +33,7 @@ ##### Storage engine to be tested # Set the session storage engine -eval SET @@session.storage_engine = 'MyISAM'; +eval SET @@session.default_storage_engine = 'MyISAM'; ##### Workarounds for known open engine specific bugs # none |