|
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)
|
|