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 17:26	Modified:	27 Jan 2015 15:55
Reporter:	Trudy Pelzer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.2-debug	OS:	Linux (SuSE 9.1)
Assigned to:	Paul DuBois	CPU Architecture:	Any

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.

This behaviour is still present on current mysql-trunk (myisam/innodb).

Setting to 'verified' as we are discontinuing the use of 'to be fixed later'.

Already documented:

http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_default

DEFAULT(col_name)
Returns the default value for a table column. An error results if the column has no default value.

No further action required.