MySQL Bugs: #10485: The JDBC getString() method generates an SQLException accessing a YEAR(2) colum

Bug #10485	The JDBC getString() method generates an SQLException accessing a YEAR(2) colum
Submitted:	9 May 2005 18:43	Modified:	19 May 2005 15:30
Reporter:	Dave Curtin
Status:	Closed
Category:	Connector/J	Severity:	S2 (Serious)
Version:	5.0.4	OS:	Microsoft Windows (Windows)
Assigned to:	Mark Matthews	Target Version:

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.

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.

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

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.

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.

> "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.

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."

This is fixed for 3.1.9, 3.2.1. See the nightly snapshots at
http://downloads.mysql.com/snapshots.php.