Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
10.0.3, 5.5.32, 5.3.12
-
None
-
None
Description
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.
Attachments
Issue Links
- duplicates
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Summary | CASE on a combination of different temporal types returns wrong values | CASE, COALESCE on a combination of different temporal types returns wrong values |
| Description |
{code} 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 | | +----------------------------------+-------------+------+-----+---------+------- {code} 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: {code} 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 | +------------------------------------------+ {code} The expected result is '2001-01-01 00:00:00.12' for both queries. {code} 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 | +------------------------------------------+ {code} The expected result is '2001-01-01 00:00:00.120'. {code} SELECT CONCAT(CASE WHEN 1 THEN d ELSE t3 END) FROM t1; +----------------------------------------+ | CONCAT(CASE WHEN 1 THEN d ELSE t3 END) | +----------------------------------------+ | 2002-01-01 | +----------------------------------------+ {code} The expected result is '2002-01-01 00:00:00'. {code} 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' | +---------+------+------------------------------------------+ {code} The expected result is '0000-00-00 00:00:00.567', without a warning. |
{code} 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 | | +----------------------------------+-------------+------+-----+---------+------- {code} 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: {code} 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 | +------------------------------------------+ {code} The expected result is '2001-01-01 00:00:00.12' for both queries. {code} 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 | +------------------------------------------+ {code} The expected result is '2001-01-01 00:00:00.120'. {code} SELECT CONCAT(CASE WHEN 1 THEN d ELSE t3 END) FROM t1; +----------------------------------------+ | CONCAT(CASE WHEN 1 THEN d ELSE t3 END) | +----------------------------------------+ | 2002-01-01 | +----------------------------------------+ {code} The expected result is '2002-01-01 00:00:00'. {code} 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' | +---------+------+------------------------------------------+ {code} The expected result is '0000-00-00 00:00:00.567', without a warning. A similar problem is observed with COALESCE: {code} 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 | +-------------------------+ {code} The expected result is '2002-01-01 00:00:00.000' for both queries. {code} 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 | +---------------------------+ {code} The expected result is '2001-01-01 00:00:00.120' for both queries. |
| Description |
{code} 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 | | +----------------------------------+-------------+------+-----+---------+------- {code} 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: {code} 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 | +------------------------------------------+ {code} The expected result is '2001-01-01 00:00:00.12' for both queries. {code} 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 | +------------------------------------------+ {code} The expected result is '2001-01-01 00:00:00.120'. {code} SELECT CONCAT(CASE WHEN 1 THEN d ELSE t3 END) FROM t1; +----------------------------------------+ | CONCAT(CASE WHEN 1 THEN d ELSE t3 END) | +----------------------------------------+ | 2002-01-01 | +----------------------------------------+ {code} The expected result is '2002-01-01 00:00:00'. {code} 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' | +---------+------+------------------------------------------+ {code} The expected result is '0000-00-00 00:00:00.567', without a warning. A similar problem is observed with COALESCE: {code} 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 | +-------------------------+ {code} The expected result is '2002-01-01 00:00:00.000' for both queries. {code} 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 | +---------------------------+ {code} The expected result is '2001-01-01 00:00:00.120' for both queries. |
{code} 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 | | +----------------------------------+-------------+------+-----+---------+------- {code} 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: {code} 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 | +------------------------------------------+ {code} The expected result is '2001-01-01 00:00:00.12' for both queries. {code} 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 | +------------------------------------------+ {code} The expected result is '2001-01-01 00:00:00.120'. {code} SELECT CONCAT(CASE WHEN 1 THEN d ELSE t3 END) FROM t1; +----------------------------------------+ | CONCAT(CASE WHEN 1 THEN d ELSE t3 END) | +----------------------------------------+ | 2002-01-01 | +----------------------------------------+ {code} The expected result is '2002-01-01 00:00:00'. {code} 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' | +---------+------+------------------------------------------+ {code} The expected result is '0000-00-00 00:00:00.567', without a warning. A similar problem is observed with COALESCE: {code} 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 | +-------------------------+ {code} The expected result is '2002-01-01 00:00:00.000' for both queries. {code} 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 | +---------------------------+ {code} The expected result is '2001-01-01 00:00:00.120' for both queries. A similar problems is observer with IFNULL: {code} 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 | +-------------------------+-------------------------+ {code} The expected value is '2001-01-01 00:00:00.120' for both expressions. {code} 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 | +-------------------------+-----------------------+ {code} The expected value is '2002-01-01 00:00:00.000' for both expressions. |
| Summary | CASE, COALESCE on a combination of different temporal types returns wrong values | Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types |
| Fix Version/s | 10.0.5 [ 13201 ] | |
| Fix Version/s | 5.5.33 [ 13300 ] | |
| Fix Version/s | 5.3.13 [ 12602 ] |
| Description |
{code} 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 | | +----------------------------------+-------------+------+-----+---------+------- {code} 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: {code} 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 | +------------------------------------------+ {code} The expected result is '2001-01-01 00:00:00.12' for both queries. {code} 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 | +------------------------------------------+ {code} The expected result is '2001-01-01 00:00:00.120'. {code} SELECT CONCAT(CASE WHEN 1 THEN d ELSE t3 END) FROM t1; +----------------------------------------+ | CONCAT(CASE WHEN 1 THEN d ELSE t3 END) | +----------------------------------------+ | 2002-01-01 | +----------------------------------------+ {code} The expected result is '2002-01-01 00:00:00'. {code} 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' | +---------+------+------------------------------------------+ {code} The expected result is '0000-00-00 00:00:00.567', without a warning. A similar problem is observed with COALESCE: {code} 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 | +-------------------------+ {code} The expected result is '2002-01-01 00:00:00.000' for both queries. {code} 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 | +---------------------------+ {code} The expected result is '2001-01-01 00:00:00.120' for both queries. A similar problems is observer with IFNULL: {code} 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 | +-------------------------+-------------------------+ {code} The expected value is '2001-01-01 00:00:00.120' for both expressions. {code} 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 | +-------------------------+-----------------------+ {code} The expected value is '2002-01-01 00:00:00.000' for both expressions. |
{code} 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 | | +----------------------------------+-------------+------+-----+---------+------- {code} 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: {code} 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 | +------------------------------------------+ {code} The expected result is '2001-01-01 00:00:00.120' for both queries. {code} 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 | +------------------------------------------+ {code} The expected result is '2001-01-01 00:00:00.120'. {code} SELECT CONCAT(CASE WHEN 1 THEN d ELSE t3 END) FROM t1; +----------------------------------------+ | CONCAT(CASE WHEN 1 THEN d ELSE t3 END) | +----------------------------------------+ | 2002-01-01 | +----------------------------------------+ {code} The expected result is '2002-01-01 00:00:00'. {code} 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' | +---------+------+------------------------------------------+ {code} The expected result is '0000-00-00 00:00:00.567', without a warning. A similar problem is observed with COALESCE: {code} 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 | +-------------------------+ {code} The expected result is '2002-01-01 00:00:00.000' for both queries. {code} 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 | +---------------------------+ {code} The expected result is '2001-01-01 00:00:00.120' for both queries. A similar problems is observer with IFNULL: {code} 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 | +-------------------------+-------------------------+ {code} The expected value is '2001-01-01 00:00:00.120' for both expressions. {code} 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 | +-------------------------+-----------------------+ {code} The expected value is '2002-01-01 00:00:00.000' for both expressions. |
| Resolution | Duplicate [ 3 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | defaullt [ 28359 ] | MariaDB v2 [ 44413 ] |
| Workflow | MariaDB v2 [ 44413 ] | MariaDB v3 [ 61512 ] |
| Workflow | MariaDB v3 [ 61512 ] | MariaDB v4 [ 146925 ] |
The problem was earlier fixed by:
MDEV-4863COALESCE(time_or_datetime) returns wrong results in numeric context