|
This script:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a INT, b INT UNSIGNED);
|
INSERT INTO t1 VALUES (1,1);
|
DROP TABLE IF EXISTS t2;
|
CREATE TABLE t2 AS SELECT COALESCE(a,b) AS c FROM t1;
|
SHOW CREATE TABLE t2;
|
returns
+-------+---------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+---------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`c` decimal(10,0) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+---------------------------------------------------------------------------------------------+
|
Now if I start "mysql --column-type-info" and run this query:
SELECT COALESCE(a,b) AS c FROM t1;
|
it returns the following metadata:
MariaDB [test]> SELECT COALESCE(a,b) AS c FROM t1;
|
Field 1: `c`
|
...
|
Type: LONG
|
Collation: binary (63)
|
Length: 11
|
Max_length: 1
|
Decimals: 0
|
Flags: BINARY NUM
|
Notice, COALESCE(a,b) creates a DECIMAL(10,0) field during CREATE TABLE..SELECT, but at the same time reports itself as LONG in metadata.
The same problems makes this SQL script return a wrong result set:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED);
|
INSERT INTO t1 VALUES (-1,0xFFFFFFFFFFFFFFFF);
|
SELECT COALESCE(b,a) AS c FROM t1;
|
SHOW WARNINGS;
|
It returns:
+---------------------+
|
| c |
|
+---------------------+
|
| 9223372036854775807 |
|
+---------------------+
|
1 row in set, 1 warning (0.00 sec)
|
with a warning:
+---------+------+------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+------------------------------------------------------------------------------+
|
| Warning | 1916 | Got overflow when converting '18446744073709551615' to INT. Value truncated. |
|
+---------+------+------------------------------------------------------------------------------+
|
The expected result is to return DECIMAL value of 18446744073709551615 with no warnings.
|