Hi Jay
On 17/12/2008, at 4:45 AM, Jay Pipes wrote:
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);
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.
I think this is the key issue, this behaviour was designed to deal
with a non-transactional environment.
Since Drizzle is getting rid of that architecture, the reason-to-be
for this behaviour has disappeared also.
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.
Yes. Rationale: if a column was defined as NOT NULL, then NULL is not
valid. Magically changing it into some value (whatever it may be) is
not good; MySQL did just it by necessity because of MyISAM being non-
transactional.
Cheers,
Arjen.
--
Arjen Lentz, Director @ Open Query (http://openquery.com.au)
Training and Expertise for MySQL and related tools
OurDelta: free enhanced builds for MySQL @ http://ourdelta.org
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
