[MDEV-10817] CAST(MAX(DATE'2001-01-01') AS TIME) returns a wrong result Created: 2016-09-16  Updated: 2017-11-08  Resolved: 2017-11-07

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 10.0, 10.1, 10.2
Fix Version/s: 10.2.11, 10.3.3

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Sprint: 10.2.11

 Description   

CAST from DATE to TIME usually returns zero time 00:00:00:

SELECT CAST(DATE'2001-01-01' AS TIME)


+--------------------------------+
 
| CAST(DATE'2001-01-01' AS TIME) |
 
+--------------------------------+
 
| 00:00:00                       |
 
+--------------------------------+

However, in some cases it works differently:

SELECT CAST(MAX(DATE'2001-01-01') AS TIME);
 
SHOW WARNINGS;


+-------------------------------------+
 
| CAST(MAX(DATE'2001-01-01') AS TIME) |
 
+-------------------------------------+
 
| 00:20:01                            |
 
+-------------------------------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
+---------+------+----------------------------------------------+
 
| Level   | Code | Message                                      |
 
+---------+------+----------------------------------------------+
 
| Warning | 1292 | Truncated incorrect time value: '2001-01-01' |
 
+---------+------+----------------------------------------------+
 
1 row in set (0.00 sec)

The problem happens because Item_sum_max does not implement its own get_date() method, so execution goes through the generic Item::get_date() which calls val_str() followed by string-to-time conversion.

 Comments   
Comment by Alexander Barkov [ 2016-09-16 ]

Also repeatable in this scenario:

DROP FUNCTION IF EXISTS f1;
 
CREATE FUNCTION f1() RETURNS DATE RETURN DATE'2001-01-01';
 
SELECT CAST(f1() AS TIME);

Generated at Thu Feb 08 07:45:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.