I would like to ask get some help about understanding how "with recursive" works. More precisely WHY the anchor query (the non-recursive term) isn't replicated into the sub call of the CTE. I tried my best to understand alone but I'm not sure.
First of all let's take the example of PostgreSQL which is the simplest one I found (make the sum of 1 to 100) :
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100)
SELECT sum(n) FROM t;
My code walkthrough ( I used links below) :
Evaluate the non-recursive term. For UNION [...].
Include all remaining rows in the result of the recursive query, and also place them in a temporary working table.
So long as the working table is not empty, repeat these steps:
Evaluate the recursive term, substituting the current contents of the working table for the recursive self-reference. For UNION [...]. Include all remaining rows in the result of the recursive query, and also place them in a temporary intermediate table.
Replace the contents of the working table with the contents of the intermediate table, then empty the intermediate table."
LVL 0 :
non-recursive part
- CTE : (N) 1
- WORKING TABLE : (N) 1
recursive part
- CTE : (N) 1
- WORKING TABLE : (N) 1
- INTERMEDIATE TABLE (N) 2
(this is the part I mess around I think) - substitution of WORKING TABLE
So the recursive t will use WORKING TABLE to do SELECT n+1 and put the result in INTERMEDIATE TABLE.
UNION ALL
- CTE : (N) 1 2
- WORKING TABLE : (N) 2
- INTERMEDIATE TABLE : CLEANED
Then we go into the next lvl by the call of t right? (because END condition WHERE n < 100 = FALSE)
LVL 1 :
We know coz postgreSQL says it "So long as the working table is not empty, repeat the recursive steps" So it will repeat the step 2. and 3. (if i'm correct) until END condition then do the SUM.
BUT if I just walkthrough the call of the next lvl of t should we not do VALUES(1) first ?
I'm really confused about how it is possible.
Best regards, Falt4rm
See Question&Answers more detail:os