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
