Bug #12631 INSERT..ON DUPLICATE KEY UPDATE gives errror 1110 (42000) although it shouldn't

• Submitted: 17 Aug 2005 21:20
• Modified: 18 Aug 2005 22:45
• Reporter: Roland Bouman
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.10
• OS: Windows (Win XP)
• CPU Architecture: Any

[17 Aug 2005 21:20] Roland Bouman

Description:
A statement like:

INSERT 
INTO t (
    ..columns..
)
SELECT..
ON DUPLICATE KEY 
UPDATE ..

gives:

ERROR 1110 (42000): Column '%c' specified twice

but only when there's a columnlist explicitly specified for the INSERT. 
When the columnlist is not specified, like this:

INSERT 
INTO t 
SELECT..
ON DUPLICATE KEY 
UPDATE ..

the statement executes successfully. 

There's no functional difference between these two statement variants. Therefore I expected them to behave identical.

How to repeat:
use test;

create table t(id int unsigned not null primary key);

Query OK, 0 rows affected (0.15 sec)

insert into t values (1);

Query OK, 1 row affected (0.05 sec)

insert
into t (id)
select id
from   t as t1
on duplicate key 
update id = t1.id + 100
;

ERROR 1110 (42000): Column 'id' specified twice

insert
into t
select id
from   t as t1
on duplicate key update
id = t1.id + 100;

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Suggested fix:
Ensure that these two statement variants behave similar. There's no functional difference, so they should behave similar.

[18 Aug 2005 22:45] MySQL Verification Team

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.12-beta-debug

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

mysql> create table t(id int unsigned not null primary key);
Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> insert into t values (1);
Query OK, 1 row affected (0.01 sec)

mysql> insert
    -> into t (id)
    -> select id
    -> from   t as t1
    -> on duplicate key 
    -> update id = t1.id + 100
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 1  Duplicates: 1  Warnings: 0

mysql> insert
    -> into t
    -> select id
    -> from   t as t1
    -> on duplicate key update
    -> id = t1.id + 100;
Query OK, 2 rows affected (0.00 sec)
Records: 1  Duplicates: 1  Warnings: 0

mysql>