Bug #36614 got year instead day when use rs.getInt() to fetch Date field
Submitted: 9 May 2008 5:54 Modified: 12 May 2008 8:21
Reporter: Ben Li Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S4 (Feature request)
Version:5.1.6(5.0.8) OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: JDBC Date getInt

[9 May 2008 5:54] Ben Li
Description:
When insert into a table contains Date column, using
rpst.setInt(1, 20080509);
works perfect.

when fetch that column use 
rs.getInt(1);
2008 is returned.

How to repeat:
a java class connecting to jdbc:mysql://localhost/test

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MysqlGetIntOnDate {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		Connection conn = getMySQLconn();
		int day = 20080509;
		testInsert(conn, day);
		if (day == testFetch(conn))
			System.out.println("getInt on Date field matched");
		else 
			System.out.println("getInt on Date field failed");
	}

	static Connection getMySQLconn() {
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager
				.getConnection(
					"jdbc:mysql://localhost/test?useUnicode=yes&characterEncoding=UTF-8",
					"root", "");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static void testInsert(Connection conn, int day) {
		PreparedStatement rpst = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			stmt = conn.createStatement();
			stmt.execute("drop table if exists tmptest");
			stmt
				.execute("create table tmptest(id int not null primary key, day date not null) DEFAULT CHARSET=utf8");
			stmt.close();
			String thesql = "replace into tmptest(id, day) values(1, ?)";
			rpst = conn.prepareStatement(thesql);
			rpst.setInt(1, day);
			rpst.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (rpst != null)
					rpst.close();
			} catch (SQLException e) {
			}
		}
	}
	
	public static int testFetch(Connection conn) {
		PreparedStatement rpst = null;
		ResultSet rs = null;

		try {
			String thesql = "select day from tmptest where id = 1";
			rpst = conn.prepareStatement(thesql);
			rs = rpst.executeQuery();
			if (rs.next()) {
				int day = rs.getInt(1);
				System.out.println("got day: " + day);
				return day;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rpst.close();
			} catch (SQLException e) {
			}
		}
		return 0;
	}

}

Suggested fix:
for Connector/J 5.0.8, a quick and dirty patch

diff -ur src.orig/com/mysql/jdbc/ResultSet.java src/com/mysql/jdbc/ResultSet.java
--- src.orig/com/mysql/jdbc/ResultSet.java      2007-10-05 05:11:24.000000000 +0800
+++ src/com/mysql/jdbc/ResultSet.java   2008-05-08 18:51:42.000000000 +0800
@@ -2649,7 +2649,12 @@

                                if (!needsFullParse) {
                                        try {
-                                               return parseIntWithOverflowCheck(columnIndex,
+                                               int field_mysqltype = this.fields[columnIndex - 1].getMysqlType();
+                                               if (field_mysqltype == MysqlDefs.FIELD_TYPE_DATE || field_mysqltype == MysqlDefs.FIELD_TYPE_NEWDATE)
+                                                       return parseDateIntWithOverflowCheck(columnIndex,
+                                                               intAsBytes, null);
+                                               else
+                                                       return parseIntWithOverflowCheck(columnIndex,
                                                                intAsBytes, null);
                                        } catch (NumberFormatException nfe) {
                                                try {
@@ -7326,6 +7331,96 @@
                return (int) valueAsDouble;
        }

+       public static int getDateInt(byte[] buf, int offset, int endPos) throws NumberFormatException {
+               int base = 10;
+
+               int s = offset;
+
+               /* Skip white space. */
+               while (Character.isWhitespace((char) buf[s]) && (s < endPos)) {
+                       ++s;
+               }
+
+               if (s == endPos) {
+                       throw new NumberFormatException(new String(buf));
+               }
+
+               /* Check for a sign. */
+               boolean negative = false;
+
+               if ((char) buf[s] == '-') {
+                       negative = true;
+                       ++s;
+               } else if ((char) buf[s] == '+') {
+                       ++s;
+               }
+
+               /* Save the pointer so we can check later if anything happened. */
+               int save = s;
+
+               int cutoff = Integer.MAX_VALUE / base;
+               int cutlim = (Integer.MAX_VALUE % base);
+
+               if (negative) {
+                       cutlim++;
+               }
+
+               boolean overflow = false;
+
+               int i = 0;
+
+               for (; s < endPos; s++) {
+                       char c = (char) buf[s];
+
+                       if (Character.isDigit(c)) {
+                               c -= '0';
+                       } else if (Character.isLetter(c)) {
+                               c = (char) (Character.toUpperCase(c) - 'A' + 10);
+                       } else {
+                               continue;
+                       }
+
+                       if (c >= base) {
+                               break;
+                       }
+
+                       /* Check for overflow. */
+                       if ((i > cutoff) || ((i == cutoff) && (c > cutlim))) {
+                               overflow = true;
+                       } else {
+                               i *= base;
+                               i += c;
+                       }
+               }
+
+               if (s == save) {
+                       throw new NumberFormatException(new String(buf));
+               }
+
+               if (overflow) {
+                       throw new NumberFormatException(new String(buf));
+               }
+
+               /* Return the result of the appropriate sign. */
+               return (negative ? (-i) : i);
+       }
+
+       private int parseDateIntWithOverflowCheck(int columnIndex, byte[] valueAsBytes,
+               String valueAsString) throws NumberFormatException, SQLException {
+
+               if (valueAsBytes == null && valueAsString == null) {
+                       return 0;
+               }
+
+               if (valueAsBytes != null) {
+                       return getDateInt(valueAsBytes, 0 , valueAsBytes.length);
+               } else {
+                       valueAsString = valueAsString.trim();
+                       valueAsString.replace('-', ',');
+                       return Integer.parseInt(valueAsString);
+               }
+       }
+
        private int parseIntWithOverflowCheck(int columnIndex, byte[] valueAsBytes,
                        String valueAsString) throws NumberFormatException, SQLException {
[12 May 2008 8:13] Tonci Grgin
Test case on latest c/J 5.1 sources

Attachment: TestBug36614.java (text/x-java), 1.61 KiB.

[12 May 2008 8:21] Tonci Grgin
Hi Ben and thanks for your understandable feature request. How ever, I find it not reasonable. Consider this; what would be assertion line if I used one? 
assertEquals(long, getInt)? I will leave Mark to rule on this one.

Attached test case result:
.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

Connected to 5.0.58-pb1083-log
java.vm.version         : 1.5.0_12-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_12-b04
os.name                 : Windows XP
os.version              : null
sun.management.compiler : HotSpot Client Compiler
2008
2008-05-09

Time: 0,313

OK (1 test)