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