Debian Codegraph @

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

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