On 14 Sep 2017, at 10:43pm, Nico Williams <n...@cryptonector.com> wrote:
> On Thu, Sep 14, 2017 at 1:10 PM Simon Slavin <slav...@bigfraud.org > <mailto:slav...@bigfraud.org>> wrote: > >> Can you not do it with WITH ? I don’t really understand how WITH works >> but it would seem to evaluate its terms just once for each iteration. > > In PostgreSQL CTEs fiction as an optimizer barrier: the engine always > evaluates each CTE in order and stores their results in temporary tables, > then it runs the main statement with those tables as sources. > > But in SQLite3 CTEs are not materialized first -- they are like VIEWs, and > so they do not function as optimizer barriers, therefore they cannot be > used for CSE. That, and the other answers to my question, clarifies how SQLite works very well. I come from a background where SQL is used only for storage and fast searching. Anything that involves computing is done in software. So I’m not familiar with the functions of SQLite which do heavy processing, like reformatting numbers for printing and CTEs. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
