Debian Codegraph @

mysql-test/suite/versioning/r/partition.result

raw
1 set @save_persistent=@@global.innodb_stats_persistent;
2 set global innodb_stats_persistent= 0;
3 call mtr.add_suppression("need more HISTORY partitions");
4 set system_versioning_alter_history=keep;
5 # Check conventional partitioning on temporal tables
6 create or replace table t1 (
7 x int,
8 row_start SYS_DATATYPE as row start invisible,
9 row_end SYS_DATATYPE as row end invisible,
10 period for system_time(row_start, row_end))
11 with system versioning
12 partition by range columns (x) (
13 partition p0 values less than (100),
14 partition p1 values less than (1000));
15 insert into t1 values (3), (300);
16 select * from t1;
17 x
18 3
19 300
20 select * from t1 partition (p0);
21 x
22 3
23 select * from t1 partition (p1);
24 x
25 300
26 delete from t1;
27 select * from t1;
28 x
29 select * from t1 partition (p0);
30 x
31 select * from t1 partition (p1);
32 x
33 select * from t1 for system_time all;
34 x
35 3
36 300
37 select * from t1 partition (p0) for system_time all;
38 x
39 3
40 select * from t1 partition (p1) for system_time all;
41 x
42 300
43 # Engine change native <-> non-native versioning prohibited
44 create or replace table t1 (
45 i int,
46 row_start SYS_DATATYPE as row start invisible,
47 row_end SYS_DATATYPE as row end invisible,
48 period for system_time(row_start, row_end))
49 engine=DEFAULT_ENGINE
50 with system versioning partition by hash(i);
51 alter table t1 engine=NON_DEFAULT_ENGINE;
52 ERROR HY000: Not allowed for system-versioned `test`.`t1`. Change to/from native system versioning engine is not supported.
53 ## CREATE TABLE
54 create or replace table t1 (x int)
55 partition by system_time (
56 partition p0 history,
57 partition pn current);
58 ERROR HY000: Table `t1` is not system-versioned
59 create or replace table t1 (x int);
60 alter table t1
61 partition by system_time (
62 partition p0 history,
63 partition pn current);
64 ERROR HY000: Table `t1` is not system-versioned
65 create or replace table t1 (x int)
66 with system versioning
67 partition by system_time (
68 partition p0 current);
69 ERROR HY000: Wrong partitions for `t1`: must have at least one HISTORY and exactly one last CURRENT
70 create or replace table t1 (x int)
71 with system versioning
72 partition by system_time (
73 partition p0 current,
74 partition p1 current);
75 ERROR HY000: Wrong partitions for `t1`: must have at least one HISTORY and exactly one last CURRENT
76 create or replace table t1 (x int)
77 with system versioning
78 partition by system_time (
79 partition p0 history,
80 partition p1 history);
81 ERROR HY000: Wrong partitions for `t1`: must have at least one HISTORY and exactly one last CURRENT
82 create or replace table t1 (x int)
83 with system versioning
84 partition by system_time (
85 partition pn current,
86 partition p0 history);
87 ERROR HY000: Wrong partitions for `t1`: must have at least one HISTORY and exactly one last CURRENT
88 create or replace table t1 (x int)
89 with system versioning
90 partition by system_time (
91 partition p0,
92 partition pn current);
93 ERROR HY000: Wrong partitions for `t1`: must have at least one HISTORY and exactly one last CURRENT
94 create or replace table t1 (x int)
95 with system versioning
96 partition by system_time (
97 partition p0 history,
98 partition pn current);
99 create or replace table t1 (a int)
100 partition by range (a) (
101 partition p0 history,
102 partition p1 current);
103 ERROR HY000: Wrong partition type `SYSTEM_TIME` for partitioning by `RANGE`
104 create or replace table t1 (b int)
105 partition by range (a) (
106 partition p0 current,
107 partition p1 history);
108 ERROR HY000: Wrong partition type `SYSTEM_TIME` for partitioning by `RANGE`
109 ## ALTER TABLE
110 alter table t1 add partition (
111 partition p1 current);
112 ERROR HY000: Wrong partitions for `t1`: must have at least one HISTORY and exactly one last CURRENT
113 alter table t1 add partition (
114 partition p1 history);
115 Warnings:
116 Warning 4115 Maybe missing parameters: no rotation condition for multiple HISTORY partitions.
117 show create table t1;
118 Table Create Table
119 t1 CREATE TABLE `t1` (
120 `x` int(11) DEFAULT NULL
121 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
122 PARTITION BY SYSTEM_TIME
123 (PARTITION `p0` HISTORY ENGINE = DEFAULT_ENGINE,
124 PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE,
125 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
126 insert into t1 values (1), (2);
127 alter table t1 drop partition pn;
128 ERROR HY000: Wrong partitions for `t1`: must have at least one HISTORY and exactly one last CURRENT
129 alter table t1 drop partition p1;
130 alter table t1 drop partition p0;
131 ERROR HY000: Wrong partitions for `t1`: must have at least one HISTORY and exactly one last CURRENT
132 select x from t1;
133 x
134 1
135 2
136 # rename works
137 create or replace table t1 (x int) with system versioning
138 partition by system_time;
139 alter table t1 reorganize partition p0 into
140 (partition custom_name history);
141 show create table t1;
142 Table Create Table
143 t1 CREATE TABLE `t1` (
144 `x` int(11) DEFAULT NULL
145 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
146 PARTITION BY SYSTEM_TIME
147 (PARTITION `custom_name` HISTORY ENGINE = DEFAULT_ENGINE,
148 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
149 # merge and split doesn't (MDEV-19938)
150 create or replace table t1 (x int) with system versioning
151 partition by system_time limit 10 partitions 3;
152 alter table t1 reorganize partition p0, p1 into (partition p00 history);
153 ERROR HY000: REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers
154 alter table t1 reorganize partition p1 into (partition p1 history, partition p2 history);
155 ERROR HY000: REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers
156 # Bug tempesta-tech/mariadb#260: incorrect IB partitioning warning
157 create or replace table t1 (x int)
158 with system versioning
159 partition by system_time limit 1;
160 alter table t1 change x big int;
161 create or replace table t1 (i int) engine myisam partition by hash(i) partitions 2;
162 alter table t1 add partition (partition px history);
163 ERROR HY000: Wrong partition type `SYSTEM_TIME` for partitioning by `HASH`
164 ## INSERT, UPDATE, DELETE
165 create or replace table t1 (x int)
166 with system versioning
167 partition by system_time;
168 set @now= now(6);
169 insert into t1 values (1);
170 set @str= concat('select x, row_start < @now as A, row_end > @now as B from t1 partition (p0)');
171 prepare select_p0 from @str;
172 set @str= concat("select x, row_start > @now as C, row_end = SYS_TIME_MAX as D from t1 partition (pn)");;
173 prepare select_pn from @str;
174 execute select_p0;
175 x A B
176 execute select_pn;
177 x C D
178 1 1 1
179 set @str= concat('select row_start from t1 partition (pn) into @ts0');
180 prepare stmt from @str;
181 execute stmt;
182 drop prepare stmt;
183 set @now= now(6);
184 delete from t1;
185 execute select_p0;
186 x A B
187 1 1 1
188 execute select_pn;
189 x C D
190 set @str= concat('select row_start from t1 partition (p0) into @ts1');
191 prepare stmt from @str;
192 execute stmt;
193 drop prepare stmt;
194 select @ts0 = @ts1;
195 @ts0 = @ts1
196 1
197 set @now= now(6);
198 insert into t1 values (2);
199 execute select_p0;
200 x A B
201 1 1 0
202 execute select_pn;
203 x C D
204 2 1 1
205 set @str= concat('select row_start from t1 partition (pn) into @ts0');
206 prepare stmt from @str;
207 execute stmt;
208 drop prepare stmt;
209 set @now= now(6);
210 update t1 set x = x + 1;
211 execute select_p0;
212 x A B
213 1 1 0
214 2 1 1
215 execute select_pn;
216 x C D
217 3 1 1
218 drop prepare select_p0;
219 drop prepare select_pn;
220 set @str= concat('select row_start from t1 partition (p0) where x = 2 into @ts1');
221 prepare stmt from @str;
222 execute stmt;
223 drop prepare stmt;
224 set @str= concat('select row_end from t1 partition (p0) where x = 2 into @ts2');
225 prepare stmt from @str;
226 execute stmt;
227 drop prepare stmt;
228 set @str= concat('select row_start from t1 partition (pn) into @ts3');
229 prepare stmt from @str;
230 execute stmt;
231 drop prepare stmt;
232 select @ts0 = @ts1;
233 @ts0 = @ts1
234 1
235 select @ts2 = @ts3;
236 @ts2 = @ts3
237 1
238 #
239 # Rotation by LIMIT
240 #
241 create or replace table t1 (x int)
242 with system versioning
243 partition by system_time limit 0 partitions 3;
244 ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'LIMIT'
245 create or replace table t1 (x int)
246 with system versioning
247 partition by system_time limit 2 partitions 3;
248 show create table t1;
249 Table Create Table
250 t1 CREATE TABLE `t1` (
251 `x` int(11) DEFAULT NULL
252 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
253 PARTITION BY SYSTEM_TIME LIMIT 2
254 PARTITIONS 3
255 alter table t1 drop partition non_existent;
256 ERROR HY000: Wrong partition name or partition list
257 insert into t1 values (1), (2), (3), (4), (5), (6);
258 select * from t1 partition (pn);
259 x
260 1
261 2
262 3
263 4
264 5
265 6
266 delete from t1 where x < 4;
267 delete from t1;
268 Warnings:
269 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
270 # You see warning above ^
271 select * from t1 partition (p0);
272 x
273 1
274 2
275 3
276 select * from t1 partition (p1);
277 x
278 4
279 5
280 6
281 insert into t1 values (7), (8);
282 ### warn about full partition
283 delete from t1;
284 Warnings:
285 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
286 # You see warning above ^
287 select * from t1 partition (p1) order by x;
288 x
289 4
290 5
291 6
292 7
293 8
294 #
295 # Rotation by INTERVAL
296 #
297 create or replace table t1 (x int)
298 with system versioning
299 partition by system_time interval 0 second partitions 3;
300 ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL'
301 create table t1 (i int) with system versioning
302 partition by system_time interval 6 day limit 98;
303 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'limit 98' at line 2
304 create or replace table t1 (pk int) with system versioning
305 partition by system_time interval 60 year partitions 4;
306 ERROR 22003: TIMESTAMP value is out of range in 'INTERVAL'
307 # INTERVAL and ALTER TABLE
308 create or replace table t1 (i int) with system versioning
309 partition by system_time interval 1 hour;
310 set @ts=(select partition_description from information_schema.partitions
311 where table_schema='test' and table_name='t1' and partition_name='p0');
312 alter table t1 add column b int;
313 select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1';
314 partition_name partition_ordinal_position partition_method timediff(partition_description, @ts)
315 p0 1 SYSTEM_TIME 00:00:00.000000
316 pn 2 SYSTEM_TIME NULL
317 Warnings:
318 Warning 1292 Incorrect time value: 'CURRENT' for column `information_schema`.`(temporary)`.`PARTITION_DESCRIPTION` at row 2
319 alter table t1 add partition (partition p1 history, partition p2 history);
320 select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1';
321 partition_name partition_ordinal_position partition_method timediff(partition_description, @ts)
322 p0 1 SYSTEM_TIME 00:00:00.000000
323 p1 2 SYSTEM_TIME 01:00:00.000000
324 p2 3 SYSTEM_TIME 02:00:00.000000
325 pn 4 SYSTEM_TIME NULL
326 Warnings:
327 Warning 1292 Incorrect time value: 'CURRENT' for column `information_schema`.`(temporary)`.`PARTITION_DESCRIPTION` at row 4
328 alter table t1 drop partition p0;
329 select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1';
330 partition_name partition_ordinal_position partition_method timediff(partition_description, @ts)
331 p1 1 SYSTEM_TIME 01:00:00.000000
332 p2 2 SYSTEM_TIME 02:00:00.000000
333 pn 3 SYSTEM_TIME NULL
334 Warnings:
335 Warning 1292 Incorrect time value: 'CURRENT' for column `information_schema`.`(temporary)`.`PARTITION_DESCRIPTION` at row 3
336 alter table t1 drop partition p2;
337 ERROR HY000: Can only drop oldest partitions when rotating by INTERVAL
338 select partition_name,partition_ordinal_position,partition_method,timediff(partition_description, @ts) from information_schema.partitions where table_schema='test' and table_name='t1';
339 partition_name partition_ordinal_position partition_method timediff(partition_description, @ts)
340 p1 1 SYSTEM_TIME 01:00:00.000000
341 p2 2 SYSTEM_TIME 02:00:00.000000
342 pn 3 SYSTEM_TIME NULL
343 Warnings:
344 Warning 1292 Incorrect time value: 'CURRENT' for column `information_schema`.`(temporary)`.`PARTITION_DESCRIPTION` at row 3
345 set timestamp=unix_timestamp('2001-02-03 10:20:30');
346 create or replace table t1 (i int) with system versioning
347 partition by system_time interval 1 day
348 subpartition by key (i) subpartitions 2
349 (partition p1 history, partition pn current);
350 set timestamp=unix_timestamp('2001-02-03 10:20:40');
351 insert t1 values (1);
352 delete from t1;
353 set timestamp=unix_timestamp('2001-02-04 10:20:50');
354 insert t1 values (2);
355 delete from t1;
356 Warnings:
357 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions
358 select subpartition_name, partition_description, table_rows from information_schema.partitions where table_schema='test' and table_name='t1';
359 subpartition_name partition_description table_rows
360 p1sp0 2001-02-04 00:00:00 1
361 p1sp1 2001-02-04 00:00:00 1
362 pnsp0 CURRENT 0
363 pnsp1 CURRENT 0
364 select * from t1 partition (p1);
365 i
366 1
367 2
368 set timestamp=unix_timestamp('2001-02-04 10:20:55');
369 alter table t1 add partition (partition p0 history, partition p2 history);
370 set timestamp=unix_timestamp('2001-02-04 10:30:00');
371 insert t1 values (4),(5);
372 set timestamp=unix_timestamp('2001-02-04 10:30:10');
373 update t1 set i=6 where i=5;
374 select subpartition_name, partition_description, table_rows from information_schema.partitions where table_schema='test' and table_name='t1';
375 subpartition_name partition_description table_rows
376 p1sp0 2001-02-04 00:00:00 1
377 p1sp1 2001-02-04 00:00:00 0
378 p0sp0 2001-02-05 00:00:00 1
379 p0sp1 2001-02-05 00:00:00 1
380 p2sp0 2001-02-06 00:00:00 0
381 p2sp1 2001-02-06 00:00:00 0
382 pnsp0 CURRENT 0
383 pnsp1 CURRENT 2
384 select * from t1 partition (p1);
385 i
386 1
387 select * from t1 partition (p0);
388 i
389 5
390 2
391 select * from t1 partition (p2);
392 i
393 alter table t1 rebuild partition p0, p1, p2;
394 select * from t1 partition (p1);
395 i
396 1
397 select * from t1 partition (p0);
398 i
399 5
400 2
401 select * from t1 partition (p2);
402 i
403 ## pruning check
404 set @ts=(select partition_description from information_schema.partitions
405 where table_schema='test' and table_name='t1' and partition_name='p0' limit 1);
406 select * from t1;
407 i
408 4
409 6
410 explain partitions select * from t1;
411 id select_type table partitions type possible_keys key key_len ref rows Extra
412 1 SIMPLE t1 pn_pnsp0,pn_pnsp1 ALL NULL NULL NULL NULL # Using where
413 explain partitions select * from t1 for system_time as of '2001-02-04 10:20:30';
414 id select_type table partitions type possible_keys key key_len ref rows Extra
415 1 SIMPLE t1 p0_p0sp0,p0_p0sp1,p2_p2sp0,p2_p2sp1,pn_pnsp0,pn_pnsp1 ALL NULL NULL NULL NULL # Using where
416 set @ts=(select row_end from t1 for system_time all where i=1);
417 select * from t1 for system_time all where row_end = @ts;
418 i
419 1
420 explain partitions select * from t1 for system_time all where row_end = @ts;
421 id select_type table partitions type possible_keys key key_len ref rows Extra
422 1 SIMPLE t1 p1_p1sp0,p1_p1sp1 # NULL NULL NULL NULL # #
423 #
424 # MDEV-16023 Unfortunate error message WARN_VERS_PART_FULL
425 #
426 set timestamp= unix_timestamp('2020-07-29 10:30:10');
427 create or replace table t1 (a int) with system versioning
428 partition by system_time interval 1 second (
429 partition p0 history,
430 partition p1 history,
431 partition pc current
432 );
433 set timestamp= unix_timestamp('2020-07-29 10:30:14');
434 insert into t1 values (1),(2),(3);
435 show warnings;
436 Level Code Message
437 # Cleanup
438 set timestamp= default;
439 ## INTERVAL ... STARTS
440 create or replace table t1 (i int) with system versioning
441 partition by system_time interval 1 day starts 'a';
442 ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'STARTS'
443 create or replace table t1 (i int) with system versioning
444 partition by system_time interval 1 day starts '00:00:00';
445 ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'STARTS'
446 create or replace table t1 (i int) with system versioning
447 partition by system_time interval 1 day starts '2000-00-01 00:00:00';
448 ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'STARTS'
449 create or replace table t1 (i int) with system versioning
450 partition by system_time interval 1 day starts 946684800;
451 ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'STARTS'
452 create or replace table t1 (i int) with system versioning
453 partition by system_time interval 1 day starts '2000-01-01 00:00:00';
454 show create table t1;
455 Table Create Table
456 t1 CREATE TABLE `t1` (
457 `i` int(11) DEFAULT NULL
458 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
459 PARTITION BY SYSTEM_TIME INTERVAL 1 DAY STARTS TIMESTAMP'2000-01-01 00:00:00'
460 PARTITIONS 2
461 # Test STARTS warning
462 set timestamp= unix_timestamp('2000-01-01 00:00:00');
463 create or replace table t1 (i int) with system versioning
464 partition by system_time interval 1 day;
465 show create table t1;
466 Table Create Table
467 t1 CREATE TABLE `t1` (
468 `i` int(11) DEFAULT NULL
469 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
470 PARTITION BY SYSTEM_TIME INTERVAL 1 DAY STARTS TIMESTAMP'2000-01-01 00:00:00'
471 PARTITIONS 2
472 create or replace table t1 (i int) with system versioning
473 partition by system_time interval 1 day starts '2000-01-01 00:00:01';
474 Warnings:
475 Warning 4164 `t1`: STARTS is later than query time, first history partition may exceed INTERVAL value
476 # Test default STARTS rounding
477 set timestamp= unix_timestamp('1999-12-15 13:33:33');
478 create or replace table t1 (i int) with system versioning
479 partition by system_time interval 1 second;
480 show create table t1;
481 Table Create Table
482 t1 CREATE TABLE `t1` (
483 `i` int(11) DEFAULT NULL
484 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
485 PARTITION BY SYSTEM_TIME INTERVAL 1 SECOND STARTS TIMESTAMP'1999-12-15 13:33:33'
486 PARTITIONS 2
487 create or replace table t1 (i int) with system versioning
488 partition by system_time interval 1 minute;
489 show create table t1;
490 Table Create Table
491 t1 CREATE TABLE `t1` (
492 `i` int(11) DEFAULT NULL
493 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
494 PARTITION BY SYSTEM_TIME INTERVAL 1 MINUTE STARTS TIMESTAMP'1999-12-15 13:33:00'
495 PARTITIONS 2
496 create or replace table t1 (i int) with system versioning
497 partition by system_time interval 1 hour;
498 show create table t1;
499 Table Create Table
500 t1 CREATE TABLE `t1` (
501 `i` int(11) DEFAULT NULL
502 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
503 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'1999-12-15 13:00:00'
504 PARTITIONS 2
505 create or replace table t1 (i int) with system versioning
506 partition by system_time interval 1 day;
507 show create table t1;
508 Table Create Table
509 t1 CREATE TABLE `t1` (
510 `i` int(11) DEFAULT NULL
511 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
512 PARTITION BY SYSTEM_TIME INTERVAL 1 DAY STARTS TIMESTAMP'1999-12-15 00:00:00'
513 PARTITIONS 2
514 create or replace table t1 (i int) with system versioning
515 partition by system_time interval 1 month;
516 show create table t1;
517 Table Create Table
518 t1 CREATE TABLE `t1` (
519 `i` int(11) DEFAULT NULL
520 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
521 PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH STARTS TIMESTAMP'1999-12-15 00:00:00'
522 PARTITIONS 2
523 create or replace table t1 (i int) with system versioning
524 partition by system_time interval 1 year;
525 show create table t1;
526 Table Create Table
527 t1 CREATE TABLE `t1` (
528 `i` int(11) DEFAULT NULL
529 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
530 PARTITION BY SYSTEM_TIME INTERVAL 1 YEAR STARTS TIMESTAMP'1999-12-15 00:00:00'
531 PARTITIONS 2
532 # seconds equivalent of 1 day does not round:
533 create or replace table t1 (i int) with system versioning
534 partition by system_time interval 86400 second;
535 show create table t1;
536 Table Create Table
537 t1 CREATE TABLE `t1` (
538 `i` int(11) DEFAULT NULL
539 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
540 PARTITION BY SYSTEM_TIME INTERVAL 86400 SECOND STARTS TIMESTAMP'1999-12-15 13:33:33'
541 PARTITIONS 2
542 # STARTS value is in local time_zone:
543 set time_zone="+03:00";
544 create or replace table t1 (i int) with system versioning
545 partition by system_time interval 1 day starts '2000-01-01 00:00:00';
546 Warnings:
547 Warning 4164 `t1`: STARTS is later than query time, first history partition may exceed INTERVAL value
548 set timestamp= unix_timestamp('2000-01-01 00:00:00');
549 create or replace table t2 (i int) with system versioning
550 partition by system_time interval 1 day;
551 show create table t1;
552 Table Create Table
553 t1 CREATE TABLE `t1` (
554 `i` int(11) DEFAULT NULL
555 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
556 PARTITION BY SYSTEM_TIME INTERVAL 1 DAY STARTS TIMESTAMP'2000-01-01 00:00:00'
557 PARTITIONS 2
558 show create table t2;
559 Table Create Table
560 t2 CREATE TABLE `t2` (
561 `i` int(11) DEFAULT NULL
562 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
563 PARTITION BY SYSTEM_TIME INTERVAL 1 DAY STARTS TIMESTAMP'2000-01-01 00:00:00'
564 PARTITIONS 2
565 set time_zone="+00:00";
566 show create table t1;
567 Table Create Table
568 t1 CREATE TABLE `t1` (
569 `i` int(11) DEFAULT NULL
570 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
571 PARTITION BY SYSTEM_TIME INTERVAL 1 DAY STARTS TIMESTAMP'1999-12-31 21:00:00'
572 PARTITIONS 2
573 show create table t2;
574 Table Create Table
575 t2 CREATE TABLE `t2` (
576 `i` int(11) DEFAULT NULL
577 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
578 PARTITION BY SYSTEM_TIME INTERVAL 1 DAY STARTS TIMESTAMP'1999-12-31 21:00:00'
579 PARTITIONS 2
580 # Test rotation
581 set timestamp= unix_timestamp('2001-01-01 00:00:00');
582 # it's ok to add partitions for past:
583 create or replace table t1 (i int) with system versioning
584 partition by system_time interval 1 day starts '2000-01-01 00:00:00'
585 partitions 3;
586 insert into t1 values (0);
587 set timestamp= unix_timestamp('2001-01-01 00:00:01');
588 update t1 set i= i + 1;
589 Warnings:
590 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions
591 set timestamp= unix_timestamp('2001-01-01 00:00:02');
592 update t1 set i= i + 1;
593 Warnings:
594 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions
595 select *, row_end from t1 partition (p0);
596 i row_end
597 select *, row_end from t1 partition (p1);
598 i row_end
599 0 2001-01-01 00:00:01.000000
600 1 2001-01-01 00:00:02.000000
601 set timestamp= unix_timestamp('2000-01-01 00:00:00');
602 # now we "overflow" first partition a bit:
603 create or replace table t1 (i int) with system versioning
604 partition by system_time interval 1 day starts '2000-01-03 00:00:00'
605 partitions 3;
606 Warnings:
607 Warning 4164 `t1`: STARTS is later than query time, first history partition may exceed INTERVAL value
608 insert into t1 values (0);
609 set timestamp= unix_timestamp('2000-01-01 00:00:01');
610 update t1 set i= i + 1;
611 set timestamp= unix_timestamp('2000-01-02 00:00:01');
612 update t1 set i= i + 1;
613 set timestamp= unix_timestamp('2000-01-03 00:00:01');
614 update t1 set i= i + 1;
615 set timestamp= unix_timestamp('2000-01-04 00:00:01');
616 update t1 set i= i + 1;
617 select *, row_end from t1 partition (p0);
618 i row_end
619 0 2000-01-01 00:00:01.000000
620 1 2000-01-02 00:00:01.000000
621 2 2000-01-03 00:00:01.000000
622 select *, row_end from t1 partition (p1);
623 i row_end
624 3 2000-01-04 00:00:01.000000
625 # and this is how it usually goes:
626 set timestamp= unix_timestamp('2000-01-01 00:00:00');
627 create or replace table t1 (i int) with system versioning
628 partition by system_time interval 1 day
629 partitions 3;
630 insert into t1 values (0);
631 set timestamp= unix_timestamp('2000-01-01 00:00:01');
632 update t1 set i= i + 1;
633 set timestamp= unix_timestamp('2000-01-02 00:00:01');
634 update t1 set i= i + 1;
635 set timestamp= unix_timestamp('2000-01-03 00:00:01');
636 update t1 set i= i + 1;
637 Warnings:
638 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions
639 set timestamp= unix_timestamp('2000-01-04 00:00:01');
640 update t1 set i= i + 1;
641 Warnings:
642 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions
643 alter table t1 add partition (partition p2 history, partition p3 history);
644 select *, row_end from t1 partition (p0);
645 i row_end
646 0 2000-01-01 00:00:01.000000
647 select *, row_end from t1 partition (p1);
648 i row_end
649 1 2000-01-02 00:00:01.000000
650 select *, row_end from t1 partition (p2);
651 i row_end
652 2 2000-01-03 00:00:01.000000
653 select *, row_end from t1 partition (p3);
654 i row_end
655 3 2000-01-04 00:00:01.000000
656 drop tables t1, t2;
657 ## Subpartitions
658 create or replace table t1 (x int)
659 with system versioning
660 partition by system_time limit 2 partitions 3
661 subpartition by key (x)
662 subpartitions 2;
663 insert into t1 (x) values (1), (2), (3), (4), (5);
664 select * from t1 partition (pnsp0);
665 x
666 1
667 3
668 5
669 select * from t1 partition (pnsp1);
670 x
671 2
672 4
673 delete from t1 where x < 3;
674 delete from t1;
675 Warnings:
676 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
677 # You see warning above ^
678 delete from t1;
679 Warnings:
680 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
681 # You see warning above ^ (no matter if nothing was deleted)
682 select * from t1 partition (p0sp0);
683 x
684 1
685 select * from t1 partition (p0sp1);
686 x
687 2
688 select * from t1 partition (p1sp0);
689 x
690 3
691 5
692 select * from t1 partition (p1sp1);
693 x
694 4
695 # check implicit sys fields for implicit engine of partitioned table
696 create or replace table t1 (a bigint)
697 with system versioning
698 partition by range (a)
699 (partition p0 values less than (20) engine innodb,
700 partition p1 values less than maxvalue engine innodb);
701 insert into t1 values (1);
702 select * from t1 partition (p0);
703 a
704 1
705 # check for partition engine
706 create or replace table t1 (
707 f_int1 integer default 0
708 ) with system versioning
709 partition by range(f_int1)
710 subpartition by hash(f_int1)
711 ( partition part1 values less than (1000)
712 (subpartition subpart11 storage engine = 'innodb',
713 subpartition subpart12 storage engine = 'innodb'));
714 insert into t1 values (1);
715 select * from t1 partition (part1);
716 f_int1
717 1
718 #
719 # TRX_ID versioning (moved from partition_innodb.test)
720 #
721 # MDEV-15951 system versioning by trx id doesn't work with partitioning
722 # currently trx_id does not support partitioning by system_time
723 create or replace table t1(
724 i int,
725 row_start bigint unsigned generated always as row start,
726 row_end bigint unsigned generated always as row end,
727 period for system_time(row_start, row_end)
728 ) engine=InnoDB with system versioning partition by system_time (
729 partition p0 history,
730 partition pn current
731 );
732 ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `t1`
733 create or replace table t1(
734 i int,
735 row_start bigint unsigned generated always as row start,
736 row_end bigint unsigned generated always as row end,
737 period for system_time(row_start, row_end)
738 ) engine=InnoDB with system versioning;
739 alter table t1 partition by system_time (
740 partition p0 history,
741 partition pn current
742 );
743 ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `t1`
744 drop table t1;
745 create or replace table t (
746 a int primary key,
747 row_start bigint unsigned as row start invisible,
748 row_end bigint unsigned as row end invisible,
749 period for system_time(row_start, row_end)
750 ) engine=innodb with system versioning
751 partition by key() (
752 partition p1,
753 partition p2
754 );
755 ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END
756 create or replace table t (
757 a int primary key,
758 row_start bigint unsigned as row start invisible,
759 row_end bigint unsigned as row end invisible,
760 period for system_time(row_start, row_end)
761 ) engine=innodb with system versioning
762 partition by key(a, row_start) (
763 partition p1,
764 partition p2
765 );
766 ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END
767 create or replace table t (
768 a int primary key,
769 row_start bigint unsigned as row start invisible,
770 row_end bigint unsigned as row end invisible,
771 period for system_time(row_start, row_end)
772 ) engine=innodb with system versioning
773 partition by hash(a + row_end * 2) (
774 partition p1,
775 partition p2
776 );
777 ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END
778 create or replace table t (
779 a int primary key,
780 row_start bigint unsigned as row start invisible,
781 row_end bigint unsigned as row end invisible,
782 period for system_time(row_start, row_end)
783 ) engine=innodb with system versioning
784 partition by range columns (a, row_start) (
785 partition p1 values less than (100, 100)
786 );
787 ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END
788 #
789 # Assertion in ALTER on warning from partitioning LIMIT [#446]
790 #
791 create or replace table t1 (x int) with system versioning;
792 insert into t1 values (1), (2);
793 delete from t1;
794 alter table t1 partition by system_time limit 1 (
795 partition p1 history,
796 partition pn current);
797 #
798 # MDEV-14649 Assertion `t->mysql_col_len == 8' failed in row_insert_for_mysql
799 #
800 create or replace table t1 (i int) engine=innodb partition by key(i);
801 alter table t1 add system versioning;
802 insert into t1 values();
803 #
804 # MDEV-14722 Assertion in ha_commit_trans for sub-statement
805 #
806 create or replace table t1 (i int) with system versioning
807 partition by system_time interval 1 day;
808 create or replace table t2 (f int);
809 create or replace trigger tr before insert on t2
810 for each row select table_rows from information_schema.tables
811 where table_name = 't1' into @a;
812 insert into t2 values (1);
813 #
814 # MDEV-14740 Locking assertion for system_time partitioning
815 #
816 create or replace table t1 (i int) with system versioning
817 partition by system_time interval 1 week;
818 create or replace table t2 (f int);
819 create or replace trigger tr before insert on t2
820 for each row select count(*) from t1 into @a;
821 insert into t2 values (1);
822 #
823 # MDEV-14747 ALTER PARTITION BY SYSTEM_TIME after LOCK TABLES
824 #
825 create or replace table t1 (x int) with system versioning;
826 lock table t1 write;
827 alter table t1 partition by system_time interval 1 week (
828 partition p1 history,
829 partition pn current);
830 unlock tables;
831 #
832 # MDEV-14748 Assertion in ha_myisammrg::attach_children()
833 #
834 create or replace table t1 (x int) engine=myisam with system versioning
835 partition by system_time interval 1 month (partition p1 history, partition pn current);
836 create or replace table t2 (x int) engine=myisam;
837 create or replace table t3 (x int) engine=merge union=(t2);
838 create or replace table t4 (x int) engine=myisam;
839 create or replace trigger tr after insert on t4 for each row insert into t2
840 ( select x from t3 ) union ( select x from t1 );
841 insert into t4 values (1);
842 #
843 # MDEV-14821 Assertion failure
844 #
845 create or replace table t1 (x int) with system versioning;
846 insert into t1 values (0), (1);
847 update t1 set x= x + 1;
848 alter table t1 partition by system_time limit 1 (
849 partition p1 history,
850 partition p2 history,
851 partition pn current);
852 delete from t1 where x = 1;
853 # You see warning above ^
854 delete from t1 where x = 2;
855 Warnings:
856 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p2`) is out of LIMIT, need more HISTORY partitions
857 # You see warning above ^
858 #
859 # MDEV-14923 Assertion upon INSERT into locked versioned partitioned table
860 #
861 create or replace table t1 (x int) with system versioning
862 partition by system_time;
863 lock table t1 write;
864 alter table t1 add partition (partition p0 history);
865 ERROR HY000: Duplicate partition name p0
866 insert into t1 values (1);
867 unlock tables;
868 #
869 # MDEV-15103 Assertion in ha_partition::part_records() for updating VIEW
870 #
871 create or replace table t1 (pk int primary key, f int) with system versioning
872 partition by system_time limit 100;
873 insert into t1 values (1,10), (2,20);
874 create or replace view v1 as select * from t1;
875 update v1 set f= 30;
876 #
877 # MDEV-15168 Unexpected ER_VERS_ENGINE_UNSUPPORTED upon dropping versioning on a partitioned table
878 #
879 create or replace table t (a int) with system versioning
880 partition by system_time;
881 alter table t drop system versioning;
882 ERROR HY000: Can not DROP SYSTEM VERSIONING for table `t` partitioned BY SYSTEM_TIME
883 #
884 # MDEV-15191 Assertion `bit < (map)->n_bits' failed in bitmap_is_set upon INSERT
885 #
886 create or replace table t1 (i int) with system versioning;
887 insert into t1 values (1), (2);
888 update t1 set i= 3;
889 alter table t1 partition by system_time interval 1 month (partition p1 history, partition pn current);
890 lock table t1 write;
891 alter table t1 add partition (partition p2 history);
892 insert into t1 values (4);
893 unlock tables;
894 #
895 # 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
896 #
897 create or replace table t1 (a int) with system versioning
898 partition by system_time limit 2 partitions 4;
899 insert into t1 values (1),(2),(3);
900 update t1 set a = 4;
901 delete from t1;
902 delete from t1 where a is not null;
903 #
904 # MDEV-14823 Wrong error message upon selecting from a system_time partition
905 #
906 create or replace table t1 (i int) with system versioning partition by system_time limit 10;
907 select * from t1 partition (p0) for system_time all;
908 ERROR HY000: SYSTEM_TIME partitions in table `t1` does not support historical query
909 # MDEV-18929 2nd execution of SP does not detect ER_VERS_NOT_VERSIONED
910 create or replace procedure sp()
911 select * from t1 partition (p0) for system_time all;
912 call sp;
913 ERROR HY000: SYSTEM_TIME partitions in table `t1` does not support historical query
914 call sp;
915 ERROR HY000: SYSTEM_TIME partitions in table `t1` does not support historical query
916 drop procedure sp;
917 #
918 # MDEV-15380 Index for versioned table gets corrupt after partitioning and DELETE
919 #
920 create or replace table t1 (pk int primary key)
921 engine=myisam
922 with system versioning
923 partition by key() partitions 3;
924 set timestamp=1523466002.799571;
925 insert into t1 values (11),(12);
926 set timestamp=1523466004.169435;
927 delete from t1 where pk in (11, 12);
928 Same test but for Aria storage engine
929 create or replace table t1 (pk int primary key)
930 engine=aria
931 with system versioning
932 partition by key() partitions 3;
933 set timestamp=1523466002.799571;
934 insert into t1 values (11),(12);
935 set timestamp=1523466004.169435;
936 delete from t1 where pk in (11, 12);
937 #
938 # MDEV-18136 Server crashes in Item_func_dyncol_create::prepare_arguments
939 #
940 create or replace table t1 (pk int) with system versioning
941 partition by system_time interval 7 second;
942 alter table t1
943 partition by system_time interval column_get(column_create(7,7), 7 as int) second (
944 partition ver_p1 history,
945 partition ver_pn current);
946 show create table t1;
947 Table Create Table
948 t1 CREATE TABLE `t1` (
949 `pk` int(11) DEFAULT NULL
950 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
951 PARTITION BY SYSTEM_TIME INTERVAL 7 SECOND STARTS TIMESTAMP'2018-04-11 17:00:04'
952 (PARTITION `ver_p1` HISTORY ENGINE = DEFAULT_ENGINE,
953 PARTITION `ver_pn` CURRENT ENGINE = DEFAULT_ENGINE)
954 set timestamp= default;
955 #
956 # MDEV-18794 Assertion `!m_innodb' failed in ha_partition::cmp_ref upon SELECT from partitioned table
957 #
958 create or replace table t1 (pk int auto_increment, i int, c char(1), primary key (pk), key(i))
959 engine=innodb with system versioning partition by key() partitions 2;
960 insert into t1 (i, c) values (1, 'a'), (2, 'b'), (null, 'c'), (null, 'b');
961 alter table t1 drop system versioning;
962 replace into t1 select * from t1;
963 select * from t1 where i > 0 or pk = 1000 limit 1;
964 pk i c
965 1 1 a
966 drop table t1;
967 #
968 # MDEV-19175 Server crashes in ha_partition::vers_can_native upon INSERT DELAYED into versioned partitioned table
969 #
970 create or replace table t1 (f int) with system versioning partition by hash(f);
971 insert delayed into t1 values (1);
972 #
973 # MDEV-20068 History partition rotation is not done under LOCK TABLES
974 #
975 create or replace table t1 (x int) with system versioning partition by system_time limit 1
976 (partition p1 history, partition pn current);
977 lock tables t1 write;
978 insert into t1 values (0), (1), (2), (3);
979 delete from t1 where x < 3;
980 Warnings:
981 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
982 # You see warning above ^
983 delete from t1;
984 Warnings:
985 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
986 # You see warning above ^
987 unlock tables;
988 #
989 # MDEV-20336 Assertion bitmap_is_set(read_partitions) upon SELECT FOR UPDATE from versioned table
990 #
991 create or replace table t1 (pk int primary key) with system versioning partition by system_time limit 100 (partition p1 history, partition pn current);
992 execute immediate 'select * from t1 for update';
993 pk
994 #
995 # MDEV-19903 Setup default partitions for system versioning
996 #
997 create or replace table t1 (x int) with system versioning partition by system_time;
998 show create table t1;
999 Table Create Table
1000 t1 CREATE TABLE `t1` (
1001 `x` int(11) DEFAULT NULL
1002 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1003 PARTITION BY SYSTEM_TIME
1004 PARTITIONS 2
1005 # 2 partitions are created: p0 and pn
1006 select PARTITION_NAME, PARTITION_METHOD, PARTITION_DESCRIPTION from information_schema.partitions where table_name = 't1' order by PARTITION_NAME;
1007 PARTITION_NAME PARTITION_METHOD PARTITION_DESCRIPTION
1008 p0 SYSTEM_TIME NULL
1009 pn SYSTEM_TIME CURRENT
1010 create or replace table t1 (x int) with system versioning partition by system_time limit 10 partitions 4;
1011 show create table t1;
1012 Table Create Table
1013 t1 CREATE TABLE `t1` (
1014 `x` int(11) DEFAULT NULL
1015 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1016 PARTITION BY SYSTEM_TIME LIMIT 10
1017 PARTITIONS 4
1018 # 4 partitions are created: p0, p1, p2 and pn
1019 select PARTITION_NAME, PARTITION_METHOD, PARTITION_DESCRIPTION from information_schema.partitions where table_name = 't1' order by PARTITION_NAME;
1020 PARTITION_NAME PARTITION_METHOD PARTITION_DESCRIPTION
1021 p0 SYSTEM_TIME NULL
1022 p1 SYSTEM_TIME NULL
1023 p2 SYSTEM_TIME NULL
1024 pn SYSTEM_TIME CURRENT
1025 # Test cleanup
1026 drop view v1;
1027 drop tables t, t1, t2, t3, t4;
1028 #
1029 # MDEV-18957 UPDATE with LIMIT clause is wrong for versioned partitioned tables
1030 #
1031 create or replace table t1 (
1032 x int,
1033 a varchar(255)
1034 ) with system versioning partition by system_time (partition p1 history, partition pn current);
1035 insert into t1 (x) values (1), (2), (3), (4);
1036 update t1 set a= 'foo' limit 3;
1037 update t1 set a= 'bar' limit 4;
1038 select * from t1;
1039 x a
1040 1 bar
1041 2 bar
1042 3 bar
1043 4 bar
1044 drop table t1;
1045 #
1046 # MDEV-21011 Table corruption reported for versioned partitioned table after DELETE: "Found a misplaced row"
1047 #
1048 create table t1 (a int) with system versioning
1049 partition by system_time limit 3
1050 (partition p1 history, partition p2 history, partition pn current);
1051 insert into t1 values (1),(2),(3),(4);
1052 delete from t1;
1053 delete from t1;
1054 check table t1;
1055 Table Op Msg_type Msg_text
1056 test.t1 check status OK
1057 drop table t1;
1058 #
1059 # MDEV-21233 Assertion `m_extra_cache' failed in ha_partition::late_extra_cache
1060 #
1061 create table t1 (id int, a varchar(8)) with system versioning partition by key (id) partitions 2;
1062 insert into t1 values (1,'foo'),(2,'bar');
1063 create table t2 (b int);
1064 insert into t2 values (1),(2);
1065 update t1, t2 set a = 1;
1066 drop table t1, t2;
1067 #
1068 # MDEV-20515 multi-update tries to position updated table by null reference
1069 #
1070 create or replace table t1 (a int);
1071 insert into t1 values (0), (1);
1072 create or replace table t2 (b int) with system versioning
1073 partition by system_time
1074 (partition p1 history, partition pn current);
1075 insert into t2 values (0), (2);
1076 update t1 left join t2 on a > b set b= 2 order by b;
1077 drop table t1, t2;
1078 #
1079 # MDEV-17091 Assertion `old_part_id == m_last_part' failed in
1080 # ha_partition::update_row or `part_id == m_last_part' in
1081 # ha_partition::delete_row upon UPDATE/DELETE after dropping versioning
1082 #
1083 create or replace table t1 (pk int primary key, f int) engine=innodb
1084 with system versioning
1085 partition by key() partitions 2;
1086 insert into t1 values (1,10),(2,20);
1087 # expected to hit same partition
1088 select * from t1 partition (p0);
1089 pk f
1090 1 10
1091 2 20
1092 alter table t1 drop system versioning;
1093 # 1 and 2 are expected to be in different partitions
1094 select * from t1 partition(p0);
1095 pk f
1096 1 10
1097 select * from t1 partition(p1);
1098 pk f
1099 2 20
1100 update t1 set f=pk;
1101 delete from t1;
1102 drop table t1;
1103 #
1104 # MDEV-22413 Server hangs upon UPDATE/DELETE on a view reading from versioned partitioned table
1105 #
1106 create or replace table t1 (f char(6)) engine innodb with system versioning;
1107 insert into t1 values (null);
1108 update t1 set f= 'foo';
1109 update t1 set f= 'bar';
1110 # You see warning above ^
1111 create or replace view v1 as select * from t1 for system_time all;
1112 update v1 set f = '';
1113 ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
1114 create or replace table t1 (f char(6)) engine innodb with system versioning
1115 partition by system_time limit 1
1116 (partition p1 history, partition p2 history, partition pn current);
1117 insert into t1 values (null);
1118 update t1 set f= 'foo';
1119 update t1 set f= 'bar';
1120 Warnings:
1121 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p2`) is out of LIMIT, need more HISTORY partitions
1122 create or replace view v1 as select * from t1 for system_time all;
1123 update v1 set f= '';
1124 ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
1125 delete from v1;
1126 ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
1127 drop view v1;
1128 drop table t1;
1129 #
1130 # MDEV-22112 Assertion `tab_part_info->part_type == RANGE_PARTITION || tab_part_info->part_type == LIST_PARTITION' failed in prep_alter_part_table
1131 #
1132 create table t1 (a int) with system versioning partition by system_time;
1133 drop table t1;
1134 create table t1 (a int) with system versioning partition by system_time
1135 (partition p1 history, partition pn current);
1136 alter table t1 add partition (partition p2);
1137 ERROR HY000: Wrong partition type `HASH` for partitioning by `SYSTEM_TIME`
1138 # MDEV-17891 Assertion failures in select_insert::abort_result_set and
1139 # mysql_load upon attempt to replace into a full table
1140 set @@max_heap_table_size= 1024*1024;
1141 create or replace table t1 (
1142 pk integer auto_increment,
1143 primary key (pk),
1144 f varchar(45000)
1145 ) charset=latin1 with system versioning engine=memory
1146 partition by system_time interval 1 year (partition p1 history,
1147 partition pn current);
1148 # fill the table until full
1149 insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),();
1150 insert into t1 (f) select f from t1;
1151 ERROR HY000: The table 't1' is full
1152 # leave space for exactly one record in current partition
1153 delete from t1 where pk = 1;
1154 # copy all data into history partition
1155 replace into t1 select * from t1;
1156 replace into t1 select * from t1;
1157 ERROR HY000: The table 't1' is full
1158 create or replace table t1 (
1159 pk integer auto_increment,
1160 primary key (pk),
1161 f varchar(45000)
1162 ) charset=latin1 with system versioning engine=memory
1163 partition by system_time interval 1 year (partition p1 history,
1164 partition pn current);
1165 insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),();
1166 select * into outfile 'MDEV-17891.data' from t1;
1167 load data infile 'MDEV-17891.data' replace into table t1;
1168 load data infile 'MDEV-17891.data' replace into table t1;
1169 ERROR HY000: The table 't1' is full
1170 load data infile 'MDEV-17891.data' replace into table t1;
1171 ERROR HY000: The table 't1' is full
1172 set @@max_heap_table_size= 1048576;
1173 drop table t1;
1174 #
1175 # MDEV-22178 Assertion `info->alias.str' failed in partition_info::check_partition_info instead of ER_VERS_WRONG_PARTS
1176 #
1177 create or replace table t1 (a int) with system versioning;
1178 alter table t1 partition by system_time (partition pn current);
1179 ERROR HY000: Wrong partitions for `t1`: must have at least one HISTORY and exactly one last CURRENT
1180 drop table t1;
1181 #
1182 # MDEV-22247 History partition overflow leads to wrong SELECT result
1183 #
1184 set timestamp= unix_timestamp('2000-01-01 00:00:00');
1185 create or replace table t1 (x int) with system versioning
1186 partition by system_time interval 1 hour
1187 (partition p0 history, partition p1 history, partition pn current);
1188 insert into t1 values (0);
1189 update t1 set x= x + 1;
1190 set timestamp= unix_timestamp('2000-01-01 02:00:01');
1191 update t1 set x= x + 1;
1192 Warnings:
1193 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions
1194 select *, row_start, row_end from t1 for system_time as of '2000-01-01 02:00:00';
1195 x row_start row_end
1196 1 2000-01-01 00:00:00.000000 2000-01-01 02:00:01.000000
1197 explain partitions select * from t1 for system_time as of '2000-01-01 02:00:00';
1198 id select_type table partitions type possible_keys key key_len ref rows Extra
1199 1 SIMPLE t1 p1,pn ALL NULL NULL NULL NULL # Using where
1200 explain partitions select * from t1;
1201 id select_type table partitions type possible_keys key key_len ref rows Extra
1202 1 SIMPLE t1 pn # NULL NULL NULL NULL # #
1203 drop table t1;
1204 #
1205 # MDEV-27244 Table corruption upon adding serial data type
1206 #
1207 create table t1 (f int, key(f)) with system versioning
1208 partition by system_time limit 10 (partition p0 history, partition pn current);
1209 alter table t1 add x serial;
1210 alter table t1 add partition (partition p1 history);
1211 alter table t1 add partition (partition p2 history);
1212 drop table t1;
1213 #
1214 # MDEV-27217 DELETE partition selection doesn't work for history partitions
1215 #
1216 create table t1 (f char) with system versioning
1217 partition by system_time limit 10 (
1218 partition p0 history,
1219 partition p1 history,
1220 partition p2 history,
1221 partition pn current);
1222 delete from t1 partition (p1);
1223 ERROR HY000: Not allowed for system-versioned table `test`.`t1`
1224 delete from t1 partition (p0, pn);
1225 ERROR HY000: Not allowed for system-versioned table `test`.`t1`
1226 delete from t1 partition (p0, p1);
1227 ERROR HY000: Not allowed for system-versioned table `test`.`t1`
1228 delete from t1 partition (p0, p1, pn);
1229 ERROR HY000: Not allowed for system-versioned table `test`.`t1`
1230 drop table t1;
1231 set timestamp=unix_timestamp('2000-01-01 00:00:00');
1232 create or replace table t1 (i int) with system versioning
1233 partition by system_time interval 1 day (
1234 partition p0 history,
1235 partition p1 history,
1236 partition pn current);
1237 set timestamp=unix_timestamp('2000-01-02 00:00:00');
1238 insert t1 values (1);
1239 delete from t1 partition (p0, pn);
1240 ERROR HY000: Not allowed for system-versioned table `test`.`t1`
1241 delete from t1 partition (p0, p1, pn);
1242 ERROR HY000: Not allowed for system-versioned table `test`.`t1`
1243 lock tables t1 write;
1244 delete from t1 partition (p0, pn);
1245 ERROR HY000: Not allowed for system-versioned table `test`.`t1`
1246 delete from t1;
1247 unlock tables;
1248 drop table t1;
1249 set timestamp= default;
1250 #
1251 # MDEV-25546 LIMIT partitioning does not respect ROLLBACK
1252 #
1253 create or replace table t1 (pk int primary key)
1254 with system versioning engine innodb
1255 partition by system_time limit 100 (
1256 partition p0 history,
1257 partition p1 history,
1258 partition pn current);
1259 insert into t1 select seq from seq_1_to_90;
1260 start transaction;
1261 replace into t1 select seq from seq_1_to_80;
1262 replace into t1 select seq from seq_1_to_70;
1263 replace into t1 select seq from seq_1_to_60;
1264 select partition_name, table_rows
1265 from information_schema.partitions
1266 where table_name = 't1';
1267 partition_name table_rows
1268 p0 150
1269 p1 60
1270 pn 90
1271 rollback;
1272 select partition_name, table_rows
1273 from information_schema.partitions
1274 where table_name = 't1';
1275 partition_name table_rows
1276 p0 0
1277 p1 0
1278 pn 90
1279 replace into t1 select seq from seq_1_to_10;
1280 select partition_name, table_rows
1281 from information_schema.partitions
1282 where table_name = 't1';
1283 partition_name table_rows
1284 p0 10
1285 p1 0
1286 pn 90
1287 drop table t1;
1288 #
1289 # MDEV-28271 Assertion on TRUNCATE PARTITION for PARTITION BY SYSTEM_TIME
1290 #
1291 create table t1 (x int) with system versioning
1292 partition by system_time limit 1 (
1293 partition p0 history,
1294 partition p1 history,
1295 partition p2 history, # p2 just disables warning about p1 partition full
1296 partition pn current);
1297 insert into t1 values (0);
1298 update t1 set x= x + 1;
1299 update t1 set x= x + 1;
1300 select * from t1 partition (p0);
1301 x
1302 0
1303 select * from t1 partition (p1);
1304 x
1305 1
1306 select * from t1 partition (pn);
1307 x
1308 2
1309 delete from t1;
1310 Warnings:
1311 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p2`) is out of LIMIT, need more HISTORY partitions
1312 delete history from t1;
1313 select * from t1 partition (p0);
1314 x
1315 select * from t1 partition (p1);
1316 x
1317 select * from t1 partition (pn);
1318 x
1319 insert into t1 values (0);
1320 update t1 set x= x + 1;
1321 update t1 set x= x + 1;
1322 # TRUNCATE PARTITION ALL does the same
1323 alter table t1 truncate partition all;
1324 select * from t1 partition (p0);
1325 x
1326 select * from t1 partition (p1);
1327 x
1328 select * from t1 partition (pn);
1329 x
1330 insert into t1 values (0);
1331 update t1 set x= x + 1;
1332 update t1 set x= x + 1;
1333 # TRUNCATE PARTITION deletes data from HISTORY partition
1334 alter table t1 truncate partition p1;
1335 select * from t1 partition (p0);
1336 x
1337 0
1338 select * from t1 partition (p1);
1339 x
1340 select * from t1 partition (pn);
1341 x
1342 2
1343 # or from CURRENT partition
1344 alter table t1 truncate partition pn;
1345 select * from t1 partition (p0);
1346 x
1347 0
1348 select * from t1 partition (p1);
1349 x
1350 select * from t1 partition (pn);
1351 x
1352 drop table t1;
1353 #
1354 # MDEV-20077 Warning on full history partition is delayed until next DML statement
1355 #
1356 # DELETE
1357 create table t1 (x int) with system versioning
1358 partition by system_time limit 100 (
1359 partition p0 history,
1360 partition p1 history,
1361 partition pn current);
1362 insert into t1 select seq from seq_0_to_200;
1363 # p0 is filled with 100 records (no warnings):
1364 delete from t1 where x <= 99;
1365 # p1 is filled with 1 + 100 records (warning is printed):
1366 delete from t1 where x <= 100;
1367 delete from t1;
1368 Warnings:
1369 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
1370 # You see warning above ^
1371 select count(*) from t1 partition (p0);
1372 count(*)
1373 100
1374 select count(*) from t1 partition (p1);
1375 count(*)
1376 101
1377 drop table t1;
1378 # DELETE under LOCK TABLES
1379 create table t1 (x int) with system versioning
1380 partition by system_time limit 100 (
1381 partition p0 history,
1382 partition p1 history,
1383 partition pn current);
1384 insert into t1 select seq from seq_0_to_200;
1385 lock tables t1 write;
1386 # (LOCK TABLES) p0 is filled with 100 records (no warnings):
1387 delete from t1 where x <= 99;
1388 # (LOCK TABLES) p1 is filled with 1 + 100 records (warning is printed):
1389 delete from t1 where x <= 100;
1390 delete from t1;
1391 Warnings:
1392 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
1393 # You see warning above ^
1394 unlock tables;
1395 select count(*) from t1 partition (p0);
1396 count(*)
1397 100
1398 select count(*) from t1 partition (p1);
1399 count(*)
1400 101
1401 drop table t1;
1402 # DELETE multitable
1403 create table t1 (x int) with system versioning
1404 partition by system_time limit 100 (
1405 partition p0 history,
1406 partition p1 history,
1407 partition pn current);
1408 create table t2 (y int);
1409 insert into t1 select seq from seq_0_to_200;
1410 insert into t2 select seq from seq_0_to_3;
1411 delete t1, t2 from t1 join t2 where x < 50 and y = 0;
1412 delete t1, t2 from t1 join t2 where x < 100 and y = 1;
1413 delete t1, t2 from t1 join t2 where x < 150 and y = 2;
1414 delete t1, t2 from t1 join t2;
1415 Warnings:
1416 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
1417 # You see warning above ^
1418 select count(*) from t1 partition (p0);
1419 count(*)
1420 100
1421 select count(*) from t1 partition (p1);
1422 count(*)
1423 101
1424 drop table t1;
1425 # UDPATE
1426 create table t1 (x int) with system versioning
1427 partition by system_time limit 100 (
1428 partition p0 history,
1429 partition p1 history,
1430 partition pn current);
1431 insert into t1 select seq from seq_0_to_49;
1432 update t1 set x= x + 1;
1433 update t1 set x= x + 1;
1434 update t1 set x= x + 1;
1435 update t1 set x= x + 1;
1436 Warnings:
1437 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
1438 # You see warning above ^
1439 select count(*) from t1 partition (p0);
1440 count(*)
1441 100
1442 select count(*) from t1 partition (p1);
1443 count(*)
1444 100
1445 drop tables t1, t2;
1446 # UPDATE multitable
1447 create table t1 (x int) with system versioning
1448 partition by system_time limit 100 (
1449 partition p0 history,
1450 partition p1 history,
1451 partition pn current);
1452 create table t2 (y int);
1453 insert into t1 select seq from seq_0_to_49;
1454 insert into t2 values (5);
1455 update t1, t2 set x= x + 1;
1456 update t1, t2 set x= x + 1;
1457 update t1, t2 set x= x + 1;
1458 update t1, t2 set x= x + 1;
1459 Warnings:
1460 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
1461 # You see warning above ^
1462 select count(*) from t1 partition (p0);
1463 count(*)
1464 100
1465 select count(*) from t1 partition (p1);
1466 count(*)
1467 100
1468 drop tables t1, t2;
1469 # INSERT .. ON DUPLICATE KEY UPDATE (ODKU)
1470 create table t1 (x int primary key) with system versioning
1471 partition by system_time limit 100 (
1472 partition p0 history,
1473 partition p1 history,
1474 partition pn current);
1475 insert into t1 select seq from seq_0_to_100;
1476 delete from t1 where x <= 99;
1477 insert into t1 values (100) on duplicate key update x= 400;
1478 select count(*) from t1 partition (p0);
1479 count(*)
1480 100
1481 select count(*) from t1 partition (p1);
1482 count(*)
1483 1
1484 drop table t1;
1485 # INSERT .. SELECT .. ON DUPLICATE KEY UPDATE (ODKU)
1486 create table t1 (x int primary key) with system versioning
1487 partition by system_time limit 100 (
1488 partition p0 history,
1489 partition p1 history,
1490 partition pn current);
1491 create table t2 (y int);
1492 insert into t2 values (100);
1493 insert into t1 select seq from seq_0_to_100;
1494 delete from t1 where x <= 99;
1495 insert into t1 select * from t2 on duplicate key update x= 500;
1496 select count(*) from t1 partition (p0);
1497 count(*)
1498 100
1499 select count(*) from t1 partition (p1);
1500 count(*)
1501 1
1502 drop tables t1, t2;
1503 # REPLACE
1504 create table t1 (x int primary key) with system versioning
1505 partition by system_time limit 100 (
1506 partition p0 history,
1507 partition p1 history,
1508 partition pn current);
1509 insert into t1 select seq from seq_0_to_100;
1510 delete from t1 where x < 99;
1511 replace t1 values (100);
1512 replace t1 values (100);
1513 select count(*) from t1 partition (p0);
1514 count(*)
1515 100
1516 select count(*) from t1 partition (p1);
1517 count(*)
1518 1
1519 drop table t1;
1520 # LOAD DATA .. REPLACE
1521 create table t1 (x int primary key) with system versioning
1522 partition by system_time limit 100 (
1523 partition p0 history,
1524 partition p1 history,
1525 partition pn current);
1526 insert into t1 select seq from seq_0_to_49;
1527 select x into outfile 'MDEV-20077.data' from t1;
1528 load data infile 'MDEV-20077.data' replace into table t1 (x);
1529 load data infile 'MDEV-20077.data' replace into table t1 (x);
1530 load data infile 'MDEV-20077.data' replace into table t1 (x);
1531 load data infile 'MDEV-20077.data' replace into table t1 (x);
1532 Warnings:
1533 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
1534 # You see warning above ^
1535 select count(*) from t1 partition (p0);
1536 count(*)
1537 100
1538 select count(*) from t1 partition (p1);
1539 count(*)
1540 100
1541 drop table t1;
1542 # REPLACE .. SELECT
1543 create table t1 (x int primary key) with system versioning
1544 partition by system_time limit 100 (
1545 partition p0 history,
1546 partition p1 history,
1547 partition pn current);
1548 insert into t1 select seq from seq_0_to_49;
1549 replace t1 select * from t1;
1550 replace t1 select * from t1;
1551 replace t1 select * from t1;
1552 replace t1 select * from t1;
1553 Warnings:
1554 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
1555 # You see warning above ^
1556 select count(*) from t1 partition (p0);
1557 count(*)
1558 100
1559 select count(*) from t1 partition (p1);
1560 count(*)
1561 100
1562 drop table t1;
1563 #
1564 # MDEV-28552 Assertion `inited==RND' failed in handler::ha_rnd_end
1565 #
1566 create table tcount (c int unsigned);
1567 insert into tcount values (0);
1568 create table t (f int) with system versioning
1569 partition by system_time limit 1000
1570 (partition p1 history, partition pn current);
1571 insert into t values (1),(2);
1572 create trigger tr before insert on t for each row update tcount set c = c + 1;
1573 insert into t select * from t;
1574 drop table tcount, t;
1575 #
1576 # 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
1577 #
1578 set timestamp=unix_timestamp('2000-01-01 00:00:00');
1579 create table t1 (i int);
1580 create table t2 (i int);
1581 alter table t1 partition by system_time
1582 interval (select i from t2) day (partition p1 history, partition pn current);
1583 ERROR 42000: INTERVAL does not support subqueries or stored functions
1584 drop table t1;
1585 create table t1 (id int) with system versioning
1586 partition by system_time
1587 interval (select i from t2) day (partition p1 history, partition pn current);
1588 ERROR 42000: INTERVAL does not support subqueries or stored functions
1589 create table t1 (id int) with system versioning
1590 partition by system_time
1591 interval "hello" day (partition p1 history, partition pn current);
1592 ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL'
1593 create table t1 (id int) with system versioning
1594 partition by system_time
1595 interval 3.893 day (partition p1 history, partition pn current);
1596 drop table t1, t2;
1597 create table t1 (id int) with system versioning
1598 partition by system_time interval "3-11" year_month (partition p1 history, partition pn current);
1599 show create table t1;
1600 Table Create Table
1601 t1 CREATE TABLE `t1` (
1602 `id` int(11) DEFAULT NULL
1603 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1604 PARTITION BY SYSTEM_TIME INTERVAL '3-11' YEAR_MONTH STARTS TIMESTAMP'2000-01-01 00:00:00'
1605 (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE,
1606 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
1607 drop table t1;
1608 create table t1 (id int) with system versioning
1609 partition by system_time interval "3 11" day_hour (partition p1 history, partition pn current);
1610 show create table t1;
1611 Table Create Table
1612 t1 CREATE TABLE `t1` (
1613 `id` int(11) DEFAULT NULL
1614 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1615 PARTITION BY SYSTEM_TIME INTERVAL '3 11' DAY_HOUR STARTS TIMESTAMP'2000-01-01 00:00:00'
1616 (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE,
1617 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
1618 drop table t1;
1619 create table t1 (id int) with system versioning
1620 partition by system_time interval "3 11:12" day_minute (partition p1 history, partition pn current);
1621 show create table t1;
1622 Table Create Table
1623 t1 CREATE TABLE `t1` (
1624 `id` int(11) DEFAULT NULL
1625 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1626 PARTITION BY SYSTEM_TIME INTERVAL '3 11:12' DAY_MINUTE STARTS TIMESTAMP'2000-01-01 00:00:00'
1627 (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE,
1628 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
1629 drop table t1;
1630 create table t1 (id int) with system versioning
1631 partition by system_time interval "3 11:12:13" day_second (partition p1 history, partition pn current);
1632 show create table t1;
1633 Table Create Table
1634 t1 CREATE TABLE `t1` (
1635 `id` int(11) DEFAULT NULL
1636 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1637 PARTITION BY SYSTEM_TIME INTERVAL '3 11:12:13' DAY_SECOND STARTS TIMESTAMP'2000-01-01 00:00:00'
1638 (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE,
1639 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
1640 drop table t1;
1641 create table t1 (id int) with system versioning
1642 partition by system_time interval "11:12" hour_minute (partition p1 history, partition pn current);
1643 show create table t1;
1644 Table Create Table
1645 t1 CREATE TABLE `t1` (
1646 `id` int(11) DEFAULT NULL
1647 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1648 PARTITION BY SYSTEM_TIME INTERVAL '11:12' HOUR_MINUTE STARTS TIMESTAMP'2000-01-01 00:00:00'
1649 (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE,
1650 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
1651 drop table t1;
1652 create table t1 (id int) with system versioning
1653 partition by system_time interval "11:12:13" hour_second (partition p1 history, partition pn current);
1654 show create table t1;
1655 Table Create Table
1656 t1 CREATE TABLE `t1` (
1657 `id` int(11) DEFAULT NULL
1658 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1659 PARTITION BY SYSTEM_TIME INTERVAL '11:12:13' HOUR_SECOND STARTS TIMESTAMP'2000-01-01 00:00:00'
1660 (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE,
1661 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
1662 drop table t1;
1663 create table t1 (id int) with system versioning
1664 partition by system_time interval "12:13" minute_second (partition p1 history, partition pn current);
1665 show create table t1;
1666 Table Create Table
1667 t1 CREATE TABLE `t1` (
1668 `id` int(11) DEFAULT NULL
1669 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1670 PARTITION BY SYSTEM_TIME INTERVAL '12:13' MINUTE_SECOND STARTS TIMESTAMP'2000-01-01 00:00:00'
1671 (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE,
1672 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
1673 drop table t1;
1674 create table t1 (id int) with system versioning
1675 partition by system_time interval "12:13.123" minute_microsecond (partition p1 history, partition pn current);
1676 ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL'
1677 #
1678 # End of 10.3 tests
1679 #
1680 #
1681 # MDEV-22283 Server crashes in key_copy or unexpected error 156: The table already existed in the storage engine
1682 #
1683 create table t1 (a int primary key) engine=aria page_checksum=0
1684 with system versioning
1685 partition by system_time (partition p1 history, partition pn current);
1686 alter table t1 add partition (partition p2 history);
1687 Warnings:
1688 Warning 4115 Maybe missing parameters: no rotation condition for multiple HISTORY partitions.
1689 show table status;
1690 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary
1691 t1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Got error 174 "Fatal error during initialization of handler" from storage engine Aria NULL NULL
1692 Warnings:
1693 Warning 1030 Got error 174 "Fatal error during initialization of handler" from storage engine Aria
1694 drop table t1;
1695 create table t1 (b int) engine=aria row_format=dynamic with system versioning
1696 partition by system_time (partition p1 history, partition pn current);
1697 insert into t1 values (1);
1698 replace into t1 values (1);
1699 drop table t1;
1700 #
1701 # MDEV-18794 Assertion `!m_innodb' failed in ha_partition::cmp_ref upon SELECT from partitioned table
1702 #
1703 create or replace table t1 (pk int auto_increment, i int, c char(1), primary key (pk), key(i))
1704 engine=innodb with system versioning partition by key() partitions 2;
1705 insert into t1 (i, c) values (1, 'a'), (2, 'b'), (null, 'c'), (null, 'b');
1706 alter table t1 drop system versioning;
1707 replace into t1 select * from t1;
1708 select * from t1 where i > 0 or pk = 1000 limit 1;
1709 pk i c
1710 1 1 a
1711 drop table t1;
1712 #
1713 # End of 10.4 tests
1714 #
1715 #
1716 # MDEV-22153 ALTER add default history partitions makes table inaccessible
1717 #
1718 create or replace table t1 (x int) with system versioning partition by system_time;
1719 alter table t1 add partition partitions 1;
1720 Warnings:
1721 Warning 4115 Maybe missing parameters: no rotation condition for multiple HISTORY partitions.
1722 show create table t1;
1723 Table Create Table
1724 t1 CREATE TABLE `t1` (
1725 `x` int(11) DEFAULT NULL
1726 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1727 PARTITION BY SYSTEM_TIME
1728 PARTITIONS 3
1729 alter table t1 add partition partitions 2;
1730 Warnings:
1731 Warning 4115 Maybe missing parameters: no rotation condition for multiple HISTORY partitions.
1732 show create table t1;
1733 Table Create Table
1734 t1 CREATE TABLE `t1` (
1735 `x` int(11) DEFAULT NULL
1736 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1737 PARTITION BY SYSTEM_TIME
1738 PARTITIONS 5
1739 alter table t1 add partition partitions 3;
1740 Warnings:
1741 Warning 4115 Maybe missing parameters: no rotation condition for multiple HISTORY partitions.
1742 show create table t1;
1743 Table Create Table
1744 t1 CREATE TABLE `t1` (
1745 `x` int(11) DEFAULT NULL
1746 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1747 PARTITION BY SYSTEM_TIME
1748 PARTITIONS 8
1749 drop tables t1;
1750 #
1751 # MDEV-22207 Drop default history partitions renders table inaccessible
1752 #
1753 create or replace table t1 (i int) with system versioning
1754 partition by system_time limit 1 partitions 5;
1755 alter table t1 drop partition p0, p2;
1756 show create table t1;
1757 Table Create Table
1758 t1 CREATE TABLE `t1` (
1759 `i` int(11) DEFAULT NULL
1760 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1761 PARTITION BY SYSTEM_TIME LIMIT 1
1762 (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE,
1763 PARTITION `p3` HISTORY ENGINE = DEFAULT_ENGINE,
1764 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
1765 alter table t1 add partition partitions 1;
1766 show create table t1;
1767 Table Create Table
1768 t1 CREATE TABLE `t1` (
1769 `i` int(11) DEFAULT NULL
1770 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1771 PARTITION BY SYSTEM_TIME LIMIT 1
1772 (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE,
1773 PARTITION `p3` HISTORY ENGINE = DEFAULT_ENGINE,
1774 PARTITION `p2` HISTORY ENGINE = DEFAULT_ENGINE,
1775 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
1776 drop tables t1;
1777 #
1778 # MDEV-22155 ALTER add default history partitions name clash on non-default partitions
1779 #
1780 set timestamp= default;
1781 create or replace table t1 (x int) with system versioning
1782 partition by system_time limit 1
1783 (partition p2 history, partition p8 history, partition pn current);
1784 alter table t1 add partition partitions 1;
1785 alter table t1 add partition partitions 2;
1786 show create table t1;
1787 Table Create Table
1788 t1 CREATE TABLE `t1` (
1789 `x` int(11) DEFAULT NULL
1790 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1791 PARTITION BY SYSTEM_TIME LIMIT 1
1792 (PARTITION `p2` HISTORY ENGINE = DEFAULT_ENGINE,
1793 PARTITION `p8` HISTORY ENGINE = DEFAULT_ENGINE,
1794 PARTITION `p3` HISTORY ENGINE = DEFAULT_ENGINE,
1795 PARTITION `p4` HISTORY ENGINE = DEFAULT_ENGINE,
1796 PARTITION `p5` HISTORY ENGINE = DEFAULT_ENGINE,
1797 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
1798 alter table t1 add partition partitions 8;
1799 show create table t1;
1800 Table Create Table
1801 t1 CREATE TABLE `t1` (
1802 `x` int(11) DEFAULT NULL
1803 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1804 PARTITION BY SYSTEM_TIME LIMIT 1
1805 (PARTITION `p2` HISTORY ENGINE = DEFAULT_ENGINE,
1806 PARTITION `p8` HISTORY ENGINE = DEFAULT_ENGINE,
1807 PARTITION `p3` HISTORY ENGINE = DEFAULT_ENGINE,
1808 PARTITION `p4` HISTORY ENGINE = DEFAULT_ENGINE,
1809 PARTITION `p5` HISTORY ENGINE = DEFAULT_ENGINE,
1810 PARTITION `p9` HISTORY ENGINE = DEFAULT_ENGINE,
1811 PARTITION `p10` HISTORY ENGINE = DEFAULT_ENGINE,
1812 PARTITION `p11` HISTORY ENGINE = DEFAULT_ENGINE,
1813 PARTITION `p12` HISTORY ENGINE = DEFAULT_ENGINE,
1814 PARTITION `p13` HISTORY ENGINE = DEFAULT_ENGINE,
1815 PARTITION `p14` HISTORY ENGINE = DEFAULT_ENGINE,
1816 PARTITION `p15` HISTORY ENGINE = DEFAULT_ENGINE,
1817 PARTITION `p16` HISTORY ENGINE = DEFAULT_ENGINE,
1818 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
1819 drop tables t1;
1820 #
1821 # MDEV-27328 Change of SYSTEM_TIME partitioning options is not possible without data copy
1822 #
1823 create or replace table t1 (f int) with system versioning
1824 partition by hash(f);
1825 alter table t1 partition by system_time;
1826 show create table t1;
1827 Table Create Table
1828 t1 CREATE TABLE `t1` (
1829 `f` int(11) DEFAULT NULL
1830 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1831 PARTITION BY SYSTEM_TIME
1832 PARTITIONS 2
1833 create or replace table t1 (f int) with system versioning
1834 partition by system_time;
1835 alter table t1 partition by hash(f);
1836 show create table t1;
1837 Table Create Table
1838 t1 CREATE TABLE `t1` (
1839 `f` int(11) DEFAULT NULL
1840 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1841 PARTITION BY HASH (`f`)
1842 create or replace table t1 (x int) with system versioning;
1843 alter table t1 partition by system_time;
1844 show create table t1;
1845 Table Create Table
1846 t1 CREATE TABLE `t1` (
1847 `x` int(11) DEFAULT NULL
1848 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1849 PARTITION BY SYSTEM_TIME
1850 PARTITIONS 2
1851 create or replace table t1 (x int) with system versioning
1852 partition by system_time limit 100 partitions 4;
1853 show create table t1;
1854 Table Create Table
1855 t1 CREATE TABLE `t1` (
1856 `x` int(11) DEFAULT NULL
1857 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1858 PARTITION BY SYSTEM_TIME LIMIT 100
1859 PARTITIONS 4
1860 alter table t1 add partition partitions 2;
1861 alter table t1 partition by system_time;
1862 show create table t1;
1863 Table Create Table
1864 t1 CREATE TABLE `t1` (
1865 `x` int(11) DEFAULT NULL
1866 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1867 PARTITION BY SYSTEM_TIME
1868 PARTITIONS 6
1869 alter table t1 partition by system_time limit 33;
1870 show create table t1;
1871 Table Create Table
1872 t1 CREATE TABLE `t1` (
1873 `x` int(11) DEFAULT NULL
1874 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1875 PARTITION BY SYSTEM_TIME LIMIT 33
1876 PARTITIONS 6
1877 set timestamp= unix_timestamp('2000-01-01 00:00:00');
1878 insert t1 values (0);
1879 set timestamp= unix_timestamp('2000-01-01 00:10:00');
1880 update t1 set x= x + 1;
1881 set timestamp= unix_timestamp('2000-01-01 01:00:00');
1882 update t1 set x= x + 1;
1883 set timestamp= unix_timestamp('2000-01-01 01:30:00');
1884 update t1 set x= x + 1;
1885 set timestamp= unix_timestamp('2000-01-01 02:00:00');
1886 update t1 set x= x + 1;
1887 alter table t1 partition by system_time interval 1 hour
1888 starts '2000-01-01 00:00:00';
1889 show create table t1;
1890 Table Create Table
1891 t1 CREATE TABLE `t1` (
1892 `x` int(11) DEFAULT NULL
1893 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1894 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00'
1895 PARTITIONS 6
1896 select * from t1 partition (p0);
1897 x
1898 0
1899 select * from t1 partition (p1);
1900 x
1901 1
1902 2
1903 select * from t1 partition (p2);
1904 x
1905 3
1906 select * from t1 partition (pn);
1907 x
1908 4
1909 set timestamp= default;
1910 alter table t1 partition by system_time limit 1;
1911 show create table t1;
1912 Table Create Table
1913 t1 CREATE TABLE `t1` (
1914 `x` int(11) DEFAULT NULL
1915 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1916 PARTITION BY SYSTEM_TIME LIMIT 1
1917 PARTITIONS 6
1918 update t1 set x= x + 1;
1919 update t1 set x= x + 1;
1920 Warnings:
1921 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p4`) is out of LIMIT, need more HISTORY partitions
1922 # You see warning above ^
1923 select * from t1 partition (p0);
1924 x
1925 0
1926 select * from t1 partition (p1);
1927 x
1928 1
1929 2
1930 select * from t1 partition (p2);
1931 x
1932 3
1933 select * from t1 partition (p3);
1934 x
1935 4
1936 select * from t1 partition (p4);
1937 x
1938 5
1939 select * from t1 partition (pn);
1940 x
1941 6
1942 drop table t1;
1943 # End of 10.6 tests
1944 #
1945 # MDEV-22166 MIGRATE PARTITION: move out partition into a table
1946 #
1947 create or replace table t1 (x int)
1948 with system versioning
1949 partition by range(x) (
1950 partition p1 values less than (10),
1951 partition p2 values less than (20),
1952 partition p3 values less than (30),
1953 partition p4 values less than (40),
1954 partition p5 values less than (50),
1955 partition pn values less than maxvalue);
1956 insert into t1 values (2), (12), (22), (32), (42), (52);
1957 update t1 set x= x + 1;
1958 alter table t1 convert partition p2 to table tp2;
1959 show create table tp2;
1960 Table Create Table
1961 tp2 CREATE TABLE `tp2` (
1962 `x` int(11) DEFAULT NULL
1963 ) ENGINE=X DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1964 select * from tp2;
1965 x
1966 13
1967 select * from tp2 for system_time all order by x;
1968 x
1969 12
1970 13
1971 show create table t1;
1972 Table Create Table
1973 t1 CREATE TABLE `t1` (
1974 `x` int(11) DEFAULT NULL
1975 ) ENGINE=X DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
1976 PARTITION BY RANGE (`x`)
1977 (PARTITION `p1` VALUES LESS THAN (10) ENGINE = X,
1978 PARTITION `p3` VALUES LESS THAN (30) ENGINE = X,
1979 PARTITION `p4` VALUES LESS THAN (40) ENGINE = X,
1980 PARTITION `p5` VALUES LESS THAN (50) ENGINE = X,
1981 PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = X)
1982 select * from t1 order by x;
1983 x
1984 3
1985 23
1986 33
1987 43
1988 53
1989 select * from t1 for system_time all order by x;
1990 x
1991 2
1992 3
1993 22
1994 23
1995 32
1996 33
1997 42
1998 43
1999 52
2000 53
2001 # SP
2002 create or replace procedure sp()
2003 alter table t1 convert partition p3 to table tp3;
2004 call sp;
2005 show create table tp3;
2006 Table Create Table
2007 tp3 CREATE TABLE `tp3` (
2008 `x` int(11) DEFAULT NULL
2009 ) ENGINE=X DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2010 select * from tp3;
2011 x
2012 23
2013 show create table t1;
2014 Table Create Table
2015 t1 CREATE TABLE `t1` (
2016 `x` int(11) DEFAULT NULL
2017 ) ENGINE=X DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2018 PARTITION BY RANGE (`x`)
2019 (PARTITION `p1` VALUES LESS THAN (10) ENGINE = X,
2020 PARTITION `p4` VALUES LESS THAN (40) ENGINE = X,
2021 PARTITION `p5` VALUES LESS THAN (50) ENGINE = X,
2022 PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = X)
2023 select * from t1 order by x;
2024 x
2025 3
2026 33
2027 43
2028 53
2029 drop table tp3;
2030 call sp;
2031 ERROR HY000: Wrong partition name or partition list
2032 call sp;
2033 ERROR HY000: Wrong partition name or partition list
2034 drop procedure sp;
2035 # LOCK TABLES, PS, SP
2036 create or replace procedure sp()
2037 alter table t1 convert partition p4 to table tp4;
2038 lock tables t1 write;
2039 prepare stmt from 'call sp';
2040 execute stmt;
2041 unlock tables;
2042 show create table tp4;
2043 Table Create Table
2044 tp4 CREATE TABLE `tp4` (
2045 `x` int(11) DEFAULT NULL
2046 ) ENGINE=X DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2047 select * from tp4;
2048 x
2049 33
2050 show create table t1;
2051 Table Create Table
2052 t1 CREATE TABLE `t1` (
2053 `x` int(11) DEFAULT NULL
2054 ) ENGINE=X DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2055 PARTITION BY RANGE (`x`)
2056 (PARTITION `p1` VALUES LESS THAN (10) ENGINE = X,
2057 PARTITION `p5` VALUES LESS THAN (50) ENGINE = X,
2058 PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = X)
2059 select * from t1 order by x;
2060 x
2061 3
2062 43
2063 53
2064 drop table tp4;
2065 lock tables t1 write;
2066 execute stmt;
2067 ERROR HY000: Wrong partition name or partition list
2068 call sp;
2069 ERROR HY000: Wrong partition name or partition list
2070 drop prepare stmt;
2071 unlock tables;
2072 drop procedure sp;
2073 unlock tables;
2074 drop tables t1, tp2;
2075 # System-versioned tables (SYSTEM_TIME LIMIT)
2076 create or replace table t1 (
2077 x int,
2078 row_start timestamp(6) as row start invisible,
2079 row_end timestamp(6) as row end invisible,
2080 period for system_time(row_start, row_end)
2081 ) with system versioning
2082 partition by system_time limit 1 partitions 4;
2083 insert into t1 values (2), (12), (22);
2084 update t1 set x= x + 1 where x = 2;
2085 update t1 set x= x + 1 where x = 12;
2086 update t1 set x= x + 1 where x = 22;
2087 Warnings:
2088 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p2`) is out of LIMIT, need more HISTORY partitions
2089 select * from t1 partition (p1);
2090 x
2091 12
2092 alter table t1 convert partition pn to table tp1;
2093 ERROR HY000: Wrong partitions for `t1`: must have at least one HISTORY and exactly one last CURRENT
2094 alter table t1 convert partition p1 to table tp1;
2095 show create table tp1;
2096 Table Create Table
2097 tp1 CREATE TABLE `tp1` (
2098 `x` int(11) DEFAULT NULL,
2099 `row_start` timestamp(6) GENERATED ALWAYS AS ROW START INVISIBLE,
2100 `row_end` timestamp(6) GENERATED ALWAYS AS ROW END INVISIBLE,
2101 PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
2102 ) ENGINE=X DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2103 select * from tp1;
2104 x
2105 select * from tp1 for system_time all;
2106 x
2107 12
2108 show create table t1;
2109 Table Create Table
2110 t1 CREATE TABLE `t1` (
2111 `x` int(11) DEFAULT NULL,
2112 `row_start` timestamp(6) GENERATED ALWAYS AS ROW START INVISIBLE,
2113 `row_end` timestamp(6) GENERATED ALWAYS AS ROW END INVISIBLE,
2114 PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
2115 ) ENGINE=X DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2116 PARTITION BY SYSTEM_TIME LIMIT 1
2117 (PARTITION `p0` HISTORY ENGINE = X,
2118 PARTITION `p2` HISTORY ENGINE = X,
2119 PARTITION `pn` CURRENT ENGINE = X)
2120 select * from t1 order by x;
2121 x
2122 3
2123 13
2124 23
2125 select * from t1 for system_time all order by x;
2126 x
2127 2
2128 3
2129 13
2130 22
2131 23
2132 drop tables t1, tp1;
2133 # System-versioned tables (SYSTEM_TIME INTERVAL)
2134 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2135 create or replace table t1 (
2136 x int,
2137 row_start timestamp(6) as row start invisible,
2138 row_end timestamp(6) as row end invisible,
2139 period for system_time(row_start, row_end)
2140 ) with system versioning
2141 partition by system_time interval 1 hour partitions 4;
2142 insert into t1 values (2), (12), (22);
2143 set timestamp= unix_timestamp('2000-01-01 00:00:01');
2144 update t1 set x= x + 1 where x = 2;
2145 set timestamp= unix_timestamp('2000-01-01 01:00:00');
2146 update t1 set x= x + 1 where x = 12;
2147 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2148 update t1 set x= x + 1 where x = 22;
2149 select * from t1 partition (p0);
2150 x
2151 2
2152 select * from t1 partition (p1);
2153 x
2154 12
2155 select * from t1 partition (p2);
2156 x
2157 22
2158 alter table t1 convert partition p1 to table tp1;
2159 ERROR HY000: Can only drop oldest partitions when rotating by INTERVAL
2160 alter table t1 convert partition p0 to table tp0;
2161 alter table t1 convert partition p1 to table tp1;
2162 alter table t1 convert partition p2 to table tp2;
2163 ERROR HY000: Wrong partitions for `t1`: must have at least one HISTORY and exactly one last CURRENT
2164 show create table tp0;
2165 Table Create Table
2166 tp0 CREATE TABLE `tp0` (
2167 `x` int(11) DEFAULT NULL,
2168 `row_start` timestamp(6) GENERATED ALWAYS AS ROW START INVISIBLE,
2169 `row_end` timestamp(6) GENERATED ALWAYS AS ROW END INVISIBLE,
2170 PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
2171 ) ENGINE=X DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2172 show create table tp1;
2173 Table Create Table
2174 tp1 CREATE TABLE `tp1` (
2175 `x` int(11) DEFAULT NULL,
2176 `row_start` timestamp(6) GENERATED ALWAYS AS ROW START INVISIBLE,
2177 `row_end` timestamp(6) GENERATED ALWAYS AS ROW END INVISIBLE,
2178 PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
2179 ) ENGINE=X DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2180 select * from tp0;
2181 x
2182 select * from tp1;
2183 x
2184 select * from tp0 for system_time all;
2185 x
2186 2
2187 select * from tp1 for system_time all;
2188 x
2189 12
2190 show create table t1;
2191 Table Create Table
2192 t1 CREATE TABLE `t1` (
2193 `x` int(11) DEFAULT NULL,
2194 `row_start` timestamp(6) GENERATED ALWAYS AS ROW START INVISIBLE,
2195 `row_end` timestamp(6) GENERATED ALWAYS AS ROW END INVISIBLE,
2196 PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
2197 ) ENGINE=X DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2198 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 02:00:00'
2199 (PARTITION `p2` HISTORY ENGINE = X,
2200 PARTITION `pn` CURRENT ENGINE = X)
2201 select * from t1;
2202 x
2203 3
2204 13
2205 23
2206 select * from t1 for system_time all order by x;
2207 x
2208 3
2209 13
2210 22
2211 23
2212 drop tables t1, tp0, tp1;
2213 # System-versioned tables (implicit)
2214 create or replace table t1(x int) with system versioning
2215 partition by system_time limit 1 partitions 3;
2216 alter table t1 convert partition p1 to table tp1;
2217 show create table tp1;
2218 Table Create Table
2219 tp1 CREATE TABLE `tp1` (
2220 `x` int(11) DEFAULT NULL
2221 ) ENGINE=X DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2222 show create table t1;
2223 Table Create Table
2224 t1 CREATE TABLE `t1` (
2225 `x` int(11) DEFAULT NULL
2226 ) ENGINE=X DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2227 PARTITION BY SYSTEM_TIME LIMIT 1
2228 (PARTITION `p0` HISTORY ENGINE = X,
2229 PARTITION `pn` CURRENT ENGINE = X)
2230 drop tables t1, tp1;
2231 # Complex table
2232 create or replace table t1 (
2233 x int primary key auto_increment,
2234 t timestamp(6) default '2001-11-11 11:11:11',
2235 b blob(4096) compressed null,
2236 c varchar(1033) character set utf8 not null,
2237 u int,
2238 unique key (x, u),
2239 m enum('a', 'b', 'c') not null default 'a' comment 'absolute',
2240 i1 tinyint, i2 smallint, i3 bigint,
2241 index three(i1, i2, i3),
2242 v1 timestamp(6) generated always as (t + interval 1 day),
2243 v2 timestamp(6) generated always as (t + interval 1 month) stored,
2244 s timestamp(6) as row start,
2245 e timestamp(6) as row end,
2246 period for system_time (s, e),
2247 ps date, pe date,
2248 period for app_time (ps, pe),
2249 constraint check_constr check (u > -1))
2250 with system versioning default charset=ucs2
2251 partition by range(x) (
2252 partition p0 values less than (10),
2253 partition p1 values less than (20),
2254 partition pn values less than maxvalue);
2255 alter table t1 convert partition p1 to table tp1;
2256 show create table tp1;
2257 Table Create Table
2258 tp1 CREATE TABLE `tp1` (
2259 `x` int(11) NOT NULL AUTO_INCREMENT,
2260 `t` timestamp(6) NULL DEFAULT '2001-11-11 11:11:11.000000',
2261 `b` blob /*M!100301 COMPRESSED*/ DEFAULT NULL,
2262 `c` varchar(1033) CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci NOT NULL,
2263 `u` int(11) DEFAULT NULL,
2264 `m` enum('a','b','c') NOT NULL DEFAULT 'a' COMMENT 'absolute',
2265 `i1` tinyint(4) DEFAULT NULL,
2266 `i2` smallint(6) DEFAULT NULL,
2267 `i3` bigint(20) DEFAULT NULL,
2268 `v1` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 day) VIRTUAL,
2269 `v2` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 month) STORED,
2270 `s` timestamp(6) GENERATED ALWAYS AS ROW START,
2271 `e` timestamp(6) GENERATED ALWAYS AS ROW END,
2272 `ps` date NOT NULL,
2273 `pe` date NOT NULL,
2274 PERIOD FOR `app_time` (`ps`, `pe`),
2275 PRIMARY KEY (`x`,`e`),
2276 UNIQUE KEY `x` (`x`,`u`,`e`),
2277 KEY `three` (`i1`,`i2`,`i3`),
2278 PERIOD FOR SYSTEM_TIME (`s`, `e`),
2279 CONSTRAINT `check_constr` CHECK (`u` > -1)
2280 ) ENGINE=X DEFAULT CHARSET=ucs2 COLLATE=ucs2_uca1400_ai_ci WITH SYSTEM VERSIONING
2281 show create table t1;
2282 Table Create Table
2283 t1 CREATE TABLE `t1` (
2284 `x` int(11) NOT NULL AUTO_INCREMENT,
2285 `t` timestamp(6) NULL DEFAULT '2001-11-11 11:11:11.000000',
2286 `b` blob /*M!100301 COMPRESSED*/ DEFAULT NULL,
2287 `c` varchar(1033) CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci NOT NULL,
2288 `u` int(11) DEFAULT NULL,
2289 `m` enum('a','b','c') NOT NULL DEFAULT 'a' COMMENT 'absolute',
2290 `i1` tinyint(4) DEFAULT NULL,
2291 `i2` smallint(6) DEFAULT NULL,
2292 `i3` bigint(20) DEFAULT NULL,
2293 `v1` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 day) VIRTUAL,
2294 `v2` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 month) STORED,
2295 `s` timestamp(6) GENERATED ALWAYS AS ROW START,
2296 `e` timestamp(6) GENERATED ALWAYS AS ROW END,
2297 `ps` date NOT NULL,
2298 `pe` date NOT NULL,
2299 PERIOD FOR `app_time` (`ps`, `pe`),
2300 PRIMARY KEY (`x`,`e`),
2301 UNIQUE KEY `x` (`x`,`u`,`e`),
2302 KEY `three` (`i1`,`i2`,`i3`),
2303 PERIOD FOR SYSTEM_TIME (`s`, `e`),
2304 CONSTRAINT `check_constr` CHECK (`u` > -1)
2305 ) ENGINE=X DEFAULT CHARSET=ucs2 COLLATE=ucs2_uca1400_ai_ci WITH SYSTEM VERSIONING
2306 PARTITION BY RANGE (`x`)
2307 (PARTITION `p0` VALUES LESS THAN (10) ENGINE = X,
2308 PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = X)
2309 drop tables t1, tp1;
2310 #
2311 # MDEV-29841 Partition by system_time can be converted into table but not back
2312 #
2313 create or replace table t (a int) with system versioning
2314 partition by system_time limit 10 partitions 3;
2315 alter table t convert partition p0 to table tp;
2316 alter table t convert table tp to partition p0;
2317 ERROR HY000: CONVERT TABLE TO PARTITION can only be used on RANGE/LIST partitions
2318 drop tables t, tp;
2319 #
2320 # End of 10.7 tests
2321 #
2322 #
2323 # MDEV-17554 Auto-create new partition for system versioned tables
2324 # with history partitioned by INTERVAL/LIMIT
2325 #
2326 create or replace table t1 (x int) with system versioning
2327 partition by system_time limit 1 auto;
2328 show create table t1;
2329 Table Create Table
2330 t1 CREATE TABLE `t1` (
2331 `x` int(11) DEFAULT NULL
2332 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2333 PARTITION BY SYSTEM_TIME LIMIT 1 AUTO
2334 PARTITIONS 2
2335 # Turn off AUTO
2336 alter table t1 partition by system_time limit 1;
2337 show create table t1;
2338 Table Create Table
2339 t1 CREATE TABLE `t1` (
2340 `x` int(11) DEFAULT NULL
2341 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2342 PARTITION BY SYSTEM_TIME LIMIT 1
2343 PARTITIONS 2
2344 # Get AUTO back
2345 alter table t1 partition by system_time limit 1 auto;
2346 show create table t1;
2347 Table Create Table
2348 t1 CREATE TABLE `t1` (
2349 `x` int(11) DEFAULT NULL
2350 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2351 PARTITION BY SYSTEM_TIME LIMIT 1 AUTO
2352 PARTITIONS 2
2353 insert into t1 values (1);
2354 create or replace table t2 (y int);
2355 insert into t2 values (2);
2356 insert into t1 select * from t2;
2357 insert into t2 select * from t1;
2358 show create table t1;
2359 Table Create Table
2360 t1 CREATE TABLE `t1` (
2361 `x` int(11) DEFAULT NULL
2362 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2363 PARTITION BY SYSTEM_TIME LIMIT 1 AUTO
2364 PARTITIONS 2
2365 # Too many partitions error
2366 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2367 create or replace table t1 (x int) with system versioning
2368 partition by system_time interval 1 hour auto;
2369 set timestamp= unix_timestamp('2001-01-01 00:01:00');
2370 update t1 set x= x + 1;
2371 ERROR HY000: Versioned table `test`.`t1`: adding HISTORY partition(s) failed
2372 show warnings;
2373 Level Code Message
2374 Warning 1499 Too many partitions (including subpartitions) were defined
2375 Error 4189 Versioned table `test`.`t1`: adding HISTORY partition(s) failed
2376 # Auto-create failed error
2377 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2378 create or replace table t1 (x int) with system versioning engine innodb
2379 partition by system_time interval 1 hour auto;
2380 insert into t1 values (1);
2381 call mtr.add_suppression("rror number .*(File exists|file operation)");
2382 call mtr.add_suppression("InnoDB: The file '.*test/t1#P#p1\\.ibd' already exists");
2383 set timestamp= unix_timestamp('2000-01-01 01:00:00');
2384 update t1 set x= x + 2;
2385 ERROR HY000: Got error 184 "Tablespace already exists" from storage engine InnoDB
2386 show warnings;
2387 Level Code Message
2388 Error 1030 Got error 184 "Tablespace already exists" from storage engine InnoDB
2389 Warning 4189 Versioned table `test`.`t1`: adding HISTORY partition(s) failed
2390 # Partition overflow error and manual fix
2391 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2392 create or replace table t1 (x int) with system versioning
2393 partition by system_time interval 1 hour;
2394 insert into t1 values (440);
2395 set timestamp= unix_timestamp('2000-01-01 00:10:00');
2396 update t1 set x= x + 1;
2397 # Check how pruning boundaries work
2398 explain partitions select * from t1 for system_time as of '2000-01-01 00:59:58';
2399 id select_type table partitions type possible_keys key key_len ref rows Extra
2400 1 SIMPLE t1 p0,pn # NULL NULL NULL NULL # #
2401 explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
2402 id select_type table partitions type possible_keys key key_len ref rows Extra
2403 1 SIMPLE t1 p0,pn # NULL NULL NULL NULL # #
2404 explain partitions select * from t1 for system_time as of '2000-01-01 01:00:00';
2405 id select_type table partitions type possible_keys key key_len ref rows Extra
2406 1 SIMPLE t1 p0,pn # NULL NULL NULL NULL # #
2407 select * from t1 for system_time as of '2000-01-01 00:09:59';
2408 x
2409 440
2410 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2411 update t1 set x= x + 1;
2412 Warnings:
2413 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p0`) is out of INTERVAL, need more HISTORY partitions
2414 select * from t1 for system_time as of '2000-01-01 01:00:00';
2415 x
2416 441
2417 select * from t1 partition (p0) order by x;
2418 x
2419 440
2420 441
2421 # Here is how manual fix works: just add new partitions there
2422 alter table t1 add partition partitions 3;
2423 select * from t1 for system_time as of '2000-01-01 01:00:00';
2424 x
2425 441
2426 select * from t1 partition (p0) order by x;
2427 x
2428 440
2429 # Check pruning after ALTER
2430 explain partitions select * from t1 for system_time as of '2000-01-01 00:59:58';
2431 id select_type table partitions type possible_keys key key_len ref rows Extra
2432 1 SIMPLE t1 p0,p1,p2,p3,pn # NULL NULL NULL NULL # #
2433 explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
2434 id select_type table partitions type possible_keys key key_len ref rows Extra
2435 1 SIMPLE t1 p1,p2,p3,pn # NULL NULL NULL NULL # #
2436 explain partitions select * from t1 for system_time as of '2000-01-01 01:00:00';
2437 id select_type table partitions type possible_keys key key_len ref rows Extra
2438 1 SIMPLE t1 p1,p2,p3,pn # NULL NULL NULL NULL # #
2439 drop table t1;
2440 create or replace table t1 (x int) with system versioning
2441 partition by system_time interval 3600 second
2442 starts '2000-01-01 00:00:00' auto partitions 3;
2443 affected rows: 0
2444 insert into t1 values (1);
2445 affected rows: 1
2446 show create table t1;
2447 Table Create Table
2448 t1 CREATE TABLE `t1` (
2449 `x` int(11) DEFAULT NULL
2450 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2451 PARTITION BY SYSTEM_TIME INTERVAL 3600 SECOND STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2452 PARTITIONS 3
2453 affected rows: 1
2454 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2455 affected rows: 0
2456 update t1 set x= x + 1;
2457 affected rows: 1
2458 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2459 show create table t1;
2460 Table Create Table
2461 t1 CREATE TABLE `t1` (
2462 `x` int(11) DEFAULT NULL
2463 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2464 PARTITION BY SYSTEM_TIME INTERVAL 3600 SECOND STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2465 PARTITIONS 4
2466 affected rows: 1
2467 set timestamp= unix_timestamp('2000-01-01 03:00:00');
2468 affected rows: 0
2469 update t1 set x= x + 2;
2470 affected rows: 1
2471 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2472 show create table t1;
2473 Table Create Table
2474 t1 CREATE TABLE `t1` (
2475 `x` int(11) DEFAULT NULL
2476 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2477 PARTITION BY SYSTEM_TIME INTERVAL 3600 SECOND STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2478 PARTITIONS 5
2479 affected rows: 1
2480 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2481 affected rows: 0
2482 create or replace table t1 (x int) with system versioning
2483 partition by system_time interval 1 hour auto (
2484 partition p1 history,
2485 partition p3 history,
2486 partition pn current);
2487 affected rows: 0
2488 insert into t1 values (1);
2489 affected rows: 1
2490 show create table t1;
2491 Table Create Table
2492 t1 CREATE TABLE `t1` (
2493 `x` int(11) DEFAULT NULL
2494 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2495 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2496 (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE,
2497 PARTITION `p3` HISTORY ENGINE = DEFAULT_ENGINE,
2498 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
2499 affected rows: 1
2500 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2501 affected rows: 0
2502 update t1 set x= x + 3;
2503 affected rows: 1
2504 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2505 show create table t1;
2506 Table Create Table
2507 t1 CREATE TABLE `t1` (
2508 `x` int(11) DEFAULT NULL
2509 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2510 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2511 (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE,
2512 PARTITION `p3` HISTORY ENGINE = DEFAULT_ENGINE,
2513 PARTITION `p2` HISTORY ENGINE = DEFAULT_ENGINE,
2514 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
2515 affected rows: 1
2516 set timestamp= unix_timestamp('2000-01-01 03:00:00');
2517 affected rows: 0
2518 update t1 set x= x + 4;
2519 affected rows: 1
2520 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2521 show create table t1;
2522 Table Create Table
2523 t1 CREATE TABLE `t1` (
2524 `x` int(11) DEFAULT NULL
2525 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2526 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2527 (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE,
2528 PARTITION `p3` HISTORY ENGINE = DEFAULT_ENGINE,
2529 PARTITION `p2` HISTORY ENGINE = DEFAULT_ENGINE,
2530 PARTITION `p4` HISTORY ENGINE = DEFAULT_ENGINE,
2531 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
2532 affected rows: 1
2533 set timestamp= unix_timestamp('2000-01-01 04:00:00');
2534 affected rows: 0
2535 lock tables t1 write;
2536 affected rows: 0
2537 update t1 set x= x + 5;
2538 affected rows: 1
2539 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2540 show create table t1;
2541 Table Create Table
2542 t1 CREATE TABLE `t1` (
2543 `x` int(11) DEFAULT NULL
2544 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2545 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2546 (PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE,
2547 PARTITION `p3` HISTORY ENGINE = DEFAULT_ENGINE,
2548 PARTITION `p2` HISTORY ENGINE = DEFAULT_ENGINE,
2549 PARTITION `p4` HISTORY ENGINE = DEFAULT_ENGINE,
2550 PARTITION `p5` HISTORY ENGINE = DEFAULT_ENGINE,
2551 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
2552 affected rows: 1
2553 unlock tables;
2554 affected rows: 0
2555 set timestamp= default;
2556 affected rows: 0
2557 # Couple of more LOCK TABLES cases
2558 create or replace table t1 (x int) with system versioning
2559 partition by system_time limit 1 auto;
2560 affected rows: 0
2561 lock tables t1 write;
2562 affected rows: 0
2563 insert into t1 values (1);
2564 affected rows: 1
2565 update t1 set x= x + 1;
2566 affected rows: 1
2567 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2568 update t1 set x= x + 2;
2569 affected rows: 1
2570 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2571 update t1 set x= x + 3;
2572 affected rows: 1
2573 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2574 show create table t1;
2575 Table Create Table
2576 t1 CREATE TABLE `t1` (
2577 `x` int(11) DEFAULT NULL
2578 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2579 PARTITION BY SYSTEM_TIME LIMIT 1 AUTO
2580 PARTITIONS 4
2581 affected rows: 1
2582 unlock tables;
2583 affected rows: 0
2584 show create table t1;
2585 Table Create Table
2586 t1 CREATE TABLE `t1` (
2587 `x` int(11) DEFAULT NULL
2588 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2589 PARTITION BY SYSTEM_TIME LIMIT 1 AUTO
2590 PARTITIONS 4
2591 affected rows: 1
2592 # Overflow prevention under LOCK TABLES
2593 create or replace table t1 (x int)
2594 with system versioning partition by system_time
2595 limit 10 auto;
2596 affected rows: 0
2597 insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9);
2598 affected rows: 9
2599 info: Records: 9 Duplicates: 0 Warnings: 0
2600 update t1 set x= x + 10;
2601 affected rows: 9
2602 info: Rows matched: 9 Changed: 9 Inserted: 9 Warnings: 0
2603 lock tables t1 write;
2604 affected rows: 0
2605 update t1 set x= 1 where x = 11;
2606 affected rows: 1
2607 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2608 update t1 set x= 2 where x = 12;
2609 affected rows: 1
2610 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2611 update t1 set x= 3 where x = 13;
2612 affected rows: 1
2613 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2614 unlock tables;
2615 affected rows: 0
2616 select count(x) from t1 partition (p0);
2617 count(x)
2618 10
2619 affected rows: 1
2620 show create table t1;
2621 Table Create Table
2622 t1 CREATE TABLE `t1` (
2623 `x` int(11) DEFAULT NULL
2624 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2625 PARTITION BY SYSTEM_TIME LIMIT 10 AUTO
2626 PARTITIONS 3
2627 affected rows: 1
2628 drop tables t1;
2629 affected rows: 0
2630 # Test VIEW, LOCK TABLES
2631 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2632 affected rows: 0
2633 create or replace table t1 (x int) with system versioning
2634 partition by system_time interval 1 hour auto;
2635 affected rows: 0
2636 create or replace view v1 as select * from t1;
2637 affected rows: 0
2638 insert into t1 values (1);
2639 affected rows: 1
2640 set timestamp= unix_timestamp('2000-01-01 01:00:00');
2641 affected rows: 0
2642 update v1 set x= x + 2;
2643 affected rows: 1
2644 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2645 show create table t1;
2646 Table Create Table
2647 t1 CREATE TABLE `t1` (
2648 `x` int(11) DEFAULT NULL
2649 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2650 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2651 PARTITIONS 3
2652 affected rows: 1
2653 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2654 affected rows: 0
2655 lock tables v1 write;
2656 affected rows: 0
2657 update v1 set x= x + 3;
2658 affected rows: 1
2659 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2660 show create table t1;
2661 Table Create Table
2662 t1 CREATE TABLE `t1` (
2663 `x` int(11) DEFAULT NULL
2664 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2665 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2666 PARTITIONS 4
2667 affected rows: 1
2668 unlock tables;
2669 affected rows: 0
2670 drop view v1;
2671 affected rows: 0
2672 drop tables t1;
2673 affected rows: 0
2674 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2675 affected rows: 0
2676 create or replace table t1 (x int) with system versioning
2677 partition by system_time interval 1 hour auto partitions 3;
2678 affected rows: 0
2679 create or replace table t2 (y int) with system versioning
2680 partition by system_time interval 1 hour auto;
2681 affected rows: 0
2682 insert into t1 values (1);
2683 affected rows: 1
2684 insert into t2 values (2);
2685 affected rows: 1
2686 set timestamp= unix_timestamp('2000-01-01 01:00:00');
2687 affected rows: 0
2688 update t1, t2 set x= x + 1, y= y + 1;
2689 affected rows: 2
2690 info: Rows matched: 2 Changed: 2 Warnings: 0
2691 show create table t1;
2692 Table Create Table
2693 t1 CREATE TABLE `t1` (
2694 `x` int(11) DEFAULT NULL
2695 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2696 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2697 PARTITIONS 3
2698 affected rows: 1
2699 show create table t2;
2700 Table Create Table
2701 t2 CREATE TABLE `t2` (
2702 `y` int(11) DEFAULT NULL
2703 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2704 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2705 PARTITIONS 3
2706 affected rows: 1
2707 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2708 affected rows: 0
2709 update t1, t2 set x= x + 1, y= y + 1;
2710 affected rows: 2
2711 info: Rows matched: 2 Changed: 2 Warnings: 0
2712 show create table t1;
2713 Table Create Table
2714 t1 CREATE TABLE `t1` (
2715 `x` int(11) DEFAULT NULL
2716 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2717 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2718 PARTITIONS 4
2719 affected rows: 1
2720 show create table t2;
2721 Table Create Table
2722 t2 CREATE TABLE `t2` (
2723 `y` int(11) DEFAULT NULL
2724 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2725 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2726 PARTITIONS 4
2727 affected rows: 1
2728 set timestamp= unix_timestamp('2000-01-01 03:00:00');
2729 affected rows: 0
2730 update t1, t2 set t1.x= 0 where t1.x< t2.y;
2731 affected rows: 1
2732 info: Rows matched: 1 Changed: 1 Warnings: 0
2733 show create table t1;
2734 Table Create Table
2735 t1 CREATE TABLE `t1` (
2736 `x` int(11) DEFAULT NULL
2737 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2738 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2739 PARTITIONS 5
2740 affected rows: 1
2741 show create table t2;
2742 Table Create Table
2743 t2 CREATE TABLE `t2` (
2744 `y` int(11) DEFAULT NULL
2745 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2746 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2747 PARTITIONS ok
2748 affected rows: 1
2749 drop tables t1, t2;
2750 affected rows: 0
2751 # PS, SP, LOCK TABLES
2752 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2753 affected rows: 0
2754 create or replace table t1 (x int) with system versioning
2755 partition by system_time interval 1 hour auto;
2756 affected rows: 0
2757 insert into t1 values (1);
2758 affected rows: 1
2759 set timestamp= unix_timestamp('2000-01-01 01:00:00');
2760 affected rows: 0
2761 execute immediate 'update t1 set x= x + 5';
2762 affected rows: 1
2763 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2764 show create table t1;
2765 Table Create Table
2766 t1 CREATE TABLE `t1` (
2767 `x` int(11) DEFAULT NULL
2768 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2769 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2770 PARTITIONS 3
2771 affected rows: 1
2772 prepare s from 'update t1 set x= x + 6';
2773 affected rows: 0
2774 info: Statement prepared
2775 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2776 affected rows: 0
2777 execute s;
2778 affected rows: 1
2779 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2780 execute s;
2781 affected rows: 1
2782 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2783 show create table t1;
2784 Table Create Table
2785 t1 CREATE TABLE `t1` (
2786 `x` int(11) DEFAULT NULL
2787 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2788 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2789 PARTITIONS 4
2790 affected rows: 1
2791 set timestamp= unix_timestamp('2000-01-01 03:00:00');
2792 affected rows: 0
2793 lock tables t1 write;
2794 affected rows: 0
2795 execute s;
2796 affected rows: 1
2797 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2798 execute s;
2799 affected rows: 1
2800 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2801 show create table t1;
2802 Table Create Table
2803 t1 CREATE TABLE `t1` (
2804 `x` int(11) DEFAULT NULL
2805 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2806 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2807 PARTITIONS 5
2808 affected rows: 1
2809 unlock tables;
2810 affected rows: 0
2811 drop prepare s;
2812 affected rows: 0
2813 create procedure sp() update t1 set x= x + 7;
2814 affected rows: 0
2815 set timestamp= unix_timestamp('2000-01-01 04:00:00');
2816 affected rows: 0
2817 call sp;
2818 affected rows: 1
2819 call sp;
2820 affected rows: 1
2821 show create table t1;
2822 Table Create Table
2823 t1 CREATE TABLE `t1` (
2824 `x` int(11) DEFAULT NULL
2825 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2826 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2827 PARTITIONS 6
2828 affected rows: 1
2829 set timestamp= unix_timestamp('2000-01-01 05:00:00');
2830 affected rows: 0
2831 lock tables t1 write;
2832 affected rows: 0
2833 call sp;
2834 affected rows: 1
2835 call sp;
2836 affected rows: 1
2837 show create table t1;
2838 Table Create Table
2839 t1 CREATE TABLE `t1` (
2840 `x` int(11) DEFAULT NULL
2841 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
2842 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2843 PARTITIONS 7
2844 affected rows: 1
2845 unlock tables;
2846 affected rows: 0
2847 drop procedure sp;
2848 affected rows: 0
2849 set timestamp= unix_timestamp('2001-01-01 00:00:00');
2850 affected rows: 0
2851 create or replace table t1 (i int) with system versioning
2852 partition by system_time interval 1 day starts '2001-01-01 00:00:00';
2853 affected rows: 0
2854 insert into t1 values (0);
2855 affected rows: 1
2856 set timestamp= unix_timestamp('2001-01-01 00:00:01');
2857 affected rows: 0
2858 prepare s from 'update t1 set i= i + 1';
2859 affected rows: 0
2860 info: Statement prepared
2861 execute s;
2862 affected rows: 1
2863 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2864 set timestamp= unix_timestamp('2001-01-02 00:00:01');
2865 affected rows: 0
2866 execute s;
2867 affected rows: 1
2868 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 1
2869 Warnings:
2870 Warning 4114 Versioned table `test`.`t1`: last HISTORY partition (`p0`) is out of INTERVAL, need more HISTORY partitions
2871 drop prepare s;
2872 affected rows: 0
2873 # Complex table
2874 set timestamp= unix_timestamp('2000-01-01 00:00:00');
2875 affected rows: 0
2876 create or replace table t1 (
2877 x int primary key auto_increment,
2878 t timestamp(6) default '2001-11-11 11:11:11',
2879 b blob(4096) compressed null,
2880 c varchar(1033) character set utf8 not null,
2881 u int unique,
2882 m enum('a', 'b', 'c') not null default 'a' comment 'absolute',
2883 i1 tinyint, i2 smallint, i3 bigint,
2884 index three(i1, i2, i3),
2885 v1 timestamp(6) generated always as (t + interval 1 day),
2886 v2 timestamp(6) generated always as (t + interval 1 month) stored,
2887 s timestamp(6) as row start,
2888 e timestamp(6) as row end,
2889 period for system_time (s, e),
2890 ps date, pe date,
2891 period for app_time (ps, pe),
2892 constraint check_constr check (u > -1))
2893 with system versioning default charset=ucs2
2894 partition by system_time interval 1 hour auto (
2895 partition p2 history,
2896 partition pn current);
2897 affected rows: 0
2898 show create table t1;
2899 Table Create Table
2900 t1 CREATE TABLE `t1` (
2901 `x` int(11) NOT NULL AUTO_INCREMENT,
2902 `t` timestamp(6) NULL DEFAULT '2001-11-11 11:11:11.000000',
2903 `b` blob /*M!100301 COMPRESSED*/ DEFAULT NULL,
2904 `c` varchar(1033) CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci NOT NULL,
2905 `u` int(11) DEFAULT NULL,
2906 `m` enum('a','b','c') NOT NULL DEFAULT 'a' COMMENT 'absolute',
2907 `i1` tinyint(4) DEFAULT NULL,
2908 `i2` smallint(6) DEFAULT NULL,
2909 `i3` bigint(20) DEFAULT NULL,
2910 `v1` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 day) VIRTUAL,
2911 `v2` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 month) STORED,
2912 `s` timestamp(6) GENERATED ALWAYS AS ROW START,
2913 `e` timestamp(6) GENERATED ALWAYS AS ROW END,
2914 `ps` date NOT NULL,
2915 `pe` date NOT NULL,
2916 PERIOD FOR `app_time` (`ps`, `pe`),
2917 PRIMARY KEY (`x`,`e`),
2918 UNIQUE KEY `u` (`u`,`e`),
2919 KEY `three` (`i1`,`i2`,`i3`),
2920 PERIOD FOR SYSTEM_TIME (`s`, `e`),
2921 CONSTRAINT `check_constr` CHECK (`u` > -1)
2922 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=ucs2 COLLATE=ucs2_uca1400_ai_ci WITH SYSTEM VERSIONING
2923 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2924 (PARTITION `p2` HISTORY ENGINE = DEFAULT_ENGINE,
2925 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
2926 affected rows: 1
2927 insert into t1 (x, c, u, i1, i2, i3, ps, pe)
2928 values (1, 'cc', 0, 1, 2, 3, '1999-01-01', '2000-01-01');
2929 affected rows: 1
2930 set timestamp= unix_timestamp('2000-01-01 01:00:00');
2931 affected rows: 0
2932 update t1 set x= x + 8;
2933 affected rows: 1
2934 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2935 show create table t1;
2936 Table Create Table
2937 t1 CREATE TABLE `t1` (
2938 `x` int(11) NOT NULL AUTO_INCREMENT,
2939 `t` timestamp(6) NULL DEFAULT '2001-11-11 11:11:11.000000',
2940 `b` blob /*M!100301 COMPRESSED*/ DEFAULT NULL,
2941 `c` varchar(1033) CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci NOT NULL,
2942 `u` int(11) DEFAULT NULL,
2943 `m` enum('a','b','c') NOT NULL DEFAULT 'a' COMMENT 'absolute',
2944 `i1` tinyint(4) DEFAULT NULL,
2945 `i2` smallint(6) DEFAULT NULL,
2946 `i3` bigint(20) DEFAULT NULL,
2947 `v1` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 day) VIRTUAL,
2948 `v2` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 month) STORED,
2949 `s` timestamp(6) GENERATED ALWAYS AS ROW START,
2950 `e` timestamp(6) GENERATED ALWAYS AS ROW END,
2951 `ps` date NOT NULL,
2952 `pe` date NOT NULL,
2953 PERIOD FOR `app_time` (`ps`, `pe`),
2954 PRIMARY KEY (`x`,`e`),
2955 UNIQUE KEY `u` (`u`,`e`),
2956 KEY `three` (`i1`,`i2`,`i3`),
2957 PERIOD FOR SYSTEM_TIME (`s`, `e`),
2958 CONSTRAINT `check_constr` CHECK (`u` > -1)
2959 ) ENGINE=DEFAULT_ENGINE AUTO_INCREMENT=10 DEFAULT CHARSET=ucs2 COLLATE=ucs2_uca1400_ai_ci WITH SYSTEM VERSIONING
2960 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2961 (PARTITION `p2` HISTORY ENGINE = DEFAULT_ENGINE,
2962 PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE,
2963 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
2964 affected rows: 1
2965 set timestamp= unix_timestamp('2000-01-01 02:00:00');
2966 affected rows: 0
2967 update t1 set x= x - 8;
2968 affected rows: 1
2969 info: Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0
2970 show create table t1;
2971 Table Create Table
2972 t1 CREATE TABLE `t1` (
2973 `x` int(11) NOT NULL AUTO_INCREMENT,
2974 `t` timestamp(6) NULL DEFAULT '2001-11-11 11:11:11.000000',
2975 `b` blob /*M!100301 COMPRESSED*/ DEFAULT NULL,
2976 `c` varchar(1033) CHARACTER SET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci NOT NULL,
2977 `u` int(11) DEFAULT NULL,
2978 `m` enum('a','b','c') NOT NULL DEFAULT 'a' COMMENT 'absolute',
2979 `i1` tinyint(4) DEFAULT NULL,
2980 `i2` smallint(6) DEFAULT NULL,
2981 `i3` bigint(20) DEFAULT NULL,
2982 `v1` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 day) VIRTUAL,
2983 `v2` timestamp(6) GENERATED ALWAYS AS (`t` + interval 1 month) STORED,
2984 `s` timestamp(6) GENERATED ALWAYS AS ROW START,
2985 `e` timestamp(6) GENERATED ALWAYS AS ROW END,
2986 `ps` date NOT NULL,
2987 `pe` date NOT NULL,
2988 PERIOD FOR `app_time` (`ps`, `pe`),
2989 PRIMARY KEY (`x`,`e`),
2990 UNIQUE KEY `u` (`u`,`e`),
2991 KEY `three` (`i1`,`i2`,`i3`),
2992 PERIOD FOR SYSTEM_TIME (`s`, `e`),
2993 CONSTRAINT `check_constr` CHECK (`u` > -1)
2994 ) ENGINE=DEFAULT_ENGINE AUTO_INCREMENT=10 DEFAULT CHARSET=ucs2 COLLATE=ucs2_uca1400_ai_ci WITH SYSTEM VERSIONING
2995 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
2996 (PARTITION `p2` HISTORY ENGINE = DEFAULT_ENGINE,
2997 PARTITION `p1` HISTORY ENGINE = DEFAULT_ENGINE,
2998 PARTITION `p3` HISTORY ENGINE = DEFAULT_ENGINE,
2999 PARTITION `pn` CURRENT ENGINE = DEFAULT_ENGINE)
3000 affected rows: 1
3001 # INSERT .. ON DUPLICATE KEY UPDATE (ODKU)
3002 set timestamp= unix_timestamp('2000-01-01 00:00:00');
3003 create or replace table t1 (x int primary key) with system versioning
3004 partition by system_time interval 1 hour auto;
3005 insert into t1 values (1);
3006 set timestamp= unix_timestamp('2000-01-01 01:00:00');
3007 insert into t1 values (1) on duplicate key update x= x + 1;
3008 show create table t1;
3009 Table Create Table
3010 t1 CREATE TABLE `t1` (
3011 `x` int(11) NOT NULL,
3012 PRIMARY KEY (`x`)
3013 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3014 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3015 PARTITIONS 3
3016 # LOAD DATA .. REPLACE
3017 set timestamp= unix_timestamp('2000-01-01 00:00:00');
3018 create or replace table t1 (x int primary key) with system versioning
3019 partition by system_time interval 1 hour auto;
3020 insert t1 values (1), (2), (3);
3021 select x into outfile 'MDEV-17554.data' from t1;
3022 set timestamp= unix_timestamp('2000-01-01 01:00:00');
3023 load data infile 'MDEV-17554.data' replace into table t1 (x);
3024 show create table t1;
3025 Table Create Table
3026 t1 CREATE TABLE `t1` (
3027 `x` int(11) NOT NULL,
3028 PRIMARY KEY (`x`)
3029 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3030 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3031 PARTITIONS 3
3032 # Concurrent DML
3033 set timestamp= unix_timestamp('2000-01-01 00:00:00');
3034 create or replace table t1 (x int) with system versioning
3035 partition by system_time interval 1 hour auto partitions 3;
3036 insert into t1 values (1);
3037 show create table t1;
3038 Table Create Table
3039 t1 CREATE TABLE `t1` (
3040 `x` int(11) DEFAULT NULL
3041 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3042 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3043 PARTITIONS 3
3044 connect con8, localhost, root;
3045 connect con7, localhost, root;
3046 connect con6, localhost, root;
3047 connect con5, localhost, root;
3048 connect con4, localhost, root;
3049 connect con3, localhost, root;
3050 connect con2, localhost, root;
3051 connect con1, localhost, root;
3052 set timestamp= unix_timestamp('2000-01-01 02:00:00');
3053 update t1 set x= x + 10;
3054 connection con2;
3055 set timestamp= unix_timestamp('2000-01-01 02:00:00');
3056 update t1 set x= x + 20;
3057 connection con3;
3058 set timestamp= unix_timestamp('2000-01-01 02:00:00');
3059 update t1 set x= x + 30;
3060 connection con4;
3061 set timestamp= unix_timestamp('2000-01-01 02:00:00');
3062 update t1 set x= x + 40;
3063 connection con5;
3064 set timestamp= unix_timestamp('2000-01-01 02:00:00');
3065 update t1 set x= x + 50;
3066 connection con6;
3067 set timestamp= unix_timestamp('2000-01-01 02:00:00');
3068 update t1 set x= x + 60;
3069 connection con7;
3070 set timestamp= unix_timestamp('2000-01-01 02:00:00');
3071 update t1 set x= x + 70;
3072 connection con8;
3073 set timestamp= unix_timestamp('2000-01-01 02:00:00');
3074 update t1 set x= x + 80;
3075 connection con1;
3076 disconnect con1;
3077 connection con2;
3078 disconnect con2;
3079 connection con3;
3080 disconnect con3;
3081 connection con4;
3082 disconnect con4;
3083 connection con5;
3084 disconnect con5;
3085 connection con6;
3086 disconnect con6;
3087 connection con7;
3088 disconnect con7;
3089 disconnect con8;
3090 connection default;
3091 show create table t1;
3092 Table Create Table
3093 t1 CREATE TABLE `t1` (
3094 `x` int(11) DEFAULT NULL
3095 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3096 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3097 PARTITIONS 4
3098 drop tables t1;
3099 set timestamp= default;
3100 # Concurrent DML (LIMIT)
3101 create or replace table t1 (x int) with system versioning engine heap
3102 partition by system_time limit 1 auto partitions 3;
3103 insert into t1 values (1);
3104 update t1 set x= x + N; # (running multithreaded for 3 times)
3105 drop tables t1;
3106 # Transaction
3107 set timestamp= unix_timestamp('2000-01-01 00:00:00');
3108 create or replace table t1 (x int) with system versioning engine innodb
3109 partition by system_time interval 1 hour auto;
3110 insert into t1 values (1);
3111 set timestamp= unix_timestamp('2000-01-01 01:00:00');
3112 start transaction;
3113 update t1 set x= 0;
3114 connect con1, localhost, root;
3115 select * from t1;
3116 x
3117 1
3118 show create table t1;
3119 Table Create Table
3120 t1 CREATE TABLE `t1` (
3121 `x` int(11) DEFAULT NULL
3122 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3123 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3124 PARTITIONS 3
3125 connection default;
3126 commit;
3127 show create table t1;
3128 Table Create Table
3129 t1 CREATE TABLE `t1` (
3130 `x` int(11) DEFAULT NULL
3131 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3132 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3133 PARTITIONS 3
3134 set timestamp= unix_timestamp('2000-01-01 02:00:00');
3135 start transaction;
3136 update t1 set x= 1;
3137 connection con1;
3138 select * from t1;
3139 x
3140 0
3141 connection default;
3142 rollback;
3143 show create table t1;
3144 Table Create Table
3145 t1 CREATE TABLE `t1` (
3146 `x` int(11) DEFAULT NULL
3147 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3148 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3149 PARTITIONS 4
3150 disconnect con1;
3151 connection default;
3152 drop table t1;
3153 #
3154 # MDEV-25479 Auto-create: 2nd and further executions of PS or SP fail to create partition
3155 #
3156 create table t (a int) with system versioning
3157 partition by system_time interval 1 hour auto;
3158 insert into t values (1), (2);
3159 prepare stmt from "update t set a= a + 1";
3160 set @@timestamp= @@timestamp + 3601;
3161 execute stmt;
3162 set @@timestamp= @@timestamp + 3601;
3163 execute stmt;
3164 drop prepare stmt;
3165 show create table t;
3166 Table Create Table
3167 t CREATE TABLE `t` (
3168 `a` int(11) DEFAULT NULL
3169 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3170 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 02:00:00' AUTO
3171 PARTITIONS 4
3172 create procedure sp() update t set a= a + 1;
3173 set @@timestamp= @@timestamp + 3601;
3174 call sp();
3175 set @@timestamp= @@timestamp + 3601;
3176 call sp();
3177 drop procedure sp;
3178 show create table t;
3179 Table Create Table
3180 t CREATE TABLE `t` (
3181 `a` int(11) DEFAULT NULL
3182 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3183 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 02:00:00' AUTO
3184 PARTITIONS 6
3185 drop table t;
3186 #
3187 # MDEV-23639 Auto-create does not work under LOCK TABLES or inside triggers
3188 #
3189 set timestamp= unix_timestamp('2000-01-01 00:00:00');
3190 create or replace table t1 (x int) with system versioning
3191 partition by system_time interval 1 hour auto
3192 partitions 3;
3193 create table t2 (x int);
3194 create table t3 (x int);
3195 insert into t3 values (3);
3196 create trigger tr after insert on t2 for each row update t1 set x= x + 11;
3197 create or replace procedure sp() update t1 set x= x + 5;
3198 create or replace procedure sp2() insert into t2 values (5);
3199 prepare ps from 'update t1 set x= x + 6';
3200 prepare ps2 from 'insert into t2 values (6)';
3201 insert into t1 values (1);
3202 set timestamp= unix_timestamp('2000-01-01 02:00:00');
3203 show create table t1;
3204 Table Create Table
3205 t1 CREATE TABLE `t1` (
3206 `x` int(11) DEFAULT NULL
3207 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3208 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3209 PARTITIONS 3
3210 insert into t2 values (2);
3211 show create table t1;
3212 Table Create Table
3213 t1 CREATE TABLE `t1` (
3214 `x` int(11) DEFAULT NULL
3215 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3216 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3217 PARTITIONS 4
3218 set timestamp= unix_timestamp('2000-01-01 03:00:00');
3219 call sp;
3220 call sp;
3221 show create table t1;
3222 Table Create Table
3223 t1 CREATE TABLE `t1` (
3224 `x` int(11) DEFAULT NULL
3225 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3226 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3227 PARTITIONS 5
3228 set timestamp= unix_timestamp('2000-01-01 04:00:00');
3229 call sp2;
3230 call sp2;
3231 show create table t1;
3232 Table Create Table
3233 t1 CREATE TABLE `t1` (
3234 `x` int(11) DEFAULT NULL
3235 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3236 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3237 PARTITIONS 6
3238 set timestamp= unix_timestamp('2000-01-01 05:00:00');
3239 execute ps;
3240 execute ps;
3241 show create table t1;
3242 Table Create Table
3243 t1 CREATE TABLE `t1` (
3244 `x` int(11) DEFAULT NULL
3245 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3246 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3247 PARTITIONS 7
3248 set timestamp= unix_timestamp('2000-01-01 06:00:00');
3249 execute ps2;
3250 execute ps2;
3251 show create table t1;
3252 Table Create Table
3253 t1 CREATE TABLE `t1` (
3254 `x` int(11) DEFAULT NULL
3255 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3256 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3257 PARTITIONS 8
3258 set timestamp= unix_timestamp('2000-01-01 08:00:00');
3259 lock tables t1 write, t2 write;
3260 show create table t1;
3261 Table Create Table
3262 t1 CREATE TABLE `t1` (
3263 `x` int(11) DEFAULT NULL
3264 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3265 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3266 PARTITIONS 10
3267 set timestamp= unix_timestamp('2000-01-01 09:00:00');
3268 update t1 set x= x + 1;
3269 show create table t1;
3270 Table Create Table
3271 t1 CREATE TABLE `t1` (
3272 `x` int(11) DEFAULT NULL
3273 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3274 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3275 PARTITIONS 11
3276 set timestamp= unix_timestamp('2000-01-01 10:00:00');
3277 update t1 set x= x + 2;
3278 show create table t1;
3279 Table Create Table
3280 t1 CREATE TABLE `t1` (
3281 `x` int(11) DEFAULT NULL
3282 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3283 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3284 PARTITIONS 12
3285 update t2 set x= x + 1;
3286 set timestamp= unix_timestamp('2000-01-01 11:00:00');
3287 insert into t2 values (4);
3288 show create table t1;
3289 Table Create Table
3290 t1 CREATE TABLE `t1` (
3291 `x` int(11) DEFAULT NULL
3292 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3293 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3294 PARTITIONS 13
3295 update t3 set x= x + 1;
3296 ERROR HY000: Table 't3' was not locked with LOCK TABLES
3297 set timestamp= unix_timestamp('2000-01-01 12:00:00');
3298 call sp;
3299 call sp;
3300 show create table t1;
3301 Table Create Table
3302 t1 CREATE TABLE `t1` (
3303 `x` int(11) DEFAULT NULL
3304 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3305 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3306 PARTITIONS 14
3307 set timestamp= unix_timestamp('2000-01-01 13:00:00');
3308 call sp2;
3309 call sp2;
3310 show create table t1;
3311 Table Create Table
3312 t1 CREATE TABLE `t1` (
3313 `x` int(11) DEFAULT NULL
3314 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3315 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3316 PARTITIONS 15
3317 set timestamp= unix_timestamp('2000-01-01 14:00:00');
3318 execute ps;
3319 execute ps;
3320 show create table t1;
3321 Table Create Table
3322 t1 CREATE TABLE `t1` (
3323 `x` int(11) DEFAULT NULL
3324 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3325 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3326 PARTITIONS 16
3327 set timestamp= unix_timestamp('2000-01-01 15:00:00');
3328 execute ps2;
3329 execute ps2;
3330 show create table t1;
3331 Table Create Table
3332 t1 CREATE TABLE `t1` (
3333 `x` int(11) DEFAULT NULL
3334 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3335 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3336 PARTITIONS 17
3337 unlock tables;
3338 show create table t1;
3339 Table Create Table
3340 t1 CREATE TABLE `t1` (
3341 `x` int(11) DEFAULT NULL
3342 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
3343 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
3344 PARTITIONS 17
3345 drop tables t1, t2, t3;
3346 drop procedure sp;
3347 drop procedure sp2;
3348 drop prepare ps;
3349 drop prepare ps2;
3350 #
3351 # MDEV-27456 Assertion `!thd->is_error()' fails in vers_create_partitions upon DML with ER_UNKNOWN_PARTITION
3352 #
3353 create table t (a int) with system versioning
3354 partition by system_time interval 1 minute auto;
3355 set @@timestamp= @@timestamp + 61;
3356 select * from t;
3357 a
3358 delete from t partition (px);
3359 ERROR HY000: Unknown partition 'px' in table 't'
3360 lock tables t write;
3361 delete from t partition (px);
3362 ERROR HY000: Unknown partition 'px' in table 't'
3363 unlock tables;
3364 drop table t;
3365 set timestamp= default;
3366 #
3367 # MDEV-28978 Assertion failure in THD::binlog_query or unexpected
3368 # ER_ERROR_ON_WRITE with auto-partitioning
3369 #
3370 create table t (a int) with system versioning partition by system_time limit 6 auto;
3371 insert into t () values (),(),(),(),(),();
3372 update t set a = 1;
3373 update t set a = 2 limit 0;
3374 drop table t;
3375 #
3376 # MDEV-31244 Assertion "not SELECT" in vers_set_hist_part()
3377 #
3378 create table t (a int) with system versioning partition by system_time;
3379 create function f() returns int
3380 begin
3381 update t set a = 1;
3382 return 1;
3383 end $
3384 create procedure p() select f();
3385 call p();
3386 f()
3387 1
3388 call p();
3389 f()
3390 1
3391 drop procedure p;
3392 drop function f;
3393 drop table t;
3394 #
3395 # MDEV-29873 MSAN uninitialized value errors in bcmp /
3396 # prep_alter_part_table upon re-partitioning by system time
3397 #
3398 create table t (a int) with system versioning partition by system_time interval 5 week;
3399 alter table t partition by system_time interval 10 week;
3400 drop table t;
3401 #
3402 # MDEV-16546 System versioning setting to allow history modification
3403 #
3404 create table t1 (a varchar(100)) with system versioning
3405 partition by system_time interval 1 day
3406 starts '2021-09-30 00:00:00' partitions 3;
3407 set system_versioning_insert_history=1;
3408 insert into t1 (a,row_start,row_end) values
3409 ('p0', '2021-09-30', '2021-09-30 10:00:00'),
3410 ('p1', '2021-09-30', '2021-10-01 10:00:00'),
3411 ('overflows, so also p1','2021-09-30', '2021-10-10 10:00:00'),
3412 ('pn, current', '2021-09-30', SYS_TIME_MAX);
3413 select table_name,partition_name,partition_ordinal_position,partition_method,partition_description,table_rows
3414 from information_schema.partitions where table_schema='test';
3415 table_name partition_name partition_ordinal_position partition_method partition_description table_rows
3416 t1 p0 1 SYSTEM_TIME 2021-10-01 00:00:00 1
3417 t1 p1 2 SYSTEM_TIME 2021-10-02 00:00:00 2
3418 t1 pn 3 SYSTEM_TIME CURRENT 1
3419 drop table t1;
3420 set system_versioning_insert_history=0;
3421 #
3422 # MDEV-29727 ALTER and CREATE with default partitioning
3423 # differently react to SQL_MODE => unusable SHOW CREATE
3424 #
3425 create table t (a int) with system versioning;
3426 alter table t partition by system_time partitions 3;
3427 ERROR HY000: Maybe missing parameters: no rotation condition for multiple HISTORY partitions.
3428 drop table t;
3429 create table t (a int) with system versioning partition by system_time partitions 3;
3430 ERROR HY000: Maybe missing parameters: no rotation condition for multiple HISTORY partitions.
3431 #
3432 # MDEV-36115 InnoDB: assertion: node->pcur->rel_pos == BTR_PCUR_ON
3433 # in row_update_for_mysql
3434 #
3435 create table t (a int key) engine=innodb
3436 with system versioning
3437 partition by key() partitions 3;
3438 start transaction;
3439 insert into t values (1),(2),(3),(4),(5),(6),(7),(8);
3440 set timestamp=+1;
3441 delete from t;
3442 insert into t values (1),(2);
3443 DELETE from t;
3444 drop table t;
3445 #
3446 # MDEV-36817 Server crashes in do_mark_index_columns instead of
3447 # ER_DUP_ENTRY on partitioned table
3448 #
3449 create table t (f int, unique(f)) engine=innodb partition by key (f) partitions 2;
3450 insert into t (f) values (1), (3);
3451 update t set f = 0;
3452 ERROR 23000: Duplicate entry '0' for key 'f'
3453 drop table t;
3454 #
3455 # End of 10.5 tests
3456 #
3457 #
3458 # MDEV-31903 Server crashes in _ma_reset_history upon UNLOCK table with auto-create history partitions
3459 #
3460 set timestamp= unix_timestamp('2000-01-01 00:00:00');
3461 create table t1 (x int) engine=aria with system versioning partition by system_time interval 1 hour auto partitions 3;
3462 insert into t1 values (1);
3463 create table t2 (x int) engine=aria;
3464 create trigger tr after insert on t2 for each row update t1 set x= x + 11;
3465 lock tables t1 write, t2 write;
3466 update t1 set x= x + 1;
3467 set timestamp= unix_timestamp('2000-01-01 13:00:00');
3468 insert into t2 values (5);
3469 unlock tables;
3470 drop table t1, t2;
3471 set timestamp= default;
3472 #
3473 # MDEV-29872 MSAN/Valgrind uninitialised value errors in TABLE::vers_switch_partition
3474 #
3475 create table t (a int) with system versioning partition by system_time limit 100 partitions 3;
3476 drop table t;
3477 #
3478 # End of 10.9 tests
3479 #
3480 #
3481 # MDEV-34775 Wrong reopen of already open routine due to auto-create in SP
3482 #
3483 create table t (a int) with system versioning
3484 partition by system_time
3485 interval 1 minute auto;
3486 create function f()
3487 returns int
3488 begin
3489 replace into t select * from t;
3490 return 0;
3491 end $
3492 set timestamp= @@timestamp + 61;
3493 select f();
3494 f()
3495 0
3496 drop table t;
3497 drop function f;
3498 #
3499 # MDEV-33370 Assertion `!is_set() || (m_status == DA_OK_BULK &&
3500 # is_bulk_op())' failed after ALTER TABLE of versioned table
3501 #
3502 create table t1 (i int) with system versioning
3503 partition by system_time interval 1 month (
3504 partition ver_p1 history,
3505 partition ver_p2 history,
3506 partition ver_pn current);
3507 alter table `t1` partition by system_time interval 10007 year ;
3508 ERROR 22003: TIMESTAMP value is out of range in 'INTERVAL'
3509 drop table t1;
3510 #
3511 # MDEV-29114 ALTER puts row into different partition for system_time
3512 # interval partitioning
3513 #
3514 # CREATE case
3515 set timestamp= unix_timestamp('2000-01-01 00:00:00');
3516 create or replace table t1 (x int) with system versioning
3517 partition by system_time interval 1 hour (
3518 partition p0 history, partition p1 history, partition pn current);
3519 set timestamp= unix_timestamp('2000-01-01 00:00:00');
3520 insert t1 values (0);
3521 set timestamp= unix_timestamp('2000-01-01 00:10:00');
3522 update t1 set x= 1;
3523 set timestamp= unix_timestamp('2000-01-01 01:00:00');
3524 update t1 set x= 2;
3525 set timestamp= unix_timestamp('2000-01-01 01:30:00');
3526 update t1 set x= 3;
3527 # CREATE result: row 1 got into p1
3528 select *, row_start, row_end from t1 partition (p0);
3529 x row_start row_end
3530 0 2000-01-01 00:00:00.000000 2000-01-01 00:10:00.000000
3531 select *, row_start, row_end from t1 partition (p1);
3532 x row_start row_end
3533 1 2000-01-01 00:10:00.000000 2000-01-01 01:00:00.000000
3534 2 2000-01-01 01:00:00.000000 2000-01-01 01:30:00.000000
3535 flush tables;
3536 # For CREATE pruning is affected by current timestamp, but SELECT works
3537 # in any case since row 1 got into p1
3538 set timestamp= unix_timestamp('2000-01-01 00:00:00');
3539 explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
3540 id select_type table partitions type possible_keys key key_len ref rows Extra
3541 1 SIMPLE t1 p0,p1,pn ALL NULL NULL NULL NULL 4 Using where
3542 select * from t1 for system_time as of '2000-01-01 00:59:59';
3543 x
3544 1
3545 set timestamp= unix_timestamp('2020-01-01 00:00:00');
3546 explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
3547 id select_type table partitions type possible_keys key key_len ref rows Extra
3548 1 SIMPLE t1 p0,p1,pn ALL NULL NULL NULL NULL 4 Using where
3549 select * from t1 for system_time as of '2000-01-01 00:59:59';
3550 x
3551 1
3552 # ALTER case
3553 set timestamp= unix_timestamp('2000-01-01 00:00:00');
3554 alter table t1
3555 partition by system_time interval 1 hour (
3556 partition p0 history, partition p1 history, partition pn current);
3557 # ALTER result: row 1 got into p1
3558 Select *, row_start, row_end from t1 partition (p0);
3559 x row_start row_end
3560 0 2000-01-01 00:00:00.000000 2000-01-01 00:10:00.000000
3561 Select *, row_start, row_end from t1 partition (p1);
3562 x row_start row_end
3563 1 2000-01-01 00:10:00.000000 2000-01-01 01:00:00.000000
3564 2 2000-01-01 01:00:00.000000 2000-01-01 01:30:00.000000
3565 flush tables;
3566 Explain partitions select * from t1 for system_time as of '2000-01-01 00:59:59';
3567 id select_type table partitions type possible_keys key key_len ref rows Extra
3568 1 SIMPLE t1 p0,p1,pn ALL NULL NULL NULL NULL 4 Using where
3569 Select * from t1 for system_time as of '2000-01-01 00:59:59';
3570 x
3571 1
3572 drop table t1;
3573 #
3574 # MDEV-36362 MariaDB crashes when parsing fuzzer generated PARTITION
3575 #
3576 create table t
3577 partition by system_time
3578 interval setval(a, 1) second_microsecond;
3579 ERROR 42000: INTERVAL does not support subqueries or stored functions
3580 create table t (x int) with system versioning
3581 partition by system_time interval nextval(s) hour;
3582 ERROR 42000: INTERVAL does not support subqueries or stored functions
3583 create table t (x int) with system versioning
3584 partition by system_time interval lastval(s) hour;
3585 ERROR 42000: INTERVAL does not support subqueries or stored functions
3586 #
3587 # MDEV-33289 INTERVAL partitioning by system time does not work close to
3588 # the end of timestamp range
3589 #
3590 set timestamp= unix_timestamp('2024-01-21 00:00:00');
3591 create or replace table t (a int) with system versioning
3592 partition by system_time interval 10 year auto;
3593 insert into t values (0);
3594 set timestamp= unix_timestamp('2034-01-20 23:59:59');
3595 update t set a= 1;
3596 set timestamp= unix_timestamp('2034-01-20 00:00:00');
3597 update t set a= 2;
3598 set timestamp= unix_timestamp('2035-01-21 00:00:00');
3599 update t set a= 3;
3600 select * from t partition (p0);
3601 a
3602 0
3603 select * from t partition (p1);
3604 a
3605 2
3606 select * from t partition (pn);
3607 a
3608 3
3609 select * from t for system_time as of '2035-01-20 00:00:00';
3610 a
3611 2
3612 drop tables t;
3613 create or replace table t (a int) with system versioning
3614 partition by system_time interval 10 year partitions 10;
3615 ERROR 22003: TIMESTAMP value is out of range in 'INTERVAL'
3616 create or replace table t (a int) with system versioning
3617 partition by system_time interval 10 year;
3618 drop table t;
3619 #
3620 # MDEV-38710 Assertion is_lock_owner on error returning from auto-create in mysql_admin_table
3621 #
3622 set timestamp=2147483647;
3623 create table t (x int) with system versioning partition by system_time interval 1 hour auto;
3624 repair table t;
3625 Table Op Msg_type Msg_text
3626 test.t repair status OK
3627 drop table t;
3628 set timestamp=default;
3629 # End of 10.11 tests
3630 set global innodb_stats_persistent= @save_persistent;