Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.