[sqlite] WHERE clause not working in combination with random()

[Domingo Alvarez Duarte]

Now that we have solved the expression column reevaluation problem on the
"ORDER BY" clause what about the same problem on the "WHERE" clause ?  

CREATE TABLE myTable (a INTEGER);
INSERT INTO myTable VALUES (1),(2),(3),(4),(5);
CREATE VIEW myView AS SELECT a,random()%100 AS rr FROM myTable;
SELECT a, rr FROM myView WHERE rr < 30 ORDER BY rr;  

Some outputs of the above through sqlite3 "sqlite3 < test-where-random.sql": 


=====  

2|-86
4|-60
1|59
====  

5|5
3|66  

====  

5|-83
4|30
1|64
====  

Please remember that the "random" function here is only a detail the real
problem is the "column expression" been reevaluated more than once.  

Cheers !  

?  

?