Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

Keith Medcalf
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

Richard Hipp-3
On 10/29/19, Keith Medcalf <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

Richard Hipp-3
In reply to this post by Keith Medcalf
On 10/29/19, Keith Medcalf <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

Richard Hipp-3
On 10/29/19, Richard Hipp <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

Keith Medcalf
In reply to this post by Richard Hipp-3

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 <[hidden email]> On
>Behalf Of Richard Hipp
>Sent: Tuesday, 29 October, 2019 19:48
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] Minor Change Request: CURRENT_* functions also have
>SQLITE_FUNC_CONSTANT ?
>
>On 10/29/19, Keith Medcalf <[hidden email]> 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
>[hidden email]
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

Simon Slavin-3
On 30 Oct 2019, at 3:58am, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

Keith Medcalf

On Tuesday, 29 October, 2019 23:05, Simon Slavin <[hidden email]> wrote:

>On 30 Oct 2019, at 3:58am, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

Dominique Devienne
On Wed, Oct 30, 2019 at 8:32 AM Keith Medcalf <[hidden email]> wrote:

> On Tuesday, 29 October, 2019 23:05, Simon Slavin <[hidden email]>
> wrote:
> >On 30 Oct 2019, at 3:58am, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

curmudgeon
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 <[hidden email]> on behalf of Dominique Devienne <[hidden email]>
Sent: Wednesday, October 30, 2019 9:21:12 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

On Wed, Oct 30, 2019 at 8:32 AM Keith Medcalf <[hidden email]> wrote:

> On Tuesday, 29 October, 2019 23:05, Simon Slavin <[hidden email]>
> wrote:
> >On 30 Oct 2019, at 3:58am, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

Richard Hipp-3
In reply to this post by Keith Medcalf
On 10/29/19, Keith Medcalf <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

Dominique Devienne
On Wed, Oct 30, 2019 at 11:26 AM Richard Hipp <[hidden email]> wrote:

> On 10/29/19, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

Dominique Devienne
In reply to this post by Richard Hipp-3
On Wed, Oct 30, 2019 at 11:26 AM Richard Hipp <[hidden email]> wrote:

> On 10/29/19, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Minor Change Request: CURRENT_* functions also have SQLITE_FUNC_CONSTANT ?

Keith Medcalf

On Wednesday, 30 October, 2019 06:41, Dominique Devienne <[hidden email]> wrote:

>On Wed, Oct 30, 2019 at 11:26 AM Richard Hipp <[hidden email]> wrote:

>> On 10/29/19, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users