On 2018/07/19 10:23 PM, Lifepillar wrote:
On 19/07/2018 15:53, R Smith wrote:
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.
Thank you for pointing out the obvious that I still managed to miss. :)
I guess my explanation got carried away in why multiple references is a
problem and not so much a sub-query reference. The mechanism of
sub-querying within a recursive query still disallows a second reference
to the recursive set in any way I imagine, and if it is referenced in a
sub-query like that, I imagine it would throw an error if you then also
refer to it in the main FROM (or any other JOIN).
In theory anyway. I have not checked this in Postgres, but I'm willing
to bet this alteration of the same query cannot prepare without error.
It is still perfectly defined, and the join doesn't affect the
calculation or theoretical outcome (it's basically a no-op), but its
mere presence should confuse any recursing algorithm, at least so I
hope, else I'll be eating my words. :)
WITH count_down(v) AS (
SELECT 5
UNION ALL
SELECT cd.v - 1
FROM count_down AS cd
LEFT JOIN (
SELECT a.v FROM count_down AS a
) AS dd ON dd.v = 0
WHERE cd.v > 0
)
SELECT * FROM count_down;
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.
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
