On 2017-09-12 11:41 AM, Jens Alfke wrote:
On Sep 12, 2017, at 11:09 AM, Warren Young <war...@etr-usa.com> wrote:

From my reading of the docs, I don’t see that that is the purpose of 


Actually it is. "A deterministic function always gives the same answer when it has the same 
inputs." That is the definition of a mathematical (also called "pure") function. 
Such a function call can of course be factored out as a common subexpression.

The purpose is simply so that the SQLite internals know whether it is safe to 
use the user-defined function in certain query types.

The reason SQLITE_DETERMINISTIC was added is to allow such functions to be used 
in indexes and then matched with the same function in a query. That allows 
indexing things like JSON properties (via the deterministic json_value 

Practically speaking any optimization to reduce actual calls to the deterministic function would have to be at compile time to rewrite the query to explicitly keep the result of the function and use it several times, which is someone users can also do by writing the query differently.

The fact is, any runtime-level smarts to prevent multiple calls to the function would have to involve creating and maintaining an index of inputs to outputs so that the DBMS knows whether the function was already called with particular inputs or not, so that would be an added complexity.

-- Darren Duncan

sqlite-users mailing list

Reply via email to