Bug #10212 YEAR datatype Field returns Date as the type of column when retrieving metadata

• Submitted: 27 Apr 2005 18:35
• Modified: 10 May 2005 19:00
• Reporter: A Y
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• OS: Windows (Windows 2000)
• Assigned to: Mark Matthews
• CPU Architecture: Any

[27 Apr 2005 18:35] A Y

Description:
I have a database table with a column of type YEAR(4). When I retrieve the metadata for the column using JDBC (mysql connector) I get the column type as Date(java.sql.Types 91). The actual object that is retrieved using resultSet.getObject() return Integer. This is causing problems with my dynamic casting of objects. Can someone please let me know if this is a bug or by design?

Thanks

How to repeat:
1. create a table with column of type YEAR(4)
2. using JDBC making a call to retireve metadata for this column in table
3. Retrieve the actual data using resultSet.getObject(columnId)

The metadata retrieved will be java.sql.Types.Date(91) whereas the actual data retrieved is Integer

[27 Apr 2005 19:46] Mark Matthews

The following testcase works, so is there something you do materially different that would cause this testcase to be invalid?

public void testBug10212() throws Exception {
		String tableName = "testBug10212";
		
		try {
			createTable(tableName, "(field1 YEAR(4))");
			this.stmt.executeUpdate("INSERT INTO " + tableName + " VALUES (1974)");
			this.rs = this.conn.prepareStatement("SELECT field1 FROM " + tableName).executeQuery();
			
			ResultSetMetaData rsmd = this.rs.getMetaData();
			assertTrue(this.rs.next());
			assertEquals("java.sql.Date", rsmd.getColumnClassName(1));
			assertEquals("java.sql.Date", this.rs.getObject(1).getClass().getName());
			
			this.rs = this.stmt.executeQuery("SELECT field1 FROM " + tableName);
			
			rsmd = this.rs.getMetaData();
			assertTrue(this.rs.next());
			assertEquals("java.sql.Date", rsmd.getColumnClassName(1));
			assertEquals("java.sql.Date", this.rs.getObject(1).getClass().getName());
		} finally {
			if (this.rs != null) {
				this.rs.close();
				this.rs = null;
			}
		}
	}

[27 Apr 2005 19:46] Mark Matthews

I should add that I tested w/ Connector/J 3.1.8 and MySQL-4.0.x and 4.1.11.

[10 May 2005 18:53] A Y

I think I found the problem. I was using mysql-connector-java-3.1.6-bin.jar and when I switched to mysql-connector-java-3.1.7-bin.jar, the problem went away. Thanks for the help.