On 19/07/2018 15:53, R Smith wrote:
On 2018/07/19 2:32 PM, Christian Duta wrote:
WITH RECURSIVE
count_down(v) AS (
SELECT 5
UNION ALL
SELECT cd.v - 1
FROM (
SELECT cd.v
FROM count_down AS cd
) AS cd
WHERE cd.v > 0
)
SELECT * FROM count_down;
Error: near line 1: recursive reference in a subquery: count_down
What is the reasoning behind this restriction?
And more importantly: Are there plans to allow recursive references
inside of a subquery in a future release of SQLite?
The problem is that there can be only one "builder" of the recursive set.
In your example above, the full recursive set is not known at the time
of first encountering the sub-query.
i.e. do you expect it to have results on the first iteration?
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 first step is to evaluate the non-recursive
term. In the example above that is 'select 5', which builds a working
table with one record. When the recursive term is evaluated the first
time, the current working table is used. So, to answer your question:
yes, after the first iteration, the result (of the recursive term) is
a table with one record containing the value 4. SQL's recursion is
iteration in disguise.
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.
Life.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
