Jens Alfke wrote: > can someone please tell me how to hoist/factor out the subexpression manually > then?
Move the subexpression into a subquery, then prevent subquery flattening (http://www.sqlite.org/optoverview.html#flattening) by violating one of the listed constraints. (These rules might change in the future ...) In this case, let's use rule 7: the subquery does not have a FROM clause: SELECT x, x FROM (SELECT slow(10) AS x); Or use rule 13, and put "LIMIT 9223372036854775807" on both queries. > I've tried using a "WITH" clause, but it doesn't help A recursive CTE is never flattened: WITH RECURSIVE cte(x) AS ( SELECT slow(10) UNION ALL -- dummy recursion that must be constructed so -- that it never actually happens: SELECT x FROM cte WHERE x IS NULL ) SELECT x, x FROM cte; (However, if "FROM cte" is used more than once in the query, it will be executed more than once.) Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
