|
When two double columns with fixed scale are mixed for hybrid functions, e.g. COALESCE, the fixed length nature is always preserved:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a FLOAT(20,4), b FLOAT(20,3), c FLOAT(20,4));
|
INSERT INTO t1 VALUES (1111,2222,3333);
|
DROP TABLE IF EXISTS t2;
|
CREATE TABLE t2 SELECT
|
COALESCE(a,8888),
|
COALESCE(8888,a),
|
COALESCE(a,a),
|
COALESCE(a,b),
|
COALESCE(a,c),
|
COALESCE(b,b),
|
COALESCE(b,a),
|
COALESCE(b,c),
|
COALESCE(c,a),
|
COALESCE(c,b),
|
COALESCE(c,c)
|
FROM t1\G
|
SHOW CREATE TABLE t2;
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`COALESCE(a,8888)` double(20,4) DEFAULT NULL,
|
`COALESCE(8888,a)` double(20,4) DEFAULT NULL,
|
`COALESCE(a,a)` double(20,4) DEFAULT NULL,
|
`COALESCE(a,b)` double(21,4) DEFAULT NULL,
|
`COALESCE(a,c)` double(20,4) DEFAULT NULL,
|
`COALESCE(b,b)` double(20,3) DEFAULT NULL,
|
`COALESCE(b,a)` double(21,4) DEFAULT NULL,
|
`COALESCE(b,c)` double(21,4) DEFAULT NULL,
|
`COALESCE(c,a)` double(20,4) DEFAULT NULL,
|
`COALESCE(c,b)` double(21,4) DEFAULT NULL,
|
`COALESCE(c,c)` double(20,4) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Fixed length is also preserved in UNION in case if the columns have exactly the length and scale:
CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1;
|
SHOW CREATE TABLE t2;
|
+-------+-------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`a` float(20,4) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------+
|
CREATE OR REPLACE TABLE t2 SELECT a FROM t1 UNION SELECT c FROM t1;
|
SHOW CREATE TABLE t2;
|
+-------+-------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`a` float(20,4) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------+
|
CREATE OR REPLACE TABLE t2 SELECT b FROM t1 UNION SELECT b FROM t1;
|
SHOW CREATE TABLE t2;
|
+-------+-------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`b` float(20,3) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------+
|
CREATE OR REPLACE TABLE t2 SELECT c FROM t1 UNION SELECT c FROM t1;
|
SHOW CREATE TABLE t2;
|
+-------+-------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`c` float(20,4) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------+
|
CREATE OR REPLACE TABLE t2 SELECT c FROM t1 UNION SELECT a FROM t1;
|
SHOW CREATE TABLE t2;
|
+-------+-------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`c` float(20,4) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------+
|
But fixed length is not preserved in UNION in case if the columns have different definitions. A non-fixed-length column is created:
CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1;
|
SHOW CREATE TABLE t2;
|
+-------+-------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`a` float DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------+
|
CREATE OR REPLACE TABLE t2 AS SELECT b FROM t1 UNION SELECT a FROM t1;
|
SHOW CREATE TABLE t2;
|
+-------+-------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`b` float DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------+
|
Hybrid functions and UNION should return the same column types.
The code in Item_type_holder::join_types was written in 4.1 time and was not changed significantly since that. So it implements simplified data type aggregation rules.
The code in Item_func::count_real_length was notably improved since that and returns more precise data types.
It would be nice to reuse hybrid function aggregation code for UNION.
|