|
SELECT CAST(DATE'2001-01-01' AS TIME), CAST(LAST_VALUE(DATE'2001-01-01') AS TIME);
|
+--------------------------------+--------------------------------------------+
|
| CAST(DATE'2001-01-01' AS TIME) | CAST(LAST_VALUE(DATE'2001-01-01') AS TIME) |
|
+--------------------------------+--------------------------------------------+
|
| 00:00:00 | 00:20:01 |
|
+--------------------------------+--------------------------------------------+
|
1 row in set, 1 warning (0.00 sec)
|
The expected behavior would be to return '00:00:00' in both columns.
The same problem is repeatable with NAME_CONST:
SELECT CAST(NAME_CONST('name',DATE'2001-01-01') AS TIME);
|
+---------------------------------------------------+
|
| CAST(NAME_CONST('name',DATE'2001-01-01') AS TIME) |
|
+---------------------------------------------------+
|
| 00:20:01 |
|
+---------------------------------------------------+
|
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
The same problem is repeatable with SP variables:
DELIMITER $$
|
BEGIN NOT ATOMIC
|
DECLARE a DATE DEFAULT '2001-01-01';
|
SELECT CAST(a AS TIME);
|
END;
|
$$
|
DELIMITER ;
|
+-----------------+
|
| CAST(a AS TIME) |
|
+-----------------+
|
| 00:20:01 |
|
+-----------------+
|
1 row in set (0.00 sec)
|
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
The problem is that the underlying classes Item_func_last_value, Item_name_const, Item_splocal do not implement their own version of the virtual method get_date(), and the default implementation in Item::get_date() does not handle conversion between different temporal data types.
So instead of doing direct date->time conversion, execution goes throw two conversions: date->string->time.
The same problem is repeatable with Window functions, both with CAST(AS TIME), and with functions that expect a TIME inpute, like HOUR():
CREATE OR REPLACE TABLE t1 (dt DATE,country VARCHAR(10), amount INT);
|
INSERT INTO t1 VALUES ('2000-01-01','DE',102);
|
INSERT INTO t1 VALUES ('2000-01-02','DE',101);
|
SELECT
|
dt, country, amount,
|
FIRST_VALUE(dt) OVER () AS first,
|
MINUTE(FIRST_VALUE(dt) OVER ()) AS m_first,
|
LAST_VALUE(dt) OVER () AS last,
|
MINUTE(LAST_VALUE(dt) OVER ()) AS m_last
|
FROM t1
|
ORDER BY country, dt;
|
+------------+---------+--------+------------+---------+------------+--------+
|
| dt | country | amount | first | m_first | last | m_last |
|
+------------+---------+--------+------------+---------+------------+--------+
|
| 2000-01-01 | DE | 102 | 2000-01-01 | 20 | 2000-01-02 | 20 |
|
| 2000-01-02 | DE | 101 | 2000-01-01 | 20 | 2000-01-02 | 20 |
|
+------------+---------+--------+------------+---------+------------+--------+
|
2 rows in set, 4 warnings (0.00 sec)
|
Notice, the result of HOUR() is 20, which is something unexpected. It should be 0.
|