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: | |
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
[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".