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 commit or rollback [of the outer
transaction only -- not changed on interim savepoint operations]) would be the
most useful and logically consistent implementation.
If transactions are disabled then no static value is saved for the transaction
and the behavior stays as it is.
This would require that the value of 'now' be cached in the transaction
structure I suppose ...
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 (which is what I usually do anyway, even with other SQL
implementations that do claim to have either statement or transaction stable
concepts of 'now' since that is usually what one wants anyway).
> 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 the way, `SELECT CURRENT_TIME AS q FROM t WHERE q <> q`
> >> trigger that bug too, I've just checked (and it took less than 2
> >> seconds to trigger).
> >
> > That is version specific. What version of SQLite are you using? The
> current version optimizes out the clause q <> q thusly:
>
> No, it is NOT version specific.
>
> strftime vs. CURRENT_TIME triggers racing differently (strftime trigger
> in inner
> loop [and return *random* rows, easily triggered in shell with big
> enough
> table], CURRENT_TIME trigger in outer loop [and *randomly* return all
> rows; not
> easy to trigger in shell, but still it is triggered by my perl script
> without
> much problem]), but still triggers it.
>
> (By the way, better optimizer should've moved strftime out of inner loop
> as
> well; and even better optimizer should've eliminated all repeated calls
> for pure
> functions with constant arguments; but that's just "missing optimization
> opportunity/missing feature", not a "real bug"; and even if that
> optimization
> was implemented, it still would not have fixed racing between
> DATE('now',$foo)
> and DATE('now',$bar), as in OP's query).
>
> > sqlite> .explain
> > sqlite> explain select value, current_time as q from x where q <> q
> and value < 1000000000;
> > SELECT item[0] = {0:0}
> > item[1] = FUNCTION:current_time() AS q
> > FROM {0,*} = x
> > WHERE AND(REGISTER(1),LT({0:0},REGISTER(4)))
> > END
> > addr opcode p1 p2 p3 p4 p5 comment
> > ---- ------------- ---- ---- ---- ------------- -- ------------
> -
> > 0 Trace 0 0 0 00
> > 1 Function 0 0 2 current_time(0) 00
> > 2 Function 0 0 3 current_time(0) 00
>
> ...And as you see, current_time function evaluated *many* times (and
> there are
> no caching *inside* of current_time function, in *any* sqlite version).
>
> > 3 Ne 3 1 2 72
> > 4 Integer 1000000000 4 0 00
> > 5 Goto 0 18 0 00
> > 6 VOpen 0 0 0 vtab:6E06F0:50B800 00
> > 7 Copy 4 7 0 00
> > 8 Integer 4 5 0 00
> > 9 Integer 1 6 0 00
> > 10 VFilter 0 16 5 00
> > 11 IfNot 1 15 1 00
> > 12 VColumn 0 0 8 00
> > 13 Function 0 0 9 current_time(0) 00
> > 14 ResultRow 8 2 0 00
> > 15 VNext 0 11 0 00
> > 16 Close 0 0 0 00
> > 17 Halt 0 0 0 00
> > 18 Transaction 0 0 0 00
> > 19 VerifyCookie 0 1 0 00
> > 20 Goto 0 6 0 00
> >
> > So you will either get all rows or none.
>
> And? It should ALWAYS return NONE. It does not.
>
> _______________________________________________
> 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
