Bug #51660 allow empty fields in dates, integers, etc.
Submitted: 2 Mar 2010 22:47 Modified: 28 Dec 2012 19:22
Reporter: ivo welch Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[2 Mar 2010 22:47] ivo welch
Description:

Range (or enum) checking is wonderful, but it would be nice to have something in between superstrict and no restriction at all.  In particular, empty fields should be allowable:

[CODE]
set sql_mode = 'TRADITIONAL,ALLOW_INVALID_DATES';

create table q (d date DEFAULT NULL);  # DEFAULT NULL does not do what I wish
insert into q values ('0000-00-00'); --> ERROR 1292
insert into q values (''); --> ERROR 1292
[/CODE]

I asked elsewhere, and was told that I should read it into a different type of field, and then see if I can convert it.  of course, maybe this already exists---I am a newbie.  I hope this doesn't take too much time.

How to repeat:

everytime.

Suggested fix:

feature
[28 Dec 2012 19:22] Sveta Smirnova
Thank you for the report.

You use TRADITIONAL SQL mode which includes NO_ZERO_IN_DATE SQL mode. Just don't set such a mode and you will get desired results.

If you need other SQL modes which TRADITIONAL sets, specify them explicitly and don't use this shortcut.

This is not MySQL bug and this feature is already implemented.