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

Alexander Barkov created issue - 2013-08-05 09:25

Alexander Barkov made changes - 2013-08-05 09:28

Field	Original Value	New Value
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. Consider this SQL script: {code:sql} 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; {code} MySQL-5.6 does follow this rule, and the output is: {code} +-------------------------------------------------------+ \| 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 \| +-------+--------------------------------------------------------------------------------------------------------------+ {code} Notice: ucs2 is consistently reported in both tables in the output. MariaDB does not follow this rule, and the output is: {code:sql} +-------------------------------------------------------+ \| 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 \| +-------+------------------------------------------------------------------------------------------- {code} Moreover, the result of CHARSET() and of "SHOW CREATE" do not match to each over (binary vs latin1).	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: {code:sql} 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; {code} MySQL-5.6 does follow this rule, and the output is: {code} +-------------------------------------------------------+ \| 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 \| +-------+--------------------------------------------------------------------------------------------------------------+ {code} Notice: ucs2 is consistently reported in both tables in the output. MariaDB does not follow this rule, and the output is: {code:sql} +-------------------------------------------------------+ \| 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 \| +-------+------------------------------------------------------------------------------------------- {code} Moreover, the result of CHARSET() and of "SHOW CREATE" do not match to each over (binary vs latin1).

Alexander Barkov made changes - 2013-08-05 09:30

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:
{code:sql}
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;
{code}

MySQL-5.6 does follow this rule, and the output is:
{code}
+-------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------+
{code}
Notice: ucs2 is consistently reported in both tables in the output.

MariaDB does not follow this rule, and the output is:
{code:sql}
+-------------------------------------------------------+
| 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 |
+-------+-------------------------------------------------------------------------------------------
{code}

Moreover, the result of CHARSET() and of "SHOW CREATE" do not
match to each over (binary vs latin1).

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
{quote}
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.
{quote}

Consider this SQL script:
{code:sql}
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;
{code}

MySQL-5.6 does follow this rule, and the output is:
{code}
+-------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------+
{code}
Notice: ucs2 is consistently reported in both tables in the output.

MariaDB does not follow this rule, and the output is:
{code:sql}
+-------------------------------------------------------+
| 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 |
+-------+-------------------------------------------------------------------------------------------
{code}

Moreover, the result of CHARSET() and of "SHOW CREATE" do not
match to each over (binary vs latin1).

Alexander Barkov made changes - 2013-08-05 09:31

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
{quote}
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.
{quote}

Consider this SQL script:
{code:sql}
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;
{code}

MySQL-5.6 does follow this rule, and the output is:
{code}
+-------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------+
{code}
Notice: ucs2 is consistently reported in both tables in the output.

MariaDB does not follow this rule, and the output is:
{code:sql}
+-------------------------------------------------------+
| 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 |
+-------+-------------------------------------------------------------------------------------------
{code}

Moreover, the result of CHARSET() and of "SHOW CREATE" do not
match to each over (binary vs latin1).

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
{quote}
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.
{quote}

Consider this SQL script:
{code:sql}
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;
{code}

MySQL-5.6 does follow this rule, and the output is:
{code}
+-------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------+
{code}
Notice: ucs2 is consistently reported in both tables in the output.

MariaDB does not follow this rule, and the output is:
{code:sql}
+-------------------------------------------------------+
| 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 |
+-------+-------------------------------------------------------------------------------------------
{code}

Moreover, the result of CHARSET() and of "SHOW CREATE" do not
match to each over (binary vs latin1).

Alexander Barkov made changes - 2013-08-05 10:18

Assignee

Alexander Barkov [ bar ]

Alexander Barkov made changes - 2013-08-05 10:22

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
{quote}
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.
{quote}

Consider this SQL script:
{code:sql}
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;
{code}

MySQL-5.6 does follow this rule, and the output is:
{code}
+-------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------+
{code}
Notice: ucs2 is consistently reported in both tables in the output.

MariaDB does not follow this rule, and the output is:
{code:sql}
+-------------------------------------------------------+
| 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 |
+-------+-------------------------------------------------------------------------------------------
{code}

Moreover, the result of CHARSET() and of "SHOW CREATE" do not
match to each over (binary vs latin1).

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
{quote}
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.
{quote}

Consider this SQL script:
{code:sql}
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;
{code}

MySQL-5.6 does follow this rule, and the output is:
{code}
+-------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------+
{code}
Notice: ucs2 is consistently reported in both tables in the output.

MariaDB does not follow this rule, and the output is:
{code:sql}
+-------------------------------------------------------+
| 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 |
+-------+-------------------------------------------------------------------------------------------
{code}

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:
{code:sql}
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;
{code}

Results from MySQL-5.6 are consistently correct:
{code:sql}
+---------------------------------------------------------+
| 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 |
+-------+--------------------------------------------------------------------------------------------------------------+
{code}

Result from MariaDB-10.0 are wrong and non-consistent:
{code:sql}
+---------------------------------------------------------+
| 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 |
+-------+-------------------------------------------------------------------------------------------+

{code}

Alexander Barkov made changes - 2013-08-05 10:22

Summary

Wrong character set of ADDTIME()

Wrong character set of ADDTIME() and DATE_ADD()

Alexander Barkov made changes - 2013-08-07 13:16

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2013-08-08 21:41

Fix Version/s

10.0.5 [ 13201 ]

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

Pushed into 10.0.5

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

Alexander Barkov made changes - 2013-08-22 13:03

Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2014-06-13 15:06

Workflow

defaullt [ 28325 ]

MariaDB v2 [ 42894 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:50

Workflow

MariaDB v2 [ 42894 ]

MariaDB v3 [ 61924 ]

Sergei Golubchik made changes - 2021-12-06 21:39

Workflow

MariaDB v3 [ 61924 ]

MariaDB v4 [ 146899 ]

MariaDB Server

Wrong character set of ADDTIME() and DATE_ADD()

Details

Description

Attachments

Activity

People

Dates

Git Integration