SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30');
drop table if exists t1, t2, t3;
create table t1 (a datetime(7));
ERROR 42000: Too big precision specified for 'a'. Maximum is 6
create table t1 (a datetime(3), key(a));
insert t1 values ('2010-12-11 00:20:03.1234');
insert t1 values ('2010-12-11 15:47:11.1234');
insert t1 values (20101211010203.45678);
insert t1 values (20101211030405.789e0);
insert ignore t1 values (99991231235959e1);
Warnings:
Warning	1265	Data truncated for column 'a' at row 1
select * from t1;
a
0000-00-00 00:00:00.000
2010-12-11 00:20:03.123
2010-12-11 01:02:03.456
2010-12-11 03:04:05.789
2010-12-11 15:47:11.123
select cast(a AS double(30,6)) from t1;
cast(a AS double(30,6))
0.000000
20101211002003.120000
20101211010203.457031
20101211030405.790000
20101211154711.120000
select a DIV 1 from t1;
a DIV 1
0
20101211002003
20101211010203
20101211030405
20101211154711
select group_concat(distinct a) from t1;
group_concat(distinct a)
0000-00-00 00:00:00.000,2010-12-11 00:20:03.123,2010-12-11 01:02:03.456,2010-12-11 03:04:05.789,2010-12-11 15:47:11.123
alter table t1 engine=innodb;
select * from t1 order by a;
a
0000-00-00 00:00:00.000
2010-12-11 00:20:03.123
2010-12-11 01:02:03.456
2010-12-11 03:04:05.789
2010-12-11 15:47:11.123
select * from t1 order by a+0;
a
0000-00-00 00:00:00.000
2010-12-11 00:20:03.123
2010-12-11 01:02:03.456
2010-12-11 03:04:05.789
2010-12-11 15:47:11.123
drop table t1;
create table t1 (a datetime(4)) engine=innodb;
insert t1 values ('2010-12-11 01:02:03.456789');
select * from t1;
a
2010-12-11 01:02:03.4567
select extract(microsecond from a + interval 100 microsecond) from t1 where a>'2010-11-12 01:02:03.456';
extract(microsecond from a + interval 100 microsecond)
456800
select a from t1 where a>'2010-11-12 01:02:03.456' group by a;
a
2010-12-11 01:02:03.4567
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` datetime(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
show columns from t1;
Field	Type	Null	Key	Default	Extra
a	datetime(4)	YES		NULL	
select table_name, column_name, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, datetime_precision, character_set_name, collation_name, column_type, column_key, extra from information_schema.columns where table_name='t1';
table_name	t1
column_name	a
column_default	NULL
is_nullable	YES
data_type	datetime
character_maximum_length	NULL
character_octet_length	NULL
numeric_precision	NULL
numeric_scale	NULL
datetime_precision	4
character_set_name	NULL
collation_name	NULL
column_type	datetime(4)
column_key	
extra	
select a, a+interval 9876543 microsecond from t1;
a	a+interval 9876543 microsecond
2010-12-11 01:02:03.4567	2010-12-11 01:02:13.333243
update t1 set a=a+interval 9876543 microsecond;
select * from t1;
a
2010-12-11 01:02:13.3332
select a, a + interval 2 year from t1;
a	a + interval 2 year
2010-12-11 01:02:13.3332	2012-12-11 01:02:13.3332
insert ignore t1 select a + interval 2 year from t1;
select * from t1;
a
2010-12-11 01:02:13.3332
2012-12-11 01:02:13.3332
delete from t1 where a < 20110101;
select * from t1;
a
2012-12-11 01:02:13.3332
create table t2 select * from t1;
create table t3 like t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` datetime(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `a` datetime(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t2, t3;
insert t1 values ('2010-12-13 14:15:16.222222');
select a, a+0, a-1, a*1, a/2 from t1;
a	a+0	a-1	a*1	a/2
2012-12-11 01:02:13.3332	20121211010213.3332	20121211010212.3332	20121211010213.3332	10060605505106.66660000
2010-12-13 14:15:16.2222	20101213141516.2222	20101213141515.2222	20101213141516.2222	10050606570758.11110000
select max(a), min(a), sum(a), avg(a) from t1;
max(a)	min(a)	sum(a)	avg(a)
2012-12-11 01:02:13.3332	2010-12-13 14:15:16.2222	40222424151729.5554	20111212075864.77770000
create table t2 select a, a+0, a-1, a*1, a/2 from t1;
create table t3 select max(a), min(a), sum(a), avg(a) from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` datetime(4) DEFAULT NULL,
  `a+0` decimal(19,4) DEFAULT NULL,
  `a-1` decimal(19,4) DEFAULT NULL,
  `a*1` decimal(19,4) DEFAULT NULL,
  `a/2` decimal(22,8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `max(a)` datetime(4) DEFAULT NULL,
  `min(a)` datetime(4) DEFAULT NULL,
  `sum(a)` decimal(40,4) DEFAULT NULL,
  `avg(a)` decimal(22,8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1, t2, t3;
create table t1 (f0_datetime datetime(0), f1_datetime datetime(1), f2_datetime datetime(2), f3_datetime datetime(3), f4_datetime datetime(4), f5_datetime datetime(5), f6_datetime datetime(6));
insert t1 values ( '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432');
select * from t1;
f0_datetime	2010-11-12 11:14:17
f1_datetime	2010-11-12 11:14:17.7
f2_datetime	2010-11-12 11:14:17.76
f3_datetime	2010-11-12 11:14:17.765
f4_datetime	2010-11-12 11:14:17.7654
f5_datetime	2010-11-12 11:14:17.76543
f6_datetime	2010-11-12 11:14:17.765432
select cast(f0_datetime as time(4)) time4_f0_datetime, cast(f1_datetime as datetime(3)) datetime3_f1_datetime, cast(f2_datetime as date) date_f2_datetime, cast(f4_datetime as double) double_f3_datetime, cast(f4_datetime as decimal(40,5)) decimal5_f4_datetime, cast(f5_datetime as signed) bigint_f5_datetime, cast(f6_datetime as char(255)) varchar_f6_datetime from t1;
time4_f0_datetime	11:14:17.0000
datetime3_f1_datetime	2010-11-12 11:14:17.700
date_f2_datetime	2010-11-12
double_f3_datetime	20101112111417.766
decimal5_f4_datetime	20101112111417.76540
bigint_f5_datetime	20101112111417
varchar_f6_datetime	2010-11-12 11:14:17.765432
create table t2 (time4_f0_datetime time(4), datetime3_f1_datetime datetime(3), date_f2_datetime date, double_f3_datetime double, decimal5_f4_datetime decimal(40,5), bigint_f5_datetime bigint, varchar_f6_datetime varchar(255));
insert t2 select * from t1;
Warnings:
Level	Note
Code	1265
Message	Data truncated for column 'time4_f0_datetime' at row 1
Level	Note
Code	1265
Message	Data truncated for column 'date_f2_datetime' at row 1
select * from t2;
time4_f0_datetime	11:14:17.0000
datetime3_f1_datetime	2010-11-12 11:14:17.700
date_f2_datetime	2010-11-12
double_f3_datetime	20101112111417.766
decimal5_f4_datetime	20101112111417.76540
bigint_f5_datetime	20101112111417
varchar_f6_datetime	2010-11-12 11:14:17.765432
alter table t1 change f0_datetime time4_f0_datetime time(4), change f1_datetime datetime3_f1_datetime datetime(3), change f2_datetime date_f2_datetime date, change f3_datetime double_f3_datetime double, change f4_datetime decimal5_f4_datetime decimal(40,5), change f5_datetime bigint_f5_datetime bigint, change f6_datetime varchar_f6_datetime varchar(255);
Warnings:
Level	Note
Code	1265
Message	Data truncated for column 'time4_f0_datetime' at row 1
Level	Note
Code	1265
Message	Data truncated for column 'date_f2_datetime' at row 1
select * from t1;
time4_f0_datetime	11:14:17.0000
datetime3_f1_datetime	2010-11-12 11:14:17.700
date_f2_datetime	2010-11-12
double_f3_datetime	20101112111417.766
decimal5_f4_datetime	20101112111417.76540
bigint_f5_datetime	20101112111417
varchar_f6_datetime	2010-11-12 11:14:17.765432
alter table t1 modify time4_f0_datetime datetime(0), modify datetime3_f1_datetime datetime(1), modify date_f2_datetime datetime(2), modify double_f3_datetime datetime(3), modify decimal5_f4_datetime datetime(4), modify bigint_f5_datetime datetime(5), modify varchar_f6_datetime datetime(6);
select * from t1;
time4_f0_datetime	2001-02-03 11:14:17
datetime3_f1_datetime	2010-11-12 11:14:17.7
date_f2_datetime	2010-11-12 00:00:00.00
double_f3_datetime	2010-11-12 11:14:17.766
decimal5_f4_datetime	2010-11-12 11:14:17.7654
bigint_f5_datetime	2010-11-12 11:14:17.00000
varchar_f6_datetime	2010-11-12 11:14:17.765432
delete from t1;
insert t1 select * from t2;
select * from t1;
time4_f0_datetime	2001-02-03 11:14:17
datetime3_f1_datetime	2010-11-12 11:14:17.7
date_f2_datetime	2010-11-12 00:00:00.00
double_f3_datetime	2010-11-12 11:14:17.765
decimal5_f4_datetime	2010-11-12 11:14:17.7654
bigint_f5_datetime	2010-11-12 11:14:17.00000
varchar_f6_datetime	2010-11-12 11:14:17.765432
drop table t1, t2;
create table t1 (a datetime(6), b datetime(6));
create procedure foo(x datetime, y datetime(4)) insert into t1 values (x, y);
call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123');
select * from t1;
a	b
2010-02-03 04:05:06.000000	2010-02-03 04:05:06.789100
create procedure bar(a int, c datetime(5))
begin
declare b datetime(4);
set b = c + interval a microsecond;
insert t1 values (b, c + interval a microsecond);
end|
call bar(1111111, '2011-01-02 3:4:5.123456');
select * from t1;
a	b
2010-02-03 04:05:06.000000	2010-02-03 04:05:06.789100
2011-01-02 03:04:06.234500	2011-01-02 03:04:06.234561
drop procedure foo;
drop procedure bar;
create function xyz(s char(20)) returns datetime(4)
return addtime('2010-10-10 10:10:10.101010', s);
select xyz('1:1:1.010101');
xyz('1:1:1.010101')
2010-10-10 11:11:11.1111
drop function xyz;
create view v1 as select * from t1 group by a,b;
select * from v1;
a	b
2010-02-03 04:05:06.000000	2010-02-03 04:05:06.789100
2011-01-02 03:04:06.234500	2011-01-02 03:04:06.234561
show columns from v1;
Field	Type	Null	Key	Default	Extra
a	datetime(6)	YES		NULL	
b	datetime(6)	YES		NULL	
create table t2 select * from v1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` datetime(6) DEFAULT NULL,
  `b` datetime(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
select * from t2;
a	b
2010-02-03 04:05:06.000000	2010-02-03 04:05:06.789100
2011-01-02 03:04:06.234500	2011-01-02 03:04:06.234561
drop view v1;
drop table t1, t2;
SET timestamp=DEFAULT;
CREATE TABLE t1 (
taken datetime(5) NOT NULL DEFAULT '0000-00-00 00:00:00',
id int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (id,taken),
KEY taken (taken)
)
PARTITION BY RANGE (to_days(taken))
(
PARTITION p01 VALUES LESS THAN (732920),
PARTITION p02 VALUES LESS THAN (732950),
PARTITION p03 VALUES LESS THAN MAXVALUE);
INSERT INTO t1 VALUES
('2006-09-27 21:50:01.123456',0),
('2006-09-27 21:50:01.123456',1),
('2006-09-27 21:50:01.123456',2),
('2006-09-28 21:50:01.123456',3),
('2006-09-29 21:50:01.123456',4),
('2006-09-29 21:50:01.123456',5),
('2006-09-30 21:50:01.123456',6),
('2006-10-01 21:50:01.123456',7),
('2006-10-02 21:50:01.123456',8),
('2006-10-02 21:50:01.123456',9);
SELECT id,to_days(taken) FROM t1 order by 2;
id	to_days(taken)
0	732946
1	732946
2	732946
3	732947
5	732948
4	732948
6	732949
7	732950
8	732951
9	732951
CREATE TABLE t2 (
taken datetime(5) NOT NULL DEFAULT '0000-00-00 00:00:00',
id int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (id,taken),
KEY taken (taken)
)
PARTITION BY RANGE (extract(microsecond from taken))
(
PARTITION p01 VALUES LESS THAN (123000),
PARTITION p02 VALUES LESS THAN (500000),
PARTITION p03 VALUES LESS THAN MAXVALUE);
INSERT INTO t2 VALUES
('2006-09-27 21:50:01',0),
('2006-09-27 21:50:01.1',1),
('2006-09-27 21:50:01.12',2),
('2006-09-28 21:50:01.123',3),
('2006-09-29 21:50:01.1234',4),
('2006-09-29 21:50:01.12345',5),
('2006-09-30 21:50:01.123456',6),
('2006-10-01 21:50:01.56',7),
('2006-10-02 21:50:01.567',8),
('2006-10-02 21:50:01.5678',9);
select table_name,partition_name,partition_method,partition_expression,partition_description,table_rows from information_schema.partitions where table_name in ('t1', 't2');
table_name	partition_name	partition_method	partition_expression	partition_description	table_rows
t1	p01	RANGE	to_days(`taken`)	732920	0
t1	p02	RANGE	to_days(`taken`)	732950	7
t1	p03	RANGE	to_days(`taken`)	MAXVALUE	3
t2	p01	RANGE	extract(microsecond from `taken`)	123000	3
t2	p02	RANGE	extract(microsecond from `taken`)	500000	4
t2	p03	RANGE	extract(microsecond from `taken`)	MAXVALUE	3
drop table t1, t2;
create table t1 (a datetime, b datetime(6));
insert t1 values ('2010-01-02 03:04:05.678912', '2010-01-02 03:04:05.678912');
update t1 set b=a;
select * from t1;
a	b
2010-01-02 03:04:05	2010-01-02 03:04:05.000000
alter table t1 modify b datetime, modify a datetime(6);
select * from t1;
a	b
2010-01-02 03:04:05.000000	2010-01-02 03:04:05
drop table t1;
#
# MDEV-4651 Crash in my_decimal2decimal in a ORDER BY query
#
SET @@time_zone='+00:00';
CREATE TABLE t1 (a DATETIME(4) NOT NULL);
INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2000-00-00 00:00:00');
SELECT UNIX_TIMESTAMP(a) FROM t1 ORDER BY 1;
UNIX_TIMESTAMP(a)
NULL
978307200.0000
DROP TABLE t1;
SET @@time_zone=DEFAULT;
#
# Start of 10.4 tests
#
#
# MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE()
#
CREATE TABLE t1 (a1 DATETIME(6), a2 DATETIME(6) NOT NULL);
CREATE TABLE t2 AS SELECT
ROUND(a1) AS r1,
ROUND(a2) AS r2,
TRUNCATE(a1,0) AS t1,
TRUNCATE(a2,0) AS t2
FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `r1` datetime DEFAULT NULL,
  `r2` datetime DEFAULT NULL,
  `t1` datetime DEFAULT NULL,
  `t2` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t2;
DROP TABLE t1;
CREATE TABLE t1 (a DATETIME(6));
INSERT INTO t1 VALUES
('0000-00-00 00:00:00.999999'),
('0000-00-00 23:59:59.999999'),
('0000-00-01 00:00:00.999999'),
('0000-00-01 23:59:59.999999'),
('0000-00-31 23:59:59.999999'),
('0000-01-01 00:00:00.999999'),
('0000-01-01 23:59:59.999999'),
('0000-01-31 23:59:59.999999'),
('0000-02-28 23:59:59.999999'),
('0000-12-31 23:59:59.999999'),
('0001-01-01 00:00:00.999999'),
('0001-02-28 23:59:59.999999'),
('0001-12-31 23:59:59.999999'),
('0004-02-28 23:59:59.999999'),
('0004-02-29 23:59:59.999999'),
('2000-02-29 23:59:59.999999'),
('2000-12-31 23:59:59.999999'),
('9999-12-31 23:59:59.999999');
SELECT a, TRUNCATE(a,0) FROM t1;
a	TRUNCATE(a,0)
0000-00-00 00:00:00.999999	0000-00-00 00:00:00
0000-00-00 23:59:59.999999	0000-00-00 23:59:59
0000-00-01 00:00:00.999999	0000-00-01 00:00:00
0000-00-01 23:59:59.999999	0000-00-01 23:59:59
0000-00-31 23:59:59.999999	0000-00-31 23:59:59
0000-01-01 00:00:00.999999	0000-01-01 00:00:00
0000-01-01 23:59:59.999999	0000-01-01 23:59:59
0000-01-31 23:59:59.999999	0000-01-31 23:59:59
0000-02-28 23:59:59.999999	0000-02-28 23:59:59
0000-12-31 23:59:59.999999	0000-12-31 23:59:59
0001-01-01 00:00:00.999999	0001-01-01 00:00:00
0001-02-28 23:59:59.999999	0001-02-28 23:59:59
0001-12-31 23:59:59.999999	0001-12-31 23:59:59
0004-02-28 23:59:59.999999	0004-02-28 23:59:59
0004-02-29 23:59:59.999999	0004-02-29 23:59:59
2000-02-29 23:59:59.999999	2000-02-29 23:59:59
2000-12-31 23:59:59.999999	2000-12-31 23:59:59
9999-12-31 23:59:59.999999	9999-12-31 23:59:59
SELECT a, TRUNCATE(a,1) FROM t1;
a	TRUNCATE(a,1)
0000-00-00 00:00:00.999999	0000-00-00 00:00:00.9
0000-00-00 23:59:59.999999	0000-00-00 23:59:59.9
0000-00-01 00:00:00.999999	0000-00-01 00:00:00.9
0000-00-01 23:59:59.999999	0000-00-01 23:59:59.9
0000-00-31 23:59:59.999999	0000-00-31 23:59:59.9
0000-01-01 00:00:00.999999	0000-01-01 00:00:00.9
0000-01-01 23:59:59.999999	0000-01-01 23:59:59.9
0000-01-31 23:59:59.999999	0000-01-31 23:59:59.9
0000-02-28 23:59:59.999999	0000-02-28 23:59:59.9
0000-12-31 23:59:59.999999	0000-12-31 23:59:59.9
0001-01-01 00:00:00.999999	0001-01-01 00:00:00.9
0001-02-28 23:59:59.999999	0001-02-28 23:59:59.9
0001-12-31 23:59:59.999999	0001-12-31 23:59:59.9
0004-02-28 23:59:59.999999	0004-02-28 23:59:59.9
0004-02-29 23:59:59.999999	0004-02-29 23:59:59.9
2000-02-29 23:59:59.999999	2000-02-29 23:59:59.9
2000-12-31 23:59:59.999999	2000-12-31 23:59:59.9
9999-12-31 23:59:59.999999	9999-12-31 23:59:59.9
SELECT a, TRUNCATE(a,2) FROM t1;
a	TRUNCATE(a,2)
0000-00-00 00:00:00.999999	0000-00-00 00:00:00.99
0000-00-00 23:59:59.999999	0000-00-00 23:59:59.99
0000-00-01 00:00:00.999999	0000-00-01 00:00:00.99
0000-00-01 23:59:59.999999	0000-00-01 23:59:59.99
0000-00-31 23:59:59.999999	0000-00-31 23:59:59.99
0000-01-01 00:00:00.999999	0000-01-01 00:00:00.99
0000-01-01 23:59:59.999999	0000-01-01 23:59:59.99
0000-01-31 23:59:59.999999	0000-01-31 23:59:59.99
0000-02-28 23:59:59.999999	0000-02-28 23:59:59.99
0000-12-31 23:59:59.999999	0000-12-31 23:59:59.99
0001-01-01 00:00:00.999999	0001-01-01 00:00:00.99
0001-02-28 23:59:59.999999	0001-02-28 23:59:59.99
0001-12-31 23:59:59.999999	0001-12-31 23:59:59.99
0004-02-28 23:59:59.999999	0004-02-28 23:59:59.99
0004-02-29 23:59:59.999999	0004-02-29 23:59:59.99
2000-02-29 23:59:59.999999	2000-02-29 23:59:59.99
2000-12-31 23:59:59.999999	2000-12-31 23:59:59.99
9999-12-31 23:59:59.999999	9999-12-31 23:59:59.99
SELECT a, TRUNCATE(a,3) FROM t1;
a	TRUNCATE(a,3)
0000-00-00 00:00:00.999999	0000-00-00 00:00:00.999
0000-00-00 23:59:59.999999	0000-00-00 23:59:59.999
0000-00-01 00:00:00.999999	0000-00-01 00:00:00.999
0000-00-01 23:59:59.999999	0000-00-01 23:59:59.999
0000-00-31 23:59:59.999999	0000-00-31 23:59:59.999
0000-01-01 00:00:00.999999	0000-01-01 00:00:00.999
0000-01-01 23:59:59.999999	0000-01-01 23:59:59.999
0000-01-31 23:59:59.999999	0000-01-31 23:59:59.999
0000-02-28 23:59:59.999999	0000-02-28 23:59:59.999
0000-12-31 23:59:59.999999	0000-12-31 23:59:59.999
0001-01-01 00:00:00.999999	0001-01-01 00:00:00.999
0001-02-28 23:59:59.999999	0001-02-28 23:59:59.999
0001-12-31 23:59:59.999999	0001-12-31 23:59:59.999
0004-02-28 23:59:59.999999	0004-02-28 23:59:59.999
0004-02-29 23:59:59.999999	0004-02-29 23:59:59.999
2000-02-29 23:59:59.999999	2000-02-29 23:59:59.999
2000-12-31 23:59:59.999999	2000-12-31 23:59:59.999
9999-12-31 23:59:59.999999	9999-12-31 23:59:59.999
SELECT a, TRUNCATE(a,4) FROM t1;
a	TRUNCATE(a,4)
0000-00-00 00:00:00.999999	0000-00-00 00:00:00.9999
0000-00-00 23:59:59.999999	0000-00-00 23:59:59.9999
0000-00-01 00:00:00.999999	0000-00-01 00:00:00.9999
0000-00-01 23:59:59.999999	0000-00-01 23:59:59.9999
0000-00-31 23:59:59.999999	0000-00-31 23:59:59.9999
0000-01-01 00:00:00.999999	0000-01-01 00:00:00.9999
0000-01-01 23:59:59.999999	0000-01-01 23:59:59.9999
0000-01-31 23:59:59.999999	0000-01-31 23:59:59.9999
0000-02-28 23:59:59.999999	0000-02-28 23:59:59.9999
0000-12-31 23:59:59.999999	0000-12-31 23:59:59.9999
0001-01-01 00:00:00.999999	0001-01-01 00:00:00.9999
0001-02-28 23:59:59.999999	0001-02-28 23:59:59.9999
0001-12-31 23:59:59.999999	0001-12-31 23:59:59.9999
0004-02-28 23:59:59.999999	0004-02-28 23:59:59.9999
0004-02-29 23:59:59.999999	0004-02-29 23:59:59.9999
2000-02-29 23:59:59.999999	2000-02-29 23:59:59.9999
2000-12-31 23:59:59.999999	2000-12-31 23:59:59.9999
9999-12-31 23:59:59.999999	9999-12-31 23:59:59.9999
SELECT a, TRUNCATE(a,5) FROM t1;
a	TRUNCATE(a,5)
0000-00-00 00:00:00.999999	0000-00-00 00:00:00.99999
0000-00-00 23:59:59.999999	0000-00-00 23:59:59.99999
0000-00-01 00:00:00.999999	0000-00-01 00:00:00.99999
0000-00-01 23:59:59.999999	0000-00-01 23:59:59.99999
0000-00-31 23:59:59.999999	0000-00-31 23:59:59.99999
0000-01-01 00:00:00.999999	0000-01-01 00:00:00.99999
0000-01-01 23:59:59.999999	0000-01-01 23:59:59.99999
0000-01-31 23:59:59.999999	0000-01-31 23:59:59.99999
0000-02-28 23:59:59.999999	0000-02-28 23:59:59.99999
0000-12-31 23:59:59.999999	0000-12-31 23:59:59.99999
0001-01-01 00:00:00.999999	0001-01-01 00:00:00.99999
0001-02-28 23:59:59.999999	0001-02-28 23:59:59.99999
0001-12-31 23:59:59.999999	0001-12-31 23:59:59.99999
0004-02-28 23:59:59.999999	0004-02-28 23:59:59.99999
0004-02-29 23:59:59.999999	0004-02-29 23:59:59.99999
2000-02-29 23:59:59.999999	2000-02-29 23:59:59.99999
2000-12-31 23:59:59.999999	2000-12-31 23:59:59.99999
9999-12-31 23:59:59.999999	9999-12-31 23:59:59.99999
SELECT a, TRUNCATE(a,6) FROM t1;
a	TRUNCATE(a,6)
0000-00-00 00:00:00.999999	0000-00-00 00:00:00.999999
0000-00-00 23:59:59.999999	0000-00-00 23:59:59.999999
0000-00-01 00:00:00.999999	0000-00-01 00:00:00.999999
0000-00-01 23:59:59.999999	0000-00-01 23:59:59.999999
0000-00-31 23:59:59.999999	0000-00-31 23:59:59.999999
0000-01-01 00:00:00.999999	0000-01-01 00:00:00.999999
0000-01-01 23:59:59.999999	0000-01-01 23:59:59.999999
0000-01-31 23:59:59.999999	0000-01-31 23:59:59.999999
0000-02-28 23:59:59.999999	0000-02-28 23:59:59.999999
0000-12-31 23:59:59.999999	0000-12-31 23:59:59.999999
0001-01-01 00:00:00.999999	0001-01-01 00:00:00.999999
0001-02-28 23:59:59.999999	0001-02-28 23:59:59.999999
0001-12-31 23:59:59.999999	0001-12-31 23:59:59.999999
0004-02-28 23:59:59.999999	0004-02-28 23:59:59.999999
0004-02-29 23:59:59.999999	0004-02-29 23:59:59.999999
2000-02-29 23:59:59.999999	2000-02-29 23:59:59.999999
2000-12-31 23:59:59.999999	2000-12-31 23:59:59.999999
9999-12-31 23:59:59.999999	9999-12-31 23:59:59.999999
SELECT a, TRUNCATE(a,7) FROM t1;
a	TRUNCATE(a,7)
0000-00-00 00:00:00.999999	0000-00-00 00:00:00.999999
0000-00-00 23:59:59.999999	0000-00-00 23:59:59.999999
0000-00-01 00:00:00.999999	0000-00-01 00:00:00.999999
0000-00-01 23:59:59.999999	0000-00-01 23:59:59.999999
0000-00-31 23:59:59.999999	0000-00-31 23:59:59.999999
0000-01-01 00:00:00.999999	0000-01-01 00:00:00.999999
0000-01-01 23:59:59.999999	0000-01-01 23:59:59.999999
0000-01-31 23:59:59.999999	0000-01-31 23:59:59.999999
0000-02-28 23:59:59.999999	0000-02-28 23:59:59.999999
0000-12-31 23:59:59.999999	0000-12-31 23:59:59.999999
0001-01-01 00:00:00.999999	0001-01-01 00:00:00.999999
0001-02-28 23:59:59.999999	0001-02-28 23:59:59.999999
0001-12-31 23:59:59.999999	0001-12-31 23:59:59.999999
0004-02-28 23:59:59.999999	0004-02-28 23:59:59.999999
0004-02-29 23:59:59.999999	0004-02-29 23:59:59.999999
2000-02-29 23:59:59.999999	2000-02-29 23:59:59.999999
2000-12-31 23:59:59.999999	2000-12-31 23:59:59.999999
9999-12-31 23:59:59.999999	9999-12-31 23:59:59.999999
SELECT a, TRUNCATE(a,-1) FROM t1;
a	TRUNCATE(a,-1)
0000-00-00 00:00:00.999999	0000-00-00 00:00:00
0000-00-00 23:59:59.999999	0000-00-00 23:59:59
0000-00-01 00:00:00.999999	0000-00-01 00:00:00
0000-00-01 23:59:59.999999	0000-00-01 23:59:59
0000-00-31 23:59:59.999999	0000-00-31 23:59:59
0000-01-01 00:00:00.999999	0000-01-01 00:00:00
0000-01-01 23:59:59.999999	0000-01-01 23:59:59
0000-01-31 23:59:59.999999	0000-01-31 23:59:59
0000-02-28 23:59:59.999999	0000-02-28 23:59:59
0000-12-31 23:59:59.999999	0000-12-31 23:59:59
0001-01-01 00:00:00.999999	0001-01-01 00:00:00
0001-02-28 23:59:59.999999	0001-02-28 23:59:59
0001-12-31 23:59:59.999999	0001-12-31 23:59:59
0004-02-28 23:59:59.999999	0004-02-28 23:59:59
0004-02-29 23:59:59.999999	0004-02-29 23:59:59
2000-02-29 23:59:59.999999	2000-02-29 23:59:59
2000-12-31 23:59:59.999999	2000-12-31 23:59:59
9999-12-31 23:59:59.999999	9999-12-31 23:59:59
SELECT a, TRUNCATE(a,-6) FROM t1;
a	TRUNCATE(a,-6)
0000-00-00 00:00:00.999999	0000-00-00 00:00:00
0000-00-00 23:59:59.999999	0000-00-00 23:59:59
0000-00-01 00:00:00.999999	0000-00-01 00:00:00
0000-00-01 23:59:59.999999	0000-00-01 23:59:59
0000-00-31 23:59:59.999999	0000-00-31 23:59:59
0000-01-01 00:00:00.999999	0000-01-01 00:00:00
0000-01-01 23:59:59.999999	0000-01-01 23:59:59
0000-01-31 23:59:59.999999	0000-01-31 23:59:59
0000-02-28 23:59:59.999999	0000-02-28 23:59:59
0000-12-31 23:59:59.999999	0000-12-31 23:59:59
0001-01-01 00:00:00.999999	0001-01-01 00:00:00
0001-02-28 23:59:59.999999	0001-02-28 23:59:59
0001-12-31 23:59:59.999999	0001-12-31 23:59:59
0004-02-28 23:59:59.999999	0004-02-28 23:59:59
0004-02-29 23:59:59.999999	0004-02-29 23:59:59
2000-02-29 23:59:59.999999	2000-02-29 23:59:59
2000-12-31 23:59:59.999999	2000-12-31 23:59:59
9999-12-31 23:59:59.999999	9999-12-31 23:59:59
SELECT a, ROUND(a) FROM t1;
a	ROUND(a)
0000-00-00 00:00:00.999999	0000-00-00 00:00:01
0000-00-00 23:59:59.999999	NULL
0000-00-01 00:00:00.999999	0000-00-01 00:00:01
0000-00-01 23:59:59.999999	NULL
0000-00-31 23:59:59.999999	NULL
0000-01-01 00:00:00.999999	0000-01-01 00:00:01
0000-01-01 23:59:59.999999	NULL
0000-01-31 23:59:59.999999	NULL
0000-02-28 23:59:59.999999	NULL
0000-12-31 23:59:59.999999	NULL
0001-01-01 00:00:00.999999	0001-01-01 00:00:01
0001-02-28 23:59:59.999999	0001-03-01 00:00:00
0001-12-31 23:59:59.999999	0002-01-01 00:00:00
0004-02-28 23:59:59.999999	0004-02-29 00:00:00
0004-02-29 23:59:59.999999	0004-03-01 00:00:00
2000-02-29 23:59:59.999999	2000-03-01 00:00:00
2000-12-31 23:59:59.999999	2001-01-01 00:00:00
9999-12-31 23:59:59.999999	9999-12-31 23:59:59
Warnings:
Warning	1411	Incorrect date value: '0000-00-00' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-02-28' for function round(datetime)
Warning	1411	Incorrect date value: '0000-12-31' for function round(datetime)
SELECT a, ROUND(a,0) FROM t1;
a	ROUND(a,0)
0000-00-00 00:00:00.999999	0000-00-00 00:00:01
0000-00-00 23:59:59.999999	NULL
0000-00-01 00:00:00.999999	0000-00-01 00:00:01
0000-00-01 23:59:59.999999	NULL
0000-00-31 23:59:59.999999	NULL
0000-01-01 00:00:00.999999	0000-01-01 00:00:01
0000-01-01 23:59:59.999999	NULL
0000-01-31 23:59:59.999999	NULL
0000-02-28 23:59:59.999999	NULL
0000-12-31 23:59:59.999999	NULL
0001-01-01 00:00:00.999999	0001-01-01 00:00:01
0001-02-28 23:59:59.999999	0001-03-01 00:00:00
0001-12-31 23:59:59.999999	0002-01-01 00:00:00
0004-02-28 23:59:59.999999	0004-02-29 00:00:00
0004-02-29 23:59:59.999999	0004-03-01 00:00:00
2000-02-29 23:59:59.999999	2000-03-01 00:00:00
2000-12-31 23:59:59.999999	2001-01-01 00:00:00
9999-12-31 23:59:59.999999	9999-12-31 23:59:59
Warnings:
Warning	1411	Incorrect date value: '0000-00-00' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-02-28' for function round(datetime)
Warning	1411	Incorrect date value: '0000-12-31' for function round(datetime)
SELECT a, ROUND(a,1) FROM t1;
a	ROUND(a,1)
0000-00-00 00:00:00.999999	0000-00-00 00:00:01.0
0000-00-00 23:59:59.999999	NULL
0000-00-01 00:00:00.999999	0000-00-01 00:00:01.0
0000-00-01 23:59:59.999999	NULL
0000-00-31 23:59:59.999999	NULL
0000-01-01 00:00:00.999999	0000-01-01 00:00:01.0
0000-01-01 23:59:59.999999	NULL
0000-01-31 23:59:59.999999	NULL
0000-02-28 23:59:59.999999	NULL
0000-12-31 23:59:59.999999	NULL
0001-01-01 00:00:00.999999	0001-01-01 00:00:01.0
0001-02-28 23:59:59.999999	0001-03-01 00:00:00.0
0001-12-31 23:59:59.999999	0002-01-01 00:00:00.0
0004-02-28 23:59:59.999999	0004-02-29 00:00:00.0
0004-02-29 23:59:59.999999	0004-03-01 00:00:00.0
2000-02-29 23:59:59.999999	2000-03-01 00:00:00.0
2000-12-31 23:59:59.999999	2001-01-01 00:00:00.0
9999-12-31 23:59:59.999999	9999-12-31 23:59:59.9
Warnings:
Warning	1411	Incorrect date value: '0000-00-00' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-02-28' for function round(datetime)
Warning	1411	Incorrect date value: '0000-12-31' for function round(datetime)
SELECT a, ROUND(a,2) FROM t1;
a	ROUND(a,2)
0000-00-00 00:00:00.999999	0000-00-00 00:00:01.00
0000-00-00 23:59:59.999999	NULL
0000-00-01 00:00:00.999999	0000-00-01 00:00:01.00
0000-00-01 23:59:59.999999	NULL
0000-00-31 23:59:59.999999	NULL
0000-01-01 00:00:00.999999	0000-01-01 00:00:01.00
0000-01-01 23:59:59.999999	NULL
0000-01-31 23:59:59.999999	NULL
0000-02-28 23:59:59.999999	NULL
0000-12-31 23:59:59.999999	NULL
0001-01-01 00:00:00.999999	0001-01-01 00:00:01.00
0001-02-28 23:59:59.999999	0001-03-01 00:00:00.00
0001-12-31 23:59:59.999999	0002-01-01 00:00:00.00
0004-02-28 23:59:59.999999	0004-02-29 00:00:00.00
0004-02-29 23:59:59.999999	0004-03-01 00:00:00.00
2000-02-29 23:59:59.999999	2000-03-01 00:00:00.00
2000-12-31 23:59:59.999999	2001-01-01 00:00:00.00
9999-12-31 23:59:59.999999	9999-12-31 23:59:59.99
Warnings:
Warning	1411	Incorrect date value: '0000-00-00' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-02-28' for function round(datetime)
Warning	1411	Incorrect date value: '0000-12-31' for function round(datetime)
SELECT a, ROUND(a,3) FROM t1;
a	ROUND(a,3)
0000-00-00 00:00:00.999999	0000-00-00 00:00:01.000
0000-00-00 23:59:59.999999	NULL
0000-00-01 00:00:00.999999	0000-00-01 00:00:01.000
0000-00-01 23:59:59.999999	NULL
0000-00-31 23:59:59.999999	NULL
0000-01-01 00:00:00.999999	0000-01-01 00:00:01.000
0000-01-01 23:59:59.999999	NULL
0000-01-31 23:59:59.999999	NULL
0000-02-28 23:59:59.999999	NULL
0000-12-31 23:59:59.999999	NULL
0001-01-01 00:00:00.999999	0001-01-01 00:00:01.000
0001-02-28 23:59:59.999999	0001-03-01 00:00:00.000
0001-12-31 23:59:59.999999	0002-01-01 00:00:00.000
0004-02-28 23:59:59.999999	0004-02-29 00:00:00.000
0004-02-29 23:59:59.999999	0004-03-01 00:00:00.000
2000-02-29 23:59:59.999999	2000-03-01 00:00:00.000
2000-12-31 23:59:59.999999	2001-01-01 00:00:00.000
9999-12-31 23:59:59.999999	9999-12-31 23:59:59.999
Warnings:
Warning	1411	Incorrect date value: '0000-00-00' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-02-28' for function round(datetime)
Warning	1411	Incorrect date value: '0000-12-31' for function round(datetime)
SELECT a, ROUND(a,4) FROM t1;
a	ROUND(a,4)
0000-00-00 00:00:00.999999	0000-00-00 00:00:01.0000
0000-00-00 23:59:59.999999	NULL
0000-00-01 00:00:00.999999	0000-00-01 00:00:01.0000
0000-00-01 23:59:59.999999	NULL
0000-00-31 23:59:59.999999	NULL
0000-01-01 00:00:00.999999	0000-01-01 00:00:01.0000
0000-01-01 23:59:59.999999	NULL
0000-01-31 23:59:59.999999	NULL
0000-02-28 23:59:59.999999	NULL
0000-12-31 23:59:59.999999	NULL
0001-01-01 00:00:00.999999	0001-01-01 00:00:01.0000
0001-02-28 23:59:59.999999	0001-03-01 00:00:00.0000
0001-12-31 23:59:59.999999	0002-01-01 00:00:00.0000
0004-02-28 23:59:59.999999	0004-02-29 00:00:00.0000
0004-02-29 23:59:59.999999	0004-03-01 00:00:00.0000
2000-02-29 23:59:59.999999	2000-03-01 00:00:00.0000
2000-12-31 23:59:59.999999	2001-01-01 00:00:00.0000
9999-12-31 23:59:59.999999	9999-12-31 23:59:59.9999
Warnings:
Warning	1411	Incorrect date value: '0000-00-00' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-02-28' for function round(datetime)
Warning	1411	Incorrect date value: '0000-12-31' for function round(datetime)
SELECT a, ROUND(a,5) FROM t1;
a	ROUND(a,5)
0000-00-00 00:00:00.999999	0000-00-00 00:00:01.00000
0000-00-00 23:59:59.999999	NULL
0000-00-01 00:00:00.999999	0000-00-01 00:00:01.00000
0000-00-01 23:59:59.999999	NULL
0000-00-31 23:59:59.999999	NULL
0000-01-01 00:00:00.999999	0000-01-01 00:00:01.00000
0000-01-01 23:59:59.999999	NULL
0000-01-31 23:59:59.999999	NULL
0000-02-28 23:59:59.999999	NULL
0000-12-31 23:59:59.999999	NULL
0001-01-01 00:00:00.999999	0001-01-01 00:00:01.00000
0001-02-28 23:59:59.999999	0001-03-01 00:00:00.00000
0001-12-31 23:59:59.999999	0002-01-01 00:00:00.00000
0004-02-28 23:59:59.999999	0004-02-29 00:00:00.00000
0004-02-29 23:59:59.999999	0004-03-01 00:00:00.00000
2000-02-29 23:59:59.999999	2000-03-01 00:00:00.00000
2000-12-31 23:59:59.999999	2001-01-01 00:00:00.00000
9999-12-31 23:59:59.999999	9999-12-31 23:59:59.99999
Warnings:
Warning	1411	Incorrect date value: '0000-00-00' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-02-28' for function round(datetime)
Warning	1411	Incorrect date value: '0000-12-31' for function round(datetime)
SELECT a, ROUND(a,6) FROM t1;
a	ROUND(a,6)
0000-00-00 00:00:00.999999	0000-00-00 00:00:00.999999
0000-00-00 23:59:59.999999	0000-00-00 23:59:59.999999
0000-00-01 00:00:00.999999	0000-00-01 00:00:00.999999
0000-00-01 23:59:59.999999	0000-00-01 23:59:59.999999
0000-00-31 23:59:59.999999	0000-00-31 23:59:59.999999
0000-01-01 00:00:00.999999	0000-01-01 00:00:00.999999
0000-01-01 23:59:59.999999	0000-01-01 23:59:59.999999
0000-01-31 23:59:59.999999	0000-01-31 23:59:59.999999
0000-02-28 23:59:59.999999	0000-02-28 23:59:59.999999
0000-12-31 23:59:59.999999	0000-12-31 23:59:59.999999
0001-01-01 00:00:00.999999	0001-01-01 00:00:00.999999
0001-02-28 23:59:59.999999	0001-02-28 23:59:59.999999
0001-12-31 23:59:59.999999	0001-12-31 23:59:59.999999
0004-02-28 23:59:59.999999	0004-02-28 23:59:59.999999
0004-02-29 23:59:59.999999	0004-02-29 23:59:59.999999
2000-02-29 23:59:59.999999	2000-02-29 23:59:59.999999
2000-12-31 23:59:59.999999	2000-12-31 23:59:59.999999
9999-12-31 23:59:59.999999	9999-12-31 23:59:59.999999
SELECT a, ROUND(a,7) FROM t1;
a	ROUND(a,7)
0000-00-00 00:00:00.999999	0000-00-00 00:00:00.999999
0000-00-00 23:59:59.999999	0000-00-00 23:59:59.999999
0000-00-01 00:00:00.999999	0000-00-01 00:00:00.999999
0000-00-01 23:59:59.999999	0000-00-01 23:59:59.999999
0000-00-31 23:59:59.999999	0000-00-31 23:59:59.999999
0000-01-01 00:00:00.999999	0000-01-01 00:00:00.999999
0000-01-01 23:59:59.999999	0000-01-01 23:59:59.999999
0000-01-31 23:59:59.999999	0000-01-31 23:59:59.999999
0000-02-28 23:59:59.999999	0000-02-28 23:59:59.999999
0000-12-31 23:59:59.999999	0000-12-31 23:59:59.999999
0001-01-01 00:00:00.999999	0001-01-01 00:00:00.999999
0001-02-28 23:59:59.999999	0001-02-28 23:59:59.999999
0001-12-31 23:59:59.999999	0001-12-31 23:59:59.999999
0004-02-28 23:59:59.999999	0004-02-28 23:59:59.999999
0004-02-29 23:59:59.999999	0004-02-29 23:59:59.999999
2000-02-29 23:59:59.999999	2000-02-29 23:59:59.999999
2000-12-31 23:59:59.999999	2000-12-31 23:59:59.999999
9999-12-31 23:59:59.999999	9999-12-31 23:59:59.999999
SELECT a, ROUND(a,-1) FROM t1;
a	ROUND(a,-1)
0000-00-00 00:00:00.999999	0000-00-00 00:00:01
0000-00-00 23:59:59.999999	NULL
0000-00-01 00:00:00.999999	0000-00-01 00:00:01
0000-00-01 23:59:59.999999	NULL
0000-00-31 23:59:59.999999	NULL
0000-01-01 00:00:00.999999	0000-01-01 00:00:01
0000-01-01 23:59:59.999999	NULL
0000-01-31 23:59:59.999999	NULL
0000-02-28 23:59:59.999999	NULL
0000-12-31 23:59:59.999999	NULL
0001-01-01 00:00:00.999999	0001-01-01 00:00:01
0001-02-28 23:59:59.999999	0001-03-01 00:00:00
0001-12-31 23:59:59.999999	0002-01-01 00:00:00
0004-02-28 23:59:59.999999	0004-02-29 00:00:00
0004-02-29 23:59:59.999999	0004-03-01 00:00:00
2000-02-29 23:59:59.999999	2000-03-01 00:00:00
2000-12-31 23:59:59.999999	2001-01-01 00:00:00
9999-12-31 23:59:59.999999	9999-12-31 23:59:59
Warnings:
Warning	1411	Incorrect date value: '0000-00-00' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-02-28' for function round(datetime)
Warning	1411	Incorrect date value: '0000-12-31' for function round(datetime)
SELECT a, ROUND(a,-6) FROM t1;
a	ROUND(a,-6)
0000-00-00 00:00:00.999999	0000-00-00 00:00:01
0000-00-00 23:59:59.999999	NULL
0000-00-01 00:00:00.999999	0000-00-01 00:00:01
0000-00-01 23:59:59.999999	NULL
0000-00-31 23:59:59.999999	NULL
0000-01-01 00:00:00.999999	0000-01-01 00:00:01
0000-01-01 23:59:59.999999	NULL
0000-01-31 23:59:59.999999	NULL
0000-02-28 23:59:59.999999	NULL
0000-12-31 23:59:59.999999	NULL
0001-01-01 00:00:00.999999	0001-01-01 00:00:01
0001-02-28 23:59:59.999999	0001-03-01 00:00:00
0001-12-31 23:59:59.999999	0002-01-01 00:00:00
0004-02-28 23:59:59.999999	0004-02-29 00:00:00
0004-02-29 23:59:59.999999	0004-03-01 00:00:00
2000-02-29 23:59:59.999999	2000-03-01 00:00:00
2000-12-31 23:59:59.999999	2001-01-01 00:00:00
9999-12-31 23:59:59.999999	9999-12-31 23:59:59
Warnings:
Warning	1411	Incorrect date value: '0000-00-00' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-31' for function round(datetime)
Warning	1411	Incorrect date value: '0000-02-28' for function round(datetime)
Warning	1411	Incorrect date value: '0000-12-31' for function round(datetime)
DROP TABLE t1;
#
# MDEV-20984 Possibly wrong result or Assertion `args[0]->type_handler()->mysql_timestamp_type() == MYSQL_TIMESTAMP_DATETIME' failed in Item_func_round::date_op
#
CREATE TABLE t1 (a DATETIME);
INSERT INTO t1 VALUES ('1979-01-03 10:33:32'),('2012-12-12 12:12:12');
SELECT ROUND(a) AS f FROM t1 GROUP BY a WITH ROLLUP;
f
1979-01-03 10:33:32
2012-12-12 12:12:12
NULL
DROP TABLE t1;
#
# MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE()
#
CREATE TABLE t1 AS SELECT
FLOOR(TIMESTAMP'2001-01-01 00:00:00.999999'),
CEILING(TIMESTAMP'2001-01-01 00:00:00.999999');
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `FLOOR(TIMESTAMP'2001-01-01 00:00:00.999999')` datetime DEFAULT NULL,
  `CEILING(TIMESTAMP'2001-01-01 00:00:00.999999')` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (a DATETIME(6));
INSERT INTO t1 VALUES ('9999-12-31 23:59:59.999999');
INSERT INTO t1 VALUES ('9999-12-31 23:59:59.99999');
INSERT INTO t1 VALUES ('9999-12-31 23:59:59.9999');
INSERT INTO t1 VALUES ('9999-12-31 23:59:59.999');
INSERT INTO t1 VALUES ('9999-12-31 23:59:59.99');
INSERT INTO t1 VALUES ('9999-12-31 23:59:59.9');
INSERT INTO t1 VALUES ('9999-12-31 23:59:59.1');
INSERT INTO t1 VALUES ('9999-12-31 23:59:59.0');
INSERT INTO t1 VALUES ('9999-12-30 23:59:59.999999');
INSERT INTO t1 VALUES ('9999-12-30 23:59:59.99999');
INSERT INTO t1 VALUES ('9999-12-30 23:59:59.9999');
INSERT INTO t1 VALUES ('9999-12-30 23:59:59.999');
INSERT INTO t1 VALUES ('9999-12-30 23:59:59.99');
INSERT INTO t1 VALUES ('9999-12-30 23:59:59.9');
INSERT INTO t1 VALUES ('9999-12-30 23:59:59.1');
INSERT INTO t1 VALUES ('9999-12-30 23:59:59.0');
INSERT INTO t1 VALUES ('0999-12-31 23:59:59.999999');
INSERT INTO t1 VALUES ('0999-12-31 23:59:59.99999');
INSERT INTO t1 VALUES ('0999-12-31 23:59:59.9999');
INSERT INTO t1 VALUES ('0999-12-31 23:59:59.999');
INSERT INTO t1 VALUES ('0999-12-31 23:59:59.99');
INSERT INTO t1 VALUES ('0999-12-31 23:59:59.9');
INSERT INTO t1 VALUES ('0999-12-31 23:59:59.1');
INSERT INTO t1 VALUES ('0999-12-31 23:59:59.0');
INSERT INTO t1 VALUES ('0001-12-31 23:59:59.999999');
INSERT INTO t1 VALUES ('0001-12-31 23:59:59.99999');
INSERT INTO t1 VALUES ('0001-12-31 23:59:59.9999');
INSERT INTO t1 VALUES ('0001-12-31 23:59:59.999');
INSERT INTO t1 VALUES ('0001-12-31 23:59:59.99');
INSERT INTO t1 VALUES ('0001-12-31 23:59:59.9');
INSERT INTO t1 VALUES ('0001-12-31 23:59:59.1');
INSERT INTO t1 VALUES ('0001-12-31 23:59:59.0');
CREATE FUNCTION FLOOR_SP(a DATETIME(6)) RETURNS DATETIME
BEGIN
RETURN
CASE
WHEN EXTRACT(MICROSECOND FROM a)=0 THEN a
ELSE TRUNCATE(a,0)
END;
END;
$$
CREATE FUNCTION CEILING_SP(a DATETIME(6)) RETURNS DATETIME
BEGIN
RETURN
CASE
WHEN TRUNCATE(a,0)=TIMESTAMP'9999-12-31 23:59:59' THEN a
WHEN EXTRACT(MICROSECOND FROM a)=0 THEN a
ELSE TRUNCATE(a,0)+INTERVAL 1 SECOND
END;
END;
$$
SELECT a, FLOOR(a), FLOOR_SP(a), FLOOR(a)=FLOOR_SP(a) FROM t1 ORDER BY a;
a	FLOOR(a)	FLOOR_SP(a)	FLOOR(a)=FLOOR_SP(a)
0001-12-31 23:59:59.000000	0001-12-31 23:59:59	0001-12-31 23:59:59	1
0001-12-31 23:59:59.100000	0001-12-31 23:59:59	0001-12-31 23:59:59	1
0001-12-31 23:59:59.900000	0001-12-31 23:59:59	0001-12-31 23:59:59	1
0001-12-31 23:59:59.990000	0001-12-31 23:59:59	0001-12-31 23:59:59	1
0001-12-31 23:59:59.999000	0001-12-31 23:59:59	0001-12-31 23:59:59	1
0001-12-31 23:59:59.999900	0001-12-31 23:59:59	0001-12-31 23:59:59	1
0001-12-31 23:59:59.999990	0001-12-31 23:59:59	0001-12-31 23:59:59	1
0001-12-31 23:59:59.999999	0001-12-31 23:59:59	0001-12-31 23:59:59	1
0999-12-31 23:59:59.000000	0999-12-31 23:59:59	0999-12-31 23:59:59	1
0999-12-31 23:59:59.100000	0999-12-31 23:59:59	0999-12-31 23:59:59	1
0999-12-31 23:59:59.900000	0999-12-31 23:59:59	0999-12-31 23:59:59	1
0999-12-31 23:59:59.990000	0999-12-31 23:59:59	0999-12-31 23:59:59	1
0999-12-31 23:59:59.999000	0999-12-31 23:59:59	0999-12-31 23:59:59	1
0999-12-31 23:59:59.999900	0999-12-31 23:59:59	0999-12-31 23:59:59	1
0999-12-31 23:59:59.999990	0999-12-31 23:59:59	0999-12-31 23:59:59	1
0999-12-31 23:59:59.999999	0999-12-31 23:59:59	0999-12-31 23:59:59	1
9999-12-30 23:59:59.000000	9999-12-30 23:59:59	9999-12-30 23:59:59	1
9999-12-30 23:59:59.100000	9999-12-30 23:59:59	9999-12-30 23:59:59	1
9999-12-30 23:59:59.900000	9999-12-30 23:59:59	9999-12-30 23:59:59	1
9999-12-30 23:59:59.990000	9999-12-30 23:59:59	9999-12-30 23:59:59	1
9999-12-30 23:59:59.999000	9999-12-30 23:59:59	9999-12-30 23:59:59	1
9999-12-30 23:59:59.999900	9999-12-30 23:59:59	9999-12-30 23:59:59	1
9999-12-30 23:59:59.999990	9999-12-30 23:59:59	9999-12-30 23:59:59	1
9999-12-30 23:59:59.999999	9999-12-30 23:59:59	9999-12-30 23:59:59	1
9999-12-31 23:59:59.000000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
9999-12-31 23:59:59.100000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
9999-12-31 23:59:59.900000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
9999-12-31 23:59:59.990000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
9999-12-31 23:59:59.999000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
9999-12-31 23:59:59.999900	9999-12-31 23:59:59	9999-12-31 23:59:59	1
9999-12-31 23:59:59.999990	9999-12-31 23:59:59	9999-12-31 23:59:59	1
9999-12-31 23:59:59.999999	9999-12-31 23:59:59	9999-12-31 23:59:59	1
SELECT a, CEILING(a), CEILING_SP(a), CEILING(a)=CEILING_SP(a) FROM t1 ORDER BY a;
a	CEILING(a)	CEILING_SP(a)	CEILING(a)=CEILING_SP(a)
0001-12-31 23:59:59.000000	0001-12-31 23:59:59	0001-12-31 23:59:59	1
0001-12-31 23:59:59.100000	0002-01-01 00:00:00	0002-01-01 00:00:00	1
0001-12-31 23:59:59.900000	0002-01-01 00:00:00	0002-01-01 00:00:00	1
0001-12-31 23:59:59.990000	0002-01-01 00:00:00	0002-01-01 00:00:00	1
0001-12-31 23:59:59.999000	0002-01-01 00:00:00	0002-01-01 00:00:00	1
0001-12-31 23:59:59.999900	0002-01-01 00:00:00	0002-01-01 00:00:00	1
0001-12-31 23:59:59.999990	0002-01-01 00:00:00	0002-01-01 00:00:00	1
0001-12-31 23:59:59.999999	0002-01-01 00:00:00	0002-01-01 00:00:00	1
0999-12-31 23:59:59.000000	0999-12-31 23:59:59	0999-12-31 23:59:59	1
0999-12-31 23:59:59.100000	1000-01-01 00:00:00	1000-01-01 00:00:00	1
0999-12-31 23:59:59.900000	1000-01-01 00:00:00	1000-01-01 00:00:00	1
0999-12-31 23:59:59.990000	1000-01-01 00:00:00	1000-01-01 00:00:00	1
0999-12-31 23:59:59.999000	1000-01-01 00:00:00	1000-01-01 00:00:00	1
0999-12-31 23:59:59.999900	1000-01-01 00:00:00	1000-01-01 00:00:00	1
0999-12-31 23:59:59.999990	1000-01-01 00:00:00	1000-01-01 00:00:00	1
0999-12-31 23:59:59.999999	1000-01-01 00:00:00	1000-01-01 00:00:00	1
9999-12-30 23:59:59.000000	9999-12-30 23:59:59	9999-12-30 23:59:59	1
9999-12-30 23:59:59.100000	9999-12-31 00:00:00	9999-12-31 00:00:00	1
9999-12-30 23:59:59.900000	9999-12-31 00:00:00	9999-12-31 00:00:00	1
9999-12-30 23:59:59.990000	9999-12-31 00:00:00	9999-12-31 00:00:00	1
9999-12-30 23:59:59.999000	9999-12-31 00:00:00	9999-12-31 00:00:00	1
9999-12-30 23:59:59.999900	9999-12-31 00:00:00	9999-12-31 00:00:00	1
9999-12-30 23:59:59.999990	9999-12-31 00:00:00	9999-12-31 00:00:00	1
9999-12-30 23:59:59.999999	9999-12-31 00:00:00	9999-12-31 00:00:00	1
9999-12-31 23:59:59.000000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
9999-12-31 23:59:59.100000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
9999-12-31 23:59:59.900000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
9999-12-31 23:59:59.990000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
9999-12-31 23:59:59.999000	9999-12-31 23:59:59	9999-12-31 23:59:59	1
9999-12-31 23:59:59.999900	9999-12-31 23:59:59	9999-12-31 23:59:59	1
9999-12-31 23:59:59.999990	9999-12-31 23:59:59	9999-12-31 23:59:59	1
9999-12-31 23:59:59.999999	9999-12-31 23:59:59	9999-12-31 23:59:59	1
DROP FUNCTION FLOOR_SP;
DROP FUNCTION CEILING_SP;
DROP TABLE t1;
CREATE TABLE t1 (a DATETIME(6));
INSERT INTO t1 VALUES ('0000-00-00 23:59:59.999999');
INSERT INTO t1 VALUES ('0000-00-01 23:59:59.999999');
INSERT INTO t1 VALUES ('0000-01-01 23:59:59.999999');
INSERT INTO t1 VALUES ('0001-00-00 23:59:59.999999');
SELECT a, FLOOR(a), CEILING(a) FROM t1;
a	FLOOR(a)	CEILING(a)
0000-00-00 23:59:59.999999	0000-00-00 23:59:59	NULL
0000-00-01 23:59:59.999999	0000-00-01 23:59:59	NULL
0000-01-01 23:59:59.999999	0000-01-01 23:59:59	NULL
0001-00-00 23:59:59.999999	0001-00-00 23:59:59	NULL
Warnings:
Warning	1411	Incorrect date value: '0000-00-00' for function round(datetime)
Warning	1411	Incorrect date value: '0000-00-01' for function round(datetime)
Warning	1411	Incorrect date value: '0000-01-01' for function round(datetime)
Warning	1411	Incorrect date value: '0001-00-00' for function round(datetime)
DROP TABLE t1;
SET sql_mode=ALLOW_INVALID_DATES;
CREATE TABLE t1 (a DATETIME(6));
INSERT INTO t1 VALUES ('2001-02-28 23:59:59.999999');
INSERT INTO t1 VALUES ('2001-02-29 23:59:59.999999');
SELECT a, FLOOR(a), CEILING(a) FROM t1;
a	FLOOR(a)	CEILING(a)
2001-02-28 23:59:59.999999	2001-02-28 23:59:59	2001-03-01 00:00:00
2001-02-29 23:59:59.999999	2001-02-29 23:59:59	NULL
Warnings:
Warning	1411	Incorrect date value: '2001-02-29' for function round(datetime)
DROP TABLE t1;
SET sql_mode=DEFAULT;
#
# End of 10.4 tests
#