On Tuesday, 29 October, 2019 23:05, Simon Slavin <slav...@bigfraud.org> wrote:
>On 30 Oct 2019, at 3:58am, Keith Medcalf <kmedc...@dessus.com> wrote:
>> Before you change anything, I think that is incorrect for the various
>> datetime functions. I think they SHOULD be permitted in CHECK
>> constraints and in generated always columns, whether those are stored or
>> virtual, whether or not parameters are given since they are constant.
>> They should not be permitted in indexes however unless they are pure
>> (deterministic).
> But can SQLite tell the difference at that stage ? For instance,
>julianday('2019-10-30') is deterministic
> julianday('now') is non-deteerministic
>Using 'now' as an argument gives different results for the same parameter
>values. Worse still, the 'now' may not be explicit, you may have a table
>column with '2019-10-30' in one row and 'now' in another.
This applies to all the datetime functions, of course, not just the julianday
function.
Within the context of a given statement, the value of julianday('now') or
julianday() is a constant and is deterministic (and it remains thus no matter
the additional parameters). However, two statements executed at a different
time will return different values each corresponding to the constant and fully
deterministic value that was in effect at that particular 'now' when the
statement was executed.
>SQLite cannot make the distinction. SQLITE_DETERMINISTIC applies to all
>values of the parameters of a function. And since at least one value can
>yield different results, julianday() has to be marked as non-
>deterministic.
SQLITE_DETERMINISTIC means that the return value is dependent on the value of
its arguments, and only the value of its arguments, and nothing but the value
of its arguments, forever and ever, yesterday, today, and tomorrow. None of
the datetime functions which use a 'now', 'utc', or 'localtime' argument meet
that requirement (or have no arguments).
SQLITE_SLOCHNG|SQLITE_FUNC_CONSTANT means that the return value is as above BUT
ONLY WITHIN THE CURRENT STATEMENT EXECUTING NOW, thus such functions cannot be
used in an index since they *may* be different for each statement execution and
an index persists across statements.
Functions that have neither of the above flags are "volatile" and the results
*may be different each time the function is called, even with the same
arguments*. An example is the random() function. Or the uuid() function.
These cannot be used in index expressions either. Nor in generated columns.
>The standard way to handle this is that instead understanding the string
>'now' you have a function now() marked as non-deterministic. Then
>julianday() can be marked as deterministic. But it's too late for that.
Well, the issue is that one has to look inside the function parameters to
determine whether or not the function is ytuly deterministic, however, no
matter the parameters the datetime functions are all constant within a
statement and the problem only arises if you want to use them in an index
expression.
Personally, I do not see a problem with permitting them to be used anywhere
EXCEPT in an index expression.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
