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 <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Richard Hipp >Sent: Tuesday, 29 October, 2019 19:48 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Subject: Re: [sqlite] Minor Change Request: CURRENT_* functions also have >SQLITE_FUNC_CONSTANT ? > >On 10/29/19, Keith Medcalf <kmedc...@dessus.com> 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
