I am looking into the SQL standard to try to determine precisely how the
CTE feature should behave.
Taking a simple case like:
with recursive foo(i) as (values(1) union all select i+1 from foo where i < 5) select * from foo;
And looking at the SQL standard 200n 7.13: General Rules: 2.c, it
provides an algorithm for evaluating the recursive query.
In this algorithm, AQEk is a <query expression>. Syntactically, I only
see two <query expression>s, and one is the entire query. The other is:
"(values(1) union all select i+1 from foo where i < 5)", so I'll assume
that AQEk must be equal to that*.
The confusing thing to me is step 2.c.ix.3.B. If the query expression
AQEk is equal to the WQEk, step 2.c.ix.3.B will always set the working
table WTk to some kind of non-empty value, because the "values(1) union
all..." will always return at least one row. This will then cause it to
loop forever.
Where am I going wrong?
Also, 2.c.ii says "If AQEk is immediately contained in some WQEi...". In
the 200n standard, it appears that it's impossible for a <query
expression> to immediately contain another <query expression>. In the
2003 standard it can, but they added another level of indirection in the
200n standard by using an intervening <table subquery>. I'm not an
authority, but I believe this is a mistake.
Regards,Jeff Davis
* Having AQEk = WQEi disturbs me, too, because in the "Framework" part
of the standard, section 6.3.3.1, the definition of contains does not
seem to allow for them to be equal.
