MySQL Bugs: #23412: delete rows with null date field

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

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'

Thank you for a problem report. Please, specify the exact version of MySQL server used. Use:

select version();

to get it.

mysql version is 4.1.21-log

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.

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

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)

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

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.