>I was hoping to find a way to force the query planner to evaluate v_random_hierarchy only once.
There is a way with a CTE if the defect I just reported is fixed. Replying to my bug report with your vote to fix the problem can help. I think PostgreSQL, the model for SQLite, has it correct. Once computed, a named CTE table should be constant until the end of the statement compilation boundary. Peter On Thu, Jan 18, 2018 at 9:26 PM, Shane Dev <devshan...@gmail.com> wrote: > On 19 January 2018 at 05:41, petern <peter.nichvolo...@gmail.com> wrote: > > > Were you expecting random() to return the same sequence when the view > > materialized again in the subquery? > > > > I was hoping to find a way to force the query planner to evaluate > v_random_hierarchy > only once. Perhaps this is not possible since it uses the non-deterministic > random() function > > > > Your ultimate query works fine when the random view is materialized once > > into a table. > > > Correct, but I work hoping to avoid the I/O penalty of this strategy when > generating a large hierarchy > > > > > > > FYI. as demonstrated above, to also count the NULL parent, use "IS" instead > > of "=". > > > > > I didn't know IS also worked with integers, good tip. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
