We're updating the issue view to help you get more done.Learn more

Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types

DROP TABLE IF EXISTS t1,t2; 
CREATE TABLE t1 (dt2 DATETIME(2), t3 TIME(3), d DATE);
INSERT INTO t1 VALUES ('2001-01-01 00:00:00.12', '00:00:00.567', '2002-01-01');
SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1;
CREATE TABLE t2 AS SELECT CASE WHEN 0 THEN dt2 ELSE t3 END FROM t1;
SELECT * FROM t2;
SHOW COLUMNS FROM t2;
+----------------------------------+
| CASE WHEN 0 THEN dt2 ELSE t3 END |
+----------------------------------+
| NULL                             |
+----------------------------------+
+----------------------------------+
| CASE WHEN 0 THEN dt2 ELSE t3 END |
+----------------------------------+
| 0000-00-00 00:00:00.000          |
+----------------------------------+
+----------------------------------+-------------+------+-----+---------+-------+
| Field                            | Type        | Null | Key | Default | Extra |
+----------------------------------+-------------+------+-----+---------+-------+
| CASE WHEN 0 THEN dt2 ELSE t3 END | datetime(3) | YES  |     | NULL    |       |
+----------------------------------+-------------+------+-----+---------+-------

Notice, CASE correctly creates a DATETIME(3) column.
However, it returns wrong values from both SELECT queries.
The expected value is:
'0000-00-00 '00:00:00.567'

More examples:

SELECT CASE WHEN 1 THEN dt2 ELSE t3 END FROM t1;
+----------------------------------+
| CASE WHEN 1 THEN dt2 ELSE t3 END |
+----------------------------------+
| 2001-01-01 00:00:00.120          |
+----------------------------------+
SELECT CONCAT(CASE WHEN 1 THEN dt2 ELSE t3 END) FROM t1;
+------------------------------------------+
| CONCAT(CASE WHEN 1 THEN dt2 ELSE t3 END) |
+------------------------------------------+
| 2001-01-01 00:00:00.12                   |
+------------------------------------------+

The expected result is '2001-01-01 00:00:00.120' for both queries.

SELECT CONCAT(CASE WHEN 0 THEN t3 ELSE dt2 END) FROM t1;
+------------------------------------------+
| CONCAT(CASE WHEN 0 THEN t3 ELSE dt2 END) |
+------------------------------------------+
| 2001-01-01 00:00:00.12                   |
+------------------------------------------+

The expected result is '2001-01-01 00:00:00.120'.

SELECT CONCAT(CASE WHEN 1 THEN d ELSE t3 END) FROM t1;
+----------------------------------------+
| CONCAT(CASE WHEN 1 THEN d ELSE t3 END) |
+----------------------------------------+
| 2002-01-01                             |
+----------------------------------------+

The expected result is '2002-01-01 00:00:00'.

SELECT CASE WHEN 1 THEN t3 ELSE d END FROM t1;
+--------------------------------+
| CASE WHEN 1 THEN t3 ELSE d END |
+--------------------------------+
| NULL                           |
+--------------------------------+
SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '00:00:00.567' |
+---------+------+------------------------------------------+

The expected result is '0000-00-00 00:00:00.567', without a warning.

A similar problem is observed with COALESCE:

SELECT COALESCE(d, t3) FROM t1;
+-------------------------+
| COALESCE(d, t3)         |
+-------------------------+
| 2002-01-01 00:00:00.000 |
+-------------------------+
SELECT CONCAT(COALESCE(d, t3)) FROM t1;
+-------------------------+
| CONCAT(COALESCE(d, t3)) |
+-------------------------+
| 2002-01-01              |
+-------------------------+

The expected result is '2002-01-01 00:00:00.000' for both queries.

SELECT COALESCE(dt2, t3) FROM t1;
+-------------------------+
| COALESCE(dt2, t3)       |
+-------------------------+
| 2001-01-01 00:00:00.120 |
+-------------------------+
SELECT CONCAT(COALESCE(dt2, t3)) FROM t1;
+---------------------------+
| CONCAT(COALESCE(dt2, t3)) |
+---------------------------+
| 2001-01-01 00:00:00.12    |
+---------------------------+

The expected result is '2001-01-01 00:00:00.120' for both queries.

A similar problems is observer with IFNULL:

SELECT IFNULL(dt2, t3), CONCAT(IFNULL(dt2, t3)) FROM t1;
+-------------------------+-------------------------+
| IFNULL(dt2, t3)         | CONCAT(IFNULL(dt2, t3)) |
+-------------------------+-------------------------+
| 2001-01-01 00:00:00.120 | 2001-01-01 00:00:00.12  |
+-------------------------+-------------------------+

The expected value is '2001-01-01 00:00:00.120' for both expressions.

SELECT IFNULL(d, t3), CONCAT(IFNULL(d, t3)) FROM t1;
+-------------------------+-----------------------+
| IFNULL(d, t3)           | CONCAT(IFNULL(d, t3)) |
+-------------------------+-----------------------+
| 2002-01-01 00:00:00.000 | 2002-01-01            |
+-------------------------+-----------------------+

The expected value is '2002-01-01 00:00:00.000' for both expressions.

Status