Bug #43580 Issue with Innodb on multi-table update
Submitted: 12 Mar 2009 10:54 Modified: 25 Jun 2009 22:39
Reporter: Nidhi Shrotriya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1-bzr, 6.0.11 OS:Any
Assigned to: Martin Hansson CPU Architecture:Any

[12 Mar 2009 10:54] Nidhi Shrotriya
Description:
Please find attached the test case file. 

Difference with other(MyISAM,Falcon,Maria,PBXT) engines as below,

With other engines:
-----------------------
update mt1,mt2 set mt1.b=mt1.b+2,mt2.b=mt1.b+10 where mt1.b between 3 and 5 and mt2.a=mt1.a-100;
select * from mt1;
a       b
102     12
103     5
104     6
105     7
106     6
107     7
108     8
109     9
201     1
select * from mt2;
a       b
1       1
2       2
3       13
4       14
5       15
6       6
7       7
8       8
9       9

With Innodb:
----------------
update mt1,mt2 set mt1.b=mt1.b+2,mt2.b=mt1.b+10 where mt1.b between 3 and 5 and mt2.a=mt1.a-100;
select * from mt1;
a       b
102     12
103     5
104     6
105     7
106     6
107     7
108     8
109     9
201     1
select * from mt2;
a       b
1       1
2       2
3       15 --
4       16 --
5       17 --
6       6
7       7
8       8
9       9

After update mt2.b returns 15,16,17 instead of 13,14,15.

How to repeat:
Please find attached the test case file.
[12 Mar 2009 11:00] Valeriy Kravchuk
I do not see any files attached yet.
[12 Mar 2009 11:09] Nidhi Shrotriya
Test Case

Attachment: update_number_bug_innodb.test (application/octet-stream, text), 845 bytes.

[12 Mar 2009 11:10] Nidhi Shrotriya
Please find the file attached.
[12 Mar 2009 11:33] Valeriy Kravchuk
Verified just as described with your test case and recent 6.0.11 from bzr on Linux.
[12 Mar 2009 11:35] Valeriy Kravchuk
5.1 from brz is also affected.
[17 Mar 2009 11:42] Marko Mäkelä
I can reproduce this in our private InnoDB 5.1 source tree (r4400). The cause seems to be that row_search_for_mysql() on table mt1 is being invoked two times: first, for the update of mt1, and then, for the update of mt2. Because InnoDB transactions will see changes that they made themselves, mt2 will be updated with the updated data of mt1.
[25 Mar 2009 13:13] Marko Mäkelä
While this bug manifests itself with InnoDB tables, it looks like it is in the query optimizer or execution. Even changing the order of the UPDATE...SET expression (update mt1,mt2 set mt2.b=mt1.b+10,mt1.b=mt1.b+2) does not affect the result.

My guess is that the evaluation order of the join is based on optimizer statistics, which may differ between engines. The update is executed at the lowest level of the join:

#1  0x0850f9b5 in ha_innobase::update_row (this=0x9ee7d18, old_row=0x9ee7e78 "\377g", new_row=0x9ee7e68 "\377g") at handler/ha_innodb.cc:4021
#2  0x083e79c6 in handler::ha_update_row (this=0x9ee7d18, old_data=0x9ee7e78 "\377g", new_data=0x9ee7e68 "\377g") at handler.cc:4602
#3  0x08372044 in multi_update::send_data (this=0x9ef5d78, not_used_values=@0xb36662e4) at sql_update.cc:1675
#4  0x0832d562 in end_send (join=0x9ef0d30, join_tab=0x9ef2220, end_of_records=false) at sql_select.cc:11954
#5  0x08334026 in evaluate_join_record (join=0x9ef0d30, join_tab=0x9ef2098, error=0) at sql_select.cc:11214
#6  0x0833421a in sub_select (join=0x9ef0d30, join_tab=0x9ef2098, end_of_records=false) at sql_select.cc:11099
#7  0x08334026 in evaluate_join_record (join=0x9ef0d30, join_tab=0x9ef1f10, error=0) at sql_select.cc:11214
#8  0x08334249 in sub_select (join=0x9ef0d30, join_tab=0x9ef1f10, end_of_records=false) at sql_select.cc:11105
#9  0x08344823 in do_select (join=0x9ef0d30, fields=0xb36662e4, table=0x0, procedure=0x0) at sql_select.cc:10855
#10 0x08355013 in JOIN::exec (this=0x9ef0d30) at sql_select.cc:2199
#11 0x0834fef1 in mysql_select (thd=0x9ea8108, rref_pointer_array=0x9ea959c, tables=0x9ef4e88, wild_num=0, fields=@0xb36662e4, conds=0x9ef5cb8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=1342177408, result=0x9ef5d78, unit=0x9ea9234, select_lex=0x9ea94a4) at sql_select.cc:2378
#12 0x0836fbca in mysql_multi_update (thd=0x9ea8108, table_list=0x9ef4e88, fields=0x9ea9538, values=0x9ea9750, conds=0x9ef5cb8, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x9ea9234, select_lex=0x9ea94a4) at sql_update.cc:1219
#13 0x082cd4ea in mysql_execute_command (thd=0x9ea8108) at sql_parse.cc:3039
#14 0x082d3a81 in mysql_parse (thd=0x9ea8108, inBuf=0x9ef4d58 "update mt1,mt2 set mt2.b=mt1.b+10,mt1.b=mt1.b+2 where mt1.b between 3 and 5 and mt2.a=mt1.a-100", length=95, found_semicolon=0xb366726c) at sql_parse.cc:5831
[2 Apr 2009 14:37] Martin Hansson
The bug appears when range scan is used to access t1. If the contents of the tables is tweaked so that index scan (of index mt1.b) is chosen instead, the result is correct.
[7 Apr 2009 9:10] 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/71504

2846 Martin Hansson	2009-04-07
      Bug#43580: Issue with Innodb on multi-table update
      
      Multi-updates on-the-fly (updating the first table while updating
      other tables) were considered safe when an update to another
      table depended on the value written to the first.
      Fixed by not disallowing on-the-fly updates when a written field
      is read in the same statement.
     @ mysql-test/r/innodb_mysql.result
        Bug#43580: Expected result.
     @ mysql-test/t/innodb_mysql.test
        Bug#43580: Test case.
     @ sql/sql_update.cc
        Bug#43580: The fix and updated comment.
[7 Apr 2009 9:13] 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/71505

2846 Martin Hansson	2009-04-07
      Bug#43580: Issue with Innodb on multi-table update
            
      Multi-updates on-the-fly (updating the first table while updating
      other tables) were considered safe even when an update to another
      table depended on the value written to the first.
      Fixed by disallowing on-the-fly updates when a written field
      is read in the same statement.
     @ mysql-test/r/innodb_mysql.result
        Bug#43580: Expected result.
     @ mysql-test/t/innodb_mysql.test
        Bug#43580: Test case.
     @ sql/sql_update.cc
        Bug#43580: The fix and updated comment.
[7 Apr 2009 10:41] Sergei Golubchik
> If the contents of the tables is tweaked so that index scan
> (of index mt1.b) is chosen instead, the result is correct.

Fine, but *what* result is correct ?
What should be the expected correct result, and why ?
[8 Apr 2009 10:13] 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/71625

2846 Martin Hansson	2009-04-08
      Bug#43580: Issue with Innodb on multi-table update
      
      This is an incremental patch, to be used for discussion.
      
      Certain multi-updates gave different results on InnoDB from
      MyISAM due to on-the-fly updates being used on the latter and
      there are dependencies between the updates.
      Fixed by turning off on-the-fly updates when dependencies are
      present.
     @ mysql-test/r/innodb_mysql.result
        Bug#43580: Test result.
     @ mysql-test/t/innodb_mysql.test
        Bug#43580: Test case.
     @ sql/sql_update.cc
        Bug#43580: The fix
[15 Apr 2009 9:32] 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/72115

2846 Martin Hansson	2009-04-15
      Bug#43580: Issue with Innodb on multi-table update
      
      Certain multi-updates gave different results on InnoDB as opposed 
      to MyISAM, due to on-the-fly updates being used on the former and
      the update order matters.
      Fixed by turning off on-the-fly updates when update order 
      dependencies are present.
     @ mysql-test/r/innodb_mysql.result
        Bug#43580: Test result.
     @ mysql-test/t/innodb_mysql.test
        Bug#43580: Test case.
     @ sql/sql_base.cc
        Bug#43580: Added a word of caution about using tmp_set here.
     @ sql/sql_update.cc
        Bug#43580: The fix
[22 Apr 2009 9:21] Martin Hansson
Pushing this patch to 5.1 and 6.0 only. Because of code difference a totally different fix - and code review - would be needed for 5.0. I suggest opening a new bug report for 5.0 if this is needed.
[22 Apr 2009 9:29] 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/72647

2864 Martin Hansson	2009-04-22
      Bug#43580: Issue with Innodb on multi-table update
            
      Certain multi-updates gave different results on InnoDB as opposed 
      to MyISAM, due to on-the-fly updates being used on the former and
      the update order matters.
      Fixed by turning off on-the-fly updates when update order 
      dependencies are present.
     @ mysql-test/r/innodb_mysql.result
        Bug#43580: Test result.
     @ mysql-test/t/innodb_mysql.test
        Bug#43580: Test case.
     @ sql/sql_base.cc
        Bug#43580: Added a word of caution about using tmp_set here.
     @ sql/sql_update.cc
        Bug#43580: The fix
[22 Apr 2009 9:32] 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/72648

2846 Martin Hansson	2009-04-22
      Bug#43580: Issue with Innodb on multi-table update
                  
      Certain multi-updates gave different results on InnoDB as opposed 
      to MyISAM, due to on-the-fly updates being used on the former and
      the update order matters.
      Fixed by turning off on-the-fly updates when update order 
      dependencies are present.
     @ mysql-test/r/innodb_mysql.result
        Bug#43580: Test result.
     @ mysql-test/t/innodb_mysql.test
        Bug#43580: Test case.
     @ sql/sql_base.cc
        Bug#43580: Added a word of caution about using tmp_set here.
     @ sql/sql_update.cc
        Bug#43580: The fix.
[22 Apr 2009 12:40] 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/72657

2864 Martin Hansson	2009-04-22
      Bug#43580: Issue with Innodb on multi-table update
                        
      Certain multi-updates gave different results on InnoDB as opposed 
      to MyISAM, due to on-the-fly updates being used on the former and
      the update order matters.
      Fixed by turning off on-the-fly updates when update order 
      dependencies are present.
     @ mysql-test/r/innodb_mysql.result
        Bug#43580: Test result.
     @ mysql-test/t/innodb_mysql.test
        Bug#43580: Test case.
     @ sql/sql_base.cc
        Bug#43580: Added a word of caution about using tmp_set here.
     @ sql/sql_update.cc
        Bug#43580: The fix.
[5 May 2009 9:38] 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/73369

2864 Martin Hansson	2009-05-05
      Bug#43580: Issue with Innodb on multi-table update
                              
      Certain multi-updates gave different results on InnoDB from
      to MyISAM, due to on-the-fly updates being used on the former and
      the update order matters.
      Fixed by turning off on-the-fly updates when update order 
      dependencies are present.
     @ mysql-test/r/innodb_mysql.result
        Bug#43580: Test result.
     @ mysql-test/suite/rpl/r/rpl_slave_skip.result
        Bug#43580: Changed test result. The InnoDB result is now what it would have been on MyISAM.
     @ mysql-test/t/innodb_mysql.test
        Bug#43580: Test case.
     @ sql/sql_base.cc
        Bug#43580: Added a word of caution about using tmp_set here.
     @ sql/sql_update.cc
        Bug#43580: Fix.
        Calls to TABLE::mark_columns_needed_for_update() are moved
        from mysql_multi_update_prepare() and right before the decison
        to do on-the-fly updates to the place where we do (or don't do)
        on-the-fly updates.
[28 May 2009 8:14] Bugs System
Pushed into 5.1.36 (revid:joro@sun.com-20090528073639-yohsb4q1jzg7ycws) (version source revid:jimw@mysql.com-20090513155634-t0kernv9w0jd7sxq) (merge vers: 5.1.35) (pib:6)
[1 Jun 2009 20:05] Paul Dubois
Noted in 5.1.36 changelog.

Multiple-table updates for InnoDB tables could produce incorrect
results.
[2 Jun 2009 8:04] Martin Hansson
I should probably be "unexpected results" rather that "incorrect results".
[17 Jun 2009 19:22] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:mhansson@mysql.com-20090513151922-qrhwq1z70ctib33r) (merge vers: 6.0.12-alpha) (pib:11)
[25 Jun 2009 22:39] Paul Dubois
Noted in 5.4.4 changelog.

Changed "incorrect" to "unexpected" in changelog entry.
[12 Aug 2009 22:41] Paul Dubois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 1:57] Paul Dubois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48] Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:33] Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[8 Oct 2009 2:48] Paul Dubois
The 5.4 fix has been pushed to 5.4.2.