| Bug #10522 | INSERTing NULL into non-NULL column behavior is inconsistent | ||
|---|---|---|---|
| Submitted: | 10 May 2005 18:28 | Modified: | 16 May 2005 16:20 |
| Reporter: | Rob Blick | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 4.1.11 | OS: | Linux (Linux) |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
[13 May 2005 14:03]
Rob Blick
Since this is getting assigned to Documentation, a third query is worth mentioning: mysql> describe tableB; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | b | int(11) | YES | | NULL | | | c | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from tableB; +------+------+ | b | c | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) mysql> insert into tableA (a) SELECT B.b FROM tableB B where B.c = 2; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 Note how this query doesn't insert anything and it doesn't give a warning. It's pretty much as expected, since the inner SELECT returns an empty set (i.e., INSERT INTO tableA (a) <empty set>), which is different from NULL, but I think worth mentioning in the documentation, given the goofy results of the other two queries.
[16 May 2005 16:20]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant product(s). Additional info: I've updated http://dev.mysql.com/doc/mysql/en/insert.html to indicate that INSERT INTO ... SELECT is treated like a multiple-row INSERT because the server does not examine the result set to see whether it contains a single row, and to indicate that an error occurs for single-row INSERT statements. No comment necessary for the INSERT INTO ... SELECT where the SELECT returns an empty result. It is unremarkable that this generates no warnings, because an empty result is perfectly legal.
Description: When inserting NULL into a column that cannot be NULL, different behaviors result, depending on the way the query is specified. Assuming the following: mysql> describe tableA; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | a | int(11) | | | 0 | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> select * from tableA; Empty set (0.00 sec) Given the above, the following two queries return different results: mysql> insert into tableA (a) values (null); ERROR 1048 (23000): Column 'a' cannot be null ---This is as expected--- mysql> insert into tableA (a) SELECT null; Query OK, 1 row affected, 1 warning (0.01 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1263 Message: Data truncated; NULL supplied to NOT NULL column 'a' at row 1 1 row in set (0.00 sec) mysql> select * from tableA; +---+ | a | +---+ | 0 | +---+ 1 row in set (0.00 sec) ---This is goofy (why is MySQL changing the data?), but at least it notifies w/ a warning--- These two queries, though logically similar, are behaving differently. How to repeat: create table tableA (a int not null); then execute these two queries: insert into tableA (a) values (null); insert into tableA (a) SELECT null; and observe the inconsistent results. Suggested fix: Make both queries evaluate consistently. In my opinion, if IGNORE is not specified in the INSERT statement, the first result should occur ("ERROR 1048 (23000): Column 'a' cannot be null"). If IGNORE is specified, the second should occur (i.e., the NULL is converted to the column's DEFAULT). In any event, they should both evaluate identically.