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