summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/parts/r/partition_datetime_innodb.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/parts/r/partition_datetime_innodb.result')
-rw-r--r--mysql-test/suite/parts/r/partition_datetime_innodb.result1192
1 files changed, 1192 insertions, 0 deletions
diff --git a/mysql-test/suite/parts/r/partition_datetime_innodb.result b/mysql-test/suite/parts/r/partition_datetime_innodb.result
new file mode 100644
index 00000000..8779bfea
--- /dev/null
+++ b/mysql-test/suite/parts/r/partition_datetime_innodb.result
@@ -0,0 +1,1192 @@
+create table t1 (a timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key(a)) engine='InnoDB'
+partition by key (a) (
+partition pa1 max_rows=20 min_rows=2,
+partition pa2 max_rows=30 min_rows=3,
+partition pa3 max_rows=30 min_rows=4,
+partition pa4 max_rows=40 min_rows=2);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`a`)
+(PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
+ PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
+ PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
+ PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB)
+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');
+select * from t1;
+a
+1975-01-01 21:21:21
+1980-10-14 03:03:00
+2000-06-15 23:59:00
+2020-12-31 12:10:30
+select * from t1 where a=19801014030300;
+a
+1980-10-14 03:03:00
+delete from t1 where a=19801014030300;
+select * from t1;
+a
+1975-01-01 21:21:21
+2000-06-15 23:59:00
+2020-12-31 12:10:30
+drop table t1;
+create table t2 (a timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key(a)) engine='InnoDB'
+partition by key (a) partitions 12;
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`a`)
+PARTITIONS 12
+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');
+select * from t2;
+a
+1975-01-01 00:01:01
+1980-10-14 13:14:15
+2000-06-15 14:15:16
+2020-12-31 10:11:12
+select * from t2 where a='1980-10-14 13:14:15';
+a
+1980-10-14 13:14:15
+delete from t2 where a='1980-10-14 13:14:15';
+select * from t2;
+a
+1975-01-01 00:01:01
+2000-06-15 14:15:16
+2020-12-31 10:11:12
+delete from t2;
+59 inserts;
+select count(*) from t2;
+count(*)
+59
+select * from t2;
+a
+1971-01-01 00:00:01
+1971-01-01 00:00:02
+1971-01-01 00:00:03
+1971-01-01 00:00:04
+1971-01-01 00:00:05
+1971-01-01 00:00:06
+1971-01-01 00:00:07
+1971-01-01 00:00:08
+1971-01-01 00:00:09
+1971-01-01 00:00:10
+1971-01-01 00:00:11
+1971-01-01 00:00:12
+1971-01-01 00:00:13
+1971-01-01 00:00:14
+1971-01-01 00:00:15
+1971-01-01 00:00:16
+1971-01-01 00:00:17
+1971-01-01 00:00:18
+1971-01-01 00:00:19
+1971-01-01 00:00:20
+1971-01-01 00:00:21
+1971-01-01 00:00:22
+1971-01-01 00:00:23
+1971-01-01 00:00:24
+1971-01-01 00:00:25
+1971-01-01 00:00:26
+1971-01-01 00:00:27
+1971-01-01 00:00:28
+1971-01-01 00:00:29
+1971-01-01 00:00:30
+1971-01-01 00:00:31
+1971-01-01 00:00:32
+1971-01-01 00:00:33
+1971-01-01 00:00:34
+1971-01-01 00:00:35
+1971-01-01 00:00:36
+1971-01-01 00:00:37
+1971-01-01 00:00:38
+1971-01-01 00:00:39
+1971-01-01 00:00:40
+1971-01-01 00:00:41
+1971-01-01 00:00:42
+1971-01-01 00:00:43
+1971-01-01 00:00:44
+1971-01-01 00:00:45
+1971-01-01 00:00:46
+1971-01-01 00:00:47
+1971-01-01 00:00:48
+1971-01-01 00:00:49
+1971-01-01 00:00:50
+1971-01-01 00:00:51
+1971-01-01 00:00:52
+1971-01-01 00:00:53
+1971-01-01 00:00:54
+1971-01-01 00:00:55
+1971-01-01 00:00:56
+1971-01-01 00:00:57
+1971-01-01 00:00:58
+1971-01-01 00:00:59
+drop table t2;
+create table t1 (a date not null, primary key(a)) engine='InnoDB'
+partition by key (a) (
+partition pa1 max_rows=20 min_rows=2,
+partition pa2 max_rows=30 min_rows=3,
+partition pa3 max_rows=30 min_rows=4,
+partition pa4 max_rows=40 min_rows=2);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` date NOT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`a`)
+(PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
+ PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
+ PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
+ PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB)
+insert into t1 values ('1975-01-01'), ('2020-12-31'), ('1980-10-14'), ('2000-06-15');
+select * from t1;
+a
+1975-01-01
+1980-10-14
+2000-06-15
+2020-12-31
+select * from t1 where a=19801014;
+a
+1980-10-14
+delete from t1 where a=19801014;
+select * from t1;
+a
+1975-01-01
+2000-06-15
+2020-12-31
+drop table t1;
+create table t2 (a date not null, primary key(a)) engine='InnoDB'
+partition by key (a) partitions 12;
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` date NOT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`a`)
+PARTITIONS 12
+insert into t2 values ('1975-01-01'), ('2020-12-31'), ('1980-10-14'), ('2000-06-15');
+select * from t2;
+a
+1975-01-01
+1980-10-14
+2000-06-15
+2020-12-31
+select * from t2 where a='1980-10-14';
+a
+1980-10-14
+delete from t2 where a='1980-10-14';
+select * from t2;
+a
+1975-01-01
+2000-06-15
+2020-12-31
+delete from t2;
+28 inserts;
+select count(*) from t2;
+count(*)
+84
+select * from t2;
+a
+1970-01-01
+1970-01-02
+1970-01-03
+1970-01-04
+1970-01-05
+1970-01-06
+1970-01-07
+1970-01-08
+1970-01-09
+1970-01-10
+1970-01-11
+1970-01-12
+1970-01-13
+1970-01-14
+1970-01-15
+1970-01-16
+1970-01-17
+1970-01-18
+1970-01-19
+1970-01-20
+1970-01-21
+1970-01-22
+1970-01-23
+1970-01-24
+1970-01-25
+1970-01-26
+1970-01-27
+1970-01-28
+1970-02-01
+1970-02-02
+1970-02-03
+1970-02-04
+1970-02-05
+1970-02-06
+1970-02-07
+1970-02-08
+1970-02-09
+1970-02-10
+1970-02-11
+1970-02-12
+1970-02-13
+1970-02-14
+1970-02-15
+1970-02-16
+1970-02-17
+1970-02-18
+1970-02-19
+1970-02-20
+1970-02-21
+1970-02-22
+1970-02-23
+1970-02-24
+1970-02-25
+1970-02-26
+1970-02-27
+1970-02-28
+1970-03-01
+1970-03-02
+1970-03-03
+1970-03-04
+1970-03-05
+1970-03-06
+1970-03-07
+1970-03-08
+1970-03-09
+1970-03-10
+1970-03-11
+1970-03-12
+1970-03-13
+1970-03-14
+1970-03-15
+1970-03-16
+1970-03-17
+1970-03-18
+1970-03-19
+1970-03-20
+1970-03-21
+1970-03-22
+1970-03-23
+1970-03-24
+1970-03-25
+1970-03-26
+1970-03-27
+1970-03-28
+drop table t2;
+create table t3 (a date not null, primary key(a)) engine='InnoDB'
+partition by range (month(a)) subpartition by key (a)
+subpartitions 3 (
+partition quarter1 values less than (4),
+partition quarter2 values less than (7),
+partition quarter3 values less than (10),
+partition quarter4 values less than (13)
+);
+show create table t3;
+Table Create Table
+t3 CREATE TABLE `t3` (
+ `a` date NOT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY RANGE (month(`a`))
+SUBPARTITION BY KEY (`a`)
+SUBPARTITIONS 3
+(PARTITION `quarter1` VALUES LESS THAN (4) ENGINE = InnoDB,
+ PARTITION `quarter2` VALUES LESS THAN (7) ENGINE = InnoDB,
+ PARTITION `quarter3` VALUES LESS THAN (10) ENGINE = InnoDB,
+ PARTITION `quarter4` VALUES LESS THAN (13) ENGINE = InnoDB)
+12 inserts;
+select count(*) from t3;
+count(*)
+12
+select * from t3;
+a
+1970-01-01
+1970-02-01
+1970-03-01
+1970-04-01
+1970-05-01
+1970-06-01
+1970-07-01
+1970-08-01
+1970-09-01
+1970-10-01
+1970-11-01
+1970-12-01
+drop table t3;
+create table t4 (a date not null, primary key(a)) engine='InnoDB'
+partition by list (month(a)) subpartition by key (a)
+subpartitions 3 (
+partition quarter1 values in (1,2,3),
+partition quarter2 values in (4,5,6),
+partition quarter3 values in (7,8,9),
+partition quarter4 values in (10,11,12)
+);
+show create table t4;
+Table Create Table
+t4 CREATE TABLE `t4` (
+ `a` date NOT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY LIST (month(`a`))
+SUBPARTITION BY KEY (`a`)
+SUBPARTITIONS 3
+(PARTITION `quarter1` VALUES IN (1,2,3) ENGINE = InnoDB,
+ PARTITION `quarter2` VALUES IN (4,5,6) ENGINE = InnoDB,
+ PARTITION `quarter3` VALUES IN (7,8,9) ENGINE = InnoDB,
+ PARTITION `quarter4` VALUES IN (10,11,12) ENGINE = InnoDB)
+12 inserts;
+select count(*) from t4;
+count(*)
+12
+select * from t4;
+a
+1970-01-01
+1970-02-01
+1970-03-01
+1970-04-01
+1970-05-01
+1970-06-01
+1970-07-01
+1970-08-01
+1970-09-01
+1970-10-01
+1970-11-01
+1970-12-01
+drop table t4;
+create table t1 (a time not null, primary key(a)) engine='InnoDB'
+partition by key (a) (
+partition pa1 max_rows=20 min_rows=2,
+partition pa2 max_rows=30 min_rows=3,
+partition pa3 max_rows=30 min_rows=4,
+partition pa4 max_rows=40 min_rows=2);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` time NOT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`a`)
+(PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
+ PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
+ PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
+ PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB)
+insert into t1 values ('21:21:21'), ('12:10:30'), ('03:03:03'), ('23:59');
+select * from t1;
+a
+03:03:03
+12:10:30
+21:21:21
+23:59:00
+select * from t1 where a=030303;
+a
+03:03:03
+delete from t1 where a=030303;
+select * from t1;
+a
+12:10:30
+21:21:21
+23:59:00
+drop table t1;
+create table t2 (a time not null, primary key(a)) engine='InnoDB'
+partition by key (a) partitions 12;
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` time NOT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`a`)
+PARTITIONS 12
+insert into t2 values ('0:1:1'), ('10:11:12'), ('13:14:15'), ('14:15:16');
+select * from t2;
+a
+00:01:01
+10:11:12
+13:14:15
+14:15:16
+select * from t2 where a='13:14:15';
+a
+13:14:15
+delete from t2 where a='13:14:15';
+select * from t2;
+a
+00:01:01
+10:11:12
+14:15:16
+delete from t2;
+59 inserts;
+select count(*) from t2;
+count(*)
+59
+select * from t2;
+a
+00:01:01
+00:01:02
+00:01:03
+00:01:04
+00:01:05
+00:01:06
+00:01:07
+00:01:08
+00:01:09
+00:01:10
+00:01:11
+00:01:12
+00:01:13
+00:01:14
+00:01:15
+00:01:16
+00:01:17
+00:01:18
+00:01:19
+00:01:20
+00:01:21
+00:01:22
+00:01:23
+00:01:24
+00:01:25
+00:01:26
+00:01:27
+00:01:28
+00:01:29
+00:01:30
+00:01:31
+00:01:32
+00:01:33
+00:01:34
+00:01:35
+00:01:36
+00:01:37
+00:01:38
+00:01:39
+00:01:40
+00:01:41
+00:01:42
+00:01:43
+00:01:44
+00:01:45
+00:01:46
+00:01:47
+00:01:48
+00:01:49
+00:01:50
+00:01:51
+00:01:52
+00:01:53
+00:01:54
+00:01:55
+00:01:56
+00:01:57
+00:01:58
+00:01:59
+drop table t2;
+create table t3 (a time not null, primary key(a)) engine='InnoDB'
+partition by range (second(a)) subpartition by key (a)
+subpartitions 3 (
+partition quarter1 values less than (16),
+partition quarter2 values less than (31),
+partition quarter3 values less than (46),
+partition quarter4 values less than (61)
+);
+show create table t3;
+Table Create Table
+t3 CREATE TABLE `t3` (
+ `a` time NOT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY RANGE (second(`a`))
+SUBPARTITION BY KEY (`a`)
+SUBPARTITIONS 3
+(PARTITION `quarter1` VALUES LESS THAN (16) ENGINE = InnoDB,
+ PARTITION `quarter2` VALUES LESS THAN (31) ENGINE = InnoDB,
+ PARTITION `quarter3` VALUES LESS THAN (46) ENGINE = InnoDB,
+ PARTITION `quarter4` VALUES LESS THAN (61) ENGINE = InnoDB)
+59 inserts;
+select count(*) from t3;
+count(*)
+59
+select * from t3;
+a
+10:00:01
+10:00:02
+10:00:03
+10:00:04
+10:00:05
+10:00:06
+10:00:07
+10:00:08
+10:00:09
+10:00:10
+10:00:11
+10:00:12
+10:00:13
+10:00:14
+10:00:15
+10:00:16
+10:00:17
+10:00:18
+10:00:19
+10:00:20
+10:00:21
+10:00:22
+10:00:23
+10:00:24
+10:00:25
+10:00:26
+10:00:27
+10:00:28
+10:00:29
+10:00:30
+10:00:31
+10:00:32
+10:00:33
+10:00:34
+10:00:35
+10:00:36
+10:00:37
+10:00:38
+10:00:39
+10:00:40
+10:00:41
+10:00:42
+10:00:43
+10:00:44
+10:00:45
+10:00:46
+10:00:47
+10:00:48
+10:00:49
+10:00:50
+10:00:51
+10:00:52
+10:00:53
+10:00:54
+10:00:55
+10:00:56
+10:00:57
+10:00:58
+10:00:59
+drop table t3;
+create table t4 (a time not null, primary key(a)) engine='InnoDB'
+partition by list (second(a)) subpartition by key (a)
+subpartitions 3 (
+partition quarter1 values in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15),
+partition quarter2 values in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30),
+partition quarter3 values in (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45),
+partition quarter4 values in (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60)
+);
+show create table t4;
+Table Create Table
+t4 CREATE TABLE `t4` (
+ `a` time NOT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY LIST (second(`a`))
+SUBPARTITION BY KEY (`a`)
+SUBPARTITIONS 3
+(PARTITION `quarter1` VALUES IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ENGINE = InnoDB,
+ PARTITION `quarter2` VALUES IN (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) ENGINE = InnoDB,
+ PARTITION `quarter3` VALUES IN (31,32,33,34,35,36,37,38,39,40,41,42,43,44,45) ENGINE = InnoDB,
+ PARTITION `quarter4` VALUES IN (46,47,48,49,50,51,52,53,54,55,56,57,58,59,60) ENGINE = InnoDB)
+59 inserts;
+select count(*) from t4;
+count(*)
+59
+select * from t4;
+a
+10:00:01
+10:00:02
+10:00:03
+10:00:04
+10:00:05
+10:00:06
+10:00:07
+10:00:08
+10:00:09
+10:00:10
+10:00:11
+10:00:12
+10:00:13
+10:00:14
+10:00:15
+10:00:16
+10:00:17
+10:00:18
+10:00:19
+10:00:20
+10:00:21
+10:00:22
+10:00:23
+10:00:24
+10:00:25
+10:00:26
+10:00:27
+10:00:28
+10:00:29
+10:00:30
+10:00:31
+10:00:32
+10:00:33
+10:00:34
+10:00:35
+10:00:36
+10:00:37
+10:00:38
+10:00:39
+10:00:40
+10:00:41
+10:00:42
+10:00:43
+10:00:44
+10:00:45
+10:00:46
+10:00:47
+10:00:48
+10:00:49
+10:00:50
+10:00:51
+10:00:52
+10:00:53
+10:00:54
+10:00:55
+10:00:56
+10:00:57
+10:00:58
+10:00:59
+drop table t4;
+create table t1 (a datetime not null, primary key(a)) engine='InnoDB'
+partition by key (a) (
+partition pa1 max_rows=20 min_rows=2,
+partition pa2 max_rows=30 min_rows=3,
+partition pa3 max_rows=30 min_rows=4,
+partition pa4 max_rows=40 min_rows=2);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` datetime NOT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`a`)
+(PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
+ PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
+ PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
+ PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB)
+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');
+select * from t1;
+a
+1975-01-01 21:21:21
+1980-10-14 03:03:00
+2000-06-15 23:59:00
+2020-12-31 12:10:30
+select * from t1 where a=19801014030300;
+a
+1980-10-14 03:03:00
+delete from t1 where a=19801014030300;
+select * from t1;
+a
+1975-01-01 21:21:21
+2000-06-15 23:59:00
+2020-12-31 12:10:30
+drop table t1;
+create table t2 (a datetime not null, primary key(a)) engine='InnoDB'
+partition by key (a) partitions 12;
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` datetime NOT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`a`)
+PARTITIONS 12
+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');
+select * from t2;
+a
+1975-01-01 00:01:01
+1980-10-14 13:14:15
+2000-06-15 14:15:16
+2020-12-31 10:11:12
+select * from t2 where a='1980-10-14 13:14:15';
+a
+1980-10-14 13:14:15
+delete from t2 where a='1980-10-14 13:14:15';
+select * from t2;
+a
+1975-01-01 00:01:01
+2000-06-15 14:15:16
+2020-12-31 10:11:12
+delete from t2;
+59 inserts;
+select count(*) from t2;
+count(*)
+59
+select * from t2;
+a
+1970-01-01 00:00:01
+1970-01-01 00:00:02
+1970-01-01 00:00:03
+1970-01-01 00:00:04
+1970-01-01 00:00:05
+1970-01-01 00:00:06
+1970-01-01 00:00:07
+1970-01-01 00:00:08
+1970-01-01 00:00:09
+1970-01-01 00:00:10
+1970-01-01 00:00:11
+1970-01-01 00:00:12
+1970-01-01 00:00:13
+1970-01-01 00:00:14
+1970-01-01 00:00:15
+1970-01-01 00:00:16
+1970-01-01 00:00:17
+1970-01-01 00:00:18
+1970-01-01 00:00:19
+1970-01-01 00:00:20
+1970-01-01 00:00:21
+1970-01-01 00:00:22
+1970-01-01 00:00:23
+1970-01-01 00:00:24
+1970-01-01 00:00:25
+1970-01-01 00:00:26
+1970-01-01 00:00:27
+1970-01-01 00:00:28
+1970-01-01 00:00:29
+1970-01-01 00:00:30
+1970-01-01 00:00:31
+1970-01-01 00:00:32
+1970-01-01 00:00:33
+1970-01-01 00:00:34
+1970-01-01 00:00:35
+1970-01-01 00:00:36
+1970-01-01 00:00:37
+1970-01-01 00:00:38
+1970-01-01 00:00:39
+1970-01-01 00:00:40
+1970-01-01 00:00:41
+1970-01-01 00:00:42
+1970-01-01 00:00:43
+1970-01-01 00:00:44
+1970-01-01 00:00:45
+1970-01-01 00:00:46
+1970-01-01 00:00:47
+1970-01-01 00:00:48
+1970-01-01 00:00:49
+1970-01-01 00:00:50
+1970-01-01 00:00:51
+1970-01-01 00:00:52
+1970-01-01 00:00:53
+1970-01-01 00:00:54
+1970-01-01 00:00:55
+1970-01-01 00:00:56
+1970-01-01 00:00:57
+1970-01-01 00:00:58
+1970-01-01 00:00:59
+drop table t2;
+create table t3 (a datetime not null, primary key(a)) engine='InnoDB'
+partition by range (month(a)) subpartition by key (a)
+subpartitions 3 (
+partition quarter1 values less than (4),
+partition quarter2 values less than (7),
+partition quarter3 values less than (10),
+partition quarter4 values less than (13)
+);
+show create table t3;
+Table Create Table
+t3 CREATE TABLE `t3` (
+ `a` datetime NOT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY RANGE (month(`a`))
+SUBPARTITION BY KEY (`a`)
+SUBPARTITIONS 3
+(PARTITION `quarter1` VALUES LESS THAN (4) ENGINE = InnoDB,
+ PARTITION `quarter2` VALUES LESS THAN (7) ENGINE = InnoDB,
+ PARTITION `quarter3` VALUES LESS THAN (10) ENGINE = InnoDB,
+ PARTITION `quarter4` VALUES LESS THAN (13) ENGINE = InnoDB)
+12 inserts;
+select count(*) from t3;
+count(*)
+12
+select * from t3;
+a
+1970-01-01 00:00:00
+1970-02-01 00:00:00
+1970-03-01 00:00:00
+1970-04-01 00:00:00
+1970-05-01 00:00:00
+1970-06-01 00:00:00
+1970-07-01 00:00:00
+1970-08-01 00:00:00
+1970-09-01 00:00:00
+1970-10-01 00:00:00
+1970-11-01 00:00:00
+1970-12-01 00:00:00
+drop table t3;
+create table t4 (a datetime not null, primary key(a)) engine='InnoDB'
+partition by list (month(a)) subpartition by key (a)
+subpartitions 3 (
+partition quarter1 values in (1,2,3),
+partition quarter2 values in (4,5,6),
+partition quarter3 values in (7,8,9),
+partition quarter4 values in (10,11,12)
+);
+show create table t4;
+Table Create Table
+t4 CREATE TABLE `t4` (
+ `a` datetime NOT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY LIST (month(`a`))
+SUBPARTITION BY KEY (`a`)
+SUBPARTITIONS 3
+(PARTITION `quarter1` VALUES IN (1,2,3) ENGINE = InnoDB,
+ PARTITION `quarter2` VALUES IN (4,5,6) ENGINE = InnoDB,
+ PARTITION `quarter3` VALUES IN (7,8,9) ENGINE = InnoDB,
+ PARTITION `quarter4` VALUES IN (10,11,12) ENGINE = InnoDB)
+12 inserts;
+select count(*) from t4;
+count(*)
+12
+select * from t4;
+a
+1970-01-01 00:00:00
+1970-02-01 00:00:00
+1970-03-01 00:00:00
+1970-04-01 00:00:00
+1970-05-01 00:00:00
+1970-06-01 00:00:00
+1970-07-01 00:00:00
+1970-08-01 00:00:00
+1970-09-01 00:00:00
+1970-10-01 00:00:00
+1970-11-01 00:00:00
+1970-12-01 00:00:00
+drop table t4;
+create table t1 (a year not null, primary key(a)) engine='InnoDB'
+partition by key (a) (
+partition pa1 max_rows=20 min_rows=2,
+partition pa2 max_rows=30 min_rows=3,
+partition pa3 max_rows=30 min_rows=4,
+partition pa4 max_rows=40 min_rows=2);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` year(4) NOT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`a`)
+(PARTITION `pa1` MAX_ROWS = 20 MIN_ROWS = 2 ENGINE = InnoDB,
+ PARTITION `pa2` MAX_ROWS = 30 MIN_ROWS = 3 ENGINE = InnoDB,
+ PARTITION `pa3` MAX_ROWS = 30 MIN_ROWS = 4 ENGINE = InnoDB,
+ PARTITION `pa4` MAX_ROWS = 40 MIN_ROWS = 2 ENGINE = InnoDB)
+insert into t1 values ('1975'), (2020), ('1980'), ('2000');
+select * from t1;
+a
+1975
+1980
+2000
+2020
+select * from t1 where a=1980;
+a
+1980
+delete from t1 where a=1980;
+select * from t1;
+a
+1975
+2000
+2020
+drop table t1;
+create table t2 (a year not null, primary key(a)) engine='InnoDB'
+partition by key (a) partitions 12;
+show create table t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` year(4) NOT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+ PARTITION BY KEY (`a`)
+PARTITIONS 12
+insert into t2 values ('1975'), ('2020'), ('1980'), ('2000');
+select * from t2;
+a
+1975
+1980
+2000
+2020
+select * from t2 where a='1980';
+a
+1980
+delete from t2 where a='1980';
+select * from t2;
+a
+1975
+2000
+2020
+delete from t2;
+255 inserts;
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 1
+select count(*) from t2;
+count(*)
+255
+select * from t2;
+a
+0000
+1902
+1903
+1904
+1905
+1906
+1907
+1908
+1909
+1910
+1911
+1912
+1913
+1914
+1915
+1916
+1917
+1918
+1919
+1920
+1921
+1922
+1923
+1924
+1925
+1926
+1927
+1928
+1929
+1930
+1931
+1932
+1933
+1934
+1935
+1936
+1937
+1938
+1939
+1940
+1941
+1942
+1943
+1944
+1945
+1946
+1947
+1948
+1949
+1950
+1951
+1952
+1953
+1954
+1955
+1956
+1957
+1958
+1959
+1960
+1961
+1962
+1963
+1964
+1965
+1966
+1967
+1968
+1969
+1970
+1971
+1972
+1973
+1974
+1975
+1976
+1977
+1978
+1979
+1980
+1981
+1982
+1983
+1984
+1985
+1986
+1987
+1988
+1989
+1990
+1991
+1992
+1993
+1994
+1995
+1996
+1997
+1998
+1999
+2000
+2001
+2002
+2003
+2004
+2005
+2006
+2007
+2008
+2009
+2010
+2011
+2012
+2013
+2014
+2015
+2016
+2017
+2018
+2019
+2020
+2021
+2022
+2023
+2024
+2025
+2026
+2027
+2028
+2029
+2030
+2031
+2032
+2033
+2034
+2035
+2036
+2037
+2038
+2039
+2040
+2041
+2042
+2043
+2044
+2045
+2046
+2047
+2048
+2049
+2050
+2051
+2052
+2053
+2054
+2055
+2056
+2057
+2058
+2059
+2060
+2061
+2062
+2063
+2064
+2065
+2066
+2067
+2068
+2069
+2070
+2071
+2072
+2073
+2074
+2075
+2076
+2077
+2078
+2079
+2080
+2081
+2082
+2083
+2084
+2085
+2086
+2087
+2088
+2089
+2090
+2091
+2092
+2093
+2094
+2095
+2096
+2097
+2098
+2099
+2100
+2101
+2102
+2103
+2104
+2105
+2106
+2107
+2108
+2109
+2110
+2111
+2112
+2113
+2114
+2115
+2116
+2117
+2118
+2119
+2120
+2121
+2122
+2123
+2124
+2125
+2126
+2127
+2128
+2129
+2130
+2131
+2132
+2133
+2134
+2135
+2136
+2137
+2138
+2139
+2140
+2141
+2142
+2143
+2144
+2145
+2146
+2147
+2148
+2149
+2150
+2151
+2152
+2153
+2154
+2155
+drop table t2;
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-29 23:07:40 +0000