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 15:13	Modified:	1 Jan 2011 17:37
Reporter:	Dave Pullin (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.24a-community-nt-log/4.1BK/5.0BK/5.1BK	OS:	Windows (Windows 2000 and Linux)
Assigned to:		CPU Architecture:	Any
Tags:	bfsm_2007_03_01, qc

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.

See this, this can't be disambiguated, coz mysql does not alow aliases in insert part. I putted "/\" into sql to point out problematic part which causes 'column ambiguous' error. taken that out it works

insert into stat_session_robots  (jsp,visit_count,host,last_visit,robot)
select t.jsp ,t.visit_count,t.host,t.last_visit,t.robot from temp_table as t
ON DUPLICATE KEY UPDATE
visit_count =  "/\t.visit_count +"/\" visit_count,
last_visit = t.last_visit;

note on previous comment: 5.1.31-community-log

using actual table name like:

visit_count =  (stat_session_robots.visit_count + t.visit_count)

solves the problem.