Bug #96442 Incorrect DATE error when calling getMetaData on prepared statement
Submitted: 7 Aug 2019 2:56 Modified: 7 Jan 2020 19:53
Reporter: Kinika Tasie-Amadi Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:8.0.16, 8.0.17 OS:Any (Noted on Windows and MacOS)
Assigned to: CPU Architecture:Any

[7 Aug 2019 2:56] Kinika Tasie-Amadi
Description:
There are several prepared statements that worked till MySQL 8.0.16. See "How to repeat" for examples. Now, calling getMetaData() on a prepared statement results in the error: Incorrect DATE: ''

Setting a log using java.sql.DriverManager.setLogWriter() indicates that the error has MySQL code 1525, state HY00

The error occurs when using Connector/J v8.0.11 or v8.0.17 (the latest at the time of writing).

How to repeat:

// call testStatements() with a JDBC connection created using e.g. java.sql.DriverManager.getConnection()

public static void testStatements(java.sql.Connection c) throws java.sql.SQLException {
    c.createStatement().executeUpdate("CREATE TABLE IF NOT EXISTS race (id INT UNSIGNED NOT NULL, rdate DATE DEFAULT NULL)");
    testStmt(c, "SELECT id FROM race WHERE rdate = ?");  // ok
    testStmt(c, "SELECT DISTINCT id FROM race WHERE rdate = ?");  // error
    testStmt(c, "SELECT * FROM race WHERE id = ?");  // ok
    testStmt(c, "SELECT * FROM race WHERE rdate = ?");  // error
    testStmt(c, "SELECT id,rdate FROM race WHERE rdate = ?");  // ok
    testStmt(c, "SELECT * FROM race WHERE rdate = '2000-01-01'");  // ok
    testStmt(c, "SELECT count(id) FROM race WHERE rdate = ?");  // error
    testStmt(c, "SELECT * FROM race HAVING rdate = ?");  // error
}

public static String testStmt(java.sql.Connection c, String statement) throws java.sql.SQLException {
    java.sql.PreparedStatement preparedStatement = c.prepareStatement(statement, java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
    try {
        preparedStatement.getMetaData();
        return "ok";
    }
    catch (java.sql.SQLException ex) {
        return "error";
    }
}

Suggested fix:
Calling getMetaData() on all the prepared statements should work. There is no date value at this point, so there should be no (incorrect) date error.

This error may be related to 8.0.16 change log which includes:

When comparing DATE values with constant strings, MySQL first tries to convert the string to a DATE and then to perform the comparison. When the conversion failed, MySQL executed the comparison treating the DATE as a string, which could lead to unpredictable behavior. Now in such cases, if the conversion of the string to a DATE fails, the comparison fails with ER_WRONG_VALUE. (Bug #29025656)
[7 Aug 2019 10:03] MySQL Verification Team
Hello Kinika Tasie-Amadi,

Thank you for the report.

regards,
Umesh
[13 Sep 2019 12:08] Alexander Soklakov
Hi Kinika,

You are right, server behaviour has changed since 8.0.16. We need to fix the metadata request in ClientPreparedStatement.

But it's worth to mention that calling this method on a ClientPreparedStatement is not effective. ServerPreparedStatement is really prepared on server side and the metadata is returned by server after prepare(). But ClientPreparedStatement only emulates the prepare() on a client side, no metadata is known until this statement is executed. So the ClientPreparedStatement.getMetaData() in fact calls the statement execution and returns the metadata obtained from result set. I.e. you call the statement twice if you do getMetaData() before execute().
[7 Jan 2020 19:53] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 8.0.19 changelog:

"For some prepared statements, calling getMetaData() on them resulted in an Incorrect DATE error, even when no DATE values were involved. This was due to some recent changes on the MySQL 8.0 Server, to which this patch adjusts Connector/J."
[21 Apr 2020 21:24] Daniel So
Posted by developer:
 
Also added the changelog entry for Connector/J 5.1.49.