Bug #20953 create proc with a create view that uses local vars/params should fail to create
Submitted: 10 Jul 2006 21:45 Modified: 14 Nov 2006 3:55
Reporter: Erica Moss Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0, 5.1.12 OS:Linux (Fedora core 5)
Assigned to: Tomash Brechko CPU Architecture:Any

[10 Jul 2006 21:45] Erica Moss
Description:
According to the manual:
A view definition is subject to the following restrictions...
Within a stored routine, the definition cannot refer to routine parameters or local variables.

If you try to do this, it will in fact fail, but not until you try to call the procedure.  It seems incorrect to for the parser not to catch a fairly obvious error such as this at the CREATE PROCEDURE time.

How to repeat:
DELIMITER //;
CREATE PROCEDURE sp(a INT)
BEGIN
    DECLARE b INT DEFAULT 9;
    CREATE VIEW bad AS SELECT a; # either including this line or 
    CREATE VIEW bad2 AS SELECT b; # this one will cause this error
END //
DELIMITER ;//

call sp(66);

# output
mysqltest: At line 247: query 'call sp(66)' failed: 1351: View's SELECT contains a variable or parameter
[21 Jul 2006 16:13] 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/9420
[25 Aug 2006 16:04] Konstantin Osipov
Reviewed by email. Proposed an alternative solution.
[5 Sep 2006 14:31] 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/11404

ChangeSet@1.2237, 2006-09-05 18:30:52+04:00, kroki@moonlight.intranet +8 -0
  BUG#20953: create proc with a create view that uses local vars/params
             should fail to create
  
  The problem was that this type of errors was checked during view
  creation, which doesn't happen when CREATE VIEW is a statement of
  a created stored routine.
  
  The solution is to perform the checks at parse time.  The idea of the
  fix is that the parser checks if a construction just parsed is allowed
  in current circumstances by testing certain flags, and this flags are
  reset for VIEWs.
  
  The side effect of this change is that if the user already have
  such bogus routines, it will now get a error when trying to do
  
    SHOW CREATE PROCEDURE proc;
  
  (and some other) and when trying to execute such routine he will get
  
    ERROR 1457 (HY000): Failed to load routine test.p5. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
  
  However there should be very few such users (if any), and they may
  (and should) drop these bogus routines.
[22 Sep 2006 12:25] Konstantin Osipov
Approved by email, asking Marc to do another review.
[22 Sep 2006 15:52] Marc Alff
Patch approved with comments (sent by email).
[12 Oct 2006 11:17] 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/13579

ChangeSet@1.2237, 2006-10-12 15:15:21+04:00, kroki@moonlight.intranet +8 -0
  BUG#20953: create proc with a create view that uses local vars/params
             should fail to create
  
  The problem was that this type of errors was checked during view
  creation, which doesn't happen when CREATE VIEW is a statement of
  a created stored routine.
  
  The solution is to perform the checks at parse time.  The idea of the
  fix is that the parser checks if a construction just parsed is allowed
  in current circumstances by testing certain flags, and this flags are
  reset for VIEWs.
  
  The side effect of this change is that if the user already have
  such bogus routines, it will now get a error when trying to do
  
    SHOW CREATE PROCEDURE proc;
  
  (and some other) and when trying to execute such routine he will get
  
    ERROR 1457 (HY000): Failed to load routine test.p5. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
  
  However there should be very few such users (if any), and they may
  (and should) drop these bogus routines.
[12 Oct 2006 14:04] 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/13600

ChangeSet@1.2237, 2006-10-12 18:02:57+04:00, kroki@moonlight.intranet +8 -0
  BUG#20953: create proc with a create view that uses local vars/params
             should fail to create
  
  The problem was that this type of errors was checked during view
  creation, which doesn't happen when CREATE VIEW is a statement of
  a created stored routine.
  
  The solution is to perform the checks at parse time.  The idea of the
  fix is that the parser checks if a construction just parsed is allowed
  in current circumstances by testing certain flags, and this flags are
  reset for VIEWs.
  
  The side effect of this change is that if the user already have
  such bogus routines, it will now get a error when trying to do
  
    SHOW CREATE PROCEDURE proc;
  
  (and some other) and when trying to execute such routine he will get
  
    ERROR 1457 (HY000): Failed to load routine test.p5. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
  
  However there should be very few such users (if any), and they may
  (and should) drop these bogus routines.
[12 Oct 2006 17:00] Tomash Brechko
Queued to 5.0-runtime and 5.1-runtime.
[14 Nov 2006 3:55] Paul Dubois
Noted in 5.0.30 (not 5.0.29), 5.1.13 changelogs.

Within a stored routine, a view definition cannot refer to routine
parameters or local variables. However, an error did not occur until
the routine was called. Now it occurs during parsing of the routine
creation statement.