Bug #9227 DROP VIEW/TABLE IF EXISTS fails in SP if view/table doesn't exist

• Submitted: 16 Mar 2005 17:13
• Modified: 13 Sep 2005 13:26
• Reporter: Paul DuBois
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.3 BK
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[16 Mar 2005 17:13] Paul DuBois

Description:
In a stored procedure (or function) a
DROP VIEW IF EXISTS statement works
if the view exists, but fails if the view does
not exist:
ERROR 1146 (42S02): Table 'test.v' doesn't exist

The same behavior occurs with
DROP TABLE IF EXiSTS.

How to repeat:
use test;
drop procedure if exists p;
delimiter $
create procedure p ()
begin
drop view if exists v;
end$
delimiter ;

# Here, p() works; v exists when p() is invoked
drop view if exists v;
create view v as select 1;
call p ();
# Here, p() fails; v does not exist when p() is invoked
# (v is dropped by the first invocation of p())
call p ();

[19 May 2005 15:27] Geert Vanderkelen

Extra note, while trying to work around:
Defining a handler like:
  declare continue handler for sqlstate '42S02' set @err = 'Table does not exists!';

The handler is not invoked and you get the error:
  ERROR 1146 (42S02) at line 27: Table 'test.t' doesn't exist
and does not continue.

[13 Sep 2005 13:26] Oleksandr Byelkin

Thank you for bug report, but I can't repeat this bug any more:
+ drop procedure if exists p;
+ Warnings:
+ Note  1305    PROCEDURE p does not exist
+ create procedure p ()
+ begin
+ drop view if exists v;
+ end//
+ drop view if exists v;
+ Warnings:
+ Note  1051    Unknown table 'test.v'
+ create view v as select 1;
+ call p ();
+ call p ();
+ Warnings:
+ Note  1051    Unknown table 'test.v'