Bug #64942 DATETIME and TIMESTAMP values returned as NULL with type VARCHAR
Submitted: 11 Apr 2012 11:57 Modified: 11 Apr 2012 13:01
Reporter: Damien Allison Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S4 (Feature request)
Version:5.1.19 OS:Any
Assigned to: CPU Architecture:Any

[11 Apr 2012 11:57] Damien Allison
Description:
I have a data table holding string content from URI targets. I store the fetch time as well as the data.

I am using connector/j to fetch the data. See below for create and query commands. When I get the column value it is always returned as null even though it is from a column that can not be null. The other strange behaviour is that the meta data for the column says its type name is VARCHAR.

This issue seems to resemble older issues: http://bugs.mysql.com/bug.php?id=1206 but not sure if it is related.

How to repeat:
CREATE TABLE Item (
  id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  uri VARCHAR(2048) NOT NULL,
  fetchedDate TIMESTAMP NOT NULL,
  data VARCHAR(30720) NOT NULL
) CHARACTER SET utf8;

I query the data as part of a joined query with: 

SELECT p.uri AS uri, p.fetchedDate AS fetchedDate, p.data AS data, p.id AS id
FROM Item p, OtherTable c 
WHERE c.otherRef = 1 AND c.itemId = p.id

I use getDate(2), getTimestamp(2), getString(2), all return null. The ResultSetMetaData.getTypeName(2) => "VARHCAR"

Suggested fix:
What ever caused this issue in previous versions seems to have worked it's way back in. Perhaps a regression suite for this class of issues? I expect that the issue is related to mapping types which are not being initialised correctly.
[11 Apr 2012 12:11] Tonci Grgin
Damien, please check DatabaseMetaData.java in test suite, around line 2381 (testABunchOfReturnTypes) for proper usage. If the problem persists, notify us again.
[11 Apr 2012 12:24] Damien Allison
I will look into that. What I am confused about is that if I use getString (based on the fact that meta data is saying the column is varchar) it returns NULL for a NOT NULL column. the java.sql.ResultSet API spec says it returns null in case of null value but doesn't make provision for unknown type mapping.
[11 Apr 2012 12:38] Damien Allison
Could you please share a link to the code? I am happy to review. If this behaviour is intentional then I am happy to degrade this bug to a feature request. Could it should be handled as an exception (unsupported type conversion?) rather than 'failing' in a way that is not in keeping with the sql API?
[11 Apr 2012 12:51] Tonci Grgin
public void testBug64942() throws Exception {
		System.out.println("java.vm.version         : " + System.getProperty("java.vm.version"));
		System.out.println("java.vm.vendor          : " + System.getProperty("java.vm.vendor"));
		System.out.println("java.runtime.version    : " + System.getProperty("java.runtime.version"));
		System.out.println("os.name                 : " + System.getProperty("os.name"));
		System.out.println("os.version              : " + System.getProperty("os.version "));
		System.out.println("sun.management.compiler : " + System.getProperty("sun.management.compiler"));

	try {
		createTable("testbug64942", "(id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,"+
			"uri VARCHAR(2048) NOT NULL,"+
			"fetchedDate TIMESTAMP NOT NULL,"+
			"data VARCHAR(255) NOT NULL"+
			") CHARACTER SET utf8;");
		this.stmt.executeUpdate("INSERT INTO testbug64942 VALUES (NULL, 'Test', NOW(),'Longer test')");
		this.rs = this.stmt.executeQuery("SELECT * FROM testbug64942");
		ResultSetMetaData rsMetaData = this.rs.getMetaData();

		int numberOfColumns = rsMetaData.getColumnCount();
		System.out.println("Dumping column MetaData: ");
		System.out.println("==========");
		for (int i = 1; i <= numberOfColumns; i++) {
		   System.out.println("Column number: " + i);
		   System.out.println("ColumnDisplaySize:" + rsMetaData.getColumnDisplaySize(i));
		   System.out.println("ColumnLabel: " + rsMetaData.getColumnLabel(i));
		   System.out.println("ColumnName: " + rsMetaData.getColumnName(i));
		   System.out.println("ColumnType: " + rsMetaData.getColumnType(i));
		   System.out.println("ColumnTypeName: " + rsMetaData.getColumnTypeName(i));
		   System.out.println("ColumnClassName: " + rsMetaData.getColumnClassName(i));

		   System.out.println("==========");
		 };
		 this.rs.close();
		 this.stmt.close();
	} finally {
	}
}

Produces:

Connected to 5.5.14-log
java.vm.version         : 1.5.0_17-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_17-b04
os.name                 : Windows Server 2008
os.version              : null
sun.management.compiler : HotSpot Client Compiler
Dumping column MetaData: 
==========
Column number: 1
ColumnDisplaySize:20
ColumnLabel: id
ColumnName: id
ColumnType: -5
ColumnTypeName: BIGINT UNSIGNED
ColumnClassName: java.math.BigInteger
==========
Column number: 2
ColumnDisplaySize:2048
ColumnLabel: uri
ColumnName: uri
ColumnType: 12
ColumnTypeName: VARCHAR
ColumnClassName: java.lang.String
==========
Column number: 3
ColumnDisplaySize:19
ColumnLabel: fetchedDate
ColumnName: fetchedDate
ColumnType: 93
ColumnTypeName: TIMESTAMP
ColumnClassName: java.sql.Timestamp
==========
Column number: 4
ColumnDisplaySize:255
ColumnLabel: data
ColumnName: data
ColumnType: 12
ColumnTypeName: VARCHAR
ColumnClassName: java.lang.String
==========

So I see no bug here.
[11 Apr 2012 13:01] Damien Allison
One thing seems to be that I was pulling the table metadata when requesting the statement metadata (resulting in varchar). 

I need to revert the changes I have made to the table to find out why I was getting NULL (my issue). I have not changed my query just altered the underlying table and using getLong rather than getDate.

I am going to close this for now until I have time to reproduce a longer example and investigate. (there is no - pending status).
[11 Apr 2012 13:01] Damien Allison
One thing seems to be that I was pulling the table metadata when requesting the statement metadata (resulting in varchar). 

I need to revert the changes I have made to the table to find out why I was getting NULL (my issue). I have not changed my query just altered the underlying table and using getLong rather than getDate.

I am going to close this for now until I have time to reproduce a longer example and investigate. (there is no - pending status).
[11 Apr 2012 13:08] Tonci Grgin
Damien, this is fine by us. We are always looking over community bugs so your new post will be noted.

Also, I see you are using way too big VARCHAR for Java specs possibly causing an error in code. Also, I do not see the INSERT command in your sample. So, next time, you should provide us with all the info you have (ie. MySQL server version is missing, JDK...) and a complete test case including connection string (mine was empty, thus I didn't mentioned it).