Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?
On Wednesday, 30 October, 2019 06:41, Dominique Devienne wrote: >On Wed, Oct 30, 2019 at 11:26 AM Richard Hipp wrote: >> On 10/29/19, Keith Medcalf 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). >> i believe the purpose of a CHECK constraint is declare an eternal >> truth about the database content, not merely something that was true >> at the point in time when the content was first inserted. Am I wrong? > The kind of CHECK constraint Keith mentioned would run afoul of > https://www.sqlite.org/pragma.html#pragma_integrity_check so you're > probably right that allowing these non-deterministic function is not > a good idea (on 2nd thought...) --DD Hmmm. Yes, I believe that is correct that check constraint on a table is a truth about the table and should always evaluate to True (or NULL), and should not become False due to the passage of time (or anything else). A tuple that passes a CHECK constraint on INSERT (or UPDATE) should eternally meet that constraint, so allowing a non-deterministic reference in a CHECK constraint could violate this. I do not think that such a restriction would apply to the value of a generated column though UNLESS that column is used in a CHECK constraint or an index, and even then only for a virtual generated column (a stored generated column does not have this problem, however, and does not need to be restricted, since the value can only change when the tuple is inserted or updated). I can see this getting very complicated very quickly. -- 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
Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?
On Wed, Oct 30, 2019 at 11:26 AM Richard Hipp wrote: > On 10/29/19, Keith Medcalf 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). > > i believe the purpose of a CHECK constraint is declare an eternal > truth about the database content, not merely something that was true > at the point in time when the content was first inserted. Am I wrong? > The kind of CHECK constraint Keith mentioned would run afoul of https://www.sqlite.org/pragma.html#pragma_integrity_check so you're probably right that allowing these non-deterministic function is not a good idea (on 2nd thought...) --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?
On Wed, Oct 30, 2019 at 11:26 AM Richard Hipp wrote: > On 10/29/19, Keith Medcalf 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). > > i believe the purpose of a CHECK constraint is declare an eternal > truth about the database content, not merely something that was true > at the point in time when the content was first inserted. Am I wrong? > That makes sense too, indeed. Basically an "invariant" as we often say. Yet I see Keith's use-cases as compelling enough to leave that decision to the user, rather than the implementation making it for us. Or perhaps you think triggers should be used instead, for those use cases? --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?
On 10/29/19, Keith Medcalf 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). i believe the purpose of a CHECK constraint is declare an eternal truth about the database content, not merely something that was true at the point in time when the content was first inserted. Am I wrong? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?
Would any kind soul be willing to explain the deterministic problem to me? I can see that having now() in a virtual column would be a waste of time as the virtual column value would change every time it was read but if it was being stored then why not? Also, I take it deterministic udf’s can be used in virtual columns. If that’s the case what’s to stop you from declaring a udf as deterministic and calling now() inside that? I’m obviously missing something. I’m guessing there must be code in sqlite.c that needs the same value returned at different times. From: sqlite-users on behalf of Dominique Devienne Sent: Wednesday, October 30, 2019 9:21:12 AM To: SQLite mailing list Subject: Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ? On Wed, Oct 30, 2019 at 8:32 AM Keith Medcalf wrote: > On Tuesday, 29 October, 2019 23:05, Simon Slavin > wrote: > >On 30 Oct 2019, at 3:58am, Keith Medcalf 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, > > Personally, I do not see a problem with permitting them to be used > anywhere EXCEPT in an index expression. > +1. I completely agree with Keith, and the use-cases he outlined are great examples of enforcing business logic using CHECK constraints. I especially like the fact one cannot alter these stored generated values, again adding a level of integrity to the DB. --DD ___ 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
Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?
On Wed, Oct 30, 2019 at 8:32 AM Keith Medcalf wrote: > On Tuesday, 29 October, 2019 23:05, Simon Slavin > wrote: > >On 30 Oct 2019, at 3:58am, Keith Medcalf 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, > > Personally, I do not see a problem with permitting them to be used > anywhere EXCEPT in an index expression. > +1. I completely agree with Keith, and the use-cases he outlined are great examples of enforcing business logic using CHECK constraints. I especially like the fact one cannot alter these stored generated values, again adding a level of integrity to the DB. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?
On Tuesday, 29 October, 2019 23:05, Simon Slavin wrote:
>On 30 Oct 2019, at 3:58am, Keith Medcalf 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
Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?
On 30 Oct 2019, at 3:58am, Keith Medcalf 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.
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.
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.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?
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). Your example in the ticket is (almost) perfectly reasonable. If one were to have: create table t (a real check (a < julianday()); ensures that at the time of record insertion or update that the value of "a" is not "in the future". Similarly the construct: create table t(data, updated generated always as (julianday()) stored); ensures that the "updated" column always has the julianday the record was inserted or updated and cannot be changed by the user (ie, you cannot "UPDATE t SET updated=47" or "INSERT INTO t (data, updated) VALUES (1, 45)" as you cannot set/update the values of generated always columns). Similarly, you might define a table thusly: create table t(basedate text, days integer, deadline generated always as (datetime(basedate, '+' || days || ' days')); so that the deadline is always calculated the same way no matter what ... it doesn't really matter if it is stored or virtual. -- 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 On >Behalf Of Richard Hipp >Sent: Tuesday, 29 October, 2019 19:48 >To: SQLite mailing list >Subject: Re: [sqlite] Minor Change Request: CURRENT_* functions also have >SQLITE_FUNC_CONSTANT ? > >On 10/29/19, Keith Medcalf wrote: >> Can the >> SQLITE_FUNC_CONSTANT attribute be added to the CURRENT_* functions >since the >> value is constant during the running of a single statement? > >No. The functions used in generated tables must be pure. They must >always give the same output given the same input. > >datetime() is this way, as long as it's input does not use modifiers >like "now", or "localtime", or "utc". And for that reason, datetime() >is marked as constant. But it throws an error if you use it in a way >that gives a non-deterministic result. > >Or, at least it is suppose to. I just tried it and that mechanism is >not working correctly for generated columns, which is a bug. > >-- >D. Richard Hipp >d...@sqlite.org >___ >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
Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?
On 10/29/19, Richard Hipp wrote: > > Or, at least it is suppose to. I just tried it and that mechanism is > not working correctly for generated columns, which is a bug. > Ticket: https://www.sqlite.org/src/tktview/830277d9db6c3ba10df1c79c6c2be58323553240 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?
On 10/29/19, Keith Medcalf wrote: > Can the > SQLITE_FUNC_CONSTANT attribute be added to the CURRENT_* functions since the > value is constant during the running of a single statement? No. The functions used in generated tables must be pure. They must always give the same output given the same input. datetime() is this way, as long as it's input does not use modifiers like "now", or "localtime", or "utc". And for that reason, datetime() is marked as constant. But it throws an error if you use it in a way that gives a non-deterministic result. Or, at least it is suppose to. I just tried it and that mechanism is not working correctly for generated columns, which is a bug. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?
On 10/29/19, Keith Medcalf wrote:
>>sqlite3
> SQLite version 3.31.0 2019-10-29 16:18:45
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table x(data, createdon default (current_timestamp),
> updatedon as (current_timestamp) stored);
> Error: non-deterministic functions prohibited in generated columns
> sqlite> create table x(data, createdon default (current_timestamp),
> updatedon as (datetime()) stored);
> sqlite> insert into x (data) values ('data 1');
> sqlite> select * from x;
> data 1|2019-10-30 00:45:49|2019-10-30 00:45:49
> sqlite> update x set data='data 2' where data='data 1';
> sqlite> select * from x;
> data 2|2019-10-30 00:45:49|2019-10-30 00:46:32
>
> The datetime() function has SQLITE_SLOCHNG and SQLITE_FUNC_CONSTANT, but the
> CURRENT_TIMESTAMP/CURRENT_TIME/CURRENT_DATE only have SQLITE_SLOCHNG.
>
> This means that you can use DATETIME() in a generate always ... stored but
> not CURRENT_TIMESTAMP, even though both produce the same result. Can the
> SQLITE_FUNC_CONSTANT attribute be added to the CURRENT_* functions since the
> value is constant during the running of a single statement?
>
> --
> 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
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
