MySQL Bugs: #30862: NULL treatments from JDBC

Bug #30862	NULL treatments from JDBC
Submitted:	6 Sep 2007 13:38	Modified:	6 Sep 2007 19:40
Reporter:	Marc Mirandews	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	MySQL 5.1.X	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Well, I don''t know if that's a bug or a missing feature, but I have problems with NULLs on JDBC.

If you put a clause NOT NULL in SQL database definition, then I can't insert a NULL value with statement.setString(x,null), even if it is a DEFAULT value defined or if I am in whichever SQL_mode.

But that's not true with timestamp, because a NULL value is converted always to NOW(), even if it has a explicit NOT NULL clause (I know it's documented, but i don't understand it).

So, first of all it would be coherent that all data types have same behaviour.

How to repeat:
It's documented so I don't think it's necessary...

Suggested fix:
Timestamps must not accept NULL values if they're defined as NOT NULL.

Thank you for the bug report. That is the timestamp data type behavior and
documented. Please read:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-type-overview.html

'You can also set any TIMESTAMP column to the current date and time by
assigning it a NULL value. Variations on automatic initialization and
update properties are described in Section 10.3.1.1, “TIMESTAMP Properties”.' 

and:

http://dev.mysql.com/doc/refman/5.1/en/timestamp.html

Thanks in advance.

Thank you first of all, 

...then it will be a feature request, so I want all my data throws an error if I insert a NULL from high level programming. So if I send a NULL to a Timestamp NOT NULL, why consider it as NOW()? That behaviour is not common with other types, an int doesn't convert a NULL to a zero if has NOT NULL, neither a String converts a NULL insert/update into an empty string. Must I parse every form data containing a date value in order to keep my database coherent (specially in (un)subscribe date cases)? 
A NULL always means "not known value" according to MySQL policy, so NOW() is not an unknown value...  

Thank you for your effort, I don't want to mean critical, I love to have the chance to speak and help to make better MySQL to all of us, specially those who work every day in make it better, and I asume you are the experts and have good reasons for all, and if so, I just want to see the meaning. 

Marc