MDEV-15413 Unexpected errors upon CREATE TABLE .. WITH SYSTEM VERSIONING AS SELECT ...

numerous fixes for CREATE ... SELECT with system versioning:
In CREATE ... SELECT the table is created based on the result set,
field properties do not count. That is
* field invisibility is *not* copied over
* AS ROW START/END is *not* copied over
* the history is *not* copied over
* system row_start/row_end fields can *not* be created from the SELECT part

3 files changed, 57 insertions, 20 deletions

diff --git a/mysql-test/suite/versioning/r/create.result b/mysql-test/suite/versioning/r/create.result
index 815eefaf82a..231aae66482 100644
--- a/mysql-test/suite/versioning/r/create.result
+++ b/mysql-test/suite/versioning/r/create.result
@@ -331,9 +331,9 @@ select * from t3;
 x23
 1
 create or replace table t3 with system versioning select x23, row_start from t1;
-ERROR HY000: Wrong parameters for `t3`: missing 'AS ROW END'
+ERROR 42S21: Duplicate column name 'row_start'
 create or replace table t3 with system versioning select x23, row_end from t1;
-ERROR HY000: Wrong parameters for `t3`: missing 'AS ROW START'
+ERROR 42S21: Duplicate column name 'row_end'
 # Prepare checking for historical row
 delete from t1;
 select row_end from t1 for system_time all into @row_end;
@@ -373,15 +373,19 @@ as select x25, row_start, row_end from t1 for system_time all;
 show create table t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `x25` int(11) DEFAULT NULL
+  `x25` int(11) DEFAULT NULL,
+  `row_start` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
+  `row_end` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
 create or replace table t2 with system versioning
-as select x25, row_start, row_end from t1;
+as select x25, row_start rs, row_end re from t1;
 show create table t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
-  `x25` int(11) DEFAULT NULL
-) ENGINE=NON_DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+  `x25` int(11) DEFAULT NULL,
+  `rs` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
+  `re` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
 create or replace table t1 (
 x26 int,
 st bigint unsigned as row start,
@@ -390,17 +394,25 @@ period for system_time (st, en)
 ) with system versioning engine innodb;
 create or replace table t2 with system versioning engine myisam
 as select * from t1;
-ERROR HY000: `st` must be of type TIMESTAMP(6) for system-versioned table `t2`
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `x26` int(11) DEFAULT NULL,
+  `st` bigint(20) unsigned NOT NULL DEFAULT 0,
+  `en` bigint(20) unsigned NOT NULL DEFAULT 0
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
 create or replace table t1 (x27 int, id int) with system versioning engine NON_DEFAULT_ENGINE;
 create or replace table t2 (b int, id int);
 create or replace table t3 with system versioning
-as select t2.b, t1.x27, t1.row_start, t1.row_end from t2 inner join t1 on t2.id=t1.id;
+as select t2.b, t1.x27, t1.row_start rs, t1.row_end re from t2 inner join t1 on t2.id=t1.id;
 show create table t3;
 Table	Create Table
 t3	CREATE TABLE `t3` (
   `b` int(11) DEFAULT NULL,
-  `x27` int(11) DEFAULT NULL
-) ENGINE=NON_DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+  `x27` int(11) DEFAULT NULL,
+  `rs` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
+  `re` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
 ## Errors
 create or replace temporary table t (x28 int) with system versioning;
 ERROR HY000: TEMPORARY tables do not support system versioning
@@ -435,8 +447,10 @@ Table	Create Table
 t3	CREATE TABLE `t3` (
   `x30` int(11) DEFAULT NULL,
   `y` int(11) DEFAULT NULL,
-  `st` timestamp(6) NOT NULL INVISIBLE DEFAULT '0000-00-00 00:00:00.000000',
-  `en` timestamp(6) NOT NULL INVISIBLE DEFAULT '0000-00-00 00:00:00.000000'
+  `row_start` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
+  `row_end` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
+  `st` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
+  `en` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
 create or replace table t3 (
 y int,
@@ -450,6 +464,8 @@ Table	Create Table
 t3	CREATE TABLE `t3` (
   `x30` int(11) DEFAULT NULL,
   `y` int(11) DEFAULT NULL,
+  `row_start` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
+  `row_end` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
   `st` timestamp(6) GENERATED ALWAYS AS ROW START INVISIBLE,
   `en` timestamp(6) GENERATED ALWAYS AS ROW END INVISIBLE,
   PERIOD FOR SYSTEM_TIME (`st`, `en`)
@@ -466,5 +482,18 @@ execute bad;
 execute bad;
 execute bad;
 # bad is good.
+# MDEV-15413 Unexpected errors upon CREATE TABLE .. WITH SYSTEM VERSIONING AS SELECT ...
+create or replace table t1 with system versioning as select 1 as i;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i` int(1) NOT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+create or replace table t1 (i int) with system versioning as select 1 as i;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
 drop database test;
 create database test;
diff --git a/mysql-test/suite/versioning/r/select.result b/mysql-test/suite/versioning/r/select.result
index 26b3a5236f8..ff13b0ebe76 100644
--- a/mysql-test/suite/versioning/r/select.result
+++ b/mysql-test/suite/versioning/r/select.result
@@ -396,7 +396,7 @@ create or replace table t1 (a int) with system versioning;
 create or replace table t2 (b int);
 create or replace view v1 as select a, row_start, row_end from t1 where a > round(rand()*1000);
 select * from v1 natural join t2;
-a	b
+a	row_start	row_end	b
 #
 # Issue #406, MDEV-14633 Assertion on TRT read
 #
diff --git a/mysql-test/suite/versioning/t/create.test b/mysql-test/suite/versioning/t/create.test
index ce135e66ec9..7a7eb556c67 100644
--- a/mysql-test/suite/versioning/t/create.test
+++ b/mysql-test/suite/versioning/t/create.test
@@ -247,9 +247,9 @@ create or replace table t3 with system versioning select x23 from t1;
 --replace_result $default_engine DEFAULT_ENGINE
 show create table t3;
 select * from t3;
---error ER_MISSING
+--error ER_DUP_FIELDNAME
 create or replace table t3 with system versioning select x23, row_start from t1;
---error ER_MISSING
+--error ER_DUP_FIELDNAME
 create or replace table t3 with system versioning select x23, row_end from t1;
 
 --echo # Prepare checking for historical row
@@ -286,8 +286,8 @@ as select x25, row_start, row_end from t1 for system_time all;
 show create table t2;
 
 create or replace table t2 with system versioning
-as select x25, row_start, row_end from t1;
---replace_result $non_default_engine NON_DEFAULT_ENGINE
+as select x25, row_start rs, row_end re from t1;
+--replace_result $default_engine DEFAULT_ENGINE
 show create table t2;
 
 create or replace table t1 (
@@ -296,16 +296,16 @@ create or replace table t1 (
   en bigint unsigned as row end,
   period for system_time (st, en)
 ) with system versioning engine innodb;
---error ER_VERS_FIELD_WRONG_TYPE
 create or replace table t2 with system versioning engine myisam
 as select * from t1;
+show create table t2;
 
 --replace_result $non_default_engine NON_DEFAULT_ENGINE
 eval create or replace table t1 (x27 int, id int) with system versioning engine $non_default_engine;
 create or replace table t2 (b int, id int);
 create or replace table t3 with system versioning
-as select t2.b, t1.x27, t1.row_start, t1.row_end from t2 inner join t1 on t2.id=t1.id;
---replace_result $non_default_engine NON_DEFAULT_ENGINE
+as select t2.b, t1.x27, t1.row_start rs, t1.row_end re from t2 inner join t1 on t2.id=t1.id;
+--replace_result $default_engine DEFAULT_ENGINE
 show create table t3;
 
 --echo ## Errors
@@ -363,5 +363,13 @@ prepare bad from 'create or replace table t2 with system versioning as select *
 execute bad; execute bad; execute bad; execute bad; execute bad; execute bad; execute bad; execute bad;
 --echo # bad is good.
 
+--echo # MDEV-15413 Unexpected errors upon CREATE TABLE .. WITH SYSTEM VERSIONING AS SELECT ...
+create or replace table t1 with system versioning as select 1 as i;
+--replace_result $default_engine DEFAULT_ENGINE
+show create table t1;
+create or replace table t1 (i int) with system versioning as select 1 as i;
+--replace_result $default_engine DEFAULT_ENGINE
+show create table t1;
+
 drop database test;
 create database test;