Bug #940 zero date is both null and not null in where clause
Submitted: 28 Jul 2003 17:35 Modified: 17 Aug 2004 0:47
Reporter: Michael Futeran Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[28 Jul 2003 17:35] Michael Futeran
Description:
When a datetime field is created with not null, 
and a value of '0000-00-00 00:00:00' is inserted
the where clause of 'is null' and 'is not null' both match.

This seems to cause rs.getDate to return null with connector J 3.0.8
and not null with 2.0.13.

How to repeat:
create temporary table _tmp (a datetime not null);
insert into _tmp values ('');
select a from _tmp where a is null;

-> a
-> 0000-00-00 00:00:00

select a where a is not null;

-> a    
-> 0000-00-00 00:00:00

Suggested fix:
Make the zero date match only 'is not null' in the where clause and make rs.getDate, rs.wasNull = false;
[29 Jul 2003 17:25] Michael Futeran
I have confirmed that mysql-connector-java-2.0.14-bin.jar sets rs.wasnull to false (correctly) and mysql-connector-java.3.0.8-stable-bin.jar sets rs.wasnull to true (incorrectly) following and rs.getDate where the date is '0000-00-00 00:00:00'.

Hope this helps.

Thank you.
[30 Jul 2003 1:19] Lenz Grimmer
Assigned to Mark, as this looks rather like a Connector/J issue.
[30 Jul 2003 1:32] Sergei Golubchik
No. It's assigned to Monty.
It's not Connector/J issue - works for command line client too.

This change is some delibetare check in the code that changes "not_null_date_column IS NULL"
to "not_null_date_column = 0". This check is there for at least 3-4 years already. Nobody but Monty can remember the reason for that.
[31 Jul 2003 18:10] Mark Matthews
This is a documented work-around (in the CHANGELOG) for the non-standard behavior of MySQL setting invalid dates to all zeroes. Java itself _can_not_ handle dates with these values, which is why the JDBC driver _has_ to convert it to NULL.
[28 Jun 2004 11:34] Sergei Golubchik
"the reason for that" is odbc compatibility
[30 Jul 2004 19:24] Sergei Golubchik
It's documented as:

     To be able to work well with ODBC programs, MySQL supports the
     following extra features when using `IS NULL':

     [...]

        * For `DATE' and `DATETIME' columns that are declared as `NOT
          NULL', you can find the special date `'0000-00-00'' by using
          a statement like this:

               SELECT * FROM TBL_NAME WHERE DATE_COLUMN IS NULL

          This is needed to get some ODBC applications to work because
          ODBC doesn't support a `'0000-00-00'' date value.

but even then I think the value cannot be both NULL and NOT NULL
[17 Aug 2004 0:47] Brian Aker
Using strict mode in a later version will solve this (since then you won't end up with bad dates).