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

[21 Sep 2005 17:17] Dave Pullin
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);
[21 Sep 2005 17:21] Dave Pullin
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.
[21 Sep 2005 17:47] Jorge del Conde
I was unable to reproduce this bug in 4.1.15 and 5.0bk
[21 Sep 2005 17:47] Jorge del Conde
Tested under Linux & WinXP
[21 Sep 2005 17:55] MySQL Verification Team
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>
[20 Feb 2006 18:49] Jaco Kroon
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.