MySQL Bugs: #19980: Columns set as "NOT NULL" receive a default "DEFAULT" value

Bug #19980	Columns set as "NOT NULL" receive a default "DEFAULT" value
Submitted:	21 May 2006 9:41	Modified:	21 May 2006 20:44
Reporter:	Claude Brown	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	V5	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
When I set a column as "NOT NULL" and do not provide a "DEFAULT" clause, I would expect all "INSERT" statements that do not provide the column to fail with an error of some sort.

However, I don't get such an error as it appears all NOT NULL columns have an automatic "DEFAULT" clause added to them.

I'm not sure if this is a bug, or intended behavior.  My view is that it shouldn't work like this unless I provide an explicit "DEFAULT" clause.

How to repeat:
Execute this SQL:

create table claude
(
            thing     datetime not null,
            other     int not null
);

insert claude (other) values (3);  -- I think this should fail

Note: the database within which this code was executed has a default table type of InnoDB.  Not sure if that is relevant.

Thank you for the bug report. Please use the adequate sql_mode for.

miguel@hegel:~/dbs/5.0$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.22-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table claude
    -> (
    ->             thing     datetime not null,
    ->             other     int not null
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> insert claude (other) values (3);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> set sql_mode="TRADITIONAL";
Query OK, 0 rows affected (0.02 sec)

mysql> insert claude (other) values (3);
ERROR 1364 (HY000): Field 'thing' doesn't have a default value
mysql>

Thanks. Sorry for wasting your time.