MySQL Bugs: #13385: values() fails with 'Column specified twice' when ON DUPLICATE KEY UPDATE used

Bug #13385	values() fails with 'Column specified twice' when ON DUPLICATE KEY UPDATE used
Submitted:	21 Sep 2005 17:17	Modified:	21 Sep 2005 17:55
Reporter:	Dave Pullin (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.13	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
INSERT INTO temp (x,y,z) select 1,2,3
    ON DUPLICATE KEY UPDATE y=VALUES(z);
yields
  Column 'z' specified twice

where as
INSERT INTO temp (x,y,z) values(1,2,3)
    ON DUPLICATE KEY UPDATE y=VALUES(z);
works correctly.

This is the simplest version of a real life problem in which the SELECT is a non-trivial query on a big table, which is why the bug is Serious.

Console Log:
mysql> select version();
+---------------+
| version()     |
+---------------+
| 4.1.13-nt-log |
+---------------+
1 row in set (0.00 sec)

mysql> drop table if exists temp;
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> INSERT INTO temp (x,y,z) select 1,2,3
    ->     ON DUPLICATE KEY UPDATE y=VALUES(z);
ERROR 1110 (42000): Column 'z' specified twice
mysql> INSERT INTO temp (x,y,z) values(1,2,3)
    ->     ON DUPLICATE KEY UPDATE y=values(z);
Query OK, 1 row affected (0.00 sec)

How to repeat:
select version();
drop table if exists temp;
create table temp ( x int primary key , y int, z int);
INSERT INTO temp (x,y,z) select 1,2,3
    ON DUPLICATE KEY UPDATE y=VALUES(z);
INSERT INTO temp (x,y,z) values(1,2,3)
    ON DUPLICATE KEY UPDATE y=values(z);

The synopsis got truncated: It should have said:

values() fails with 'Column specified twice' when ON DUPLICATE KEY UPDATE used with SELECT

The problem is specifically when VALUES() is used with SELECT.

I was unable to reproduce this bug in 4.1.15 and 5.0bk

Tested under Linux & WinXP

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.14-nt

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

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.00 sec)

mysql> drop table if exists temp;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> INSERT INTO temp (x,y,z) select 1,2,3
    ->     ON DUPLICATE KEY UPDATE y=VALUES(z);
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO temp (x,y,z) values(1,2,3)
    ->     ON DUPLICATE KEY UPDATE y=values(z);
Query OK, 2 rows affected (0.05 sec)

mysql>

An alternative way to have worked around the bug was simply to not specify the column names in the INSERT table, so if we assume that the above example table only had the three columns x, y and z one could simply have done:

INSERT INTO temp SELECT ... ON DUPLICATE KEY ...;

Anyhow - since it's fixed now.  But some of us have to work with ISPs that doesn't want to upgrade because "it might affect their stability".  Thanks for an awesome product guys.