Debian Codegraph @

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

raw
1 --source include/have_partition.inc
2
3 set @s= '1992-01-01';
4 set @e= '1999-12-31';
5
6 create table t (s date, e date);
7
8 --echo # period start/end columns are implicit NOT NULL
9 alter table t add period for a(s, e);
10 show create table t;
11
12 create or replace table t (s date, e date);
13 alter table t change s s date, add period for a(s, e);
14 show create table t;
15
16 alter table t add id int;
17 show create table t;
18 alter table t drop id;
19 show create table t;
20
21 --error ER_CONSTRAINT_FAILED
22 insert t values(@e, @s);
23
24 --error ER_PERIOD_CONSTRAINT_DROP
25 alter table t drop constraint a;
26
27 --echo # no-op
28 alter table t drop period if exists for b;
29 --echo # no-op
30 alter table t add period if not exists for a(e, s);
31
32 alter table t drop period if exists for a;
33 --echo # no-op
34 alter table t drop period if exists for a;
35
36 alter table t add period for a(s, e), add period if not exists for a(e, s);
37 show create table t;
38
39 alter table t drop period for a;
40 --echo # Constraint is dropped
41 insert t values(@e, @s);
42
43 --error ER_CANT_DROP_FIELD_OR_KEY
44 alter table t drop period for a;
45 --error ER_CANT_DROP_FIELD_OR_KEY
46 alter table t add period for a(s, e), drop period for a;
47
48 truncate t;
49 alter table t add period for a(s, e);
50 --error ER_CONSTRAINT_FAILED
51 insert t values(@e, @s);
52 alter table t add period for a(s, e), drop period for a;
53 --error ER_CONSTRAINT_FAILED
54 insert t values(@e, @s);
55 alter table t add s1 date not null, add period for b(s1, e), drop period for a;
56 show create table t;
57 insert t(s, s1, e) values(@e, @s, @e);
58 --error ER_CONSTRAINT_FAILED
59 insert t(s, s1, e) values(@e, @e, @s);
60
61 create table t1 like t;
62 show create table t1;
63 drop table t1;
64
65 --error ER_CONSTRAINT_FAILED
66 create table t2 (period for b(s,e)) select * from t;
67
68 create table t2 (period for b(s1,e)) select * from t;
69 drop table t2;
70
71 --echo # SQL16 11.27 <add table period definition>, Syntax Rules, 5)g)
72 --echo # The declared type of BC1 shall be either DATE or a timestamp type
73 --echo # and shall be equivalent to the declared type of BC2.
74 create or replace table t (s timestamp not null, e timestamp(6) not null);
75 --error ER_PERIOD_TYPES_MISMATCH
76 alter table t add period for a(s, e);
77
78 --echo # SQL16 11.27 <add table period definition>, Syntax Rules, 5)c)
79 --echo # No column of T shall have a column name that is equivalent to ATPN.
80 create or replace table t (a int, s date, e date);
81 --error ER_DUP_FIELDNAME
82 alter table t add period for a(s, e);
83
84 --echo # SQL16 11.27 <add table period definition>, Syntax Rules, 5)i)
85 --echo # Neither BC1 nor BC2 shall be an identity column, a generated column,
86 --echo # a system-time period start column, or a system-time period end column.
87 create or replace table t (id int primary key,
88 s date,
89 e date generated always as (s+1));
90 --error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
91 alter table t add period for a(s, e);
92
93 create or replace table t (id int primary key,
94 s date,
95 e date as (s+1) VIRTUAL);
96 --error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
97 alter table t add period for a(s, e);
98
99 create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
100 st timestamp(6) as row start,
101 en timestamp(6) as row end,
102 period for system_time (st, en)) with system versioning;
103 --error ER_PERIOD_FIELD_WRONG_ATTRIBUTES
104 alter table t add period for a(s, en);
105
106 --echo # SQL16 11.27 <add table period definition>, Syntax Rules, 5)b)
107 --echo # The table descriptor of T shall not include a period descriptor other
108 --echo # than a system-time period descriptor.
109 alter table t add period for a(s, e);
110 --error ER_MORE_THAN_ONE_PERIOD
111 alter table t add period for b(s, e);
112
113 --echo # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)e)v)2)B)
114 --echo # Let S be the schema identified by the explicit or implicit
115 --echo # <schema name> of TN. Let IDCN be an implementation-dependent
116 --echo # <constraint name> that is not equivalent to the <constraint name> of
117 --echo # any table constraint descriptor included in S. The following
118 --echo # <table constraint definition> is implicit:
119 --echo # CONSTRAINT IDCN CHECK ( CN1 < CN2 )
120 --echo #
121 --echo # Due to the above standard limitation, the constraint name can't always
122 --echo # match the period name. So it matches when possible; and when not, it
123 --echo # is unique not taken name prefixed with period name.
124 create or replace table t (x int, s date, e date,
125 period for mytime(s, e));
126 show create table t;
127 alter table t add constraint mytime check (x > 1);
128 show create table t;
129 --error ER_CONSTRAINT_FAILED
130 insert t values (2, @e, @s);
131 alter table t add constraint mytime_1 check (x > 2);
132 --error ER_CONSTRAINT_FAILED
133 insert t values (3, @e, @s);
134
135 drop table t;
136
137 --echo #
138 --echo # MDEV-20494 ER_NOT_FORM_FILE or assertion upon adding partition to period table
139 --echo #
140 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 );
141 lock table t1 write;
142 alter table t1 add partition (partition p2 history);
143 unlock tables;
144
145 create or replace table t1 (x int, s date, e date, period for app(s,e));
146 insert into t1 values(1, '2020-03-01', '2020-03-02');
147 insert into t1 values(1, '2020-03-01', '2020-03-02');
148 --error ER_DUP_ENTRY
149 alter table t1 add primary key(x, s, e);
150 alter table t1 add system versioning;
151
152 # cleanup
153 drop table t1;
154
155 --echo #
156 --echo # MDEV-18873 Server crashes in Compare_identifiers::operator or in
157 --echo # my_strcasecmp_utf8 upon ADD PERIOD IF NOT EXISTS with empty name
158 --echo #
159
160 # When there is no table defined.
161 --error ER_WRONG_COLUMN_NAME
162 alter table t add period if not exists for `` (s,e);
163
164 # When there is an actual table.
165 create table t(s DATE, e DATE);
166 --error ER_WRONG_COLUMN_NAME
167 alter table t add period if not exists for `` (s,e);
168
169 # When the last character is space
170 --error ER_WRONG_COLUMN_NAME
171 alter table t add period if not exists for ` ` (s,e);
172
173 # Create table with an empty period name
174 --error ER_WRONG_COLUMN_NAME
175 create table t2 (period for `` (s,e)) select * from t;
176 drop table t;
177
178 --echo #
179 --echo # MDEV-21941 RENAME doesn't work for system time or period fields
180 --echo #
181 create or replace table t1 (
182 a int, s date, e date,
183 period for mytime(s, e));
184
185 alter table t1 rename column s to x;
186 alter table t1 rename column e to y;
187
188 show create table t1;
189 # cleanup
190 drop table t1;
191
192 --echo # End of 10.5 tests