Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4870

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

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Minor
        • Resolution: Duplicate
        • Affects Version/s: 10.0.3, 5.5.32, 5.3.12
        • Fix Version/s: 10.0.5, 5.5.33, 5.3.13
        • Component/s: None
        • Labels:
          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.

            Gliffy Diagrams

              Attachments

                Issue Links

                duplicates

                Bug - A problem which impairs or prevents the functions of the product. MDEV-4863 COALESCE(time_or_datetime) returns wrong results in numeric context

                • Major - Major loss of function.
                • Closed

                  Activity

                  Hide
                  Permalink
                  bar Alexander Barkov added a comment -

                  The problem was earlier fixed by:
                  MDEV-4863 COALESCE(time_or_datetime) returns wrong results in numeric context

                  Show
                  bar Alexander Barkov added a comment - The problem was earlier fixed by: MDEV-4863 COALESCE(time_or_datetime) returns wrong results in numeric context

                    People

                    • Assignee:
                      bar Alexander Barkov
                      Reporter:
                      bar Alexander Barkov
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      2 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: