MariaDB Development
  1. MariaDB Development
  2. MDEV-4863

COALESCE(time_or_datetime) returns wrong results in numeric context

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • 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:
      3428

      Description

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a TIMESTAMP(3));
      INSERT INTO t1 VALUES ('2001-01-01 10:20:30.999');
      SELECT CAST(COALESCE(a,a) AS SIGNED) AS c1, CAST(COALESCE(a,a) AS DECIMAL(25,3)) AS c2, ROUND(COALESCE(a,a)) AS c2  FROM t1;
      +------+----------+------+
      | c1   | c2       | c2   |
      +------+----------+------+
      | 2001 | 2001.000 | 2001 |
      +------+----------+------+
      

      The above results are wrong.
      The expected results would be:

      +----------------+--------------------+----------------+
      | c1             | c2                 | c2             |
      +----------------+--------------------+----------------+
      | 20010101102031 | 20010101102030.999 | 20010101102031 |
      +----------------+--------------------+----------------+
      

      The same problem is observed with TIME data type:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a TIME(3));
      INSERT INTO t1 VALUES ('10:20:30.999');
      SELECT CAST(COALESCE(a,a) AS SIGNED) AS c1, CAST(COALESCE(a,a) AS DECIMAL(25,3)) AS c2, ROUND(COALESCE(a,a)) AS c2  FROM t1;
      +------+--------+------+
      | c1   | c2     | c2   |
      +------+--------+------+
      |   10 | 10.000 |   10 |
      +------+--------+------+
      

      The expected result is:

      +--------+------------+--------+
      | c1     | c2         | c2     |
      +--------+------------+--------+
      | 102031 | 102030.999 | 102031 |
      +--------+------------+--------+
      

      The result in string context is also wrong:

      MariaDB [test]> SELECT CONCAT(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')));
      +-----------------------------------------------------------------------+
      | CONCAT(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) |
      +-----------------------------------------------------------------------+
      | 2001-01-01                                                            |
      +-----------------------------------------------------------------------+
      1 row in set (1.06 sec)
      

      The expected result is '2001-01-01 00:00:00'.

      IF() has a similar problem:

      MariaDB [test]> SELECT CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-01 10:20:30')) AS SIGNED) AS CAST, CONCAT(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-01 10:20:30'))) AS CONCAT;
      +----------+------------+
      | CAST     | CONCAT     |
      +----------+------------+
      | 20010101 | 2001-01-01 |
      +----------+------------+
      1 row in set (0.00 sec)
      

      The expected results are 20010101000000 and '2001-01-01 00:00:00'.

      So does IFNULL:

      MariaDB [test]> SELECT CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-01 10:20:30')) AS SIGNED) AS CAST, CONCAT(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-01 10:20:30'))) AS CONCAT;+------+------------+
      | CAST | CONCAT     |
      +------+------------+
      | 2001 | 2001-01-01 |
      +------+------------+
      1 row in set (0.00 sec)
      
      

      The expected result is 20010101000000 and '2001-01-01 00:00:00'.

      CASE has a similar problem:

      MariaDB [test]> SELECT CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-01 10:20:30') END AS SIGNED) AS CAST, CONCAT(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-01 10:20:30') END) AS CONCAT;
      +----------+------------+
      | CAST     | CONCAT     |
      +----------+------------+
      | 20010101 | 2001-01-01 |
      +----------+------------+
      1 row in set (0.00 sec)
      

      The expected results are 20010101000000 and '2001-01-01 00:00:00'.

      A related problem in CASE:

      MariaDB [test]> DROP TABLE IF EXISTS t1; CREATE TABLE t1 AS SELECT CASE WHEN 1 THEN TIME('10:10:10') ELSE DATE('2001-01-01') END AS c1, CONCAT(CASE WHEN 1 THEN TIME('10:10:10') ELSE DATE('2001-01-01') END) AS c2; SHOW COLUMNS FROM t1;
      Query OK, 0 rows affected (0.00 sec)
      Query OK, 1 row affected (0.08 sec)
      Records: 1  Duplicates: 0  Warnings: 0
      +-------+---------------+------+-----+---------+-------+
      | Field | Type          | Null | Key | Default | Extra |
      +-------+---------------+------+-----+---------+-------+
      | c1    | datetime(6)   | YES  |     | NULL    |       |
      | c2    | varbinary(17) | YES  |     | NULL    |       |
      +-------+---------------+------+-----+---------+-------+
      2 rows in set (0.00 sec)
      

      Columns c2 is too short. It should be enough to fit DATETIME(6) values,
      so it should be varbinary(26) in 5.3 and varchar(26) in 5.5+.

      COALESCE has the same problem:

      MariaDB [test]> DROP TABLE IF EXISTS t1; CREATE TABLE t1 AS SELECT COALESCE(TIME('10:10:10'),DATE('2001-01-01')) AS c1,CONCAT(COALESCE(TIME('10:10:10'),DATE('2001-01-01'))) AS c2; SHOW COLUMNS FROM t1;
      Query OK, 0 rows affected (0.00 sec)
      Query OK, 1 row affected, 1 warning (0.08 sec)
      Records: 1  Duplicates: 0  Warnings: 1
      +-------+---------------+------+-----+---------+-------+
      | Field | Type          | Null | Key | Default | Extra |
      +-------+---------------+------+-----+---------+-------+
      | c1    | datetime(6)   | YES  |     | NULL    |       |
      | c2    | varbinary(17) | YES  |     | NULL    |       |
      +-------+---------------+------+-----+---------+-------+
      2 rows in set (0.00 sec)
      

      The expected length for "c2" is 26, which is a minimum length to fit a datetime(6) value.

      The same problem is repeatable with IFNULL:

      MariaDB [test]> DROP TABLE IF EXISTS t1; CREATE TABLE t1 AS SELECT IFNULL(TIME('10:10:10'),DATE('2001-01-01')) AS c1,CONCAT(IFNULL(TIME('10:10:10'),DATE('2001-01-01'))) AS c2; SHOW COLUMNS FROM t1;
      Query OK, 0 rows affected (0.00 sec)
      Query OK, 1 row affected, 1 warning (0.09 sec)
      Records: 1  Duplicates: 0  Warnings: 1
      +-------+---------------+------+-----+---------+-------+
      | Field | Type          | Null | Key | Default | Extra |
      +-------+---------------+------+-----+---------+-------+
      | c1    | datetime(6)   | YES  |     | NULL    |       |
      | c2    | varbinary(17) | YES  |     | NULL    |       |
      +-------+---------------+------+-----+---------+-------+
      2 rows in set (0.00 sec)
      

      and IF:

      {MariaDB [test]> DROP TABLE IF EXISTS t1; CREATE TABLE t1 AS SELECT IF(1,TIME('10:10:10'),DATE('2001-01-01')) AS c1,CONCAT(IF(1,TIME('10:10:10'),DATE('2001-01-01'))) AS c2; SHOW COLUMNS FROM t1;
      Query OK, 0 rows affected (0.00 sec)
      Query OK, 1 row affected, 1 warning (0.08 sec)
      Records: 1  Duplicates: 0  Warnings: 1
      +-------+---------------+------+-----+---------+-------+
      | Field | Type          | Null | Key | Default | Extra |
      +-------+---------------+------+-----+---------+-------+
      | c1    | datetime(6)   | YES  |     | NULL    |       |
      | c2    | varbinary(17) | YES  |     | NULL    |       |
      +-------+---------------+------+-----+---------+-------+
      2 rows in set (0.00 sec)
      

        Issue Links

          Activity

          Hide
          Alexander Barkov added a comment -

          Fixes in 5.3.13 and 5.5.33

          Show
          Alexander Barkov added a comment - Fixes in 5.3.13 and 5.5.33

            People

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

              Dates

              • Created:
                Updated:
                Resolved: