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
SQLITE_DETERMINISTIC:
https://www.sqlite.org/deterministic.html
<https://www.sqlite.org/deterministic.html>
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
function.)
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
