|
CASE and its abbreviations IFNULL, IF, NULLIF, COALESCE aggregate data types of multiple arguments.
They're expected to return the same result type for the same input types.
But currently there are multiple implementations for of the aggregating code in:
Item_func_case_abbreviations2::if::fix_length_and_dec() (shared by Item_func_if, Item_func_ifnull)
|
Item_func_coalesce::fix_length_and_dec()
|
Item_func_case::fix_length_and_dec()
|
This result in different behavior:
DROP TABLE IF EXISTS t1,t2;
|
CREATE TABLE t1 (a SMALLINT);
|
INSERT INTO t1 VALUES (1),(2);
|
CREATE TABLE t2 AS SELECT
|
IFNULL(a,a) AS c1,
|
COALESCE(a,a) AS c2,
|
CASE WHEN a IS NOT NULL THEN a ELSE a END AS c3,
|
IF(a IS NULL,a,a) AS c4 FROM t1;
|
SHOW CREATE TABLE t2;
|
returns
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`c1` smallint(6) DEFAULT NULL,
|
`c2` int(6) DEFAULT NULL,
|
`c3` int(6) DEFAULT NULL,
|
`c4` int(6) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Notice, IFNULL preserves the data type, while the other functions do not.
If I run this script in "mysql --column-type-info":
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a SMALLINT);
|
INSERT INTO t1 VALUES (1),(2);
|
SELECT
|
IFNULL(a,a) AS c1,
|
COALESCE(a,a) AS c2,
|
CASE WHEN a IS NOT NULL THEN a ELSE a END AS c3,
|
IF(a IS NULL,a,a) AS c4
|
FROM t1;
|
it correctly reports SHORT as the data type for all columns:
Field 1: `c1`
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: SHORT
|
Collation: binary (63)
|
Length: 6
|
Max_length: 1
|
Decimals: 0
|
Flags: BINARY NUM
|
|
Field 2: `c2`
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: SHORT
|
Collation: binary (63)
|
Length: 6
|
Max_length: 1
|
Decimals: 0
|
Flags: BINARY NUM
|
|
Field 3: `c3`
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: SHORT
|
Collation: binary (63)
|
Length: 6
|
Max_length: 1
|
Decimals: 0
|
Flags: BINARY NUM
|
|
Field 4: `c4`
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: SHORT
|
Collation: binary (63)
|
Length: 6
|
Max_length: 1
|
Decimals: 0
|
Flags: BINARY NUM
|
This script demonstrates the opposite behavior, when IFNULL returns a wrong column type, while the other function return the same data type as the expression not wrapped to hybrid functions:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 AS SELECT
|
connection_id() AS c0,
|
IFNULL(connection_id(),connection_id()) AS c1,
|
COALESCE(connection_id(), connection_id()) AS c2,
|
CASE WHEN 0 THEN connection_id() ELSE connection_id() END AS c3,
|
IF(0,connection_id(),connection_id()) AS c4;
|
SHOW CREATE TABLE t1;
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`c0` int(10) NOT NULL,
|
`c1` bigint(10) NOT NULL,
|
`c2` int(10) NOT NULL,
|
`c3` int(10) NOT NULL,
|
`c4` int(10) NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Notice, c0 is int(10), so are c2,c3,c4, but c1 created from IFNULL() is bigint(10). It should be int(10).
Proposal:
- Split count_string_result_length() and friends into two steps:
a. on the first step it will actually count attributes, this step will go into Type_handler_xxx::join_type_attributes()
b. on the second step it will install converters into the argument list, e.g. Item_func_conv_charset when character set conversion is needed.
- Unify all CASE-alike hybrid functions:
CASE, IF, NULLIF, IFNULL, COALESCE and possibly LEAST, GREATEST
to go through join_type_attributes(). Currently many of them have their own fix_length_and_dec() implementation, which actually do exactly the same thing, but in different ways.
|