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

create table t1 (a time(4) not null, key(a));
insert into t1 values ('1:2:3.001'),('1:2:3'), ('-00:00:00.6'),('-00:00:00.7'),('-00:00:00.8'),('-00:00:00.9'),('-00:00:01.0'),('-00:00:01.1'),('-00:00:01.000000'),('-00:00:01.100001'),('-00:00:01.000002'),('-00:00:01.090000');
select * from t1 order by a;
select * from t1 order by a desc;
select min(a - interval 1 hour), max(a - interval 1 hour) from t1 where a < 0;
drop table t1;

select cast(1e-6 as time(6));

--echo #
--echo # End of 5.5 tests
--echo #

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

CREATE TABLE t1 (a1 TIME(6), a2 TIME(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 TIME(6));
INSERT INTO t1 VALUES
('-838:59:59.999999'),
('-837:59:59.999999'),
('-23:59:59.999999'),
('-00:59:59.999999'),
('-00:00:59.999999'),
('00:00:00.999999'),
('00:00:59.999999'),
('00:59:59.999999'),
('23:59:59.999999'),
('837:59:59.999999'),
('838: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;

SET time_zone=DEFAULT;


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

CREATE TABLE t1 AS SELECT
  FLOOR(TIME'00:00:00.999999'),
  CEILING(TIME'00:00:00.999999');
SHOW CREATE TABLE t1;
DROP TABLE t1;

CREATE TABLE t1 (a TIME(6));

INSERT INTO t1 VALUES ('838:59:59.999999');
INSERT INTO t1 VALUES ('838:59:59.99999');
INSERT INTO t1 VALUES ('838:59:59.9999');
INSERT INTO t1 VALUES ('838:59:59.999');
INSERT INTO t1 VALUES ('838:59:59.99');
INSERT INTO t1 VALUES ('838:59:59.9');
INSERT INTO t1 VALUES ('838:59:59.1');
INSERT INTO t1 VALUES ('838:59:59.0');

INSERT INTO t1 VALUES ('837:59:59.999999');
INSERT INTO t1 VALUES ('837:59:59.99999');
INSERT INTO t1 VALUES ('837:59:59.9999');
INSERT INTO t1 VALUES ('837:59:59.999');
INSERT INTO t1 VALUES ('837:59:59.99');
INSERT INTO t1 VALUES ('837:59:59.9');
INSERT INTO t1 VALUES ('837:59:59.1');
INSERT INTO t1 VALUES ('837:59:59.0');

INSERT INTO t1 VALUES ('23:59:59.999999');
INSERT INTO t1 VALUES ('23:59:59.99999');
INSERT INTO t1 VALUES ('23:59:59.9999');
INSERT INTO t1 VALUES ('23:59:59.999');
INSERT INTO t1 VALUES ('23:59:59.99');
INSERT INTO t1 VALUES ('23:59:59.9');
INSERT INTO t1 VALUES ('23:59:59.1');
INSERT INTO t1 VALUES ('23:59:59.0');

INSERT INTO t1 VALUES ('00:00:00.999999');
INSERT INTO t1 VALUES ('00:00:00.99999');
INSERT INTO t1 VALUES ('00:00:00.9999');
INSERT INTO t1 VALUES ('00:00:00.999');
INSERT INTO t1 VALUES ('00:00:00.99');
INSERT INTO t1 VALUES ('00:00:00.9');
INSERT INTO t1 VALUES ('00:00:00.1');
INSERT INTO t1 VALUES ('00:00:00.0');

INSERT INTO t1 VALUES ('-00:00:00.999999');
INSERT INTO t1 VALUES ('-00:00:00.99999');
INSERT INTO t1 VALUES ('-00:00:00.9999');
INSERT INTO t1 VALUES ('-00:00:00.999');
INSERT INTO t1 VALUES ('-00:00:00.99');
INSERT INTO t1 VALUES ('-00:00:00.9');
INSERT INTO t1 VALUES ('-00:00:00.1');
INSERT INTO t1 VALUES ('-00:00:00.0');

INSERT INTO t1 VALUES ('-23:59:59.999999');
INSERT INTO t1 VALUES ('-23:59:59.99999');
INSERT INTO t1 VALUES ('-23:59:59.9999');
INSERT INTO t1 VALUES ('-23:59:59.999');
INSERT INTO t1 VALUES ('-23:59:59.99');
INSERT INTO t1 VALUES ('-23:59:59.9');
INSERT INTO t1 VALUES ('-23:59:59.1');
INSERT INTO t1 VALUES ('-23:59:59.0');

INSERT INTO t1 VALUES ('-837:59:59.999999');
INSERT INTO t1 VALUES ('-837:59:59.99999');
INSERT INTO t1 VALUES ('-837:59:59.9999');
INSERT INTO t1 VALUES ('-837:59:59.999');
INSERT INTO t1 VALUES ('-837:59:59.99');
INSERT INTO t1 VALUES ('-837:59:59.9');
INSERT INTO t1 VALUES ('-837:59:59.1');
INSERT INTO t1 VALUES ('-837:59:59.0');

INSERT INTO t1 VALUES ('-838:59:59.999999');
INSERT INTO t1 VALUES ('-838:59:59.99999');
INSERT INTO t1 VALUES ('-838:59:59.9999');
INSERT INTO t1 VALUES ('-838:59:59.999');
INSERT INTO t1 VALUES ('-838:59:59.99');
INSERT INTO t1 VALUES ('-838:59:59.9');
INSERT INTO t1 VALUES ('-838:59:59.1');
INSERT INTO t1 VALUES ('-838:59:59.0');

DELIMITER $$;
CREATE FUNCTION FLOOR_SP(a TIME(6)) RETURNS TIME
BEGIN
  RETURN
    CASE
      WHEN TRUNCATE(a,0)=TIME'838:59:59' THEN a
      WHEN TRUNCATE(a,0)=TIME'-838:59:59' THEN a
      WHEN a=TRUNCATE(a,0) THEN a                    -- no fractional digits
      WHEN a<0 THEN TRUNCATE(a,0)-INTERVAL 1 SECOND  -- negative values
      WHEN a>0 THEN TRUNCATE(a,0)                    -- positive values
    END;
END;
$$
DELIMITER ;$$

DELIMITER $$;
CREATE FUNCTION CEILING_SP(a TIME(6)) RETURNS TIME
BEGIN
  RETURN
    CASE
      WHEN TRUNCATE(a,0)=TIME'838:59:59' THEN a
      WHEN TRUNCATE(a,0)=TIME'-838:59:59' THEN a
      WHEN a=TRUNCATE(a,0) THEN a                    -- no fractional digits
      WHEN a<0 THEN TRUNCATE(a,0)                    -- negative values
      WHEN a>0 THEN TRUNCATE(a,0)+INTERVAL 1 SECOND  -- positive values
    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;

--echo #
--echo # MDEV-29924 Assertion `(((nr) % (1LL << 24)) % (int) log_10_int[6 - dec]) == 0' failed in my_time_packed_to_binary on SELECT when using TIME field
--echo #
create table t1 (c decimal(3,1),d time(6));
insert into t1 values (null,0.1),(null,0.1), (0.1,0.2);
select c from t1 where c<all (select d from t1);
drop table t1;

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


--echo #
--echo # Start of 10.9 tests
--echo #

--echo #
--echo # MDEV-27712 Reduce the size of Lex_length_and_dec_st from 16 to 8
--echo #

--error ER_TOO_BIG_PRECISION
CREATE TABLE t1 (a TIME(4294967295));
--error ER_TOO_BIG_PRECISION
CREATE TABLE t1 (a TIME(4294967296));
--error ER_TOO_BIG_PRECISION
CREATE TABLE t1 (a TIME(999999999999999999999999999999));

--error ER_TOO_BIG_PRECISION
SELECT CAST(1 AS TIME(4294967295));
--error ER_TOO_BIG_PRECISION
SELECT CAST(1 AS TIME(4294967296));
--error ER_TOO_BIG_PRECISION
SELECT CAST(1 AS TIME(999999999999999999999999999999));

--echo #
--echo # Start of 10.9 tests
--echo #