MySQL Bugs: #24697: insert .. select ....ON DUPLICATE KEY UPDATE fails with ambiguous column name

Bug #24697	insert .. select ....ON DUPLICATE KEY UPDATE fails with ambiguous column name
Submitted:	29 Nov 2006 16:13	Modified:	15 Sep 2007 2:52
Reporter:	Dave Pullin (Basic Quality Contributor)
Status:	Analyzing
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.24a-community-nt-log/4.1BK/5.0BK/5.1BK	OS:	Microsoft Windows (Windows 2000 and Linux)
Assigned to:		Target Version:
Tags:	qc, bfsm_2007_03_01

Description:
This is similar to Bug http://bugs.mysql.com/bug.php?id=13392 (which is fixed) for a
slightly different case.

ON DUPLICATE KEY UPDATE z=y;
yields: ERROR 1052 (23000): Column 'y' in field list is ambiguous

whereas the original bug had
ON DUPLICATE KEY UPDATE z= if(@values(y);
which now works.

CONSOLE LOG
mysql> drop table if exists temp,tempUpdates;
Query OK, 0 rows affected (0.00 sec)

mysql> create table temp ( x int primary key , y int, z int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table tempUpdates like temp;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into temp (x,y,z) select x,y,z from tempupdates as up
    -> ON DUPLICATE KEY UPDATE z= y  /*  values(y) */ ;
ERROR 1052 (23000): Column 'y' in field list is ambiguous

How to repeat:
drop table if exists temp,tempUpdates;
create table temp ( x int primary key , y int, z int);
create table tempUpdates like temp;
insert into temp (x,y,z) select x,y,z from tempupdates as up 
ON DUPLICATE KEY UPDATE z= y  /*  values(y) */ ;

Thank you for the bug report.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.32-debug Source distribution

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

mysql> drop table if exists temp,tempupdates;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> create table temp ( x int primary key , y int, z int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table tempupdates like temp;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into temp (x,y,z) select x,y,z from tempupdates as up 
    -> ON DUPLICATE KEY UPDATE z= y  /*  values(y) */ ;
ERROR 1052 (23000): Column 'y' in field list is ambiguous
mysql>

regarding info from
http://dev.mysql.com/doc/refman/5.1/en/insert-select.html
--------------
      To avoid ambigious column reference problems when the SELECT and the INSERT refer
to the same table, provide a unique alias for each table used in the SELECT part, and
qualify column names in that part with the appropriate alias.

In the values part of ON DUPLICATE KEY UPDATE, you can refer to columns in other tables,
as long as you do not use GROUP BY in the SELECT part. One side effect is that you must
qualify non-unique column names in the values part.
--------------

it's not a bug anymore (?)

"it's not a bug anymore (?)"
I agree. Disambiguating the column name with a table alias works now.