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
Category:	Connector/J	Severity:	S2 (Serious)
Version:	mysql-connector-java-3.1.12-bin.jar	OS:	Any (All)
Assigned to:		Target Version:

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.