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

    • 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

          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

            bar Alexander Barkov added a comment -

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

            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

              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.