In my test suite I register a deterministic function of the same name.  In prior releases this returned 1, and now it returns 0:

    SELECT deterministic() == deterministic()

I have confirmed that the registration is correct, and that the function is called twice for this SQL.  Using explain shows two calls, while with 3.31 it shows only one.

Is this intended, and is it a permanent change in SQLite?

Roger

> Is this intended, and is it a permanent change in SQLite?

There is an ongoing discussion about this in [](/forumpost/e79a299b3f).

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
~~~~~~

> CREATE INDEX t2a ON t2(a) WHERE deterministic();      -- works

I've updated my test to use this.  It meets the goal of verifying my wrapper really is setting the flag correctly.

Thanks,

Roger