# Test sql_mode time_truncate_fractional with strict mode.
SET sql_mode='time_truncate_fractional,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

--echo #
--echo # Testing time_truncate_fractional from VARCHAR and TIME(6) to TIME(N)
--echo #
CREATE TABLE t1 (a VARCHAR(32),
  t6 TIME(6), t5 TIME(5), t4 TIME(4),
  t3 TIME(3), t2 TIME(2), t1 TIME(1),
  t0 TIME);
INSERT INTO t1 (a) VALUES ('10:10:10.9999999');
INSERT INTO t1 (a) VALUES ('10:10:10.9999994');
--query_vertical SELECT * FROM t1;
ALTER TABLE t1 MODIFY a TIME(6);
UPDATE t1 SET t0=a, t1=a, t2=a, t3=a, t4=a, t5=a, t6=a;
--query_vertical SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing time_truncate_fractional when altering TIME(N) to a smaller size
--echo #
CREATE TABLE t1 (a TIME(6));
INSERT INTO t1 VALUES ('10:10:10.999999');
ALTER TABLE t1 MODIFY a TIME(5);
SELECT * FROM t1;
DROP TABLE t1;
CREATE TABLE t1 (a TIME(6));
INSERT INTO t1 VALUES ('10:10:10.999999');
ALTER TABLE t1 MODIFY a TIME;
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing time_truncate_fractional from DATETIME(6) to TIME(N)
--echo #
CREATE TABLE t1 (a DATETIME(6),
  t6 TIME(6), t5 TIME(5), t4 TIME(4),
  t3 TIME(3), t2 TIME(2), t1 TIME(1),
  t0 TIME);
INSERT INTO t1 (a) VALUES ('2001-01-01 10:10:10.999999');
INSERT INTO t1 (a) VALUES ('2001-01-01 23:59:59.9999994');
UPDATE t1 SET t0=a, t1=a, t2=a, t3=a, t4=a, t5=a, t6=a;
--query_vertical SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing time_truncate_fractional from DECIMAL and DOUBLE to TIME(N)
--echo #
CREATE TABLE t1 (a DECIMAL(30,7),
  t6 TIME(6), t5 TIME(5), t4 TIME(4),
  t3 TIME(3), t2 TIME(2), t1 TIME(1),
  t0 TIME);
INSERT INTO t1 (a) VALUES (101010.9999999);
--query_vertical SELECT * FROM t1;
ALTER TABLE t1 MODIFY a DOUBLE;
UPDATE t1 SET t0=a, t1=a, t2=a, t3=a, t4=a, t5=a, t6=a;
--query_vertical SELECT * FROM t1;
DROP TABLE t1;


--echo #
--echo # Testing INSERT with near-maximum and near-minimum supported values.
--echo #
CREATE TABLE t1 (a TIME(6));
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ('838:59:59.9999999');
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ('-838:59:59.9999999');
SELECT * FROM t1;
DROP TABLE t1;
CREATE TABLE t1 (a TIME);
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ('838:59:59.9999999');
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES ('-838:59:59.9999999');
SELECT * FROM t1;
DROP TABLE t1;


--echo #
--echo # Testing JOIN with comparison between TIME(6) and DECIMAL(20,6)
--echo #
CREATE TABLE t1 (a TIME(6));
INSERT INTO t1 VALUES ('11:22:33');
INSERT INTO t1 VALUES ('11:22:33.123');
INSERT INTO t1 VALUES ('-11:22:33');
INSERT INTO t1 VALUES ('-11:22:33.1234567');
CREATE TABLE t2 (b DECIMAL(20,6));
INSERT INTO t2 VALUES (112233.123);
INSERT INTO t2 VALUES (-112233.1234567);
SELECT * FROM t1, t2 WHERE a=b;
DROP TABLE t1, t2;

--echo #
--echo # Testing time_truncate_fractional from TIME(N) to BIGINT
--echo #
CREATE TABLE t1 (a BIGINT, b TIME(6));
INSERT INTO t1 (b) VALUES ('10:10:59.500000');
INSERT INTO t1 (b) VALUES ('10:10:10.500000');
INSERT INTO t1 (b) VALUES ('10:10:10.499999');
UPDATE t1 SET a=b;
SELECT * FROM t1;
DROP TABLE t1;


--echo #
--echo # Testing JOIN with comparison between TIME(6) and VARCHAR
--echo #
CREATE TABLE t1 (a TIME(6));
INSERT INTO t1 VALUES ('11:22:33');
INSERT INTO t1 VALUES ('11:22:33.123');
INSERT INTO t1 VALUES ('-11:22:33');
INSERT INTO t1 VALUES ('-11:22:33.1234567');
CREATE TABLE t2 (b VARCHAR(20));
INSERT INTO t2 VALUES ('11:22:33.123');
INSERT INTO t2 VALUES ('-11:22:33.123456');
SELECT * FROM t1, t2 WHERE a=b;
DROP TABLE t1, t2;

--echo #
--echo # Testing SEC_TO_TIME
--echo #
CREATE TABLE t1 AS SELECT SEC_TO_TIME(3661), CAST(SEC_TO_TIME(3661) AS CHAR);
SHOW CREATE TABLE t1;
SELECT * FROM t1;
DROP TABLE t1;
CREATE TABLE t1 AS SELECT
  SEC_TO_TIME(3661.9) AS c1,
  SEC_TO_TIME(3661.99) AS c2,
  SEC_TO_TIME(3661.999) AS c3,
  SEC_TO_TIME(3661.9999) AS c4,
  SEC_TO_TIME(3661.99999) AS c5,
  SEC_TO_TIME(3661.999999) AS c6,
  SEC_TO_TIME(3661.9999999) AS c7;
SHOW CREATE TABLE t1;
SELECT * FROM t1;
DROP TABLE t1;


--echo #
--echo # Testing time_truncate_fractional with MAKETIME()
--echo #
CREATE TABLE t1 (hour INT, minute INT, second DECIMAL(23,7));
INSERT INTO t1 VALUES
  (836, 59, 59.999999),
  (836, 59, 59.9999990),
  (836, 59, 59.9999991),
  (836, 59, 59.9999992),
  (836, 59, 59.9999993),
  (836, 59, 59.9999995),
  (836, 59, 59.9999996),
  (836, 59, 59.9999997),
  (836, 59, 59.9999998),
  (836, 59, 59.9999999);
SELECT hour, minute, second, MAKETIME(hour, minute, second) AS MAKETIME
FROM t1;
SELECT hour + 1, minute, second, MAKETIME(hour + 1, minute, second) AS MAKETIME
FROM t1;
SELECT -hour, minute, second, MAKETIME(-hour, minute, second) AS MAKETIME
FROM t1;
SELECT -hour - 1, minute, second, MAKETIME(-hour - 1, minute, second) AS MAKETIME
FROM t1;
DROP TABLE t1;

SELECT MAKETIME(838, 59, 59.0000005) AS MAKETIME;
SELECT MAKETIME(838, 59, 59.00000056) AS MAKETIME;
SELECT MAKETIME(838, 59, 59.000000567) AS MAKETIME;
SELECT MAKETIME(838, 59, 59.0000005678) AS MAKETIME;
SELECT MAKETIME(838, 59, 59.00000056789) AS MAKETIME;


--echo #
--echo # Testing time_truncate_fractional for DATETIME(6)
--echo #
CREATE TABLE t1 (a DATETIME(6));
INSERT INTO t1 VALUES (20010101100000.1234567);
INSERT INTO t1 VALUES (20010228235959.9999997);
INSERT INTO t1 VALUES ('2001-01-01 10:00:00.1234567');
INSERT INTO t1 VALUES ('2001-02-28 23:59:59.9999997');
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing time_truncate_fractional when altering DATETIME(N) to a smaller size
--echo #
CREATE TABLE t1 (a DATETIME(6));
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999999');
ALTER TABLE t1 MODIFY a DATETIME(5);
SELECT * FROM t1;
DROP TABLE t1;
CREATE TABLE t1 (a DATETIME(6));
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999999');
ALTER TABLE t1 MODIFY a TIME;
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing time_truncate_fractional from VARCHAR and DATETIME(6) to DATETIME(N)
--echo #
CREATE TABLE t1 (a VARCHAR(32),
  t6 DATETIME(6), t5 DATETIME(5), t4 DATETIME(4),
  t3 DATETIME(3), t2 DATETIME(2), t1 DATETIME(1),
  t0 DATETIME);
INSERT INTO t1 (a) VALUES ('2001-01-01 10:10:10.9999999');
--query_vertical SELECT * FROM t1;
ALTER TABLE t1 MODIFY a DATETIME(6);
UPDATE t1 SET t0=a, t1=a, t2=a, t3=a, t4=a, t5=a, t6=a;
--query_vertical SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing time_truncate_fractional from DATETIME(N) to BIGINT
--echo #
CREATE TABLE t1 (a BIGINT, b DATETIME(6));
INSERT INTO t1 (b) VALUES ('2001-01-01 10:10:59.500000');
INSERT INTO t1 (b) VALUES ('2001-01-01 10:10:10.500000');
INSERT INTO t1 (b) VALUES ('2001-01-01 10:10:10.499999');
UPDATE t1 SET a=b;
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing time_truncate_fractional from DECIMAL to DATETIME(N)
--echo #
CREATE TABLE t1 (a DECIMAL(30,6),
  t6 DATETIME(6), t5 DATETIME(5), t4 DATETIME(4),
  t3 DATETIME(3), t2 DATETIME(2), t1 DATETIME(1),
  t0 DATETIME);
INSERT INTO t1 (a) VALUES (20010101101010.999999);
UPDATE t1 SET t0=a, t1=a, t2=a, t3=a, t4=a, t5=a, t6=a;
--query_vertical SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing function MICROSECOND
--echo #
CREATE TABLE t1 (a DATETIME(6));
INSERT INTO t1 VALUES ('2001-01-01 11:12:13.0000019');
INSERT INTO t1 VALUES ('2001-01-01 11:12:13.1000019');
INSERT INTO t1 VALUES ('2001-01-01 11:12:13.9999999');
SELECT a, MICROSECOND(a) FROM t1;
SELECT a, EXTRACT(MICROSECOND FROM a) AS b FROM t1;
DROP TABLE t1;

SELECT MICROSECOND(CAST(20010101235959.456 AS DATETIME(6))) AS a;
SELECT EXTRACT(MICROSECOND FROM CAST(20010101235959.456 AS DATETIME(6))) AS a;

--echo #
--echo # Testing time_truncate_fractional with CAST
--echo #
CREATE TABLE t1 (a DATETIME(6));
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999999');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999998');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999997');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999996');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999995');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999994');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999993');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999992');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999991');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999990');
SELECT a, CAST(a AS DATETIME(5)) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a DATETIME(5));
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99999');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99998');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99997');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99996');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99995');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99994');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99993');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99992');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99991');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99990');
SELECT a, CAST(a AS DATETIME(4)) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a DATETIME(4));
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9999');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9998');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9997');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9996');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9995');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9994');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9993');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9992');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9991');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9990');
SELECT a, CAST(a AS DATETIME(3)) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a DATETIME(3));
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.998');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.997');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.996');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.995');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.994');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.993');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.992');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.991');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.990');
SELECT a, CAST(a AS DATETIME(2)) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a DATETIME(2));
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.99');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.98');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.97');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.96');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.95');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.94');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.93');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.92');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.91');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.90');
SELECT a, CAST(a AS DATETIME(1)) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a DATETIME(1));
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.8');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.7');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.6');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.5');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.4');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.3');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.2');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.1');
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.0');
SELECT a, CAST(a AS DATETIME) FROM t1;
DROP TABLE t1;


--echo #
--echo # Testing that Item::get_date() do no rounds.
--echo #
query_vertical SELECT
  CAST('2001-01-01 10:10:10.9999994' AS DATETIME) AS c1,
  CAST('2001-01-01 10:10:10.9999995' AS DATETIME) AS c2,
  CAST('2001-01-01 10:10:10.9999994' AS DATETIME(6)) AS c3,
  CAST('2001-01-01 10:10:10.9999995' AS DATETIME(6)) AS c4,
  CAST(20010101101010.9999994 AS DATETIME) AS c5,
  CAST(20010101101010.9999995 AS DATETIME) AS c6,
  CAST(20010101101010.9999994 AS DATETIME(6)) AS c7,
  CAST(20010101101010.9999995 AS DATETIME(6)) AS c8;

--echo #
--echo # Testing that comparison do no rounds
--echo #
CREATE TABLE t1 (t0 DATETIME, t6 DATETIME(6));
INSERT INTO t1 VALUES ('2001-01-01 00:00:00', '2001-01-01 00:00:00.999999');
INSERT INTO t1 VALUES ('2001-01-01 00:00:01', '2001-01-01 00:00:01.000000');
SELECT t0 FROM t1 WHERE t6='2001-01-01 00:00:00.9999998';
SELECT t6 FROM t1 WHERE t6='2001-01-01 00:00:00.9999998';
DROP TABLE t1;

--echo #
--echo # Testing that EXTRACT do no rounds
--echo #
query_vertical SELECT
  EXTRACT(MICROSECOND FROM '2001-01-01 00:00:00.9999994') AS c1,
  EXTRACT(MICROSECOND FROM '2001-01-01 00:00:00.9999995') AS c2,
  EXTRACT(MICROSECOND FROM 20010101000000.9999994) AS c3,
  EXTRACT(MICROSECOND FROM 20010101000000.9999995) AS c4;

##############################################################################

--echo #
--echo # Testing time_truncate_fractional when altering TIMESTAMP(N) to a smaller size
--echo #
CREATE TABLE t1 (a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6));
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999999');
ALTER TABLE t1 MODIFY a TIMESTAMP(5);
SELECT * FROM t1;
DROP TABLE t1;
CREATE TABLE t1 (a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6));
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.999999');
ALTER TABLE t1 MODIFY a TIME;
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing time_truncate_fractional from VARCHAR to TIMESTAMP(N)
--echo #
CREATE TABLE t1 (a VARCHAR(32),
  t6 TIMESTAMP(6) NULL DEFAULT NULL,
  t5 TIMESTAMP(5) NULL DEFAULT NULL,
  t4 TIMESTAMP(4) NULL DEFAULT NULL,
  t3 TIMESTAMP(3) NULL DEFAULT NULL,
  t2 TIMESTAMP(2) NULL DEFAULT NULL,
  t1 TIMESTAMP(1) NULL DEFAULT NULL,
  t0 TIMESTAMP    NULL DEFAULT NULL);
INSERT INTO t1 (a) VALUES ('2001-01-01 10:10:10.9999999');
--query_vertical SELECT * FROM t1;
UPDATE t1 SET t0=a, t1=a, t2=a, t3=a, t4=a, t5=a, t6=a;
--query_vertical SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing time_truncate_fractional from TIMESTAMP(N) to BIGINT
--echo #
CREATE TABLE t1 (a BIGINT, b TIMESTAMP(6) NULL DEFAULT NULL);
INSERT INTO t1 (b) VALUES ('2001-01-01 10:10:59.500000');
INSERT INTO t1 (b) VALUES ('2001-01-01 10:10:10.500000');
INSERT INTO t1 (b) VALUES ('2001-01-01 10:10:10.499999');
UPDATE t1 SET a=b;
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing time_truncate_fractional from TIME(6) to TIMESTAMP(N)
--echo #
SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00');
CREATE TABLE t1 (a TIME(6), t6 TIMESTAMP(6));
INSERT INTO t1 (a) VALUES ('10:10:10.999999');
SELECT * FROM t1;

UPDATE t1 SET t6=a;
--query_vertical SELECT * FROM t1;
DROP TABLE t1;
SET timestamp=default;

--echo #
--echo # Testing time_truncate_fractional from DECIMAL to TIMESTAMP(N)
--echo #
CREATE TABLE t1 (a DECIMAL(30,6), t6 TIMESTAMP(6));
INSERT INTO t1 (a) VALUES (20010101101010.999999);
UPDATE t1 SET t6=a;
--query_vertical SELECT * FROM t1;
DROP TABLE t1;


--echo #
--echo # Testing JOIN with comparison between TIMESTAMP(6) and DECIMAL(20,6)
--echo #
CREATE TABLE t1 (a TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6));
INSERT INTO t1 VALUES ('2001-01-01 11:22:33');
INSERT INTO t1 VALUES ('2001-01-01 11:22:33.123');
INSERT INTO t1 VALUES ('2001-01-01 11:22:33.1234567');
CREATE TABLE t2 (b DECIMAL(20,6));
INSERT INTO t2 VALUES (20010101112233);
INSERT INTO t2 VALUES (20010101112233.123);
INSERT INTO t2 VALUES (20010101112233.1234567);
SELECT * FROM t1, t2 WHERE a=b;
DROP TABLE t1, t2;

--echo #
--echo # Testing INSERT with near-maximum and near-minimum supported values for
--echo # TIMESTAMP.
--echo #
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP(6));
INSERT INTO t1 VALUES ('1970-01-01 00:00:01.000000');
INSERT INTO t1 VALUES ('2038-01-19 03:14:07.9999999');
SELECT * FROM t1;
DROP TABLE t1;
CREATE TABLE t1 (a TIMESTAMP);
INSERT INTO t1 VALUES ('1970-01-01 00:00:01.000000');
INSERT INTO t1 VALUES ('2038-01-19 03:14:07.999999');
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Testing INSERT with near-maximum and near-minimum supported values for
--echo # DATETIME.
--echo #
CREATE TABLE t1 (a DATETIME(6));
INSERT INTO t1 VALUES ('9999-12-31 23:59:59.9999999');
INSERT INTO t1 VALUES ('1000-01-01 00:00:00.000000');
SELECT * FROM t1;
DROP TABLE t1;
CREATE TABLE t1 (a DATETIME);
INSERT INTO t1 VALUES ('9999-12-31 23:59:59.999999');
INSERT INTO t1 VALUES ('1000-01-01 00:00:00.000000');
SELECT * FROM t1;
DROP TABLE t1;

SET sql_mode=DEFAULT;