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:
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

[5 Jun 2008 5:05] thanh hai cao
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.

Thanh Hai

How to repeat:
- windows XP
- PDT 1.0.2
- Connector/J 5.1
- mysql 5.0
[6 Oct 2008 10:09] Rick Bennett
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!
[20 Jul 2009 9:40] Tonci Grgin
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.
[20 Aug 2009 23:00] Bugs System
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".
[22 Oct 2010 20:31] Drew Bertola
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)
    ('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;

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

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.
[22 Oct 2010 20:34] Drew Bertola
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).
[22 Oct 2010 20:40] Drew Bertola
Above 2 comments should say DPT where they say PDT.  Sorry for any confusion.
[22 Oct 2010 21:00] Mark Matthews
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).
[23 Oct 2010 8:30] Drew Bertola
I found that this can be configured via connection properties with:
zeroDateTimeBehavior=convertToNull (or "exception" or "round").
[23 Oct 2010 8:30] Drew Bertola
I found that this can be configured via connection properties with:
zeroDateTimeBehavior=convertToNull (or "exception" or "round").
[25 Oct 2010 6:57] Tonci Grgin