Bug #45806 crash when replacing into a view with a join!
Submitted: 28 Jun 2009 9:23 Modified: 13 Jul 2009 19:54
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S1 (Critical)
Version:5.0.54, 5.0.84, 5.1.30, 5.1.37 OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any

[28 Jun 2009 9:23] Shane Bester 
Description:
using 'replace into' or 'insert .. on duplicate key update' into a view crashes the server: 

mysqld.exe!mysql_prepare_insert()[sql_insert.cc:1270]
mysqld.exe!mysql_insert_select_prepare()[sql_insert.cc:2845]
mysqld.exe!mysql_execute_command()[sql_parse.cc:3186]
mysqld.exe!mysql_parse()[sql_parse.cc:5933]
mysqld.exe!dispatch_command()[sql_parse.cc:1213]
mysqld.exe!do_command()[sql_parse.cc:854]
mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:293]
mysqld.exe!_threadstart()[thread.c:277]
kernel32.dll!FlsSetValue()

How to repeat:
drop table if exists `t1`,`v1`;
drop view if exists v1;
create table `t1`(`a` int) engine=myisam;
create or replace view `v1` as select `t1`.`a` from `t1`,`t1` as `a`;

#both cause a crash:
replace into `v1`(`a`) select 1 from `t1`,`t1` as `c`;

insert into `v1`(`a`) select 1 from `t1`,`t1` as `c` 
on duplicate key update `v1`.`a`=1;
[28 Jun 2009 11:40] Peter Laursen 
@Shane .. I do not understand: where is the JOIN?
[28 Jun 2009 11:52] MySQL Verification Team 
...`t1`,`t1` as `a`;
[1 Jul 2009 11:06] 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/77644

2789 Sergey Glukhov	2009-07-01
      Bug#45806 crash when replacing into a view with a join!
      The crash happend because for views which are joins
      we have table_list->table == 0 and 
      table_list->table->'any method' call leads to crash.
      The fix is to perform table_list->table->file->extra()
      method for all tables belonging to view.
     @ mysql-test/r/view.result
        test result
     @ mysql-test/t/view.test
        test case
     @ sql/sql_insert.cc
        added update_info_for_insert() function which
        updates extra info about primary key for tables
        belonging to view.
[2 Jul 2009 10:41] Georgi Kodinov 
Hi Sergey,

I have some recommendations and some questions on your fix : 
recommendations: 
1. update_info_for_insert is not a good name imho. We can "steal" the 5.1 name and extend it : something like "prepare_for_positional_update" ?
2. There's no need to pass the dublic arguments to the function only to keep the condition the same. You could check the dublic condition on prepare_for_insert (once) and then recursively check only TL_WRITE_DELAYED. 
3. The way the function is written it should be callable instead of the original code, not before it. Please make the call unconditional and remove the original code.
4. Add some test code that will actually insert some rows with REPLACE on a join view. Ditto for INSERT ... ON DUPLICATE . 

questions:
1. Wouldn't it be enough to check the TL_WRITE_DELAYED in the top-level TABLE_LIST for the view instead of going to the underlying tables ?
2. does this work as expected ? have you traced the execution to see if it actually does what REPLACE is supposed to do with a JOIN view ?
[3 Jul 2009 9: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/77858

2789 Sergey Glukhov	2009-07-03
      Bug#45806 crash when replacing into a view with a join!
      The crash happend because for views which are joins
      we have table_list->table == 0 and 
      table_list->table->'any method' call leads to crash.
      The fix is to perform table_list->table->file->extra()
      method for all tables belonging to view.
     @ mysql-test/r/view.result
        test result
     @ mysql-test/t/view.test
        test case
     @ sql/sql_insert.cc
        added prepare_for_positional_update() function
        which updates extra info about primary key for
        tables belonging to view.
[3 Jul 2009 9:49] 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/77865

2792 Sergey Glukhov	2009-07-03
      Bug#45806 crash when replacing into a view with a join!
      The crash happend because for views which are joins
      we have table_list->table == 0 and 
      table_list->table->'any method' call leads to crash.
      The fix is to perform table_list->table->file->extra()
      method for all tables belonging to view.
     @ mysql-test/r/view.result
        test result
     @ mysql-test/t/view.test
        test case
     @ sql/sql_insert.cc
        added prepare_for_positional_update() function
        which updates extra info about primary key for
        tables belonging to view.
[7 Jul 2009 7:52] Bugs System 
Pushed into 5.0.84 (revid:joro@sun.com-20090707074938-ksah1ibn0vs92cem) (version source revid:sergey.glukhov@sun.com-20090703083500-jq8vhw0tqr37j7te) (merge vers: 5.0.84) (pib:11)
[8 Jul 2009 13:30] Bugs System 
Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:sergey.glukhov@sun.com-20090703083922-a6ejp0imgq25b9tb) (merge vers: 5.1.37) (pib:11)
[9 Jul 2009 7:35] Bugs System 
Pushed into 5.0.84 (revid:joro@sun.com-20090707074938-ksah1ibn0vs92cem) (version source revid:sergey.glukhov@sun.com-20090703083500-jq8vhw0tqr37j7te) (merge vers: 5.0.84) (pib:11)
[9 Jul 2009 7:36] Bugs System 
Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:sergey.glukhov@sun.com-20090703083922-a6ejp0imgq25b9tb) (merge vers: 5.1.37) (pib:11)
[10 Jul 2009 11:20] Bugs System 
Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:sergey.glukhov@sun.com-20090703084246-enrwy4y38vlnumrw) (merge vers: 5.4.4-alpha) (pib:11)
[13 Jul 2009 19:54] Paul DuBois 
Noted in 5.0.84, 5.1.37, 5.4.4 changelogs.

The server crashed for attempts to use REPLACE or INSERT ... ON
DUPLICATE KEY UPDATE with a view defined using a join.
[10 Aug 2009 17:56] Paul DuBois 
Noted in 5.0.82sp1 changelog.
[10 Aug 2009 18:59] Bugs System 
Pushed into 5.0.85 (revid:build@mysql.com-20090810185326-yr4orhpwq09e3y50) (version source revid:build@mysql.com-20090810185326-yr4orhpwq09e3y50) (merge vers: 5.0.85) (pib:11)
[12 Aug 2009 22:11] Paul DuBois 
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[14 Aug 2009 23:03] Paul DuBois 
Ignore previous comment about 5.4.2.
[25 Aug 2009 9:23] Bugs System 
Pushed into 5.1.39 (revid:jperkin@sun.com-20090824091334-6ktgrhq218vl7zq1) (version source revid:joerg@mysql.com-20090813203300-nnskc3aofxydzi85) (merge vers: 5.1.39) (pib:11)
[26 Aug 2009 13:45] 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:32] 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)
[14 Sep 2009 16:02] Bugs System 
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[1 Oct 2009 5:58] Bugs System 
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 7:25] Bugs System 
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 13:25] Bugs System 
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)
[5 Oct 2009 10:50] Bugs System 
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)
[7 Oct 2009 16:27] Paul DuBois 
The 5.4 fix has been pushed to 5.4.2.