Details
-
Bug
-
Status: Stalled (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
This piece of code:
void Item_func_sp::fix_length_and_dec()
|
{
|
DBUG_ENTER("Item_func_sp::fix_length_and_dec"); |
|
|
DBUG_ASSERT(sp_result_field);
|
Type_std_attributes::set(sp_result_field); |
maybe_null= 1;
|
|
|
DBUG_VOID_RETURN;
|
}
|
sets all Type_std_attributes members but collation.derivation, which remains DERIVATION_COERCIBLE by default.
This query is a prove:
DROP FUNCTION IF EXISTS f1; |
CREATE FUNCTION f1() RETURNS INT RETURN 10; |
SELECT
|
COERCIBILITY(f1()),
|
COERCIBILITY(10),
|
COERCIBILITY(LENGTH('a')); |
+--------------------+------------------+---------------------------+
|
| COERCIBILITY(f1()) | COERCIBILITY(10) | COERCIBILITY(LENGTH('a')) |
|
+--------------------+------------------+---------------------------+
|
| 4 | 5 | 5 |
|
+--------------------+------------------+---------------------------+
|
Notice, the first column erroneously returns 4, which means DERIVATION_COERCIBLE, while the second and the third column correctly return 5, which is DERIVATION_NUMERIC.
This can result in wrong character set during aggregation.
Here's an example:
DROP FUNCTION IF EXISTS f1; |
DROP TABLE IF EXISTS t1; |
CREATE FUNCTION f1() RETURNS INT RETURN 10; |
CREATE TABLE t1 AS SELECT |
CONCAT(f1(),_utf8'a') as c1, |
CONCAT(10,_utf8'a') as c2, |
CONCAT(LENGTH('a'),_utf8'a') as c3; |
SHOW CREATE TABLE t1; |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`c1` varchar(12) DEFAULT NULL,
|
`c2` varchar(3) CHARACTER SET utf8 NOT NULL,
|
`c3` varchar(11) CHARACTER SET utf8 NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Notice, character set for c1 was erroneously set to latin1. The expected character set is utf8, like it was correctly set for c2 and c3.
SP functions should set its derivation to:
- DERIVATION_NUMERIC for numeric and temporal data types
- DERIVATION_IMPLICIT for string data types
This was forgotten during "WL#2649: Number-to-string conversions".