Bug #23412 delete rows with null date field
Submitted: 18 Oct 2006 8:30 Modified: 14 Nov 2006 19:13
Reporter: Ovidiu I Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.22-BK, 4.1.21 OS:Linux (linux)
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: date, delete

[18 Oct 2006 8:30] Ovidiu I
Description:
problems deleting rows from table with a null datetime field

How to repeat:
situation:

table definition: 

CREATE TABLE `users_usr` (
  `id_usr` int(11) NOT NULL auto_increment,
  `name_usr` varchar(150) NOT NULL default '',
  `lastlogin_usr` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id_usr`),
) ENGINE=MyISAM;

insert into users_usr values('','u1','');
insert into users_usr values('','u2','');

execute:
select * from users_usr where lastlogin_usr is null;

result: 
2 rows;

execute: 
delete from users_usr where lastlogin_usr is null;

result:
query ok, 0 rows deleted;

Suggested fix:
to delete that rows i must change the condition to lastlogin_usr = '0000-00-00 00:00:00'
[18 Oct 2006 15:32] Valeriy Kravchuk
Thank you for a problem report. Please, specify the exact version of MySQL server used. Use:

select version();

to get it.
[19 Oct 2006 7:49] Ovidiu I
mysql version is 4.1.21-log
[19 Oct 2006 8:34] Valeriy Kravchuk
Verified just as described with 4.1.22-BK on Linux:

openxs@suse:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `users_usr` (   `id_usr` int(11) NOT NULL auto_increment,
 `name_usr` varchar(150) NOT NULL default '',   `lastlogin_usr` datetime NOT NULL default '0000-00-00 00:00:00',   PRIMARY KEY  (`id_usr`) ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into users_usr values('','u1','');
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1265
Message: Data truncated for column 'id_usr' at row 1
*************************** 2. row ***************************
  Level: Warning
   Code: 1264
Message: Data truncated; out of range for column 'lastlogin_usr' at row 1
2 rows in set (0.00 sec)

mysql> insert into users_usr values('','u2','');
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from users_usr where lastlogin_usr is null;
+--------+----------+---------------------+
| id_usr | name_usr | lastlogin_usr       |
+--------+----------+---------------------+
|      1 | u1       | 0000-00-00 00:00:00 |
|      2 | u2       | 0000-00-00 00:00:00 |
+--------+----------+---------------------+
2 rows in set (0.00 sec)

The above IS A BUG! Column is declared as NOT NULL, so NO rows should be selected by lastlogin_usr IS NULL condition (we are not talking about auto_increment column here!!!):

mysql> select * from users_usr;
+--------+----------+---------------------+
| id_usr | name_usr | lastlogin_usr       |
+--------+----------+---------------------+
|      1 | u1       | 0000-00-00 00:00:00 |
|      2 | u2       | 0000-00-00 00:00:00 |
+--------+----------+---------------------+
2 rows in set (0.00 sec)

Where are that NULL values???

mysql> delete from users_usr where lastlogin_usr is null;
Query OK, 0 rows affected (0.00 sec)

DELETE statement works correct, though.
[19 Oct 2006 11:47] Ovidiu I
it's strage that when i select with condition lastlogin_usr is null to have results, and when delete with same condition to do nothing. i expected to delete the rows returned to select

sorry for my English.

the field is not null  but when i do select respond to is null and when i do delete don't respond to is null condition

it's a strange behavior
[20 Oct 2006 16:10] Andrey Hristov
For the curious, only valid for 4.1 series. 5.0 and 5.1 work correctly.
mysql> select version();
+------------------------------------+
| version()                          |
+------------------------------------+
| 5.1.13-beta-valgrind-max-debug-log |
+------------------------------------+
1 row in set (0.05 sec)

mysql> insert into users_usr values('','u1','');
Query OK, 1 row affected, 2 warnings (0.13 sec)

mysql> insert into users_usr values('','u2','');
Query OK, 1 row affected, 2 warnings (0.04 sec)

mysql> select * from users_usr where lastlogin_usr is null;
+--------+----------+---------------------+
| id_usr | name_usr | lastlogin_usr       |
+--------+----------+---------------------+
|      4 | u2       | 0000-00-00 00:00:00 |
|      3 | u1       | 0000-00-00 00:00:00 |
+--------+----------+---------------------+
2 rows in set (0.03 sec)

mysql> delete from users_usr where lastlogin_usr is null;
Query OK, 2 rows affected (0.04 sec)

-------------------------------------------------------------
mysql> select version();
+-------------------------------+
| version()                     |
+-------------------------------+
| 5.0.26-valgrind-max-debug-log |
+-------------------------------+
1 row in set (0.03 sec)

mysql> insert into users_usr values('','u1','');
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> insert into users_usr values('','u2','');
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql>
mysql> select * from users_usr where lastlogin_usr is null;
+--------+----------+---------------------+
| id_usr | name_usr | lastlogin_usr       |
+--------+----------+---------------------+
|      1 | u1       | 0000-00-00 00:00:00 |
|      2 | u2       | 0000-00-00 00:00:00 |
+--------+----------+---------------------+
2 rows in set (0.02 sec)

mysql> delete from users_usr where lastlogin_usr is null;
Query OK, 2 rows affected (0.03 sec)
[27 Oct 2006 13:13] Ramil Kalimullin
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
[14 Nov 2006 19:13] Paul DuBois
Noted in 4.1.23 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.