MySQL Bugs: #43796: Update query doesnt get executed to set the date field to null value

Bug #43796	Update query doesnt get executed to set the date field to null value
Submitted:	23 Mar 2009 7:44	Modified:	27 Apr 2009 5:56
Reporter:	pritam m	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Windows (Intel(R) Pentium(R) 4 CPU 2.40 GHz 2.41GHz )
Assigned to:		CPU Architecture:	Any
Tags:	Data Type 'Date', Field 'NULL' allowed, update query

Description:
This is -  
1)What I did:
I have a table celebmaster having field `DateOfDeath` date default NULL
And I executed the query,

UPDATE celebmaster SET IsActive='y',DOB='1988-7-1',DateOfDeath=null, WHERE CelebID='14227'

Above query is simplified to contain only part of our interest.
And in above case table entry for DateOfDeath is already not null or contains some date value, then I can not update it to null value.
2)What I expected:
It shuld have updated the table entry with null value for DateOfDeath field.

3)But What happened actually:
Query failed with the Error:Incorrect date value: '' for column 'DateOfDeath' at row 1

I have tried the query with using different syntax like DateOfDeath='' or DateOfDeath=Null or DateOfDeath=NULL but it doesnt make any difference!(which was actually fun to try!)

Thanks & Regards,
Pritam M

How to repeat:
Repeat this Bug as below:
1)Create table tblTest having dTest column with data type 'Date' with null value allowed to it
2)Insert a record with dTest setting to some date value say '10/03/2008'
3)Update table tblTest to clear the date entry in column dTest or to set it to null value.
eg."Update tbltest set dTest=null where TestID = 1" 
4)You should get an error "Incorrect date value: '' for column 'dTest' at row 1"

Suggested fix:
Query should execute for update date field if it allows null and set null value to the table entry.

Thank you for the problem report. What exact version of MySQL server do you use (3.7.7.1 is NOT a server version)? Please, send also the exact test case instead of a description. We need complete CREATE TABLE and INSERT(s) to be able to explain the results you get.

Sory for Inconvenience.Please note down the version number is 5.0

Thanks & Regards,
pritam M

It is not simply 5.0, but 5.0.x, and that "x" part is important. 5.0.77 is the latest and we do not fix bugs in older versions. 

Also, what about a complete test case?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Slight Change in the query forming technique made this thing to happen work.
Thanks for the support.
Regards