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

StackExchange Database Administrators Q#4957, answer score: 6