The SQLITE\_DETERMINISTIC flag means that the function can be used inside
the WHERE clause of [partial indexes][1] and
in the definition of [generated columns][2]. SQLITE\_DETERMINISTIC does not
make any claims about how often the underlying function implementation is
invoked.
[1]: https://www.sqlite.org/partialindex.html
[2]: https://www.sqlite.org/gencol.html
Manual Rigger found a bug in SQLite in which SQL like the following
would fail:
~~~~~
CREATE TABLE t1(x);
INSERT INTO t1 VALUES(1),(2),(3);
SELECT coalesce(x, abs(-9223372036854775808)) FROM t1;
~~~~~
Prior to version 3.32.0, the query at the end of the example would
fail with an integer overflow error in the abs() function, even though
the abs() function is never used. The reason for this is that SQLite
would formerly factor out the abs() function into the preamble of the
prepared statement, run it just one time, then reuse the result whenever
it was needed.
The fix in SQLite 3.32.0 is to run deterministic functions
just once when they are first used, rather than in the preamble.
A side-effect of that change is that if a deterministic function
with constant arguments appears in the input SQL multiple times, it
might be invoked once for each appearance, rather just once in the
preamble. So if you say:
~~~~~
SELECT deterministic(), deterministic() FROM t1;
~~~~~
The deterministic() function will be run twice (once for each
appearance in the input SQL), even though its result is used a
total of 6 times.
Note that even this 6-to-2 reduction in invocations is an arbitrary
artifact of the implementation and is not a guarantee. Future
versions of SQLite might invoke the function more or fewer times,
as the query optimizer sees fit.
A better test for the efficacy of the SQLITE\_DETERMINISTIC flag would
be to show that deterministic functions can be used in the WHERE
clause of partial indexes and that non-deterministic functions cannot:
~~~~~~
CREATE TABLE t2(a,b,c);
CREATE INDEX t2a ON t2(a) WHERE deterministic(); -- works
CREATE INDEX t2b ON t2(b) WHERE non_deterministic(); -- fails
~~~~~~