MySQL Bugs: #11540: Incorrect year conversion in setDate(..) for system that use B.E. year

Bug #11540	Incorrect year conversion in setDate(..) for system that use B.E. year
Submitted:	24 Jun 2005 4:29	Modified:	24 Jun 2005 20:00
Reporter:	Vorrarit Luengwattanakij	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.1.8a	OS:	Windows (Windows2000 sp4)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
I'm Thai. The locale for my machine is Thai.  The year format of Thai locale is Buddhist Era (B.E.). The current B.E. is 2548 (2005+543). It's always English year (200x) + 543. 

When query a date using ResultSet.getDate(...); and submit the date as a parameter of PreparedStatement by using setDate(...) there's no data found!!

How to repeat:
1. Set locale in control panel/regional options to Thai
2. Create test table and data

create table testdate (
    string1 varchar(10),
    date1 datetime
);

insert into testdate ('aaa', '2005-03-04');

3. Using PreparedStatement by "Select string1, date1 from table1". Then...

4. Use ResultSet.getDate("date1"), 

5. Create another PreparedStatement and put date1 by using setDate(rs.getDate("date1")). The statement is "select string1, date1 from table1 where date1=?".

At least one record should return. But there's no record return.

Suggested fix:
As I check the code in com.mysql.jdbc.PreparedStatement as below.

public void setDate(int parameterIndex, java.sql.Date x)
throws 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''");
setInternal(parameterIndex, dateFormatter.format(x));
}
}

It seems setDate will convert date to yyyy-MM-dd by using default locale, for me it's "th_TH". So the date "2005-03-04", it will be "2548-03-04". That's why there's no record return.

I think internally MySQL always use the English year for date query, no matter what the default locale is. So the code should be fixed to ...

public void setDate(int parameterIndex, java.sql.Date x)
throws 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); //Hardcode US locale there...
setInternal(parameterIndex, dateFormatter.format(x));
}
}

This should be fixed in both setDate and setTimestamp.

Hi! can't reproduce it.

I attached java program that I used for testing, and tested it against mysql 4.1.10 and connector/j 3.1.6 and 3.1.10.

In all cases first select (that get date from table without condition) returns me 
1462-03-04

and second select returns 1 row - exacty the same date 1462-03-04

BTW I assume you meant 'table1' instead of 'testdate' in table definition.

If you have any ideas how to reproduce this bug, pls let us know

test case

Attachment: bug11540.java (application/octet-stream, text), 1.33 KiB.

The testcase seems to be missing setting the default locale to TH , th.

I've got a working testcase, and will be fixing the bug.

Thanks.

Thank you for your bug report. This is fixed for 3.1.11, and will be available
in the nightly snapshot build after 00:00 GMT June 25th at http://downloads.mysql.com/snapshots.php#connector-j

Thank you, Mark. You have done a great job.

Don't forget to fix com.mysql.jdbc.ResultSet too,

All "new GregorianCalendar()" should be changed to "new GregorianCalendar(Locale.US)" too avoid problem when reading date or timestamp value back.

Hi K. Sathaporn,

I haven't found the problem with ResultSet.  Please ...
1) Submit a new bug with the way to repeat it.  or
2) Send email to me, I will help you check.

My email is vorrarit at yahoo dot com.  Please email me.  I always use MySQL with Thai locale.  I want to get update for the problem and solution.

Thanks