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)
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.0, 5.1, 6.0 BK
• OS: Any
• CPU Architecture: Any

[17 Dec 2007 14:05] Davi Arnaut

Description:
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()
begin
  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;
end|
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] MySQL Verification Team

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.