| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.0 | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[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).
[9 Jul 2020 16:57]
Bob Terrell
Sixteen years later, this issue has caused a problem in my application. This is still the behavior as of 8.0. I submit the following for consideration: 1. No value should match both IS NULL and IS NOT NULL. 2. If '0000-00-00' is an issue for a particular connector, perhaps it should only be an issue for that particular connector. 3. This does not appear to be documented anywhere except on this bug page and the aforementioned change log. It belongs somewhere like here: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-type-syntax.html 4. If NULL and '0000-00-00' are equivalent, perhaps affected columns should not have a NOT NULL option; they should always be NULL.
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;