Bug #389 Update of NULL field
Submitted: 7 May 2003 8:42 Modified: 19 May 2003 11:08
Reporter: Gabriele Scaroni Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:any OS:Any (any)
Assigned to: CPU Architecture:Any

[7 May 2003 8:42] Gabriele Scaroni 
Description:
If you try to update a field - for example a CHAR(50) - that is NOT NULL and you set it = NULL the field becomes '' (an empy string), while I think that it must return some kind of error. 

How to repeat:
create a table with some field and set one of them as NOT NULL.
Then try to update it:

UPDATE table SET field = NULL

Suggested fix:
MySQL must return some kind of error because if I set a field to NOT NULL i don't want that it assumes the NULL value, but i also don't want that = NULL becomes = '' without any kind of alert
[19 May 2003 11:08] Indrek Siitan 
This is how MySQL works. The main problem here is that MyISAM is a non-transactional table - so 
we cannot bail out on an error for invalid/out-of-range/etc data - if that would occur half-way 
through an UPDATE, some of the data would be updated (which, in MyISAM, cannot be rolled back) 
and leave the database in a non-consistent state.

It does however bump the Warnings count, which in the MySQL client is outputted as following:

Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 2

MySQL 4.1 will include a SHOW WARNINGS command, that will show the exact description of all 
warnings occured during the last INSERT/UPDATE statement.