MySQL Bugs: #23584: setDate silently ignores Calendar parameter

Bug #23584	setDate silently ignores Calendar parameter
Submitted:	24 Oct 2006 15:54	Modified:	16 Sep 2009 13:44
Reporter:	Staffan Ulfberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	mysql-connector-java-3.1.12-bin.jar	OS:	Any (All)
Assigned to:		CPU Architecture:	Any

Description:
The PreparedStatement.setDate method taking three arguments is implemented like this:

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

That is, the calendar argument is silently discarded, and time zone conversion is performed in the default timezone.  This method is typically called when the time zone is in indeed important.

I checked the sources, and it seems the same bug is present also in version 5 of the driver.

How to repeat:
Call setDate() with a Calendar containing some other time zone than the default.

Suggested fix:
Just a copy/paste from the setDate() method with obvious modifications:

public void setDate(int parameterIndex, java.sql.Date x, Calendar cal)
			throws java.sql.SQLException {
		if (x == null) {
			setNull(parameterIndex, java.sql.Types.DATE);
		} else {
			// FIXME: Have instance version of this, problem as it's
			// not thread-safe :(
			SimpleDateFormat dateFormatter = new SimpleDateFormat(
					"''yyyy-MM-dd''", Locale.US); //$NON-NLS-1$
                  dateFormatter.setCalendar(cal);
			setInternal(parameterIndex, dateFormatter.format(x));
		}
	}

Hi Staffan and thanks for your problem report.
It trully seems we're ignoring Calendar parameter in our code for both branches. Will consult on this more.

I also confirm this bug in the opposite direction, i.e., in the getDate(int, Calendar) method in ResultSet.  I've looked at the source code for version 5.1.6 of Connector/J and the problem is that in between method calls the calendar (in fact the TimeZone information of the calendar) is lost.

In my case, in class com.mysql.jdbc.ResultSetImpl the method getDate eventually calls this.getNativeDate passing it calendar.getTimeZone(), which is ok.  But after this, the method getNativeDate invokes this.thisRow.getNativeDate and it forgets the timezone!  Later, eventually the method TimeUtil.fastDateCreate is invoked, and at this moment the timezone info is long gone...

The result is that this behaviour, does not respect the ResultSet API for getDate(int, Calendar).

Fixed the result set side of this bug when using server-side prepared statements for 5.1.9. The other issues were fixed when "useLegacyDatetimeCode=false" became the default.

An entry was added to the 5.1.9 changelog:

The setDate method silently ignored the Calendar parameter. The code was implemented as follows:

public void setDate(int parameterIndex, java.sql.Date x, Calendar cal) throws SQLException {
   setDate(parameterIndex, x);
}        
      
From reviewing the code it was apparent that the Calendar parameter cal was ignored.