MySQL Bugs: #34569: DETERMINISTIC is used by the optimizer for stored functions

Bug #34569	DETERMINISTIC is used by the optimizer for stored functions
Submitted:	14 Feb 2008 21:46	Modified:	19 Feb 2008 20:32
Reporter:	Harrison Fisk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Peter Lavin	CPU Architecture:	Any
Tags:	DETERMINISTIC function

Description:
The fix for Bug #29338 involved making the DETERMINISTIC keyword for functions matter now.  The optimizer can use indexes against a DETERMINISTIC function, but not for a NONDETERMINISTIC one.

The documentation at:

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

has the following which is now incorrect:

"Currently, the DETERMINISTIC characteristic is accepted, but not yet used by the optimizer."

How to repeat:
You can see this proved by:

use world;

CREATE FUNCTION `testing`() RETURNS char(3) return 'USA';
-- full table scan
EXPLAIN SELECT * FROM Country WHERE code = testing();
DROP FUNCTION testing;

CREATE FUNCTION `testing`() RETURNS char(3) deterministic return 'USA';
-- const
EXPLAIN SELECT * FROM Country WHERE code = testing();
DROP FUNCTION testing;

Suggested fix:
Update the documentation to reflect reality.

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.