Bug #76355 Function "addtime" returns wrong column type (regression).
Submitted: 17 Mar 2015 15:34 Modified: 17 Mar 2015 16:29
Reporter: Strange Halalalal Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6/5.7 OS:Linux
Assigned to: CPU Architecture:Any
Tags: column type, functions

[17 Mar 2015 15:34] Strange Halalalal
Description:
In the latest version of MySQL 5.6 (5.6.23 as of speaking), function "addtime" will not return DATETIME type, but will return a STRING type. However in MySQL 5.1 and 5.5 they will return DATETIME type.

For the details please see below.

How to repeat:
============================================
In version 5.6.21, 5.6.23

Run the MySQL client with --column-type-info.

mysql> create table func_test (id int(11) primary key auto_increment, d date not null, t time not null) engine=innodb;
Query OK, 0 rows affected (0.40 sec)

mysql> insert into func_test (d, t) values ('2015-11-11', '10:22:33');
Query OK, 1 row affected (0.05 sec)

mysql> select addtime(d, t) from func_test;
Field   1:  `addtime(d, t)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  utf8_general_ci (33)
Length:     87
Max_length: 19
Decimals:   31
Flags:      

+---------------------+
| addtime(d, t)       |
+---------------------+
| 2015-11-11 10:22:33 |
+---------------------+
1 row in set (0.00 sec)

============================================
In version 5.1.63, 5.5.38

mysql> create table func_test (id int(11) primary key auto_increment, d date not null, t time not null) engine=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into func_test (d, t) values ('2015-11-11', '10:22:33');
Query OK, 1 row affected (0.06 sec)

mysql> select addtime(d, t) from func_test;
Field   1:  `addtime(d, t)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATETIME
Collation:  binary (63)
Length:     29
Max_length: 19
Decimals:   0
Flags:      BINARY 

+---------------------+
| addtime(d, t)       |
+---------------------+
| 2015-11-11 10:22:33 |
+---------------------+
1 row in set (0.00 sec)

Suggested fix:
Make the return type same as MySQL 5.1 and MySQL 5.5.
[17 Mar 2015 16:29] Miguel Solorzano
Thank you for the bug report.

C:\dbs>5.6\bin\mysql -uroot --column-type-info
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.24 Source distribution pull: 2015.feb20

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create table func_test (id int(11) primary key auto_increment, d date not null, t time not null) engine=innodb;
Query OK, 0 rows affected (0.25 sec)

mysql> insert into func_test (d, t) values ('2015-11-11', '10:22:33');
Query OK, 1 row affected (0.05 sec)

mysql> select addtime(d, t) from func_test;
Field   1:  `addtime(d, t)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  cp850_general_ci (4)
Length:     29
Max_length: 19
Decimals:   31
Flags:

+---------------------+
| addtime(d, t)       |
+---------------------+
| 2015-11-11 10:22:33 |
+---------------------+
1 row in set (0.00 sec)

C:\dbs>5.5\bin\mysql -uroot --column-type-info
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.43 Source distribution pull: 2015.feb20

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> select addtime(d, t) from func_test;
Field   1:  `addtime(d, t)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATETIME
Collation:  binary (63)
Length:     29
Max_length: 19
Decimals:   0
Flags:      BINARY

+---------------------+
| addtime(d, t)       |
+---------------------+
| 2015-11-11 10:22:33 |
+---------------------+
1 row in set (0.00 sec)
[17 Mar 2015 16:31] Miguel Solorzano
C:\dbs>5.7\bin\mysql -uroot --column-type-info
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.7-rc Source distribution PULL: 2015.MAR04

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)] > use test
Database changed
root@localhost [test] > create table func_test (id int(11) primary key auto_increment, d date not null, t time not null) engine=innodb;
Query OK, 0 rows affected (0.39 sec)

root@localhost [test] > insert into func_test (d, t) values ('2015-11-11', '10:22:33');
Query OK, 1 row affected (0.03 sec)

root@localhost [test] > select addtime(d, t) from func_test;
Field   1:  `addtime(d, t)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  cp850_general_ci (4)
Length:     29
Max_length: 19
Decimals:   31
Flags:

+---------------------+
| addtime(d, t)       |
+---------------------+
| 2015-11-11 10:22:33 |
+---------------------+
1 row in set (0.00 sec)

root@localhost [test] >