My viewpoint: If I say "don't do that", then don't let me do it. If I told you once, I told you a thousand times....(sorry, channeling my inner Jewish mother)....
Therefore, if I say NOT NULL, I mean "no NULL allowed". No, really. I mean it. Drizzle shouldn't act like a 3-year-old. -Sheeri On 12/16/08, Jay Pipes <[email protected]> wrote: > > Hello all, > > (and especially folks like Roy and Roland who have copies of the SQL > standard in the backs of their heads, :) > > 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. > > 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. > > 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<https://launchpad.net/%7Edrizzle-discuss> > Post to : [email protected] > Unsubscribe : > https://launchpad.net/~drizzle-discuss<https://launchpad.net/%7Edrizzle-discuss> > More help : https://help.launchpad.net/ListHelp > -- - Sheeri K. Cabral Note: Currently I work for The Pythian Group (http://www.pythian.com/ ), a remote DBA firm, and consulting would constitute a conflict of interest. However, I do recommend our work, and if you're looking for DBA work (I do MySQL work, but they have great Oracle and SQL Server DBAs too) right now, whether you need a few hours a week, or someone fulltime while you continue a job search. I have a webpage with tips on how to find a DBA: http://sheeri.net/how-to-find-a-dba/ I can recommend http://www.toomanyconnections.com as a good site to find MySQL (and Oracle) DBAs. If you're looking for web design, I recommend the small business my husband works for, http://www.digital-loom.com I regret that I have no other information for any other type of position, including developer, programmer and systems administrator.
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
