Re: [sqlite] racing with date('now') (was: Select with dates)

On Fri, Sep 20, 2013 at 9:23 AM, Keith Medcalf wrote: > > 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

Re: [sqlite] racing with date('now') (was: Select with dates)

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

Re: [sqlite] racing with date('now') (was: Select with dates)

On 17 Sep 2013, at 12:32am, James K. Lowden wrote: > I suggest that when the observed behavior is known to be at variance > with what is specified in the SQL standard, it should be viewed as a > defect and as a candidate for revision. If it also fails on > theoretical

Re: [sqlite] racing with date('now') (was: Select with dates)

On Mon, 16 Sep 2013 10:38:03 -0400 Richard Hipp wrote: > 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

Re: [sqlite] racing with date('now') (was: Select with dates)

On Sat, 14 Sep 2013 17:19:22 +0400 Yuriy Kaminskiy wrote: > > Notwithstanding the timezone you want to use, > > > > explain select * > > from entry > > where bankdate >= date('now', 'start of month') > >and bankdate < date('now', 'start of

Re: [sqlite] racing with date('now') (was: Select with dates)

There are other ways to get a consistent 'now' across an entire SQL statement: Of course! This issue is sufficiently obscure that it is not worth adding (and testing and documenting and supporting forever) yet another pragma. I personally wouldn't call it so "obscure" as it pops up

Re: [sqlite] racing with date('now') (was: Select with dates)

On Mon, Sep 16, 2013 at 12:37 PM, Jean-Christophe Deschamps < j...@antichoc.net> wrote: > I personally wouldn't call it so "obscure" as it pops up regularly with > untold consequences in a scripting language I use, but it's part of the > programmer's responsability after all. Not a deal breaker

Re: [sqlite] racing with date('now') (was: Select with dates)

On Mon, Sep 16, 2013 at 11:14 AM, Jean-Christophe Deschamps < j...@antichoc.net> wrote: > Perhaps a new connection-wide "pragma freeze_now=ON;" which would freeze > 'now' at the beginning of the next statement and unfreeze it at the next > auto-commit or when processing a "pragma freeze_now=OFF;"

Re: [sqlite] racing with date('now') (was: Select with dates)

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()

Re: [sqlite] racing with date('now') (was: Select with dates)

On Mon, Sep 16, 2013 at 10:21 AM, Ryan Johnson 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

Re: [sqlite] racing with date('now') (was: Select with dates)

On 15/09/2013 2:23 PM, Yuriy Kaminskiy wrote: Stephan Beal wrote: On Sun, Sep 15, 2013 at 1:58 PM, Yuriy Kaminskiy wrote: Sure, there can be several way to interpret CURRENT_* and *('now'). However, some of them can be useful (transaction, statement), and others (step) -

Re: [sqlite] racing with date('now') (was: Select with dates)

On 15/09/2013 3:36 AM, Petite Abeille wrote: On Sep 15, 2013, at 12:53 AM, Kees Nuyt wrote: 3) If an SQL-statement generally contains more than one reference to one or more s, then all such ref- erences are effectively evaluated simultaneously. FWIW, Oracle concurs:

Re: [sqlite] racing with date('now') (was: Select with dates)

On Sep 15, 2013, at 10:32 PM, "Keith Medcalf" wrote: > On the other hand, if one knows that the value of 'now' is not stable then > one can always bind a parameter with the appropriate value set from the host > language Or write it down somewhere once (i.e temp table),

Re: [sqlite] racing with date('now') (was: Select with dates)

You are correct. Even though the standard says 'statement stability', I think that is less useful than transaction stability. I personally think a reference to 'now' should be stable throughout a transaction (a static value set when 'now' first accessed in a transaction and cleared on a

Re: [sqlite] racing with date('now') (was: Select with dates)

Keith Medcalf wrote: >> In C there are local variables, where you can save result of impure >> functions when it is important. There are no local variables in SQL >> - with even more extreme example shown in E.Pasma message nearby - >> `SELECT strftime('%f') AS q FROM t WHERE q <> q`; >> oh, by

Re: [sqlite] racing with date('now') (was: Select with dates)

> In C there are local variables, where you can save result of impure > functions when it is important. There are no local variables in SQL > - with even more extreme example shown in E.Pasma message nearby - > `SELECT strftime('%f') AS q FROM t WHERE q <> q`; > oh, by the way, `SELECT

Re: [sqlite] racing with date('now') (was: Select with dates)

Stephan Beal wrote: > On Sun, Sep 15, 2013 at 1:58 PM, Yuriy Kaminskiy wrote: > >> Sure, there can be several way to interpret CURRENT_* and *('now'). >> However, >> some of them can be useful (transaction, statement), and others (step) - >> cannot >> be. And some

Re: [sqlite] racing with date('now') (was: Select with dates)

On 15 Sep 2013, at 12:58pm, Yuriy Kaminskiy wrote: > Wow. Are you *REALLY* arguing that > SELECT * FROM t WHERE CURRENT_TIME <> CURRENT_TIME; > that randomly (!) returning rows any less broken than > SELECT * FROM t WHERE 2*2 <> 4; > also randomly returning rows? I was,

Re: [sqlite] racing with date('now') (was: Select with dates)

Op 15 sep 2013, om 14:05 heeft Stephan Beal het volgende geschreven: On Sun, Sep 15, 2013 at 1:58 PM, Yuriy Kaminskiy wrote: Sure, there can be several way to interpret CURRENT_* and *('now'). However, some of them can be useful (transaction, statement), and others

Re: [sqlite] racing with date('now') (was: Select with dates)

On Sun, Sep 15, 2013 at 1:58 PM, Yuriy Kaminskiy wrote: > Sure, there can be several way to interpret CURRENT_* and *('now'). > However, > some of them can be useful (transaction, statement), and others (step) - > cannot > be. And some (sub-expression, the way it "works"

Re: [sqlite] racing with date('now') (was: Select with dates)

Simon Slavin wrote: > On 14 Sep 2013, at 10:41pm, Yuriy Kaminskiy wrote: > >> ... and I'd call even that difference between CURRENT_* and *('now') rather >> "query optimizer artifact" rather than "documented feature one can rely >> upon". >> Anyway, one way or other, it is

Re: [sqlite] racing with date('now') (was: Select with dates)

On Sep 15, 2013, at 12:53 AM, Kees Nuyt wrote: > 3) If an SQL-statement generally contains more than one reference > to one or more s, then all such ref- > erences are effectively evaluated simultaneously. FWIW, Oracle concurs: "All of the datetime functions that return

Re: [sqlite] racing with date('now') (was: Select with dates)

On Sat, 14 Sep 2013 21:56:23 +0400, Yuriy Kaminskiy wrote: > >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,

Re: [sqlite] racing with date('now') (was: Select with dates)

On 14 Sep 2013, at 10:58pm, Simon Slavin wrote: > On 14 Sep 2013, at 10:41pm, Yuriy Kaminskiy wrote: > >> ... and I'd call even that difference between CURRENT_* and *('now') rather >> "query optimizer artifact" rather than "documented feature one can

Re: [sqlite] racing with date('now') (was: Select with dates)

On 14 Sep 2013, at 10:41pm, Yuriy Kaminskiy wrote: > ... and I'd call even that difference between CURRENT_* and *('now') rather > "query optimizer artifact" rather than "documented feature one can rely upon". > Anyway, one way or other, it is BROKEN. I would agree with you

Re: [sqlite] racing with date('now') (was: Select with dates)

Yuriy Kaminskiy wrote: > Keith Medcalf wrote: > 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]

Re: [sqlite] racing with date('now') (was: Select with dates)

Keith Medcalf wrote: 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

Re: [sqlite] racing with date('now') (was: Select with dates)

> >> 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'

Re: [sqlite] racing with date('now') (was: Select with dates)

Keith Medcalf wrote: >> On Saturday, 14 September, 2013 07:19, Yuriy Kaminskiy said: >> Keith Medcalf wrote: On Thu, 12 Sep 2013 14:01:04 +0100 Simon Davies wrote: > Why not > SELECT * FROM "entry" WHERE >bankdate >=

Re: [sqlite] racing with date('now') (was: Select with dates)

> On Saturday, 14 September, 2013 07:19, Yuriy Kaminskiy said: > Keith Medcalf wrote: > >> On Thu, 12 Sep 2013 14:01:04 +0100 > >> Simon Davies wrote: > >> > >>> Why not > >>> SELECT * FROM "entry" WHERE > >>>bankdate >= date('now','start of month')

Re: [sqlite] racing with date('now') (was: Select with dates)

On Sat, Sep 14, 2013 at 3:19 PM, Yuriy Kaminskiy wrote: > 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

[sqlite] racing with date('now') (was: Select with dates)

Keith Medcalf wrote: >> On Thu, 12 Sep 2013 14:01:04 +0100 >> Simon Davies wrote: >> >>> Why not >>> SELECT * FROM "entry" WHERE >>>bankdate >= date('now','start of month') >>> AND bankdate < date('now','start of month','+1 month') >> The