Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.0.4
-
None
-
None
Description
ADDTIME(expr1,expr2) is a hybrid function.
- In case when expr1 is DATETIME, the result type is DATETIME.
- In case when expr1 is TIME, the result type is TIME.
- Otherwise, the result type is VARCHAR, and the choice between
DATETIME or TIME format is done per-row.
In case of VARCHAR result, the function must follow the standard
MySQL/MariaDB convention:
character set and collation of a string function returning a result
from numeric or temporal input should be set according to
@@character_set_connection and @@collation_connection
respectively.
http://dev.mysql.com/doc/refman/5.6/en/type-conversion.html
In MySQL 5.6, implicit conversion of a numeric or temporal value to string produces a value that has a character set and collation determined by the character_set_connection and collation_connection system variables.
Consider this SQL script:
set character_set_connection=ucs2; |
select charset(addtime(_latin1'10:01:01',_latin1'10:00:00')); |
drop table if exists t1; |
create table t1 as select addtime(_latin1'10:01:01',_latin1'10:00:00') as a; |
show create table t1; |
MySQL-5.6 does follow this rule, and the output is:
+-------------------------------------------------------+
|
| charset(addtime(_latin1'10:01:01',_latin1'10:00:00')) |
|
+-------------------------------------------------------+
|
| ucs2 |
|
+-------------------------------------------------------+
|
1
|
+-------+--------------------------------------------------------------------------------------------
|
|
|
...
|
|
|
------------------+
|
| Table | Create Table |
|
+-------+--------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`a` varchar(29) CHARACTER SET ucs2 DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+--------------------------------------------------------------------------------------------------------------+
|
Notice: ucs2 is consistently reported in both tables in the output.
MariaDB does not follow this rule, and the output is:
+-------------------------------------------------------+ |
| charset(addtime(_latin1'10:01:01',_latin1'10:00:00')) | |
+-------------------------------------------------------+ |
| binary | |
+-------------------------------------------------------+ |
|
|
...
|
|
|
+-------+-------------------------------------------------------------------------------------------+ |
| Table | Create Table | |
+-------+-------------------------------------------------------------------------------------------+ |
| t1 | CREATE TABLE `t1` ( |
`a` varchar(26) DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | |
+-------+------------------------------------------------------------------------------------------- |
Moreover, the result of CHARSET() and of "SHOW CREATE" do not
match to each over (binary vs latin1).
The same problem is observed with another temporal hybrid function DATE_ADD.
The script:
set character_set_connection=ucs2; |
select charset(date_add(_latin1'10:01:01',interval 10 second)); |
drop table if exists t1; |
create table t1 as select date_add(_latin1'10:01:01',interval 10 second) as a; |
show create table t1; |
Results from MySQL-5.6 are consistently correct:
+---------------------------------------------------------+ |
| charset(date_add(_latin1'10:01:01',interval 10 second)) | |
+---------------------------------------------------------+ |
| ucs2 |
|
+---------------------------------------------------------+ |
+-------+--------------------------------------------------------------------------------------------------------------+ |
| Table | Create Table | |
+-------+--------------------------------------------------------------------------------------------------------------+ |
| t1 | CREATE TABLE `t1` ( |
`a` varchar(29) CHARACTER SET ucs2 DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | |
+-------+--------------------------------------------------------------------------------------------------------------+ |
Result from MariaDB-10.0 are wrong and non-consistent:
+---------------------------------------------------------+ |
| charset(date_add(_latin1'10:01:01',interval 10 second)) | |
+---------------------------------------------------------+ |
| binary | |
+---------------------------------------------------------+ |
+-------+-------------------------------------------------------------------------------------------+ |
| Table | Create Table | |
+-------+-------------------------------------------------------------------------------------------+ |
| t1 | CREATE TABLE `t1` ( |
`a` varchar(26) DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | |
+-------+-------------------------------------------------------------------------------------------+ |
|
|