Igor Tandetnik wrote:
>
> It looks like random() is run twice for each row - once in WHERE clause
> and again in the SELECT clause. This looks like a bug.
>
I agree, this looks like a bug. This is a simpler query that shows the
same problem.
sqlite> create table t (id, a);
sqlite> select a, random() as b from t where b < 1000;
sqlite> explain select a, random() as b from t where b < 1000;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 explain select a, random() as b
from t wh
ere b < 1000; 00
1 Integer 1000 1 0 00
2 Goto 0 14 0 00
3 SetNumColumns 0 2 0 00
4 OpenRead 0 3 0 00
5 Rewind 0 12 0 00
6 Function 0 0 2 random(-1) 00
7 Ge 1 11 2 6a
8 Column 0 1 4 00
9 Function 0 0 5 random(-1) 00
10 ResultRow 4 2 0 00
11 Next 0 6 0 00
12 Close 0 0 0 00
13 Halt 0 0 0 00
14 Transaction 0 0 0 00
15 VerifyCookie 0 2 0 00
16 TableLock 0 3 0 t 00
17 Goto 0 3 0 00
sqlite>
It seems to me that SQLite should be doing a CSE (common subexpression
elimination) optimization anyway. The value of b should be calculated
only once and the result should be saved and reused in the where clause.
It only causes problems with functions that return different results
each time they are called (such as random or time('now')), but it still
inefficient for other functions and expressions. A better example of the
inefficiency is given below where the length function is substituted for
the random function.
sqlite> explain select a, length(a) as b from t where b < 1000;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 explain select a, length(a) as b
from t w
here b < 1000; 00
1 Integer 1000 1 0 00
2 Goto 0 16 0 00
3 SetNumColumns 0 2 0 00
4 OpenRead 0 3 0 00
5 Rewind 0 14 0 00
6 Column 0 1 3 00
7 Function 0 3 2 length(1) 01
8 Ge 1 13 2 6a
9 Column 0 1 5 00
10 SCopy 5 7 0 00
11 Function 0 7 6 length(1) 01
12 ResultRow 5 2 0 00
13 Next 0 6 0 00
14 Close 0 0 0 00
15 Halt 0 0 0 00
16 Transaction 0 0 0 00
17 VerifyCookie 0 2 0 00
18 TableLock 0 3 0 t 00
19 Goto 0 3 0 00
sqlite>
There is no reason to evaluate the length function twice.
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
