MySQL Bugs: #5986: TRADITIONAL: column with no default not handled correctly

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

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.

Back to the drawing board.

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.

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.

Fixed, pushed for 5.0.4.

Noted in 5.0.4 changelog.

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.

Ignore last comment (was intended for bug  #6229)