On Sat, Nov 23, 2013 at 06:18:29AM -0500, Richard Hipp wrote:
> On Sat, Nov 23, 2013 at 5:26 AM, Pepijn Van Eeckhoudt <
> pep...@vaneeckhoudt.net> wrote:
>
> > Is datetime special in thuis context or will constant expression hoisting
> > like this happen for any function?
>
> SQLite must know that the function always gives the same output given the
> same inputs. No every function works that way. Counterexamples include
> random() and last_insert_rowid(). But most built-in functions are
> factorable in the same way that datetime() is.
Wait a second... and you'll get different value of datetime('now'). In this
sense datetime() is as deterministic as random(): it may give the same result
next invocation or may not, dependng on various circumstances not related to
the function itself.
> Currently there is no API to designate an application-defined function as
> being "constant" in the sense that it always generates the same output
> given the same inputs. Hence, SQLite assumes the worst about
> application-defined functions and never tries to factor them out of the
> inner loop. Probably we should add a new API that allows the application
> to state auxiliary properties about application-defined functions (such as
> whether or not it is "constant", whether or not it can return NULL, whether
> or not it might change the encoding of its input parameters, etc.) But
> that has not been done yet.
To my opinion, the most general solution is to let to the application
programmer to decide whether to calcucale the function once (say, at
the beginning of a transaction), store the result and then access
the stored value, or to make the new call to the function each
iteration, depending on the application semantics.
Valentin Davydov.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
