Note that really in the latter case the correct attribute is SLO_CHNG which indicates that the function is fully deterministic WITHIN a statement execution but may be volatile BETWEEN statement executions.
The DETERMINISTIC attribute means the opposite of the default volatile. The function will ALWAYS FROM NOW THROUGH THE END OF THE UNIVERSE always and without exception return the same value when presented with the same parameters. SQLite3 really only cares about DETERMINISM within the current statment execution context. Unless of course you use the output in something that persists outside of the "right bloody nowness" of a statement execution -- such as using the function in an index, for example. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >[email protected]] On Behalf Of Keith Medcalf >Sent: Monday, 18 February, 2019 10:08 >To: SQLite mailing list >Subject: Re: [sqlite] Reading a table from inside a scalar function > > >SQLite does not maintain state between VDBE executions ... each >execution is a context onto itself. Nor is maintain state between >separate VDBE executions executing concurrently. That is to say that >the default volatile, SLO_CHNG or DETERMINISTIC attributes apply only >within the execution context of a single statement and not between >statements or serial re-executions of the same statement. > >Ie, if you execute the statement: > >select cosine(34); > >then the results you get is TWO SEPARATE DETERMINISTIC RESULTS. The >fact that the function returned some particular value on some >particular execution of the statement is not maintained between >executions. It is entirely possible that the function "cosine" is >DETERMINISTIC with each single execution context yet returns >different results when the statement is executed twice. > >--- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > > >>-----Original Message----- >>From: sqlite-users [mailto:sqlite-users- >>[email protected]] On Behalf Of Arun - Siara Logics >>(cc) >>Sent: Monday, 18 February, 2019 09:23 >>To: SQLite mailing list >>Subject: Re: [sqlite] Reading a table from inside a scalar function >>Importance: High >> >>Thanks Dominique, Thanks Simon, >>Do you mean to say SQLite might keep function results across >queries? >>My design would be more complicated, but it is something like this: >>If my function uses first part of a text column in the row involved >>and if I make sure all modifications are always appended to the >cell, >>then the function will always return the same value. So it can be >>deterministic and would work even if SQLite caches function results >>across queries. Am I correct? >> >> ---- On Mon, 18 Feb 2019 19:18:21 +0530 Simon Slavin >><[email protected]> wrote ---- >> > On 18 Feb 2019, at 1:15pm, Arun - Siara Logics (cc) >><[email protected]> wrote: >> > >> > > Thank you, for the detailed advice, info and the pointer. Is >>there a faster way to query the table using row id, that is, skip >the >>query parsing and planner? >> > >> > No. For fastest queries, use "WHERE rowid = <whatever>", and >list >>the columns you're interested in specifically. Do not use "SELECT >>*". >> > >> > > I still need the page cache feature and allow for concurrent >>modification of the row involved, while ensuring determinism by >>designing so. I guess sqlite3_exec() would take care of this, but >is >>there a faster way? >> > >> > If you're allowing for the table row you're reading to be >>modified, then how will your function be deterministic ? Would >>changing values in that row not lead to a change in the value >>returned by your function ? If not, why are you looking up the row >? >> > >> > Note that if you mark that function as deterministic, you cannot >>rely on SQLite calling your function at all. SQLite may reason "I >>called that function, with those arguments, a few instructions ago, >>so I already know what the result will be.". >> > >> > Simon. >> > _______________________________________________ >> > sqlite-users mailing list >> > [email protected] >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- >>users >> > >> >> >>_______________________________________________ >>sqlite-users mailing list >>[email protected] >>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > >_______________________________________________ >sqlite-users mailing list >[email protected] >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
