Bug #14186 select datefield is null not updated
Submitted: 20 Oct 2005 18:23 Modified: 22 Feb 2006 19:14
Reporter: Balazs Odor
Status: Closed
Category:Server Severity:S3 (Non-critical)
Version:4.1.14-log OS:Linux (Debian Linux)
Assigned to: Evgeny Potemkin Target Version:

[20 Oct 2005 18:23] Balazs Odor 
Description:
select ... where datefield is null: 1 rows in set
then
update ... where datefield is null: 0 rows affected

How to repeat:
> create table `test` (
`id` bigint(20) unsigned not null auto_increment,
`name` varchar(255) not null default '',
`date` date not null default '0000-00-00',
primary key (`id`)
) engine=MyISAM;

> insert into `test` (`name`,`date`) values ('Joe',CURDATE()), ('Smith',NULL);

> select * from `test` where `date` is null;
1 row in set (0.00 sec)

> update `test` set `date` = CURDATE() where `date` is null;
Query OK, 0 rows affected (0.00 sec)
[20 Oct 2005 19:46] Sinisa Milivojevic 
Hi!

Thank you for writting to us.

Your test case demonstrates so nicely one beautifull optimisation in MySQL.

If a = 1 and you want to SET a = 1, MySQL will skip that row as there is no need to change
the row. This saves lot's of time as writes are usually expensive disk-wise.
[20 Oct 2005 20:56] Balazs Odor 
I'll update `date` to the current date, where `date` is null (= '0000-00-00'), so update
required!
[21 Oct 2005 12:47] Sergei Golubchik 
You're right, of course.

What you hit is documented as

   * `Zero' date or time values used through MyODBC are converted
     automatically to `NULL' in MyODBC 2.50.12 and above, because ODBC
     can't handle such values.

That's why ('0000-00-00' IS NULL) is true.
But it doesn't work in update, which I believe is a bug.
[27 Oct 2005 23:25] 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/internals/31585
[1 Nov 2005 17:28] Evgeny Potemkin 
Date field was declared as not null, thus expression 'datefield is null'
was always false. For SELECT special handling of such cases is used. 
There 'datefield is null' converted to 'datefield eq "0000-00-00"'.

Fixed in 4.1.16
[1 Nov 2005 17:28] Evgeny Potemkin 
cset 1.2448.14.1
[1 Nov 2005 22:16] Evgeny Potemkin 
Fixed in 5.0.16
[1 Nov 2005 22:37] Balazs Odor 
The datetime field same.
[19 Jan 2006 22:43] Mike Hillyer 
If DATETIME has the same problem should this be documented yet? Or should we wait for a
fix on DATETIME as well?
[14 Feb 2006 18:19] Evgeny Potemkin 
The same problem with DATETIME is fixed by this patch too.
[22 Feb 2006 19:14] Mike Hillyer 
Documented in 4.1.16 and 5.0.16 changelogs:

      <listitem>
        <para>
          When a date column is set <literal>NOT NULL</literal> and
          contains <literal>0000-00-00</literal>, it will be updated for UPDATE
          statements that contains <literal><replaceable>columnname</replaceable> IS
NULL</literal> in the WHERE
          clause. (Bug #14186)
        </para>
      </listitem>