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.