MySQL Bugs: #94872: "java.sql.SQLException: YEAR" when reading 0000-01-01 from a DATE field

Bug #94872	"java.sql.SQLException: YEAR" when reading 0000-01-01 from a DATE field
Submitted:	2 Apr 2019 15:45	Modified:	14 Feb 2020 9:44
Reporter:	Stefan Breunig (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.15	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Using MySQL 5.7 with SQL Mode STRICT_ALL_TABLES, NO_ZERO_DATE, NO_ZERO_IN_DATE and inserting a 0-year value (e.g. 0000-01-01) into a DATE field yields this stacktrace:

 java.sql.SQLException: YEAR
     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
     at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
     at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:85)
     at com.mysql.cj.jdbc.result.ResultSetImpl.getDate(ResultSetImpl.java:847)
     at testsuite.simple.DateTest.testZeroYearBehavior(DateTest.java:189)
 Caused by: com.mysql.cj.exceptions.WrongArgumentException: YEAR
     at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
     at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
     at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
     at com.mysql.cj.result.SqlDateValueFactory.createFromDate(SqlDateValueFactory.java:108)
     at com.mysql.cj.result.SqlDateValueFactory.createFromDate(SqlDateValueFactory.java:49)
     at com.mysql.cj.result.BaseDecoratingValueFactory.createFromDate(BaseDecoratingValueFactory.java:53)
     at com.mysql.cj.result.BaseDecoratingValueFactory.createFromDate(BaseDecoratingValueFactory.java:53)
     at com.mysql.cj.protocol.a.MysqlTextValueDecoder.decodeDate(MysqlTextValueDecoder.java:72)
     at com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:90)
     at com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:250)
     at com.mysql.cj.protocol.a.result.ByteArrayRow.getValue(ByteArrayRow.java:91)
     at com.mysql.cj.jdbc.result.ResultSetImpl.getNonStringValueFromRow(ResultSetImpl.java:656)
     at com.mysql.cj.jdbc.result.ResultSetImpl.getDateOrTimestampValueFromRow(ResultSetImpl.java:679)
 Caused by: java.lang.IllegalArgumentException: YEAR
     at java.util.GregorianCalendar.computeTime(GregorianCalendar.java:2648)
     at java.util.Calendar.updateTime(Calendar.java:3393)
     at java.util.Calendar.getTimeInMillis(Calendar.java:1782)
     at com.mysql.cj.result.SqlDateValueFactory.createFromDate(SqlDateValueFactory.java:103)

How to repeat:
Add this to e.g. testsuite/simple/DateTest.java:

    public void testZeroYearBehavior() throws Exception {
        Connection testConn = this.conn;
        try {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testZeroYearBehavior");
            this.stmt.executeUpdate("CREATE TABLE testZeroYearBehavior(field DATE)");
            this.stmt.executeUpdate("INSERT INTO testZeroYearBehavior VALUES ('0000-01-01')");

            Statement stmt = testConn.createStatement();
            this.rs = stmt.executeQuery("SELECT field FROM testZeroYearBehavior");
            this.rs.next();

            assertEquals("0000-01-01", this.rs.getDate(1).toString());
        } finally {
            this.stmt.executeUpdate("DROP TABLE IF EXISTS testZeroYearBehavior");

            if (testConn != this.conn) {
                testConn.close();
            }
        }
    }

Suggested fix:
I tried around this, but as a non Java coder it appears to me that java.sql.Date itself doesn't support year 0 and below. I'll keep digging, but I'm happy for any hints.

Hello Stefan,

Thank you for the report.

regards,
Umesh

I just realized the bug might not necessarily be in Connector/J:

"""
The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.
https://dev.mysql.com/doc/refman/8.0/en/datetime.html
"""

The question is why MySQL even accepts such values in the first place. I guess for Connector/J that would mean to simply throw a DataReadException, similarly to how it's done for InvalidZeroDate already.

The code contribution Bug#95789 has a proposal to fix this bug.

The Bug#95789 is a continuation of this one report, but since it contains a contribution I close the current report as a duplicate of Bug#95789.