Bug #26334 Inconsistency: ALTER PROC/FUNC doesn't allow [NOT] DETERMINISTIC
Submitted: 13 Feb 2007 16:50 Modified: 30 Apr 2007 18:12
Reporter: Alex Rytov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S4 (Feature request)
Version:5.1.15 OS:Any
Assigned to: CPU Architecture:Any
Tags: ALTER PROCEDURE, CONTAINS SQL, DETERMINISTIC
Triage: D5 (Feature request)

[13 Feb 2007 16:50] Alex Rytov
Description:
Stored procs and functions in mySQL can have some optional 'characteristics'.
Two of them: 

[NOT] DETERMINISTIC

and

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

[if I understnad correctly] are very similar in the sense that they both provide a way to explicitly tell the server about some characteristics of procedure's SQL code which could be, in principle, found by analyzing the code itself.

So it makes exactly as much sense to change one of them in ALTER PROC (without actually changing the procedure body) as the other one.

However, the 'CONTAINS SQL' attribute can be changed in the ALTER... statement,
whereas the '[NOT] DETERMINISTIC' is only allowed in the CREATE...

Importance: obviously, this is a pretty minor thing, which isn't likely to cause anybody any trouble. However, removing this inconsistency would just make the SQL syntax a bit cleaner.

How to repeat:
create procedure p()
  not deterministic
  no sql
  select 1;

-- This one is OK:
alter procedure p
  contains sql;

-- This one fails:
alter procedure p
  deterministic;

Suggested fix:
I'd suggest to either allow both attributes in ALTER, or remove them both from ALTER.  Or explain in the documentation why the current approach makes more sense (if it does).
[30 Apr 2007 18:12] Valeriy Kravchuk
Thank you for a reasonable feature request.