Just to nitpick : SQLite version 3.16.0 2016-11-04 19:09:39 Enter ".help" for usage hints. Connected to a transient in-memory database.
sqlite> with recursive count_down(v) as ( ...> select 5 ...> union all ...> select n - 1 from count_down where n > 0 ...> ) ...> select * from count_down; Error: no such column: n should have been (replacing 'n' with 'v'): sqlite> with recursive count_down(v) as (select 5 union all select v - 1 from count_down where v > 0) ...> select * from count_down; 5 4 3 2 1 0 John Gillespie On 20 July 2018 at 14:14, Christian Duta <christian.d...@uni-tuebingen.de> wrote: > R Smith-2 wrote > > The query above is perfectly defined. In fact, it works in PostgreSQL. > > PostgreSQL's manual also has a very nice explanation of how recursive > > queries are evaluated. > > The way PostgreSQL handles recursive queries was one of my motivations to > post about this. > PostgreSQL allows for recursive references inside subqueries like the one I > posted. > > > R Smith-2 wrote > >> That said, the query above can be simplified as follows: > >> > >> with recursive count_down(v) as ( > >> select 5 > >> union all > >> select n - 1 from count_down where n > 0 > >> ) > >> select * from count_down; > >> > >> which is what the OP has likely done. > > > > I think the OP tried something a bit more complex, and then tried to > > reduce it to a simple example to post here, perhaps deceptively simple. > > However, it's still possible that his actual complex query might be > > refined into such a simpler form. > > You're right, the queries I have in mind are complex. And readability would > greatly improve > with a feature like recursive references inside subqueries. > Recursive queries are often difficult to read anyway, so having a feature > which improves > readability is a *big* plus in my book. > > But: My questions have more of a technical background than a practical one. > I try to figure out *why* SQLite decided to forbid a recursive reference be > placed inside a subquery. > Is there a technical reason? Or did the developer explicitly decide against > recursive references inside > subqueries because of the SQL standard? > > And of course: are there plans to allow for recursive references inside > subqueries? > > Best regards, > Christian Duta > > > > -- > Sent from: http://sqlite.1065341.n5.nabble.com/ > _______________________________________________ > 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
