MDEV-16975 Application-time periods: ALTER TABLE

* implicit period constraint is hidden and cannot be dropped independently
* create...like and create...select support

4 files changed, 315 insertions, 5 deletions

diff --git a/mysql-test/suite/period/r/alter.result b/mysql-test/suite/period/r/alter.result
new file mode 100644
index 00000000000..c487b4a0d7f
--- /dev/null
+++ b/mysql-test/suite/period/r/alter.result
@@ -0,0 +1,174 @@
+set @s= '1992-01-01';
+set @e= '1999-12-31';
+create or replace table t (s date, e date);
+# period start/end columns are implicit NOT NULL
+alter table t add period for a(s, e);
+show create table t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `s` date NOT NULL,
+  `e` date NOT NULL,
+  PERIOD FOR `a` (`s`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create or replace table t (s date, e date);
+alter table t change s s date, add period for a(s, e);
+show create table t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `s` date NOT NULL,
+  `e` date NOT NULL,
+  PERIOD FOR `a` (`s`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+alter table t add id int;
+show create table t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `s` date NOT NULL,
+  `e` date NOT NULL,
+  `id` int(11) DEFAULT NULL,
+  PERIOD FOR `a` (`s`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+alter table t drop id;
+show create table t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `s` date NOT NULL,
+  `e` date NOT NULL,
+  PERIOD FOR `a` (`s`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert t values(@e, @s);
+ERROR 23000: CONSTRAINT `a` failed for `test`.`t`
+alter table t drop constraint a;
+ERROR HY000: Can't DROP CONSTRAINT `a`. Use DROP PERIOD `a` for this
+# no-op
+alter table t drop period if exists for b;
+Warnings:
+Note	1091	Can't DROP PERIOD `b`; check that it exists
+# no-op
+alter table t add period if not exists for a(e, s);
+Warnings:
+Note	1060	Duplicate column name 'a'
+alter table t drop period if exists for a;
+# no-op
+alter table t drop period if exists for a;
+Warnings:
+Note	1091	Can't DROP PERIOD `a`; check that it exists
+alter table t add period for a(s, e), add period if not exists for a(e, s);
+show create table t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `s` date NOT NULL,
+  `e` date NOT NULL,
+  PERIOD FOR `a` (`s`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+alter table t drop period for a;
+# Constraint is dropped
+insert t values(@e, @s);
+alter table t drop period for a;
+ERROR 42000: Can't DROP PERIOD `a`; check that it exists
+alter table t add period for a(s, e), drop period for a;
+ERROR 42000: Can't DROP PERIOD `a`; check that it exists
+truncate t;
+alter table t add period for a(s, e);
+insert t values(@e, @s);
+ERROR 23000: CONSTRAINT `a` failed for `test`.`t`
+alter table t add period for a(s, e), drop period for a;
+insert t values(@e, @s);
+ERROR 23000: CONSTRAINT `a` failed for `test`.`t`
+alter table t add s1 date not null, add period for b(s1, e), drop period for a;
+show create table t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `s` date NOT NULL,
+  `e` date NOT NULL,
+  `s1` date NOT NULL,
+  PERIOD FOR `b` (`s1`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert t(s, s1, e) values(@e, @s, @e);
+insert t(s, s1, e) values(@e, @e, @s);
+ERROR 23000: CONSTRAINT `b` failed for `test`.`t`
+create table t1 like t;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `s` date NOT NULL,
+  `e` date NOT NULL,
+  `s1` date NOT NULL,
+  PERIOD FOR `b` (`s1`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create table t2 (period for b(s,e)) select * from t;
+ERROR 23000: CONSTRAINT `b` failed for `test`.`t2`
+create table t2 (period for b(s1,e)) select * from t;
+# SQL16 11.27 <add table period definition>, Syntax Rules, 5)g)
+# The declared type of BC1 shall be either DATE or a timestamp type
+# and shall be equivalent to the declared type of BC2.
+create or replace table t (s timestamp not null, e timestamp(6) not null);
+alter table t add period for a(s, e);
+ERROR HY000: Fields of PERIOD FOR `a` have different types
+# SQL16 11.27 <add table period definition>, Syntax Rules, 5)c)
+# No column of T shall have a column name that is equivalent to ATPN.
+create or replace table t (a int, s date, e date);
+alter table t add period for a(s, e);
+ERROR 42S21: Duplicate column name 'a'
+# SQL16 11.27 <add table period definition>, Syntax Rules, 5)i)
+# Neither BC1 nor BC2 shall be an identity column, a generated column,
+# a system-time period start column, or a system-time period end column.
+create or replace table t (id int primary key,
+s date,
+e date generated always as (s+1));
+alter table t add period for a(s, e);
+ERROR HY000: Period field `e` cannot be GENERATED ALWAYS AS
+create or replace table t (id int primary key,
+s date,
+e date as (s+1) VIRTUAL);
+alter table t add period for a(s, e);
+ERROR HY000: Period field `e` cannot be GENERATED ALWAYS AS
+create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
+st timestamp(6) as row start,
+en timestamp(6) as row end,
+period for system_time (st, en)) with system versioning;
+alter table t add period for a(s, en);
+ERROR HY000: Period field `en` cannot be GENERATED ALWAYS AS
+# SQL16 11.27 <add table period definition>, Syntax Rules, 5)b)
+# The table descriptor of T shall not include a period descriptor other
+# than a system-time period descriptor.
+alter table t add period for a(s, e);
+alter table t add period for b(s, e);
+ERROR HY000: Cannot specify more than one application-time period
+# SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)e)v)2)B)
+# Let S be the schema identified by the explicit or implicit
+# <schema name> of TN. Let IDCN be an implementation-dependent
+# <constraint name> that is not equivalent to the <constraint name> of
+# any table constraint descriptor included in S. The following
+# <table constraint definition> is implicit:
+# CONSTRAINT IDCN CHECK ( CN1 < CN2 )
+#
+# Due to the above standard limitation, the constraint name can't always
+# match the period name. So it matches when possible; and when not, it
+# is unique not taken name prefixed with period name.
+create or replace table t (x int, s date, e date,
+period for mytime(s, e));
+show create table t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `x` int(11) DEFAULT NULL,
+  `s` date NOT NULL,
+  `e` date NOT NULL,
+  PERIOD FOR `mytime` (`s`, `e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+alter table t add constraint mytime check (x > 1);
+show create table t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `x` int(11) DEFAULT NULL,
+  `s` date NOT NULL,
+  `e` date NOT NULL,
+  PERIOD FOR `mytime` (`s`, `e`),
+  CONSTRAINT `mytime` CHECK (`x` > 1)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert t values (2, @e, @s);
+ERROR 23000: CONSTRAINT `mytime_1` failed for `test`.`t`
+alter table t add constraint mytime_1 check (x > 2);
+insert t values (3, @e, @s);
+ERROR 23000: CONSTRAINT `mytime_2` failed for `test`.`t`
+create or replace database test;
diff --git a/mysql-test/suite/period/r/create.result b/mysql-test/suite/period/r/create.result
index e498e745240..df77669f727 100644
--- a/mysql-test/suite/period/r/create.result
+++ b/mysql-test/suite/period/r/create.result
@@ -1,5 +1,7 @@
 create or replace table t (id int primary key, s date, e date,
 period for mytime(s,e));
+# CONSTRAINT CHECK (s < e) is added implicitly, and shouldn't be shown
+# this is important for correct command-based replication
 show create table t;
 Table	Create Table
 t	CREATE TABLE `t` (
@@ -7,8 +9,7 @@ t	CREATE TABLE `t` (
   `s` date NOT NULL,
   `e` date NOT NULL,
   PRIMARY KEY (`id`),
-  PERIOD FOR `mytime` (`s`, `e`),
-  CONSTRAINT `CONSTRAINT_1` CHECK (`s` < `e`)
+  PERIOD FOR `mytime` (`s`, `e`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
 period for mytime(s,e));
@@ -19,8 +20,7 @@ t	CREATE TABLE `t` (
   `s` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
   `e` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
   PRIMARY KEY (`id`),
-  PERIOD FOR `mytime` (`s`, `e`),
-  CONSTRAINT `CONSTRAINT_1` CHECK (`s` < `e`)
+  PERIOD FOR `mytime` (`s`, `e`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 # SQL16, Part 2, 11.3  <table definition>, Syntax Rules, 2)a)
 # 2) If a <table period definition> TPD is specified, then:
@@ -86,7 +86,8 @@ t	CREATE TABLE `t` (
   `s` date NOT NULL,
   `e` date NOT NULL,
   PERIOD FOR `mytime` (`s`, `e`),
-  CONSTRAINT `CONSTRAINT_1` CHECK (`s` < `e`),
   CONSTRAINT `mytime` CHECK (`x` > 1)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert t values (2, '2001-01-01', '2001-01-01');
+ERROR 23000: CONSTRAINT `mytime_1` failed for `test`.`t`
 create or replace database test;
diff --git a/mysql-test/suite/period/t/alter.test b/mysql-test/suite/period/t/alter.test
new file mode 100644
index 00000000000..4d504369859
--- /dev/null
+++ b/mysql-test/suite/period/t/alter.test
@@ -0,0 +1,131 @@
+set @s= '1992-01-01';
+set @e= '1999-12-31';
+
+create or replace table t (s date, e date);
+
+--echo # period start/end columns are implicit NOT NULL
+alter table t add period for a(s, e);
+show create table t;
+
+create or replace table t (s date, e date);
+alter table t change s s date, add period for a(s, e);
+show create table t;
+
+alter table t add id int;
+show create table t;
+alter table t drop id;
+show create table t;
+
+--error ER_CONSTRAINT_FAILED
+insert t values(@e, @s);
+
+--error ER_PERIOD_CONSTRAINT_DROP
+alter table t drop constraint a;
+
+--echo # no-op
+alter table t drop period if exists for b;
+--echo # no-op
+alter table t add period if not exists for a(e, s);
+
+alter table t drop period if exists for a;
+--echo # no-op
+alter table t drop period if exists for a;
+
+alter table t add period for a(s, e), add period if not exists for a(e, s);
+show create table t;
+
+alter table t drop period for a;
+--echo # Constraint is dropped
+insert t values(@e, @s);
+
+--error ER_CANT_DROP_FIELD_OR_KEY
+alter table t drop period for a;
+--error ER_CANT_DROP_FIELD_OR_KEY
+alter table t add period for a(s, e), drop period for a;
+
+truncate t;
+alter table t add period for a(s, e);
+--error ER_CONSTRAINT_FAILED
+insert t values(@e, @s);
+alter table t add period for a(s, e), drop period for a;
+--error ER_CONSTRAINT_FAILED
+insert t values(@e, @s);
+alter table t add s1 date not null, add period for b(s1, e), drop period for a;
+show create table t;
+insert t(s, s1, e) values(@e, @s, @e);
+--error ER_CONSTRAINT_FAILED
+insert t(s, s1, e) values(@e, @e, @s);
+
+create table t1 like t;
+show create table t1;
+
+--error ER_CONSTRAINT_FAILED
+create table t2 (period for b(s,e)) select * from t;
+
+create table t2 (period for b(s1,e)) select * from t;
+
+--echo # SQL16 11.27 <add table period definition>, Syntax Rules, 5)g)
+--echo # The declared type of BC1 shall be either DATE or a timestamp type
+--echo # and shall be equivalent to the declared type of BC2.
+create or replace table t (s timestamp not null, e timestamp(6) not null);
+--error ER_PERIOD_TYPES_MISMATCH
+alter table t add period for a(s, e);
+
+--echo # SQL16 11.27 <add table period definition>, Syntax Rules, 5)c)
+--echo # No column of T shall have a column name that is equivalent to ATPN.
+create or replace table t (a int, s date, e date);
+--error ER_DUP_FIELDNAME
+alter table t add period for a(s, e);
+
+--echo # SQL16 11.27 <add table period definition>, Syntax Rules, 5)i)
+--echo # Neither BC1 nor BC2 shall be an identity column, a generated column,
+--echo # a system-time period start column, or a system-time period end column.
+create or replace table t (id int primary key,
+                           s date,
+                           e date generated always as (s+1));
+--error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
+alter table t add period for a(s, e);
+
+create or replace table t (id int primary key,
+                           s date,
+                           e date as (s+1) VIRTUAL);
+--error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
+alter table t add period for a(s, e);
+
+create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
+                           st timestamp(6) as row start,
+                           en timestamp(6) as row end,
+                           period for system_time (st, en)) with system versioning;
+--error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
+alter table t add period for a(s, en);
+
+--echo # SQL16 11.27 <add table period definition>, Syntax Rules, 5)b)
+--echo # The table descriptor of T shall not include a period descriptor other
+--echo # than a system-time period descriptor.
+alter table t add period for a(s, e);
+--error ER_MORE_THAN_ONE_PERIOD
+alter table t add period for b(s, e);
+
+--echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)e)v)2)B)
+--echo # Let S be the schema identified by the explicit or implicit
+--echo # <schema name> of TN. Let IDCN be an implementation-dependent
+--echo # <constraint name> that is not equivalent to the <constraint name> of
+--echo # any table constraint descriptor included in S. The following
+--echo # <table constraint definition> is implicit:
+--echo # CONSTRAINT IDCN CHECK ( CN1 < CN2 )
+--echo #
+--echo # Due to the above standard limitation, the constraint name can't always
+--echo # match the period name. So it matches when possible; and when not, it
+--echo # is unique not taken name prefixed with period name.
+create or replace table t (x int, s date, e date,
+                           period for mytime(s, e));
+show create table t;
+alter table t add constraint mytime check (x > 1);
+show create table t;
+--error ER_CONSTRAINT_FAILED
+insert t values (2, @e, @s);
+alter table t add constraint mytime_1 check (x > 2);
+--error ER_CONSTRAINT_FAILED
+insert t values (3, @e, @s);
+
+create or replace database test;
diff --git a/mysql-test/suite/period/t/create.test b/mysql-test/suite/period/t/create.test
index 0960d1b5420..9648fa02fbc 100644
--- a/mysql-test/suite/period/t/create.test
+++ b/mysql-test/suite/period/t/create.test
@@ -1,5 +1,7 @@
 create or replace table t (id int primary key, s date, e date,
                            period for mytime(s,e));
+--echo # CONSTRAINT CHECK (s < e) is added implicitly, and shouldn't be shown
+--echo # this is important for correct command-based replication
 show create table t;
 create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
                            period for mytime(s,e));
@@ -69,5 +71,7 @@ create or replace table t (x int, s date, e date,
                            period for mytime(s, e),
                            constraint mytime check (x > 1));
 show create table t;
+--error ER_CONSTRAINT_FAILED
+insert t values (2, '2001-01-01', '2001-01-01');
 
 create or replace database test;