MySQL Bugs: #43454: GROUP BY makes columns unrecognized when doing INSERT SELECT ... ON DUPLICATE K

Bug #43454	GROUP BY makes columns unrecognized when doing INSERT SELECT ... ON DUPLICATE K
Submitted:	6 Mar 2009 13:38	Modified:	6 Mar 2009 15:09
Reporter:	bbkr	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.1.30	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
I'm using "INSERT SELECT (...) ON DUPLICATE KEY UPDATE" statement.

When SELECT part has GROUP BY in it then it makes columns not visible for ON DUPLICATE KEY UPDATE part.

Check simple example attached.

How to repeat:
DROP TABLE IF EXISTS `foo`;
DROP TABLE IF EXISTS `bar`;

CREATE TABLE `foo` (
    `x` int,
    `y` int
) engine=InnoDB;

CREATE TABLE `bar` (
    `a` int,
    `b` int
) engine=InnoDB;

INSERT INTO `foo` (`x`, `y`)
VALUES (1, 1);

INSERT INTO `bar` (`a`, `b`)
SELECT `x`, `y`
FROM `foo`
GROUP BY `x`, `y`
ON DUPLICATE KEY UPDATE
    `b` = IFNULL(`y`, `b`);

ERROR 1054 (42S22): Unknown column 'y' in 'field list'

Suggested fix:
I found that

INSERT INTO `bar` (`a`, `b`)
SELECT DISTINCT `x`, `y`
FROM `foo`
ON DUPLICATE KEY UPDATE
    `b` = IFNULL(`y`, `b`);

works fine.

While this is easily repeatable and look unexpected, it is clearly documented in the manual, http://dev.mysql.com/doc/refman/5.1/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."

So, formally this is not a bug. You had also found a nice workaround.