On 10/4/16, Keith Medcalf <kmedc...@dessus.com> wrote: > This raises another question. Is there any way to mark a function > in-between volatile and deterministic? Currently if the deterministic flag > is not set the optimizer assumes that the function is truly volatile (it is > called every reference, even for duplicated arguments). What about > functions that are deterministic (enough) for a statement/transaction but > are not deterministic enough to be used in an index? > > An example would be a function which calls the Windows CheckTokenMembership > function. This function returns whether or current process token contains > the provided SID (group/user). This (and other like it) functions are > deterministic enough for the query optimizer to treat as deterministic, but > volatile enough that they should not be used to build anything persistent > (such as in a conditional index). >
Another example is sqlite_version() which is always the same for any database connection, but might change when you upgrade your sqlite3.dll, and so cannot be used in an index expression or in the WHERE clause of a partial index. Such functions are internally identified as "Slow Change" functions using the internal flag SQLITE_FUNC_SLOCHNG. But that flag cannot currently be set using the public API. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
