> 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

Reply via email to