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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Duplicate
    • 10.0.3, 5.5.32, 5.3.12
    • 10.0.5, 5.5.33, 5.3.13
    • 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

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.