MySQL Bugs: #48615: A function with now() may be declared deterministic

Bug #48615	A function with now() may be declared deterministic
Submitted:	7 Nov 2009 21:37	Modified:	21 May 2010 18:36
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0 +	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html
"A routine that contains the NOW() function (or its synonyms) or RAND() is nondeterministic"

But no error or warning if I 

DELIMITER $$
CREATE FUNCTION `test`.`test1`()
    RETURNS DATETIME
    DETERMINISTIC
    BEGIN
      RETURN (NOW());
    END$$
DELIMITER ; 

-- now repeat with some seconds interval
SELECT test.test1();

How to repeat:
see above.

Suggested fix:
well .. maybe only docs should explicitly mention this behaviour like "DETERMINISTIC" is ignored for routines using now() or rand()".  But I think rather an error should be returned.  

Actually from same docs document:
"A procedure or function is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise. If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERMINISTIC." It is not very clear what *is considered* means actually.

It should be documented what difference it makes to specify/declare DETERMINISTIC versus NOT in CREATE statement (if any)?

This report is a as-stupid-as-possible 'boil down' of one of the discussions in this report: http://bugs.mysql.com/48611 - but is this other one is primarily a performance related report I think a seperate report here is relevant.

Also this very much deterministic function can be defined NOT DETERMINISTIC:

DELIMITER $$
CREATE
    FUNCTION `test`.`test2`(a INTEGER)
    RETURNS INTEGER
    NOT DETERMINISTIC
    BEGIN
      RETURN a+1;
    END$$
DELIMITER ;

.. so when and how is DETERMINISTIC | NOT DETERMINISTIC used by the server?  Obviously not at create-time.  But by the optimizer maybe?

A clarification: I understand and accept of course that checking for 'determinism' at create-time is probably hopeless with non-trivial examples - except for routines using now(), synonymns of now(), and rand().

But if this flag is used by the server at all it should be made *very clear* that it is user's own responsibility to set the flag correctly.   If the information is not used at all, it should be made equally clear that this information may (or may not) be entered by user for clarity - and for no other purpose.

Quoting from the manual:

"Assessment of the nature of a function is based on the “honesty” of the creator: MySQL does not check that a function declared DETERMINISTIC is free of statements that produce nondeterministic results."

@davi .. can you please provide a link to the page with this? 

Also: do you think it is properly explained how the server makes use of determinism as defined by user?

I understand:
1) if a non-deterministic routine is declared DETERMINISTIC it may lead to unexpected results (as the optimizer may not execute routines for every call()).
2) if a deterministic routine is declared NOT DETERMINISTIC it may degrade performance (as unnessary call(s) may be executed).

.. Correct? If so I think it is very important to *pin it out*

> @davi .. can you please provide a link to the page with this? 

http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html

> Also: do you think it is properly explained how the server makes use of determinism
> as defined by user?

It certainly could be improved.

> I understand:
> 1) if a non-deterministic routine is declared DETERMINISTIC it may lead to
> unexpected results (as the optimizer may not execute routines for every call()).

I suppose so. For example, could cause problems with constant propagation.

> 2) if a deterministic routine is declared NOT DETERMINISTIC it may degrade
> performance (as unnessary call(s) may be executed).

Yes as it might prohibit some optimizations from being applied.

> .. Correct? If so I think it is very important to *pin it out*

I agree.

So, I think, we have a valid documentation request here.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.