Debian Codegraph @

mysql-test/suite/parts/inc/partition_timestamp.inc

raw
1 eval create table t1 (a timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key(a)) engine=$engine
2 partition by key (a) (
3 partition pa1 max_rows=20 min_rows=2,
4 partition pa2 max_rows=30 min_rows=3,
5 partition pa3 max_rows=30 min_rows=4,
6 partition pa4 max_rows=40 min_rows=2);
7 show create table t1;
8 insert into t1 values ('1975-01-01 21:21:21'), ('2020-12-31 12:10:30'), ('1980-10-14 03:03'), ('2000-06-15 23:59');
9 --sorted_result
10 select * from t1;
11 select * from t1 where a=19801014030300;
12 delete from t1 where a=19801014030300;
13 --sorted_result
14 select * from t1;
15 drop table t1;
16
17 eval create table t2 (a timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key(a)) engine=$engine
18 partition by key (a) partitions 12;
19 show create table t2;
20 insert into t2 values ('1975-01-01 0:1:1'), ('2020-12-31 10:11:12'), ('1980-10-14 13:14:15'), ('2000-06-15 14:15:16');
21 --sorted_result
22 select * from t2;
23 select * from t2 where a='1980-10-14 13:14:15';
24 delete from t2 where a='1980-10-14 13:14:15';
25 --sorted_result
26 select * from t2;
27 delete from t2;
28 let $count=59;
29 --echo $count inserts;
30 --disable_query_log
31 begin;
32 while ($count)
33 {
34 eval insert into t2 values (19710101000000+$count);
35 dec $count;
36 }
37 commit;
38 --enable_query_log
39 select count(*) from t2;
40 --sorted_result
41 select * from t2;
42 drop table t2;
43
44 ################################################################################
45 # The following 2 tests are no longer valid after bug #42849 has been fixed:
46 # it is not possible to use a timezone-dependent (such as month(timestamp_col)
47 # or just a timestamp_col in a numeric context) anymore.
48 ################################################################################
49
50 # eval create table t3 (a timestamp not null, primary key(a)) engine=$engine
51 # partition by range (month(a)) subpartition by key (a)
52 # subpartitions 3 (
53 # partition quarter1 values less than (4),
54 # partition quarter2 values less than (7),
55 # partition quarter3 values less than (10),
56 # partition quarter4 values less than (13)
57 # );
58 # show create table t3;
59 # let $count=12;
60 # --echo $count inserts;
61 # --disable_query_log
62 # SET TIME_ZONE= '+03:00';
63 # begin;
64 # while ($count)
65 # {
66 # eval insert into t3 values (date_add('1970-01-01 00:00:00',interval $count-1 month));
67 # dec $count;
68 # }
69 # commit;
70 # --enable_query_log
71 # select count(*) from t3;
72 # select * from t3;
73 # drop table t3;
74
75 # eval create table t4 (a timestamp not null, primary key(a)) engine=$engine
76 # partition by list (month(a)) subpartition by key (a)
77 # subpartitions 3 (
78 # partition quarter1 values in (0,1,2,3),
79 # partition quarter2 values in (4,5,6),
80 # partition quarter3 values in (7,8,9),
81 # partition quarter4 values in (10,11,12)
82 # );
83 # show create table t4;
84 # let $count=12;
85 # --echo $count inserts;
86 # --disable_query_log
87 # begin;
88 # while ($count)
89 # {
90 # eval insert into t4 values (date_add('1970-01-01 00:00:00',interval $count-1 month));
91 # dec $count;
92 # }
93 # commit;
94 # --enable_query_log
95 # select count(*) from t4;
96 # select * from t4;
97 # drop table t4;