Bug #49552 sql_buffer_result cause crash + not found records in multitable delete/subquery
Submitted: 9 Dec 2009 9:52 Modified: 7 Jul 2010 19:21
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.0.88,5.1.41 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: SQL_BUFFER_RESULT
Triage: Triaged: D1 (Critical)

[9 Dec 2009 9:52] Shane Bester
Description:
the testcase results change for different engines:

innodb: 
-------
091209 11:45:07  InnoDB: Assertion failure in thread 440 in file .\row\row0mysql.c line 1386
InnoDB: Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON

myisam:
-------
ERROR 1032 (HY000): Can't find record in 't1'

memory:
-------
ERROR 1030 (HY000): Got error -1 from storage engine

How to repeat:
drop table if exists `t1`;
create table `t1`(`a` int)engine=innodb;
insert into `t1` values (1),(2),(3);
set session sql_buffer_result=1;
delete `t1` from 
(select sum(`a`)`a` from `t1`) `x`,`t1`;

drop table if exists `t1`;
create table `t1`(`a` int)engine=myisam;
insert into `t1` values (1),(2),(3);
set session sql_buffer_result=1;
delete `t1` from 
(select sum(`a`)`a` from `t1`) `x`,`t1`;

drop table if exists `t1`;
create table `t1`(`a` int)engine=memory;
insert into `t1` values (1),(2),(3);
set session sql_buffer_result=1;
delete `t1` from 
(select sum(`a`)`a` from `t1`) `x`,`t1`;
[9 Dec 2009 10:08] Miguel Solorzano
Thank you for the bug report. Verified as described.
[22 Jan 2010 13:58] 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/97876

3333 Georgi Kodinov	2010-01-22
      Bug #49552 : sql_buffer_result cause crash + not found records
        in multitable delete/subquery
      
      SQL_BUFFER_RESULT should not have an effect on non-SELECT 
      statements according to our documentation.
      Fixed by not passing it through to multi-table DELETE (similarly
      to how it's done for multi-table UPDATE).
[29 Jan 2010 9:36] 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/98557

3333 Georgi Kodinov	2010-01-29
      Bug #49552 : sql_buffer_result cause crash + not found records
        in multitable delete/subquery
      
      SQL_BUFFER_RESULT should not have an effect on non-SELECT 
      statements according to our documentation.
      Fixed by not passing it through to multi-table DELETE (similarly
      to how it's done for multi-table UPDATE).
[29 Jan 2010 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/98558

3339 Georgi Kodinov	2010-01-29
      Bug #49552 : sql_buffer_result cause crash + not found records
        in multitable delete/subquery
      
      SQL_BUFFER_RESULT should not have an effect on non-SELECT 
      statements according to our documentation.
      Fixed by not passing it through to multi-table DELETE (similarly
      to how it's done for multi-table UPDATE).
[4 Feb 2010 10:18] Bugs System
Pushed into 5.1.44 (revid:joro@sun.com-20100204101444-2j32mhqroo0iiio6) (version source revid:joro@sun.com-20100129093628-sze9cv0neu0xbabm) (merge vers: 5.1.43) (pib:16)
[5 Feb 2010 11:45] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100204063540-9czpdmpixi3iw2yb) (version source revid:alik@sun.com-20100130220650-3md8m8w9frcwjj9f) (pib:16)
[5 Feb 2010 11:56] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100205113942-oqovjy0eoqbarn7i) (version source revid:alik@sun.com-20100204064210-ljwanqvrjs83s1gq) (merge vers: 6.0.14-alpha) (pib:16)
[5 Feb 2010 11:57] Bugs System
Pushed into 5.5.2-m2 (revid:alik@sun.com-20100203172258-1n5dsotny40yufxw) (version source revid:alik@sun.com-20100130191336-i53i9wx67n81ridm) (merge vers: 5.5.2-m2) (pib:16)
[11 Feb 2010 22:05] Paul Dubois
Noted in 5.1.44, 5.5.2, 6.0.14 changelogs.

sql_buffer_result had an effect on non-SELECT statements, contrary to
the documentation.  

Setting report to Need Merge pending push to Celosia.
[25 Feb 2010 14:57] 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/101456

2844 Georgi Kodinov	2010-02-25
      Backport of the fix for bug #49552 to 5.0-bugteam
[1 Mar 2010 8:36] Bugs System
Pushed into 5.0.91 (revid:joro@sun.com-20100301083514-xujntiv5hlavch8n) (version source revid:joro@sun.com-20100225145715-er2430j93w6fi43q) (merge vers: 5.0.91) (pib:16)
[1 Mar 2010 8:46] Bugs System
Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:joro@sun.com-20100225151215-2mfhwvyw6f3vtwxf) (merge vers: 5.1.45) (pib:16)
[1 Mar 2010 17:26] Paul Dubois
Noted in 5.0.91 changelog.

Already fixed in 5.1.x.
[2 Mar 2010 14:32] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100301095421-4cz64ibem1h2quve) (merge vers: 6.0.14-alpha) (pib:16)
[2 Mar 2010 14:37] Bugs System
Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alexey.kopytov@sun.com-20100226130631-8czhisohzf6jyo2x) (merge vers: 5.5.3-m2) (pib:16)
[2 Mar 2010 14:43] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100301093944-a4rvrmqqco6c0qao) (pib:16)
[12 Mar 2010 14:14] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:30] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:46] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[12 Mar 2010 16:33] Paul Dubois
Fixed in earlier 5.1.x, 5.5.x.
[9 Apr 2010 14:51] Paul Dubois
Noted in 5.1.43sp1 changelog.
[12 Apr 2010 10:16] Bugs System
Pushed into 5.1.47 (revid:build@mysql.com-20100412101220-adgau1r18kwgthps) (version source revid:build@mysql.com-20100412101220-adgau1r18kwgthps) (merge vers: 5.1.47) (pib:16)
[28 May 2010 6:13] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:41] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100422150658-fkhgnwwkyugtxrmu) (merge vers: 6.0.14-alpha) (pib:16)
[28 May 2010 7:09] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100429203306-tg0wz4y2xyx8edrl) (merge vers: 5.5.5-m3) (pib:16)
[29 May 2010 2:30] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 12:19] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:07] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:47] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)