MySQL Bugs: #33389: Selecting from a view into a table from within SP or trigger crashes server

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

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.

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.

stack trace from 5.0.54

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

simpler testcase

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

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;

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.

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.

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Pushed into 5.0.60

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.

Also noted fix in the 5.1.23-ndb-6.3.11 changelog.