MySQL Bugs: #26261: Missing default value isn't noticed in insert ... on duplicate key update

Bug #26261	Missing default value isn't noticed in insert ... on duplicate key update
Submitted:	10 Feb 2007 20:17	Modified:	19 Mar 2007 17:41
Reporter:	Olaf van der Spek (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0bk, 5.1.15	OS:	Windows (Windows XP)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	qc

Description:
A missing default value isn't noticed in insert ... on duplicate key update queries.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.1.15-beta-community-nt MySQL Community Server (GPL)

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

mysql> use xbt;
Database changed
mysql> drop table if exists a;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> create table a
    -> (
    ->     b int,
    ->     c int not null
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> insert into a (b) values (1);
ERROR 1364 (HY000): Field 'c' doesn't have a default value
mysql>
mysql> insert into a (b) values (1) on duplicate key update c = c;
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> select * from a;
+------+---+
| b    | c |
+------+---+
|    1 | 0 |
+------+---+
1 row in set (0.00 sec)

mysql>

verified, see attached test case

mysqltest test case

Attachment: bug26291.tgz (application/x-gtar, text), 865 bytes.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/20795

ChangeSet@1.2419, 2007-02-28 18:45:32+02:00, gkodinov@macbook.gmz +5 -0
  Bug #26261: 
   INSERT uses query_id to verify what fields are
   mentioned in the fields list of the INSERT command.
   However the check for that is made after the 
   ON DUPLICATE KEY is processed. This causes all
   the fields mentioned in ON DUPLICATE KEY to be 
   considered as mentioned in the fields list of 
   INSERT.
   Moved the check up, right after processing the
   fields list.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21783

ChangeSet@1.2419, 2007-03-13 11:28:38+02:00, gkodinov@macbook.gmz +5 -0
  Bug #26261: 
   INSERT uses query_id to verify what fields are
   mentioned in the fields list of the INSERT command.
   However the check for that is made after the 
   ON DUPLICATE KEY is processed. This causes all
   the fields mentioned in ON DUPLICATE KEY to be 
   considered as mentioned in the fields list of 
   INSERT.
   Moved the check up, right after processing the
   fields list.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22098

ChangeSet@1.2419, 2007-03-16 10:35:39+02:00, gkodinov@macbook.gmz +5 -0
  Bug #26261: 
   INSERT uses query_id to verify what fields are
   mentioned in the fields list of the INSERT command.
   However the check for that is made after the 
   ON DUPLICATE KEY is processed. This causes all
   the fields mentioned in ON DUPLICATE KEY to be 
   considered as mentioned in the fields list of 
   INSERT.
   Moved the check up, right after processing the
   fields list.

Pushed in 5.0.40, 5.1.17

Noted in 5.0.40, 5.1.17 changelogs.

For an INSERT statement that should fail due to a column with no
default value not being assigned a value, the statement succeeded
with no error if the column was assigned a value in an ON DUPLICATE
KEY UPDATE clause, even if that clause was not used.