Debian Codegraph @

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

raw
1 create or replace table t(id int, s date, e date,
2 period for p(s,e),
3 primary key(id, p without overlaps)
4 ) partition by key (id);
5 show create table t;
6 Table Create Table
7 t CREATE TABLE `t` (
8 `id` int(11) NOT NULL,
9 `s` date NOT NULL,
10 `e` date NOT NULL,
11 PERIOD FOR `p` (`s`, `e`),
12 PRIMARY KEY (`id`,`p` WITHOUT OVERLAPS)
13 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
14 PARTITION BY KEY (`id`)
15 select * from information_schema.key_period_usage;
16 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD_NAME
17 def test PRIMARY def test t p
18 create or replace table t_multi(id int, id2 int, s date, e date,
19 period for very_interesting_period(s,e),
20 primary key(id, very_interesting_period without overlaps),
21 unique(id2, very_interesting_period without overlaps)
22 );
23 select * from information_schema.key_period_usage;
24 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD_NAME
25 def test PRIMARY def test t p
26 def test PRIMARY def test t_multi very_interesting_period
27 def test id2 def test t_multi very_interesting_period
28 drop table t_multi;
29 insert into t values (1, '2003-01-01', '2003-03-01'),
30 (1, '2003-05-01', '2003-07-01');
31 insert into t values (1, '2003-02-01', '2003-04-01');
32 ERROR 23000: Duplicate entry '1-2003-04-01-2003-02-01' for key 'PRIMARY'
33 insert into t values (1, '2003-04-01', '2003-06-01');
34 ERROR 23000: Duplicate entry '1-2003-06-01-2003-04-01' for key 'PRIMARY'
35 insert into t values (1, '2003-05-15', '2003-06-15');
36 ERROR 23000: Duplicate entry '1-2003-06-15-2003-05-15' for key 'PRIMARY'
37 insert into t values (1, '2003-04-01', '2003-08-01');
38 ERROR 23000: Duplicate entry '1-2003-08-01-2003-04-01' for key 'PRIMARY'
39 insert into t values (1, '2003-03-01', '2003-05-01');
40 # expand/shrink period
41 update t set s= '2002-12-01' where s = '2003-01-01';
42 update t set s= '2003-01-01' where s = '2002-12-01';
43 update t set e= '2003-08-01' where s = '2003-05-01';
44 update t set e= '2003-07-01' where s = '2003-05-01';
45 # move left/right
46 update t set s= '2002-12-15', e= '2003-01-15' where s = '2003-01-01';
47 update t set s= '2003-01-01', e= '2003-02-01' where s = '2002-12-15';
48 # diminish/enlarge
49 update t set s= '2003-01-10', e= '2003-01-20' where s = '2003-01-01';
50 update t set s= '2003-01-01', e= '2003-02-01' where s = '2003-01-10';
51 select * from t;
52 id s e
53 1 2003-01-01 2003-02-01
54 1 2003-03-01 2003-05-01
55 1 2003-05-01 2003-07-01
56 # intersect left/right, strict inclusion/containment
57 update t set e= '2003-04-01' where s = '2003-01-01';
58 ERROR 23000: Duplicate entry '1-2003-04-01-2003-01-01' for key 'PRIMARY'
59 update t set s= '2003-04-01' where s = '2003-05-01';
60 ERROR 23000: Duplicate entry '1-2003-07-01-2003-04-01' for key 'PRIMARY'
61 update t set s= '2003-03-10', e= '2003-03-20' where s = '2003-01-01';
62 ERROR 23000: Duplicate entry '1-2003-03-20-2003-03-10' for key 'PRIMARY'
63 update t set s= '2003-04-01', e= '2003-08-01' where s = '2003-03-01';
64 ERROR 23000: Duplicate entry '1-2003-08-01-2003-04-01' for key 'PRIMARY'
65 # inclusion/containment with partial match
66 update t set s= '2003-03-01', e= '2003-04-01' where s = '2003-01-01';
67 ERROR 23000: Duplicate entry '1-2003-04-01-2003-03-01' for key 'PRIMARY'
68 update t set s= '2003-04-01', e= '2003-05-01' where s = '2003-01-01';
69 ERROR 23000: Duplicate entry '1-2003-05-01-2003-04-01' for key 'PRIMARY'
70 update t set s= '2003-03-01' where s = '2003-05-01';
71 ERROR 23000: Duplicate entry '1-2003-07-01-2003-03-01' for key 'PRIMARY'
72 update t set e= '2003-05-01' where s = '2003-01-01';
73 ERROR 23000: Duplicate entry '1-2003-05-01-2003-01-01' for key 'PRIMARY'
74 select * from t where year(s) = 2003;
75 id s e
76 1 2003-01-01 2003-02-01
77 1 2003-03-01 2003-05-01
78 1 2003-05-01 2003-07-01
79 # UPDATE ... FOR PORTION test
80 insert t values (2, '2003-04-15', '2003-05-01');
81 update t for portion of p from '2003-01-01' to '2003-01-15'
82 set id= 2;
83 select * from t;
84 id s e
85 1 2003-01-15 2003-02-01
86 1 2003-03-01 2003-05-01
87 1 2003-05-01 2003-07-01
88 2 2003-01-01 2003-01-15
89 2 2003-04-15 2003-05-01
90 update t for portion of p from '2003-01-15' to '2003-02-01'
91 set id= 2;
92 select * from t;
93 id s e
94 1 2003-03-01 2003-05-01
95 1 2003-05-01 2003-07-01
96 2 2003-01-01 2003-01-15
97 2 2003-01-15 2003-02-01
98 2 2003-04-15 2003-05-01
99 # Next, test UPDATE ... FOR PORTION resulting with an error
100 # Since MyISAM/Aria engines lack atomicity, the results would differ with
101 # innodb. So a table is going to be copied to one with explicit engine.
102 create table t_myisam (id int, s date, e date,
103 period for p(s,e),
104 primary key(id, p without overlaps))
105 engine=myisam
106 select * from t;
107 update t_myisam for portion of p from '2003-04-01' to '2003-06-01'
108 set id= 2 order by s desc;
109 ERROR 23000: Duplicate entry '2-2003-05-01-2003-04-01' for key 'PRIMARY'
110 select * from t_myisam;
111 id s e
112 1 2003-03-01 2003-05-01
113 1 2003-06-01 2003-07-01
114 2 2003-01-01 2003-01-15
115 2 2003-01-15 2003-02-01
116 2 2003-04-15 2003-05-01
117 2 2003-05-01 2003-06-01
118 create table t_innodb (id int, s date, e date,
119 period for p(s,e),
120 primary key(id, p without overlaps))
121 engine=innodb
122 select * from t;
123 update t_innodb for portion of p from '2003-04-01' to '2003-06-01'
124 set id= 2 order by s desc;
125 ERROR 23000: Duplicate entry '2-2003-05-01-2003-04-01' for key 'PRIMARY'
126 select * from t_innodb;
127 id s e
128 1 2003-03-01 2003-05-01
129 1 2003-05-01 2003-07-01
130 2 2003-01-01 2003-01-15
131 2 2003-01-15 2003-02-01
132 2 2003-04-15 2003-05-01
133 drop table t_myisam, t_innodb;
134 create or replace table t(id int, s date, e date,
135 period for p(s,e),
136 primary key(id, q without overlaps));
137 ERROR HY000: Period `q` is not found in table
138 create or replace table t(id int, s date, e date,
139 primary key(id, p without overlaps));
140 ERROR HY000: Period `p` is not found in table
141 create or replace table t(id int, s date, e date,
142 period for p(s,e),
143 primary key(id, s, p without overlaps));
144 ERROR HY000: Key `PRIMARY` cannot explicitly include column `s`
145 create or replace table t(id int, s date, e date,
146 period for p(s,e),
147 primary key(id));
148 insert into t values (1, '2003-03-01', '2003-05-01');
149 insert into t values (1, '2003-04-01', '2003-05-01');
150 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
151 create or replace table t(id int, u int, s date, e date,
152 period for p(s,e),
153 primary key(id, p without overlaps),
154 unique(u));
155 show create table t;
156 Table Create Table
157 t CREATE TABLE `t` (
158 `id` int(11) NOT NULL,
159 `u` int(11) DEFAULT NULL,
160 `s` date NOT NULL,
161 `e` date NOT NULL,
162 PERIOD FOR `p` (`s`, `e`),
163 PRIMARY KEY (`id`,`p` WITHOUT OVERLAPS),
164 UNIQUE KEY `u` (`u`)
165 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
166 insert into t values (1, 1, '2003-03-01', '2003-05-01');
167 insert into t values (1, 2, '2003-05-01', '2003-07-01');
168 insert into t values (1, 3, '2003-04-01', '2003-05-01');
169 ERROR 23000: Duplicate entry '1-2003-05-01-2003-04-01' for key 'PRIMARY'
170 create or replace table t(id int, u int, s date, e date,
171 period for p(s,e),
172 primary key(id, p without overlaps),
173 unique(u, p without overlaps));
174 show create table t;
175 Table Create Table
176 t CREATE TABLE `t` (
177 `id` int(11) NOT NULL,
178 `u` int(11) DEFAULT NULL,
179 `s` date NOT NULL,
180 `e` date NOT NULL,
181 PERIOD FOR `p` (`s`, `e`),
182 PRIMARY KEY (`id`,`p` WITHOUT OVERLAPS),
183 UNIQUE KEY `u` (`u`,`p` WITHOUT OVERLAPS)
184 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
185 insert into t values (2, NULL, '2003-03-01', '2003-05-01');
186 insert into t values (2, NULL, '2003-03-01', '2003-05-01');
187 ERROR 23000: Duplicate entry '2-2003-05-01-2003-03-01' for key 'PRIMARY'
188 insert into t values (3, NULL, '2003-03-01', '2003-05-01');
189 insert into t values (1, 1, '2003-03-01', '2003-05-01');
190 insert into t values (1, 2, '2003-05-01', '2003-07-01');
191 insert into t values (4, NULL, '2003-03-01', '2003-05-01');
192 create sequence seq start=5 engine=myisam;
193 update t set id= nextval(seq), u= nextval(seq), s='2003-05-01', e='2003-07-01'
194 where u is NULL;
195 select * from t;
196 id u s e
197 1 1 2003-03-01 2003-05-01
198 1 2 2003-05-01 2003-07-01
199 5 6 2003-05-01 2003-07-01
200 7 8 2003-05-01 2003-07-01
201 9 10 2003-05-01 2003-07-01
202 drop sequence seq;
203 create or replace table t(id int, s date, e date,
204 period for p(s,e));
205 insert into t values (1, '2003-01-01', '2003-03-01'),
206 (1, '2003-05-01', '2003-07-01'),
207 (1, '2003-02-01', '2003-04-01');
208 alter table t add primary key(id, p without overlaps);
209 ERROR 23000: Duplicate entry '1-2003-04-01-2003-02-01' for key 'PRIMARY'
210 # Historical rows are not checked against constraints
211 set @@system_versioning_alter_history= keep;
212 alter table t add system versioning;
213 delete from t;
214 alter table t add primary key(id, p without overlaps);
215 insert into t values (1, '2003-01-01', '2003-03-01'),
216 (1, '2003-03-01', '2003-05-01');
217 # `without overlaps` is not lost on alter table
218 alter table t add y int;
219 show create table t;
220 Table Create Table
221 t CREATE TABLE `t` (
222 `id` int(11) NOT NULL,
223 `s` date NOT NULL,
224 `e` date NOT NULL,
225 `y` int(11) DEFAULT NULL,
226 PERIOD FOR `p` (`s`, `e`),
227 PRIMARY KEY (`id`,`p` WITHOUT OVERLAPS)
228 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
229 alter table t drop y;
230 create or replace table t1 like t;
231 show create table t1;
232 Table Create Table
233 t1 CREATE TABLE `t1` (
234 `id` int(11) NOT NULL,
235 `s` date NOT NULL,
236 `e` date NOT NULL,
237 PERIOD FOR `p` (`s`, `e`),
238 PRIMARY KEY (`id`,`p` WITHOUT OVERLAPS)
239 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
240 create or replace table t1 (x int, s date, e date,
241 period for p(s,e),
242 primary key(x, p without overlaps));
243 alter table t1 partition by key (x);
244 create or replace table t1 (x int, s date, e date, period for p (s, e))
245 partition by hash (x);
246 alter table t1 add primary key (x, p without overlaps);
247 create or replace table t2 (x int, s date, e date,
248 period for p (s, e),
249 key(x, p without overlaps));
250 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'without overlaps))' at line 3
251 create or replace table t2 (x int, s date, e date,
252 period for p (s, e),
253 unique(x, p without overlaps, x, p without overlaps));
254 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' x, p without overlaps))' at line 3
255 create or replace table t1 (x varchar(100), s date, e date,
256 period for p(s,e),
257 primary key(x, p without overlaps));
258 create or replace table t1 (x varchar(100) compressed, s date, e date,
259 period for p(s,e),
260 primary key(x, p without overlaps));
261 ERROR HY000: Compressed column 'x' can't be used in key specification
262 create or replace table t (x int, s date, e date, period for apptime(s,e),
263 unique(x, apptime without overlaps) using hash);
264 ERROR HY000: Key `x` cannot have WITHOUT OVERLAPS
265 create or replace table t (x int, s date, e date, period for apptime(s,e),
266 b blob, unique(x, b, apptime without overlaps));
267 ERROR HY000: Key `x` cannot have WITHOUT OVERLAPS
268 create or replace table t (x int, s date, e date, b blob unique,
269 period for apptime(s,e),
270 unique(x, apptime without overlaps));
271 insert into t values (1, '2020-03-01', '2020-03-05', 'test');
272 insert into t values (1, '2020-03-05', '2020-03-10', 'test');
273 ERROR 23000: Duplicate entry 'test' for key 'b'
274 insert into t values (1, '2020-03-05', '2020-03-10', 'test2');
275 insert into t values (1, '2020-03-03', '2020-03-10', 'test3');
276 ERROR 23000: Duplicate entry '1-2020-03-10-2020-03-03' for key 'x'
277 create or replace table t (x int, s date, e date, period for apptime(s,e),
278 unique(x, apptime without overlaps));
279 replace into t values (1, '2020-03-03', '2020-03-10');
280 ERROR 42000: This version of MariaDB doesn't yet support 'WITHOUT OVERLAPS'
281 insert into t values (1, '2020-03-03', '2020-03-10')
282 on duplicate key update x = 2;
283 ERROR 42000: This version of MariaDB doesn't yet support 'WITHOUT OVERLAPS'
284 select * from t;
285 x s e
286 select * into outfile 'tmp_t.txt' from t;
287 load data infile 'tmp_t.txt' into table t;
288 load data infile 'tmp_t.txt' replace into table t;
289 ERROR 42000: This version of MariaDB doesn't yet support 'WITHOUT OVERLAPS'
290 insert into t values (1, '2020-03-01', '2020-03-05');
291 select * into outfile 'tmp_t.txt' from t;
292 load data infile 'tmp_t.txt' into table t;
293 ERROR 23000: Duplicate entry '1-2020-03-05-2020-03-01' for key 'x'
294 load data infile 'tmp_t.txt' ignore into table t;
295 Warnings:
296 Warning 1062 Duplicate entry '1-2020-03-05-2020-03-01' for key 'x'
297 load data infile 'tmp_t.txt' replace into table t;
298 ERROR 42000: This version of MariaDB doesn't yet support 'WITHOUT OVERLAPS'
299 # MDEV-22599 WITHOUT OVERLAPS does not work with prefix indexes
300 create or replace table t1 (a varchar(6), s timestamp, e timestamp,
301 period for p(s,e),
302 unique(a(3), p without overlaps));
303 insert into t1 values ('foo', '2012-01-01', '2015-12-31');
304 insert into t1 values ('foobar', '2013-01-01', '2014-01-01');
305 ERROR 23000: Duplicate entry 'foo-2014-01-01 00:00:00-2013-01-01 00:00:00' for key 'a'
306 insert into t1 values ('bar', '2012-01-01', '2015-12-31'),
307 ('baz', '2013-01-01', '2014-01-01');
308 select * from t1;
309 a s e
310 foo 2012-01-01 00:00:00 2015-12-31 00:00:00
311 bar 2012-01-01 00:00:00 2015-12-31 00:00:00
312 baz 2013-01-01 00:00:00 2014-01-01 00:00:00
313 # MDEV-22753 Server crashes in handler::ha_check_overlaps or error 190
314 # "Incompatible key or row definition" upon INSERT into versioned
315 # partitioned table with WITHOUT OVERLAPS
316 create or replace table t1 (f int, s date, e date, period for p(s,e),
317 unique(f, p without overlaps)
318 ) engine=innodb with system versioning
319 partition by system_time limit 1000
320 (partition p1 history, partition pn current);
321 alter table t1 add partition (partition p2 history);
322 show create table t1;
323 Table Create Table
324 t1 CREATE TABLE `t1` (
325 `f` int(11) DEFAULT NULL,
326 `s` date NOT NULL,
327 `e` date NOT NULL,
328 PERIOD FOR `p` (`s`, `e`),
329 UNIQUE KEY `f` (`f`,`p` WITHOUT OVERLAPS)
330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
331 PARTITION BY SYSTEM_TIME LIMIT 1000
332 (PARTITION `p1` HISTORY ENGINE = InnoDB,
333 PARTITION `p2` HISTORY ENGINE = InnoDB,
334 PARTITION `pn` CURRENT ENGINE = InnoDB)
335 insert into t1 values (1,'2013-01-12','2015-11-04'),
336 (2,'2016-03-15','2024-11-09');
337 # MDEV-22714 Assertion `index->table->is_instant()' failed upon
338 # multi-update on table with WITHOUT OVERLAPS
339 create or replace table t (a int);
340 insert into t values (0),(null),(0);
341 create or replace table t1 (f int, s date, e date, period for p(s,e),
342 unique(f, p without overlaps));
343 insert into t1 values (0,'2026-02-12','2036-09-16'),
344 (null,'2025-03-09','2032-12-05');
345 update ignore t join t1 set f = a;
346 # MDEV-22639 Assertion `inited != NONE' failed in
347 # handler::ha_check_overlaps upon multi-table update
348 create or replace table t (f int, s date, e date, period for p(s,e),
349 unique(f, p without overlaps)) engine=myisam;
350 insert into t values (1,'1988-08-25','2024-03-06');
351 create or replace table t1 (a int) engine=myisam;
352 insert into t1 values (1),(2);
353 update t join t1 set s = '2020-01-01';
354 # MDEV-22608 ASAN use-after-poison in TABLE::check_period_overlaps
355 create or replace table t1 (s date, e date, b bit, period for p(s,e),
356 unique(b, p without overlaps)) engine=myisam;
357 insert into t1 values ('2024-12-21','2034-06-29',0),
358 ('2024-12-21','2034-06-29',1);
359 update t1 set b = 1;
360 ERROR 23000: Duplicate entry '\x01-2034-06-29-2024-12-21' for key 'b'
361 # MDEV-22677 Server crashes in ha_partition::open upon update on
362 # partitioned HEAP table with WITHOUT OVERLAPS
363 create or replace table t (id int, s date, e date, period for p(s,e),
364 primary key(id, p without overlaps)
365 ) engine=heap partition by hash(id);
366 update t set id = 1;
367 drop table t, t1;
368 #
369 # MDEV-30415 PERIOD false positive overlap wtih utf8mb4_unicode_nopad_ci
370 #
371 CREATE TABLE t1 (
372 datetime_column_name_1 DATETIME(6) NOT NULL,
373 datetime_column_name_2 DATETIME(6) NOT NULL,
374 text_column_name TEXT COLLATE utf8mb4_unicode_nopad_ci NOT NULL,
375 PERIOD FOR period_name (datetime_column_name_1, datetime_column_name_2),
376 UNIQUE KEY index_name (text_column_name(191),period_name WITHOUT OVERLAPS)
377 ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_nopad_ci;
378 INSERT INTO t1 (datetime_column_name_1, datetime_column_name_2, text_column_name)
379 VALUES
380 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc'),
381 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc ');
382 TRUNCATE TABLE t1;
383 INSERT INTO t1 (datetime_column_name_1, datetime_column_name_2, text_column_name)
384 VALUES
385 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc '),
386 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc');
387 DROP TABLE t1;
388 CREATE TABLE `t1` (
389 datetime_column_name_1 DATETIME(6) NOT NULL,
390 datetime_column_name_2 DATETIME(6) NOT NULL,
391 text_column_name TEXT COLLATE utf8mb4_unicode_nopad_ci NOT NULL,
392 PERIOD FOR period_name (datetime_column_name_1, datetime_column_name_2),
393 UNIQUE KEY index_name (text_column_name(191),period_name WITHOUT OVERLAPS)
394 ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_nopad_ci;
395 INSERT INTO t1 VALUES
396 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'def '),
397 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'def');
398 TRUNCATE TABLE t1;
399 INSERT INTO t1 VALUES
400 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'def'),
401 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'def '),
402 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'def ');
403 SELECT *, LENGTH(text_column_name) FROM t1;
404 datetime_column_name_1 datetime_column_name_2 text_column_name LENGTH(text_column_name)
405 2000-01-01 00:00:00.000000 2001-01-01 00:00:00.000000 def 3
406 2000-01-01 00:00:00.000000 2001-01-01 00:00:00.000000 def 4
407 2000-01-01 00:00:00.000000 2001-01-01 00:00:00.000000 def 5
408 DROP TABLE t1;
409 CREATE TABLE t1 (
410 datetime_column_name_1 DATETIME(6) NOT NULL,
411 datetime_column_name_2 DATETIME(6) NOT NULL,
412 text_column_name TEXT COLLATE utf8mb4_unicode_nopad_ci NOT NULL,
413 PERIOD FOR period_name (datetime_column_name_1, datetime_column_name_2),
414 UNIQUE KEY index_name (text_column_name(3),period_name WITHOUT OVERLAPS)
415 ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_nopad_ci;
416 INSERT INTO t1 (datetime_column_name_1, datetime_column_name_2, text_column_name)
417 VALUES
418 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc'),
419 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc ');
420 ERROR 23000: Duplicate entry 'abc-2001-01-01 00:00:00.000000-2000-01-01 00:00:00.000000' for key 'index_name'
421 TRUNCATE TABLE t1;
422 INSERT INTO t1 (datetime_column_name_1, datetime_column_name_2, text_column_name)
423 VALUES
424 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc '),
425 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc');
426 ERROR 23000: Duplicate entry 'abc-2001-01-01 00:00:00.000000-2000-01-01 00:00:00.000000' for key 'index_name'
427 DROP TABLE t1;
428 CREATE TABLE t1 (
429 datetime_column_name_1 DATETIME(6) NOT NULL,
430 datetime_column_name_2 DATETIME(6) NOT NULL,
431 char_column_name CHAR(255) COLLATE utf8mb4_unicode_nopad_ci NOT NULL,
432 PERIOD FOR period_name (datetime_column_name_1, datetime_column_name_2),
433 UNIQUE KEY index_name (char_column_name(191),period_name WITHOUT OVERLAPS)
434 ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_nopad_ci;
435 INSERT INTO t1 (datetime_column_name_1, datetime_column_name_2, char_column_name)
436 VALUES
437 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc'),
438 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc ');
439 ERROR 23000: Duplicate entry 'abc-2001-01-01 00:00:00.000000-2000-01-01 00:00:00.000000' for key 'index_name'
440 TRUNCATE TABLE t1;
441 INSERT INTO t1 (datetime_column_name_1, datetime_column_name_2, char_column_name)
442 VALUES
443 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc '),
444 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc');
445 ERROR 23000: Duplicate entry 'abc-2001-01-01 00:00:00.000000-2000-01-01 00:00:00.000000' for key 'index_name'
446 DROP TABLE t1;
447 CREATE TABLE t1 (
448 datetime_column_name_1 DATETIME(6) NOT NULL,
449 datetime_column_name_2 DATETIME(6) NOT NULL,
450 char_column_name CHAR(255) COLLATE utf8mb4_unicode_nopad_ci NOT NULL,
451 PERIOD FOR period_name (datetime_column_name_1, datetime_column_name_2),
452 UNIQUE KEY index_name (char_column_name(3),period_name WITHOUT OVERLAPS)
453 ) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_nopad_ci;
454 INSERT INTO t1 (datetime_column_name_1, datetime_column_name_2, char_column_name)
455 VALUES
456 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc'),
457 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc ');
458 ERROR 23000: Duplicate entry 'abc-2001-01-01 00:00:00.000000-2000-01-01 00:00:00.000000' for key 'index_name'
459 TRUNCATE TABLE t1;
460 INSERT INTO t1 (datetime_column_name_1, datetime_column_name_2, char_column_name)
461 VALUES
462 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc '),
463 ('2000-01-01 00:00:00.000000', '2001-01-01 00:00:00.000000', 'abc');
464 ERROR 23000: Duplicate entry 'abc-2001-01-01 00:00:00.000000-2000-01-01 00:00:00.000000' for key 'index_name'
465 DROP TABLE t1;
466 # MDEV-25370 Update for portion changes autoincrement key in period table
467 create or replace table cars(id int auto_increment,
468 price int, s date, e date,
469 period for p(s,e),
470 primary key(id, p without overlaps));
471 insert into cars(price, s, e) values (1000, '2018-01-01', '2020-01-01');
472 select * from cars;
473 id price s e
474 1 1000 2018-01-01 2020-01-01
475 update cars for portion of p from '2019-01-01' to '2019-12-01' set price= 1100;
476 select * from cars;
477 id price s e
478 1 1000 2018-01-01 2019-01-01
479 1 1000 2019-12-01 2020-01-01
480 1 1100 2019-01-01 2019-12-01
481 delete from cars for portion of p from '2019-12-10' to '2019-12-20';
482 select * from cars;
483 id price s e
484 1 1000 2018-01-01 2019-01-01
485 1 1000 2019-12-01 2019-12-10
486 1 1000 2019-12-20 2020-01-01
487 1 1100 2019-01-01 2019-12-01
488 # AUTO_INCREMENT field is separate from WITHOUT OVERLAPS
489 create or replace table cars(id int primary key auto_increment,
490 car_id int,
491 price int, s date, e date,
492 period for p(s,e),
493 unique(car_id, p without overlaps));
494 insert cars(car_id, price, s, e) values (1, 1000, '2018-01-01', '2020-01-01');
495 select * from cars;
496 id car_id price s e
497 1 1 1000 2018-01-01 2020-01-01
498 update cars for portion of p from '2019-01-01' to '2019-12-01' set price= 1100;
499 select * from cars;
500 id car_id price s e
501 1 1 1100 2019-01-01 2019-12-01
502 2 1 1000 2018-01-01 2019-01-01
503 3 1 1000 2019-12-01 2020-01-01
504 delete from cars for portion of p from '2019-12-10' to '2019-12-20';
505 select * from cars;
506 id car_id price s e
507 1 1 1100 2019-01-01 2019-12-01
508 2 1 1000 2018-01-01 2019-01-01
509 4 1 1000 2019-12-01 2019-12-10
510 5 1 1000 2019-12-20 2020-01-01
511 # AUTO_INCREMENT field is both standalone and in WITHOUT OVERLAPS
512 create or replace table cars(id int primary key auto_increment,
513 price int, s date, e date,
514 period for p(s,e),
515 unique(id, p without overlaps));
516 insert cars(price, s, e) values (1000, '2018-01-01', '2020-01-01');
517 insert cars(price, s, e) values (1000, '2021-01-01', '2022-01-01');
518 update cars for portion of p from '2019-01-01' to '2019-12-01' set price= 1100;
519 # autoincrement index is: id int primary key
520 # id increments each time.
521 select * from cars;
522 id price s e
523 1 1100 2019-01-01 2019-12-01
524 2 1000 2021-01-01 2022-01-01
525 3 1000 2018-01-01 2019-01-01
526 4 1000 2019-12-01 2020-01-01
527 truncate cars;
528 insert cars(price, s, e) values (1000, '2018-01-01', '2020-01-01');
529 delete from cars for portion of p from '2019-12-10' to '2019-12-20';
530 select * from cars;
531 id price s e
532 2 1000 2018-01-01 2019-12-10
533 3 1000 2019-12-20 2020-01-01
534 create or replace table cars(id int unique auto_increment,
535 price int, s date, e date,
536 period for p(s,e),
537 primary key (id, p without overlaps));
538 insert cars(price, s, e) values (1000, '2018-01-01', '2020-01-01');
539 # autoincrement index is: primary key (id, p without overlaps)
540 # id is not incremented, hence duplication error
541 update cars for portion of p from '2019-01-01' to '2019-12-01' set price= 1100;
542 ERROR 23000: Duplicate entry '1' for key 'id'
543 truncate cars;
544 insert cars(price, s, e) values (1000, '2018-01-01', '2020-01-01');
545 delete from cars for portion of p from '2019-12-10' to '2019-12-20';
546 ERROR 23000: Duplicate entry '1' for key 'id'
547 drop table cars;