summaryrefslogtreecommitdiff
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.result966
1 files changed, 483 insertions, 483 deletions
diff --git a/mysql-test/suite/parts/r/partition_datetime_innodb.result b/mysql-test/suite/parts/r/partition_datetime_innodb.result
index 8779bfeafa7..f00b7a3e478 100644
--- a/mysql-test/suite/parts/r/partition_datetime_innodb.result
+++ b/mysql-test/suite/parts/r/partition_datetime_innodb.result
@@ -145,19 +145,19 @@ t1 CREATE TABLE `t1` (
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
+1975-01-01
+2000-06-15
select * from t1 where a=19801014;
a
1980-10-14
delete from t1 where a=19801014;
select * from t1;
a
+2020-12-31
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;
@@ -174,8 +174,8 @@ select * from t2;
a
1975-01-01
1980-10-14
-2000-06-15
2020-12-31
+2000-06-15
select * from t2 where a='1980-10-14';
a
1980-10-14
@@ -183,8 +183,8 @@ delete from t2 where a='1980-10-14';
select * from t2;
a
1975-01-01
-2000-06-15
2020-12-31
+2000-06-15
delete from t2;
28 inserts;
select count(*) from t2;
@@ -192,90 +192,90 @@ 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-12
+1970-01-13
+1970-01-27
+1970-02-03
+1970-02-09
+1970-02-13
+1970-02-21
+1970-03-05
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-23
+1970-01-06
+1970-01-08
+1970-01-16
+1970-01-18
+1970-01-22
+1970-02-06
+1970-02-12
+1970-02-20
+1970-02-22
+1970-02-28
+1970-03-02
+1970-03-08
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-01-05
+1970-01-07
+1970-01-11
+1970-01-15
+1970-01-21
+1970-02-01
+1970-02-11
+1970-02-15
+1970-02-19
+1970-03-03
+1970-03-11
+1970-03-13
+1970-03-17
+1970-03-19
1970-03-25
-1970-03-26
1970-03-27
+1970-01-02
+1970-01-10
+1970-01-20
+1970-01-26
+1970-02-08
+1970-02-18
+1970-02-24
+1970-02-26
+1970-03-10
+1970-03-14
+1970-03-18
+1970-03-26
1970-03-28
+1970-01-01
+1970-01-03
+1970-01-09
+1970-01-17
+1970-01-19
+1970-01-23
+1970-01-25
+1970-02-05
+1970-02-07
+1970-02-17
+1970-02-23
+1970-02-25
+1970-02-27
+1970-03-01
+1970-03-21
drop table t2;
create table t3 (a date not null, primary key(a)) engine='InnoDB'
partition by range (month(a)) subpartition by key (a)
@@ -304,18 +304,18 @@ count(*)
12
select * from t3;
a
-1970-01-01
1970-02-01
+1970-01-01
1970-03-01
1970-04-01
1970-05-01
1970-06-01
+1970-09-01
1970-07-01
1970-08-01
-1970-09-01
+1970-12-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)
@@ -344,18 +344,18 @@ count(*)
12
select * from t4;
a
-1970-01-01
1970-02-01
+1970-01-01
1970-03-01
1970-04-01
1970-05-01
1970-06-01
+1970-09-01
1970-07-01
1970-08-01
-1970-09-01
+1970-12-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) (
@@ -378,18 +378,18 @@ 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
+12:10:30
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
+12:10:30
drop table t1;
create table t2 (a time not null, primary key(a)) engine='InnoDB'
partition by key (a) partitions 12;
@@ -404,19 +404,19 @@ 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
+00:01:01
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
+00:01:01
delete from t2;
59 inserts;
select count(*) from t2;
@@ -424,65 +424,65 @@ count(*)
59
select * from t2;
a
-00:01:01
-00:01:02
-00:01:03
-00:01:04
+00:01:15
+00:01:23
+00:01:39
+00:01:47
+00:01:59
+00:01:16
+00:01:24
+00:01:40
+00:01:48
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:21
+00:01:37
+00:01:45
+00:01:57
00:01:14
-00:01:15
-00:01:16
-00:01:17
-00:01:18
+00:01:22
+00:01:38
+00:01:46
+00:01:58
+00:01:03
+00:01:11
00:01:19
+00:01:35
+00:01:43
+00:01:55
+00:01:04
+00:01:12
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:36
+00:01:44
+00:01:56
+00:01:01
+00:01:09
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:53
+00:01:02
+00:01:10
+00:01:18
+00:01:34
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:54
+00:01:07
+00:01:27
+00:01:31
00:01:51
+00:01:08
+00:01:28
+00:01:32
00:01:52
-00:01:53
-00:01:54
-00:01:55
-00:01:56
-00:01:57
-00:01:58
-00:01:59
+00:01:17
+00:01:25
+00:01:49
+00:01:06
+00:01:26
+00:01:30
+00:01:50
drop table t2;
create table t3 (a time not null, primary key(a)) engine='InnoDB'
partition by range (second(a)) subpartition by key (a)
@@ -509,67 +509,67 @@ SUBPARTITIONS 3
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
+select a, second(a), if(second(a)<16,1,if(second(a)<31,2,if(second(a)<45,3,4))) from t3;
+a second(a) if(second(a)<16,1,if(second(a)<31,2,if(second(a)<45,3,4)))
+10:00:01 1 1
+10:00:06 6 1
+10:00:07 7 1
+10:00:14 14 1
+10:00:15 15 1
+10:00:02 2 1
+10:00:03 3 1
+10:00:08 8 1
+10:00:09 9 1
+10:00:04 4 1
+10:00:05 5 1
+10:00:10 10 1
+10:00:11 11 1
+10:00:12 12 1
+10:00:13 13 1
+10:00:20 20 2
+10:00:21 21 2
+10:00:26 26 2
+10:00:27 27 2
+10:00:16 16 2
+10:00:17 17 2
+10:00:22 22 2
+10:00:23 23 2
+10:00:28 28 2
+10:00:29 29 2
+10:00:18 18 2
+10:00:19 19 2
+10:00:24 24 2
+10:00:25 25 2
+10:00:30 30 2
+10:00:32 32 3
+10:00:33 33 3
+10:00:35 35 3
+10:00:40 40 3
+10:00:41 41 3
+10:00:34 34 3
+10:00:36 36 3
+10:00:37 37 3
+10:00:42 42 3
+10:00:43 43 3
+10:00:31 31 3
+10:00:38 38 3
+10:00:39 39 3
+10:00:44 44 3
+10:00:45 45 4
+10:00:46 46 4
+10:00:48 48 4
+10:00:49 49 4
+10:00:54 54 4
+10:00:55 55 4
+10:00:50 50 4
+10:00:51 51 4
+10:00:56 56 4
+10:00:57 57 4
+10:00:59 59 4
+10:00:47 47 4
+10:00:52 52 4
+10:00:53 53 4
+10:00:58 58 4
drop table t3;
create table t4 (a time not null, primary key(a)) engine='InnoDB'
partition by list (second(a)) subpartition by key (a)
@@ -599,64 +599,64 @@ count(*)
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:14
+10:00:15
+10:00:02
+10:00:03
10:00:08
10:00:09
+10:00:04
+10:00:05
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:16
+10:00:17
+10:00:22
+10:00:23
10:00:28
10:00:29
+10:00:18
+10:00:19
+10:00:24
+10:00:25
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:34
+10:00:36
+10:00:37
10:00:42
10:00:43
+10:00:31
+10:00:38
+10:00:39
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:50
+10:00:51
10:00:56
10:00:57
-10:00:58
10:00:59
+10:00:47
+10:00:52
+10:00:53
+10:00:58
drop table t4;
create table t1 (a datetime not null, primary key(a)) engine='InnoDB'
partition by key (a) (
@@ -679,9 +679,9 @@ insert into t1 values ('1975-01-01 21:21:21'), ('2020-12-31 12:10:30'), ('1980-1
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
+1980-10-14 03:03:00
select * from t1 where a=19801014030300;
a
1980-10-14 03:03:00
@@ -705,19 +705,19 @@ 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
+2000-06-15 14:15:16
+1980-10-14 13:14:15
+1975-01-01 00:01:01
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
+2000-06-15 14:15:16
+1975-01-01 00:01:01
delete from t2;
59 inserts;
select count(*) from t2;
@@ -725,65 +725,65 @@ 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:33
+1970-01-01 00:00:37
+1970-01-01 00:00:51
+1970-01-01 00:00:59
+1970-01-01 00:00:04
+1970-01-01 00:00:10
+1970-01-01 00:00:14
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:48
+1970-01-01 00:00:52
+1970-01-01 00:00:56
+1970-01-01 00:00:03
+1970-01-01 00:00:07
+1970-01-01 00:00:17
+1970-01-01 00:00:21
+1970-01-01 00:00:27
+1970-01-01 00:00:31
+1970-01-01 00:00:35
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:08
+1970-01-01 00:00:12
+1970-01-01 00:00:18
+1970-01-01 00:00:22
+1970-01-01 00:00:32
+1970-01-01 00:00:36
+1970-01-01 00:00:42
1970-01-01 00:00:46
+1970-01-01 00:00:01
+1970-01-01 00:00:05
+1970-01-01 00:00:11
+1970-01-01 00:00:15
+1970-01-01 00:00:25
+1970-01-01 00:00:29
+1970-01-01 00:00:43
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:02
+1970-01-01 00:00:06
+1970-01-01 00:00:16
+1970-01-01 00:00:20
+1970-01-01 00:00:26
+1970-01-01 00:00:30
+1970-01-01 00:00:40
+1970-01-01 00:00:44
+1970-01-01 00:00:50
+1970-01-01 00:00:54
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)
@@ -812,15 +812,15 @@ count(*)
12
select * from t3;
a
-1970-01-01 00:00:00
1970-02-01 00:00:00
+1970-01-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-07-01 00:00:00
1970-10-01 00:00:00
1970-11-01 00:00:00
1970-12-01 00:00:00
@@ -852,15 +852,15 @@ count(*)
12
select * from t4;
a
-1970-01-01 00:00:00
1970-02-01 00:00:00
+1970-01-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-07-01 00:00:00
1970-10-01 00:00:00
1970-11-01 00:00:00
1970-12-01 00:00:00
@@ -885,19 +885,19 @@ t1 CREATE TABLE `t1` (
insert into t1 values ('1975'), (2020), ('1980'), ('2000');
select * from t1;
a
-1975
1980
2000
2020
+1975
select * from t1 where a=1980;
a
1980
delete from t1 where a=1980;
select * from t1;
a
-1975
2000
2020
+1975
drop table t1;
create table t2 (a year not null, primary key(a)) engine='InnoDB'
partition by key (a) partitions 12;
@@ -912,19 +912,19 @@ PARTITIONS 12
insert into t2 values ('1975'), ('2020'), ('1980'), ('2000');
select * from t2;
a
+2020
1975
1980
2000
-2020
select * from t2 where a='1980';
a
1980
delete from t2 where a='1980';
select * from t2;
a
+2020
1975
2000
-2020
delete from t2;
255 inserts;
Warnings:
@@ -934,259 +934,259 @@ 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
+1933
+1945
+1957
+1969
+1981
+1993
+2001
+2013
+2029
+2041
+2053
+2065
+2077
+2089
+2100
+2101
+2102
+2103
+2114
+2132
+2133
+2134
+2135
+2146
+1904
+1916
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
+2008
+2020
+2024
+2036
+2048
+2060
+2072
+2084
+1907
+1919
+1931
+1943
+1955
+1967
+1979
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
+2027
+2039
+2051
+2063
+2075
+2087
+2097
+2099
+2110
+2111
+2112
+2113
+2123
+2129
+2131
+2142
+2143
+2144
+2145
+2147
+1902
+1914
+1926
+1938
+1950
+1962
+1974
+1986
+2006
2018
-2019
-2020
+2034
+2046
+2058
+2070
+2082
+1905
+1917
+1929
+1941
+1953
+1965
+1977
+1989
+1997
+2009
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
+2120
+2121
+2124
+2125
+2126
+2127
+2152
+2153
+0000
+1912
+1924
+1936
+1948
+1960
+1972
+1984
+1996
+2004
+2016
+2032
+2044
+2056
+2068
+2080
+2116
+2148
+1903
+1915
+1927
+1939
+1951
+1963
+1975
+1987
+2007
+2019
+2023
+2035
+2047
+2059
+2071
+2083
+2155
+1910
+1922
+1934
+1946
+1958
+1970
+1982
+1994
+2002
+2014
+2030
+2042
+2054
+2066
+2078
+2090
+1913
+1925
+1937
+1949
+1961
+1973
+1985
+2005
+2017
+2033
+2045
+2057
+2069
+2081
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
+1908
+1920
+1944
+1956
+1968
+1980
+1992
+2000
+2012
+2028
+2040
+2052
+2064
+2076
+2088
+2096
+2098
+2108
+2128
+2130
+2140
+1911
+1923
+1935
+1947
+1959
+1971
+1983
+1995
+2003
+2015
+2031
+2043
+2055
+2067
+2079
+2091
+2115
+1906
+1918
+1930
+1942
+1954
+1966
+1978
+1990
+1998
+2010
+2022
+2026
+2038
+2050
+2062
+2074
+2086
+2109
+2122
+2141
2154
-2155
drop table t2;
View Lorry Controller Status