MySQL Bugs: #6229: Inconsistent handling of DEFAULT on a NOT NULL column

Bug #6229	Inconsistent handling of DEFAULT on a NOT NULL column
Submitted:	23 Oct 2004 19:26	Modified:	28 Apr 2005 16:42
Reporter:	Trudy Pelzer
Status:	To be fixed later
Category:	Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.2-debug	OS:	Linux (SuSE 9.1)
Assigned to:	Ramil Kalimullin	Target Version:

Description:
When sql_mode='' and a column is defined as NOT NULL, attempts to insert a
record should insert the "default" default value for the column data type (e.g. zero for
a numeric column) when no value is specified for that column. This appears to happen only
sometimes. 

This bug is probably related to Bug#5986, which deals with the same situation
when sql_mode='traditional'.

How to repeat:
mysql> set sql_mode = '';

mysql> create table t1 (col1 int not null);

mysql> show create table t1;
+-------+--------------------------------------------------+
| Table | Create Table                                                      |
+-------+--------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `col1` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------+
-- This is the correct response; we are no longer changing the
column definition to include an explicit DEFAULT=0 clause.

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)
-- This is the correct response; we are still using the implicit default
value for the INSERT (providing sql_mode is not 'traditional'.

mysql> insert into t1 values(default);
Query OK, 1 row affected (0.00 sec)
-- This is the correct response.

mysql> insert into t1 values(default(col1));
ERROR 1364 (HY000): Field 'col1' doesn't have a default value
-- This is the incorrect response because it is inconsistent with the previous two
examples. The correct response is to INSERT a row with {0}.

mysql> select * from t1;
+------+
| col1 |
+------+
|    0 |
|    0 |
+------+
-- The table should have 3 rows, all with {0}.

mysql> drop table t1;

Verified against latest BK source.

i disagree with the premise of this bug -- default(col) for a column that has no default
should always be an error. the documentation says this explicitly.

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).

In the future we should look at also giving errors for VALUES() and VALUES(DEFAULT) in
not strict mode

I have updated the manual regarding this issue.