SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', '');
SET @default_sql_mode=@@sql_mode;
#
# DATE: SET
#
CREATE TABLE t1 (a DATE, b DATETIME(4));
INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999');
UPDATE t1 SET a=b;
Warnings:
Note	1265	Data truncated for column 'a' at row 1
SELECT a FROM t1;
a
2000-12-31
DROP TABLE t1;
CREATE TABLE t1 (a DATE, 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 1
Note	1265	Data truncated for column 'a' at row 2
SELECT a FROM t1;
a
2000-12-31
2000-12-31
DROP TABLE t1;
CREATE TABLE t1 (a DATE, b DECIMAL(38,10));
INSERT INTO t1 VALUES(NULL,20001231235959.9999);
INSERT INTO t1 VALUES(NULL,20001231235959.9999999);
UPDATE t1 SET a=b;
Warnings:
Note	1265	Data truncated for column 'a' at row 1
Note	1265	Data truncated for column 'a' at row 2
SELECT a FROM t1;
a
2000-12-31
2000-12-31
DROP TABLE t1;
#
# DATE: ALTER
#
CREATE TABLE t1 (a DATETIME(4));
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
ALTER TABLE t1 MODIFY a DATE;
Warnings:
Note	1265	Data truncated for column 'a' at row 1
SELECT a FROM t1;
a
2000-12-31
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 DATE;
Warnings:
Note	1265	Data truncated for column 'a' at row 1
Note	1292	Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999'
Note	1265	Data truncated for column 'a' at row 2
SELECT a FROM t1;
a
2000-12-31
2000-12-31
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 DATE;
Warnings:
Note	1265	Data truncated for column 'a' at row 1
Note	1265	Data truncated for column 'a' at row 2
SELECT a FROM t1;
a
2000-12-31
2000-12-31
DROP TABLE t1;
#
# DATE: CAST
#
CREATE TABLE t1 (a DATETIME(4));
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
SELECT a, CAST(a AS DATE) FROM t1;
a	CAST(a AS DATE)
2000-12-31 23:59:59.9999	2000-12-31
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 DATE) FROM t1;
a	CAST(a AS DATE)
2000-12-31 23:59:59.9999	2000-12-31
2000-12-31 23:59:59.9999999	2000-12-31
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 DATE) FROM t1;
a	CAST(a AS DATE)
20001231235959.9999000000	2000-12-31
20001231235959.9999999000	2000-12-31
DROP TABLE t1;
#
# Equal field propagation
#
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES (20010101);
INSERT INTO t1 VALUES (20010102);
SELECT * FROM t1 WHERE a= 20010101235959.9999999;
a
2001-01-02
SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999';
a
2001-01-02
Warnings:
Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND a>='2001-01-01 23:59:59.9999999';
a
2001-01-02
Warnings:
Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND CONCAT(a)='2001-01-02';
a
2001-01-02
Warnings:
Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND a>='2001-01-01 23:59:59.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: '2001-01-01 23:59:59.9999999'
Note	1292	Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999'
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2001-01-02'
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND CONCAT(a)='2001-01-02';
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: '2001-01-01 23:59:59.9999999'
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2001-01-02'
DROP TABLE t1;
#
# Comparing non-temporal to DATE
#
# Although conversion from non-temporal to DATE (e.g. on SET) does not round,
# comparison between non-temporal to DATE is performed as DATETIME.
# So rounding does happen here.
CREATE TABLE t1 (a VARCHAR(64));
INSERT t1 VALUES ('2001-01-01 23:59:59.9999999');
SELECT * FROM t1 WHERE a=DATE'2001-01-02';
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)=DATE'2001-01-02';
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)=DATE'2001-01-02';
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=DATE'2001-01-02';
a
20010101235959.9999999
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2001-01-02';
a
20010101235959.9999999
DROP TABLE t1;