Hi! On Tue, Dec 16, 2008 at 7:45 PM, 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, :)
Like Roy, I trust my hard disk rather than the back of my head. > From what I understand, the SQL standard leave the behaviour of NULL very > much up to implementation, so the community should decide this one. Mm, like what Roy uttered - I think it is defined pretty well. NULL-ordering is implementation defined in the sense that they can either come before all or after all other values. Anyway, here goes: 4.13 Columns, fields, and attributes .... Every column has a nullability characteristic that indicates whether the value from that column can be the null value. A nullability characteristic is either known not nullable or possibly nullable. Let C be a column of a base table T. C is known not nullable if and only if at least one of the following is true: — There exists at least one constraint NNC that is not deferrable and that simply contains a <search condition> that is a <boolean value expression> that is a known-not-null condition for C. .... and this follows way later on: ISO/IEC 9075-2:2003 (E) 11.4 <column definition> .... a) If a <column constraint definition> is specified that contains the <column constraint> NOT NULL, then it is equivalent to the following <table constraint definition>: CND CHECK ( C IS NOT NULL ) CA So, if you put these together, NOT NULL means the column cannot contain a NULL value. I think this settles it quite clearly. > 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. Not with sql_mode := 'STRICT_ALL_TABLES'. With this setting, MySQL correctly rejects both statements. > > 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. Well, that surprises me. I mean, LOAD DATA INFILE *does* manage to crap it up (in MySQL at least - didn't test it on drizzle). So that seems to indicate that NULL handling for the statements you gave is effectively isolated from LOAD DATA INFILE. So I am guessing, in MySQL NULL checking sits in the parser, and LOAD DATA INFILE bypasses that, and makes you lose. Full session: mysql> truncate table t1; Query OK, 0 rows affected (0.06 sec) mysql> set sql_mode:='STRICT_ALL_TABLES'; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values (null); ERROR 1048 (23000): Column 'id' cannot be null mysql> insert into t1 values (null), (null); ERROR 1048 (23000): Column 'id' cannot be null mysql> select * from t1; Empty set (0.00 sec) mysql> load data local infile 'D:/tmp/t.txt' into table t1 fields escaped by '\\' lines terminated by '\r\n'; Query OK, 4 rows affected, 4 warnings (0.06 sec) Records: 4 Deleted: 0 Skipped: 0 Warnings: 4 Warning (Code 1263): Column set to default value; NULL supplied to NOT NULL column 'id' at row 1 Warning (Code 1263): Column set to default value; NULL supplied to NOT NULL column 'id' at row 2 Warning (Code 1263): Column set to default value; NULL supplied to NOT NULL column 'id' at row 3 Warning (Code 1263): Column set to default value; NULL supplied to NOT NULL column 'id' at row 4 mysql> select * from t1; +----+ | id | +----+ | 0 | | 0 | | 0 | | 0 | +----+ 4 rows in set (0.00 sec) > 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. The current default behaviour is incorrect. STRICT_ALL_TABLES is closer, but what is still incorrect is the behaviour of accepting the NULLs through LOAD DATA INFILE. (Personally, I think it would be cool if drizzle'd fix LOAD DATA INFILE) I hope this helps. > > 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 > -- Roland Bouman http://rpbouman.blogspot.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
