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
|
create 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 view v as select * from t;
select * from information_schema.periods;
--sorted_result
select * from information_schema.key_period_usage;
drop view v;
create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
period for mytime(s,e));
show create table t;
--echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)a)
--echo # 2) If a <table period definition> TPD is specified, then:
--echo # a) <table scope> shall not be specified.
--error ER_PERIOD_TEMPORARY_NOT_ALLOWED
create or replace temporary table t (s date, e date, period for mytime(s,e));
--echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)e)iii)
--echo # The <data type or domain name> contained in CD1 is either DATE or a
--echo # timestamp type and it is equivalent to the <data type or domain name>
--echo # contained in CD2.
--error ER_PERIOD_TYPES_MISMATCH
create or replace table t (id int primary key, s datetime, e date,
period for mytime(s,e));
--error ER_PERIOD_TYPES_MISMATCH
create or replace table t (s timestamp(2), e timestamp(6),
period for mytime(s,e));
--error ER_WRONG_FIELD_SPEC
create or replace table t (id int primary key, s int, e date,
period for mytime(s,e));
--error ER_WRONG_FIELD_SPEC
create or replace table t (id int primary key, s time, e time,
period for mytime(s,e));
--error ER_BAD_FIELD_ERROR
create or replace table t (id int primary key, s date, e date,
period for mytime(s,x));
--echo # MDEV-18842: Unfortunate error message when the same column is used
--echo # for application period start and end
--error ER_FIELD_SPECIFIED_TWICE
create or replace table t (s date, t date, period for apt(s,s));
--error ER_MORE_THAN_ONE_PERIOD
create or replace table t (id int primary key, s date, e date,
period for mytime(s,e),
period for mytime2(s,e));
--echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)d)
--echo # No <column name> in any <column definition> shall be equivalent to PN.
--error ER_DUP_FIELDNAME
create or replace table t (mytime int, s date, e date,
period for mytime(s,e));
--echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)e)v)2)A)
--echo # Neither CD1 nor CD2 shall contain an <identity column specification>, a
--echo # <generation clause>, a <system time period start column specification>,
--echo # or a <system time period end column specification>.
--error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
create or replace table t (id int primary key,
s date,
e date generated always as (s+1),
period for mytime(s,e));
--error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
create or replace table t (id int primary key,
s date,
e date as (s+1) VIRTUAL,
period for mytime(s,e));
--error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
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),
period for mytime(st,e)) with system versioning;
--echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)
--echo # Let IDCN be an implementation-dependent <constraint name> that is not
--echo # equivalent to the <constraint name> of any table constraint descriptor
--echo # included in S.
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');
show status like "Feature_application_time_periods";
drop table t;
--echo # MDEV-29387: Period name with more than 32 symbols crashes the server
--echo #
--echo # test 34 symbols
create table t2 (s date, e date,
period for `abcd123456789012345678901234567890` (s,e));
drop table t2;
--echo # test 64 symbols
create table t2 (s date, e date, period for
`abcd123456789012345678901234567890123456789012345678901234567890`
(s,e));
drop table t2;
--echo # MDEV-32205 Server crashes in get_schema_key_period_usage_record on
--echo # server without InnoDB
--echo # Make sure innodb id disabled, but there's at least one innodb table
--disable_warnings
select "yes" from information_schema.tables where engine="innodb" limit 1;
select plugin_status from information_schema.all_plugins where plugin_name = "innodb";
select * from information_schema.periods;
select * from information_schema.key_period_usage;
--enable_warnings
--echo # [DUPLICATE] MDEV-32204 Server crashes in
--echo # get_schema_key_period_usage_record
create table t (a date) engine=myisam;
create table t1 (a int) engine=merge union = (t) ;
--sorted_result
select 1 from information_schema.key_period_usage;
drop table t1;
drop table t;
create view v1 as select 1;
create view v2 as select * from v1;
drop view v1;
--sorted_result
select * from information_schema.key_period_usage;
drop view v2;
|
