MySQL Bugs: #29313: INSERT ... SELECT ... ON DUPLICATE KEY

Bug #29313	INSERT ... SELECT ... ON DUPLICATE KEY
Submitted:	23 Jun 2007 10:29	Modified:	26 Jun 2007 7:32
Reporter:	Quentin Raynaud	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.0.41-community	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	insert, ON DUPLICATE KEY, SELECT

Description:
I found a very funny bug that is returning an error message `ambigous entry for column nb` and I think it should not.

How to repeat:
Here is the table I am working on :
CREATE TABLE `cat_path` (
  `id` int(11) unsigned NOT NULL,
  `path` int(11) unsigned NOT NULL,
  `nb` int(11) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

AND here is how to duplicate what I think is a bug :
INSERT INTO `cat_path` SELECT 2 as id, path, nb WHERE id = 1 ON DUPLICATE KEY UPDATE nb = nb + VALUES(nb);
IDs 2 and 1 are there for the example, they are normally variables of a stored procedure but it is not the problem here.

Suggested fix:
I found that shitty workaround to get through for people that may have the same problem :
INSERT INTO `cat_path` (SELECT * FROM (SELECT 2 as id, path, nb AS nb2 WHERE id = 1) W) ON DUPLICATE KEY UPDATE nb = nb + VALUES(nb);

Thank you for a problem report. I was able to repeat the behaviour described on latest 5.0.46-BK, but I think it is intended and even documented to some extent. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/insert-select.html:

"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."

Note that "side effect". It explains why this:

mysql> INSERT INTO `cat_path` SELECT 2 as id, path, nb FROM `cat_path` WHERE id = 1 ON DUPLICATE KEY UPDATE nb = nb + VALUES(nb);
ERROR 1052 (23000): Column 'nb' in field list is ambiguous

does NOT work, whil the following:

mysql> INSERT INTO `cat_path` SELECT 2 as id, path, nb FROM `cat_path` c1 WHERE id = 1 ON DUPLICATE KEY UPDATE nb = `cat_path`.nb + VALUES(nb);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

works as expected. You need to specify what "table" are you getting nb from.