1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
|
set @s= '1992-01-01';
set @e= '1999-12-31';
create 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1;
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;
drop table t2;
# 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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`
drop table t;
#
# MDEV-20494 ER_NOT_FORM_FILE or assertion upon adding partition to period table
#
create table t1 (f date, t date, period for app(f,t)) with system versioning partition by system_time ( partition p1 history, partition pn current );
lock table t1 write;
alter table t1 add partition (partition p2 history);
Warnings:
Warning 4115 Maybe missing parameters: no rotation condition for multiple HISTORY partitions.
unlock tables;
create or replace table t1 (x int, s date, e date, period for app(s,e));
insert into t1 values(1, '2020-03-01', '2020-03-02');
insert into t1 values(1, '2020-03-01', '2020-03-02');
alter table t1 add primary key(x, s, e);
ERROR 23000: Duplicate entry '1-2020-03-01-2020-03-02' for key 'PRIMARY'
alter table t1 add system versioning;
drop table t1;
#
# MDEV-18873 Server crashes in Compare_identifiers::operator or in
# my_strcasecmp_utf8 upon ADD PERIOD IF NOT EXISTS with empty name
#
alter table t add period if not exists for `` (s,e);
ERROR 42000: Incorrect column name ''
create table t(s DATE, e DATE);
alter table t add period if not exists for `` (s,e);
ERROR 42000: Incorrect column name ''
alter table t add period if not exists for ` ` (s,e);
ERROR 42000: Incorrect column name ' '
create table t2 (period for `` (s,e)) select * from t;
ERROR 42000: Incorrect column name ''
drop table t;
#
# MDEV-21941 RENAME doesn't work for system time or period fields
#
create or replace table t1 (
a int, s date, e date,
period for mytime(s, e));
alter table t1 rename column s to x;
alter table t1 rename column e to y;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`x` date NOT NULL,
`y` date NOT NULL,
PERIOD FOR `mytime` (`x`, `y`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1;
# End of 10.5 tests
|
