6

Because SQLite sometimes evaluates functions like random() more than once if they appear in a subquery, which may be a bug, eg:

select op, op from (select abs(random())%10 as op from (select 1));
3|1

I need an easy way to force SQLite to materialize the subquery so that random() is only evaluated once

this is a simplified example for demonstration - my real world query does not have from (select 1) but selects from a view

Jack Douglas
  • 39,869
  • 15
  • 101
  • 176

2 Answers2

6

From the SQLite docs (my emphasis):

Subqueries might also need to be materialized when they appear in the FROM clause of a SELECT statement. For example:

SELECT * FROM ex1 JOIN (SELECT b FROM ex2) AS t ON t.b=ex1.a;

Depending on the query, SQLite might need to materialize the (SELECT b FROM ex2) subquery into a temporary table, then perform the join between ex1 and the temporary table. The query optimizer tries to avoid this by "flattening" the query. In the previous example the query can be flattened, and SQLite will automatically transform the query into

SELECT ex1.*, ex2.b FROM ex1 JOIN ex2 ON ex2.b=ex1.a;

More complex queries may or may not be able to employ query flattening to avoid the temporary table. Whether or not the query can be flattened depends on such factors as whether or not the subquery or outer query contain aggregate functions, ORDER BY or GROUP BY clauses, LIMIT clauses, and so forth. The rules for when a query and cannot be flattened are very complex and are beyond the scope of this document.

You may be able to work around the problem with a view, but it seems that in this case at least, adding a dummy union is enough to prevent "flattening":

select op, op
from (select abs(random())%10 as op from (select 1) union all select 1 where 1=2);

6|6
Jack Douglas
  • 39,869
  • 15
  • 101
  • 176
0

To be sure that the result will not change in the future version of sqlite, It will be better not to relay on actual implementation. Try this:

SELECT op, op FROM (select abs(x.r)%10 as op from ((select 1) JOIN (select random() r)) x);
op          op        
----------  ----------
8           8
Petr
  • 101
  • 1