|
CREATE OR REPLACE TABLE t1 (a INT, b INT UNSIGNED);
|
INSERT INTO t1 VALUES (0x7FFFFFFF,0xFFFFFFFF);
|
CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1;
|
SHOW CREATE TABLE t2;
|
SELECT * FROM t2;
|
returns
+-------+---------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+---------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`a` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+---------------------------------------------------------------------------------------+
|
This is wrong, as huge positive unsigned values are converted to negative values:
+------------+
|
| a |
|
+------------+
|
| 2147483647 |
|
| -1 |
|
+------------+
|
Note, unlike UNION, hybrid functions aggregate data types correctly:
CREATE OR REPLACE TABLE t1 (a INT, b INT UNSIGNED);
|
INSERT INTO t1 VALUES (0x7FFFFFFF,0xFFFFFFFF);
|
CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a,b), COALESCE(b,a) FROM t1;
|
SHOW CREATE TABLE t2;
|
SELECT * FROM t2;
|
returns
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`COALESCE(a,b)` decimal(10,0) DEFAULT NULL,
|
`COALESCE(b,a)` decimal(10,0) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
|
and the values are preserved:
+---------------+---------------+
|
| COALESCE(a,b) | COALESCE(b,a) |
|
+---------------+---------------+
|
| 2147483647 | 4294967295 |
|
+---------------+---------------+
|
|