MariaDB Development
  1. MariaDB Development
  2. MDEV-4870

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

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor 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
    • Labels:
      None
    • Global Rank:
      3435

      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.

        Issue Links

          Activity

          Hide
          Alexander Barkov added a comment -

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

          Show
          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:
              Alexander Barkov
              Reporter:
              Alexander Barkov
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: