Details
Major
Description
DROP TABLE IF EXISTS t1; CREATE TABLE t1 (val bit(1)); INSERT INTO t1 VALUES (0); DROP TABLE IF EXISTS t2; CREATE TABLE t2 AS SELECT COALESCE(val, 1) AS c FROM t1; SELECT * FROM t2; SHOW CREATE TABLE t2;
returns
+------+ | c | +------+ | 0 | +------+
+-------+--------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` ( `c` decimal(1,0) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+--------------------------------------------------------------------------------------------+
Now if I run "mysql --column-type-info test" and execute this query:
SELECT COALESCE(val, 1) FROM t1;
it returns the following result and metadata:
+------------------+ | COALESCE(val, 1) | +------------------+ | 0 | +------------------+
Field 1: `COALESCE(val, 1)` ... Type: VAR_STRING Collation: binary (63) Length: 1 Max_length: 1 Decimals: 31 Flags: UNSIGNED BINARY
Notice, COALESCE(val,1) positions itself as:
- DECIMAL(1,0) in CREATE TABLE...SELECT
- VARBINARY(1) in metadata returned by SELECT
The expected result is to return the same data type in both cases
Note, a UNION between a BIT column and an INT literal creates a column of type VARBINARY(20):
DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (val BIT(1)); CREATE TABLE t2 AS SELECT val FROM t1 UNION SELECT 1; SHOW CREATE TABLE t2;
+-------+-----------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` ( `val` varbinary(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------------+
which is closer to what metadata returns for COALESCE(val,1) rather than what CREATE TABLE..SELECT returns for COALESCE(val,1).
Summary:
- SELECT bit_column FROM t1 - returns a string-alike value with BIT type in metadata
- SELECT bit_column FROM t1 UNION SELECT 1 – returns a number-alike value with VAR_STRING data type in metadata
- SELECT COALESCE(bit_column,1) FROM t1 – returns a number-alike value with VAR_STRING metadata
- CREATE TABLE t2 AS SELECT val FROM t1 UNION SELECT 1; – creates a column of type VARBINARY(20), with number-alike values
- CREATE TABLE t2 AS SELECT COALESCE(val,1) FROM t1; – creates a column of type DECIMAL(1,0)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
MySQL-5.7.8 is also affected.