diff options
Diffstat (limited to 'mysql-test/t/func_time.test')
| -rw-r--r-- | mysql-test/t/func_time.test | 139 |
1 files changed, 134 insertions, 5 deletions
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 8323bd30d2c..1f1f3a29574 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -526,7 +526,7 @@ SELECT MAKETIME(0, 0, 4294967296); SELECT MAKETIME(CAST(-1 AS UNSIGNED), 0, 0); # check if EXTRACT() handles out-of-range values correctly -SELECT EXTRACT(HOUR FROM '100000:02:03'); +SELECT EXTRACT(HOUR FROM '10000:02:03'); # check if we get proper warnings if both input string truncation # and out-of-range value occur @@ -883,7 +883,9 @@ SELECT STR_TO_DATE(SPACE(2),'1'); --echo # SET GLOBAL SQL_MODE=''; +--disable_warnings DO STR_TO_DATE((''), FROM_DAYS(@@GLOBAL.SQL_MODE)); +--enable_warnings SET GLOBAL SQL_MODE=DEFAULT; --echo # @@ -1119,7 +1121,9 @@ drop table t1; # # lp:731815 Crash/valgrind warning Item::send with 5.1-micro # +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); select convert_tz(timediff('0000-00-00 00:00:00', cast('2008-03-26 07:09:06' as datetime)), 'UTC', 'Europe/Moscow'); +SET timestamp=DEFAULT; # # lp:736370 Datetime functions in subquery context cause wrong result and bogus warnings in mysql-5.1-micr @@ -1140,7 +1144,11 @@ drop table t1; # # lp:737092 Assertion `item->null_value' failed in get_datetime_value in 5.1-micro # +SET timestamp=UNIX_TIMESTAMP('2014-04-14 10:10:10'); +select now() > coalesce(time('21:43:24'), date('2010-05-03')); +SET timestamp=UNIX_TIMESTAMP('2014-04-14 22:22:22'); select now() > coalesce(time('21:43:24'), date('2010-05-03')); +SET timestamp=DEFAULT; # # lp:737104 Crash in DTCollation::set in 5.1-micro @@ -1184,9 +1192,12 @@ insert into t1 values ('0000-00-00 00:00:00'); select cast(f1 AS time) from t1; drop table t1; +SET timestamp=UNIX_TIMESTAMP('2014-06-01 10:20:30'); select greatest(cast("0-0-0" as date), cast("10:20:05" as time)); select greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '0000-00-00'; +select greatest(cast("0-0-0" as date), cast("10:20:05" as time)) = '2014-06-01'; select cast(greatest(cast("0-0-0" as date), cast("10:20:05" as time)) as datetime(6)); +SET timestamp=DEFAULT; select microsecond('12:00:00.123456'), microsecond('2009-12-31 23:59:59.000010'); @@ -1388,6 +1399,7 @@ DROP TABLE t1; --echo # --echo # MDEV-4870 Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types --echo # +SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); 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; @@ -1407,7 +1419,7 @@ 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; - +SET timestamp=DEFAULT; --echo # --echo # MDEV-4724 Some temporal functions do not preserve microseconds @@ -1521,12 +1533,14 @@ SHOW COLUMNS FROM t1; SELECT * FROM t1; DROP TABLE t1; +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); 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; +SET timestamp=DEFAULT; --echo # --echo # MDEV-4869 Wrong result of MAKETIME(0, 0, -0.1) @@ -1536,6 +1550,7 @@ SELECT MAKETIME(0, 0, -0.1); --echo # --echo # MDEV-4857 Wrong result of HOUR('1 00:00:00') --echo # +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); SELECT HOUR('1 02:00:00'), HOUR('26:00:00'); SELECT HOUR(TIME'1 02:00:00'), HOUR(TIME'26:00:00'); SELECT HOUR(TIME('1 02:00:00')), HOUR(TIME('26:00:00')); @@ -1545,6 +1560,7 @@ SELECT EXTRACT(HOUR FROM '1 02:00:00'), EXTRACT(HOUR FROM '26:00:00'); SELECT EXTRACT(HOUR FROM TIME'1 02:00:00'), EXTRACT(HOUR FROM TIME'26:00:00'); SELECT EXTRACT(HOUR FROM TIME('1 02:00:00')), EXTRACT(HOUR FROM TIME('26:00:00')); SELECT EXTRACT(DAY FROM TIME('1 02:00:00')), EXTRACT(DAY FROM TIME('26:00:00')); +SET timestamp=DEFAULT; --echo # --echo # MDEV-5458 RQG hits 'sql/tztime.cc:799: my_time_t sec_since_epoch(int, int, int, int, int, int): Assertion `mon > 0 && mon < 13' failed.' @@ -1557,7 +1573,6 @@ SELECT GREATEST(t, CURRENT_DATE()) FROM t1; DROP TABLE t1; SET TIMESTAMP=DEFAULT; - --echo # --echo # MDEV-5504 Server crashes in String::length on SELECT with MONTHNAME, GROUP BY, ROLLUP --echo # @@ -1571,6 +1586,61 @@ DROP TABLE t1; --echo # SELECT DATE_ADD('2001-01-01 10:20:30',INTERVAL 250000000000.0 SECOND) AS c1, DATE_ADD('2001-01-01 10:20:30',INTERVAL 2000000000000000000.0 SECOND) AS c2; +--echo # +--echo # MDEV-4838 Wrong metadata for DATE_ADD('string', INVERVAL) +--echo # +--enable_metadata +SELECT DATE_ADD('2011-01-02 12:13:14', INTERVAL 1 MINUTE); +--disable_metadata + +--echo # +--echo # MDEV-5450 Assertion `cached_field_ type == MYSQL_TYPE_STRING || ltime.time_type == MYSQL_TIMESTAMP_NONE || mysql_type_to_time_type(cached_field_type) == ltime.time_type' fails with IF, ISNULL, ADDDATE +--echo # + +CREATE TABLE t1 (a DATETIME, b DATE); +INSERT INTO t1 VALUES (NULL, '2012-12-21'); +SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1; +SELECT CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) FROM t1; +SELECT CAST(ADDDATE(COALESCE(a,b),0) AS CHAR) FROM t1; +SELECT CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; +SELECT IF(1,ADDTIME(IFNULL(a,b),0),1) FROM t1; +SELECT CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) FROM t1; +SELECT CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) FROM t1; +SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; +DROP TABLE t1; + +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); +CREATE TABLE t1 (a DATETIME, b TIME); +INSERT INTO t1 VALUES (NULL, '00:20:12'); +SELECT IF(1,ADDDATE(IFNULL(a,b),0),1) FROM t1; +SELECT CAST(ADDDATE(IFNULL(a,b),0) AS CHAR) FROM t1; +SELECT CAST(ADDDATE(COALESCE(a,b),0) AS CHAR) FROM t1; +SELECT CAST(ADDDATE(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; +SELECT IF(1,ADDTIME(IFNULL(a,b),0),1) FROM t1; +SELECT CAST(ADDTIME(IFNULL(a,b),0) AS CHAR) FROM t1; +SELECT CAST(ADDTIME(COALESCE(a,b),0) AS CHAR) FROM t1; +SELECT CAST(ADDTIME(CASE WHEN 0 THEN a ELSE b END,0) AS CHAR) FROM t1; +DROP TABLE t1; +SET timestamp=DEFAULT; + + +--echo # +--echo # MDEV-5870 Assertion `ltime->neg == 0' fails with COALESCE, ADDDATE, MAKEDATE +--echo # +CREATE TABLE t1 (dt DATETIME); +INSERT INTO t1 VALUES ('2003-05-13 19:36:05'), ('2012-12-12 09:20:06'); +SELECT COALESCE(ADDDATE(MAKEDATE(2011,121), dt), '2006-09-12' ) FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-6101 Hybrid functions do not add CURRENT_DATE when converting TIME to DATETIME +--echo # +SET timestamp=UNIX_TIMESTAMP('2014-04-15 01:02:03'); +SELECT IF(1,TIME'10:20:30',DATE'2001-01-01'); +SELECT IFNULL(TIME'10:20:30',DATE'2001-01-01'); +SELECT CASE WHEN 1 THEN TIME'10:20:30' ELSE DATE'2001-01-01' END; +SELECT COALESCE(TIME'10:20:30',DATE'2001-01-01'); +SET timestamp=DEFAULT; --echo # --echo # MDEV-5750 Assertion `ltime->year == 0' fails on a query with EXTRACT DAY_MINUTE and TIME column @@ -1603,7 +1673,6 @@ DROP TABLE t1; --echo # SELECT 1 MOD ADDTIME( '13:58:57', '00:00:01' ) + 2; - --echo # --echo # MDEV-11819 NO_ZERO_IN_DATE: Incorrect generated column value --echo # @@ -1626,10 +1695,70 @@ DO TO_DAYS(SEC_TO_TIME(TIME(CEILING(UUID())))); --enable_warnings DO TO_DAYS(SEC_TO_TIME(MAKEDATE('',RAND(~(''))))); -SELECT TO_DAYS(SEC_TO_TIME(MAKEDATE(0,RAND(~0)))); SELECT SEC_TO_TIME(MAKEDATE(0,RAND(~0))); --echo # --echo # End of 5.5 tests --echo # + +--echo # +--echo # MDEV-8205 timediff returns null when comparing decimal time to time string value +--echo # + +# 1h difference +SELECT + TIMEDIFF('2014-01-01 00:00:00' , '2014-01-01 01:00:00' ) AS str_str, + TIMEDIFF('2014-01-01 00:00:00' , 20140101010000.000 ) AS str_dec, + TIMEDIFF(20140101000000.000 , 20140101010000.000 ) AS dec_dec, + TIMEDIFF(20140101000000.000 , '2014-01-01 01:00:00' ) AS dec_str; + +# 1D1h difference +SELECT + TIMEDIFF('2014-01-01 00:00:00' , '2014-01-02 01:00:00' ) AS str_str, + TIMEDIFF('2014-01-01 00:00:00' , 20140102010000.000 ) AS str_dec, + TIMEDIFF(20140101000000.000 , 20140102010000.000 ) AS dec_dec, + TIMEDIFF(20140101000000.000 , '2014-01-02 01:00:00' ) AS dec_str; + +# 1M1D1h difference +SELECT + TIMEDIFF('2014-01-01 00:00:00' , '2014-02-02 01:00:00' ) AS str_str, + TIMEDIFF('2014-01-01 00:00:00' , 20140202010000.000 ) AS str_dec, + TIMEDIFF(20140101000000.000 , 20140202010000.000 ) AS dec_dec, + TIMEDIFF(20140101000000.000 , '2014-02-02 01:00:00' ) AS dec_str; + +# 2M1D1h difference +SELECT + TIMEDIFF('2014-01-01 00:00:00' , '2014-03-02 01:00:00' ) AS str_str, + TIMEDIFF('2014-01-01 00:00:00' , 20140302010000.000 ) AS str_dec, + TIMEDIFF(20140101000000.000 , 20140302010000.000 ) AS dec_dec, + TIMEDIFF(20140101000000.000 , '2014-03-02 01:00:00' ) AS dec_str; + + +--echo # +--echo # MDEV-10787 Assertion `ltime->neg == 0' failed in void date_to_datetime(MYSQL_TIME*) +--echo # +CREATE TABLE t1 (d DATE); +INSERT INTO t1 VALUES ('2005-07-20'),('2012-12-21'); +SELECT REPLACE( ADDDATE( d, INTERVAL 0.6732771076944444 HOUR_SECOND ), '2', 'x' ) FROM t1; +SELECT REPLACE( ADDDATE( d, INTERVAL '0.6732771076944444' HOUR_SECOND ), '2', 'x' ) FROM t1; +SELECT CAST(ADDDATE( d, INTERVAL 6732771076944444 SECOND) AS CHAR) FROM t1; +SELECT CAST(ADDDATE( d, INTERVAL '67327710769444:44' HOUR_SECOND) AS CHAR) FROM t1; +SELECT CAST(ADDDATE( d, INTERVAL '673277107694:44:44' HOUR_SECOND) AS CHAR) FROM t1; +DROP TABLE t1; + +# Maximum possible DAY_SECOND values in various formats +SELECT ADDDATE(DATE'0000-01-01', INTERVAL '3652423:23:59:59' DAY_SECOND); +SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:87658175:59:59' DAY_SECOND); +SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:0:5259490559:59' DAY_SECOND); +SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:0:0:315569433599' DAY_SECOND); + +# Out-of-range INTERVAL DAY_SECOND values +SELECT ADDDATE(DATE'0000-01-01', INTERVAL '3652423:0:0:315569433559' DAY_SECOND); +SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:87658175:0:315569433559' DAY_SECOND); +SELECT ADDDATE(DATE'0000-01-01', INTERVAL '0:0:5259490559:315569433599' DAY_SECOND); + + +--echo # +--echo # End of 10.0 tests +--echo # |