Roy Lyseng wrote:
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.
Yep.
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.
To be fair (to me!), the context of the discussion was around NOT NULL
columns, and therefore I did not include any discussion of behaviour of
TIMESTAMP when NULLs are allowed. The declared behaviour is summarized
here:
"TIMESTAMP columns are NOT NULL by default, cannot contain NULL values,
and assigning NULL assigns the current timestamp. However, a TIMESTAMP
column can be allowed to contain NULL by declaring it with the NULL
attribute. In this case, the default value also becomes NULL unless
overridden with a DEFAULT clause that specifies a different default
value. DEFAULT NULL can be used to explicitly specify NULL as the
default value. (For a TIMESTAMP column not declared with the NULL
attribute, DEFAULT NULL is illegal.) If a TIMESTAMP column allows NULL
values, assigning NULL sets it to NULL, not to the current timestamp. "
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
