SQLITE_DETERMINISTIC change in 3.32
(1) By Roger Binns (rogerbinns) on 2020-05-27 18:33:24 [link] [source]
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
(2.1) By Stephan Beal (stephan) on 2020-05-27 19:14:50 edited from 2.0 in reply to 1 [link] [source]
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 and in the definition of generated columns. SQLITE_DETERMINISTIC does not make any claims about how often the underlying function implementation is invoked.
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
(5) By Roger Binns (rogerbinns) on 2020-05-27 21:48:55 in reply to 3 [source]
> 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