MDEV-8441 Bad SHOW CREATE TABLE output for a table with a virtual column

4 files changed, 52 insertions, 4 deletions

diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result
index 521952f4373..e68adf80073 100644
--- a/mysql-test/suite/vcol/r/vcol_misc.result
+++ b/mysql-test/suite/vcol/r/vcol_misc.result
@@ -322,3 +322,29 @@ drop table t1;
 create table t1 (a int, b int as (b is null) virtual);
 ERROR HY000: A computed column cannot be based on a computed column
 # end of 5.3 tests
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-8441 Bad SHOW CREATE TABLE output for a table with a virtual column
+#
+CREATE TABLE t1 (a DATETIME, b TIMESTAMP AS (TIMESTAMP(a)));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` datetime DEFAULT NULL,
+  `b` timestamp AS (TIMESTAMP(a)) VIRTUAL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME, b TIMESTAMP AS (TIMESTAMP(a)),c TIMESTAMP);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` datetime DEFAULT NULL,
+  `b` timestamp AS (TIMESTAMP(a)) VIRTUAL,
+  `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs_innodb.result b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs_innodb.result
index 899a19c68f0..c2b601f8c46 100644
--- a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs_innodb.result
@@ -2539,7 +2539,7 @@ show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` datetime DEFAULT NULL,
-  `b` timestamp AS (timestamp(a)) VIRTUAL NULL ON UPDATE CURRENT_TIMESTAMP
+  `b` timestamp AS (timestamp(a)) VIRTUAL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 insert into t1 values ('2008-12-31',default);
 select * from t1;
@@ -2554,7 +2554,7 @@ show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` datetime DEFAULT NULL,
-  `b` timestamp AS (timestampadd(minute,1,a)) VIRTUAL NULL ON UPDATE CURRENT_TIMESTAMP
+  `b` timestamp AS (timestampadd(minute,1,a)) VIRTUAL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 insert into t1 values ('2003-01-02',default);
 select * from t1;
diff --git a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs_myisam.result b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs_myisam.result
index 0034f25e223..ac48c289059 100644
--- a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs_myisam.result
@@ -2539,7 +2539,7 @@ show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` datetime DEFAULT NULL,
-  `b` timestamp AS (timestamp(a)) VIRTUAL NULL ON UPDATE CURRENT_TIMESTAMP
+  `b` timestamp AS (timestamp(a)) VIRTUAL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 insert into t1 values ('2008-12-31',default);
 select * from t1;
@@ -2554,7 +2554,7 @@ show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` datetime DEFAULT NULL,
-  `b` timestamp AS (timestampadd(minute,1,a)) VIRTUAL NULL ON UPDATE CURRENT_TIMESTAMP
+  `b` timestamp AS (timestampadd(minute,1,a)) VIRTUAL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 insert into t1 values ('2003-01-02',default);
 select * from t1;
diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test
index a4c1fc06ce9..38f86702d70 100644
--- a/mysql-test/suite/vcol/t/vcol_misc.test
+++ b/mysql-test/suite/vcol/t/vcol_misc.test
@@ -283,3 +283,25 @@ drop table t1;
 create table t1 (a int, b int as (b is null) virtual);
 
 --echo # end of 5.3 tests
+
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-8441 Bad SHOW CREATE TABLE output for a table with a virtual column
+--echo #
+CREATE TABLE t1 (a DATETIME, b TIMESTAMP AS (TIMESTAMP(a)));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+# Make sure that if the first TIMESTAMP column appears to be virtual,
+# then no further promotion is done, so the next TIMESTAMP column "c" does not
+# get the "DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" attributes.
+CREATE TABLE t1 (a DATETIME, b TIMESTAMP AS (TIMESTAMP(a)),c TIMESTAMP);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.1 tests
+--echo #