[MDEV-15340] Wrong result HOUR(case_expression_with_time_and_datetime) Created: 2018-02-17  Updated: 2018-08-03  Resolved: 2018-02-19

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

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

Issue Links:
Blocks
blocks MDEV-4912 Data type plugin API version 1 Closed
blocks MDEV-8894 Inserting fractional seconds into My... Closed
Duplicate
is duplicated by MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'0... Closed

 Description    

SELECT
 
  COALESCE(TIME'800:00:00', NOW()) AS c,
 
  HOUR(COALESCE(TIME'800:00:00',NOW())) AS hc;


+---------------------+------+
 
| c                   | hc   |
 
+---------------------+------+
 
| 2018-03-22 08:00:00 |  800 |
 
+---------------------+------+

The result for hc is wrong. It returns HOURS() of the first argument of COALESCE() before its conversion to DATETIME. The expected result is to return hours after conversion, i,e. 8, like the hour part in the first column c.

The same problem is repeatable with CASE and its abbreviations:

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;



 Comments   
Comment by Alexander Barkov [ 2018-02-19 ]

A related problem:

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;


+------+------+----------+----------+
 
| c1   | c2   | c3       | c4       |
 
+------+------+----------+----------+
 
| NULL | NULL | 34:20:30 | 34:20:30 |
 
+------+------+----------+----------+

The result for c3 and c4 is wrong. The expected result is NULL for all four columns.

Comment by Alexander Barkov [ 2018-02-19 ]

Pushed to bb-10.2-ext

Generated at Thu Feb 08 08:20:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.