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)
• Status: Closed
• 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

[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] MySQL Verification Team

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:[e-mail]-20100204101444-2j32mhqroo0iiio6) (version source revid:[e-mail]-20100129093628-sze9cv0neu0xbabm) (merge vers: 5.1.43) (pib:16)

[5 Feb 2010 11:45] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100204063540-9czpdmpixi3iw2yb) (version source revid:[e-mail]-20100130220650-3md8m8w9frcwjj9f) (pib:16)

[5 Feb 2010 11:56] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100205113942-oqovjy0eoqbarn7i) (version source revid:[e-mail]-20100204064210-ljwanqvrjs83s1gq) (merge vers: 6.0.14-alpha) (pib:16)

[5 Feb 2010 11:57] Bugs System

Pushed into 5.5.2-m2 (revid:[e-mail]-20100203172258-1n5dsotny40yufxw) (version source revid:[e-mail]-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:[e-mail]-20100301083514-xujntiv5hlavch8n) (version source revid:[e-mail]-20100225145715-er2430j93w6fi43q) (merge vers: 5.0.91) (pib:16)

[1 Mar 2010 8:46] Bugs System

Pushed into 5.1.45 (revid:[e-mail]-20100301083827-xnimmrjg6bh33o1o) (version source revid:[e-mail]-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:[e-mail]-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:[e-mail]-20100301095421-4cz64ibem1h2quve) (merge vers: 6.0.14-alpha) (pib:16)

[2 Mar 2010 14:37] Bugs System

Pushed into 5.5.3-m2 (revid:[e-mail]-20100302072233-t3uqgjzdukt1pyhe) (version source revid:[e-mail]-20100226130631-8czhisohzf6jyo2x) (merge vers: 5.5.3-m2) (pib:16)

[2 Mar 2010 14:43] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100302072432-k8xvfkgcggkwgi94) (version source revid:[e-mail]-20100301093944-a4rvrmqqco6c0qao) (pib:16)

[12 Mar 2010 14:14] Bugs System

Pushed into 5.1.44-ndb-7.0.14 (revid:[e-mail]-20100312135944-t0z8s1da2orvl66x) (version source revid:[e-mail]-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:[e-mail]-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:[e-mail]-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:[e-mail]-20100312135724-xcw8vw2lu3mijrhn) (version source revid:[e-mail]-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:[e-mail]-20100412101220-adgau1r18kwgthps) (version source revid:[e-mail]-20100412101220-adgau1r18kwgthps) (merge vers: 5.1.47) (pib:16)

[28 May 2010 6:13] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:41] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100422150658-fkhgnwwkyugtxrmu) (merge vers: 6.0.14-alpha) (pib:16)

[28 May 2010 7:09] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-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:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-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:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-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:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)