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
Category:Server: Docs Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: Peter Lavin Target Version:
Tags: DETERMINISTIC function
Triage: D4 (Minor)

[14 Feb 2008 21:46] Harrison Fisk 
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.
[19 Feb 2008 20:32] Peter Lavin 
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.