Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL)
-
10.1.8-4
Description
If I start "mysql --column-type-info test" and run this script:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a INT, b VARCHAR(10));
|
INSERT INTO t1 VALUES (-2147483648,'100x');
|
SELECT LEAST(a,b),GREATEST(a,b) FROM t1;
|
it returns the following metadata:
Field 1: `LEAST(a,b)`
|
...
|
Type: VAR_STRING
|
Collation: binary (63)
|
Length: 23
|
Max_length: 11
|
Decimals: 31
|
Flags: BINARY
|
|
Field 2: `GREATEST(a,b)`
|
...
|
Type: VAR_STRING
|
Collation: binary (63)
|
Length: 23
|
Max_length: 3
|
Decimals: 31
|
Flags: BINARY
|
That is the columns are reported to be of the VARBINARY data type.
Now if I do CREATE TABLE..SELECT:
DROP TABLE IF EXISTS t2;
|
CREATE TABLE t2 AS SELECT LEAST(a,b),GREATEST(a,b) FROM t1;
|
SHOW CREATE TABLE t2;
|
it creates double columns:
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`LEAST(a,b)` double DEFAULT NULL,
|
`GREATEST(a,b)` double DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
|
Summary:
- metadata reports the VARBINARY data type
- CREATE TABLE .. SELECT creates columns of the DOUBLE data types.
The expected result would be to have the same data type in both cases.
DOUBLE looks the best candidate.
Repeatable in MySQL-5.7.8