On Monday, 3 October, 2016 12:30, Richard Hipp wrote: > On 10/1/16, Jens Alfke <j...@mooseyard.com> wrote: > > the WHERE clause in a CREATE INDEX statement > > explicitly disallows function calls.... Is this limitation something > > that might be lifted soon > Deterministic SQL functions are now allowed in partial index WHERE > clauses, as of a few minutes ago. The current "Prerelease Snapshot" > (https://www.sqlite.org/download.html) supports this capability. > Please try it out and report any problems. Thanks.
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). _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
