SQLite Forum

"Scope" of SQLITE_DETERMINISTIC?
Login

"Scope" of SQLITE_DETERMINISTIC?

(1) By Stephan Beal (stephan) on 2021-03-20 18:43:17 [link] [source]

https://sqlite.org/c3ref/c_deterministic.html:

The SQLITE_DETERMINISTIC flag means that the new function always gives the same output when the input parameters are the same.

Does "always" mean...

  1. For the duration of the connection into which the UDF is installed or...

  2. For the duration of a given statement's evaluation (a single sqlite3_step()) or...

  3. For the duration of a given statement handle's lifetime or...

  4. For the duration of all currently-being-evaluated statements?

  5. Something different?

e.g. if a statement is prepared and cached for the duration of the connection, as opposed to being used as a "one-shot," are SQLITE_DETERMINISTIC UDFs used by that statement treated as always returning the same result for the same input, regardless of how long that statement handle lives?

Use case: a UDF which does something like:

select my_app_dir() || 'sub/path/within/app/dir';

where the directory name returned by my_app_dir() may change during the life of the application but will never change during the evaluation (as opposed to sqlite3_stmt handle lifetime) of any given statement or series of statements processed concurrently during recursion.

FWIW, my goal here is to attempt to micro-optimize away a handful of allocations if the scope of the deterministic flag is suitable for that purpose. (That's what i do for fun on weekends. Nevermind for a moment that creating, sending, and reading this post is already allocating more memory than this micro-optimization would likely ever save. ;))

:-?

(2.1) By Keith Medcalf (kmedcalf) on 2021-03-20 19:18:34 edited from 2.0 in reply to 1 [link] [source]

SQLITE_DETERMINISTIC means that the function results are dependent on its parameters (or lack thereof) forever until the heat death of the multiverse. That is, that the result of function(x) for the same value of x will forever and always be the same.

There is another constant for things which are deterministic for the duration of a statement execution but may change between executions: SQLITE_SLOCHNG

I am not absolutely certain whether SLOCHNG implies step stability or the greater requirement of statement stability, but its general effect is to prevent the use of SLOCHNG functions in INDEX expressions but allow them to be used elsewhere as if they were DETERMINISTIC.

For example, I have UDFs called UserName(), UserSID(), LookupName(), LookupSID(), TokenHasSID(), TokenHasName(), GetFileAttributes(), DoesFileExist() which retrieve the username, usersid, lookup the name given a sid, lookup a sid given a name, test whether the current process security token contains a certain SID or name, the attributes of a file, or whether a file exists or not. Clearly these functions are "deterministic" for the duration of a statement execution, but subject to change between statements. Hence they are flagged SQLITE_SLOCHNG and SQLITE_CONSTANT. This allows the optimizer to move them into the execution prolog as constants but prohibits the use of the functions where true determinism is required (such as in indexes).

(3) By Keith Medcalf (kmedcalf) on 2021-03-20 19:30:54 in reply to 2.1 [link] [source]

In any case, the flags (DETERMINISTIC/SLOCHNG/CONSTANT) only apply for the duration of the execution of a statement -- that is, from the first call to sqlite3_step until the statement execution completes (either returning SQLITE_DONE or an error), or sqlite3_reset is called on the statement. Each statement execution context is entirely self contained and NOTHING save the database itself persists from one execution of a statement to another.

The various flags (DETERMINISTIC/SLOCHNG/CONSTANT) tell the query planner how the function is to be executed and where it is allowed to be used.

For example, a function with no flags is VOLATILE. That means that its result is not ever dependent on its parameters and that it must always be called whenever needed, each and every time. SQLITE_CONSTANT means that the return value is constant (deterministic) at the time it is executed and should be treated as such. SQLITE_SLOCHNG means that the value can change "slowly" and therefore should not be allowed where true determinism is required. SQLITE_DETERMINISTIC means the same as SQLITE_CONSTANT but may have additional requirements imposed on it.

The query planner may make use of the information provided to optimize the calling of such functions (for example, by only calling a constant function once for each given set of paramters, but does not actually impose this contraint -- only allows that to be considered).

(6) By Stephan Beal (stephan) on 2021-03-20 20:08:25 in reply to 2.1 [source]

I am not absolutely certain whether SLOCHNG implies step stability or the greater requirement of statement stability, but its general effect is to prevent the use of SLOCHNG functions in INDEX expressions but allow them to be used elsewhere as if they were DETERMINISTIC.

Thank you for the detailed responses, Keith!

SLOCHNG sounds like it's close enough to what i'm looking for, but it's not in the current docs and even google only finds 3 references to it from mailing archives.

This function would never be used in an index or some such, i'd just like to prevent that it gets called 293 times for:

select my_app_dir() || pathname from vfile;

for any given step loop. A subsequent step loop, however, should be free to call it again (once).

In any case, this isn't a "requirement" or "must-have" - just trying to eliminate some technically superfluous allocations.

(7) By Keith Medcalf (kmedcalf) on 2021-03-20 20:28:39 in reply to 6 [link] [source]

The current trunk version will only execute such functions once. Notice the ONCE block around lines 11-13:

sqlite> .eqp full
sqlite> select unixtime() || ':' || value from generate_series where start=1 and stop=10;
QUERY PLAN
`--SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: (~960 rows)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     21    0                    0   Start at 21
1     VOpen          0     0     0     vtab:124169C5310  0
2     Explain        2     0     0     SCAN TABLE generate_series VIRTUAL TABLE INDEX 3: (~960 rows)  0
3     Noop           0     0     0                    0   Begin WHERE-loop0: generate_series
4     Integer        1     3     0                    0   r[3]=1
5     Integer        10    4     0                    0   r[4]=10
6     Integer        3     1     0                    0   r[1]=3
7     Integer        2     2     0                    0   r[2]=2
8     VFilter        0     19    1                    0   iplan=r[1] zplan=''
9       Noop           0     0     0                    0   Begin WHERE-core
10      Once           0     14    0                    0
11      Function       0     0     7     unixtime(-1)   0   r[7]=func()
12      String8        0     8     0     :              0   r[8]=':'
13      Concat         8     7     6                    0   r[6]=r[7]+r[8]
14      VColumn        0     0     8                    0   r[8]=vcolumn(0); generate_series.value
15      Concat         8     6     5                    0   r[5]=r[6]+r[8]
16      ResultRow      5     1     0                    0   output=r[5]
17      Noop           0     0     0                    0   End WHERE-core
18    VNext          0     9     0                    0
19    Noop           0     0     0                    0   End WHERE-loop0: generate_series
20    Halt           0     0     0                    0
21    Transaction    0     0     0     0              1   usesStmtJournal=0
22    Goto           0     1     0                    0
┌────────────────────────────┐
│ unixtime() || ':' || value │
├────────────────────────────┤
│ 1616271686.964:1           │
│ 1616271686.964:2           │
│ 1616271686.964:3           │
│ 1616271686.964:4           │
│ 1616271686.964:5           │
│ 1616271686.964:6           │
│ 1616271686.964:7           │
│ 1616271686.964:8           │
│ 1616271686.964:9           │
│ 1616271686.964:10          │
└────────────────────────────┘
sqlite>

(8) By Stephan Beal (stephan) on 2021-03-20 21:10:44 in reply to 7 [link] [source]

The current trunk version will only execute such functions once. Notice the ONCE block around lines 11-13:

Great, thank you :). Poking around in sqlite3.c:

**   DFUNCTION(zName, nArg, iArg, bNC, xFunc)
**     Like FUNCTION except it omits the SQLITE_FUNC_CONSTANT flag and
**     adds the SQLITE_FUNC_SLOCHNG flag.  Used for date & time functions
**     and functions like sqlite_version() that can change, but not during
**     a single query. ...

That exactly describes the effect i'm looking for.

It seems that SLOCHNG is only the private/internal API, though. Oh, well. No biggie.

(4) By Keith Medcalf (kmedcalf) on 2021-03-20 19:33:11 in reply to 1 [link] [source]

So, in you case, my_app_dir() is a CONSTANT SLOCHNG function. It is stable for the duration of a statement but is not stable for the duration of forever as would be implied by the DETERMINISTIC flag, and therefore cannot be used in an index, for example.

(5) By Keith Medcalf (kmedcalf) on 2021-03-20 19:57:03 in reply to 4 [link] [source]

With the recent changes for Pure Functions, I am not sure exactly what SLOCHNG implies.

(9) By Simon Slavin (slavin) on 2021-03-21 02:04:49 in reply to 4 [link] [source]

In that situation, you should not mark this function as deterministic. SQLite cannot take advantage of the fact that it doesn't change within one statement. Sorry about that.

(10) By Keith Medcalf (kmedcalf) on 2021-03-21 03:00:31 in reply to 9 [link] [source]

Perhaps all that is required is exposure of the sqlite3NotPureFunc internal function as a user API so that UDFs can be marked as deterministic yet flag impure usage ...

(11) By Simon Slavin (slavin) on 2021-03-21 13:28:17 in reply to 10 [link] [source]

An alternative would be to add an extra parameter to the function which changes with each statement. A function is deterministic (by SQLite standards)if it returns the same result given the same parameters. If every statement uses a new value (random number ?) for one of the parameters that lets SQLite take care of matching the parameters.