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).
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).