| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.14-log | OS: | Linux (Debian Linux) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[20 Oct 2005 17:46]
MySQL Verification Team
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>
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)