I have table of structure data in SQL Server. I want to travese the tree and find all nodes with a recursive query.

Additionally, the structure data is versioned. I want to always use the latest version, and ignore other branches. To do this, I need to use a sub-query with an aggregate function like MAX(version), or a cross apply with TOP 1 version. But these are not allowed in recursive queries, and give the errors

GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression

and

The TOP or OFFSET operator is not allowed in the recursive part of a recursive common table expression

However, SQL Server accepts and correctly handles the query, MAX or TOP included, if I add a UNION ALL to an empty query afterwards.

SQL Fiddle

-- INIT database
CREATE TABLE Structure (
  Parent CHAR(20),
  Version int,
  Child CHAR(20),
);

INSERT INTO Structure VALUES 
('Q', 1, 'A'), 
('A', 1, 'B'), 
('A', 2, 'B'), 
('A', 3, 'C'),
('C', 1, 'D'),
('C', 2, 'E');


-- QUERY database

WITH my_structure(root, level, path, article) AS (
    select *
    from (SELECT root = Parent, level = 0, cast('root' as varchar(max)) as path, Parent
                FROM Structure
                where Parent = 'Q' and version = 1
        ) a 
        UNION ALL
    select o.root, level+1, cast(concat(o.path, '/', e.Child) as varchar(max)), e.Child
    from (
      SELECT Parent, Version, Child FROM Structure a
        where a.Version = (
            select max(aa.version) from Structure aa 
            where aa.Parent = a.Parent)

      /* Comment out the following three rows and the query will no longer be considered valid */
            union all
      select Parent, Version, Child FROM Structure a
      where 1 = 0
      /*                                                               */
    
    ) e
    INNER JOIN my_structure o 
    ON o.article = e.Parent
)
SELECT *
FROM my_structure a

I have tried to understand what is happening here without success.

• Why does SQL Server not allow to use an aggregate function in a subquery in the recursive common table expression, when clearly, it works to do so?

• Why does UNION-ing on a table without any rows change if the statement is a valid SQL query or not? The aggregate function is still there.

• Is this UNION ALL workaround generally known? I discovered it by accident.