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

[20 Oct 2005 16: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 17: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 18:56] Balazs Odor
I'll update `date` to the current date, where `date` is null (= '0000-00-00'), so update required!
[21 Oct 2005 10: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 21: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 16: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 16:28] Evgeny Potemkin
cset 1.2448.14.1
[1 Nov 2005 21:16] Evgeny Potemkin
Fixed in 5.0.16
[1 Nov 2005 21:37] Balazs Odor
The datetime field same.
[19 Jan 2006 21: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 17:19] Evgeny Potemkin
The same problem with DATETIME is fixed by this patch too.
[22 Feb 2006 18: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>