| Bug #8143 | A date with value 0 is treated as a NULL value | ||
|---|---|---|---|
| Submitted: | 26 Jan 2005 13:00 | Modified: | 15 Aug 2006 3:19 |
| Reporter: | a b | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.8-standard-log | OS: | Linux (SuSE Linux 8.2) |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
[26 Jan 2005 13:07]
MySQL Verification Team
Verified with 4.1.10-debug-log
[23 May 2005 13:56]
Michael Widenius
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: This is not a bug but instad intended behaviour. See the manual section http://dev.mysql.com/doc/mysql/en/comparison-operators.html for more information (search after IS NULL)
[24 May 2005 12:32]
Michael Widenius
I reopened this as I missed the fact that DELETE now is not consistent to SELECT and this should be fixed at least in 5.1
[2 Jun 2006 11:34]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/7205
[23 Jul 2006 10:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/9460
[3 Aug 2006 16:47]
Magnus Blåudd
Pushed to 5.0.25
[4 Aug 2006 3:34]
Paul DuBois
Noted in 5.0.25 changelog. For ODBC compatibility, MySQL supports use of WHERE col_name IS NULL for DATE or DATETIME columns that are NOT NULL, to allow column values of '0000-00-00' or '0000-00-00 00:00:00' to be selected. However, this was not working for WHERE clauses in DELETE statements.
[14 Aug 2006 20:54]
Konstantin Osipov
Pushed into 5.1.12
[15 Aug 2006 3:19]
Paul DuBois
Noted in 5.1.12 changelog.
[27 Oct 2006 13:10]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/14477 ChangeSet@1.2610, 2006-10-27 18:08:50+05:00, ramil@mysql.com +3 -0 Fix for bug #23412: delete rows with null date field Backport of the fix for bug #8143: A date with value 0 is treated as a NULL value
Description: A table is defined with non-null date field. When this date field has the value 0 (i.e. 0000-00-00), it is treated as a null value in select operations. How to repeat: drop table if exists nulltest; create table nulltest ( id integer not null primary key auto_increment, datum date not null ); insert into nulltest (datum) values (0); select * from nulltest where datum is null; -- in the previous line the error occurs: the single row of the table is selected select * from nulltest where datum = 0; delete from nulltest where datum is null; -- delete does not show this erroneous behaviour delete from nulltest where datum = 0;