MySQL Bugs: #16630: error, when INSERT t1 SELECT ... FROM t1 ON DUPLICATE

Bug #16630	error, when INSERT t1 SELECT ... FROM t1 ON DUPLICATE
Submitted:	19 Jan 2006 12:47	Modified:	6 Mar 2007 19:42
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	4.1	OS:
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
MySQL will update the wrong row under the following conditions:
1. INSERT t1 SELECT ... FROM t1 source ON DUPLICATE col = source.col
    Attention: The table t1 is target of the INSERT and source
                    in the inner SELECT
2. A DUPLICATE KEY occurs

Please have a look into the attached test with several variations
of the test conditions and many comments.

It looks like the server executes the SELECT embedded within the 
insert first. After that he starts to process the records of the result
set line by line.
Whenever a DUPLICATE KEY occurs he executes the
ON DUPLICATE clause, but he takes the input data for the Update
from 
- MySQL 4.1: the first (not matching) record which physical follows
  the last record of the result set.
  If there is no such following record the last record the of the 
  result set will be taken
- MySQL 5.0, 5.1: the last record of the source table.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
         4.1 last ChangeSet@1.2479, 2006-01-18
         5.0 last ChangeSet@1.2025.6.1, 2006-01-17
         5.1 last ChangeSet@1.2072, 2006-01-18

How to repeat:
Please use my attached testscript ml004.test
  copy it to mysql-test/t
  echo "Dummy" > r/ml004.result   # Produce a dummy file with 
                                                   # expected results
  ./mysql-test-run ml004

  The comments printed into the protocol are adjusted to
  the behaviour of MySQL 4.1

testscript

Attachment: ml004.test (application/test, text), 5.00 KiB.

reject file got on MySQL 4.1

Attachment: ml004.reject (application/octet-stream, text), 5.03 KiB.

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/commits/19643

ChangeSet@1.2409, 2007-02-10 00:19:48+03:00, evgen@moonbone.local +6 -0
  Bug#16630: The update fields of the INSERT .. SELECT .. ON DUPLICATE KEY
  UPDATE contains wrong data if the SELECT employs a temporary table.
  
  If the UPDATE values of the INSERT .. SELECT .. ON DUPLICATE KEY UPDATE
  statement contains fields from the SELECT part and the select employs a
  temporary table then those fields will contain wrong values because they
  aren't corrected to get data from the temporary table.
  
  The solution is to add these fields to the selects all_fields list,
  to store pointers to those fields in the selects ref_pointer_array and
  access them via Item_ref objects.
  
  The substitution for Item_ref objects is done in the new function called 
  Item_field::update_value_transformer(). It is called through 
  item->transform() mechanism at the end of the select_insert::prepare()
  function.

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/commits/20181

ChangeSet@1.2409, 2007-02-16 19:39:28+03:00, evgen@moonbone.local +6 -0
  Bug#16630: The update fields of the INSERT .. SELECT .. ON DUPLICATE KEY
  UPDATE contains wrong data if the SELECT employs a temporary table.
  
  If the UPDATE values of the INSERT .. SELECT .. ON DUPLICATE KEY UPDATE
  statement contains fields from the SELECT part and the select employs a
  temporary table then those fields will contain wrong values because they
  aren't corrected to get data from the temporary table.
  
  The solution is to add these fields to the selects all_fields list,
  to store pointers to those fields in the selects ref_pointer_array and
  to access them via Item_ref objects.
  
  The substitution for Item_ref objects is done in the new function called 
  Item_field::update_value_transformer(). It is called through the
  item->transform() mechanism at the end of the select_insert::prepare()
  function.

Fixed in 5.0.38, 5.1.17-beta

Noted in 5.0.38, 5.1.17 changelogs.