diff options
Diffstat (limited to 'mysql-test/t/func_time.test')
| -rw-r--r-- | mysql-test/t/func_time.test | 243 |
1 files changed, 243 insertions, 0 deletions
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 6f9359e6095..045b91b3e7b 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -1194,3 +1194,246 @@ SELECT 1 FROM DUAL WHERE SECOND(TIMEDIFF(NULL, '12:12:12')); SET TIME_ZONE='+02:00'; SELECT UNIX_TIMESTAMP(STR_TO_DATE('2020','%Y')); SET TIME_ZONE=DEFAULT; + + +--echo # +--echo # MDEV-4863 COALESCE(time_or_datetime) returns wrong results in numeric context +--echo # +CREATE TABLE t1 (a TIMESTAMP(3)); +INSERT INTO t1 VALUES ('2001-01-01 10:20:30.999'); +SELECT CAST(COALESCE(a,a) AS SIGNED) AS c1, CAST(COALESCE(a,a) AS DECIMAL(25,3)) AS c2, ROUND(COALESCE(a,a)) AS c2 FROM t1; +DROP TABLE t1; +CREATE TABLE t1 (a TIME(3)); +INSERT INTO t1 VALUES ('10:20:30.999'); +SELECT CAST(COALESCE(a,a) AS SIGNED) AS c1, CAST(COALESCE(a,a) AS DECIMAL(25,3)) AS c2, ROUND(COALESCE(a,a)) AS c2 FROM t1; +DROP TABLE t1; + +SELECT + CAST(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, + CAST(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, + COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, + CONCAT(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, + TIME(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, + DATE(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, + TIMESTAMP(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; + +SELECT + CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1, + CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2, + COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))+0e0 AS c3, + CONCAT(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c4, + TIME(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5, + DATE(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6, + TIMESTAMP(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7; + +SELECT + CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, + CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, + IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, + CONCAT(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, + TIME(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, + DATE(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, + TIMESTAMP(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; + +SELECT + CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1, + CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2, + IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))+0e0 AS c3, + CONCAT(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c4, + TIME(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5, + DATE(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6, + TIMESTAMP(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7; + +SELECT + CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, + CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, + IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, + CONCAT(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, + TIME(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, + DATE(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, + TIMESTAMP(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; + +SELECT + CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1, + CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2, + IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))+0e0 AS c3, + CONCAT(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c4, + TIME(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5, + DATE(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6, + TIMESTAMP(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7; + +SELECT + CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1, + CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2, + CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END+0e0 AS c3, + CONCAT(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c4, + TIME(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c5, + DATE(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6, + TIMESTAMP(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7; + +SELECT + CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1, + CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2, + CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END+0e0 AS c3, + CONCAT(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c4, + TIME(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c5, + DATE(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6, + TIMESTAMP(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7; + +CREATE TABLE t1 AS SELECT + CONCAT(COALESCE(TIME(101010),TIME(101010))) AS c1, + CONCAT(IF(0,TIME(101010),TIME(101010))) AS c2, + CONCAT(IFNULL(TIME(101010),TIME(101010))) AS c3, + CONCAT(CASE WHEN 1 THEN TIME(101010) ELSE TIME(101010) END) AS c4; +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # +--echo # MDEV-4870 Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types +--echo # +CREATE TABLE t1 (dt2 DATETIME(2), t3 TIME(3), d DATE); +INSERT INTO t1 VALUES ('2001-01-01 00:00:00.12', '00:00:00.567', '2002-01-01'); +SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1; +CREATE TABLE t2 AS SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1; +SELECT * FROM t2; +SHOW COLUMNS FROM t2; +DROP TABLE t2; +SELECT CASE WHEN 1 THEN dt2 ELSE t3 END FROM t1; +SELECT CONCAT(CASE WHEN 1 THEN dt2 ELSE t3 END) FROM t1; +SELECT CONCAT(CASE WHEN 0 THEN t3 ELSE dt2 END) FROM t1; +SELECT CONCAT(CASE WHEN 1 THEN d ELSE t3 END) FROM t1; +SELECT CASE WHEN 1 THEN t3 ELSE d END FROM t1; +SELECT COALESCE(d, t3) FROM t1; +SELECT CONCAT(COALESCE(d, t3)) FROM t1; +SELECT COALESCE(dt2, t3) FROM t1; +SELECT CONCAT(COALESCE(dt2, t3)) FROM t1; +SELECT IFNULL(dt2, t3), CONCAT(IFNULL(dt2, t3)) FROM t1; +SELECT IFNULL(d, t3), CONCAT(IFNULL(d, t3)) FROM t1; +DROP TABLE t1; + + +--echo # +--echo # MDEV-4724 Some temporal functions do not preserve microseconds +--echo # +SELECT MAKETIME(10,10,10.231); +SELECT MAKETIME(0, 0, 59.9); +CREATE TABLE t1 AS SELECT + MAKETIME(10,00,00), + MAKETIME(10,00,00.1), + MAKETIME(10,00,00.12), + MAKETIME(10,00,00.123), + MAKETIME(10,00,00.1234), + MAKETIME(10,00,00.12345), + MAKETIME(10,00,00.123456); +SHOW COLUMNS FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT + TIME('10:00:00'), + TIME('10:00:00.1'), + TIME('10:00:00.12'), + TIME('10:00:00.123'), + TIME('10:00:00.1234'), + TIME('10:00:00.12345'), + TIME('10:00:00.12346'); +SHOW COLUMNS FROM t1; +DROP TABLE t1; + +SET TIME_ZONE='+00:00'; +SET TIMESTAMP=UNIX_TIMESTAMP('2012-10-16 22:46:17'); +SELECT NOW(), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP('2012-10-16 22:46:17'); +SET TIMESTAMP=UNIX_TIMESTAMP('1970-01-02 03:04:05.123456'); +SELECT @@timestamp, FROM_UNIXTIME(@@timestamp); +SET TIME_ZONE=DEFAULT; +SET TIMESTAMP=DEFAULT; + +SELECT TIME('2012-10-16 15:54:16.12'); +SELECT TIMESTAMP('2012-10-16 15:54:16.12'); +SELECT TIMEDIFF('10:10:10.1','00:00:00'); +SELECT TIME_TO_SEC('10:10:10'); +SELECT ADDTIME(TIME('10:10:10.1'),'10:10:10.12'); +SELECT ADDTIME(TIMESTAMP('2001-01-01 10:10:10.1'),'10:10:10.12'); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00.1', INTERVAL 1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00.12', INTERVAL 1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00.123', INTERVAL 1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00.1234', INTERVAL 1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00.12345', INTERVAL 1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00.123456', INTERVAL 1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1234 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12345 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123456 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1234 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12345 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123456 SECOND); +SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 'xxx' SECOND); +SELECT CONVERT_TZ('2001-01-01 10:20:30.12','+00:00','+01:00'); + +--echo # +--echo # MDEV-4861 TIME/DATETIME arithmetics does not preserve INTERVAL precision +--echo # +CREATE TABLE t1 (t0 TIME); +INSERT INTO t1 VALUES ('00:00:00'); +SELECT t0 + INTERVAL 1.1 SECOND FROM t1; +CREATE TABLE t2 AS SELECT t0 + INTERVAL 1.1 SECOND FROM t1; +SHOW COLUMNS FROM t2; +DROP TABLE t1,t2; + +CREATE TABLE t1 (t0 DATETIME); +INSERT INTO t1 VALUES ('2001-01-01 00:00:00'); +SELECT t0 + INTERVAL 1.1 SECOND FROM t1; +CREATE TABLE t2 AS SELECT t0 + INTERVAL 1.1 SECOND FROM t1; +SHOW COLUMNS FROM t2; +DROP TABLE t1, t2; + + +--echo # +--echo # MDEV-4843 Wrong data type for TIMESTAMP('2001-01-01','10:10:10') +--echo # +CREATE TABLE t1 AS SELECT + TIMESTAMP('2001-01-01','10:10:10'), + TIMESTAMP('2001-01-01','10:10:10.1'), + TIMESTAMP('2001-01-01','10:10:10.12'), + TIMESTAMP('2001-01-01','10:10:10.123'), + TIMESTAMP('2001-01-01','10:10:10.1234'), + TIMESTAMP('2001-01-01','10:10:10.12345'), + TIMESTAMP('2001-01-01','10:10:10.123456'), + TIMESTAMP('2001-01-01','10:10:10.1234567'); +SHOW COLUMNS FROM t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT + TIMESTAMP('2001-01-01 00:00:00','10:10:10'), + TIMESTAMP('2001-01-01 00:00:00.1','10:10:10'), + TIMESTAMP('2001-01-01 00:00:00.12','10:10:10'), + TIMESTAMP('2001-01-01 00:00:00.123','10:10:10'), + TIMESTAMP('2001-01-01 00:00:00.1234','10:10:10'), + TIMESTAMP('2001-01-01 00:00:00.12345','10:10:10'), + TIMESTAMP('2001-01-01 00:00:00.123456','10:10:10'), + TIMESTAMP('2001-01-01 00:00:00.1234567','10:10:10'); +SHOW COLUMNS FROM t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT + TIMESTAMP('00:00:00','10:10:10'), + TIMESTAMP(TIME('00:00:00'),'10:10:10'); +SHOW COLUMNS FROM t1; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-4869 Wrong result of MAKETIME(0, 0, -0.1) +--echo # +SELECT MAKETIME(0, 0, -0.1); + |