From aef530bb6955d8c13a1ff9c5624c74fefa68943c Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Mon, 19 Feb 2018 23:41:01 +0400 Subject: MDEV-15340 Wrong result HOUR(case_expression_with_time_and_datetime) The problem was that Item_func_hybrid_field_type::get_date() did not convert the result to the correct data type, so MYSQL_TIME::time_type of the get_date() result could be not in sync with field_type(). Changes: 1. Adding two new classes Datetime and Date to store MYSQL_TIMESTAMP_DATETIME and MYSQL_TIMESTAMP_DATE values respectively (in addition to earlier added class Time, for MYSQL_TIMESTAMP_TIME values). 2. Adding Item_func_hybrid_field_type::time_op(). It performs the operation using TIME representation, and always returns a MYSQL_TIME value with time_type=MYSQL_TIMESTAMP_TIME. Implementing time_op() for all affected children classes. 3. Fixing all implementations of date_op() to perform the operation using strictly DATETIME representation. Now they always return a MYSQL_TIME value with time_type=MYSQL_TIMESTAMP_{DATE|DATETIME}, according to the result data type. 4. Removing assignment of ltime.time_type to mysql_timestamp_type() from all val_xxx_from_date_op(), because now date_op() makes sure to return a proper MYSQL_TIME value with a good time_type (and other member) 5. Adding Item_func_hybrid_field_type::val_xxx_from_time_op(). 6. Overriding Type_handler_time_common::Item_func_hybrid_field_type_val_xxx() to call val_xxx_from_time_op() instead of val_xxx_from_date_op(). 7. Modified Item_func::get_arg0_date() to return strictly a TIME value if TIME_TIME_ONLY is passed, or return strictly a DATETIME value otherwise. If args[0] returned a value of a different temporal type, (for example a TIME value when TIME_TIME_ONLY was not passed, or a DATETIME value when TIME_TIME_ONLY was passed), the conversion is automatically applied. Earlier, get_arg0_date() did not guarantee a result in accordance to TIME_TIME_ONLY flag. --- mysql-test/t/func_time.test | 30 ++++++++++++++++++++++++++++++ 1 file changed, 30 insertions(+) (limited to 'mysql-test/t/func_time.test') diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 09c13598cfd..0066d4a434b 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -1945,3 +1945,33 @@ SELECT CONVERT_TZ(ROW(1,1),1,1); SELECT CONVERT_TZ(1, ROW(1,1), 1); --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION SELECT CONVERT_TZ(1, 1, ROW(1,1)); + + +--echo # +--echo # MDEV-15340 Wrong result HOUR(case_expression_with_time_and_datetime) +--echo # + +SET TIMESTAMP=UNIX_TIMESTAMP('2018-02-17 01:02:03'); +SELECT + COALESCE(TIME'800:00:00', NOW()) AS c, + HOUR(COALESCE(TIME'800:00:00',NOW())) AS hc; + +SELECT + CASE WHEN TRUE THEN TIME'800:00:00' ELSE NOW() END AS c, + HOUR(CASE WHEN TRUE THEN TIME'800:00:00' ELSE NOW() END) AS hc; + +SELECT + IFNULL(TIME'800:00:00', NOW()) AS c, + HOUR(IFNULL(TIME'800:00:00', NOW())) AS hc; + +SELECT + IF(TRUE,TIME'800:00:00', NOW()) AS c, + HOUR(IF(TRUE,TIME'800:00:00', NOW())) AS hc; + +SELECT + ADDTIME(TIME'10:20:30', TIMESTAMP'2001-01-01 00:00:00') AS c1, + ADDTIME(TIME'10:20:30', COALESCE(TIMESTAMP'2001-01-01 00:00:00',TIMESTAMP'2001-01-01 00:00:00')) AS c2, + ADDTIME(TIME'10:20:30', DATE'2001-01-01') AS c3, + ADDTIME(TIME'10:20:30', COALESCE(DATE'2001-01-01',TIMESTAMP'2001-01-01 00:00:00')) AS c4; + +SET TIMESTAMP=DEFAULT; -- cgit v1.2.1