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:Microsoft Windows (Windows XP)
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: qc

[10 Feb 2007 20:17] Olaf van der Spek
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>
[11 Feb 2007 9:47] Hartmut Holzgraefe
verified, see attached test case
[11 Feb 2007 9:47] Hartmut Holzgraefe
mysqltest test case

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

[28 Feb 2007 16:45] Bugs System
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.
[13 Mar 2007 9:29] Bugs System
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.
[16 Mar 2007 8:36] Bugs System
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.
[17 Mar 2007 17:55] Alexey Botchkov
Pushed in 5.0.40, 5.1.17
[19 Mar 2007 17:41] Paul Dubois
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.