MySQL Bugs: #36508: inserting null value to not null column with default value explicitly defined

Bug #36508	inserting null value to not null column with default value explicitly defined
Submitted:	5 May 2008 15:15	Modified:	6 May 2008 16:45
Reporter:	Eugene Toropov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.1.24	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.1.24-rc-community MySQL Community Server (GPL)

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

mysql> create table a(b varchar(20) not null default '');
Query OK, 0 rows affected (0.17 sec)

mysql> insert into a set b = null;
ERROR 1048 (23000): Column 'b' cannot be null

I expect last command (insert into...) should be successfully executed. As a result I expect to see default value ('') instead of null inserted to "a" table.

This can be reproduced on Windows and Linux systems.

How to repeat:
See description

Suggested fix:
The explicit default value should be inserted instead of null

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You explicitly indicated value should be not null. So explicit insert of NULL value rejects. Other RDBMS such as PostgreSQL and MSSQL behaves same way.

Use syntax like: insert into a values(); or insert into a set b = default; if you want use default value.

Hi Sveta. Could you please show me the place in docs where this situation is described. I found only this page: http://dev.mysql.com/doc/refman/5.1/en/data-type-defaults.html , but my situation is not described there (not null + default explicitly defined).

I'm not sure this is not a bug, because this works fine in MySQL 4.1.

One additional detail is (I believe) that writing NULL to a TIMESTAMP NOT NULL will insert CURRENT_TIMESTAMP.

Maybe this confusion comes from here?

@eugene ..

It does not work for me as you describe it in neither 4.0 or 4.1!  

And one detail about the use of this system:  If you want Sveta to be notified when you post you will need to change status from 'not a bug' to 'open'.  As long as status is 'not a bug' nobody will see your posts and nothing will happen!

I'm sorry. I mage a mistake. INSERT fails in both cases (MySQL 4/5). But as for update - it works with warning on 4.x and fails on 5.x:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 363079 to server version: 4.1.15-standard-log

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

mysql> create table a(b varchar(20) not null default '');
Query OK, 0 rows affected (0.01 sec)

mysql> insert into a set b = '123';
Query OK, 1 row affected (0.00 sec)

mysql> update a set b = null;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1263 | Data truncated; NULL supplied to NOT NULL column 'b' at row 1 |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

Is it correct?

Changing status...

Yes, this is documented.

About UPDATE at http://dev.mysql.com/doc/refman/5.1/en/update.html:

If you update a column that has been declared NOT NULL by setting to NULL, the column is set to the default value appropriate for the data type and the warning count is incremented. The default value is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types.

About INSERT at http://dev.mysql.com/doc/refman/5.1/en/constraint-invalid-data.html:

If you try to store NULL into a column that doesn't take NULL values, an error occurs for single-row INSERT  statements. For multiple-row INSERT  statements or for INSERT INTO ... SELECT statements, MySQL Server stores the implicit default value for the column data type. In general, this is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types.