Bug #33389 Selecting from a view into a table from within SP or trigger crashes server
Submitted: 20 Dec 2007 4:52 Modified: 14 Mar 2008 22:18
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.0.54, 5.0.52, 5.1.22 OS:Any (Windows, Linux, 32 or 64 bit)
Assigned to: Alexey Kopytov CPU Architecture:Any
Triage: D1 (Critical)

[20 Dec 2007 4:52] Valeriy Kravchuk
Description:
The following stored procedure materializes a row from a view:

CREATE PROCEDURE `update_active_deliveries_m_v`(p_id bigint)
    DETERMINISTIC
BEGIN
    DELETE FROM active_deliveries_m_v WHERE package_id = p_id;
    REPLACE INTO active_deliveries_m_v SELECT * FROM active_deliveries_v WHERE package_id = p_id;
END//

When the procedure is called, it crashes the server on the second consecutive call with the same p_id value. Same DELETE and REPLACE INTO called outside of SP just work.

The active_deliveries_v view has a call to stored function (or to UDF) in its definition.

How to repeat:
1. Execute:

create function cardit_compare(a0 int, a1 char, a2 char, a3 char, a4 int, a5 char, a6 char, a7 char) returns int return 1;

in test database as root.

2. Load bug_repl.sql attached into a test database. 

3. Check that there is 'root'.'%' with ALL privileges on *.* in the database. 

4. Execute:

call update_active_deliveries_m_v(381275702);
call update_active_deliveries_m_v(381275702);

You'll get a crash.

Suggested fix:
Do not crash. Or do not allow to use views with stored functions or UDFs in stored procedures.
[20 Dec 2007 14:51] Chris DiMartino
Note:  The stored function/udf in the view does not appear to have any bearing on whether or not it crashes.  There is another procedure that I have that does the exactly same thing to a different view which also contains the same stored function/udf and that one works flawlessly.
[5 Jan 2008 17:03] Shane Bester
stack trace from 5.0.54

Attachment: bug33389_stack_trace_5.0.54.txt (text/plain), 5.07 KiB.

[6 Jan 2008 9:39] Shane Bester
simpler testcase

Attachment: smallview.sql (application/unknown, text), 839 bytes.

[29 Jan 2008 14:51] Konstantin Osipov
As the test case from Shane demonstrates, this is not a stored procedures bug,
but a yet another bug in subquery optimisation and re-execution.
Below goes a test case that does not use stored procedures:
 drop database if exists test;
create database test;
use test;

drop table if exists `t1`;
create table `t1` (`a` char(3) default null,key(`a`)) engine=innodb;

drop table if exists `t2`;
create table `t2` (`b` char(4))engine=myisam;
                   
drop table if exists `t3`;
create table `t3` (`c` char(4)not null) engine=myisam;
                   
drop view if exists `v1`;
create view `v1` as select `b` from `t2`;
                   
drop table if exists `v2`;
drop view if exists `v2`;
create view `v2` as 
select 1 as a from `t3` `s`
left join `v1` `c` on 1
where `c` in (select 1 from `t1` where `b` = `a`);
                   
insert into `t3` values ();
                   
prepare stmt from "select * from v2 where a = ?";
set @a=1;
execute stmt using @a; 
execute stmt using @a;
execute stmt using @a;
execute stmt using @a;
[10 Feb 2008 12: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/41987

ChangeSet@1.2593, 2008-02-10 15:56:54+03:00, kaa@mbp. +3 -0
  Fix for bug #33389: Selecting from a view into a table from within SP
                      or trigger crashes server
  
  Under some circumstances a combination of VIEWs, subselects with outer
  references and PS/SP/triggers could lead to use of uninitialized memory
  and server crash as a result.
  
  Fixed by changing the code in Item_field::fix_fields() so that in cases
  when the field is a VIEW reference, we first check whether the field
  is also an outer reference, and mark it appropriately before returning.
[12 Feb 2008 9:44] 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/42093

ChangeSet@1.2593, 2008-02-12 12:43:55+03:00, kaa@mbp. +3 -0
  Fix for bug #33389: Selecting from a view into a table from within SP
                      or trigger crashes server
  
  Under some circumstances a combination of VIEWs, subselects with outer
  references and PS/SP/triggers could lead to use of uninitialized memory
  and server crash as a result.
  
  Fixed by changing the code in Item_field::fix_fields() so that in cases
  when the field is a VIEW reference, we first check whether the field
  is also an outer reference, and mark it appropriately before returning.
[13 Mar 2008 19:28] Bugs System
Pushed into 6.0.5-alpha
[13 Mar 2008 19:35] Bugs System
Pushed into 5.1.24-rc
[13 Mar 2008 19:43] Bugs System
Pushed into 5.0.60
[14 Mar 2008 22:18] Jon Stephens
Documented bugfix in the 5.0.60, 5.1.24, and 6.0.5 changelogs as follows:

        Certain combinations of views, subselects with outer references and
        stored routines or triggers could cause the server to crash.
[2 Apr 2008 16:08] Jon Stephens
Also noted fix in the 5.1.23-ndb-6.3.11 changelog.