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

[19 Jan 2006 12:47] Matthias Leich
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
[19 Jan 2006 12:49] Matthias Leich
testscript

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

[19 Jan 2006 12:49] Matthias Leich
reject file got on MySQL 4.1

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

[9 Feb 2007 21:20] Bugs System
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.
[20 Feb 2007 14:21] Bugs System
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.
[28 Feb 2007 17:39] Sergey Glukhov
Fixed in 5.0.38, 5.1.17-beta
[6 Mar 2007 19:42] Paul Dubois
Noted in 5.0.38, 5.1.17 changelogs.