Bug #10485 The JDBC getString() method generates an SQLException accessing a YEAR(2) colum
Submitted: 9 May 2005 16:43 Modified: 19 May 2005 13:30
Reporter: Dave Curtin Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.0.4 OS:Microsoft Windows (Windows)
Assigned to: Mark Matthews CPU Architecture:Any

[9 May 2005 16:43] Dave Curtin
Description:
The JDBC getString() method generates an SQLException accessing a YEAR(2) column.

Server version: 5.0.4-beta-nt

using: mysql-connector-java-3.2.0-alpha-bin.jar

How to repeat:
---------- MySQL commands ----------

mysql> CREATE TABLE DTYPE ( c1 YEAR(2) );
Query OK, 0 rows affected (0.14 sec)

mysql> describe dtype;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | year(2) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert dtype values  ( '05' );
Query OK, 1 row affected (0.01 sec)

mysql> select * from dtype;
+------+
| c1   |
+------+
|   05 |
+------+
1 row in set (0.00 sec)

---------- Java Test program ----------

package jdbc.main;

import java.sql.*;
import javax.sql.*;

public class Test000 {
  private static java.sql.Connection con = null;

/** main(). */
public static void main(String[] args) {
  Test000 test000 = new Test000();
  try {
    test000.runMe();
    if (con != null)
      con.close();
  }
  catch (java.sql.SQLException sqlEx) {
    System.out.println("Error = " + sqlEx.toString() );
  }
} // end main()

/** runMe(). */
public void runMe() throws java.sql.SQLException {
  ResultSet rs;
  Statement stmt;
  ResultSetMetaData rsmd;

  con = TestUtils.getMySQLConnection(
      "localhost","3306","test","djc","");
  stmt = con.createStatement();
  rs = stmt.executeQuery("SELECT * FROM DTYPE");
  rsmd = rs.getMetaData();
  System.out.println("Col 1 = " +
                     " name=" + rsmd.getColumnName(1) +
                     " label=" + rsmd.getColumnLabel(1) +
                     " type=" + rsmd.getColumnTypeName(1) +
                     " class=" + rsmd.getColumnClassName(1) +
                     " precision=" + rsmd.getPrecision(1) +
                     " scale=" + rsmd.getScale(1));

  rs.next();
  System.out.println("Col 1 Row 1 = " + rs.getString(1) );
} // end runMe()

---------- Console output from test program ----------

Col 1 =  name=c1 label=c1 type=YEAR class=java.sql.Date precision=2 scale=0

Error = java.sql.SQLException: Bad format for DATE '05' in column 1.
[9 May 2005 17:23] Mark Matthews
I'm curious as to what you might propose for the correct behavior, considering that a two-digit year is pretty near as ambiguous as you can get (and is not a recommended way to store years...witness the Y2K problem).

Should it be the following from java's SimpleDateFormat (which is of course an unstable way of specifying dates):

"For parsing with the abbreviated year pattern ("y" or "yy"), SimpleDateFormat must interpret the abbreviated year relative to some century. It does this by adjusting dates to be within 80 years before and 20 years after the time the SimpleDateFormat  instance is created."

or should we use MySQL's notion of two-digit years:

" As a two-digit string in the range '00' to '99'. Values in the ranges '00' to '69' and '70' to '99' are converted to YEAR values in the ranges 2000 to 2069 and 1970 to 1999."

In either case, it's a little scary to store year information, because the range isn't very wide, and the rules don't line up between Java behavior and MySQL behavior.
[9 May 2005 18:55] Dave Curtin
Regardless if a 2-digit year is wise, I would think that the MySQL documentation (MySQL Reference Manual, sections 11.1.2 and 11.3.3) should be adhered to.

Reading the manual, I assume that YEAR(2) and YEAR(4) are 2 different storage formats: both types physically store values ranging from 0 to 255 in 1 byte, however, they should be interpreted differently:

stored value  format  getString()  meaning
------------  ------  -----------  ------------
05            YEAR(2) 05           2005
05            YEAR(4) 1905         1905
[9 May 2005 19:10] Dave Curtin
I may have misunderstood the question. I was trying to say that the behavior should agree with what is documented. If the question is how should YEAR(2) work regardless of the current behavior my 2 cents are:

- It should follow the SQL standard if there is one (I don't know, I'm new to SQL).
- (If there is no SQL standard,) having 2 interpretations for the same stored value based on a (2) / (4) 'display format' is looking  for trouble, i.e. a uniform '1970-1999 / 2000-2069' would be clearer.
[19 May 2005 8:52] Vasily Kishkin
I tried to insert value "05" to both YEAR(2) and YEAR(4) field and got next follow result:

+---------+---------+
| YEAR(2) | YEAR(4) |
+---------+---------+
|      05 |    2005 |
+---------+---------+
1 row in set (0.02 sec)

I want to say that select statement returns right values for both fields.
[19 May 2005 12:47] Dave Curtin
> "I want to say that select statement returns right values for both fields"

You are correrct, but that is not the issue.

The issue is that a call to the ResultSet.getString() method generates an SQL exception and that the ResultSet class was obtained fromo the JDBC Connector/J (MySQL) jar file.
[19 May 2005 12:52] Dave Curtin
Note, just below the program listing above, the output from

"System.out.println("Error = " + sqlEx.toString() );"

is shown...

"Error = java.sql.SQLException: Bad format for DATE '05' in column 1."
[19 May 2005 13:30] Mark Matthews
This is fixed for 3.1.9, 3.2.1. See the nightly snapshots at http://downloads.mysql.com/snapshots.php.