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- >boun...@mailinglists.sqlite.org] 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 ><slav...@bigfraud.org> wrote ---- > > On 18 Feb 2019, at 1:15pm, Arun - Siara Logics (cc) ><a...@siara.cc> 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 > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- >users > > > > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
