--source include/have_partition.inc

let type=datetime;
--source include/type_hrtime.inc

#
# partitioning
#
eval CREATE TABLE t1 (
  taken $type(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;

eval CREATE TABLE t2 (
  taken $type(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);

--sorted_result
select table_name,partition_name,partition_method,partition_expression,partition_description,table_rows from information_schema.partitions where table_name in ('t1', 't2');

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;
alter table t1 modify b datetime, modify a datetime(6);
select * from t1;
drop table t1;

--echo #
--echo # MDEV-4651 Crash in my_decimal2decimal in a ORDER BY query
--echo #
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;
DROP TABLE t1;
SET @@time_zone=DEFAULT;


--echo #
--echo # Start of 10.4 tests
--echo #

--echo #
--echo # MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE()
--echo #

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;
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;
SELECT a, TRUNCATE(a,1) FROM t1;
SELECT a, TRUNCATE(a,2) FROM t1;
SELECT a, TRUNCATE(a,3) FROM t1;
SELECT a, TRUNCATE(a,4) FROM t1;
SELECT a, TRUNCATE(a,5) FROM t1;
SELECT a, TRUNCATE(a,6) FROM t1;
SELECT a, TRUNCATE(a,7) FROM t1;
SELECT a, TRUNCATE(a,-1) FROM t1;
SELECT a, TRUNCATE(a,-6) FROM t1;

SELECT a, ROUND(a) FROM t1;
SELECT a, ROUND(a,0) FROM t1;
SELECT a, ROUND(a,1) FROM t1;
SELECT a, ROUND(a,2) FROM t1;
SELECT a, ROUND(a,3) FROM t1;
SELECT a, ROUND(a,4) FROM t1;
SELECT a, ROUND(a,5) FROM t1;
SELECT a, ROUND(a,6) FROM t1;
SELECT a, ROUND(a,7) FROM t1;
SELECT a, ROUND(a,-1) FROM t1;
SELECT a, ROUND(a,-6) FROM t1;

DROP TABLE t1;

--echo #
--echo # MDEV-20984 Possibly wrong result or Assertion `args[0]->type_handler()->mysql_timestamp_type() == MYSQL_TIMESTAMP_DATETIME' failed in Item_func_round::date_op
--echo #

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;
DROP TABLE t1;


--echo #
--echo # MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE()
--echo #

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;
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');


DELIMITER $$;
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;
$$
DELIMITER ;$$

DELIMITER $$;
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;
$$
DELIMITER ;$$

SELECT a, FLOOR(a), FLOOR_SP(a), FLOOR(a)=FLOOR_SP(a) FROM t1 ORDER BY a;
SELECT a, CEILING(a), CEILING_SP(a), CEILING(a)=CEILING_SP(a) FROM t1 ORDER BY a;

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;
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;
DROP TABLE t1;
SET sql_mode=DEFAULT;

--echo #
--echo # End of 10.4 tests
--echo #