CREATE TABLE #NUMS
(
    N BIGINT
 )
 ;

INSERT INTO #NUMS
VALUES
 (3),
 (5),
 (7)
 ;

 

WITH R AS
(
    SELECT N FROM #NUMS
 
    UNION ALL
 
    SELECT N*N AS N FROM R WHERE N*N < 10000000
)
SELECT N FROM R ORDER BY N
;

If R is now only {9, 25, 49}, then we have a mislabeling problem. R is understood to be the union of the anchor member result set and all the subsequent recursive member result sets. Whereas {9, 25, 49} is only a component of R. It is not the full R that we have accrued so far. Therefore, to write the recursive member as selecting from R makes no sense.

************* EDIT *************

WITH R AS
(
    SELECT   N
    INTO     R[0]
    FROM     #NUMS
 
    UNION ALL
 
    SELECT   N*N AS N
    INTO     R[K+1]
    FROM     R[K]
    WHERE    N*N < 10000000
)
SELECT N FROM R ORDER BY N
;

CREATE TABLE #NUMS
(N BIGINT);

INSERT INTO #NUMS
VALUES (3), (5), (7);

WITH R AS
(
    SELECT N FROM #NUMS
    UNION ALL
    SELECT N*N AS N FROM R WHERE N*N < 10000000
)
SELECT N FROM R ORDER BY N;

If R is now only {9, 25, 49}, then we have a mislabeling problem. R is understood to be the union of the anchor member result set and all the subsequent recursive member result sets. Whereas {9, 25, 49} is only a component of R. It is not the full R that we have accrued so far. Therefore, to write the recursive member as selecting from R makes no sense.

WITH R AS
(
    SELECT   N
    INTO     R[0]
    FROM     #NUMS
    UNION ALL
    SELECT   N*N AS N
    INTO     R[K+1]
    FROM     R[K]
    WHERE    N*N < 10000000
)
SELECT N FROM R ORDER BY N;

************* EDIT *************

I certainly appreciate what @Max Vernon and @Michael S. have detailed below. Namely, that (1) all the components are created up to the recursion limit or null set, and then (2) all the components are unioned together. This is how I understand SQL recursion to actually work.

If we were redesigning SQL, maybe we would enforce a more clear and explicit syntax, something like this:

WITH R AS
(
    SELECT   N
    INTO     R[0]
    FROM     #NUMS

    UNION ALL

    SELECT   N*N AS N
    INTO     R[K+1]
    FROM     R[K]
    WHERE    N*N < 10000000
)
SELECT N FROM R ORDER BY N
;

Sort of like an inductive proof in mathematics.

The problem with SQL recursion as it currently stands is that it is written in a confusing way. The way it is written says that each component is formed by selecting from R, but it does not mean the full R that has been (or, appears to have been) constructed so far. It just means the previous component.