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

COALESCE(time_or_datetime) returns wrong results in numeric context

        Details

        • Type: Bug
        • Status: Closed
        • Priority: 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
        • Component/s: None
        • Labels:
          None

          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)

            Gliffy Diagrams

              Attachments

                Issue Links

                blocks

                Bug - A problem which impairs or prevents the functions of the product. MDEV-4724 Some temporal functions do not preserve microseconds

                • Minor - Minor loss of function, or other problem where easy workaround is present.
                • Closed
                is duplicated by

                Bug - A problem which impairs or prevents the functions of the product. MDEV-4870 Wrong values of CASE, COALESCE, IFNULL on a combination of different temporal types

                • Minor - Minor loss of function, or other problem where easy workaround is present.
                • Closed

                  Activity

                  Hide
                  Permalink
                  bar Alexander Barkov added a comment -

                  Fixes in 5.3.13 and 5.5.33

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

                    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: