Bug #615 Multiple inserting NULL into a column that has been declared NOT NULL
Submitted: 6 Jun 2003 13:51 Modified: 1 Jul 2003 3:18
Reporter: Indrek Siitan Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.13, 4.1.0 OS:Any (Any)
Assigned to: Alexey Botchkov CPU Architecture:Any

[6 Jun 2003 13:51] Indrek Siitan
The manual states at 6.4.3, on INSERTs - more specific in the paragraphs
about INSERT ... SELECT and INSERT ... VALUES statement with multiple value

    * Inserting NULL into a column that has been declared NOT NULL. The
column is set to its default value.

This doesn't seem to be the case for mysqld 4.0.13 (linux) or
4.1.0-alpha-max-nt (WinXP). Om both servers I end up with the following
result, which could easily be replicated:

Please notice that the second INSERT did result in 0, instead of the
default value 8.

How to repeat:
mysql> CREATE TABLE foo (id int NOT NULL default 8);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO foo (id) VALUES (NULL);
ERROR 1048: Column 'id' cannot be null

mysql> INSERT INTO foo (id) VALUES (3),(NULL),(5);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM foo;
| id |
|  3 |
|  0 |
|  5 |
3 rows in set (0.01 sec)
[25 Jun 2003 21:24] Alexey Botchkov
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
[1 Jul 2003 3:18] Sergei Golubchik
after further discussions it happens out that it was the bug in the manual.
It should be read as "The column is set to default value for it's type" - which is 0 for INT.

Manual will be fixed.