Debian Codegraph @

mysql-test/suite/versioning/t/partition.test

raw
1 -- source include/have_partition.inc
2 -- source suite/versioning/common.inc
3 -- source suite/versioning/engines.inc
4 -- source include/have_sequence.inc
5
6 set @save_persistent=@@global.innodb_stats_persistent;
7 set global innodb_stats_persistent= 0;
8
9 call mtr.add_suppression("need more HISTORY partitions");
10
11 --enable_prepare_warnings
12
13 set system_versioning_alter_history=keep;
14 --let $datadir= `select @@datadir`
15 --echo # Check conventional partitioning on temporal tables
16
17 --replace_result $sys_datatype_expl SYS_DATATYPE
18 eval create or replace table t1 (
19 x int,
20 row_start $sys_datatype_expl as row start invisible,
21 row_end $sys_datatype_expl as row end invisible,
22 period for system_time(row_start, row_end))
23 with system versioning
24 partition by range columns (x) (
25 partition p0 values less than (100),
26 partition p1 values less than (1000));
27
28 insert into t1 values (3), (300);
29 select * from t1;
30 select * from t1 partition (p0);
31 select * from t1 partition (p1);
32
33 delete from t1;
34 select * from t1;
35 select * from t1 partition (p0);
36 select * from t1 partition (p1);
37 select * from t1 for system_time all;
38 select * from t1 partition (p0) for system_time all;
39 select * from t1 partition (p1) for system_time all;
40
41 --echo # Engine change native <-> non-native versioning prohibited
42 --replace_result $sys_datatype_expl SYS_DATATYPE $default_engine DEFAULT_ENGINE
43 eval create or replace table t1 (
44 i int,
45 row_start $sys_datatype_expl as row start invisible,
46 row_end $sys_datatype_expl as row end invisible,
47 period for system_time(row_start, row_end))
48 engine=$default_engine
49 with system versioning partition by hash(i);
50 --replace_result $non_default_engine NON_DEFAULT_ENGINE
51 --error ER_VERS_ALTER_ENGINE_PROHIBITED
52 eval alter table t1 engine=$non_default_engine;
53
54
55 --echo ## CREATE TABLE
56
57 --error ER_VERS_NOT_VERSIONED
58 create or replace table t1 (x int)
59 partition by system_time (
60 partition p0 history,
61 partition pn current);
62
63 create or replace table t1 (x int);
64 --error ER_VERS_NOT_VERSIONED
65 alter table t1
66 partition by system_time (
67 partition p0 history,
68 partition pn current);
69
70 --error ER_VERS_WRONG_PARTS
71 create or replace table t1 (x int)
72 with system versioning
73 partition by system_time (
74 partition p0 current);
75
76 --error ER_VERS_WRONG_PARTS
77 create or replace table t1 (x int)
78 with system versioning
79 partition by system_time (
80 partition p0 current,
81 partition p1 current);
82
83 --error ER_VERS_WRONG_PARTS
84 create or replace table t1 (x int)
85 with system versioning
86 partition by system_time (
87 partition p0 history,
88 partition p1 history);
89
90 --error ER_VERS_WRONG_PARTS
91 create or replace table t1 (x int)
92 with system versioning
93 partition by system_time (
94 partition pn current,
95 partition p0 history);
96
97 --error ER_VERS_WRONG_PARTS
98 create or replace table t1 (x int)
99 with system versioning
100 partition by system_time (
101 partition p0,
102 partition pn current);
103
104 create or replace table t1 (x int)
105 with system versioning
106 partition by system_time (
107 partition p0 history,
108 partition pn current);
109
110 --error ER_PARTITION_WRONG_TYPE
111 create or replace table t1 (a int)
112 partition by range (a) (
113 partition p0 history,
114 partition p1 current);
115
116 --error ER_PARTITION_WRONG_TYPE
117 create or replace table t1 (b int)
118 partition by range (a) (
119 partition p0 current,
120 partition p1 history);
121
122
123 --echo ## ALTER TABLE
124
125 --error ER_VERS_WRONG_PARTS
126 alter table t1 add partition (
127 partition p1 current);
128
129 alter table t1 add partition (
130 partition p1 history);
131
132 --replace_result $default_engine DEFAULT_ENGINE
133 show create table t1;
134
135 insert into t1 values (1), (2);
136
137 --error ER_VERS_WRONG_PARTS
138 alter table t1 drop partition pn;
139 alter table t1 drop partition p1;
140 --error ER_VERS_WRONG_PARTS
141 alter table t1 drop partition p0;
142
143 select x from t1;
144
145 --echo # rename works
146 create or replace table t1 (x int) with system versioning
147 partition by system_time;
148 alter table t1 reorganize partition p0 into
149 (partition custom_name history);
150 --replace_result $default_engine DEFAULT_ENGINE
151 show create table t1;
152
153 --echo # merge and split doesn't (MDEV-19938)
154 create or replace table t1 (x int) with system versioning
155 partition by system_time limit 10 partitions 3;
156 --error ER_REORG_HASH_ONLY_ON_SAME_NO
157 alter table t1 reorganize partition p0, p1 into (partition p00 history);
158 --error ER_REORG_HASH_ONLY_ON_SAME_NO
159 alter table t1 reorganize partition p1 into (partition p1 history, partition p2 history);
160
161
162 --echo # Bug tempesta-tech/mariadb#260: incorrect IB partitioning warning
163 create or replace table t1 (x int)
164 with system versioning
165 partition by system_time limit 1;
166 alter table t1 change x big int;
167
168 create or replace table t1 (i int) engine myisam partition by hash(i) partitions 2;
169 --error ER_PARTITION_WRONG_TYPE
170 alter table t1 add partition (partition px history);
171
172
173 --echo ## INSERT, UPDATE, DELETE
174 create or replace table t1 (x int)
175 with system versioning
176 partition by system_time;
177
178 set @now= now(6);
179 insert into t1 values (1);
180 set @str= concat('select x, row_start < @now as A, row_end > @now as B from t1 partition (p0)');
181 prepare select_p0 from @str;
182 --replace_result $sys_time_max SYS_TIME_MAX
183 --eval set @str= concat("select x, row_start > @now as C, row_end = $sys_time_max as D from t1 partition (pn)");
184 prepare select_pn from @str;
185
186 execute select_p0;
187 execute select_pn;
188
189 set @str= concat('select row_start from t1 partition (pn) into @ts0');
190 prepare stmt from @str; execute stmt; drop prepare stmt;
191
192 --source suite/versioning/wait_system_clock.inc
193
194 set @now= now(6);
195 delete from t1;
196 execute select_p0;
197 execute select_pn;
198
199 set @str= concat('select row_start from t1 partition (p0) into @ts1');
200 prepare stmt from @str; execute stmt; drop prepare stmt;
201
202 select @ts0 = @ts1;
203
204 set @now= now(6);
205 insert into t1 values (2);
206
207 --source suite/versioning/wait_system_clock.inc
208
209 execute select_p0;
210 execute select_pn;
211
212 set @str= concat('select row_start from t1 partition (pn) into @ts0');
213 prepare stmt from @str; execute stmt; drop prepare stmt;
214
215 set @now= now(6);
216 update t1 set x = x + 1;
217
218 --source suite/versioning/wait_system_clock.inc
219
220 execute select_p0;
221 execute select_pn;
222
223 drop prepare select_p0;
224 drop prepare select_pn;
225
226 set @str= concat('select row_start from t1 partition (p0) where x = 2 into @ts1');
227 prepare stmt from @str; execute stmt; drop prepare stmt;
228 set @str= concat('select row_end from t1 partition (p0) where x = 2 into @ts2');
229 prepare stmt from @str; execute stmt; drop prepare stmt;
230 set @str= concat('select row_start from t1 partition (pn) into @ts3');
231 prepare stmt from @str; execute stmt; drop prepare stmt;
232
233 select @ts0 = @ts1;
234 select @ts2 = @ts3;
235
236 --echo #
237 --echo # Rotation by LIMIT
238 --echo #
239 --error ER_PART_WRONG_VALUE
240 create or replace table t1 (x int)
241 with system versioning
242 partition by system_time limit 0 partitions 3;
243
244 create or replace table t1 (x int)
245 with system versioning
246 partition by system_time limit 2 partitions 3;
247
248 --replace_result $default_engine DEFAULT_ENGINE
249 show create table t1;
250
251 --error ER_PARTITION_DOES_NOT_EXIST
252 alter table t1 drop partition non_existent;
253
254 insert into t1 values (1), (2), (3), (4), (5), (6);
255 select * from t1 partition (pn);
256 delete from t1 where x < 4;
257 delete from t1;
258 --echo # You see warning above ^
259 select * from t1 partition (p0);
260 select * from t1 partition (p1);
261
262 insert into t1 values (7), (8);
263 --echo ### warn about full partition
264 delete from t1;
265 --echo # You see warning above ^
266 select * from t1 partition (p1) order by x;
267
268 --echo #
269 --echo # Rotation by INTERVAL
270 --echo #
271 --error ER_PART_WRONG_VALUE
272 create or replace table t1 (x int)
273 with system versioning
274 partition by system_time interval 0 second partitions 3;
275
276 --error ER_PARSE_ERROR
277 create table t1 (i int) with system versioning
278 partition by system_time interval 6 day limit 98;
279
280 --error ER_DATA_OUT_OF_RANGE
281 create or replace table t1 (pk int) with system versioning
282 partition by system_time interval 60 year partitions 4;
283
284 --echo # INTERVAL and ALTER TABLE
285 create or replace table t1 (i int) with system versioning
286 partition by system_time interval 1 hour;
287
288 set @ts=(select partition_description from information_schema.partitions
289 where table_schema='test' and table_name='t1' and partition_name='p0');
290
291 alter table t1 add column b int;
292 select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1';
293 alter table t1 add partition (partition p1 history, partition p2 history);
294 select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1';
295 alter table t1 drop partition p0;
296 select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1';
297 --error ER_VERS_DROP_PARTITION_INTERVAL
298 alter table t1 drop partition p2;
299 select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1';
300
301 #
302 # partition rotation (moved from partition_rotation.test)
303 #
304 set timestamp=unix_timestamp('2001-02-03 10:20:30');
305 create or replace table t1 (i int) with system versioning
306 partition by system_time interval 1 day
307 subpartition by key (i) subpartitions 2
308 (partition p1 history, partition pn current);
309 set timestamp=unix_timestamp('2001-02-03 10:20:40');
310 insert t1 values (1); delete from t1;
311 set timestamp=unix_timestamp('2001-02-04 10:20:50');
312 insert t1 values (2); delete from t1;
313
314 select subpartition_name, partition_description, table_rows from information_schema.partitions where table_schema='test' and table_name='t1';
315 select * from t1 partition (p1);
316
317 set timestamp=unix_timestamp('2001-02-04 10:20:55');
318 alter table t1 add partition (partition p0 history, partition p2 history);
319 set timestamp=unix_timestamp('2001-02-04 10:30:00');
320 insert t1 values (4),(5);
321 set timestamp=unix_timestamp('2001-02-04 10:30:10');
322 update t1 set i=6 where i=5;
323
324 select subpartition_name, partition_description, table_rows from information_schema.partitions where table_schema='test' and table_name='t1';
325 select * from t1 partition (p1);
326 select * from t1 partition (p0);
327 select * from t1 partition (p2);
328
329 alter table t1 rebuild partition p0, p1, p2;
330 select * from t1 partition (p1);
331 select * from t1 partition (p0);
332 select * from t1 partition (p2);
333
334 --echo ## pruning check
335 set @ts=(select partition_description from information_schema.partitions
336 where table_schema='test' and table_name='t1' and partition_name='p0' limit 1);
337 --sorted_result
338 select * from t1;
339 --replace_column 10 #
340 explain partitions select * from t1;
341 --replace_column 10 #
342 explain partitions select * from t1 for system_time as of '2001-02-04 10:20:30';
343 set @ts=(select row_end from t1 for system_time all where i=1);
344 select * from t1 for system_time all where row_end = @ts;
345 --replace_column 5 # 10 # 11 #
346 explain partitions select * from t1 for system_time all where row_end = @ts;
347
348 --echo #
349 --echo # MDEV-16023 Unfortunate error message WARN_VERS_PART_FULL
350 --echo #
351
352 set timestamp= unix_timestamp('2020-07-29 10:30:10');
353 create or replace table t1 (a int) with system versioning
354 partition by system_time interval 1 second (
355 partition p0 history,
356 partition p1 history,
357 partition pc current
358 );
359
360 set timestamp= unix_timestamp('2020-07-29 10:30:14');
361 insert into t1 values (1),(2),(3);
362 show warnings;
363
364 --echo # Cleanup
365 set timestamp= default;
366
367 --echo ## INTERVAL ... STARTS
368 --error ER_PART_WRONG_VALUE
369 create or replace table t1 (i int) with system versioning
370 partition by system_time interval 1 day starts 'a';
371
372 --error ER_PART_WRONG_VALUE
373 create or replace table t1 (i int) with system versioning
374 partition by system_time interval 1 day starts '00:00:00';
375
376 --error ER_PART_WRONG_VALUE
377 create or replace table t1 (i int) with system versioning
378 partition by system_time interval 1 day starts '2000-00-01 00:00:00';
379
380 --error ER_PART_WRONG_VALUE
381 create or replace table t1 (i int) with system versioning
382 partition by system_time interval 1 day starts 946684800;
383
384 create or replace table t1 (i int) with system versioning
385 partition by system_time interval 1 day starts '2000-01-01 00:00:00';
386 --replace_result $default_engine DEFAULT_ENGINE
387 show create table t1;
388
389 --echo # Test STARTS warning
390 set timestamp= unix_timestamp('2000-01-01 00:00:00');
391 create or replace table t1 (i int) with system versioning
392 partition by system_time interval 1 day;
393 --replace_result $default_engine DEFAULT_ENGINE
394 show create table t1;
395
396 create or replace table t1 (i int) with system versioning
397 partition by system_time interval 1 day starts '2000-01-01 00:00:01';
398
399 --echo # Test default STARTS rounding
400 set timestamp= unix_timestamp('1999-12-15 13:33:33');
401 create or replace table t1 (i int) with system versioning
402 partition by system_time interval 1 second;
403 --replace_result $default_engine DEFAULT_ENGINE
404 show create table t1;
405
406 create or replace table t1 (i int) with system versioning
407 partition by system_time interval 1 minute;
408 --replace_result $default_engine DEFAULT_ENGINE
409 show create table t1;
410
411 create or replace table t1 (i int) with system versioning
412 partition by system_time interval 1 hour;
413 --replace_result $default_engine DEFAULT_ENGINE
414 show create table t1;
415
416 create or replace table t1 (i int) with system versioning
417 partition by system_time interval 1 day;
418 --replace_result $default_engine DEFAULT_ENGINE
419 show create table t1;
420
421 create or replace table t1 (i int) with system versioning
422 partition by system_time interval 1 month;
423 --replace_result $default_engine DEFAULT_ENGINE
424 show create table t1;
425
426 create or replace table t1 (i int) with system versioning
427 partition by system_time interval 1 year;
428 --replace_result $default_engine DEFAULT_ENGINE
429 show create table t1;
430
431 --echo # seconds equivalent of 1 day does not round:
432 create or replace table t1 (i int) with system versioning
433 partition by system_time interval 86400 second;
434 --replace_result $default_engine DEFAULT_ENGINE
435 show create table t1;
436
437 --echo # STARTS value is in local time_zone:
438 set time_zone="+03:00";
439 create or replace table t1 (i int) with system versioning
440 partition by system_time interval 1 day starts '2000-01-01 00:00:00';
441
442 set timestamp= unix_timestamp('2000-01-01 00:00:00');
443 create or replace table t2 (i int) with system versioning
444 partition by system_time interval 1 day;
445
446 --replace_result $default_engine DEFAULT_ENGINE
447 show create table t1;
448 --replace_result $default_engine DEFAULT_ENGINE
449 show create table t2;
450 set time_zone="+00:00";
451 --replace_result $default_engine DEFAULT_ENGINE
452 show create table t1;
453 --replace_result $default_engine DEFAULT_ENGINE
454 show create table t2;
455
456 --echo # Test rotation
457 set timestamp= unix_timestamp('2001-01-01 00:00:00');
458 --echo # it's ok to add partitions for past:
459 create or replace table t1 (i int) with system versioning
460 partition by system_time interval 1 day starts '2000-01-01 00:00:00'
461 partitions 3;
462
463 insert into t1 values (0);
464 set timestamp= unix_timestamp('2001-01-01 00:00:01');
465 update t1 set i= i + 1;
466 set timestamp= unix_timestamp('2001-01-01 00:00:02');
467 update t1 set i= i + 1;
468
469 select *, row_end from t1 partition (p0);
470 select *, row_end from t1 partition (p1);
471
472 set timestamp= unix_timestamp('2000-01-01 00:00:00');
473 --echo # now we "overflow" first partition a bit:
474 create or replace table t1 (i int) with system versioning
475 partition by system_time interval 1 day starts '2000-01-03 00:00:00'
476 partitions 3;
477
478 insert into t1 values (0);
479 set timestamp= unix_timestamp('2000-01-01 00:00:01');
480 update t1 set i= i + 1;
481 set timestamp= unix_timestamp('2000-01-02 00:00:01');
482 update t1 set i= i + 1;
483 set timestamp= unix_timestamp('2000-01-03 00:00:01');
484 update t1 set i= i + 1;
485 set timestamp= unix_timestamp('2000-01-04 00:00:01');
486 update t1 set i= i + 1;
487
488 select *, row_end from t1 partition (p0);
489 select *, row_end from t1 partition (p1);
490
491 --echo # and this is how it usually goes:
492 set timestamp= unix_timestamp('2000-01-01 00:00:00');
493 create or replace table t1 (i int) with system versioning
494 partition by system_time interval 1 day
495 partitions 3;
496
497 insert into t1 values (0);
498 set timestamp= unix_timestamp('2000-01-01 00:00:01');
499 update t1 set i= i + 1;
500 set timestamp= unix_timestamp('2000-01-02 00:00:01');
501 update t1 set i= i + 1;
502 set timestamp= unix_timestamp('2000-01-03 00:00:01');
503 update t1 set i= i + 1;
504 set timestamp= unix_timestamp('2000-01-04 00:00:01');
505 update t1 set i= i + 1;
506
507 alter table t1 add partition (partition p2 history, partition p3 history);
508 select *, row_end from t1 partition (p0);
509 select *, row_end from t1 partition (p1);
510 select *, row_end from t1 partition (p2);
511 select *, row_end from t1 partition (p3);
512
513 drop tables t1, t2;
514
515 --echo ## Subpartitions
516 create or replace table t1 (x int)
517 with system versioning
518 partition by system_time limit 2 partitions 3
519 subpartition by key (x)
520 subpartitions 2;
521
522 insert into t1 (x) values (1), (2), (3), (4), (5);
523 select * from t1 partition (pnsp0);
524 select * from t1 partition (pnsp1);
525
526 delete from t1 where x < 3;
527 delete from t1;
528 --echo # You see warning above ^
529 delete from t1;
530 --echo # You see warning above ^ (no matter if nothing was deleted)
531 select * from t1 partition (p0sp0);
532 select * from t1 partition (p0sp1);
533 select * from t1 partition (p1sp0);
534 select * from t1 partition (p1sp1);
535
536 --echo # check implicit sys fields for implicit engine of partitioned table
537 create or replace table t1 (a bigint)
538 with system versioning
539 partition by range (a)
540 (partition p0 values less than (20) engine innodb,
541 partition p1 values less than maxvalue engine innodb);
542 insert into t1 values (1);
543 select * from t1 partition (p0);
544
545 --echo # check for partition engine
546 create or replace table t1 (
547 f_int1 integer default 0
548 ) with system versioning
549 partition by range(f_int1)
550 subpartition by hash(f_int1)
551 ( partition part1 values less than (1000)
552 (subpartition subpart11 storage engine = 'innodb',
553 subpartition subpart12 storage engine = 'innodb'));
554 insert into t1 values (1);
555 select * from t1 partition (part1);
556
557 --echo #
558 --echo # TRX_ID versioning (moved from partition_innodb.test)
559 --echo #
560 --echo # MDEV-15951 system versioning by trx id doesn't work with partitioning
561 --echo # currently trx_id does not support partitioning by system_time
562 --error ER_VERS_FIELD_WRONG_TYPE
563 create or replace table t1(
564 i int,
565 row_start bigint unsigned generated always as row start,
566 row_end bigint unsigned generated always as row end,
567 period for system_time(row_start, row_end)
568 ) engine=InnoDB with system versioning partition by system_time (
569 partition p0 history,
570 partition pn current
571 );
572
573 create or replace table t1(
574 i int,
575 row_start bigint unsigned generated always as row start,
576 row_end bigint unsigned generated always as row end,
577 period for system_time(row_start, row_end)
578 ) engine=InnoDB with system versioning;
579
580 --error ER_VERS_FIELD_WRONG_TYPE
581 alter table t1 partition by system_time (
582 partition p0 history,
583 partition pn current
584 );
585
586 drop table t1;
587
588 --error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED
589 create or replace table t (
590 a int primary key,
591 row_start bigint unsigned as row start invisible,
592 row_end bigint unsigned as row end invisible,
593 period for system_time(row_start, row_end)
594 ) engine=innodb with system versioning
595 partition by key() (
596 partition p1,
597 partition p2
598 );
599
600 --error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED
601 create or replace table t (
602 a int primary key,
603 row_start bigint unsigned as row start invisible,
604 row_end bigint unsigned as row end invisible,
605 period for system_time(row_start, row_end)
606 ) engine=innodb with system versioning
607 partition by key(a, row_start) (
608 partition p1,
609 partition p2
610 );
611
612 --error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED
613 create or replace table t (
614 a int primary key,
615 row_start bigint unsigned as row start invisible,
616 row_end bigint unsigned as row end invisible,
617 period for system_time(row_start, row_end)
618 ) engine=innodb with system versioning
619 partition by hash(a + row_end * 2) (
620 partition p1,
621 partition p2
622 );
623
624 --error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED
625 create or replace table t (
626 a int primary key,
627 row_start bigint unsigned as row start invisible,
628 row_end bigint unsigned as row end invisible,
629 period for system_time(row_start, row_end)
630 ) engine=innodb with system versioning
631 partition by range columns (a, row_start) (
632 partition p1 values less than (100, 100)
633 );
634
635 --echo #
636 --echo # Assertion in ALTER on warning from partitioning LIMIT [#446]
637 --echo #
638 create or replace table t1 (x int) with system versioning;
639 insert into t1 values (1), (2);
640 delete from t1;
641 alter table t1 partition by system_time limit 1 (
642 partition p1 history,
643 partition pn current);
644
645 --echo #
646 --echo # MDEV-14649 Assertion `t->mysql_col_len == 8' failed in row_insert_for_mysql
647 --echo #
648 create or replace table t1 (i int) engine=innodb partition by key(i);
649 alter table t1 add system versioning;
650 insert into t1 values();
651
652 --echo #
653 --echo # MDEV-14722 Assertion in ha_commit_trans for sub-statement
654 --echo #
655 create or replace table t1 (i int) with system versioning
656 partition by system_time interval 1 day;
657 create or replace table t2 (f int);
658 create or replace trigger tr before insert on t2
659 for each row select table_rows from information_schema.tables
660 where table_name = 't1' into @a;
661 insert into t2 values (1);
662
663 --echo #
664 --echo # MDEV-14740 Locking assertion for system_time partitioning
665 --echo #
666 create or replace table t1 (i int) with system versioning
667 partition by system_time interval 1 week;
668 create or replace table t2 (f int);
669 create or replace trigger tr before insert on t2
670 for each row select count(*) from t1 into @a;
671 insert into t2 values (1);
672
673 --echo #
674 --echo # MDEV-14747 ALTER PARTITION BY SYSTEM_TIME after LOCK TABLES
675 --echo #
676 create or replace table t1 (x int) with system versioning;
677 lock table t1 write;
678 alter table t1 partition by system_time interval 1 week (
679 partition p1 history,
680 partition pn current);
681 unlock tables;
682
683 --echo #
684 --echo # MDEV-14748 Assertion in ha_myisammrg::attach_children()
685 --echo #
686 create or replace table t1 (x int) engine=myisam with system versioning
687 partition by system_time interval 1 month (partition p1 history, partition pn current);
688 create or replace table t2 (x int) engine=myisam;
689 create or replace table t3 (x int) engine=merge union=(t2);
690 create or replace table t4 (x int) engine=myisam;
691 create or replace trigger tr after insert on t4 for each row insert into t2
692 ( select x from t3 ) union ( select x from t1 );
693 insert into t4 values (1);
694
695 --echo #
696 --echo # MDEV-14821 Assertion failure
697 --echo #
698 create or replace table t1 (x int) with system versioning;
699 insert into t1 values (0), (1);
700 update t1 set x= x + 1;
701 alter table t1 partition by system_time limit 1 (
702 partition p1 history,
703 partition p2 history,
704 partition pn current);
705 delete from t1 where x = 1;
706 --echo # You see warning above ^
707 delete from t1 where x = 2;
708 --echo # You see warning above ^
709
710 --echo #
711 --echo # MDEV-14923 Assertion upon INSERT into locked versioned partitioned table
712 --echo #
713 create or replace table t1 (x int) with system versioning
714 partition by system_time;
715 lock table t1 write;
716 --error ER_SAME_NAME_PARTITION
717 alter table t1 add partition (partition p0 history);
718 insert into t1 values (1);
719 unlock tables;
720
721 --echo #
722 --echo # MDEV-15103 Assertion in ha_partition::part_records() for updating VIEW
723 --echo #
724 create or replace table t1 (pk int primary key, f int) with system versioning
725 partition by system_time limit 100;
726 insert into t1 values (1,10), (2,20);
727 create or replace view v1 as select * from t1;
728 update v1 set f= 30;
729
730 --echo #
731 --echo # MDEV-15168 Unexpected ER_VERS_ENGINE_UNSUPPORTED upon dropping versioning on a partitioned table
732 --echo #
733 create or replace table t (a int) with system versioning
734 partition by system_time;
735 --error ER_DROP_VERSIONING_SYSTEM_TIME_PARTITION
736 alter table t drop system versioning;
737
738 --echo #
739 --echo # MDEV-15191 Assertion `bit < (map)->n_bits' failed in bitmap_is_set upon INSERT
740 --echo #
741 create or replace table t1 (i int) with system versioning;
742 insert into t1 values (1), (2);
743 update t1 set i= 3;
744 alter table t1 partition by system_time interval 1 month (partition p1 history, partition pn current);
745 lock table t1 write;
746 alter table t1 add partition (partition p2 history);
747 insert into t1 values (4);
748 unlock tables;
749
750 --echo #
751 --echo # MDEV-15036 Assertion `!is_set() || (m_status == DA_OK_BULK && is_bulk_op())' in Diagnostics_area::set_ok_status or unexpected ER_RANGE_NOT_INCREASING_ERROR
752 --echo #
753 create or replace table t1 (a int) with system versioning
754 partition by system_time limit 2 partitions 4;
755 insert into t1 values (1),(2),(3);
756 update t1 set a = 4;
757 delete from t1;
758 delete from t1 where a is not null;
759
760 --echo #
761 --echo # MDEV-14823 Wrong error message upon selecting from a system_time partition
762 --echo #
763 create or replace table t1 (i int) with system versioning partition by system_time limit 10;
764 --error ER_VERS_QUERY_IN_PARTITION
765 select * from t1 partition (p0) for system_time all;
766 --echo # MDEV-18929 2nd execution of SP does not detect ER_VERS_NOT_VERSIONED
767 create or replace procedure sp()
768 select * from t1 partition (p0) for system_time all;
769 --error ER_VERS_QUERY_IN_PARTITION
770 call sp;
771 --error ER_VERS_QUERY_IN_PARTITION
772 call sp;
773 drop procedure sp;
774
775 --echo #
776 --echo # MDEV-15380 Index for versioned table gets corrupt after partitioning and DELETE
777 --echo #
778 create or replace table t1 (pk int primary key)
779 engine=myisam
780 with system versioning
781 partition by key() partitions 3;
782 set timestamp=1523466002.799571;
783 insert into t1 values (11),(12);
784 set timestamp=1523466004.169435;
785 delete from t1 where pk in (11, 12);
786 --echo Same test but for Aria storage engine
787 create or replace table t1 (pk int primary key)
788 engine=aria
789 with system versioning
790 partition by key() partitions 3;
791 set timestamp=1523466002.799571;
792 insert into t1 values (11),(12);
793 set timestamp=1523466004.169435;
794 delete from t1 where pk in (11, 12);
795
796 --echo #
797 --echo # MDEV-18136 Server crashes in Item_func_dyncol_create::prepare_arguments
798 --echo #
799 create or replace table t1 (pk int) with system versioning
800 partition by system_time interval 7 second;
801 alter table t1
802 partition by system_time interval column_get(column_create(7,7), 7 as int) second (
803 partition ver_p1 history,
804 partition ver_pn current);
805 --replace_result $default_engine DEFAULT_ENGINE
806 show create table t1;
807 set timestamp= default;
808
809 --echo #
810 --echo # MDEV-18794 Assertion `!m_innodb' failed in ha_partition::cmp_ref upon SELECT from partitioned table
811 --echo #
812 create or replace table t1 (pk int auto_increment, i int, c char(1), primary key (pk), key(i))
813 engine=innodb with system versioning partition by key() partitions 2;
814 insert into t1 (i, c) values (1, 'a'), (2, 'b'), (null, 'c'), (null, 'b');
815 alter table t1 drop system versioning;
816 replace into t1 select * from t1;
817 select * from t1 where i > 0 or pk = 1000 limit 1;
818 drop table t1;
819
820 --echo #
821 --echo # MDEV-19175 Server crashes in ha_partition::vers_can_native upon INSERT DELAYED into versioned partitioned table
822 --echo #
823 create or replace table t1 (f int) with system versioning partition by hash(f);
824 # delayed works differently in embedded server
825 --error 0,ER_DELAYED_NOT_SUPPORTED
826 insert delayed into t1 values (1);
827
828 --echo #
829 --echo # MDEV-20068 History partition rotation is not done under LOCK TABLES
830 --echo #
831 create or replace table t1 (x int) with system versioning partition by system_time limit 1
832 (partition p1 history, partition pn current);
833 lock tables t1 write;
834 insert into t1 values (0), (1), (2), (3);
835 delete from t1 where x < 3;
836 --echo # You see warning above ^
837 delete from t1;
838 --echo # You see warning above ^
839 unlock tables;
840
841 --echo #
842 --echo # MDEV-20336 Assertion bitmap_is_set(read_partitions) upon SELECT FOR UPDATE from versioned table
843 --echo #
844 create or replace table t1 (pk int primary key) with system versioning partition by system_time limit 100 (partition p1 history, partition pn current);
845 execute immediate 'select * from t1 for update';
846
847 --echo #
848 --echo # MDEV-19903 Setup default partitions for system versioning
849 --echo #
850 create or replace table t1 (x int) with system versioning partition by system_time;
851 --replace_result $default_engine DEFAULT_ENGINE
852 show create table t1;
853 --echo # 2 partitions are created: p0 and pn
854 select PARTITION_NAME, PARTITION_METHOD, PARTITION_DESCRIPTION from information_schema.partitions where table_name = 't1' order by PARTITION_NAME;
855
856 create or replace table t1 (x int) with system versioning partition by system_time limit 10 partitions 4;
857 --replace_result $default_engine DEFAULT_ENGINE
858 show create table t1;
859 --echo # 4 partitions are created: p0, p1, p2 and pn
860 select PARTITION_NAME, PARTITION_METHOD, PARTITION_DESCRIPTION from information_schema.partitions where table_name = 't1' order by PARTITION_NAME;
861
862 --echo # Test cleanup
863 drop view v1;
864 drop tables t, t1, t2, t3, t4;
865
866 --echo #
867 --echo # MDEV-18957 UPDATE with LIMIT clause is wrong for versioned partitioned tables
868 --echo #
869 create or replace table t1 (
870 x int,
871 a varchar(255)
872 ) with system versioning partition by system_time (partition p1 history, partition pn current);
873
874 insert into t1 (x) values (1), (2), (3), (4);
875 update t1 set a= 'foo' limit 3;
876 update t1 set a= 'bar' limit 4;
877 select * from t1;
878 drop table t1;
879
880 --echo #
881 --echo # MDEV-21011 Table corruption reported for versioned partitioned table after DELETE: "Found a misplaced row"
882 --echo #
883 create table t1 (a int) with system versioning
884 partition by system_time limit 3
885 (partition p1 history, partition p2 history, partition pn current);
886 insert into t1 values (1),(2),(3),(4);
887 delete from t1;
888 delete from t1;
889 check table t1;
890
891 # cleanup
892 drop table t1;
893
894 --echo #
895 --echo # MDEV-21233 Assertion `m_extra_cache' failed in ha_partition::late_extra_cache
896 --echo #
897 create table t1 (id int, a varchar(8)) with system versioning partition by key (id) partitions 2;
898 insert into t1 values (1,'foo'),(2,'bar');
899
900 create table t2 (b int);
901 insert into t2 values (1),(2);
902
903 update t1, t2 set a = 1;
904
905 # cleanup
906 drop table t1, t2;
907
908 --echo #
909 --echo # MDEV-20515 multi-update tries to position updated table by null reference
910 --echo #
911 create or replace table t1 (a int);
912 insert into t1 values (0), (1);
913
914 create or replace table t2 (b int) with system versioning
915 partition by system_time
916 (partition p1 history, partition pn current);
917
918 insert into t2 values (0), (2);
919 update t1 left join t2 on a > b set b= 2 order by b;
920
921 # cleanup
922 drop table t1, t2;
923
924 --echo #
925 --echo # MDEV-17091 Assertion `old_part_id == m_last_part' failed in
926 --echo # ha_partition::update_row or `part_id == m_last_part' in
927 --echo # ha_partition::delete_row upon UPDATE/DELETE after dropping versioning
928 --echo #
929 create or replace table t1 (pk int primary key, f int) engine=innodb
930 with system versioning
931 partition by key() partitions 2;
932 insert into t1 values (1,10),(2,20);
933 --echo # expected to hit same partition
934 select * from t1 partition (p0);
935 alter table t1 drop system versioning;
936
937 --echo # 1 and 2 are expected to be in different partitions
938 select * from t1 partition(p0);
939 select * from t1 partition(p1);
940
941 update t1 set f=pk;
942 delete from t1;
943 drop table t1;
944
945 --echo #
946 --echo # MDEV-22413 Server hangs upon UPDATE/DELETE on a view reading from versioned partitioned table
947 --echo #
948 create or replace table t1 (f char(6)) engine innodb with system versioning;
949
950 insert into t1 values (null);
951 update t1 set f= 'foo';
952 update t1 set f= 'bar';
953 --echo # You see warning above ^
954
955 create or replace view v1 as select * from t1 for system_time all;
956 --error ER_TABLE_NOT_LOCKED_FOR_WRITE
957 update v1 set f = '';
958
959 create or replace table t1 (f char(6)) engine innodb with system versioning
960 partition by system_time limit 1
961 (partition p1 history, partition p2 history, partition pn current);
962
963 insert into t1 values (null);
964 update t1 set f= 'foo';
965 update t1 set f= 'bar';
966
967 create or replace view v1 as select * from t1 for system_time all;
968 --error ER_TABLE_NOT_LOCKED_FOR_WRITE
969 update v1 set f= '';
970 --error ER_TABLE_NOT_LOCKED_FOR_WRITE
971 delete from v1;
972
973 # cleanup
974 drop view v1;
975 drop table t1;
976
977 --echo #
978 --echo # MDEV-22112 Assertion `tab_part_info->part_type == RANGE_PARTITION || tab_part_info->part_type == LIST_PARTITION' failed in prep_alter_part_table
979 --echo #
980
981 create table t1 (a int) with system versioning partition by system_time;
982 drop table t1;
983
984 create table t1 (a int) with system versioning partition by system_time
985 (partition p1 history, partition pn current);
986 --error ER_PARTITION_WRONG_TYPE
987 alter table t1 add partition (partition p2);
988
989 --echo # MDEV-17891 Assertion failures in select_insert::abort_result_set and
990 --echo # mysql_load upon attempt to replace into a full table
991
992 --let $max_heap_table_size_orig= `select @@max_heap_table_size;`
993 set @@max_heap_table_size= 1024*1024;
994 create or replace table t1 (
995 pk integer auto_increment,
996 primary key (pk),
997 f varchar(45000)
998 ) charset=latin1 with system versioning engine=memory
999 partition by system_time interval 1 year (partition p1 history,
1000 partition pn current);
1001
1002 --echo # fill the table until full
1003 insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),();
1004 --error ER_RECORD_FILE_FULL
1005 insert into t1 (f) select f from t1;
1006 --echo # leave space for exactly one record in current partition
1007 delete from t1 where pk = 1;
1008 --echo # copy all data into history partition
1009 replace into t1 select * from t1;
1010 --error ER_RECORD_FILE_FULL
1011 replace into t1 select * from t1;
1012
1013 create or replace table t1 (
1014 pk integer auto_increment,
1015 primary key (pk),
1016 f varchar(45000)
1017 ) charset=latin1 with system versioning engine=memory
1018 partition by system_time interval 1 year (partition p1 history,
1019 partition pn current);
1020
1021 insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),();
1022
1023 select * into outfile 'MDEV-17891.data' from t1;
1024 load data infile 'MDEV-17891.data' replace into table t1;
1025 --error ER_RECORD_FILE_FULL
1026 load data infile 'MDEV-17891.data' replace into table t1;
1027 --error ER_RECORD_FILE_FULL
1028 load data infile 'MDEV-17891.data' replace into table t1;
1029
1030 # Cleanup
1031 --remove_file $datadir/test/MDEV-17891.data
1032 eval set @@max_heap_table_size= $max_heap_table_size_orig;
1033 drop table t1;
1034
1035 --echo #
1036 --echo # MDEV-22178 Assertion `info->alias.str' failed in partition_info::check_partition_info instead of ER_VERS_WRONG_PARTS
1037 --echo #
1038 create or replace table t1 (a int) with system versioning;
1039 --error ER_VERS_WRONG_PARTS
1040 alter table t1 partition by system_time (partition pn current);
1041 # Cleanup
1042 drop table t1;
1043
1044 --echo #
1045 --echo # MDEV-22247 History partition overflow leads to wrong SELECT result
1046 --echo #
1047 set timestamp= unix_timestamp('2000-01-01 00:00:00');
1048 create or replace table t1 (x int) with system versioning
1049 partition by system_time interval 1 hour
1050 (partition p0 history, partition p1 history, partition pn current);
1051
1052 insert into t1 values (0);
1053 update t1 set x= x + 1;
1054
1055 set timestamp= unix_timestamp('2000-01-01 02:00:01');
1056 update t1 set x= x + 1;
1057
1058 select *, row_start, row_end from t1 for system_time as of '2000-01-01 02:00:00';
1059 --replace_column 10 #
1060 explain partitions select * from t1 for system_time as of '2000-01-01 02:00:00';
1061 --replace_column 5 # 10 # 11 #
1062 explain partitions select * from t1;
1063 drop table t1;
1064
1065 --echo #
1066 --echo # MDEV-27244 Table corruption upon adding serial data type
1067 --echo #
1068 create table t1 (f int, key(f)) with system versioning
1069 partition by system_time limit 10 (partition p0 history, partition pn current);
1070 alter table t1 add x serial;
1071 alter table t1 add partition (partition p1 history);
1072 alter table t1 add partition (partition p2 history);
1073 drop table t1;
1074
1075 --echo #
1076 --echo # MDEV-27217 DELETE partition selection doesn't work for history partitions
1077 --echo #
1078 create table t1 (f char) with system versioning
1079 partition by system_time limit 10 (
1080 partition p0 history,
1081 partition p1 history,
1082 partition p2 history,
1083 partition pn current);
1084
1085 --error ER_VERS_NOT_ALLOWED
1086 delete from t1 partition (p1);
1087 --error ER_VERS_NOT_ALLOWED
1088 delete from t1 partition (p0, pn);
1089 --error ER_VERS_NOT_ALLOWED
1090 delete from t1 partition (p0, p1);
1091 --error ER_VERS_NOT_ALLOWED
1092 delete from t1 partition (p0, p1, pn);
1093 drop table t1;
1094
1095 set timestamp=unix_timestamp('2000-01-01 00:00:00');
1096 create or replace table t1 (i int) with system versioning
1097 partition by system_time interval 1 day (
1098 partition p0 history,
1099 partition p1 history,
1100 partition pn current);
1101 set timestamp=unix_timestamp('2000-01-02 00:00:00');
1102 insert t1 values (1);
1103 --error ER_VERS_NOT_ALLOWED
1104 delete from t1 partition (p0, pn);
1105 --error ER_VERS_NOT_ALLOWED
1106 delete from t1 partition (p0, p1, pn);
1107 lock tables t1 write;
1108 --error ER_VERS_NOT_ALLOWED
1109 delete from t1 partition (p0, pn);
1110 delete from t1;
1111 unlock tables;
1112 drop table t1;
1113 set timestamp= default;
1114
1115 --echo #
1116 --echo # MDEV-25546 LIMIT partitioning does not respect ROLLBACK
1117 --echo #
1118 create or replace table t1 (pk int primary key)
1119 with system versioning engine innodb
1120 partition by system_time limit 100 (
1121 partition p0 history,
1122 partition p1 history,
1123 partition pn current);
1124 insert into t1 select seq from seq_1_to_90;
1125
1126 start transaction;
1127 # Puts 80 rows into p0
1128 replace into t1 select seq from seq_1_to_80;
1129 # Puts another 70 rows into p0
1130 replace into t1 select seq from seq_1_to_70;
1131 # Puts 60 rows into p1
1132 replace into t1 select seq from seq_1_to_60;
1133
1134 select partition_name, table_rows
1135 from information_schema.partitions
1136 where table_name = 't1';
1137 rollback;
1138
1139 select partition_name, table_rows
1140 from information_schema.partitions
1141 where table_name = 't1';
1142
1143 # Should put 10 rows into the empty partition p0
1144 replace into t1 select seq from seq_1_to_10;
1145 select partition_name, table_rows
1146 from information_schema.partitions
1147 where table_name = 't1';
1148 # Cleanup
1149 drop table t1;
1150
1151 --echo #
1152 --echo # MDEV-28271 Assertion on TRUNCATE PARTITION for PARTITION BY SYSTEM_TIME
1153 --echo #
1154 create table t1 (x int) with system versioning
1155 partition by system_time limit 1 (
1156 partition p0 history,
1157 partition p1 history,
1158 partition p2 history, # p2 just disables warning about p1 partition full
1159 partition pn current);
1160
1161 insert into t1 values (0);
1162 update t1 set x= x + 1;
1163 update t1 set x= x + 1;
1164
1165 select * from t1 partition (p0);
1166 select * from t1 partition (p1);
1167 select * from t1 partition (pn);
1168
1169 delete from t1;
1170 delete history from t1;
1171 select * from t1 partition (p0);
1172 select * from t1 partition (p1);
1173 select * from t1 partition (pn);
1174
1175 insert into t1 values (0);
1176 update t1 set x= x + 1;
1177 update t1 set x= x + 1;
1178
1179 --echo # TRUNCATE PARTITION ALL does the same
1180 alter table t1 truncate partition all;
1181 select * from t1 partition (p0);
1182 select * from t1 partition (p1);
1183 select * from t1 partition (pn);
1184
1185 insert into t1 values (0);
1186 update t1 set x= x + 1;
1187 update t1 set x= x + 1;
1188
1189 --echo # TRUNCATE PARTITION deletes data from HISTORY partition
1190 alter table t1 truncate partition p1;
1191 select * from t1 partition (p0);
1192 select * from t1 partition (p1);
1193 select * from t1 partition (pn);
1194
1195 --echo # or from CURRENT partition
1196 alter table t1 truncate partition pn;
1197 select * from t1 partition (p0);
1198 select * from t1 partition (p1);
1199 select * from t1 partition (pn);
1200
1201 drop table t1;
1202
1203 --echo #
1204 --echo # MDEV-20077 Warning on full history partition is delayed until next DML statement
1205 --echo #
1206 --echo # DELETE
1207 create table t1 (x int) with system versioning
1208 partition by system_time limit 100 (
1209 partition p0 history,
1210 partition p1 history,
1211 partition pn current);
1212
1213 insert into t1 select seq from seq_0_to_200;
1214
1215 --echo # p0 is filled with 100 records (no warnings):
1216 delete from t1 where x <= 99;
1217 --echo # p1 is filled with 1 + 100 records (warning is printed):
1218 delete from t1 where x <= 100;
1219 delete from t1;
1220 --echo # You see warning above ^
1221
1222 select count(*) from t1 partition (p0);
1223 select count(*) from t1 partition (p1);
1224 drop table t1;
1225
1226 --echo # DELETE under LOCK TABLES
1227 create table t1 (x int) with system versioning
1228 partition by system_time limit 100 (
1229 partition p0 history,
1230 partition p1 history,
1231 partition pn current);
1232
1233 insert into t1 select seq from seq_0_to_200;
1234
1235 lock tables t1 write;
1236 --echo # (LOCK TABLES) p0 is filled with 100 records (no warnings):
1237 delete from t1 where x <= 99;
1238 --echo # (LOCK TABLES) p1 is filled with 1 + 100 records (warning is printed):
1239 delete from t1 where x <= 100;
1240 delete from t1;
1241 --echo # You see warning above ^
1242 unlock tables;
1243
1244 select count(*) from t1 partition (p0);
1245 select count(*) from t1 partition (p1);
1246 drop table t1;
1247
1248 --echo # DELETE multitable
1249 create table t1 (x int) with system versioning
1250 partition by system_time limit 100 (
1251 partition p0 history,
1252 partition p1 history,
1253 partition pn current);
1254
1255 create table t2 (y int);
1256
1257 insert into t1 select seq from seq_0_to_200;
1258 insert into t2 select seq from seq_0_to_3;
1259 delete t1, t2 from t1 join t2 where x < 50 and y = 0;
1260 delete t1, t2 from t1 join t2 where x < 100 and y = 1;
1261 delete t1, t2 from t1 join t2 where x < 150 and y = 2;
1262 delete t1, t2 from t1 join t2;
1263 --echo # You see warning above ^
1264
1265 select count(*) from t1 partition (p0);
1266 select count(*) from t1 partition (p1);
1267 drop table t1;
1268
1269 --echo # UDPATE
1270 create table t1 (x int) with system versioning
1271 partition by system_time limit 100 (
1272 partition p0 history,
1273 partition p1 history,
1274 partition pn current);
1275
1276 insert into t1 select seq from seq_0_to_49;
1277
1278 update t1 set x= x + 1;
1279 update t1 set x= x + 1;
1280 update t1 set x= x + 1;
1281 update t1 set x= x + 1;
1282 --echo # You see warning above ^
1283
1284 select count(*) from t1 partition (p0);
1285 select count(*) from t1 partition (p1);
1286 drop tables t1, t2;
1287
1288 --echo # UPDATE multitable
1289 create table t1 (x int) with system versioning
1290 partition by system_time limit 100 (
1291 partition p0 history,
1292 partition p1 history,
1293 partition pn current);
1294
1295 create table t2 (y int);
1296
1297 insert into t1 select seq from seq_0_to_49;
1298 insert into t2 values (5);
1299
1300 update t1, t2 set x= x + 1;
1301 update t1, t2 set x= x + 1;
1302 update t1, t2 set x= x + 1;
1303 update t1, t2 set x= x + 1;
1304 --echo # You see warning above ^
1305
1306 select count(*) from t1 partition (p0);
1307 select count(*) from t1 partition (p1);
1308 drop tables t1, t2;
1309
1310 --echo # INSERT .. ON DUPLICATE KEY UPDATE (ODKU)
1311 create table t1 (x int primary key) with system versioning
1312 partition by system_time limit 100 (
1313 partition p0 history,
1314 partition p1 history,
1315 partition pn current);
1316
1317 insert into t1 select seq from seq_0_to_100;
1318
1319 delete from t1 where x <= 99;
1320 insert into t1 values (100) on duplicate key update x= 400;
1321
1322 select count(*) from t1 partition (p0);
1323 select count(*) from t1 partition (p1);
1324 drop table t1;
1325
1326 --echo # INSERT .. SELECT .. ON DUPLICATE KEY UPDATE (ODKU)
1327 create table t1 (x int primary key) with system versioning
1328 partition by system_time limit 100 (
1329 partition p0 history,
1330 partition p1 history,
1331 partition pn current);
1332
1333 create table t2 (y int);
1334 insert into t2 values (100);
1335 insert into t1 select seq from seq_0_to_100;
1336
1337 delete from t1 where x <= 99;
1338 insert into t1 select * from t2 on duplicate key update x= 500;
1339
1340 select count(*) from t1 partition (p0);
1341 select count(*) from t1 partition (p1);
1342 drop tables t1, t2;
1343
1344 --echo # REPLACE
1345 create table t1 (x int primary key) with system versioning
1346 partition by system_time limit 100 (
1347 partition p0 history,
1348 partition p1 history,
1349 partition pn current);
1350
1351 insert into t1 select seq from seq_0_to_100;
1352
1353 delete from t1 where x < 99;
1354 replace t1 values (100);
1355 replace t1 values (100);
1356
1357 select count(*) from t1 partition (p0);
1358 select count(*) from t1 partition (p1);
1359 drop table t1;
1360
1361 --echo # LOAD DATA .. REPLACE
1362 create table t1 (x int primary key) with system versioning
1363 partition by system_time limit 100 (
1364 partition p0 history,
1365 partition p1 history,
1366 partition pn current);
1367
1368 insert into t1 select seq from seq_0_to_49;
1369 select x into outfile 'MDEV-20077.data' from t1;
1370
1371 load data infile 'MDEV-20077.data' replace into table t1 (x);
1372 load data infile 'MDEV-20077.data' replace into table t1 (x);
1373 load data infile 'MDEV-20077.data' replace into table t1 (x);
1374 load data infile 'MDEV-20077.data' replace into table t1 (x);
1375 --echo # You see warning above ^
1376
1377 select count(*) from t1 partition (p0);
1378 select count(*) from t1 partition (p1);
1379 drop table t1;
1380 --remove_file $datadir/test/MDEV-20077.data
1381
1382 --echo # REPLACE .. SELECT
1383 create table t1 (x int primary key) with system versioning
1384 partition by system_time limit 100 (
1385 partition p0 history,
1386 partition p1 history,
1387 partition pn current);
1388
1389 insert into t1 select seq from seq_0_to_49;
1390 replace t1 select * from t1;
1391 replace t1 select * from t1;
1392 replace t1 select * from t1;
1393 replace t1 select * from t1;
1394 --echo # You see warning above ^
1395
1396 select count(*) from t1 partition (p0);
1397 select count(*) from t1 partition (p1);
1398 drop table t1;
1399
1400 --echo #
1401 --echo # MDEV-28552 Assertion `inited==RND' failed in handler::ha_rnd_end
1402 --echo #
1403 create table tcount (c int unsigned);
1404 insert into tcount values (0);
1405
1406 create table t (f int) with system versioning
1407 partition by system_time limit 1000
1408 (partition p1 history, partition pn current);
1409 insert into t values (1),(2);
1410 create trigger tr before insert on t for each row update tcount set c = c + 1;
1411
1412 insert into t select * from t;
1413
1414 # cleanup
1415 drop table tcount, t;
1416
1417 --echo #
1418 --echo # MDEV-19569 Assertion `table_list->table' failed in find_field_in_table_ref and Assertion `table_ref->table || table_ref->view' in Field_iterator_table_ref::set_field_iterator
1419 --echo #
1420 set timestamp=unix_timestamp('2000-01-01 00:00:00');
1421 create table t1 (i int);
1422 create table t2 (i int);
1423
1424 --error ER_SUBQUERIES_NOT_SUPPORTED
1425 alter table t1 partition by system_time
1426 interval (select i from t2) day (partition p1 history, partition pn current);
1427
1428 drop table t1;
1429
1430 --error ER_SUBQUERIES_NOT_SUPPORTED
1431 create table t1 (id int) with system versioning
1432 partition by system_time
1433 interval (select i from t2) day (partition p1 history, partition pn current);
1434
1435 --error ER_PART_WRONG_VALUE
1436 create table t1 (id int) with system versioning
1437 partition by system_time
1438 interval "hello" day (partition p1 history, partition pn current);
1439
1440 create table t1 (id int) with system versioning
1441 partition by system_time
1442 interval 3.893 day (partition p1 history, partition pn current);
1443
1444 drop table t1, t2;
1445
1446 create table t1 (id int) with system versioning
1447 partition by system_time interval "3-11" year_month (partition p1 history, partition pn current);
1448 --replace_result $default_engine DEFAULT_ENGINE
1449 show create table t1;
1450 drop table t1;
1451
1452 create table t1 (id int) with system versioning
1453 partition by system_time interval "3 11" day_hour (partition p1 history, partition pn current);
1454 --replace_result $default_engine DEFAULT_ENGINE
1455 show create table t1;
1456 drop table t1;
1457
1458 create table t1 (id int) with system versioning
1459 partition by system_time interval "3 11:12" day_minute (partition p1 history, partition pn current);
1460 --replace_result $default_engine DEFAULT_ENGINE
1461 show create table t1;
1462 drop table t1;
1463
1464 create table t1 (id int) with system versioning
1465 partition by system_time interval "3 11:12:13" day_second (partition p1 history, partition pn current);
1466 --replace_result $default_engine DEFAULT_ENGINE
1467 show create table t1;
1468 drop table t1;
1469
1470 create table t1 (id int) with system versioning
1471 partition by system_time interval "11:12" hour_minute (partition p1 history, partition pn current);
1472 --replace_result $default_engine DEFAULT_ENGINE
1473 show create table t1;
1474 drop table t1;
1475
1476 create table t1 (id int) with system versioning
1477 partition by system_time interval "11:12:13" hour_second (partition p1 history, partition pn current);
1478 --replace_result $default_engine DEFAULT_ENGINE
1479 show create table t1;
1480 drop table t1;
1481
1482 create table t1 (id int) with system versioning
1483 partition by system_time interval "12:13" minute_second (partition p1 history, partition pn current);
1484 --replace_result $default_engine DEFAULT_ENGINE
1485 show create table t1;
1486 drop table t1;
1487
1488 --error ER_PART_WRONG_VALUE
1489 create table t1 (id int) with system versioning
1490 partition by system_time interval "12:13.123" minute_microsecond (partition p1 history, partition pn current);
1491
1492 --echo #
1493 --echo # End of 10.3 tests
1494 --echo #
1495
1496 --echo #
1497 --echo # MDEV-22283 Server crashes in key_copy or unexpected error 156: The table already existed in the storage engine
1498 --echo #
1499 create table t1 (a int primary key) engine=aria page_checksum=0
1500 with system versioning
1501 partition by system_time (partition p1 history, partition pn current);
1502
1503 alter table t1 add partition (partition p2 history);
1504 show table status;
1505 drop table t1;
1506
1507 create table t1 (b int) engine=aria row_format=dynamic with system versioning
1508 partition by system_time (partition p1 history, partition pn current);
1509 insert into t1 values (1);
1510 replace into t1 values (1);
1511
1512 # cleanup
1513 drop table t1;
1514
1515 --echo #
1516 --echo # MDEV-18794 Assertion `!m_innodb' failed in ha_partition::cmp_ref upon SELECT from partitioned table
1517 --echo #
1518 create or replace table t1 (pk int auto_increment, i int, c char(1), primary key (pk), key(i))
1519 engine=innodb with system versioning partition by key() partitions 2;
1520 insert into t1 (i, c) values (1, 'a'), (2, 'b'), (null, 'c'), (null, 'b');
1521 alter table t1 drop system versioning;
1522 replace into t1 select * from t1;
1523 select * from t1 where i > 0 or pk = 1000 limit 1;
1524 drop table t1;
1525
1526 --echo #
1527 --echo # End of 10.4 tests
1528 --echo #
1529
1530 --echo #
1531 --echo # MDEV-22153 ALTER add default history partitions makes table inaccessible
1532 --echo #
1533 create or replace table t1 (x int) with system versioning partition by system_time;
1534 alter table t1 add partition partitions 1;
1535 --replace_result $default_engine DEFAULT_ENGINE
1536 show create table t1;
1537 alter table t1 add partition partitions 2;
1538 --replace_result $default_engine DEFAULT_ENGINE
1539 show create table t1;
1540 alter table t1 add partition partitions 3;
1541 --replace_result $default_engine DEFAULT_ENGINE
1542 show create table t1;
1543 drop tables t1;
1544
1545 --echo #
1546 --echo # MDEV-22207 Drop default history partitions renders table inaccessible
1547 --echo #
1548 create or replace table t1 (i int) with system versioning
1549 partition by system_time limit 1 partitions 5;
1550
1551 alter table t1 drop partition p0, p2;
1552 --replace_result $default_engine DEFAULT_ENGINE
1553 show create table t1;
1554 alter table t1 add partition partitions 1;
1555 --replace_result $default_engine DEFAULT_ENGINE
1556 show create table t1;
1557
1558 drop tables t1;
1559
1560 --echo #
1561 --echo # MDEV-22155 ALTER add default history partitions name clash on non-default partitions
1562 --echo #
1563 set timestamp= default;
1564 create or replace table t1 (x int) with system versioning
1565 partition by system_time limit 1
1566 (partition p2 history, partition p8 history, partition pn current);
1567 alter table t1 add partition partitions 1;
1568 alter table t1 add partition partitions 2;
1569 --replace_result $default_engine DEFAULT_ENGINE
1570 show create table t1;
1571 alter table t1 add partition partitions 8;
1572 --replace_result $default_engine DEFAULT_ENGINE
1573 show create table t1;
1574
1575 drop tables t1;
1576
1577 --echo #
1578 --echo # MDEV-27328 Change of SYSTEM_TIME partitioning options is not possible without data copy
1579 --echo #
1580 create or replace table t1 (f int) with system versioning
1581 partition by hash(f);
1582 alter table t1 partition by system_time;
1583 --replace_result $default_engine DEFAULT_ENGINE
1584 show create table t1;
1585
1586 create or replace table t1 (f int) with system versioning
1587 partition by system_time;
1588 alter table t1 partition by hash(f);
1589 --replace_result $default_engine DEFAULT_ENGINE
1590 show create table t1;
1591
1592 create or replace table t1 (x int) with system versioning;
1593 alter table t1 partition by system_time;
1594 --replace_result $default_engine DEFAULT_ENGINE
1595 show create table t1;
1596
1597 create or replace table t1 (x int) with system versioning
1598 partition by system_time limit 100 partitions 4;
1599 --replace_result $default_engine DEFAULT_ENGINE
1600 show create table t1;
1601 alter table t1 add partition partitions 2;
1602 alter table t1 partition by system_time;
1603 --replace_result $default_engine DEFAULT_ENGINE
1604 show create table t1;
1605 alter table t1 partition by system_time limit 33;
1606 --replace_result $default_engine DEFAULT_ENGINE
1607 show create table t1;
1608 set timestamp= unix_timestamp('2000-01-01 00:00:00');
1609 insert t1 values (0);
1610 set timestamp= unix_timestamp('2000-01-01 00:10:00');
1611 update t1 set x= x + 1;
1612 set timestamp= unix_timestamp('2000-01-01 01:00:00');
1613 update t1 set x= x + 1;
1614 set timestamp= unix_timestamp('2000-01-01 01:30:00');
1615 update t1 set x= x + 1;
1616 set timestamp= unix_timestamp('2000-01-01 02:00:00');
1617 update t1 set x= x + 1;
1618
1619 # When we switch to INTERVAL we must reorganize partitions.
1620 # Otherwise pruning won't work correctly.
1621 alter table t1 partition by system_time interval 1 hour
1622 starts '2000-01-01 00:00:00';
1623 --replace_result $default_engine DEFAULT_ENGINE
1624 show create table t1;
1625 select * from t1 partition (p0);
1626 select * from t1 partition (p1);
1627 select * from t1 partition (p2);
1628 select * from t1 partition (pn);
1629 set timestamp= default;
1630
1631 # When we switch to LIMIT we probably don't want to reorganize old partitions.
1632 # Note: reorganize for LIMIT is broken, it pushes all history into first partition.
1633 # TODO: MDEV-27337
1634 alter table t1 partition by system_time limit 1;
1635 --replace_result $default_engine DEFAULT_ENGINE
1636 show create table t1;
1637 update t1 set x= x + 1;
1638 update t1 set x= x + 1;
1639 --echo # You see warning above ^
1640 select * from t1 partition (p0);
1641 select * from t1 partition (p1);
1642 select * from t1 partition (p2);
1643 select * from t1 partition (p3);
1644 select * from t1 partition (p4);
1645 select * from t1 partition (pn);
1646
1647 drop table t1;
1648
1649 --echo # End of 10.6 tests
1650
1651 --echo #
1652 --echo # MDEV-22166 MIGRATE PARTITION: move out partition into a table
1653 --echo #
1654 create or replace table t1 (x int)
1655 with system versioning
1656 partition by range(x) (
1657 partition p1 values less than (10),
1658 partition p2 values less than (20),
1659 partition p3 values less than (30),
1660 partition p4 values less than (40),
1661 partition p5 values less than (50),
1662 partition pn values less than maxvalue);
1663
1664 insert into t1 values (2), (12), (22), (32), (42), (52);
1665 update t1 set x= x + 1;
1666
1667 alter table t1 convert partition p2 to table tp2;
1668 --replace_result $default_engine X ' PAGE_CHECKSUM=1' ''
1669 show create table tp2;
1670 select * from tp2;
1671 select * from tp2 for system_time all order by x;
1672
1673 --replace_result $default_engine X ' PAGE_CHECKSUM=1' ''
1674 show create table t1;
1675 select * from t1 order by x;
1676 select * from t1 for system_time all order by x;
1677
1678 --echo # SP
1679 create or replace procedure sp()
1680 alter table t1 convert partition p3 to table tp3;
1681 call sp;
1682 --replace_result $default_engine X ' PAGE_CHECKSUM=1' ''
1683 show create table tp3;
1684 select * from tp3;
1685 --replace_result $default_engine X ' PAGE_CHECKSUM=1' ''
1686 show create table t1;
1687 select * from t1 order by x;
1688 drop table tp3;
1689 --error ER_PARTITION_DOES_NOT_EXIST
1690 call sp;
1691 --error ER_PARTITION_DOES_NOT_EXIST
1692 call sp;
1693 drop procedure sp;
1694
1695 --echo # LOCK TABLES, PS, SP
1696 create or replace procedure sp()
1697 alter table t1 convert partition p4 to table tp4;
1698 lock tables t1 write;
1699 prepare stmt from 'call sp';
1700 execute stmt;
1701
1702 # TODO: don't unlock here (see above TODO)
1703 unlock tables;
1704 --replace_result $default_engine X ' PAGE_CHECKSUM=1' ''
1705 show create table tp4;
1706 select * from tp4;
1707 --replace_result $default_engine X ' PAGE_CHECKSUM=1' ''
1708 show create table t1;
1709 select * from t1 order by x;
1710 drop table tp4;
1711 lock tables t1 write;
1712 --error ER_PARTITION_DOES_NOT_EXIST
1713 execute stmt;
1714 --error ER_PARTITION_DOES_NOT_EXIST
1715 call sp;
1716 drop prepare stmt;
1717 unlock tables;
1718 drop procedure sp;
1719 unlock tables;
1720
1721 drop tables t1, tp2;
1722
1723 --echo # System-versioned tables (SYSTEM_TIME LIMIT)
1724
1725 create or replace table t1 (
1726 x int,
1727 row_start timestamp(6) as row start invisible,
1728 row_end timestamp(6) as row end invisible,
1729 period for system_time(row_start, row_end)
1730 ) with system versioning
1731 partition by system_time limit 1 partitions 4;
1732
1733 insert into t1 values (2), (12), (22);
1734 update t1 set x= x + 1 where x = 2;
1735 update t1 set x= x + 1 where x = 12;
1736 update t1 set x= x + 1 where x = 22;
1737
1738 select * from t1 partition (p1);
1739 --error ER_VERS_WRONG_PARTS
1740 alter table t1 convert partition pn to table tp1;
1741 alter table t1 convert partition p1 to table tp1;
1742
1743 --replace_result $default_engine X ' PAGE_CHECKSUM=1' ''
1744 show create table tp1;
1745 select * from tp1;
1746 select * from tp1 for system_time all;
1747
1748 --replace_result $default_engine X ' PAGE_CHECKSUM=1' ''
1749 show create table t1;
1750 select * from t1 order by x;
1751 select * from t1 for system_time all order by x;
1752
1753 drop tables t1, tp1;
1754
1755 --echo # System-versioned tables (SYSTEM_TIME INTERVAL)
1756
1757 set timestamp= unix_timestamp('2000-01-01 00:00:00');
1758 create or replace table t1 (
1759 x int,
1760 row_start timestamp(6) as row start invisible,
1761 row_end timestamp(6) as row end invisible,
1762 period for system_time(row_start, row_end)
1763 ) with system versioning
1764 partition by system_time interval 1 hour partitions 4;
1765
1766 insert into t1 values (2), (12), (22);
1767 set timestamp= unix_timestamp('2000-01-01 00:00:01');
1768 update t1 set x= x + 1 where x = 2;
1769 set timestamp= unix_timestamp('2000-01-01 01:00:00');
1770 update t1 set x= x + 1 where x = 12;
1771 set timestamp= unix_timestamp('2000-01-01 02:00:00');
1772 update t1 set x= x + 1 where x = 22;
1773
1774 select * from t1 partition (p0);
1775 select * from t1 partition (p1);
1776 select * from t1 partition (p2);
1777 --error ER_VERS_DROP_PARTITION_INTERVAL
1778 alter table t1 convert partition p1 to table tp1;
1779 alter table t1 convert partition p0 to table tp0;
1780 alter table t1 convert partition p1 to table tp1;
1781 --error ER_VERS_WRONG_PARTS
1782 alter table t1 convert partition p2 to table tp2;
1783
1784 --replace_result $default_engine X ' PAGE_CHECKSUM=1' ''
1785 show create table tp0;
1786 --replace_result $default_engine X ' PAGE_CHECKSUM=1' ''
1787 show create table tp1;
1788 select * from tp0;
1789 select * from tp1;
1790 select * from tp0 for system_time all;
1791 select * from tp1 for system_time all;
1792
1793 --replace_result $default_engine X ' PAGE_CHECKSUM=1' ''
1794 show create table t1;
1795 select * from t1;
1796 select * from t1 for system_time all order by x;
1797
1798 drop tables t1, tp0, tp1;
1799
1800 --echo # System-versioned tables (implicit)
1801
1802 create or replace table t1(x int) with system versioning
1803 partition by system_time limit 1 partitions 3;
1804
1805 alter table t1 convert partition p1 to table tp1;
1806 --replace_result $default_engine X ' PAGE_CHECKSUM=1' ''
1807 show create table tp1;
1808 --replace_result $default_engine X ' PAGE_CHECKSUM=1' ''
1809 show create table t1;
1810
1811 drop tables t1, tp1;
1812
1813 if (!$MTR_COMBINATION_HEAP)
1814 {
1815 --echo # Complex table
1816 create or replace table t1 (
1817 x int primary key auto_increment,
1818 t timestamp(6) default '2001-11-11 11:11:11',
1819 b blob(4096) compressed null,
1820 c varchar(1033) character set utf8 not null,
1821 u int,
1822 unique key (x, u),
1823 m enum('a', 'b', 'c') not null default 'a' comment 'absolute',
1824 i1 tinyint, i2 smallint, i3 bigint,
1825 index three(i1, i2, i3),
1826 v1 timestamp(6) generated always as (t + interval 1 day),
1827 v2 timestamp(6) generated always as (t + interval 1 month) stored,
1828 s timestamp(6) as row start,
1829 e timestamp(6) as row end,
1830 period for system_time (s, e),
1831 ps date, pe date,
1832 period for app_time (ps, pe),
1833 constraint check_constr check (u > -1))
1834 with system versioning default charset=ucs2
1835 partition by range(x) (
1836 partition p0 values less than (10),
1837 partition p1 values less than (20),
1838 partition pn values less than maxvalue);
1839
1840 alter table t1 convert partition p1 to table tp1;
1841
1842 --replace_result $default_engine X ' PAGE_CHECKSUM=1' ''
1843 show create table tp1;
1844 --replace_result $default_engine X ' PAGE_CHECKSUM=1' ''
1845 show create table t1;
1846
1847 drop tables t1, tp1;
1848 }
1849
1850 --echo #
1851 --echo # MDEV-29841 Partition by system_time can be converted into table but not back
1852 --echo #
1853 create or replace table t (a int) with system versioning
1854 partition by system_time limit 10 partitions 3;
1855 alter table t convert partition p0 to table tp;
1856 --error ER_ONLY_ON_RANGE_LIST_PARTITION
1857 alter table t convert table tp to partition p0;
1858 drop tables t, tp;
1859
1860 --echo #
1861 --echo # End of 10.7 tests
1862 --echo #
1863
1864 --echo #
1865 --echo # MDEV-17554 Auto-create new partition for system versioned tables
1866 --echo # with history partitioned by INTERVAL/LIMIT
1867 --echo #
1868 create or replace table t1 (x int) with system versioning
1869 partition by system_time limit 1 auto;
1870 --replace_result $default_engine DEFAULT_ENGINE
1871 show create table t1;
1872 --echo # Turn off AUTO
1873 alter table t1 partition by system_time limit 1;
1874 --replace_result $default_engine DEFAULT_ENGINE
1875 show create table t1;
1876 --echo # Get AUTO back
1877 alter table t1 partition by system_time limit 1 auto;
1878 --replace_result $default_engine DEFAULT_ENGINE
1879 show create table t1;
1880
1881 insert into t1 values (1);
1882
1883 create or replace table t2 (y int);
1884 insert into t2 values (2);
1885
1886 insert into t1 select * from t2;
1887 insert into t2 select * from t1;
1888 --replace_result $default_engine DEFAULT_ENGINE
1889 show create table t1;
1890
1891 --echo # Too many partitions error
1892 set timestamp= unix_timestamp('2000-01-01 00:00:00');
1893 create or replace table t1 (x int) with system versioning
1894 partition by system_time interval 1 hour auto;
1895 set timestamp= unix_timestamp('2001-01-01 00:01:00');
1896 --error ER_VERS_HIST_PART_FAILED
1897 update t1 set x= x + 1;
1898 show warnings;
1899
1900 --echo # Auto-create failed error
1901 set timestamp= unix_timestamp('2000-01-01 00:00:00');
1902 create or replace table t1 (x int) with system versioning engine innodb
1903 partition by system_time interval 1 hour auto;
1904
1905 insert into t1 values (1);
1906
1907 call mtr.add_suppression("rror number .*(File exists|file operation)");
1908 call mtr.add_suppression("InnoDB: The file '.*test/t1#P#p1\\.ibd' already exists");
1909
1910 --let $datadir= `select @@datadir`
1911 --let $dummy= $datadir/test/t1#P#p1.ibd
1912 --write_file $dummy
1913 EOF
1914
1915 set timestamp= unix_timestamp('2000-01-01 01:00:00');
1916 --error ER_GET_ERRNO
1917 update t1 set x= x + 2;
1918 show warnings;
1919 --remove_file $dummy
1920
1921 --echo # Partition overflow error and manual fix
1922 set timestamp= unix_timestamp('2000-01-01 00:00:00');
1923 create or replace table t1 (x int) with system versioning
1924 partition by system_time interval 1 hour;
1925
1926 insert into t1 values (440);
1927 set timestamp= unix_timestamp('2000-01-01 00:10:00');
1928 update t1 set x= x + 1;
1929
1930 --echo # Check how pruning boundaries work
1931 --replace_column 5 # 10 # 11 #
1932 explain partitions select * from t1 for system_time as of '2000-01-01 00:59:58';
1933 --replace_column 5 # 10 # 11 #
1934 explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
1935 --replace_column 5 # 10 # 11 #
1936 explain partitions select * from t1 for system_time as of '2000-01-01 01:00:00';
1937 select * from t1 for system_time as of '2000-01-01 00:09:59';
1938
1939 set timestamp= unix_timestamp('2000-01-01 02:00:00');
1940 update t1 set x= x + 1;
1941
1942 select * from t1 for system_time as of '2000-01-01 01:00:00';
1943 select * from t1 partition (p0) order by x;
1944
1945 --echo # Here is how manual fix works: just add new partitions there
1946 alter table t1 add partition partitions 3;
1947 select * from t1 for system_time as of '2000-01-01 01:00:00';
1948 select * from t1 partition (p0) order by x;
1949
1950 --echo # Check pruning after ALTER
1951 --replace_column 5 # 10 # 11 #
1952 explain partitions select * from t1 for system_time as of '2000-01-01 00:59:58';
1953 --replace_column 5 # 10 # 11 #
1954 explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
1955 --replace_column 5 # 10 # 11 #
1956 explain partitions select * from t1 for system_time as of '2000-01-01 01:00:00';
1957
1958 drop table t1;
1959
1960 --enable_info
1961 create or replace table t1 (x int) with system versioning
1962 partition by system_time interval 3600 second
1963 starts '2000-01-01 00:00:00' auto partitions 3;
1964
1965 insert into t1 values (1);
1966 --replace_result $default_engine DEFAULT_ENGINE
1967 show create table t1;
1968
1969 set timestamp= unix_timestamp('2000-01-01 02:00:00');
1970 update t1 set x= x + 1;
1971 --replace_result $default_engine DEFAULT_ENGINE
1972 show create table t1;
1973
1974 set timestamp= unix_timestamp('2000-01-01 03:00:00');
1975 update t1 set x= x + 2;
1976 --replace_result $default_engine DEFAULT_ENGINE
1977 show create table t1;
1978
1979 set timestamp= unix_timestamp('2000-01-01 00:00:00');
1980 create or replace table t1 (x int) with system versioning
1981 partition by system_time interval 1 hour auto (
1982 partition p1 history,
1983 partition p3 history,
1984 partition pn current);
1985
1986 insert into t1 values (1);
1987 --replace_result $default_engine DEFAULT_ENGINE
1988 show create table t1;
1989
1990 set timestamp= unix_timestamp('2000-01-01 02:00:00');
1991 update t1 set x= x + 3;
1992 --replace_result $default_engine DEFAULT_ENGINE
1993 show create table t1;
1994
1995 set timestamp= unix_timestamp('2000-01-01 03:00:00');
1996 update t1 set x= x + 4;
1997 --replace_result $default_engine DEFAULT_ENGINE
1998 show create table t1;
1999
2000 set timestamp= unix_timestamp('2000-01-01 04:00:00');
2001 lock tables t1 write;
2002 update t1 set x= x + 5;
2003 --replace_result $default_engine DEFAULT_ENGINE
2004 show create table t1;
2005 unlock tables;
2006 set timestamp= default;
2007
2008 --echo # Couple of more LOCK TABLES cases
2009 create or replace table t1 (x int) with system versioning
2010 partition by system_time limit 1 auto;
2011 lock tables t1 write;
2012 insert into t1 values (1);
2013 update t1 set x= x + 1;
2014 update t1 set x= x + 2;
2015 update t1 set x= x + 3;
2016 --replace_result $default_engine DEFAULT_ENGINE
2017 show create table t1;
2018 unlock tables;
2019 --replace_result $default_engine DEFAULT_ENGINE
2020 show create table t1;
2021
2022 --echo # Overflow prevention under LOCK TABLES
2023 create or replace table t1 (x int)
2024 with system versioning partition by system_time
2025 limit 10 auto;
2026
2027 insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9);
2028 update t1 set x= x + 10;
2029
2030 lock tables t1 write;
2031 update t1 set x= 1 where x = 11;
2032 update t1 set x= 2 where x = 12;
2033 update t1 set x= 3 where x = 13;
2034 unlock tables;
2035
2036 select count(x) from t1 partition (p0);
2037 --replace_result $default_engine DEFAULT_ENGINE
2038 show create table t1;
2039 drop tables t1;
2040
2041 --echo # Test VIEW, LOCK TABLES
2042 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2043 create or replace table t1 (x int) with system versioning
2044 partition by system_time interval 1 hour auto;
2045 create or replace view v1 as select * from t1;
2046
2047 insert into t1 values (1);
2048 set timestamp= unix_timestamp('2000-01-01 01:00:00');
2049 update v1 set x= x + 2;
2050 --replace_result $default_engine DEFAULT_ENGINE
2051 show create table t1;
2052
2053 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2054 lock tables v1 write;
2055 update v1 set x= x + 3;
2056 --replace_result $default_engine DEFAULT_ENGINE
2057 show create table t1;
2058 unlock tables;
2059
2060 drop view v1;
2061 drop tables t1;
2062
2063 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2064 create or replace table t1 (x int) with system versioning
2065 partition by system_time interval 1 hour auto partitions 3;
2066
2067 create or replace table t2 (y int) with system versioning
2068 partition by system_time interval 1 hour auto;
2069
2070 insert into t1 values (1);
2071 insert into t2 values (2);
2072
2073 set timestamp= unix_timestamp('2000-01-01 01:00:00');
2074 update t1, t2 set x= x + 1, y= y + 1;
2075 --replace_result $default_engine DEFAULT_ENGINE
2076 show create table t1;
2077 --replace_result $default_engine DEFAULT_ENGINE
2078 show create table t2;
2079
2080 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2081 update t1, t2 set x= x + 1, y= y + 1;
2082 --replace_result $default_engine DEFAULT_ENGINE
2083 show create table t1;
2084 --replace_result $default_engine DEFAULT_ENGINE
2085 show create table t2;
2086
2087 set timestamp= unix_timestamp('2000-01-01 03:00:00');
2088 update t1, t2 set t1.x= 0 where t1.x< t2.y;
2089 --replace_result $default_engine DEFAULT_ENGINE
2090 show create table t1;
2091 # Multiupdate_prelocking_strategy::handle_end() is processed after table open.
2092 # For PS it is possible to skip unneeded auto-creation because the above happens at
2093 # prepare stage and auto-creation is done at execute stage.
2094 --replace_result $default_engine DEFAULT_ENGINE 'PARTITIONS 4' 'PARTITIONS ok' 'PARTITIONS 5' 'PARTITIONS ok'
2095 show create table t2;
2096
2097 drop tables t1, t2;
2098
2099 --echo # PS, SP, LOCK TABLES
2100 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2101 create or replace table t1 (x int) with system versioning
2102 partition by system_time interval 1 hour auto;
2103
2104 insert into t1 values (1);
2105
2106 set timestamp= unix_timestamp('2000-01-01 01:00:00');
2107 execute immediate 'update t1 set x= x + 5';
2108 --replace_result $default_engine DEFAULT_ENGINE
2109 show create table t1;
2110
2111 prepare s from 'update t1 set x= x + 6';
2112 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2113 execute s; execute s;
2114 --replace_result $default_engine DEFAULT_ENGINE
2115 show create table t1;
2116
2117 set timestamp= unix_timestamp('2000-01-01 03:00:00');
2118 lock tables t1 write;
2119 execute s; execute s;
2120 --replace_result $default_engine DEFAULT_ENGINE
2121 show create table t1;
2122 unlock tables;
2123 drop prepare s;
2124
2125 create procedure sp() update t1 set x= x + 7;
2126 set timestamp= unix_timestamp('2000-01-01 04:00:00');
2127 call sp; call sp;
2128 --replace_result $default_engine DEFAULT_ENGINE
2129 show create table t1;
2130 set timestamp= unix_timestamp('2000-01-01 05:00:00');
2131 lock tables t1 write;
2132 call sp; call sp;
2133 --replace_result $default_engine DEFAULT_ENGINE
2134 show create table t1;
2135 unlock tables;
2136 drop procedure sp;
2137
2138 set timestamp= unix_timestamp('2001-01-01 00:00:00');
2139 create or replace table t1 (i int) with system versioning
2140 partition by system_time interval 1 day starts '2001-01-01 00:00:00';
2141 insert into t1 values (0);
2142 set timestamp= unix_timestamp('2001-01-01 00:00:01');
2143 prepare s from 'update t1 set i= i + 1';
2144 execute s;
2145 set timestamp= unix_timestamp('2001-01-02 00:00:01');
2146 execute s;
2147 drop prepare s;
2148
2149 # Because of blobs:
2150 if (!$MTR_COMBINATION_HEAP)
2151 {
2152 --echo # Complex table
2153 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2154 create or replace table t1 (
2155 x int primary key auto_increment,
2156 t timestamp(6) default '2001-11-11 11:11:11',
2157 b blob(4096) compressed null,
2158 c varchar(1033) character set utf8 not null,
2159 u int unique,
2160 m enum('a', 'b', 'c') not null default 'a' comment 'absolute',
2161 i1 tinyint, i2 smallint, i3 bigint,
2162 index three(i1, i2, i3),
2163 v1 timestamp(6) generated always as (t + interval 1 day),
2164 v2 timestamp(6) generated always as (t + interval 1 month) stored,
2165 s timestamp(6) as row start,
2166 e timestamp(6) as row end,
2167 period for system_time (s, e),
2168 ps date, pe date,
2169 period for app_time (ps, pe),
2170 constraint check_constr check (u > -1))
2171 with system versioning default charset=ucs2
2172 partition by system_time interval 1 hour auto (
2173 partition p2 history,
2174 partition pn current);
2175 --replace_result $default_engine DEFAULT_ENGINE
2176 show create table t1;
2177
2178 insert into t1 (x, c, u, i1, i2, i3, ps, pe)
2179 values (1, 'cc', 0, 1, 2, 3, '1999-01-01', '2000-01-01');
2180 set timestamp= unix_timestamp('2000-01-01 01:00:00');
2181 update t1 set x= x + 8;
2182 --replace_result $default_engine DEFAULT_ENGINE
2183 show create table t1;
2184
2185 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2186 update t1 set x= x - 8;
2187 --replace_result $default_engine DEFAULT_ENGINE
2188 show create table t1;
2189 }
2190 --disable_info
2191
2192 --echo # INSERT .. ON DUPLICATE KEY UPDATE (ODKU)
2193 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2194 create or replace table t1 (x int primary key) with system versioning
2195 partition by system_time interval 1 hour auto;
2196 insert into t1 values (1);
2197 set timestamp= unix_timestamp('2000-01-01 01:00:00');
2198 insert into t1 values (1) on duplicate key update x= x + 1;
2199 --replace_result $default_engine DEFAULT_ENGINE
2200 show create table t1;
2201
2202 --echo # LOAD DATA .. REPLACE
2203 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2204 create or replace table t1 (x int primary key) with system versioning
2205 partition by system_time interval 1 hour auto;
2206
2207 insert t1 values (1), (2), (3);
2208 select x into outfile 'MDEV-17554.data' from t1;
2209
2210 set timestamp= unix_timestamp('2000-01-01 01:00:00');
2211 load data infile 'MDEV-17554.data' replace into table t1 (x);
2212 --replace_result $default_engine DEFAULT_ENGINE
2213 show create table t1;
2214 --remove_file $datadir/test/MDEV-17554.data
2215
2216 --echo # Concurrent DML
2217 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2218 create or replace table t1 (x int) with system versioning
2219 partition by system_time interval 1 hour auto partitions 3;
2220
2221 insert into t1 values (1);
2222 --replace_result $default_engine DEFAULT_ENGINE
2223 show create table t1;
2224
2225 --connect con8, localhost, root
2226 --connect con7, localhost, root
2227 --connect con6, localhost, root
2228 --connect con5, localhost, root
2229 --connect con4, localhost, root
2230 --connect con3, localhost, root
2231 --connect con2, localhost, root
2232 --connect con1, localhost, root
2233 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2234 send update t1 set x= x + 10;
2235 --connection con2
2236 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2237 send update t1 set x= x + 20;
2238 --connection con3
2239 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2240 send update t1 set x= x + 30;
2241 --connection con4
2242 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2243 send update t1 set x= x + 40;
2244 --connection con5
2245 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2246 send update t1 set x= x + 50;
2247 --connection con6
2248 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2249 send update t1 set x= x + 60;
2250 --connection con7
2251 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2252 send update t1 set x= x + 70;
2253 --connection con8
2254 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2255 update t1 set x= x + 80;
2256 --connection con1
2257 reap;
2258 --disconnect con1
2259 --connection con2
2260 reap;
2261 --disconnect con2
2262 --connection con3
2263 reap;
2264 --disconnect con3
2265 --connection con4
2266 reap;
2267 --disconnect con4
2268 --connection con5
2269 reap;
2270 --disconnect con5
2271 --connection con6
2272 reap;
2273 --disconnect con6
2274 --connection con7
2275 reap;
2276 --disconnect con7
2277 --disconnect con8
2278 --connection default
2279 --replace_result $default_engine DEFAULT_ENGINE
2280 show create table t1;
2281
2282 drop tables t1;
2283 set timestamp= default;
2284
2285 --echo # Concurrent DML (LIMIT)
2286 create or replace table t1 (x int) with system versioning engine heap
2287 partition by system_time limit 1 auto partitions 3;
2288
2289 insert into t1 values (1);
2290
2291 --let $max_loop= 3
2292 # For more intensity use
2293 # --let $max_loop= 30
2294 --echo update t1 set x= x + N; # (running multithreaded for $max_loop times)
2295 --disable_query_log
2296 --disable_result_log
2297 --connect con9, localhost, root
2298 --connect con10, localhost, root
2299 --connect con11, localhost, root
2300 --connect con12, localhost, root
2301 --connect con13, localhost, root
2302 --connect con14, localhost, root
2303 --connect con15, localhost, root
2304 --connect con16, localhost, root
2305 --connect con17, localhost, root
2306 --connect con18, localhost, root
2307 --connect con19, localhost, root
2308 --connect con20, localhost, root
2309 --connect con8, localhost, root
2310 --connect con7, localhost, root
2311 --connect con6, localhost, root
2312 --connect con5, localhost, root
2313 --connect con4, localhost, root
2314 --connect con3, localhost, root
2315 --connect con2, localhost, root
2316 --connect con1, localhost, root
2317 --let $i= 0
2318 while ($i < $max_loop)
2319 {
2320 --connection con1
2321 send update t1 set x= x + 100;
2322 --connection con2
2323 send update t1 set x= x + 200;
2324 --connection con3
2325 send update t1 set x= x + 300;
2326 --connection con4
2327 send update t1 set x= x + 400;
2328 --connection con5
2329 send update t1 set x= x + 500;
2330 --connection con6
2331 send update t1 set x= x + 600;
2332 --connection con7
2333 send update t1 set x= x + 700;
2334 --connection con8
2335 send update t1 set x= x + 800;
2336 --connection con9
2337 send update t1 set x= x + 900;
2338 --connection con10
2339 send update t1 set x= x + 1000;
2340 --connection con11
2341 send update t1 set x= x + 1100;
2342 --connection con12
2343 send update t1 set x= x + 1200;
2344 --connection con13
2345 send update t1 set x= x + 1300;
2346 --connection con14
2347 send update t1 set x= x + 1400;
2348 --connection con15
2349 send update t1 set x= x + 1500;
2350 --connection con16
2351 send update t1 set x= x + 1600;
2352 --connection con17
2353 send update t1 set x= x + 1700;
2354 --connection con18
2355 send update t1 set x= x + 1800;
2356 --connection con19
2357 send update t1 set x= x + 1900;
2358 --connection con20
2359 send update t1 set x= x + 2000;
2360 --connection con1
2361 reap;
2362 --connection con2
2363 reap;
2364 --connection con3
2365 reap;
2366 --connection con4
2367 reap;
2368 --connection con5
2369 reap;
2370 --connection con6
2371 reap;
2372 --connection con7
2373 reap;
2374 --connection con8
2375 reap;
2376 --connection con9
2377 reap;
2378 --connection con10
2379 reap;
2380 --connection con11
2381 reap;
2382 --connection con12
2383 reap;
2384 --connection con13
2385 reap;
2386 --connection con14
2387 reap;
2388 --connection con15
2389 reap;
2390 --connection con16
2391 reap;
2392 --connection con17
2393 reap;
2394 --connection con18
2395 reap;
2396 --connection con19
2397 reap;
2398 --connection con20
2399 reap;
2400 --inc $i
2401 }
2402
2403 --disconnect con1
2404 --disconnect con2
2405 --disconnect con3
2406 --disconnect con4
2407 --disconnect con5
2408 --disconnect con6
2409 --disconnect con7
2410 --disconnect con8
2411 --disconnect con9
2412 --disconnect con10
2413 --disconnect con11
2414 --disconnect con12
2415 --disconnect con13
2416 --disconnect con14
2417 --disconnect con15
2418 --disconnect con16
2419 --disconnect con17
2420 --disconnect con18
2421 --disconnect con19
2422 --disconnect con20
2423
2424 --connection default
2425 # Result is undeterministic under LIMIT concurrency (MDEV-28459)
2426 # show create table t1;
2427
2428 --enable_query_log
2429 --enable_result_log
2430
2431 drop tables t1;
2432
2433 --echo # Transaction
2434 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2435 create or replace table t1 (x int) with system versioning engine innodb
2436 partition by system_time interval 1 hour auto;
2437
2438 insert into t1 values (1);
2439 set timestamp= unix_timestamp('2000-01-01 01:00:00');
2440 start transaction;
2441 update t1 set x= 0;
2442 --connect con1, localhost, root
2443 select * from t1;
2444 show create table t1;
2445 --connection default
2446 commit;
2447 show create table t1;
2448
2449 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2450 start transaction;
2451 update t1 set x= 1;
2452 --connection con1
2453 select * from t1;
2454 --connection default
2455 rollback;
2456 show create table t1;
2457 --disconnect con1
2458 --connection default
2459 drop table t1;
2460
2461 --echo #
2462 --echo # MDEV-25479 Auto-create: 2nd and further executions of PS or SP fail to create partition
2463 --echo #
2464 create table t (a int) with system versioning
2465 partition by system_time interval 1 hour auto;
2466
2467 insert into t values (1), (2);
2468 prepare stmt from "update t set a= a + 1";
2469 set @@timestamp= @@timestamp + 3601;
2470 execute stmt;
2471 set @@timestamp= @@timestamp + 3601;
2472 execute stmt;
2473 drop prepare stmt;
2474 --replace_result $default_engine DEFAULT_ENGINE
2475 show create table t;
2476
2477 create procedure sp() update t set a= a + 1;
2478 set @@timestamp= @@timestamp + 3601;
2479 call sp();
2480 set @@timestamp= @@timestamp + 3601;
2481 call sp();
2482 drop procedure sp;
2483 --replace_result $default_engine DEFAULT_ENGINE
2484 show create table t;
2485
2486 # Cleanup
2487 drop table t;
2488
2489 --echo #
2490 --echo # MDEV-23639 Auto-create does not work under LOCK TABLES or inside triggers
2491 --echo #
2492 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2493 create or replace table t1 (x int) with system versioning
2494 partition by system_time interval 1 hour auto
2495 partitions 3;
2496
2497 create table t2 (x int);
2498 create table t3 (x int);
2499 insert into t3 values (3);
2500
2501 create trigger tr after insert on t2 for each row update t1 set x= x + 11;
2502 create or replace procedure sp() update t1 set x= x + 5;
2503 create or replace procedure sp2() insert into t2 values (5);
2504 prepare ps from 'update t1 set x= x + 6';
2505 prepare ps2 from 'insert into t2 values (6)';
2506
2507 insert into t1 values (1);
2508 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2509 --replace_result $default_engine DEFAULT_ENGINE
2510 show create table t1;
2511 insert into t2 values (2);
2512 --replace_result $default_engine DEFAULT_ENGINE
2513 show create table t1;
2514
2515 set timestamp= unix_timestamp('2000-01-01 03:00:00');
2516 call sp; call sp;
2517 --replace_result $default_engine DEFAULT_ENGINE
2518 show create table t1;
2519 set timestamp= unix_timestamp('2000-01-01 04:00:00');
2520 call sp2; call sp2;
2521 --replace_result $default_engine DEFAULT_ENGINE
2522 show create table t1;
2523
2524 set timestamp= unix_timestamp('2000-01-01 05:00:00');
2525 execute ps; execute ps;
2526 --replace_result $default_engine DEFAULT_ENGINE
2527 show create table t1;
2528 set timestamp= unix_timestamp('2000-01-01 06:00:00');
2529 execute ps2; execute ps2;
2530 --replace_result $default_engine DEFAULT_ENGINE
2531 show create table t1;
2532
2533 set timestamp= unix_timestamp('2000-01-01 08:00:00');
2534 lock tables t1 write, t2 write;
2535 --replace_result $default_engine DEFAULT_ENGINE
2536 show create table t1;
2537 set timestamp= unix_timestamp('2000-01-01 09:00:00');
2538 update t1 set x= x + 1;
2539 --replace_result $default_engine DEFAULT_ENGINE
2540 show create table t1;
2541 set timestamp= unix_timestamp('2000-01-01 10:00:00');
2542 update t1 set x= x + 2;
2543 --replace_result $default_engine DEFAULT_ENGINE
2544 show create table t1;
2545 update t2 set x= x + 1;
2546 set timestamp= unix_timestamp('2000-01-01 11:00:00');
2547 insert into t2 values (4);
2548 --replace_result $default_engine DEFAULT_ENGINE
2549 show create table t1;
2550 --error ER_TABLE_NOT_LOCKED
2551 update t3 set x= x + 1;
2552
2553 set timestamp= unix_timestamp('2000-01-01 12:00:00');
2554 call sp; call sp;
2555 --replace_result $default_engine DEFAULT_ENGINE
2556 show create table t1;
2557 set timestamp= unix_timestamp('2000-01-01 13:00:00');
2558 call sp2; call sp2;
2559 --replace_result $default_engine DEFAULT_ENGINE
2560 show create table t1;
2561
2562 set timestamp= unix_timestamp('2000-01-01 14:00:00');
2563 execute ps; execute ps;
2564 --replace_result $default_engine DEFAULT_ENGINE
2565 show create table t1;
2566 set timestamp= unix_timestamp('2000-01-01 15:00:00');
2567 execute ps2; execute ps2;
2568 --replace_result $default_engine DEFAULT_ENGINE
2569 show create table t1;
2570
2571 unlock tables;
2572 --replace_result $default_engine DEFAULT_ENGINE
2573 show create table t1;
2574
2575 # Cleanup
2576 drop tables t1, t2, t3;
2577 drop procedure sp;
2578 drop procedure sp2;
2579 drop prepare ps;
2580 drop prepare ps2;
2581
2582 --echo #
2583 --echo # MDEV-27456 Assertion `!thd->is_error()' fails in vers_create_partitions upon DML with ER_UNKNOWN_PARTITION
2584 --echo #
2585 create table t (a int) with system versioning
2586 partition by system_time interval 1 minute auto;
2587 set @@timestamp= @@timestamp + 61;
2588 select * from t;
2589 --error ER_UNKNOWN_PARTITION
2590 delete from t partition (px);
2591 lock tables t write;
2592 --error ER_UNKNOWN_PARTITION
2593 delete from t partition (px);
2594 unlock tables;
2595 drop table t;
2596 set timestamp= default;
2597
2598 --echo #
2599 --echo # MDEV-28978 Assertion failure in THD::binlog_query or unexpected
2600 --echo # ER_ERROR_ON_WRITE with auto-partitioning
2601 --echo #
2602 create table t (a int) with system versioning partition by system_time limit 6 auto;
2603 insert into t () values (),(),(),(),(),();
2604 update t set a = 1;
2605 update t set a = 2 limit 0;
2606 # cleanup
2607 drop table t;
2608
2609 --echo #
2610 --echo # MDEV-31244 Assertion "not SELECT" in vers_set_hist_part()
2611 --echo #
2612 create table t (a int) with system versioning partition by system_time;
2613 --delimiter $
2614 create function f() returns int
2615 begin
2616 update t set a = 1;
2617 return 1;
2618 end $
2619 --delimiter ;
2620 create procedure p() select f();
2621 call p();
2622 call p();
2623 # cleanup
2624 drop procedure p;
2625 drop function f;
2626 drop table t;
2627
2628 --echo #
2629 --echo # MDEV-29873 MSAN uninitialized value errors in bcmp /
2630 --echo # prep_alter_part_table upon re-partitioning by system time
2631 --echo #
2632 create table t (a int) with system versioning partition by system_time interval 5 week;
2633 alter table t partition by system_time interval 10 week;
2634 # cleanup
2635 drop table t;
2636
2637 --echo #
2638 --echo # MDEV-16546 System versioning setting to allow history modification
2639 --echo #
2640 create table t1 (a varchar(100)) with system versioning
2641 partition by system_time interval 1 day
2642 starts '2021-09-30 00:00:00' partitions 3;
2643 set system_versioning_insert_history=1;
2644 --replace_result $sys_time_max SYS_TIME_MAX
2645 eval insert into t1 (a,row_start,row_end) values
2646 ('p0', '2021-09-30', '2021-09-30 10:00:00'),
2647 ('p1', '2021-09-30', '2021-10-01 10:00:00'),
2648 ('overflows, so also p1','2021-09-30', '2021-10-10 10:00:00'),
2649 ('pn, current', '2021-09-30', $sys_time_max);
2650 select table_name,partition_name,partition_ordinal_position,partition_method,partition_description,table_rows
2651 from information_schema.partitions where table_schema='test';
2652 drop table t1;
2653 set system_versioning_insert_history=0;
2654
2655 --disable_prepare_warnings
2656
2657 --echo #
2658 --echo # MDEV-29727 ALTER and CREATE with default partitioning
2659 --echo # differently react to SQL_MODE => unusable SHOW CREATE
2660 --echo #
2661 create table t (a int) with system versioning;
2662 --error WARN_VERS_PARAMETERS
2663 alter table t partition by system_time partitions 3;
2664 drop table t;
2665 --error WARN_VERS_PARAMETERS
2666 create table t (a int) with system versioning partition by system_time partitions 3;
2667
2668 --echo #
2669 --echo # MDEV-36115 InnoDB: assertion: node->pcur->rel_pos == BTR_PCUR_ON
2670 --echo # in row_update_for_mysql
2671 --echo #
2672 create table t (a int key) engine=innodb
2673 with system versioning
2674 partition by key() partitions 3;
2675
2676 start transaction;
2677 insert into t values (1),(2),(3),(4),(5),(6),(7),(8);
2678 set timestamp=+1;
2679 delete from t;
2680 insert into t values (1),(2);
2681 DELETE from t;
2682 drop table t;
2683
2684 --echo #
2685 --echo # MDEV-36817 Server crashes in do_mark_index_columns instead of
2686 --echo # ER_DUP_ENTRY on partitioned table
2687 --echo #
2688 create table t (f int, unique(f)) engine=innodb partition by key (f) partitions 2;
2689 insert into t (f) values (1), (3);
2690 --error ER_DUP_ENTRY
2691 update t set f = 0;
2692 drop table t;
2693
2694 --echo #
2695 --echo # End of 10.5 tests
2696 --echo #
2697
2698 --echo #
2699 --echo # MDEV-31903 Server crashes in _ma_reset_history upon UNLOCK table with auto-create history partitions
2700 --echo #
2701 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2702 create table t1 (x int) engine=aria with system versioning partition by system_time interval 1 hour auto partitions 3;
2703 insert into t1 values (1);
2704
2705 create table t2 (x int) engine=aria;
2706 create trigger tr after insert on t2 for each row update t1 set x= x + 11;
2707 lock tables t1 write, t2 write;
2708 update t1 set x= x + 1;
2709 set timestamp= unix_timestamp('2000-01-01 13:00:00');
2710 insert into t2 values (5);
2711 unlock tables;
2712 drop table t1, t2;
2713 set timestamp= default;
2714
2715 --echo #
2716 --echo # MDEV-29872 MSAN/Valgrind uninitialised value errors in TABLE::vers_switch_partition
2717 --echo #
2718 create table t (a int) with system versioning partition by system_time limit 100 partitions 3;
2719 let $emb= `select if(version() like '%embedded%', 1, 0)`;
2720 if (!$emb)
2721 {
2722 --disable_result_log
2723 --disable_query_log
2724 --error ER_DELAYED_NOT_SUPPORTED
2725 insert delayed into t () values ();
2726 --enable_query_log
2727 --enable_result_log
2728 }
2729 # cleanup
2730 drop table t;
2731
2732 --echo #
2733 --echo # End of 10.9 tests
2734 --echo #
2735
2736 --echo #
2737 --echo # MDEV-34775 Wrong reopen of already open routine due to auto-create in SP
2738 --echo #
2739
2740 create table t (a int) with system versioning
2741 partition by system_time
2742 interval 1 minute auto;
2743
2744 --delimiter $
2745 create function f()
2746 returns int
2747 begin
2748 replace into t select * from t;
2749 return 0;
2750 end $
2751 --delimiter ;
2752
2753 set timestamp= @@timestamp + 61;
2754 select f();
2755
2756 drop table t;
2757 drop function f;
2758
2759 --echo #
2760 --echo # MDEV-33370 Assertion `!is_set() || (m_status == DA_OK_BULK &&
2761 --echo # is_bulk_op())' failed after ALTER TABLE of versioned table
2762 --echo #
2763 create table t1 (i int) with system versioning
2764 partition by system_time interval 1 month (
2765 partition ver_p1 history,
2766 partition ver_p2 history,
2767 partition ver_pn current);
2768
2769 --error ER_DATA_OUT_OF_RANGE
2770 alter table `t1` partition by system_time interval 10007 year ;
2771 drop table t1;
2772
2773 --echo #
2774 --echo # MDEV-29114 ALTER puts row into different partition for system_time
2775 --echo # interval partitioning
2776 --echo #
2777 --echo # CREATE case
2778 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2779 create or replace table t1 (x int) with system versioning
2780 partition by system_time interval 1 hour (
2781 partition p0 history, partition p1 history, partition pn current);
2782 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2783 insert t1 values (0);
2784 set timestamp= unix_timestamp('2000-01-01 00:10:00');
2785 update t1 set x= 1;
2786 set timestamp= unix_timestamp('2000-01-01 01:00:00');
2787 update t1 set x= 2;
2788 set timestamp= unix_timestamp('2000-01-01 01:30:00');
2789 update t1 set x= 3;
2790
2791 --echo # CREATE result: row 1 got into p1
2792 select *, row_start, row_end from t1 partition (p0);
2793 select *, row_start, row_end from t1 partition (p1);
2794 flush tables;
2795 --echo # For CREATE pruning is affected by current timestamp, but SELECT works
2796 --echo # in any case since row 1 got into p1
2797 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2798 explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
2799 select * from t1 for system_time as of '2000-01-01 00:59:59';
2800 set timestamp= unix_timestamp('2020-01-01 00:00:00');
2801 explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
2802 select * from t1 for system_time as of '2000-01-01 00:59:59';
2803
2804 --echo # ALTER case
2805 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2806 alter table t1
2807 partition by system_time interval 1 hour (
2808 partition p0 history, partition p1 history, partition pn current);
2809
2810 --echo # ALTER result: row 1 got into p1
2811 Select *, row_start, row_end from t1 partition (p0);
2812 Select *, row_start, row_end from t1 partition (p1);
2813 flush tables;
2814 Explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
2815 Select * from t1 for system_time as of '2000-01-01 00:59:59';
2816
2817 drop table t1;
2818
2819 --echo #
2820 --echo # MDEV-36362 MariaDB crashes when parsing fuzzer generated PARTITION
2821 --echo #
2822 --error ER_SUBQUERIES_NOT_SUPPORTED
2823 create table t
2824 partition by system_time
2825 interval setval(a, 1) second_microsecond;
2826
2827 --error ER_SUBQUERIES_NOT_SUPPORTED
2828 create table t (x int) with system versioning
2829 partition by system_time interval nextval(s) hour;
2830
2831 --error ER_SUBQUERIES_NOT_SUPPORTED
2832 create table t (x int) with system versioning
2833 partition by system_time interval lastval(s) hour;
2834
2835 --echo #
2836 --echo # MDEV-33289 INTERVAL partitioning by system time does not work close to
2837 --echo # the end of timestamp range
2838 --echo #
2839 set timestamp= unix_timestamp('2024-01-21 00:00:00');
2840 create or replace table t (a int) with system versioning
2841 partition by system_time interval 10 year auto;
2842 insert into t values (0);
2843 set timestamp= unix_timestamp('2034-01-20 23:59:59');
2844 update t set a= 1;
2845 set timestamp= unix_timestamp('2034-01-20 00:00:00');
2846 update t set a= 2;
2847 set timestamp= unix_timestamp('2035-01-21 00:00:00');
2848 update t set a= 3;
2849
2850 select * from t partition (p0);
2851 select * from t partition (p1);
2852 select * from t partition (pn);
2853 select * from t for system_time as of '2035-01-20 00:00:00';
2854 drop tables t;
2855
2856 --error ER_DATA_OUT_OF_RANGE
2857 create or replace table t (a int) with system versioning
2858 partition by system_time interval 10 year partitions 10;
2859
2860 create or replace table t (a int) with system versioning
2861 partition by system_time interval 10 year;
2862 drop table t;
2863
2864 --echo #
2865 --echo # MDEV-38710 Assertion is_lock_owner on error returning from auto-create in mysql_admin_table
2866 --echo #
2867
2868 set timestamp=2147483647;
2869 create table t (x int) with system versioning partition by system_time interval 1 hour auto;
2870 repair table t;
2871 drop table t;
2872 set timestamp=default;
2873
2874 --echo # End of 10.11 tests
2875
2876 set global innodb_stats_persistent= @save_persistent;
2877 --source suite/versioning/common_finish.inc