Bug #33289 Stored Procedure: bad data if view is replaced within a iteration
Submitted: 17 Dec 2007 14:05 Modified: 20 Jun 2012 16:36
Reporter: Davi Arnaut (OCA) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0, 5.1, 6.0 BK OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[17 Dec 2007 14:05] Davi Arnaut
In some circumstances if a view is replaced within a iteration inside a stored procedure, selects from the view will always return the same value.

How to repeat:
create view v1 as select 1;
delimiter |;
create procedure bad_data()
  declare iteration integer default 0;
  while iteration < 10 do
    if iteration = 1 then
      drop view v1;
      create view v1 as select 2;
    end if;
    select * from v1;
    set iteration= iteration+1;
  end while;
delimiter ;|
call bad_data();
select * from v1;
[17 Dec 2007 14:13] Davi Arnaut
The expected result is that after the view is replaced, it should only return values from the new view, not from the old dropped one, yet this only return values from the old view.
[17 Dec 2007 16:06] Miguel Solorzano
Thank you for the bug report.
[20 Jun 2012 16:36] Paul Dubois
Noted in 5.6.6 changelog.

Bad data could be returned from a view used within a stored program
if the view definition was changed between executions of the program
or while the view was used within a program loop.