MySQL Bugs: #43654: "not null" is not working

Bug #43654	"not null" is not working
Submitted:	14 Mar 2009 18:21	Modified:	14 Mar 2009 20:18
Reporter:	Ari Arantes	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0.77	OS:	FreeBSD
Assigned to:		CPU Architecture:	Any

Description:
"not null" field is not working how it should.

How to repeat:
mysql> create table table1 (id integer not null primary key, name varchar(30) not null) engine innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into table1 (id) values (2);
Query OK, 1 row affected, 1 warning (0.00 sec)

I think MySQL should not insert the value above, because "name" was not specified and it is required.

The same error:
mysql> insert into table1 (name) values ('A');
Query OK, 1 row affected, 1 warning (0.00 sec)

The "id" field is required and MySQL inserted even without its value.

More errors with "NOT NULL":

mysql> insert into table1 values (3, null);
ERROR 1048 (23000): Column 'name' cannot be null
mysql> insert into table1 values (3, '');
Query OK, 1 row affected (0.00 sec)

I found explanation about sql-mode. But I think "not null" should by default.

Thank you for the bug report. Please read the Manual about sql_mode and default values:

http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

"If strict SQL mode is not enabled, MySQL sets the column to the implicit default value for the column data type."

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.80-Win x86-64 bzr revno:2772-log Source distribution

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

mysql 5.0 > use test
Database changed
mysql 5.0 > set sql_mode=STRICT_TRANS_TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql 5.0 > create table table1 (id integer not null primary key, name varchar(30) not null)
    -> engine innodb;
Query OK, 0 rows affected (0.16 sec)

mysql 5.0 > insert into table1 (id) values (2);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql 5.0 > insert into table1 (name) values ('A');
ERROR 1364 (HY000): Field 'id' doesn't have a default value