| 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: | |
| 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 16:29]
MySQL Verification Team
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]
MySQL Verification Team
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] >
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.