Nice fix Richard, and it works as you have documented. With the added [cebd6fc551] to reset iCurrentTime when the statement is reset, my testing indicates that if the p->iCurrentTime=0; added at line 570 of vdbe.c by the original patch [daf6ba413c] (change fragment [56e648f5ba9a9181]) is removed, then 'now' is stable for the statement rather than just each step.
That is, 'now' is stable from its first "access" in a statement until that statement is reset. Unless you have found that it causes other issues (I haven't found any yet) then removing the above mentioned clearing of iCurrentTime during each step will result in SQLite's concept of 'now' being consistent with that implemented in other SQL engines and in the standard. Personally, I think that 'now' should be constant for the duration of a transaction across all statements executed within the same transaction -- however that is not what the standard says -- the standard only says that 'now' be stable for the duration of the execution of a single statement which appears to have been met if the aforesaid clearing of iCurrentTime in vdbe.c is removed. > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Richard Hipp > Sent: Monday, 16 September, 2013 08:38 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] racing with date('now') (was: Select with dates) > > On Mon, Sep 16, 2013 at 10:21 AM, Ryan Johnson > <ryan.john...@cs.utoronto.ca>wrote: > > > Rhetorical question: if sqlite3's behavior were tightened up would > anybody > > complain? Is there any possible use case where replacing the current > > random-ish behavior with something consistent would change an > application? > > Seems like the requested behavior happens on accident often enough > that no > > current application could rely on its failure to appear. > > > > There are perhaps 2 million applications in the wild that use SQLite, so > it > will be difficult to check them all. But one can easily imagine that one > or > more of those two million applications does something like this: > > SELECT current_timestamp, * FROM tab; > > And then runs sqlite3_step() every five or ten seconds in a background > process to fetch a new row, and expects the timestamp on each row to > reflect the actual time of retrieval from disk. Causing 'now' to mean > exactly the same time for an entire SQL statement would break such > applications. > > As a compromise, the current SQLite trunk causes 'now' to be exactly the > same for all date and time functions within a single sqlite3_step() > call. > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
