SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', '');
SET @default_sql_mode=@@sql_mode;
#
# DATETIME: SET
#
CREATE TABLE t1 (a DATETIME(3), b DATETIME(4));
INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999');
UPDATE t1 SET a=b;
SELECT a FROM t1;
a
2001-01-01 00:00:00.000
DROP TABLE t1;
CREATE TABLE t1 (a DATETIME(3), b VARCHAR(64));
INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999');
INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999999');
UPDATE t1 SET a=b;
Warnings:
Note	1265	Data truncated for column 'a' at row 2
SELECT a FROM t1;
a
2001-01-01 00:00:00.000
2001-01-01 00:00:00.000
DROP TABLE t1;
CREATE TABLE t1 (a DATETIME(3), b DECIMAL(38,10));
INSERT INTO t1 VALUES(NULL,20001231235959.9999);
INSERT INTO t1 VALUES(NULL,20001231235959.9999999);
UPDATE t1 SET a=b;
SELECT a FROM t1;
a
2001-01-01 00:00:00.000
2001-01-01 00:00:00.000
DROP TABLE t1;
#
# DATETIME: ALTER
#
CREATE TABLE t1 (a DATETIME(4));
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
ALTER TABLE t1 MODIFY a DATETIME(3);
SELECT a FROM t1;
a
2001-01-01 00:00:00.000
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999999');
ALTER TABLE t1 MODIFY a DATETIME(3);
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
SELECT a FROM t1;
a
2001-01-01 00:00:00.000
2001-01-01 00:00:00.000
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(38,10));
INSERT INTO t1 VALUES(20001231235959.9999);
INSERT INTO t1 VALUES(20001231235959.9999999);
ALTER TABLE t1 MODIFY a DATETIME(3);
SELECT a FROM t1;
a
2001-01-01 00:00:00.000
2001-01-01 00:00:00.000
DROP TABLE t1;
#
# Corner case:
# ALTER DATETIME to a shorter DATETIME
# All values round, maximum possible value truncates.
#
SET time_zone='+00:00';
CREATE TABLE t1 (ID INT, a DATETIME(6), comment VARCHAR(64));
INSERT INTO t1 VALUES (0, '9999-12-30 23:59:58.999999', 'Should round');
INSERT INTO t1 VALUES (1, '9999-12-31 22:59:59.999999', 'Should round');
INSERT INTO t1 VALUES (2, '9999-12-31 23:59:58.999999', 'Should round');
INSERT INTO t1 VALUES (3, '9999-12-31 23:59:59.999999', 'Should truncate');
ALTER TABLE t1 MODIFY a DATETIME(5);
Warnings:
Warning	1264	Out of range value for column 'a' at row 4
SELECT * FROM t1;
ID	a	comment
0	9999-12-30 23:59:59.00000	Should round
1	9999-12-31 23:00:00.00000	Should round
2	9999-12-31 23:59:59.00000	Should round
3	9999-12-31 23:59:59.99999	Should truncate
DROP TABLE t1;
SET time_zone=DEFAULT;
#
# NOW
#
SET time_zone='+00:00';
SET timestamp=UNIX_TIMESTAMP('2010-12-31 23:59:59.999999');
CREATE OR REPLACE TABLE t1 (id SERIAL, a DATETIME(4));
INSERT INTO t1 (a) VALUES (now(6));
INSERT INTO t1 (a) VALUES (CURRENT_TIMESTAMP(6));
INSERT INTO t1 (a) VALUES (CURRENT_TIME(6));
SELECT * FROM t1;
id	a
1	2011-01-01 00:00:00.0000
2	2011-01-01 00:00:00.0000
3	2011-01-01 00:00:00.0000
DROP TABLE t1;
SET timestamp=DEFAULT;
SET time_zone=DEFAULT;
#
# DATETIME: CAST
#
CREATE TABLE t1 (a DATETIME(4));
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
SELECT a, CAST(a AS DATETIME(3)) FROM t1;
a	CAST(a AS DATETIME(3))
2000-12-31 23:59:59.9999	2001-01-01 00:00:00.000
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999999');
SELECT a, CAST(a AS DATETIME(3)) FROM t1;
a	CAST(a AS DATETIME(3))
2000-12-31 23:59:59.9999	2001-01-01 00:00:00.000
2000-12-31 23:59:59.9999999	2001-01-01 00:00:00.000
Warnings:
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(38,10));
INSERT INTO t1 VALUES(20001231235959.9999);
INSERT INTO t1 VALUES(20001231235959.9999999);
SELECT a, CAST(a AS DATETIME(3)) FROM t1;
a	CAST(a AS DATETIME(3))
20001231235959.9999000000	2001-01-01 00:00:00.000
20001231235959.9999999000	2001-01-01 00:00:00.000
DROP TABLE t1;
#
# Equal field propagation
#
CREATE TABLE t1 (a DATETIME(6));
INSERT INTO t1 VALUES (20010101235959.999999);
INSERT INTO t1 VALUES (20010101235959.9999999);
SELECT * FROM t1 WHERE a=20010101235959.9999999;
a
2001-01-02 00:00:00.000000
SELECT * FROM t1 WHERE a='20010101235959.9999999';
a
2001-01-02 00:00:00.000000
Warnings:
Note	1292	Truncated incorrect datetime value: '20010101235959.9999999'
SELECT * FROM t1 WHERE a='20010101235959.9999999' AND a>='20010101235959.9999999';
a
2001-01-02 00:00:00.000000
Warnings:
Note	1292	Truncated incorrect datetime value: '20010101235959.9999999'
Note	1292	Truncated incorrect datetime value: '20010101235959.9999999'
SELECT * FROM t1 WHERE a='20010101235959.9999999' AND CONCAT(a)='2001-01-02 00:00:00.000000';
a
2001-01-02 00:00:00.000000
Warnings:
Note	1292	Truncated incorrect datetime value: '20010101235959.9999999'
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='20010101235959.9999999' AND a>='20010101235959.9999999';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1292	Truncated incorrect datetime value: '20010101235959.9999999'
Note	1292	Truncated incorrect datetime value: '20010101235959.9999999'
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-02 00:00:00'
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='20010101235959.9999999' AND CONCAT(a)='2001-01-02 00:00:00.000000';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1292	Truncated incorrect datetime value: '20010101235959.9999999'
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2001-01-02 00:00:00'
DROP TABLE t1;
#
# Comparing non-temporal to DATETIME
#
CREATE TABLE t1 (a VARCHAR(64));
INSERT t1 VALUES ('2001-01-01 23:59:59.9999999');
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-02 00:00:00';
a
2001-01-01 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
SELECT * FROM t1 WHERE CONCAT(a)=TIMESTAMP'2001-01-02 00:00:00';
a
2001-01-01 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
SELECT * FROM t1 WHERE COALESCE(a)=TIMESTAMP'2001-01-02 00:00:00';
a
2001-01-01 23:59:59.9999999
Warnings:
Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(32,7));
INSERT t1 VALUES (20010101235959.9999999);
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-02 00:00:00';
a
20010101235959.9999999
SELECT * FROM t1 WHERE COALESCE(a)=TIMESTAMP'2001-01-02 00:00:00';
a
20010101235959.9999999
DROP TABLE t1;
#
# Literal corner case
#
SELECT TIMESTAMP'9999-12-31 23:59:59.999999';
TIMESTAMP'9999-12-31 23:59:59.999999'
9999-12-31 23:59:59.999999
SELECT TIME'9999-12-31 23:59:59.9999999';
ERROR HY000: Incorrect TIME value: '9999-12-31 23:59:59.9999999'