MySQL Bugs: #43580: Issue with Innodb on multi-table update

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

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.

I do not see any files attached yet.

Test Case

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

Please find the file attached.

Verified just as described with your test case and recent 6.0.11 from bzr on Linux.

5.1 from brz is also affected.

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.

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

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.

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.

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.

> 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 ?

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

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

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.

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

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.

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.

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.

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)

Noted in 5.1.36 changelog.

Multiple-table updates for InnoDB tables could produce incorrect
results.

I should probably be "unexpected results" rather that "incorrect results".

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)

Noted in 5.4.4 changelog.

Changed "incorrect" to "unexpected" in changelog entry.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

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)

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)

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)

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)

The 5.4 fix has been pushed to 5.4.2.