1SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', ''); 2SET @default_sql_mode=@@sql_mode; 3# 4# DATE: SET 5# 6CREATE TABLE t1 (a DATE, b DATETIME(4)); 7INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999'); 8UPDATE t1 SET a=b; 9Warnings: 10Note 1265 Data truncated for column 'a' at row 1 11SELECT a FROM t1; 12a 132000-12-31 14DROP TABLE t1; 15CREATE TABLE t1 (a DATE, b VARCHAR(64)); 16INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999'); 17INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999999'); 18UPDATE t1 SET a=b; 19Warnings: 20Note 1265 Data truncated for column 'a' at row 1 21Note 1265 Data truncated for column 'a' at row 2 22SELECT a FROM t1; 23a 242000-12-31 252000-12-31 26DROP TABLE t1; 27CREATE TABLE t1 (a DATE, b DECIMAL(38,10)); 28INSERT INTO t1 VALUES(NULL,20001231235959.9999); 29INSERT INTO t1 VALUES(NULL,20001231235959.9999999); 30UPDATE t1 SET a=b; 31Warnings: 32Note 1265 Data truncated for column 'a' at row 1 33Note 1265 Data truncated for column 'a' at row 2 34SELECT a FROM t1; 35a 362000-12-31 372000-12-31 38DROP TABLE t1; 39# 40# DATE: ALTER 41# 42CREATE TABLE t1 (a DATETIME(4)); 43INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999'); 44ALTER TABLE t1 MODIFY a DATE; 45Warnings: 46Note 1265 Data truncated for column 'a' at row 1 47SELECT a FROM t1; 48a 492000-12-31 50DROP TABLE t1; 51CREATE TABLE t1 (a VARCHAR(64)); 52INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999'); 53INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999999'); 54ALTER TABLE t1 MODIFY a DATE; 55Warnings: 56Note 1265 Data truncated for column 'a' at row 1 57Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 58Note 1265 Data truncated for column 'a' at row 2 59SELECT a FROM t1; 60a 612000-12-31 622000-12-31 63DROP TABLE t1; 64CREATE TABLE t1 (a DECIMAL(38,10)); 65INSERT INTO t1 VALUES(20001231235959.9999); 66INSERT INTO t1 VALUES(20001231235959.9999999); 67ALTER TABLE t1 MODIFY a DATE; 68Warnings: 69Note 1265 Data truncated for column 'a' at row 1 70Note 1265 Data truncated for column 'a' at row 2 71SELECT a FROM t1; 72a 732000-12-31 742000-12-31 75DROP TABLE t1; 76# 77# DATE: CAST 78# 79CREATE TABLE t1 (a DATETIME(4)); 80INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999'); 81SELECT a, CAST(a AS DATE) FROM t1; 82a CAST(a AS DATE) 832000-12-31 23:59:59.9999 2000-12-31 84DROP TABLE t1; 85CREATE TABLE t1 (a VARCHAR(64)); 86INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999'); 87INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999999'); 88SELECT a, CAST(a AS DATE) FROM t1; 89a CAST(a AS DATE) 902000-12-31 23:59:59.9999 2000-12-31 912000-12-31 23:59:59.9999999 2000-12-31 92Warnings: 93Note 1292 Truncated incorrect datetime value: '2000-12-31 23:59:59.9999999' 94DROP TABLE t1; 95CREATE TABLE t1 (a DECIMAL(38,10)); 96INSERT INTO t1 VALUES(20001231235959.9999); 97INSERT INTO t1 VALUES(20001231235959.9999999); 98SELECT a, CAST(a AS DATE) FROM t1; 99a CAST(a AS DATE) 10020001231235959.9999000000 2000-12-31 10120001231235959.9999999000 2000-12-31 102DROP TABLE t1; 103# 104# Equal field propagation 105# 106CREATE TABLE t1 (a DATE); 107INSERT INTO t1 VALUES (20010101); 108INSERT INTO t1 VALUES (20010102); 109SELECT * FROM t1 WHERE a= 20010101235959.9999999; 110a 1112001-01-02 112SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999'; 113a 1142001-01-02 115Warnings: 116Note 1292 Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999' 117SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND a>='2001-01-01 23:59:59.9999999'; 118a 1192001-01-02 120Warnings: 121Note 1292 Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999' 122Note 1292 Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999' 123SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND CONCAT(a)='2001-01-02'; 124a 1252001-01-02 126Warnings: 127Note 1292 Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999' 128EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND a>='2001-01-01 23:59:59.9999999'; 129id select_type table type possible_keys key key_len ref rows filtered Extra 1301 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 131Warnings: 132Note 1292 Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999' 133Note 1292 Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999' 134Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2001-01-02' 135EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND CONCAT(a)='2001-01-02'; 136id select_type table type possible_keys key key_len ref rows filtered Extra 1371 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 138Warnings: 139Note 1292 Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999' 140Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2001-01-02' 141DROP TABLE t1; 142# 143# Comparing non-temporal to DATE 144# 145# Although conversion from non-temporal to DATE (e.g. on SET) does not round, 146# comparison between non-temporal to DATE is performed as DATETIME. 147# So rounding does happen here. 148CREATE TABLE t1 (a VARCHAR(64)); 149INSERT t1 VALUES ('2001-01-01 23:59:59.9999999'); 150SELECT * FROM t1 WHERE a=DATE'2001-01-02'; 151a 1522001-01-01 23:59:59.9999999 153Warnings: 154Note 1292 Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999' 155SELECT * FROM t1 WHERE CONCAT(a)=DATE'2001-01-02'; 156a 1572001-01-01 23:59:59.9999999 158Warnings: 159Note 1292 Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999' 160SELECT * FROM t1 WHERE COALESCE(a)=DATE'2001-01-02'; 161a 1622001-01-01 23:59:59.9999999 163Warnings: 164Note 1292 Truncated incorrect datetime value: '2001-01-01 23:59:59.9999999' 165DROP TABLE t1; 166CREATE TABLE t1 (a DECIMAL(32,7)); 167INSERT t1 VALUES (20010101235959.9999999); 168SELECT * FROM t1 WHERE a=DATE'2001-01-02'; 169a 17020010101235959.9999999 171SELECT * FROM t1 WHERE COALESCE(a)=DATE'2001-01-02'; 172a 17320010101235959.9999999 174DROP TABLE t1; 175