> 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: 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 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. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users