Debian Codegraph @

mysql-test/suite/period/t/create.test

raw
1 create table t (id int primary key, s date, e date, period for mytime(s,e));
2 --echo # CONSTRAINT CHECK (s < e) is added implicitly, and shouldn't be shown
3 --echo # this is important for correct command-based replication
4 show create table t;
5 create view v as select * from t;
6 select * from information_schema.periods;
7 --sorted_result
8 select * from information_schema.key_period_usage;
9 drop view v;
10
11 create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
12 period for mytime(s,e));
13 show create table t;
14
15 --echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)a)
16 --echo # 2) If a <table period definition> TPD is specified, then:
17 --echo # a) <table scope> shall not be specified.
18 --error ER_PERIOD_TEMPORARY_NOT_ALLOWED
19 create or replace temporary table t (s date, e date, period for mytime(s,e));
20
21 --echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)e)iii)
22 --echo # The <data type or domain name> contained in CD1 is either DATE or a
23 --echo # timestamp type and it is equivalent to the <data type or domain name>
24 --echo # contained in CD2.
25 --error ER_PERIOD_TYPES_MISMATCH
26 create or replace table t (id int primary key, s datetime, e date,
27 period for mytime(s,e));
28 --error ER_PERIOD_TYPES_MISMATCH
29 create or replace table t (s timestamp(2), e timestamp(6),
30 period for mytime(s,e));
31 --error ER_WRONG_FIELD_SPEC
32 create or replace table t (id int primary key, s int, e date,
33 period for mytime(s,e));
34 --error ER_WRONG_FIELD_SPEC
35 create or replace table t (id int primary key, s time, e time,
36 period for mytime(s,e));
37 --error ER_BAD_FIELD_ERROR
38 create or replace table t (id int primary key, s date, e date,
39 period for mytime(s,x));
40
41 --echo # MDEV-18842: Unfortunate error message when the same column is used
42 --echo # for application period start and end
43 --error ER_FIELD_SPECIFIED_TWICE
44 create or replace table t (s date, t date, period for apt(s,s));
45
46 --error ER_MORE_THAN_ONE_PERIOD
47 create or replace table t (id int primary key, s date, e date,
48 period for mytime(s,e),
49 period for mytime2(s,e));
50
51 --echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)d)
52 --echo # No <column name> in any <column definition> shall be equivalent to PN.
53 --error ER_DUP_FIELDNAME
54 create or replace table t (mytime int, s date, e date,
55 period for mytime(s,e));
56
57 --echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)e)v)2)A)
58 --echo # Neither CD1 nor CD2 shall contain an <identity column specification>, a
59 --echo # <generation clause>, a <system time period start column specification>,
60 --echo # or a <system time period end column specification>.
61 --error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
62 create or replace table t (id int primary key,
63 s date,
64 e date generated always as (s+1),
65 period for mytime(s,e));
66
67 --error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
68 create or replace table t (id int primary key,
69 s date,
70 e date as (s+1) VIRTUAL,
71 period for mytime(s,e));
72
73 --error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
74 create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
75 st timestamp(6) as row start,
76 en timestamp(6) as row end,
77 period for system_time (st, en),
78 period for mytime(st,e)) with system versioning;
79
80 --echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)
81 --echo # Let IDCN be an implementation-dependent <constraint name> that is not
82 --echo # equivalent to the <constraint name> of any table constraint descriptor
83 --echo # included in S.
84 create or replace table t (x int, s date, e date,
85 period for mytime(s, e),
86 constraint mytime check (x > 1));
87 show create table t;
88 --error ER_CONSTRAINT_FAILED
89 insert t values (2, '2001-01-01', '2001-01-01');
90
91 show status like "Feature_application_time_periods";
92
93 drop table t;
94
95 --echo # MDEV-29387: Period name with more than 32 symbols crashes the server
96 --echo #
97
98 --echo # test 34 symbols
99 create table t2 (s date, e date,
100 period for `abcd123456789012345678901234567890` (s,e));
101 drop table t2;
102
103 --echo # test 64 symbols
104 create table t2 (s date, e date, period for
105 `abcd123456789012345678901234567890123456789012345678901234567890`
106 (s,e));
107 drop table t2;
108
109
110 --echo # MDEV-32205 Server crashes in get_schema_key_period_usage_record on
111 --echo # server without InnoDB
112
113 --echo # Make sure innodb id disabled, but there's at least one innodb table
114 --disable_warnings
115 select "yes" from information_schema.tables where engine="innodb" limit 1;
116 select plugin_status from information_schema.all_plugins where plugin_name = "innodb";
117 select * from information_schema.periods;
118 select * from information_schema.key_period_usage;
119 --enable_warnings
120
121 --echo # [DUPLICATE] MDEV-32204 Server crashes in
122 --echo # get_schema_key_period_usage_record
123
124 create table t (a date) engine=myisam;
125 create table t1 (a int) engine=merge union = (t) ;
126
127 --sorted_result
128 select 1 from information_schema.key_period_usage;
129 drop table t1;
130 drop table t;
131
132 create view v1 as select 1;
133 create view v2 as select * from v1;
134 drop view v1;
135 --sorted_result
136 select * from information_schema.key_period_usage;
137 drop view v2;