|
This MTR test:
CREATE TABLE t1
|
(
|
c_int INT,
|
c_enum ENUM('1')
|
);
|
|
CREATE TABLE t2 AS SELECT c_int FROM t1 UNION SELECT c_enum FROM t1;
|
SHOW CREATE TABLE t2;
|
DROP TABLE t2;
|
|
CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(c_int, c_enum) FROM t1;
|
SHOW CREATE TABLE t2;
|
DROP TABLE t2;
|
|
--enable_metadata
|
SELECT c_int FROM t1 UNION SELECT c_enum FROM t1;
|
SELECT COALESCE(c_int, c_enum) FROM t1;
|
--disable_metadata
|
|
DROP TABLE t1;
|
produces the following output:
CREATE TABLE t1
|
(
|
c_int INT,
|
c_enum ENUM('1')
|
);
|
CREATE TABLE t2 AS SELECT c_int FROM t1 UNION SELECT c_enum FROM t1;
|
SHOW CREATE TABLE t2;
|
Table Create Table
|
t2 CREATE TABLE `t2` (
|
`c_int` varchar(11) DEFAULT NULL
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1
|
DROP TABLE t2;
|
CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(c_int, c_enum) FROM t1;
|
SHOW CREATE TABLE t2;
|
Table Create Table
|
t2 CREATE TABLE `t2` (
|
`COALESCE(c_int, c_enum)` varchar(11) DEFAULT NULL
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1
|
DROP TABLE t2;
|
SELECT c_int FROM t1 UNION SELECT c_enum FROM t1;
|
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
|
def c_int c_int 253 11 0 Y 00 8
|
c_int
|
SELECT COALESCE(c_int, c_enum) FROM t1;
|
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
|
def COALESCE(c_int, c_enum) 254 11 0Y 0 39 8
|
COALESCE(c_int, c_enum)
|
DROP TABLE t1;
|
Obvervations:
- according to the output from the two SHOW CREATE TABLE t2 queries, a mixture of INT+ENUM produces VARCHAR columns, in both UNION and COALEACE statements.
- the result set metadata the UNION query reports type code 253 (MYSQL_TYPE_VAR_STRING), which is correct.
- the result set metadata the COALESCE query reports type code 254 (MYSQL_TYPE_STRING), which is wrong.
|