How to force SQLite to materialize a subquery

sqlite

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

Best Answer

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

Related Solutions

SQLite – How to Properly Format SQLite Shell Output

For "human readable" output, you can use column mode, and turn header output on. That will get you something similar to the sqlplus output in your examples:

sqlite> select * from foo;
234|kshitiz|dba.se

sqlite> .mode column
sqlite> select * from foo;
234         kshitiz     dba.se

sqlite> .headers on
sqlite> select * from foo;
bar         baz         baf
----------  ----------  ----------
234         kshitiz     dba.se

Sql-server – Converting outer apply with Correlated subquery in SQL Server to SQLite

SQLite does not support lateral or correlated joins. You can work around that using a join condition. For example:

create table t1 (id int, name text);
create table t2 (id int, t1id int references t1(id), name text);
insert into t1 values (1, 'a'), (2, 'b'), (3, 'c');
insert into t2 values (1, 1, 'a1'), (2, 1, 'a2'), (3, 2, 'b1');

Now we'd like to look up at most one row from t2, sorted by name. Here's one approach that will work in SQLite:

select  *
from    t1
left join
        t2
on      t2.id =
        (
        select  id
        from    t2
        where   t1.id = t2.t1id
        order by
                name desc
        limit   1
        )

Example at SQL Fiddle.

Best Answer

Related Solutions

SQLite – How to Properly Format SQLite Shell Output

Sql-server – Converting outer apply with Correlated subquery in SQL Server to SQLite

Related Question