MySQL Bugs: #32043: Some Error While Updating a row

Bug #32043	Some Error While Updating a row
Submitted:	2 Nov 2007 5:26	Modified:	2 Nov 2007 8:23
Reporter:	Anish R	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.27	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	insert, NOT NULL, UPDATE

Description:
Hello Friends,

I have found a major issue in MySQL.

I have created a table named

'ahs_sports' using the following command.

CREATE TABLE `ahs_sports` (
  `sport_id` int(11) NOT NULL
   auto_increment,
  `sport_desc` varchar(255) NOT NULL,
  `sports_order` int(11) NOT NULL,
  PRIMARY KEY  (`sport_id`),
  UNIQUE KEY `sport_desc` (`sport_desc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

In this table the 'sport_desc' is NOT NULL,
When I am inserting data to this table. If I leave the "sport_desc" filed NULL MySQL will return error(This is the correct behavior). If I provide data for all mandatory fields (not null) the data is added (Also correct behavior).

But the problem is when I am trying to update the existing row with a NULL for the field "sport_desc" the field  is set to NULL. I have used the Query shown below to Update the table.

UPDATE ahs_sports SET sport_desc = NULL WHERE `sport_id` =39

NOT NULL rule is always applicable to INSERT and UPDATE.

Kindly verify the above issue.

Thanks,
Anish R.

How to repeat:
Please verify the above issue

Suggested fix:
I am requesing to the mysql developing team

Depending on your SQL_MODE settings such illegal values are either changed to the default value for the column ('' in this case) and a warning is created, or an error is thrown for the strict modes.

In your case the update was accepted so the first of the above was the case and you sould have received a result like:

mysql> update ahs_sports set sport_desc = NULL where sport_id=1;
Query OK, 1 row affected, 1 warning (0.31 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+------------------------------------+
| Level   | Code | Message                            |
+---------+------+------------------------------------+
| Warning | 1048 | Column 'sport_desc' cannot be null | 
+---------+------+------------------------------------+
1 row in set (0.00 sec)