Bug #26486 "default NULL" silently inserted with STRICT_ALL_TABLES

• Submitted: 20 Feb 2007 3:54
• Modified: 5 Mar 2007 20:16
• Reporter: Pete Harlan (Candidate Quality Contributor)
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S4 (Feature request)
• Version: 5.0.36 BK
• OS: Linux (Debian Linux (etch) x86_64)
• Assigned to: Paul DuBois
• CPU Architecture: Any

[20 Feb 2007 3:54] Pete Harlan

Description:
The documentation says, "A value is missing when a new row to be inserted does not contain a value for a column that has no explicit DEFAULT clause in its definition."

That text appears here: <http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html>

I found that sentence misleading, because even with STRICT_ALL_TABLES, if a column allows NULL values and no default is given in the create table statement, MySQL treats the column specification as though "default NULL" were explicitly written (and prints it that way in a "show create table", for example.)

How to repeat:
drop table if exists foo;
create table foo (i int, j int not null);
insert into foo (j) values (4);
select * from foo;

-- Shows that (NULL, 4) was inserted into foo

Suggested fix:
Add "non-NULL to the sentence in the documentation so that it reads:

"A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition."

If you want to add an SQL mode that allows the user to specify that they don't want "default NULL" inserted for them for columns that allow NULL, that would be a bonus.  But I find the current behavior reasonable, just not clearly documented.

Thanks.

[5 Mar 2007 20:16] Paul DuBois

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.