Bug #45806 crash when replacing into a view with a join!
Submitted: 28 Jun 11:23 Modified: 13 Jul 21:54
Reporter: Shane Bester
Status: Closed
Category:Server: Views Severity:S1 (Critical)
Version:5.0.54, 5.0.84, 5.1.30, 5.1.37 OS:Any
Assigned to: Sergey Gluhov Target Version:5.1+
Triage: Triaged: D1 (Critical)

[28 Jun 11: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 13:40] Peter Laursen 
@Shane .. I do not understand: where is the JOIN?
[28 Jun 13:52] Shane Bester 
...`t1`,`t1` as `a`;
[1 Jul 13: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 12: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 11: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 11: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 9: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 15: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 9: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 9: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 13: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 21: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 19:56] Paul DuBois 
Noted in 5.0.82sp1 changelog.
[10 Aug 20: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)
[13 Aug 0:11] 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 1:03] Paul DuBois 
Ignore previous comment about 5.4.2.
[25 Aug 11: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 15: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 15: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 15: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 18: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 18: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 7: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 9: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 15: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 12: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 18:27] Paul DuBois 
The 5.4 fix has been pushed to 5.4.2.