> >> You can easily reproduce this problem if you switch unit from month to
> >> millisecond, e.g.
> >> SELECT * FROM t WHERE strftime('%f') <> strftime('%f');
> >> will non-deterministically return rows.
> >> IMO, correct [= least surprise] behavior should be "timestamp used for
> >> 'now' should cached on first row step, and reused in all following calls
> >> [until SQLITE_OK/SQLITE_DONE returned]", but fixing that won't be easy :-|.
> > That would require a change to SQLite itself. Without doing that I
> believe this will solve those problems by using only one 'now', and also
> only running each date() call once (even my previous solutions could
> suffer from race conditions since they retrieved 'now' multiple times):
> >
> > sqlite> explain select *
> > ...> from entry,
> > ...> (select bdate, (select date(bdate, '+1 month'))
> as edate
> > ...> From (select date('now', 'localtime', 'start
> of month') as bdate) as ttemp limit 1) as tstamp
> > ...> where bankdate >= bdate
> > ...> and bankdate < edate;
>
> Unfortunately, it is not only extremely inconvenient to rewrite queries
> this
> way, but also this workaround relies on current implementation of query
> optimizer. Next version may decide to e.g. flatten query, and break
> assumption
> that inner date() will be called only once.
>
> > It is a bit complicated though. It would be much simpler to do the
> date calculation in the host language and pass the boundary values as
> parameters to the query.
>
> In other words, "The way it is implemented currently, sqlite DATE()
> function
> considered harmful and should not be used." Huh.
Not at all. They may be quite useful for formatting output. That the function
date('now') returns the date now ought to be expected. If you wanted the date
at the start of the transaction, one ought to reasonably expect to call
date('start of transaction').
CURRENT_TIMESTAMP is a static timestamp, but you still have the same issue if
you wish to eliminate the repetitive calls to the date function. If that
doesn't bother you then you ought to be able to replace 'now' with
current_timestamp.
> And same problem: some next version of query optimizer may decide to
> change the way this query will be executed.
Perhaps, and that is why you have to clearly declare what you want in your
select statement.
> PS from postgresql documentation:
> === cut
> http://www.postgresql.org/docs/9.3/static/functions-
> datetime.html#FUNCTIONS-DATETIME-CURRENT
> ===
> Since these functions return the start time of the current transaction,
> their
> values do not change during the transaction. This is considered a
> feature: the
> intent is to allow a single transaction to have a consistent notion of
> the
> "current" time, so that multiple modifications within the same
> transaction bear
> the same time stamp.
> === cut ===
> *That's* way to go.
Well, that is one way to go. SQLite goes that way too. Instead of asking for
datetime('now') which returns the current 'nowness' value, you can use
CURRENT_TIMESTAMP/CURRENT_TIME/CURRENT_DATE which is a static value probably
representing the first time is was called in the transaction (or perhaps
statement).
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
