|
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.
|