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

On 28 Aug 2015, at 10:20am, Domingo Alvarez Duarte wrote: > Please remember that the "random" function here is only a detail the real > problem is the "column expression" been reevaluated more than once. Yes. When considering fixes for this problem one needs to also consider

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

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;

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

OK, I am truly grateful for what I have learned in this thread! And I have composed an new, personal, rule: don't use a function in _anything_ other than in the column list portion of a SELECT statement. If necessary, this means I will be using a CREATE TEMPORARY TABLE results type construct when