I love it when my assumptions line up with the sql standard. On Tue, Dec 16, 2008 at 11:28 AM, Roy Lyseng <[email protected]> wrote: > > > 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 >
-- Eric Bergen [email protected] http://www.provenscaling.com _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
