MySQL Bugs: #37213: Connector/J doesn't work with DATE/DATETIME field value 0000-00-00

Bug #37213	Connector/J doesn't work with DATE/DATETIME field value 0000-00-00
Submitted:	5 Jun 2008 5:05	Modified:	25 Oct 2010 6:57
Reporter:	thanh hai cao	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Connector/J DATE fails 0000-00-00

Description:
I'm using PDT (http://www.eclipse.org/pdt/), and add Connector/J profile with mySQL support also.

Everything work cool except the issue:
When the table have anything field with DATE/DATETIME types, its value is 0000-00-00, I can't make a SELECT query consist of that field.

E.g: table A have field name F1 type DATE, have 1 record value '0000-00-00'.

we can not use 'SELECT * FROM A'.
But, can SELECT the other fields.

Regards,
Thanh Hai

How to repeat:
- windows XP
- PDT 1.0.2
- Connector/J 5.1
- mysql 5.0

It appears to adjust dates between 1582-10-05 and 1582-10-14 ten days forward, dates prior to this period in the same year ten days backward, and reduces the offset by about 1 day a century prior to this period.

I can only imagine the bizarre mix of adjusts for the Gregorian calendar and mistyped variables.

openoffice.org Base on a PC.  This bug pretty much makes Connector/J worthless for my application unless we change the way the database notes non-dates.  Luckily we're not Genealogists--and, yes, there is supposed to be some humor in this!

Guys, apologies for huge delay in processing... Can someone attach full test case demonstrating this behavior (along with DML/DDL statements)? I am especially interested in connection string used.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

create table scratch.drew_test_dates (
  id int unsigned not null primary key auto_increment,
  test_date_str char(10),
  test_date date not null default '0000-00-00'
);

insert into scratch.drew_test_dates
    (test_date_str, test_date)
  values
    ('2010-01-01', '2010-01-01'),
    ('0000-00-00', '0000-00-00'),
    ('2010-10-22', '2010-10-22')
;

select * from scratch.drew_test_dates;
select id, test_date_str, test_date from scratch.drew_test_dates;
select id, test_date_str, concat(test_date) from scratch.drew_test_dates;

In PDT:
select * shows no rows returned in results window
select w/ explicit cols show no results...
select w/ explicit cols and date col wrapped in concat() shows expected results.

Connector/J is from 5.1.0 jar.
Eclipse Helios rel: 20100617-1415,
 PDTVersion: 1.8.0.v201005280400-7B7C77CcNBGKBaIdEbPQ
Build id: 1.7.2.v20100528-1022

BTW,
In MySQL workbench 5.2.22 RC rev. 6091, only the wildcard select gives meaningful results.  Explicit selects show "BLOB" for the date value, either with concat() wrapper or without.

BTW, I can't determine if this is a connector/J bug or a PDT bug.

If it's a connector/J bug, it real should be an S2 since it's reporting erroneous data (much worse than a crash, if you ask me).

Above 2 comments should say DPT where they say PDT.  Sorry for any confusion.

Java can't represent dates with all-zeroes in them. There are configurations that will make Connector/J throw an exception when it hits an all-zero date, datetime or timestamp, but it seems your connection has been configured to not use them (they're enabled by default).

I found that this can be configured via connection properties with:
zeroDateTimeBehavior=convertToNull (or "exception" or "round").

I found that this can be configured via connection properties with:
zeroDateTimeBehavior=convertToNull (or "exception" or "round").

!Bg