Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL), 10.2(EOL), 10.3(EOL)
Description
This problem is repeatable with CASE, COALESCE, IF, NULLIF, LEAST, GREATEST.
This problem is not repeatable with IFNULL, which does preserve exact data types.
FLOAT is converted to DOUBLE
CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); |
CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; |
DESCRIBE t2;
|
+-------------+--------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------------+--------------+------+-----+---------+-------+
|
| COALESCE(a) | double(10,2) | YES | | NULL | |
|
+-------------+--------------+------+-----+---------+-------+
|
CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); |
CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; |
DESCRIBE t2;
|
+------------+--------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+------------+--------------+------+-----+---------+-------+
|
| LEAST(a,a) | double(19,2) | YES | | NULL | |
|
+------------+--------------+------+-----+---------+-------+
|
TINYINT is converted to INT
CREATE OR REPLACE TABLE t1 (a TINYINT(1)); |
CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; |
DESCRIBE t2;
|
+-------------+--------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------------+--------+------+-----+---------+-------+
|
| COALESCE(a) | int(4) | YES | | NULL | |
|
+-------------+--------+------+-----+---------+-------+
|
CREATE OR REPLACE TABLE t1 (a TINYINT(1)); |
CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; |
DESCRIBE t2;
|
+------------+--------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+------------+--------+------+-----+---------+-------+
|
| LEAST(a,a) | int(4) | YES | | NULL | |
|
+------------+--------+------+-----+---------+-------+
|
UNION
Unlike hybrid functions, UNION correctly preserves the exact data types:
CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); |
CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; |
DESCRIBE t2;
|
+-------+-------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------+-------------+------+-----+---------+-------+
|
| a | float(10,2) | YES | | NULL | |
|
+-------+-------------+------+-----+---------+-------+
|
CREATE OR REPLACE TABLE t1 (a TINYINT(1)); |
CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; |
DESCRIBE t2;
|
+-------+------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------+------------+------+-----+---------+-------+
|
| a | tinyint(4) | YES | | NULL | |
|
+-------+------------+------+-----+---------+-------+
|
Hybrid functions should be fixed to preserve the exact data types, like UNION does.
Note, UNION is probably wrong with returning tinyint(4) instead of tinyint(1). But this problem will be addressed separately.
Attachments
Issue Links
- blocks
-
MDEV-4912 Data type plugin API version 1
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
| Description |
{code:sql}
CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | COALESCE(a) | double(10,2) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | LEAST(a,a) | double(19,2) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | float(10,2) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------+------+-----+---------+-------+ | COALESCE(a) | int(4) | YES | | NULL | | +-------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------+------+-----+---------+-------+ | LEAST(a,a) | int(4) | YES | | NULL | | +------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | a | tinyint(4) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ {noformat} |
This problem is repeatable with {{CASE}}, {{COALESCE}}, {{IF}}, {{NULLIF}}.
This problem is not repeatable with {{IFNULL}}, which does preserve exact data types. {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | COALESCE(a) | double(10,2) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | LEAST(a,a) | double(19,2) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | float(10,2) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------+------+-----+---------+-------+ | COALESCE(a) | int(4) | YES | | NULL | | +-------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------+------+-----+---------+-------+ | LEAST(a,a) | int(4) | YES | | NULL | | +------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | a | tinyint(4) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ {noformat} |
| Summary | CASE and CASE-alike hybrid functions do not preserve exact type types | CASE and CASE-alike hybrid functions do not preserve exact data types |
| Description |
This problem is repeatable with {{CASE}}, {{COALESCE}}, {{IF}}, {{NULLIF}}.
This problem is not repeatable with {{IFNULL}}, which does preserve exact data types. {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | COALESCE(a) | double(10,2) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | LEAST(a,a) | double(19,2) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | float(10,2) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------+------+-----+---------+-------+ | COALESCE(a) | int(4) | YES | | NULL | | +-------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------+------+-----+---------+-------+ | LEAST(a,a) | int(4) | YES | | NULL | | +------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | a | tinyint(4) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ {noformat} |
This problem is repeatable with {{CASE}}, {{COALESCE}}, {{IF}}, {{NULLIF}}.
This problem is not repeatable with {{IFNULL}}, which does preserve exact data types. h3. {{FLOAT}} is converted to {{DOUBLE}} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | COALESCE(a) | double(10,2) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | LEAST(a,a) | double(19,2) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | float(10,2) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ {noformat} h3. {{TINYINT}} is converted to {{INT}} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------+------+-----+---------+-------+ | COALESCE(a) | int(4) | YES | | NULL | | +-------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------+------+-----+---------+-------+ | LEAST(a,a) | int(4) | YES | | NULL | | +------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | a | tinyint(4) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ {noformat} |
| Description |
This problem is repeatable with {{CASE}}, {{COALESCE}}, {{IF}}, {{NULLIF}}.
This problem is not repeatable with {{IFNULL}}, which does preserve exact data types. h3. {{FLOAT}} is converted to {{DOUBLE}} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | COALESCE(a) | double(10,2) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | LEAST(a,a) | double(19,2) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | float(10,2) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ {noformat} h3. {{TINYINT}} is converted to {{INT}} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------+------+-----+---------+-------+ | COALESCE(a) | int(4) | YES | | NULL | | +-------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------+------+-----+---------+-------+ | LEAST(a,a) | int(4) | YES | | NULL | | +------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | a | tinyint(4) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ {noformat} |
This problem is repeatable with {{CASE}}, {{COALESCE}}, {{IF}}, {{NULLIF}}, {{LEAST}}, {{GREATEST}}.
This problem is not repeatable with {{IFNULL}}, which does preserve exact data types. h3. {{FLOAT}} is converted to {{DOUBLE}} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | COALESCE(a) | double(10,2) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | LEAST(a,a) | double(19,2) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | float(10,2) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ {noformat} h3. {{TINYINT}} is converted to {{INT}} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------+------+-----+---------+-------+ | COALESCE(a) | int(4) | YES | | NULL | | +-------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------+------+-----+---------+-------+ | LEAST(a,a) | int(4) | YES | | NULL | | +------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | a | tinyint(4) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ {noformat} |
| Description |
This problem is repeatable with {{CASE}}, {{COALESCE}}, {{IF}}, {{NULLIF}}, {{LEAST}}, {{GREATEST}}.
This problem is not repeatable with {{IFNULL}}, which does preserve exact data types. h3. {{FLOAT}} is converted to {{DOUBLE}} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | COALESCE(a) | double(10,2) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | LEAST(a,a) | double(19,2) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | float(10,2) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ {noformat} h3. {{TINYINT}} is converted to {{INT}} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------+------+-----+---------+-------+ | COALESCE(a) | int(4) | YES | | NULL | | +-------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------+------+-----+---------+-------+ | LEAST(a,a) | int(4) | YES | | NULL | | +------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | a | tinyint(4) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ {noformat} |
This problem is repeatable with {{CASE}}, {{COALESCE}}, {{IF}}, {{NULLIF}}, {{LEAST}}, {{GREATEST}}.
This problem is not repeatable with {{IFNULL}}, which does preserve exact data types. h3. {{FLOAT}} is converted to {{DOUBLE}} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | COALESCE(a) | double(10,2) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | LEAST(a,a) | double(19,2) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ {noformat} h3. {{TINYINT}} is converted to {{INT}} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------+------+-----+---------+-------+ | COALESCE(a) | int(4) | YES | | NULL | | +-------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------+------+-----+---------+-------+ | LEAST(a,a) | int(4) | YES | | NULL | | +------------+--------+------+-----+---------+-------+ {noformat} h3. {{UNION}} Unlike hybrid functions, {{UNION}} correctly preserves the exact data types: {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | float(10,2) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | a | tinyint(4) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ {noformat} Hybrid functions should be fixed to preserve the exact data types, like {{UNION}} does. |
| Description |
This problem is repeatable with {{CASE}}, {{COALESCE}}, {{IF}}, {{NULLIF}}, {{LEAST}}, {{GREATEST}}.
This problem is not repeatable with {{IFNULL}}, which does preserve exact data types. h3. {{FLOAT}} is converted to {{DOUBLE}} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | COALESCE(a) | double(10,2) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | LEAST(a,a) | double(19,2) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ {noformat} h3. {{TINYINT}} is converted to {{INT}} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------+------+-----+---------+-------+ | COALESCE(a) | int(4) | YES | | NULL | | +-------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------+------+-----+---------+-------+ | LEAST(a,a) | int(4) | YES | | NULL | | +------------+--------+------+-----+---------+-------+ {noformat} h3. {{UNION}} Unlike hybrid functions, {{UNION}} correctly preserves the exact data types: {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | float(10,2) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | a | tinyint(4) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ {noformat} Hybrid functions should be fixed to preserve the exact data types, like {{UNION}} does. |
This problem is repeatable with {{CASE}}, {{COALESCE}}, {{IF}}, {{NULLIF}}, {{LEAST}}, {{GREATEST}}.
This problem is not repeatable with {{IFNULL}}, which does preserve exact data types. h3. {{FLOAT}} is converted to {{DOUBLE}} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | COALESCE(a) | double(10,2) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | LEAST(a,a) | double(19,2) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ {noformat} h3. {{TINYINT}} is converted to {{INT}} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(a) FROM t1; DESCRIBE t2; {code} {noformat} +-------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------+------+-----+---------+-------+ | COALESCE(a) | int(4) | YES | | NULL | | +-------------+--------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT LEAST(a,a) FROM t1; DESCRIBE t2; {code} {noformat} +------------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------+------+-----+---------+-------+ | LEAST(a,a) | int(4) | YES | | NULL | | +------------+--------+------+-----+---------+-------+ {noformat} h3. {{UNION}} Unlike hybrid functions, {{UNION}} correctly preserves the exact data types: {code:sql} CREATE OR REPLACE TABLE t1 (a FLOAT(10,2)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | float(10,2) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ {noformat} {code:sql} CREATE OR REPLACE TABLE t1 (a TINYINT(1)); CREATE OR REPLACE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1; DESCRIBE t2; {code} {noformat} +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | a | tinyint(4) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ {noformat} Hybrid functions should be fixed to preserve the exact data types, like {{UNION}} does. Note, {{UNION}} is probably wrong with returning {{tinyint(4)}} instead of {{tinyint(1)}}. But this problem will be addressed separately. |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Alexander Barkov [ bar ] | Oleksandr Byelkin [ sanja ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Oleksandr Byelkin [ sanja ] | Alexander Barkov [ bar ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Fix Version/s | 10.3.1 [ 22532 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Link |
This issue relates to |
| Workflow | MariaDB v3 [ 80512 ] | MariaDB v4 [ 152035 ] |
OK to push