MySQL Bugs: #8732: INSERT ... SELECT ...ON DUPLICATE KEY UPDATE problem

Bug #8732	INSERT ... SELECT ...ON DUPLICATE KEY UPDATE problem
Submitted:	23 Feb 2005 14:49	Modified:	5 Aug 2005 16:02
Reporter:	Tobias Asplund	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	Any (*)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
if you do a INSERT INTO table (col_list) SELECT ... ON DUPLICATE KEY UPDATE and refer to the same column in the col_list and in the UPDATE clause, it will bail out, doesn't seem like it should, since not listing the column in the INSERT list will make it work, see below:

How to repeat:
CREATE TABLE t105 ( a CHAR(1) );
-- Query OK, 0 rows affected (0.00 sec)

  CREATE TABLE t106 ( a CHAR(1) );
-- Query OK, 0 rows affected (0.00 sec)

  INSERT INTO t105 VALUES ('a');
-- Query OK, 1 row affected (0.00 sec)

  INSERT INTO t106 (a) SELECT a FROM t105;  --  This works fine
-- Query OK, 1 row affected (0.00 sec)
-- Records: 1  Duplicates: 0  Warnings: 0

  INSERT INTO t106 (a) SELECT a FROM t105 ON DUPLICATE KEY UPDATE t106.a = 1;
-- ERROR 1110 (42000): Column 'a' specified twice

  INSERT INTO t106 SELECT a FROM t105 ON DUPLICATE KEY UPDATE t106.a = 1;
-- Query OK, 1 row affected (0.00 sec)
-- Records: 1  Duplicates: 0  Warnings: 0

-- The only difference is that the first case lists the column in the table.

I concur this happening on 4.1.10

I have also added a comment to the doc page with regard to this as it's just taken me 2 hours to figure this out!  :)

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

Duplicate of http://bugs.mysql.com/8147

verified for 4.1.11

Related to this bug, but a bit different (using 4.1.11):

create table t1 (i int not null primary key, p int);
create table t2 (i int not null primary key, p int);

insert into t2 values(1,1);
insert into t1 select i,p from t2;

insert into t1(i,p) select i,p from t2 on duplicate key update t1.p=t1.p+values(p);
ERROR 1110 (42000): Column 'p' specified twice

I don't like the values() syntax. It is not clear from which table (t1,t2?) the value is taken. I would prefer a syntax like:

insert into t1 (i,p) select i,p from t2 on duplicate key update t1.p=t1.p+t2.p;

but this gives:
ERROR 1109 (42S02): Unknown table 't2' in field list

I should have added: The following syntax should work, but does not:
 
insert into t1(i,p) select t2.i, t2.p from t2 
on duplicate key update t1.p=t1.p+values(t2.p);

 ERROR 1109 (42S02): Unknown table 't2' in field list

INSERT INTO traffic_account (kunde,datum,stunde,bytes)
        select kunde,DATE(zeit),HOUR(zeit),traffic from traffic_accounting
        ON DUPLICATE KEY UPDATE bytes=bytes+VALUES(traffic);
Does not work, this is already explained in this topic.
    
INSERT INTO traffic_account select DATE(zeit),HOUR(zeit),kunde,traffic 
        from traffic_accounting 
        ON DUPLICATE KEY UPDATE bytes=bytes+VALUES(traffic);
This run as explained in this topic, and should do the same thing !!!

But if i check the result from
select DATE(zeit),HOUR(zeit),kunde,sum(traffic) from traffic_accounting group by DATE(zeit),HOUR(zeit),kunde;
wich should give the same values than the insert on an empty table the result is complete different !!!

The Insert * on duplicate statement produce many (1415) Rows where bytes=0;
But in traffic_accounting traffic is always > 0 and never sum(traffic) result in  an overflow.

Since this bug result in defekt data. I think it the Severity should be increased to Ciritcal !!!

Dup of Bug#10109 (currently in patch pending state)