How do I force SQLite to materialize a subquery? 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
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