Bug #72609 setDate() not using a proleptic Gregorian calendar

• Submitted: 10 May 2014 21:16
• Modified: 31 Jul 2018 14:08
• Reporter: Alan Egerton
• Status: Closed
• Category: Connector / J
• Severity: S2 (Serious)
• OS: Any
• Assigned to: Alexander Soklakov
• CPU Architecture: Any

[10 May 2014 21:16] Alan Egerton

Description:
Following standard SQL, MySQL uses a proleptic Gregorian calendar - see <http://dev.mysql.com/doc/en/mysql-calendar.html>.

However, when a Java Date value is bound to a prepared statement parameter, it is converted to a string format using the default calendar of the US locale (which has Julian/Gregorian cutover and therefore is not proleptic).

Thus attempting to bind date values prior to the cutover causes the wrong value to be stored.

How to repeat:
See <http://stackoverflow.com/q/23584810> for a case in point.

Suggested fix:
In com.mysql.jdbc.PreparedStatement.newSetDateInternal(int, Date, Calendar):

	if (this.ddf == null) {
		this.ddf = new SimpleDateFormat("''yyyy-MM-dd''", Locale.US); //$NON-NLS-1$

		// now set the dateformat's calendar to be proleptic
		final GregorianCalendar proleptic = new GregorianCalendar();
		proleptic.setGregorianChange(new Date(Long.MIN_VALUE));
		this.ddf.setCalendar(proleptic);
	}

[11 May 2014 11:49] Ravi Shankar

The proposed fix should work.
Could be safer to insert the following line

proleptic.clear();

after the fix line

final GregorianCalendar proleptic = new GregorianCalendar();   

to reset all time fields? 

Please, consider also to fix the portion of code 

else
      {
        SimpleDateFormat dateFormatter = new SimpleDateFormat("''yyyy-MM-dd''", Locale.US);

        setInternal(parameterIndex, dateFormatter.format(x));

        this.parameterTypes[(parameterIndex - 1 + getParameterIndexOffset())] = 91;
      }

in
public void setDate(int parameterIndex, java.sql.Date x, Calendar cal)
    throws SQLException

[13 May 2014 8:08] Alexander Soklakov

Hi Alan,

Thank you for this interesting report.
Verified by code review.

[13 May 2014 10:03] Alan Egerton

Proposed pathfile

Attachment: patch.txt (text/plain), 12.13 KiB.

[13 May 2014 10:05] Alan Egerton

I think this problem was a little more serious than first imagined: Connector/J does not appear to be using a proleptic Gregorian calendar at all.

The patch I submitted above should, I think, correct this throughout the library: i.e. for both data modification and retrieval.  Hopefully it's useful?

[5 Jun 2014 12:59] Ravi Shankar

Can you make some prediction about when the fix will be released?
Thank you very much.

[31 Jul 2018 14:08] Daniel So

Posted by developer:
 
Added the following entry to the Connector/J 5.1.47 changelog:

"When a Java Date value was bound to a PreparedStatement parameter, attempts to format the value by a proleptic GregorianCalendar failed to make the dates proleptic, so that dates before the Julian-Gregorian cutover (October 15, 1582) were stored wrongly. With this fix, a proleptic calendar is properly used if supplied to the setDate() method. Note that when trying to retrieve dates stored that way, a proleptic GregorianCalendar must also be explicitly supplied to the getDate() method."

[2 Aug 2018 15:46] Daniel So

Posted by developer:
 
Corrected chagnelog entry to the following:

"When a Java Date value was bound to a PreparedStatement parameter, attempts to format the value by a proleptic GregorianCalendar failed to make the dates proleptic, so that dates before the Julian-Gregorian cutover (October 15, 1582) were stored wrongly. With this fix, a proleptic calendar is properly used if supplied to the setDate() method.

Note that when trying to set or retrieve dates with PreparedSatement methods, a proleptic GregorianCalendar should always be explicitly supplied to the setDate() and getDate() method. See the discussion here for details."

The entry also added to the Connector/J 8.0.13 changelog.

Related information has been added to the Connector/J 8.0 and 5.1 Developer Guides.