Bug #45668 MySQL Server returns the datatype as binary instead of date

• Submitted: 23 Jun 2009 5:57
• Modified: 23 Jun 2009 7:00
• Reporter: Saminathan Arumugam
• Status: Can't repeat
• Category: MySQL Server: DDL
• Severity: S1 (Critical)
• Version: 4.1.22
• OS: Linux
• CPU Architecture: Any
• Tags: "jdbc driver", "MySQL Server 4.1.22", BINARY, date

[23 Jun 2009 5:57] Saminathan Arumugam

Description:
MySQL J Connector 3.1.13 returns the data type as binary instead of datetime.

How to repeat:
Use: MySQL 4.1.22
J Connector: 3.1.13

Query: 
create table t1 as select monthname((FROM_UNIXTIME(0)+INTERVAL(100000000000/1000)second))

[23 Jun 2009 6:03] Saminathan Arumugam

Column type seems to be binary in MySQL server itself, so I think its a bug with MySQL server itself.

Here is the show create table:

CREATE TABLE `t1` (
  `monthname((FROM_UNIXTIME(0)+INTERVAL(100000000000/1000)second))` binary(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

[23 Jun 2009 6:17] Sveta Smirnova

Thank you for the report.

This is fixed in version 5.0:

mysql> create table t1 as select monthname((FROM_UNIXTIME(0)+INTERVAL(100000000000/1000)second));
Query OK, 1 row affected (0.36 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `monthname((FROM_UNIXTIME(0)+INTERVAL(100000000000/1000)second))` varchar(9) character set latin1 default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

VARCHAR(9) is correct here, because monthname is surely VARCHAR and not any of DATE/TIME types

[23 Jun 2009 6:30] Saminathan Arumugam

Yeah, I accept it should be varchar and not datetime.

Is there any patch available for the specified version

[23 Jun 2009 7:00] Saminathan Arumugam

Even max function over this nested from_unixtime function returns binary instead of date/time

mysql> create table table1(col1 bigint);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into table1 values(unix_timestamp(now()) * 1000);
Query OK, 1 row affected (0.00 sec)

mysql> create table table2 as select max(FROM_UNIXTIME(0)+INTERVAL(col1/1000) second) col1 from table1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table table2;
+--------+-----------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                          |
+--------+-----------------------------------------------------------------------------------------------------------------------+
| table2 | CREATE TABLE `table2` (
  `col1` binary(29) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[25 Aug 2009 6:10] Roel Van de Paar

Not so in 5.1.37:

mysql> create table table1(col1 bigint);
Query OK, 0 rows affected (0.12 sec)

mysql> insert into table1 values(unix_timestamp(now()) * 1000);
Query OK, 1 row affected (0.00 sec)

mysql> create table table2 as select max(FROM_UNIXTIME(0)+INTERVAL(col1/1000) second) col1 from table1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table table2;
+--------+-----------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                  |
+--------+-----------------------------------------------------------------------------------------------+
| table2 | CREATE TABLE `table2` (
  `col1` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)