diff options
Diffstat (limited to 'mysql-test/main/func_time.test')
| -rw-r--r-- | mysql-test/main/func_time.test | 933 |
1 files changed, 933 insertions, 0 deletions
diff --git a/mysql-test/main/func_time.test b/mysql-test/main/func_time.test index f6f39d967f2..4c9315629f1 100644 --- a/mysql-test/main/func_time.test +++ b/mysql-test/main/func_time.test @@ -1705,6 +1705,59 @@ DO TO_DAYS(SEC_TO_TIME(TIME(CEILING(UUID())))); DO TO_DAYS(SEC_TO_TIME(MAKEDATE('',RAND(~(''))))); SELECT SEC_TO_TIME(MAKEDATE(0,RAND(~0))); +# +# MDEV-16810 AddressSanitizer: stack-buffer-overflow in int10_to_str +# +SELECT PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')); + + +--echo # +--echo # MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result +--echo # + +--vertical_results +SELECT + MAKETIME(1e10,0,0), + MAKETIME(-1e10,0,0), + MAKETIME(1e50,0,0), + MAKETIME(-1e50,0,0), + MAKETIME(COALESCE(1e50),0,0), + MAKETIME(COALESCE(-1e50),0,0); +--horizontal_results + +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (1e30),(-1e30); +SELECT MAKETIME(a,0,0) FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-17244 MAKETIME(900,0,0.111) returns a wrong result +--echo # + +SELECT MAKETIME(900,0,0); +SELECT MAKETIME(900,0,0.1); +SELECT MAKETIME(900,0,0.11); +SELECT MAKETIME(900,0,0.111); +SELECT MAKETIME(900,0,0.1111); +SELECT MAKETIME(900,0,0.11111); +SELECT MAKETIME(900,0,0.111111); +SELECT MAKETIME(900,0,0.1111111); +SELECT MAKETIME(900,0,0.11111111); +SELECT MAKETIME(900,0,0.111111111); +SELECT MAKETIME(900,0,EXP(1)); + +SELECT MAKETIME(-900,0,0); +SELECT MAKETIME(-900,0,0.1); +SELECT MAKETIME(-900,0,0.11); +SELECT MAKETIME(-900,0,0.111); +SELECT MAKETIME(-900,0,0.1111); +SELECT MAKETIME(-900,0,0.11111); +SELECT MAKETIME(-900,0,0.111111); +SELECT MAKETIME(-900,0,0.1111111); +SELECT MAKETIME(-900,0,0.11111111); +SELECT MAKETIME(-900,0,0.111111111); +SELECT MAKETIME(-900,0,EXP(1)); + --echo # --echo # End of 5.5 tests @@ -2141,6 +2194,32 @@ DROP TABLE t_ts, t_trig; --echo # +--echo # MDEV-16878 Functions ADDTIME and SUBTIME get wrongly removed from WHERE by the equal expression optimizer +--echo # + +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:10'),('00:00:20'); +SELECT a, SUBTIME(a,TIME'00:00:01'), ADDTIME(a,TIME'00:00:01') FROM t1; +SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09'; +SELECT * FROM t1 WHERE ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; +SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; +DROP TABLE t1; + + +--echo # +--echo # MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME) +--echo # + +set timestamp=unix_timestamp('2018-08-02 10:10:10'); +SELECT + LAST_DAY(TIME'00:00:00') AS c1, + CAST(CAST(LAST_DAY(TIME'00:00:00') AS DATE) AS TIME) AS c2, + CAST(LAST_DAY(TIME'00:00:00') AS TIME) AS c3; + +--echo # End of 10.3 tests + +--echo # --echo # MDEV-14032 SEC_TO_TIME executes side effect two times --echo # @@ -2161,3 +2240,857 @@ SELECT SEC_TO_TIME(f1()); SELECT * FROM t1; DROP TABLE t1; DROP FUNCTION f1; + +--echo # +--echo # MDEV-17351 MICROSECOND(XXX(int_number_out_of_range)) erroneously returns 999999 +--echo # + +--echo # Reject anything that's parsed as DATETIME or DATE + +CREATE TABLE t1 (a VARCHAR(64)); +INSERT INTO t1 VALUES +('2001-01-01 10:20:30'), +('01-01-01 10:20:30'), +('2001-01-01 '), +('20010101102030'), +('010101102030'); +SELECT ADDTIME(DATE'2001-01-01',a), a FROM t1; +DROP TABLE t1; + + +--vertical_results + +--echo # GREATEST(decimal, time) + +SELECT + GREATEST(8395959, TIME'00:00:00') AS c0, + GREATEST(8395959.0, TIME'00:00:00') AS c1, + GREATEST(8395959.00, TIME'00:00:00') AS c2, + GREATEST(8395959.000, TIME'00:00:00') AS c3, + GREATEST(8395959.0000, TIME'00:00:00') AS c4, + GREATEST(8395959.00000, TIME'00:00:00') AS c5, + GREATEST(8395959.000000, TIME'00:00:00') AS c6, + GREATEST(8395959.0000000, TIME'00:00:00') AS c7; + +SELECT + MICROSECOND(GREATEST(8395959, TIME'00:00:00')) AS c0, + MICROSECOND(GREATEST(8395959.0, TIME'00:00:00')) AS c1, + MICROSECOND(GREATEST(8395959.00, TIME'00:00:00')) AS c2, + MICROSECOND(GREATEST(8395959.000, TIME'00:00:00')) AS c3, + MICROSECOND(GREATEST(8395959.0000, TIME'00:00:00')) AS c4, + MICROSECOND(GREATEST(8395959.00000, TIME'00:00:00')) AS c5, + MICROSECOND(GREATEST(8395959.000000, TIME'00:00:00')) AS c6, + MICROSECOND(GREATEST(8395959.0000000, TIME'00:00:00')) AS c7; + +SELECT + CAST(GREATEST(8395959, TIME'00:00:00') AS SIGNED) AS ci, + CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,0)) AS c0, + CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,1)) AS c1, + CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,2)) AS c2, + CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,3)) AS c3, + CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,4)) AS c4, + CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,5)) AS c5, + CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,6)) AS c6, + CAST(GREATEST(8395959, TIME'00:00:00') AS DECIMAL(30,7)) AS c7; + +SELECT + GREATEST(8395959, TIME'00:00:00') AS ci, + GREATEST(8395959, TIME'00:00:00')+0 AS c0, + GREATEST(8395959, TIME'00:00:00')+0.0 AS c1, + GREATEST(8395959, TIME'00:00:00')+0.00 AS c2, + GREATEST(8395959, TIME'00:00:00')+0.000 AS c3, + GREATEST(8395959, TIME'00:00:00')+0.0000 AS c4, + GREATEST(8395959, TIME'00:00:00')+0.00000 AS c5, + GREATEST(8395959, TIME'00:00:00')+0.000000 AS c6, + GREATEST(8395959, TIME'00:00:00')+0.0000000 AS c7; + + + +--echo # GREATEST(string, time) + +SELECT + GREATEST('839:59:59', TIME'00:00:00') AS ci, + GREATEST('839:59:59.0', TIME'00:00:00') AS c1, + GREATEST('839:59:59.00', TIME'00:00:00') AS c2, + GREATEST('839:59:59.000', TIME'00:00:00') AS c3, + GREATEST('839:59:59.0000', TIME'00:00:00') AS c4, + GREATEST('839:59:59.00000', TIME'00:00:00') AS c5, + GREATEST('839:59:59.000000', TIME'00:00:00') AS c6, + GREATEST('839:59:59.0000000', TIME'00:00:00') AS c7; + +SELECT + MICROSECOND(GREATEST('839:59:59', TIME'00:00:00')) AS ci, + MICROSECOND(GREATEST('839:59:59.0', TIME'00:00:00')) AS c1, + MICROSECOND(GREATEST('839:59:59.00', TIME'00:00:00')) AS c2, + MICROSECOND(GREATEST('839:59:59.000', TIME'00:00:00')) AS c3, + MICROSECOND(GREATEST('839:59:59.0000', TIME'00:00:00')) AS c4, + MICROSECOND(GREATEST('839:59:59.00000', TIME'00:00:00')) AS c5, + MICROSECOND(GREATEST('839:59:59.000000', TIME'00:00:00')) AS c6, + MICROSECOND(GREATEST('839:59:59.0000000', TIME'00:00:00')) AS c7; + +SELECT + CAST(GREATEST('839:59:59', TIME'00:00:00') AS SIGNED) AS ci, + CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,0)) AS c0, + CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,1)) AS c1, + CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,2)) AS c2, + CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,3)) AS c3, + CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,4)) AS c4, + CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,5)) AS c5, + CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,6)) AS c6, + CAST(GREATEST('839:59:59', TIME'00:00:00') AS DECIMAL(30,7)) AS c7; + +SELECT + GREATEST('839:59:59', TIME'00:00:00') AS ci, + GREATEST('839:59:59', TIME'00:00:00')+0 AS c0, + GREATEST('839:59:59', TIME'00:00:00')+0.0 AS c1, + GREATEST('839:59:59', TIME'00:00:00')+0.00 AS c2, + GREATEST('839:59:59', TIME'00:00:00')+0.000 AS c3, + GREATEST('839:59:59', TIME'00:00:00')+0.0000 AS c4, + GREATEST('839:59:59', TIME'00:00:00')+0.00000 AS c5, + GREATEST('839:59:59', TIME'00:00:00')+0.000000 AS c6, + GREATEST('839:59:59', TIME'00:00:00')+0.0000000 AS c7; + + +--echo # ADDTIME(datetime, decimal) + +SELECT + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS c0, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0) AS c1, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.00) AS c2, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.000) AS c3, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0000) AS c4, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.00000) AS c5, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.000000) AS c6, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0000000) AS c7; + +SELECT + MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)) AS c0, + MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0)) AS c1, + MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.00)) AS c2, + MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.000)) AS c3, + MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0000)) AS c4, + MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.00000)) AS c5, + MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.000000)) AS c6, + MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0000000)) AS c7; + +SELECT + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS SIGNED) AS ci, + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,0)) AS c0, + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,1)) AS c1, + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,2)) AS c2, + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,3)) AS c3, + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,4)) AS c4, + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,5)) AS c5, + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,6)) AS c6, + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS DECIMAL(30,7)) AS c7; + +SELECT + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959) AS ci, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0 AS c0, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.0 AS c1, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.00 AS c2, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.000 AS c3, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.0000 AS c4, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.00000 AS c5, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.000000 AS c6, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)+0.0000000 AS c7; + +--echo # ADDTIME(datetime, string) + +SELECT + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS c0, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0') AS c1, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.00') AS c2, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.000') AS c3, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0000') AS c4, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.00000') AS c5, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.000000') AS c6, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0000000') AS c7; + +SELECT + MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')) AS c0, + MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0')) AS c1, + MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.00')) AS c2, + MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.000')) AS c3, + MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0000')) AS c4, + MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.00000')) AS c5, + MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.000000')) AS c6, + MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59.0000000')) AS c7; + +SELECT + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS SIGNED) AS ci, + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,0)) AS c0, + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,1)) AS c1, + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,2)) AS c2, + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,3)) AS c3, + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,4)) AS c4, + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,5)) AS c5, + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,6)) AS c6, + CAST(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS DECIMAL(30,7)) AS c7; + +SELECT + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59') AS ci, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0 AS c0, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.0 AS c1, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.00 AS c2, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.000 AS c3, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.0000 AS c4, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.00000 AS c5, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.000000 AS c6, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '839:59:59')+0.0000000 AS c7; + +--echo # ADDTIME(time, decimal) + +SELECT + ADDTIME(TIME'00:00:00', 8395959) AS c0, + ADDTIME(TIME'00:00:00', 8395959.0) AS c1, + ADDTIME(TIME'00:00:00', 8395959.00) AS c2, + ADDTIME(TIME'00:00:00', 8395959.000) AS c3, + ADDTIME(TIME'00:00:00', 8395959.0000) AS c4, + ADDTIME(TIME'00:00:00', 8395959.00000) AS c5, + ADDTIME(TIME'00:00:00', 8395959.000000) AS c6, + ADDTIME(TIME'00:00:00', 8395959.0000000) AS c7; + +SELECT + MICROSECOND(ADDTIME(TIME'00:00:00', 8395959)) AS c0, + MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.0)) AS c1, + MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.00)) AS c2, + MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.000)) AS c3, + MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.0000)) AS c4, + MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.00000)) AS c5, + MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.000000)) AS c6, + MICROSECOND(ADDTIME(TIME'00:00:00', 8395959.0000000)) AS c7; + +SELECT + CAST(ADDTIME(TIME'00:00:00', 8395959) AS SIGNED) AS ci, + CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,0)) AS c0, + CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,1)) AS c1, + CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,2)) AS c2, + CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,3)) AS c3, + CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,4)) AS c4, + CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,5)) AS c5, + CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,6)) AS c6, + CAST(ADDTIME(TIME'00:00:00', 8395959) AS DECIMAL(30,7)) AS c7; + +SELECT + ADDTIME(TIME'00:00:00', 8395959) AS ci, + ADDTIME(TIME'00:00:00', 8395959)+0 AS c0, + ADDTIME(TIME'00:00:00', 8395959)+0.0 AS c1, + ADDTIME(TIME'00:00:00', 8395959)+0.00 AS c2, + ADDTIME(TIME'00:00:00', 8395959)+0.000 AS c3, + ADDTIME(TIME'00:00:00', 8395959)+0.0000 AS c4, + ADDTIME(TIME'00:00:00', 8395959)+0.00000 AS c5, + ADDTIME(TIME'00:00:00', 8395959)+0.000000 AS c6, + ADDTIME(TIME'00:00:00', 8395959)+0.0000000 AS c7; + +--echo # ADDTIME(time,string) + +SELECT + ADDTIME(TIME'00:00:00', '839:59:59') AS c0, + ADDTIME(TIME'00:00:00', '839:59:59.0') AS c1, + ADDTIME(TIME'00:00:00', '839:59:59.00') AS c2, + ADDTIME(TIME'00:00:00', '839:59:59.000') AS c3, + ADDTIME(TIME'00:00:00', '839:59:59.0000') AS c4, + ADDTIME(TIME'00:00:00', '839:59:59.00000') AS c5, + ADDTIME(TIME'00:00:00', '839:59:59.000000') AS c6, + ADDTIME(TIME'00:00:00', '839:59:59.0000000') AS c7; + +SELECT + MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59')) AS c0, + MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.0')) AS c1, + MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.00')) AS c2, + MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.000')) AS c3, + MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.0000')) AS c4, + MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.00000')) AS c5, + MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.000000')) AS c6, + MICROSECOND(ADDTIME(TIME'00:00:00', '839:59:59.0000000')) AS c7; + +SELECT + CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS SIGNED) AS ci, + CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,0)) AS c0, + CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,1)) AS c1, + CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,2)) AS c2, + CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,3)) AS c3, + CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,4)) AS c4, + CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,5)) AS c5, + CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,6)) AS c6, + CAST(ADDTIME(TIME'00:00:00', '839:59:59') AS DECIMAL(30,7)) AS c7; + +SELECT + ADDTIME(TIME'00:00:00', '839:59:59') AS ci, + ADDTIME(TIME'00:00:00', '839:59:59')+0 AS c0, + ADDTIME(TIME'00:00:00', '839:59:59')+0.0 AS c1, + ADDTIME(TIME'00:00:00', '839:59:59')+0.00 AS c2, + ADDTIME(TIME'00:00:00', '839:59:59')+0.000 AS c3, + ADDTIME(TIME'00:00:00', '839:59:59')+0.0000 AS c4, + ADDTIME(TIME'00:00:00', '839:59:59')+0.00000 AS c5, + ADDTIME(TIME'00:00:00', '839:59:59')+0.000000 AS c6, + ADDTIME(TIME'00:00:00', '839:59:59')+0.0000000 AS c7; + +--echo # ADDTIME(int,int) +SELECT + ADDTIME(0, 8395959) AS c, + MICROSECOND(ADDTIME(0, 8395959)) AS cm, + CAST(ADDTIME(0, 8395959) AS SIGNED) AS cs_fixme_mdev_17384, + CAST(ADDTIME(0, 8395959) AS DECIMAL(30,0)) AS cd300; + +SELECT + ADDTIME(20010101000000, 8395959) AS c, + MICROSECOND(ADDTIME(20010101000000, 8395959)) AS cm, + CAST(ADDTIME(20010101000000, 8395959) AS SIGNED) AS cs_fixme_mdev_17384, + CAST(ADDTIME(20010101000000, 8395959) AS DECIMAL(30,0)) AS cd300; + +--echo # ADDTIME(decimal,int) +--echo # 8385960 in cd300 is correct: addtime returns '838:59:59.9' +--echo # which is further *rounded* to a decimals(30,0) +SELECT + ADDTIME(0.0, 8395959) AS c, + MICROSECOND(ADDTIME(0.0, 8395959)) AS cm, + CAST(ADDTIME(0.0, 8395959) AS SIGNED) AS cs_fixme_mdev_17384, + CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,0)) AS cd300; + +SELECT + ADDTIME(20010101000000.0, 8395959) AS c, + MICROSECOND(ADDTIME(20010101000000.0, 8395959)) AS cm, + CAST(ADDTIME(20010101000000.0, 8395959) AS SIGNED) AS cs_fixme_mdev_17384, + CAST(ADDTIME(20010101000000.0, 8395959) AS DECIMAL(30,0)) AS cd300; + + +--echo # ADDTIME(decimal,decimal) + +SELECT + ADDTIME(0.0, 8395959.0) AS c1, + ADDTIME(0.0, 8395959.00) AS c2, + ADDTIME(0.0, 8395959.000) AS c3, + ADDTIME(0.0, 8395959.0000) AS c4, + ADDTIME(0.0, 8395959.00000) AS c5, + ADDTIME(0.0, 8395959.000000) AS c6, + ADDTIME(0.0, 8395959.0000000) AS c7; + +SELECT + MICROSECOND(ADDTIME(0.0, 8395959.0)) AS c1, + MICROSECOND(ADDTIME(0.0, 8395959.00)) AS c2, + MICROSECOND(ADDTIME(0.0, 8395959.000)) AS c3, + MICROSECOND(ADDTIME(0.0, 8395959.0000)) AS c4, + MICROSECOND(ADDTIME(0.0, 8395959.00000)) AS c5, + MICROSECOND(ADDTIME(0.0, 8395959.000000)) AS c6, + MICROSECOND(ADDTIME(0.0, 8395959.0000000)) AS c7; + +--echo # 8385960 in c1 is correct: addtime returns '838:59:59.9' +--echo # which is further *rounded* to a decimals(30,0) +SELECT + CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,0)) AS c0, + CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,1)) AS c1, + CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,2)) AS c2, + CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,3)) AS c3, + CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,4)) AS c4, + CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,5)) AS c5, + CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,6)) AS c6, + CAST(ADDTIME(0.0, 8395959) AS DECIMAL(30,7)) AS c7; + +SELECT + ADDTIME(0.0, 8395959)+0 AS c0, + ADDTIME(0.0, 8395959)+0.0 AS c1, + ADDTIME(0.0, 8395959)+0.00 AS c2, + ADDTIME(0.0, 8395959)+0.000 AS c3, + ADDTIME(0.0, 8395959)+0.0000 AS c4, + ADDTIME(0.0, 8395959)+0.00000 AS c5, + ADDTIME(0.0, 8395959)+0.000000 AS c6, + ADDTIME(0.0, 8395959)+0.0000000 AS c7; + + +--echo # TIMESTAMP(string,decimal) + +SELECT + TIMESTAMP('2001-01-01', 8395959) AS ci, + TIMESTAMP('2001-01-01', 8395959.0) AS c1, + TIMESTAMP('2001-01-01', 8395959.00) AS c2, + TIMESTAMP('2001-01-01', 8395959.000) AS c3, + TIMESTAMP('2001-01-01', 8395959.0000) AS c4, + TIMESTAMP('2001-01-01', 8395959.00000) AS c5, + TIMESTAMP('2001-01-01', 8395959.000000) AS c6, + TIMESTAMP('2001-01-01', 8395959.0000000) AS c7; + +SELECT + MICROSECOND(TIMESTAMP('2001-01-01', 8395959)) AS ci, + MICROSECOND(TIMESTAMP('2001-01-01', 8395959.0)) AS c1, + MICROSECOND(TIMESTAMP('2001-01-01', 8395959.00)) AS c2, + MICROSECOND(TIMESTAMP('2001-01-01', 8395959.000)) AS c3, + MICROSECOND(TIMESTAMP('2001-01-01', 8395959.0000)) AS c4, + MICROSECOND(TIMESTAMP('2001-01-01', 8395959.00000)) AS c5, + MICROSECOND(TIMESTAMP('2001-01-01', 8395959.000000)) AS c6, + MICROSECOND(TIMESTAMP('2001-01-01', 8395959.0000000)) AS c7; + +SELECT + CAST(TIMESTAMP('2001-01-01', 8395959) AS SIGNED) AS ci, + CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,0)) AS c0, + CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,1)) AS c1, + CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,2)) AS c2, + CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,3)) AS c3, + CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,4)) AS c4, + CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,5)) AS c5, + CAST(TIMESTAMP('2001-01-01', 8395959) AS DECIMAL(30,6)) AS c6; + +SELECT + TIMESTAMP('2001-01-01', 8395959) AS ci, + TIMESTAMP('2001-01-01', 8395959)+0 AS c0, + TIMESTAMP('2001-01-01', 8395959)+0.0 AS c1, + TIMESTAMP('2001-01-01', 8395959)+0.00 AS c2, + TIMESTAMP('2001-01-01', 8395959)+0.000 AS c3, + TIMESTAMP('2001-01-01', 8395959)+0.0000 AS c4, + TIMESTAMP('2001-01-01', 8395959)+0.00000 AS c5, + TIMESTAMP('2001-01-01', 8395959)+0.000000 AS c6, + TIMESTAMP('2001-01-01', 8395959)+0.0000000 AS c7; + +--echo # TIMESTAMP(string,string) + +SELECT + TIMESTAMP('2001-01-01', '839:59:59') AS ci, + TIMESTAMP('2001-01-01', '839:59:59.0') AS c1, + TIMESTAMP('2001-01-01', '839:59:59.00') AS c2, + TIMESTAMP('2001-01-01', '839:59:59.000') AS c3, + TIMESTAMP('2001-01-01', '839:59:59.0000') AS c4, + TIMESTAMP('2001-01-01', '839:59:59.00000') AS c5, + TIMESTAMP('2001-01-01', '839:59:59.000000') AS c6, + TIMESTAMP('2001-01-01', '839:59:59.0000000') AS c7; + +SELECT + MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59')) AS ci, + MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.0')) AS c1, + MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.00')) AS c2, + MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.000')) AS c3, + MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.0000')) AS c4, + MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.00000')) AS c5, + MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.000000')) AS c6, + MICROSECOND(TIMESTAMP('2001-01-01', '839:59:59.0000000')) AS c7; + +SELECT + CAST(TIMESTAMP('2001-01-01', '839:59:59') AS SIGNED) AS ci, + CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,0)) AS c0, + CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,1)) AS c1, + CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,2)) AS c2, + CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,3)) AS c3, + CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,4)) AS c4, + CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,5)) AS c5, + CAST(TIMESTAMP('2001-01-01', '839:59:59') AS DECIMAL(30,6)) AS c6; + +SELECT + TIMESTAMP('2001-01-01', '839:59:59') AS ci, + TIMESTAMP('2001-01-01', '839:59:59')+0 AS c0, + TIMESTAMP('2001-01-01', '839:59:59')+0.0 AS c1, + TIMESTAMP('2001-01-01', '839:59:59')+0.00 AS c2, + TIMESTAMP('2001-01-01', '839:59:59')+0.000 AS c3, + TIMESTAMP('2001-01-01', '839:59:59')+0.0000 AS c4, + TIMESTAMP('2001-01-01', '839:59:59')+0.00000 AS c5, + TIMESTAMP('2001-01-01', '839:59:59')+0.000000 AS c6, + TIMESTAMP('2001-01-01', '839:59:59')+0.0000000 AS c7; + +--horizontal_results + +--echo # Corner cases for TIMESTAMP(timestamp,xxx) + +--echo # HOUR is outside of supported INTERVAL DAYS TO SECONDS range +--echo # Expect NULL with INTERVAL warnings +CREATE TABLE t1 (a VARCHAR(64), b VARCHAR(64)); +INSERT INTO t1 VALUES ('4294967296:00:00', '178956970 16:00:00'); +INSERT INTO t1 VALUES ('4294967295:59:59', '178956970 15:59:59'); +INSERT INTO t1 VALUES ('4294967294:59:59', '178956970 14:59:59'); +INSERT INTO t1 VALUES ('87649416:00:00', '3652059 00:00:00'); +SELECT TIMESTAMP('0001-01-01 00:00:00', a) AS ta, TIMESTAMP('0001-01-01 00:00:00', b) AS tb FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(64), b VARCHAR(64)); +INSERT INTO t1 VALUES ('-4294967296:00:00', '-178956970 16:00:00'); +INSERT INTO t1 VALUES ('-4294967295:59:59', '-178956970 15:59:59'); +INSERT INTO t1 VALUES ('-4294967294:59:59', '-178956970 14:59:59'); +INSERT INTO t1 VALUES ('-87649416:00:00', '-3652059 00:00:00'); +SELECT TIMESTAMP('9999-12-31 23:59:59', a) AS ta, TIMESTAMP('9999-12-31 23:59:59.999999', b) AS tb FROM t1; +DROP TABLE t1; + +--echo # HOUR is OK +--echo # Expect max or near-max DATETIME value + no INTERVAL warnings +CREATE TABLE t1 (a VARCHAR(64), b VARCHAR(64)); +INSERT INTO t1 VALUES ('87649415:59:59.999999', '3652058 23:59:59.999999'); +INSERT INTO t1 VALUES ('87649415:59:59', '3652058 23:59:59'); +SELECT TIMESTAMP('0001-01-01 00:00:00', a) AS ta, TIMESTAMP('0001-01-01 00:00:00', b) AS tb FROM t1; +DROP TABLE t1; + +--echo # HOUR is OK +--echo # Expect near '0001-01-01 00:00:00' DATETIME value + no INTERVAL warnings +CREATE TABLE t1 (a VARCHAR(64), b VARCHAR(64)); +INSERT INTO t1 VALUES ('-87649415:59:59.999999', '-3652058 23:59:59.999999'); +INSERT INTO t1 VALUES ('-87649415:59:59', '-3652058 23:59:59'); +SELECT TIMESTAMP('9999-12-31 23:59:59', a) AS ta, TIMESTAMP('9999-12-31 23:59:59.999999', b) AS tb FROM t1; +DROP TABLE t1; + +--echo # HOUR is OK +--echo # Expect NULL on datetime arithmetic overflow + no INTERVAL warnings +CREATE TABLE t1 (a VARCHAR(64), b VARCHAR(64)); +INSERT INTO t1 VALUES ('-00:00:00.000001', '-0 00:00:00.000001'); +SELECT TIMESTAMP('0001-01-01 00:00:00', a) AS ta, TIMESTAMP('0001-01-01 00:00:00', b) AS tb FROM t1; +DROP TABLE t1; + + +--echo # Corner cases for ADDTIME(timestamp,xxx) +--vertical_results + +--echo # HOUR is outside of UINT_MAX32 range +--echo # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value" + +SELECT + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959) AS ci, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0 AS c0, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.0 AS c1, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.00 AS c2, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.000 AS c3, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.0000 AS c4, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.00000 AS c5, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.000000 AS c6, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672965959)+0.0000000 AS c7; + +SELECT + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59') AS ci, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0 AS c0, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.0 AS c1, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.00 AS c2, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.000 AS c3, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.0000 AS c4, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.00000 AS c5, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.000000 AS c6, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967296:59:59')+0.0000000 AS c7; + +## TODO: add '0001-01-01 00:00:00' + +--echo # HOUR UINT_MAX32 +--echo # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value" +SELECT + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959) AS ci, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0 AS c0, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.0 AS c1, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.00 AS c2, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.000 AS c3, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.0000 AS c4, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.00000 AS c5, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.000000 AS c6, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 42949672955959)+0.0000000 AS c7; + +SELECT + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59') AS ci, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0 AS c0, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.0 AS c1, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.00 AS c2, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.000 AS c3, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.0000 AS c4, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.00000 AS c5, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.000000 AS c6, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '4294967295:59:59')+0.0000000 AS c7; + +--echo # HOUR is max_useful_hour()+1 +--echo # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value" +SELECT + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959) AS ci, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0 AS c0, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.0 AS c1, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.00 AS c2, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.000 AS c3, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.0000 AS c4, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.00000 AS c5, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.000000 AS c6, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494165959)+0.0000000 AS c7; + +SELECT + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59') AS ci, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0 AS c0, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.0 AS c1, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.00 AS c2, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.000 AS c3, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.0000 AS c4, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.00000 AS c5, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.000000 AS c6, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649416:59:59')+0.0000000 AS c7; + +--echo # HOUR is max_useful_hour() +--echo # Expect NULL (calc_time_diff overflows ) + no INTERVAL warnings +SELECT + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959) AS ci, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0 AS c0, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.0 AS c1, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.00 AS c2, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.000 AS c3, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.0000 AS c4, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.00000 AS c5, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.000000 AS c6, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 876494155959)+0.0000000 AS c7; + +SELECT + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59') AS ci, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0 AS c0, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.0 AS c1, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.00 AS c2, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.000 AS c3, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.0000 AS c4, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.00000 AS c5, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.000000 AS c6, + ADDTIME(TIMESTAMP'2001-01-01 00:00:00', '87649415:59:59')+0.0000000 AS c7; + +--echo # HOUR is max_useful_hour() +--echo # Expect non-NULL + no warnings +SELECT + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959) AS ci, + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0 AS c0, + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.0 AS c1, + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.00 AS c2, + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.000 AS c3, + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.0000 AS c4, + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.00000 AS c5, + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.000000 AS c6, + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', 876494155959)+0.0000000 AS c7; + +SELECT + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59') AS ci, + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0 AS c0, + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.0 AS c1, + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.00 AS c2, + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.000 AS c3, + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.0000 AS c4, + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.00000 AS c5, + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.000000 AS c6, + ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59')+0.0000000 AS c7; +--horizontal_results + + +--echo # Corner cases for ADDTIME(time,xxx) +--vertical_results + +--echo # HOUR outside of UINT32 range +--echo # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value" +SELECT + ADDTIME(TIME'00:00:00', 42949672965959) AS ci, + ADDTIME(TIME'00:00:00', 42949672965959)+0 AS c0, + ADDTIME(TIME'00:00:00', 42949672965959)+0.0 AS c1, + ADDTIME(TIME'00:00:00', 42949672965959)+0.00 AS c2, + ADDTIME(TIME'00:00:00', 42949672965959)+0.000 AS c3, + ADDTIME(TIME'00:00:00', 42949672965959)+0.0000 AS c4, + ADDTIME(TIME'00:00:00', 42949672965959)+0.00000 AS c5, + ADDTIME(TIME'00:00:00', 42949672965959)+0.000000 AS c6, + ADDTIME(TIME'00:00:00', 42949672965959)+0.0000000 AS c7; + +SELECT + ADDTIME(TIME'00:00:00', '4294967296:59:59') AS ci, + ADDTIME(TIME'00:00:00', '4294967296:59:59')+0 AS c0, + ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.0 AS c1, + ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.00 AS c2, + ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.000 AS c3, + ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.0000 AS c4, + ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.00000 AS c5, + ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.000000 AS c6, + ADDTIME(TIME'00:00:00', '4294967296:59:59')+0.0000000 AS c7; + +--echo # HOUR is UINT_MAX32 (outside of INTERVAL DAY TO SECOND range) +--echo # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value" +SELECT + ADDTIME(TIME'00:00:00', 42949672955959) AS ci, + ADDTIME(TIME'00:00:00', 42949672955959)+0 AS c0, + ADDTIME(TIME'00:00:00', 42949672955959)+0.0 AS c1, + ADDTIME(TIME'00:00:00', 42949672955959)+0.00 AS c2, + ADDTIME(TIME'00:00:00', 42949672955959)+0.000 AS c3, + ADDTIME(TIME'00:00:00', 42949672955959)+0.0000 AS c4, + ADDTIME(TIME'00:00:00', 42949672955959)+0.00000 AS c5, + ADDTIME(TIME'00:00:00', 42949672955959)+0.000000 AS c6, + ADDTIME(TIME'00:00:00', 42949672955959)+0.0000000 AS c7; + +SELECT + ADDTIME(TIME'00:00:00', '4294967295:59:59') AS ci, + ADDTIME(TIME'00:00:00', '4294967295:59:59')+0 AS c0, + ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.0 AS c1, + ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.00 AS c2, + ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.000 AS c3, + ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.0000 AS c4, + ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.00000 AS c5, + ADDTIME(TIME'00:00:00', '4294967295:59:59')+0.000000 AS c6, + ADDTIME(TIME'00:00:00', '4294967295;00:00')+0.0000000 AS c7; + +--echo # HOUR is max_useful_hour()+1 (outside of INTERVAL DAY TO SECOND range) +--echo # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value" + +SELECT + ADDTIME(TIME'00:00:00', 876494165959) AS ci, + ADDTIME(TIME'00:00:00', 876494165959)+0 AS c0, + ADDTIME(TIME'00:00:00', 876494165959)+0.0 AS c1, + ADDTIME(TIME'00:00:00', 876494165959)+0.00 AS c2, + ADDTIME(TIME'00:00:00', 876494165959)+0.000 AS c3, + ADDTIME(TIME'00:00:00', 876494165959)+0.0000 AS c4, + ADDTIME(TIME'00:00:00', 876494165959)+0.00000 AS c5, + ADDTIME(TIME'00:00:00', 876494165959)+0.000000 AS c6, + ADDTIME(TIME'00:00:00', 876494165959)+0.0000000 AS c7; + +SELECT + ADDTIME(TIME'00:00:00', '87649416:59:59') AS ci, + ADDTIME(TIME'00:00:00', '87649416:59:59')+0 AS c0, + ADDTIME(TIME'00:00:00', '87649416:59:59')+0.0 AS c1, + ADDTIME(TIME'00:00:00', '87649416:59:59')+0.00 AS c2, + ADDTIME(TIME'00:00:00', '87649416:59:59')+0.000 AS c3, + ADDTIME(TIME'00:00:00', '87649416:59:59')+0.0000 AS c4, + ADDTIME(TIME'00:00:00', '87649416:59:59')+0.00000 AS c5, + ADDTIME(TIME'00:00:00', '87649416:59:59')+0.000000 AS c6, + ADDTIME(TIME'00:00:00', '87649416:59:59')+0.0000000 AS c7; + +--echo # HOUR is max_useful_hour()+1 (outside of INTERVAL DAY TO SECOND range) +--echo # Expect NULL + "Incorrect INTERVAL DAY TO SECOND value" + +SELECT + ADDTIME(TIME'-838:59:59', 876494165959) AS ci, + ADDTIME(TIME'-838:59:59.9', 876494165959) AS c1, + ADDTIME(TIME'-838:59:59.99', 876494165959) AS c2, + ADDTIME(TIME'-838:59:59.999', 876494165959) AS c3, + ADDTIME(TIME'-838:59:59.9999', 876494165959) AS c4, + ADDTIME(TIME'-838:59:59.99999', 876494165959) AS c5, + ADDTIME(TIME'-838:59:59.999999', 876494165959) AS c6; + +SELECT + ADDTIME(TIME'-838:59:59', '87649416:59:59') AS ci, + ADDTIME(TIME'-838:59:59.9', '87649416:59:59') AS c1, + ADDTIME(TIME'-838:59:59.99', '87649416:59:59') AS c2, + ADDTIME(TIME'-838:59:59.999', '87649416:59:59') AS c3, + ADDTIME(TIME'-838:59:59.9999', '87649416:59:59') AS c4, + ADDTIME(TIME'-838:59:59.99999', '87649416:59:59') AS c5, + ADDTIME(TIME'-838:59:59.999999', '87649416:59:59') AS c6; + +# This does not give a warning about nanosecond truncation in --ps runs +# so disable warnings +--disable_warnings +SELECT + ADDTIME(TIME'-838:59:59.9999999', '87649416:59:59') AS c7; +--enable_warnings + +--echo # HOUR is max_useful_hour() (inside INTERVAL DAY TO SECOND range) +--echo # Expect max TIME(0) + zero fraction + TIME warnings + no INTEVAL warnings +SELECT + ADDTIME(TIME'00:00:00', 876494155959) AS ci, + ADDTIME(TIME'00:00:00', 876494155959)+0 AS c0, + ADDTIME(TIME'00:00:00', 876494155959)+0.0 AS c1, + ADDTIME(TIME'00:00:00', 876494155959)+0.00 AS c2, + ADDTIME(TIME'00:00:00', 876494155959)+0.000 AS c3, + ADDTIME(TIME'00:00:00', 876494155959)+0.0000 AS c4, + ADDTIME(TIME'00:00:00', 876494155959)+0.00000 AS c5, + ADDTIME(TIME'00:00:00', 876494155959)+0.000000 AS c6, + ADDTIME(TIME'00:00:00', 876494155959)+0.0000000 AS c7; + +SELECT + ADDTIME(TIME'00:00:00', '87649415:59:59') AS ci, + ADDTIME(TIME'00:00:00', '87649415:59:59')+0 AS c0, + ADDTIME(TIME'00:00:00', '87649415:59:59')+0.0 AS c1, + ADDTIME(TIME'00:00:00', '87649415:59:59')+0.00 AS c2, + ADDTIME(TIME'00:00:00', '87649415:59:59')+0.000 AS c3, + ADDTIME(TIME'00:00:00', '87649415:59:59')+0.0000 AS c4, + ADDTIME(TIME'00:00:00', '87649415:59:59')+0.00000 AS c5, + ADDTIME(TIME'00:00:00', '87649415:59:59')+0.000000 AS c6, + ADDTIME(TIME'00:00:00', '87649415:59:59')+0.0000000 AS c7; + + +--echo # HOUR is max_useful_hour() (inside INTERVAL DAY TO SECOND range) +--echo # Expect max TIME(N) + TIME warnings + no INTERVAL warnings + +SELECT + ADDTIME(TIME'-838:59:59', 876494155959) AS ci, + ADDTIME(TIME'-838:59:59.9', 876494155959) AS c1, + ADDTIME(TIME'-838:59:59.99', 876494155959) AS c2, + ADDTIME(TIME'-838:59:59.999', 876494155959) AS c3, + ADDTIME(TIME'-838:59:59.9999', 876494155959) AS c4, + ADDTIME(TIME'-838:59:59.99999', 876494155959) AS c5, + ADDTIME(TIME'-838:59:59.999999', 876494155959) AS c6; + +SELECT + ADDTIME(TIME'-838:59:59', '87649415:59:59') AS ci, + ADDTIME(TIME'-838:59:59.9', '87649415:59:59') AS c1, + ADDTIME(TIME'-838:59:59.99', '87649415:59:59') AS c2, + ADDTIME(TIME'-838:59:59.999', '87649415:59:59') AS c3, + ADDTIME(TIME'-838:59:59.9999', '87649415:59:59') AS c4, + ADDTIME(TIME'-838:59:59.99999', '87649415:59:59') AS c5, + ADDTIME(TIME'-838:59:59.999999', '87649415:59:59') AS c6; + +# This does not give a warning about nanosecond truncation in --ps runs +# so disable warnings +--disable_warnings +SELECT + ADDTIME(TIME'-838:59:59.9999999', '87649415:59:59') AS c7; +--enable_warnings + +--horizontal_results + + +--echo # +--echo # MDEV-17400 The result of TIME('42949672965959-01') depends on architecture +--echo # + +SELECT TIME('42949672955959-01'), TIME('42949672965959-01'); +SELECT TIME('18446744073709551615-01'), TIME('18446744073709551616-01'); + +--echo # +--echo # MDEV-17434 EXTRACT(DAY FROM negative_time) returns wrong result +--echo # + +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 VALUES ('-24:10:10.10'); +SELECT + EXTRACT(MINUTE FROM a), + EXTRACT(SECOND FROM a), + EXTRACT(MICROSECOND FROM a), + EXTRACT(DAY FROM a), + EXTRACT(DAY_HOUR FROM a), + EXTRACT(DAY_MINUTE FROM a), + EXTRACT(DAY_SECOND FROM a), + EXTRACT(DAY_MICROSECOND FROM a) +FROM t1; +CREATE TABLE t2 AS +SELECT + EXTRACT(MINUTE FROM a), + EXTRACT(SECOND FROM a), + EXTRACT(MICROSECOND FROM a), + EXTRACT(DAY FROM a), + EXTRACT(DAY_HOUR FROM a), + EXTRACT(DAY_MINUTE FROM a), + EXTRACT(DAY_SECOND FROM a), + EXTRACT(DAY_MICROSECOND FROM a) +FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; + + +--echo # +--echo # MDEV-17478 Wrong result for TIME('+100:20:30') +--echo # + +SELECT TIME('+100:20:30'); + +--echo # +--echo # MDEV-17477 Wrong result for TIME('-2001-01-01 10:20:30') +--echo # + +SELECT TIME('-2001-01-01 10:20:30'); +SELECT TIME('2001-01-01') AS c1, TIME('2001-01-01 ') AS c2; +SELECT TIME('0001:01:01 '), TIME('0001:01:01 '); +SELECT TIME('1 2'), TIME('1 2 '); + +SELECT TIME('2001-01-01T'), TIME('2001-01-01T '); +SELECT TIME('901-01-01T1'), TIME('901-01-01T10'); +SELECT TIME('091-01-01T1'), TIME('091-01-01T10'); + +SELECT TIME('0001:01:01x'), TIME('0001:01:01xx'); +SELECT TIME('0001:01:01.'), TIME('0001:01:01..'); +SELECT TIME('0001:01:01-'), TIME('0001:01:01--'); +SELECT TIME('0001:01:01-'), TIME('0001:01:01--'); + +SELECT TIME('-xxx'), TIME('-xxxxxxxxxxxxxxxxxxxx'); +SELECT TIME('- '), TIME('- '); +SELECT TIME('-'), TIME('-'); +SELECT TIME('1-1-1 1:1:1'), TIME('1-1-1 1:1:1.0'); +SELECT TIME('1-1-1 1:2:3'), TIME('1-1-1 1:2:3.0'); + +SELECT + CAST('20050326112233 garbage' as datetime), + CAST('20050326 garbage' as date), + CAST('50326 garbage' as time); + +SELECT TIME('- 01:00:00'), TIME('- 1 01:00:00'); |