Jay Pipes wrote:
Hello all,
(and especially folks like Roy and Roland who have copies of the SQL
standard in the backs of their heads, :)
Or on a hard-drive close to me, which is the case :)
This week, we're in the middle of re-enabling the remaining test cases
for Drizzle, and I'm working on the test of NULL behaviour.
From what I understand, the SQL standard leave the behaviour of NULL
very much up to implementation, so the community should decide this one.
I wonder who told you that fairy tale...
The concept of a NULL is fairly complex, and I think the NULL value
would never have taken on if the semantics defining it was sloppy :)
What should happen when the following occurs?
CREATE TABLE t1 (id INT NOT NULL);
INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (NULL),(NULL);
The behaviour in both cases would be to throw an exception and reject
the statements. The only exception (I think) is the case where you have
a trigger that catches the NULL and an replaces it with something else.
But I guess there are people with more familiarity with triggers than me
who can tell you more about that.
Thanks,
Roy
Believe it or not, this is the current behaviour, as specified in the
MySQL manual (http://dev.mysql.com/doc/refman/5.1/en/insert.html):
"Inserting NULL into a column that has been declared NOT NULL. For
multiple-row INSERT statements or INSERT INTO ... SELECT statements, the
column is set to the implicit default value for the column data type.
This is 0 for numeric types, the empty string ('') for string types, and
the “zero” value for date and time types. INSERT INTO ... SELECT
statements are handled the same way as multiple-row inserts because the
server does not examine the result set from the SELECT to see whether it
returns a single row. (For a single-row INSERT, no warning occurs when
NULL is inserted into a NOT NULL column. Instead, the statement fails
with an error.)"
Therefore for the above example the correct behaviour is to throw an
error (1048) on the first insert, but on the second, multi-row insert,
use the implicit default value for an integer column (0) and throw a
warning for each row.
Ostensibly, the above behaviour was designed so that LOAD DATA and other
multi-row or batch operations would continue if a NULL value was
inserted into a NOT NULL column.
Please comment on whether the above behaviour is correct, or whether an
error should always be thrown in the case of a NULL being inserted into
a NOT NULL column.
Cheers,
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
