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? Because it
can't, since by the time it is reached (which must be before the first
row from the main select is produced, as is the nature of FROM/JOIN
clauses), there is no set built to choose any values from, and since it
has no values, it will produce no rows, and if it doesn't produce rows,
the entire recursive part of the query doesn't produce rows (since it
selects from the very sub-query in question, which has no rows yet on
the first iteration), and therefore there simply is no row added at all
on the first (or any next) iteration since there never occurs a first row.
And THAT would probably thorroughly confuse even more people than my
explanation of it does.
Or, let's say you envision the sub-query building its own recursive set
before we get to the main query - this seems like a solution, but it
would fill up to infinity because you did not specify a limit or WHERE
clause inside the sub-query.
But, let's assume for a moment you did add a WHERE or LIMIT to avoid
filling the sub-query-recursed-set of rows to infinity, where does the
main recursive set build from?
It clearly has to accommodate all rows added by the sub-query recursive
set, and in your case there is no main recursive set (because you did
not FROM or JOIN the count_down table, which by definition stops this
query from being "recursive").
But again, let's assume for a moment you did add the count_down table to
the FROM/JOIN in the second select (the recursive part) also, then you
have 2 recursive sets being built, one for the sub query, and the
correct main query one. What will happen now is either one of the sets
will not be what you wanted, OR, they will feed each other into an
exponential growing set (depending on the nature of the JOIN you apply).
None of this is what we want from the recursive queries.
I guess it's much like recursion in programming, you have to be very
careful not to run into infinite loops or nonsense results, and for that
you have to have some strict rules. SQL solves this by not allowing a
dream in a dream.
Please note that I've only discussed some of the theory here, but there
might be other unrelated physical design limits in the sqlite code which
may or may not prevent it, and which the devs may or may not overcome
eventually.
That said, if this is something you seriously hope to employ as a
mechanic for finding a more complex result from a recursive query - note
that you can daisy-chain recursive queries together to form nicely
complex results.
This simple example shows the basic idea, but you can also use any join
on them, filter from one another in the recursive parts, etc. There
really is no conceptual limit, so long as any recursive part doesn't
self-reference in a sub-part of it.
-- SQLite version 3.24.0 [ Release: 2018-06-04 ]
WITH a(x) AS (
SELECT 1 UNION ALL SELECT x+2 FROM a WHERE x < 3
), b(fx) AS (
SELECT 1 UNION ALL SELECT fx*x FROM b,a LIMIT 30
)
SELECT * FROM b;
-- fx
-- ---
-- 1
-- 1
-- 3
-- 1
-- 3
-- 3
-- 9
-- 1
-- 3
-- 3
-- 9
-- 3
-- 9
-- 9
-- 27
-- 1
-- 3
-- 3
-- 9
-- 3
-- 9
-- 9
-- 27
-- 3
-- 9
-- 9
-- 27
-- 9
-- 27
-- 27
PS: Apologies, I know the descriptions above lack some simplistic
clarity, but I couldn't find the vocabulary for reducing it to more
succinct, yet well-defined, ideas.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
