MySQL Bugs: #13392: values() fails with 'ambiguous' or returns NULL with ON DUPLICATE and SELECT

Bug #13392	values() fails with 'ambiguous' or returns NULL with ON DUPLICATE and SELECT
Submitted:	21 Sep 2005 22:47	Modified:	31 Oct 2005 20:13
Reporter:	Dave Pullin (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.12-beta-nt-log	OS:	Any (All)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
(Thank you for responding so quickly to my bug report http://bugs.mysql.com/?id=13385 -- this has enabled me to get to the real problem!)

Using INSERT .... SELECT ... ON DUPLICATE KEY UPDATE y=values(y)

fails with 
ERROR 1052 (23000): Column 'y' in field list is ambiguous

All the ways that I have attempted to disambiguate 'y' that do not give this error or give 'unknown column', return NULL for the value of the values() function.

CONSOLE LOG:
mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.0.12-beta-nt-log |
+--------------------+
1 row in set (0.00 sec)

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.02 sec)

mysql> insert into temp (x,y,z) values (1,1,1);
Query OK, 1 row affected (0.00 sec)

mysql> create table tempUpdates like temp;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tempUpdates (x,y,z) values (1,2,2),(100,100,100) ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from temp;
+---+------+------+
| x | y    | z    |
+---+------+------+
| 1 |    1 |    1 |
+---+------+------+
1 row in set (0.00 sec)

mysql> insert into temp (x,y,z) select x,y,z from tempupdates as up ON DUPLICATE
 KEY UPDATE y=values(y);
ERROR 1052 (23000): Column 'y' in field list is ambiguous
mysql> insert into temp (x,y,z) select x,y as yu,z from tempupdates as up ON DUP
LICATE KEY UPDATE y=values(yu);
ERROR 1054 (42S22): Unknown column 'yu' in 'field list'
mysql> insert into temp (x,y,z) select x,y as yu,z from tempupdates as up ON DUP
LICATE KEY UPDATE y=values(up.yu);
ERROR 1054 (42S22): Unknown column 'up.yu' in 'field list'
mysql> select * from temp;
+---+------+------+
| x | y    | z    |
+---+------+------+
| 1 |    1 |    1 |
+---+------+------+
1 row in set (0.00 sec)

mysql> insert into temp (x,y,z) select x,y as yu,z from tempupdates as up ON DUP
LICATE KEY UPDATE y=values(up.y);
Query OK, 3 rows affected (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 0

mysql> select * from temp;
+-----+------+------+
| x   | y    | z    |
+-----+------+------+
|   1 | NULL |    1 |
| 100 |  100 |  100 |
+-----+------+------+
2 rows in set (0.00 sec)

mysql> insert into temp (x,y,z) select x,y,z from tempupdates as up ON DUPLICATE
 KEY UPDATE y=values(up.y);
Query OK, 4 rows affected (0.00 sec)
Records: 2  Duplicates: 2  Warnings: 0

mysql> select * from temp;
+-----+------+------+
| x   | y    | z    |
+-----+------+------+
|   1 | NULL |    1 |
| 100 | NULL |  100 |
+-----+------+------+
2 rows in set (0.00 sec)

mysql>

How to repeat:
select version();
drop table if exists temp,tempUpdates;
create table temp ( x int primary key , y int, z int);
insert into temp (x,y,z) values (1,1,1);
create table tempUpdates like temp;
insert into tempUpdates (x,y,z) values (1,2,2),(100,100,100) ;
select * from temp;
insert into temp (x,y,z) select x,y,z from tempupdates as up ON DUPLICATE KEY UPDATE y=values(y);
insert into temp (x,y,z) select x,y as yu,z from tempupdates as up ON DUPLICATE KEY UPDATE y=values(yu);
insert into temp (x,y,z) select x,y as yu,z from tempupdates as up ON DUPLICATE KEY UPDATE y=values(up.yu);
select * from temp;
insert into temp (x,y,z) select x,y as yu,z from tempupdates as up ON DUPLICATE KEY UPDATE y=values(up.y);
select * from temp;
insert into temp (x,y,z) select x,y,z from tempupdates as up ON DUPLICATE KEY UPDATE y=values(up.y);
select * from temp;

Suggested fix:

y=values(y)
   should NOT be ambiguous, since both Y's should be from the set of names of columns in the target table.  It looks as though the parser might be looking at the column names in the source SELECT. I thouht the point of values(Y) vs Y was to distinguish between the 'new' value and the 'current' value, as in 
 y=y+values(y)  to increment Y with the result of SELECT Y.

 If you deem that it is Ambiguous, please document how it should be disambiguated.

Thanks for your bug report.  I was able to reproduce this bug using 5.0.12 from bk under FC4 (Linux)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31125

According to documentation "VALUES(col_name)  in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred."
http://dev.mysql.com/doc/refman/5.0/en/insert.html

So:
insert into temp (x,y,z) select x,y,z from tempupdates as up ON DUPLICATE KEY
UPDATE y=values(y);
Returns error - bug, should be fixed.

insert into temp (x,y,z) select x,y as yu,z from tempupdates as up ON DUPLICATE
KEY UPDATE y=values(yu);
This is ok, because there is no 'yu' column in 'temp' table.

insert into temp (x,y,z) select x,y as yu,z from tempupdates as up ON DUPLICATE
KEY UPDATE y=values(up.yu);
insert into temp (x,y,z) select x,y as yu,z from tempupdates as up ON DUPLICATE
KEY UPDATE y=values(up.y);
Allows 'up' table in values() - bug, should be fixed.

Item_insert_value::fix_fields() passed to it's arg full table list,
This results in finding second column which shouldn't be found, and
failing with error about ambiguous field.

Fixed in 4.1.16, cset  1.2462.1.1

Fixed in 5.0.16

Noted in 4.1.16, 5.0.16 changelogs.