MySQL Bugs: #19847: Date is wrong

Bug #19847	Date is wrong
Submitted:	16 May 2006 11:52	Modified:	11 Nov 2009 2:36
Reporter:	Andre Timmer	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	mysql-connector-java-3.1.12-bin.jar	OS:
Assigned to:		CPU Architecture:	Any

Description:
Date with zero's get a different value using a mysql unix client or jdbc.

How to repeat:
drop table if exists test;

CREATE TABLE test (
  col1 date
) ENGINE=innodb DEFAULT CHARSET=utf8;

insert into test (col1) values ('19240000');
insert into test (col1) values ('1924-00-00');
commit;

select col1, ifnull(col1, 'x') from test;

-------------------------------
- with a mysql unix client 
-------------------------------

+------------+-------------------+
| col1       | ifnull(col1, 'x') |
+------------+-------------------+
| 1924-00-00 | 1924-00-00        |
| 1924-00-00 | 1924-00-00        |
+------------+-------------------+

-------------------------------
- with a jdbc client 
-------------------------------

col1	ifnull(col1, 'x')
1	1923-11-30	1924-00-00
2	1923-11-30	1924-00-00

Suggested fix:
The jdbc values seem not to be correct.

What do you expect the values to be? Java dates can't have any zero components in them that can't usually be zero (days, months), so the Java class Date rounds them.

We could throw an exception by default (as we do with all-zero date/datetime values), but there will never be a way to represent these dates with fidelity.

What exactly are you using the zeroeth day and/or month to represent? If they're sentinal values, maybe it makes more sense to use either a YEAR type, or use the 1st month and 1st day?

Our database is filled with third party data.
Company create data is mostly supplied as yyyy-mm-dd.
For older companys it's sometimes yyyy-00-00 since they don't know the exact date any more.

IF one does resultset.getDate(..) on a date value there maybe is no other solutions than the current one. We do actually resultset.getString(..) here it should be possible to return the zero parts correctly.

You can get the behavior you want in existing drivers by calling ResultSet.getString() as long as "noDatetimeStringSync=true" is set on your connection properties.

We'll look at handling zero components for day/month the same way we handle all-zero datetimes, i.e. configurable behavior, but exceptions by default, since those values can not be represented with any fidelity by Java dates.

Great! Maybe related is http://bugs.mysql.com/bug.php?id=19848