Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL), 10.2(EOL), 10.3(EOL)
Description
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.
Attachments
Issue Links
- blocks
-
MDEV-4912 Data type plugin API version 1
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-12775 Reuse data type aggregation code for hybrid functions and UNION
-
- Closed
-