summaryrefslogtreecommitdiff
path: root/mysql-test/suite/vcol
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/vcol')
-rw-r--r--mysql-test/suite/vcol/inc/vcol_column_def_options.inc6
-rw-r--r--mysql-test/suite/vcol/inc/vcol_ins_upd.inc2
-rw-r--r--mysql-test/suite/vcol/inc/vcol_select.inc4
-rw-r--r--mysql-test/suite/vcol/r/binlog.result2
-rw-r--r--mysql-test/suite/vcol/r/rpl_vcol.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_archive.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_blackhole.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_column_def_options_innodb.result8
-rw-r--r--mysql-test/suite/vcol/r/vcol_column_def_options_myisam.result8
-rw-r--r--mysql-test/suite/vcol/r/vcol_csv.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_handler_aria.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_handler_innodb.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_handler_myisam.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_ins_upd_innodb.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_ins_upd_myisam.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_keys_innodb.result9
-rw-r--r--mysql-test/suite/vcol/r/vcol_keys_myisam.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_memory.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_non_stored_columns_innodb.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_non_stored_columns_myisam.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_partition_innodb.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_partition_myisam.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_select_innodb.result8
-rw-r--r--mysql-test/suite/vcol/r/vcol_select_myisam.result8
-rw-r--r--mysql-test/suite/vcol/r/vcol_sql_mode.result213
-rw-r--r--mysql-test/suite/vcol/r/vcol_sql_mode_datetime.result20
-rw-r--r--mysql-test/suite/vcol/r/vcol_sql_mode_time.result20
-rw-r--r--mysql-test/suite/vcol/r/vcol_sql_mode_timestamp.result25
-rw-r--r--mysql-test/suite/vcol/r/vcol_sql_mode_upgrade.result94
-rw-r--r--mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_trigger_sp_innodb.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_trigger_sp_myisam.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_view_innodb.result2
-rw-r--r--mysql-test/suite/vcol/r/vcol_view_myisam.result2
-rw-r--r--mysql-test/suite/vcol/t/rpl_vcol.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_archive.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_blackhole.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_blocked_sql_funcs.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_column_def_options_innodb.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_column_def_options_myisam.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_csv.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_handler_aria.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_handler_innodb.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_handler_myisam.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_ins_upd_innodb.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_ins_upd_myisam.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_keys_innodb.test4
-rw-r--r--mysql-test/suite/vcol/t/vcol_keys_myisam.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_memory.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_non_stored_columns_innodb.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_non_stored_columns_myisam.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_partition_innodb.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_partition_myisam.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_select_innodb.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_select_myisam.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_sql_mode.test83
-rw-r--r--mysql-test/suite/vcol/t/vcol_sql_mode_datetime.test12
-rw-r--r--mysql-test/suite/vcol/t/vcol_sql_mode_time.test12
-rw-r--r--mysql-test/suite/vcol/t/vcol_sql_mode_timestamp.test12
-rw-r--r--mysql-test/suite/vcol/t/vcol_sql_mode_upgrade.test20
-rw-r--r--mysql-test/suite/vcol/t/vcol_supported_sql_funcs.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_trigger_sp_innodb.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_trigger_sp_myisam.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_view_innodb.test2
-rw-r--r--mysql-test/suite/vcol/t/vcol_view_myisam.test2
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