Re: [sqlite] Question regarding guarantees of SQLITE_DETERMINISTIC

[Stephan Beal]

On Mon, Feb 17, 2014 at 6:23 PM, RSmith  wrote:

> The flag is telling SQLite that your function will behave
> determinsitcally, i.e. it won't change the output for the same inputs
> within a single query.
>

i figured so, just wanted to be sure.


> SQLite then uses this information to "maybe" cache the output and re-use
> it, but there is no guarantee the optimisation is possible within every
> query situation, so it is very much possible SQLite can call your function
> again within the same query, you have to make your function behave
> deterministically if you tell SQLite that it is so - else query results can
> be undefined.
>

But it would be really handy if sqlite3 could somehow guaranty that the
optimization would apply :). (No, i'm not asking for that as a feature - it
doesn't sound feasible to me for all query constructs.)


> The OP seems to have "tested" SQLite's determinism with adding a very
> indeterministic function, so what I was trying to point out is either his
> function isn't behaving deterministically and/or he did not specify the
> flag to let SQLite know -


It was your response to that which lead me to that flag - i wasn't aware of
it before and wanted to double-check before i go applying it to my local
now() impl (which i "now" won't do, of course). But... i can set it on 5 or
6 others which do behave deterministically, so thank you for that :).

but to your question specifically, no the flag does not force determinism
> (AFAICT),


That coincides with Richard's answer (which i consider to be definitive ;).


> it only allows the optimisation


But it "would be cool if..." ;)

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question regarding guarantees of SQLITE_DETERMINISTIC

[RSmith]

On 2014/02/17 18:47, Stephan Beal wrote:

Hi, all,

Regarding SQLITE_DETERMINISTIC:

http://www.sqlite.org/c3ref/create_function.html

does specifying that flag guaranty that sqlite3 will only call my
"deterministic" function one time during any given SQL statement, or must
my function actually guaranty that deterministic behaviour itself?


The flag is telling SQLite that your function will behave determinsitcally, i.e. it won't change the output for the same inputs 
within a single query. SQLite then uses this information to "maybe" cache the output and re-use it, but there is no guarantee the 
optimisation is possible within every query situation, so it is very much possible SQLite can call your function again within the 
same query, you have to make your function behave deterministically if you tell SQLite that it is so - else query results can be 
undefined.


The OP seems to have "tested" SQLite's determinism with adding a very indeterministic function, so what I was trying to point out is 
either his function isn't behaving deterministically and/or he did not specify the flag to let SQLite know - but to your question 
specifically, no the flag does not force determinism (AFAICT), it only allows the optimisation, or more specifically, NOT specifying 
the flag forces SQLIte to call the function every time and not expect determinsitic results (even if your return values are constant).



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question regarding guarantees of SQLITE_DETERMINISTIC

[Richard Hipp]

On Mon, Feb 17, 2014 at 11:47 AM, Stephan Beal wrote:

> Hi, all,
>
> Regarding SQLITE_DETERMINISTIC:
>
> http://www.sqlite.org/c3ref/create_function.html
>
> does specifying that flag guaranty that sqlite3 will only call my
> "deterministic" function one time during any given SQL statement, or must
> my function actually guaranty that deterministic behaviour itself?
>
> The canonical example is a now() function which returns time(NULL) (there
> was a long thread on this topic a few months back).
>
> My concrete questions:
>
> - Does such function need to make the guaranty itself or is marking it as
> SQLITE_DETERMINISTIC enough to keep sqlite from calling it twice in one
> statement?
>

The is a constraint on the function implementation, that allows SQLite to
perform certain optimizations in the generated VDBE code that would
otherwise not be valid.  So the function must guarantee that it will always
return the same values given the same inputs.


>
> - Can "within a single SQL statement" be interpreted as "within the
> lifetime of a given preparation of a given sqlite3_stmt handle" without
> distorting the truth too much, or is there a more complex definition
> involving subselects and such?
>
>
In call to the function within any subquery or trigger must return the same
value if it has the same inputs.

But the return value can change after each sqlite3_reset().

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question regarding guarantees of SQLITE_DETERMINISTIC

[Stephan Beal]

Hi, all,

Regarding SQLITE_DETERMINISTIC:

http://www.sqlite.org/c3ref/create_function.html

does specifying that flag guaranty that sqlite3 will only call my
"deterministic" function one time during any given SQL statement, or must
my function actually guaranty that deterministic behaviour itself?

The canonical example is a now() function which returns time(NULL) (there
was a long thread on this topic a few months back).

My concrete questions:

- Does such function need to make the guaranty itself or is marking it as
SQLITE_DETERMINISTIC enough to keep sqlite from calling it twice in one
statement?

- Can "within a single SQL statement" be interpreted as "within the
lifetime of a given preparation of a given sqlite3_stmt handle" without
distorting the truth too much, or is there a more complex definition
involving subselects and such?


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users