Jay Pipes wrote:
All,
First, a HUGE thank you to all who participated in the discussion. It
was, as is usually the case in an open, transparent forum, very
enlightening.
It was clear after reading all the discussion:
a) that the SQL standard is indeed clear in the area
b) that Drizzle should throw an error when a NULL value is inserted
manually into a NOT NULL column (with 2 notable exceptions, see below)
c) that there is a possible bug in LOAD DATA INFILE in both MySQL and
Drizzle which, even in sql_mode=strict_xxx (MySQL) LOAD DATA INFILE does
not throw an error
Therefore, going forward, I will ensure our null-behaviour test case
tests for appropriate error throwing in all cases, including LOAD DATA
INFILE and multi-row INSERTs.
BTW, the two exceptions to the rule are:
1) AUTOINCREMENT fields, where inserting a NULL will insert the next
autoincrement value
AUTOINCREMENT is much like a trigger, so this is acceptable because it
is a clearly defined rule for the column.
2) For TIMESTAMP fields, where inserting a NULL inserts the current
timestamp
I am more uncertain about this. When the column is nullable, is the
current timestamp being inserted or is a NULL value inserted? In the
former case, how will you ever be able to insert NULL? I think this
behaviour would be less confusing if you required an explicit trigger
definition, eg.
TS TIMESTAMP ON INSERT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
MySQL supports the ON UPDATE clause, but uses DEFAULT for the insert
case. This is probably stretching the semantics of DEFAULT a little...
Thanks,
Roy
Cheers, and thanks all for your help!
Jay
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
