[MDEV-4841] Wrong character set of ADDTIME() and DATE_ADD() - JIRA

Details

Type: Bug
Status: Closed
Priority: Minor
Resolution: Fixed
Affects Version/s: 10.0.4
Fix Version/s: 10.0.5
Component/s: None
Labels:
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 |
+-------+-------------------------------------------------------------------------------------------+

Gliffy Diagrams

Attachments

Activity

Hide

Permalink

Alexander Barkov added a comment - 2013-08-22 13:03

Pushed into 10.0.5

Show

Alexander Barkov added a comment - 2013-08-22 13:03 Pushed into 10.0.5

People

Assignee:

Alexander Barkov

Reporter:

Alexander Barkov

Votes:

0 Vote for this issue

Watchers:

2 Start watching this issue

Dates

Created:

2013-08-05 09:25

Updated:

2013-08-22 13:03

Resolved:

2013-08-22 13:03

Time Tracking

Estimated:

Not Specified

Remaining:

Logged:

Agile

View on Board