Bug #21706 NULL rejected for NOT NULL column in non-strict mode
Submitted: 18 Aug 2006 1:39 Modified: 18 Sep 2006 4:27
Reporter: Jon Stephens Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.1.11/5.1.12-bk OS:Windows (Windows/Linux)
Assigned to: MySQL Verification Team CPU Architecture:Any

[18 Aug 2006 1:39] Jon Stephens
Description:
According to the Manual, if the server is running in the default mode, attempting to insert a NULL into a NOT NULL column should use the implicit default. However, in the case of a single row, the insert is rejected.

mysql> SELECT VERSION();
+----------------------+
| VERSION()            |
+----------------------+
| 5.1.12-beta-20060817 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.SQL_MODE;
+-------------------+
| @@GLOBAL.SQL_MODE |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT @@SESSION.SQL_MODE;
+--------------------+
| @@SESSION.SQL_MODE |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE a (b INT NOT NULL);
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO a VALUES (NULL);
ERROR 1048 (23000): Column 'b' cannot be null
mysql> SELECT * FROM a;
Empty set (0.01 sec)

However, the implicit default *is* used in a multi-row insert:

mysql> INSERT INTO a VALUES (2), (NULL), (3);
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+---------------------------+
| Level   | Code | Message                   |
+---------+------+---------------------------+
| Warning | 1048 | Column 'b' cannot be null |
+---------+------+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM a;
+---+
| b |
+---+
| 2 |
| 0 |
| 3 |
+---+
3 rows in set (0.04 sec)

mysql> CREATE TABLE c (d INT NOT NULL DEFAULT 0);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO c VALUES (NULL);
ERROR 1048 (23000): Column 'd' cannot be null
mysql> CREATE TABLE e (f VARCHAR(20) NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO e VALUES (NULL);
ERROR 1048 (23000): Column 'f' cannot be null

I got these results on 5.1.11-beta/Win2K Server and 5.1.12-bk-20060817 on SuSE Linux 10.0.

How to repeat:
See description.

Suggested fix:
The implicit default should be used when not running the server in strict mode.
[18 Aug 2006 4:27] MySQL Verification Team
Thank you for the bug report. From the Manual:

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

"As of MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.

If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE statement includes no value for the column, MySQL handles the column according to the SQL mode in effect at the time:

#

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

hegel:/home/miguel/dbs/5.1 # bin/mysqladmin -uroot create qwe
hegel:/home/miguel/dbs/5.1 # bin/mysql -uroot qwe
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.12-beta-debug

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

mysql> SELECT @@GLOBAL.SQL_MODE;
+-------------------+
| @@GLOBAL.SQL_MODE |
+-------------------+
|                   |
+-------------------+
1 row in set (0.01 sec)

mysql> SELECT @@SESSION.SQL_MODE;
+--------------------+
| @@SESSION.SQL_MODE |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE a (b INT NOT NULL);
Query OK, 0 rows affected (0.01 sec)

That is your case column b not has explict default value. Then if you
insert not including a value:

mysql> INSERT INTO a values ();
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1364
Message: Field 'b' doesn't have a default value
1 row in set (0.00 sec)

mysql> SELECT * FROM a;
+---+
| b |
+---+
| 0 |
+---+
1 row in set (0.00 sec)

the server inserts the implicit 0 value, but your sample has a NULL
value:

mysql> INSERT INTO a VALUES (NULL);
ERROR 1048 (23000): Column 'b' cannot be null

that is correct can't take NULL values.

The multi-row behavior:

http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

STRICT_TRANS_TABLES

<cut>

"For non-transactional tables, the behavior is the same for either mode, if the bad value occurs in the first row to be inserted or updated. The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict option is enabled:"

Then is clear that the multi-row insert/update has special treatment by the
server, but I was unable to find something in the Manual regarding to the non-strict mode, multi-rows and not explicit default value for column. So I
think is something to be documented. Do you agree with the above?

Thanks in advance.
[18 Sep 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".