Description:
Historically MySQL hasn't supported negative values for DATE columns and on trying to insert such value it fails with something like `Incorrect DATE value: '-0001-01-01'.`
Previously if you tried to INSERT such value using a JDBC prepared statement it failed with the same error message (from the server).
After updating to MySQL Connector/J 8.0.23 and onwards the INSERT using JDBC prepared statement succeeds but inserts the wrong value. INSERT using plain string statement still fails with error from server.
For example inserting -0001-01-01 actually gets stored as 0002-01-01 with new Connector/J instead of throwing error. My guess is https://github.com/mysql/mysql-connector-j/commit/60553fd2557267f3184b77e1832025f80d79e89a... is relevant.
I'm attaching the BaseBugReport to help with reproducing this.
How to repeat:
import com.mysql.cj.jdbc.util.BaseBugReport;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
class Scratch
extends BaseBugReport
{
public static void main(String[] args)
throws Exception
{
new Scratch().run();
}
@Override
public void setUp()
throws Exception
{
Connection connection = getConnection();
Statement create = connection.createStatement();
create.executeUpdate("CREATE TABLE test (dt DATE)");
create.close();
}
@Override
public void tearDown()
throws Exception
{
Connection connection = getConnection();
Statement drop = connection.createStatement();
drop.executeUpdate("DROP TABLE test");
}
@Override
public void runTest()
throws Exception
{
LocalDate negativeDate = LocalDate.of(-1, 1, 1);
Connection connection = getConnection();
try (PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO test VALUES (?)")) {
preparedStatement.setObject(1, negativeDate);
int affectedRows = preparedStatement.executeUpdate();
assertTrue("Expected a single row to be inserted", affectedRows == 1);
}
try (Statement select = connection.createStatement();
ResultSet resultSet = select.executeQuery("SELECT dt FROM test")) {
while (resultSet.next()) {
LocalDate dateFromMySql = resultSet.getObject(1, LocalDate.class);
System.out.println("Inserted: '%s', formatted without ERA: '%s', date read back from MySQL: '%s'".formatted(negativeDate, negativeDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")), dateFromMySql));
assertTrue("Expected to read back what was inserted, wrote '%s', read '%s'".formatted(negativeDate, dateFromMySql), dateFromMySql.equals(negativeDate));
}
}
}
}
