MySQL Bugs: #9728: decreased functionality in "on duplicate key update"

Bug #9728	decreased functionality in "on duplicate key update"
Submitted:	7 Apr 2005 16:52	Modified:	27 Jun 2005 9:54
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.11	OS:	Any (*)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
The query below worked fine in mysql 4.1.10a. It stopped working in 4.1.11.

I couldnt find any reference in the documentation towards if it should work or not.

mysql 4.1.10 returned:
select * from a1;
+---+
| a |
+---+
| 2 |
| 3 |
+---+

which is expected.

Mysql 4.1.11 does not execute the last insert, but returns:
ERROR 1109 (42S02): Unknown table 'b1' in field list

Which should apply only to the left hand side of any expression in the update.

How to repeat:
create table a1 (a int not null unique);
insert into a1 values (1),(2);
create table b1  (a int, b int);
insert into b1 values (1,3);

insert into a1 select b1.a  from b1  on duplicate key update a1.a=b1.b;

Suggested fix:
-

Just to say:

The error msg should also rather say:
Attempt to update read only value.

At all the table b1 is know to the query.

The change seem to have been caused by fix for BUG#8147

Fix for #8147 strips list of insert_table_list to only insert table, which results in reported error.
Changes removed, added flag to Item to resolve ambigous fields reported in bug #8147.

Fixed in 4.1.13, cset 1.2306.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fixed reported in 4.1.13 change history; bug closed.

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/26313

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/26364

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/26365

The bug fix was not completely accurate and is rewritten for 4.1.13