Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
-
None
Description
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.
Attachments
Issue Links
- blocks
-
MDEV-4912 Data type plugin API version 1
-
- Closed
-
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-