Bug #5986 TRADITIONAL: column with no default not handled correctly
Submitted: 8 Oct 2004 16:29 Modified: 28 Apr 2005 14:42
Reporter: Paul Dubois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.2 OS:
Assigned to: Jim Winstead CPU Architecture:Any

[8 Oct 2004 16:29] Paul Dubois
Description:
If a non-NULL column has no default value, attempts to insert
a record should fail when no value is specified for that
column.  This appears to happen only sometimes.

How to repeat:

mysql> set sql_mode = traditional;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t (i int not null) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values ();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (default);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (default(i));
ERROR 1364 (HY000): Field 'i' doesn't have a default value

All three of the insert statements should fail. Only the third
one actually fails.
[13 Jan 2005 1:31] Jim Winstead
Back to the drawing board.
[14 Jan 2005 4:06] Jim Winstead
New patch with correct behavior in non-TRADITIONAL mode, too. (Now issues warnings for all 
cases where a default value is forced upon a field with no default set.)

Also, a second patch is in the works that reports warnings for cases where the default value is 
used implicitly, like "INSERT INTO t VALUES ()", which does not currently happen.
[5 Apr 2005 15:01] Jim Winstead
http://lists.mysql.com/internals/20676

ChangeSet@1.1761, 2005-01-13 18:23:34-08:00, jimw@mysql.com
  In TRADITIONAL mode, don't allow a NOT NULL field with no default be set to
  DEFAULT (with no argument) or to the field's type's default by not being
  listed in the list of fields being inserted. (Bug #5986)                    

http://lists.mysql.com/internals/20708

ChangeSet@1.1762, 2005-01-14 17:09:35-08:00, jimw@mysql.com
  Enable warnings for 'no default' fields being set to default when they
  are not specified in an insert. Most of these changes are actually to
  clean up the test suite to either specify defaults to avoid warnings,
  or add the warnings to the results. Related to bug #5986.
[6 Apr 2005 4:20] Jim Winstead
Fixed, pushed for 5.0.4.
[12 Apr 2005 2:17] Paul Dubois
Noted in 5.0.4 changelog.
[28 Apr 2005 14:35] Michael Widenius
I agree with Jim here. We should keep the current behavior for now.

The reason are:

- 5.0 is already in beta and we should do any behavior changes (if things
  are not really critical, which this is not)
- DEFAULT(column_name) is a new function in 5.0 for which we can be more
  strict with this than before
- We do in not strict mode generate warnings for the cases () and (default)
  so the users will have a chance to notice the error.
- With our current code, it's not trivial to change "VALUES (DEFAULT) " to give an error in this case (becasue we don't know in fix_fields which column this is refering to).

I have updated the manual regarding this issue.
[28 Apr 2005 14:39] Michael Widenius
Ignore last comment (was intended for bug  #6229)