Debian Codegraph @

mysql-test/suite/period/r/alter.result

raw
1 set @s= '1992-01-01';
2 set @e= '1999-12-31';
3 create table t (s date, e date);
4 # period start/end columns are implicit NOT NULL
5 alter table t add period for a(s, e);
6 show create table t;
7 Table Create Table
8 t CREATE TABLE `t` (
9 `s` date NOT NULL,
10 `e` date NOT NULL,
11 PERIOD FOR `a` (`s`, `e`)
12 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
13 create or replace table t (s date, e date);
14 alter table t change s s date, add period for a(s, e);
15 show create table t;
16 Table Create Table
17 t CREATE TABLE `t` (
18 `s` date NOT NULL,
19 `e` date NOT NULL,
20 PERIOD FOR `a` (`s`, `e`)
21 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
22 alter table t add id int;
23 show create table t;
24 Table Create Table
25 t CREATE TABLE `t` (
26 `s` date NOT NULL,
27 `e` date NOT NULL,
28 `id` int(11) DEFAULT NULL,
29 PERIOD FOR `a` (`s`, `e`)
30 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
31 alter table t drop id;
32 show create table t;
33 Table Create Table
34 t CREATE TABLE `t` (
35 `s` date NOT NULL,
36 `e` date NOT NULL,
37 PERIOD FOR `a` (`s`, `e`)
38 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
39 insert t values(@e, @s);
40 ERROR 23000: CONSTRAINT `a` failed for `test`.`t`
41 alter table t drop constraint a;
42 ERROR HY000: Can't DROP CONSTRAINT `a`. Use DROP PERIOD `a` for this
43 # no-op
44 alter table t drop period if exists for b;
45 Warnings:
46 Note 1091 Can't DROP PERIOD `b`; check that it exists
47 # no-op
48 alter table t add period if not exists for a(e, s);
49 Warnings:
50 Note 1060 Duplicate column name 'a'
51 alter table t drop period if exists for a;
52 # no-op
53 alter table t drop period if exists for a;
54 Warnings:
55 Note 1091 Can't DROP PERIOD `a`; check that it exists
56 alter table t add period for a(s, e), add period if not exists for a(e, s);
57 show create table t;
58 Table Create Table
59 t CREATE TABLE `t` (
60 `s` date NOT NULL,
61 `e` date NOT NULL,
62 PERIOD FOR `a` (`s`, `e`)
63 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
64 alter table t drop period for a;
65 # Constraint is dropped
66 insert t values(@e, @s);
67 alter table t drop period for a;
68 ERROR 42000: Can't DROP PERIOD `a`; check that it exists
69 alter table t add period for a(s, e), drop period for a;
70 ERROR 42000: Can't DROP PERIOD `a`; check that it exists
71 truncate t;
72 alter table t add period for a(s, e);
73 insert t values(@e, @s);
74 ERROR 23000: CONSTRAINT `a` failed for `test`.`t`
75 alter table t add period for a(s, e), drop period for a;
76 insert t values(@e, @s);
77 ERROR 23000: CONSTRAINT `a` failed for `test`.`t`
78 alter table t add s1 date not null, add period for b(s1, e), drop period for a;
79 show create table t;
80 Table Create Table
81 t CREATE TABLE `t` (
82 `s` date NOT NULL,
83 `e` date NOT NULL,
84 `s1` date NOT NULL,
85 PERIOD FOR `b` (`s1`, `e`)
86 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
87 insert t(s, s1, e) values(@e, @s, @e);
88 insert t(s, s1, e) values(@e, @e, @s);
89 ERROR 23000: CONSTRAINT `b` failed for `test`.`t`
90 create table t1 like t;
91 show create table t1;
92 Table Create Table
93 t1 CREATE TABLE `t1` (
94 `s` date NOT NULL,
95 `e` date NOT NULL,
96 `s1` date NOT NULL,
97 PERIOD FOR `b` (`s1`, `e`)
98 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
99 drop table t1;
100 create table t2 (period for b(s,e)) select * from t;
101 ERROR 23000: CONSTRAINT `b` failed for `test`.`t2`
102 create table t2 (period for b(s1,e)) select * from t;
103 drop table t2;
104 # SQL16 11.27 <add table period definition>, Syntax Rules, 5)g)
105 # The declared type of BC1 shall be either DATE or a timestamp type
106 # and shall be equivalent to the declared type of BC2.
107 create or replace table t (s timestamp not null, e timestamp(6) not null);
108 alter table t add period for a(s, e);
109 ERROR HY000: Fields of PERIOD FOR `a` have different types
110 # SQL16 11.27 <add table period definition>, Syntax Rules, 5)c)
111 # No column of T shall have a column name that is equivalent to ATPN.
112 create or replace table t (a int, s date, e date);
113 alter table t add period for a(s, e);
114 ERROR 42S21: Duplicate column name 'a'
115 # SQL16 11.27 <add table period definition>, Syntax Rules, 5)i)
116 # Neither BC1 nor BC2 shall be an identity column, a generated column,
117 # a system-time period start column, or a system-time period end column.
118 create or replace table t (id int primary key,
119 s date,
120 e date generated always as (s+1));
121 alter table t add period for a(s, e);
122 ERROR HY000: Period field `e` cannot be GENERATED ALWAYS AS
123 create or replace table t (id int primary key,
124 s date,
125 e date as (s+1) VIRTUAL);
126 alter table t add period for a(s, e);
127 ERROR HY000: Period field `e` cannot be GENERATED ALWAYS AS
128 create or replace table t (id int primary key, s timestamp(6), e timestamp(6),
129 st timestamp(6) as row start,
130 en timestamp(6) as row end,
131 period for system_time (st, en)) with system versioning;
132 alter table t add period for a(s, en);
133 ERROR HY000: Period field `en` cannot be GENERATED ALWAYS AS
134 # SQL16 11.27 <add table period definition>, Syntax Rules, 5)b)
135 # The table descriptor of T shall not include a period descriptor other
136 # than a system-time period descriptor.
137 alter table t add period for a(s, e);
138 alter table t add period for b(s, e);
139 ERROR HY000: Cannot specify more than one application-time period
140 # SQL16, Part 2, 11.3 <table definition>, Syntax Rules, 2)e)v)2)B)
141 # Let S be the schema identified by the explicit or implicit
142 # <schema name> of TN. Let IDCN be an implementation-dependent
143 # <constraint name> that is not equivalent to the <constraint name> of
144 # any table constraint descriptor included in S. The following
145 # <table constraint definition> is implicit:
146 # CONSTRAINT IDCN CHECK ( CN1 < CN2 )
147 #
148 # Due to the above standard limitation, the constraint name can't always
149 # match the period name. So it matches when possible; and when not, it
150 # is unique not taken name prefixed with period name.
151 create or replace table t (x int, s date, e date,
152 period for mytime(s, e));
153 show create table t;
154 Table Create Table
155 t CREATE TABLE `t` (
156 `x` int(11) DEFAULT NULL,
157 `s` date NOT NULL,
158 `e` date NOT NULL,
159 PERIOD FOR `mytime` (`s`, `e`)
160 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
161 alter table t add constraint mytime check (x > 1);
162 show create table t;
163 Table Create Table
164 t CREATE TABLE `t` (
165 `x` int(11) DEFAULT NULL,
166 `s` date NOT NULL,
167 `e` date NOT NULL,
168 PERIOD FOR `mytime` (`s`, `e`),
169 CONSTRAINT `mytime` CHECK (`x` > 1)
170 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
171 insert t values (2, @e, @s);
172 ERROR 23000: CONSTRAINT `mytime_1` failed for `test`.`t`
173 alter table t add constraint mytime_1 check (x > 2);
174 insert t values (3, @e, @s);
175 ERROR 23000: CONSTRAINT `mytime_2` failed for `test`.`t`
176 drop table t;
177 #
178 # MDEV-20494 ER_NOT_FORM_FILE or assertion upon adding partition to period table
179 #
180 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 );
181 lock table t1 write;
182 alter table t1 add partition (partition p2 history);
183 Warnings:
184 Warning 4115 Maybe missing parameters: no rotation condition for multiple HISTORY partitions.
185 unlock tables;
186 create or replace table t1 (x int, s date, e date, period for app(s,e));
187 insert into t1 values(1, '2020-03-01', '2020-03-02');
188 insert into t1 values(1, '2020-03-01', '2020-03-02');
189 alter table t1 add primary key(x, s, e);
190 ERROR 23000: Duplicate entry '1-2020-03-01-2020-03-02' for key 'PRIMARY'
191 alter table t1 add system versioning;
192 drop table t1;
193 #
194 # MDEV-18873 Server crashes in Compare_identifiers::operator or in
195 # my_strcasecmp_utf8 upon ADD PERIOD IF NOT EXISTS with empty name
196 #
197 alter table t add period if not exists for `` (s,e);
198 ERROR 42000: Incorrect column name ''
199 create table t(s DATE, e DATE);
200 alter table t add period if not exists for `` (s,e);
201 ERROR 42000: Incorrect column name ''
202 alter table t add period if not exists for ` ` (s,e);
203 ERROR 42000: Incorrect column name ' '
204 create table t2 (period for `` (s,e)) select * from t;
205 ERROR 42000: Incorrect column name ''
206 drop table t;
207 #
208 # MDEV-21941 RENAME doesn't work for system time or period fields
209 #
210 create or replace table t1 (
211 a int, s date, e date,
212 period for mytime(s, e));
213 alter table t1 rename column s to x;
214 alter table t1 rename column e to y;
215 show create table t1;
216 Table Create Table
217 t1 CREATE TABLE `t1` (
218 `a` int(11) DEFAULT NULL,
219 `x` date NOT NULL,
220 `y` date NOT NULL,
221 PERIOD FOR `mytime` (`x`, `y`)
222 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
223 drop table t1;
224 # End of 10.5 tests