MySQL Bugs: #1206: ResultSetMetaData.getColumnType(int) returns VARCHAR (12) for DATETIME columns

Bug #1206	ResultSetMetaData.getColumnType(int) returns VARCHAR (12) for DATETIME columns
Submitted:	5 Sep 2003 14:54	Modified:	5 Sep 2003 15:50
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	4.1.0-alpha	OS:	Windows (Windows 2000 Pro V 5.0 sp2)
Assigned to:		CPU Architecture:	Any

Description:
When using the MySQLConnector/J to access a MyISAM table, the ResultSetMetaData.getColumnType(int) method returns VARCHAR (12) instead of DATETIME (93).  This was repeated with versions 3.0.6, 3.0.8 and 3.1 alpha of the MySQLConnector/J driver.  See the code in the 'How to repeat' section to recreate the problem.  I am not sure whether this problem is related to the MySQL Connector/J driver or the 4.1.0-alpha database server.
     This could be very problematic since this method is used in the com.workingdogs.village api which is used by the Torque Jakarta Project.  All database retrievals in Torque rely on this functionality which causes DATETIME fields always to be returned as null.

How to repeat:
-- Here is the SQL to create the test db
drop database if exists test;
create database test;

grant all privileges on test.* to testuser identified by 'testuser';

use test;

drop table if exists TestTable;

CREATE TABLE TestTable
(
                    TestTableId BIGINT NOT NULL,
                    TestDate DATETIME NULL,
    PRIMARY KEY(TestTableId)
);

insert into TestTable (TestTableId, TestDate) values (1,'2003-09-04');
----------------------------------------------------------------------
// Here is the sample JDBC code to show the error

import java.sql.*;

public class JDBCDateTest {

  public static void main(String[] args) throws Exception{

	// Load the driver
	Class.forName("com.mysql.jdbc.Driver");

	// Establish a connection
	String url = "jdbc:mysql://localhost:3306/test";
	Connection conn = DriverManager.getConnection(url,"testuser", "testuser");

	// Create and execute a statment
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery("SELECT * From TestTable");
	String columnName = "TestDate";

	// Display the results
	ResultSetMetaData meta = rs.getMetaData();
	int columnNumber = rs.findColumn(columnName);

	while(rs.next()) {
		System.out.println("Column Value: " + rs.getDate(columnName));
		System.out.println("Column Type Name: " + meta.getColumnTypeName (columnNumber));
		System.out.println("Column Type: " + meta.getColumnType (columnNumber));
	}
  }
}
// This produced the following results for me:
// Column Value: 2003-09-04
// Column Type Name: UNKNOWN
// Column Type: 12

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

This has been fixed for quite some time in the nightly builds of 3.0.x and 3.1.x. It was related to protocol-level changes in MySQL-4.1.0. I've tested both with your code, and get the correct type, 'DATETIME'.

Check out http://mmmysql.sourceforge.net/snapshots/ for nightly builds of Connector/J that you can test this with yourself.

This seems to be an issue only with the 1.3.1_04 jdk.  I upgraded to 1.4.2 and the problem was corrected.