H2: Hierarchy recursive CTE SOURCE subquery missing sort column from order by on projection

[jasper07]

H2: order by siblingRank on hierarchy projection fails, regression in cds.services 4.8.0

Description

The GenreHierarchy projection uses order by siblingRank:

entity GenreHierarchy as projection on my.Genres
    excluding {children} order by siblingRank

This fails with Column "MY.SIBLINGRANK" not found when upgrading from cds.services 4.4.2 to 4.8.0.

We hit this issue in our own project using @hierarchy : parent with order by siblingRank on cds.services 4.8.0 and replicated it here on the main branch.

The generated recursive CTE SOURCE subquery only selects ID, NAME, and PARENT_ID but does not include SIBLINGRANK. The sort_path calculation then references my."SIBLINGRANK" which is not in scope:

WITH SOURCE AS (
  SELECT T0."ID" as "NODE_ID", T0."NAME", T0."ID", T0."PARENT_ID"
  FROM "LOCALIZED_ADMINSERVICE_GENREHIERARCHY" T0
)
SELECT
  ...
  array [row_number() over(order by my."SIBLINGRANK" NULLS FIRST, my."NODE_ID" NULLS FIRST)] AS sort_path
FROM SOURCE my
WHERE parent_id is NULL

Steps to reproduce

1. Clone cloud-cap-samples-java (main branch, cds.services.version 4.8.0)
2. Run with mvn spring-boot:run
3. Browse genres in the admin UI

Expected

Works as it does on cds.services 4.4.2, siblings sorted by siblingRank.

Actual

Column "MY.SIBLINGRANK" not found [42122-232]

Versions

• Works: cds.services.version 4.4.2, Java 25 (SapMachine)
• Broken: cds.services.version 4.8.0, Java 25 (SapMachine)
• Not tested with Java 21
• H2 database

Stack trace

Full error

com.sap.cds.CdsDataStoreException: SQL: WITH ROOT_HIERARCHY AS (SELECT * FROM (
  WITH RECURSIVE REC_ROOT_HIERARCHY (
    "NODE_ID", "NAME", "ID", "PARENT_ID",
    hierarchy_level, hierarchy_path, is_leaf, sort_path
  ) AS (
    WITH SOURCE AS (
      SELECT T0."ID" as "NODE_ID", T0."NAME", T0."ID", T0."PARENT_ID"
      FROM "LOCALIZED_ADMINSERVICE_GENREHIERARCHY" T0
    )
    SELECT
      my."NODE_ID", my."NAME", my."ID", my."PARENT_ID",
      1 AS hierarchy_level,
      array [node_id] AS hierarchy_path,
      NOT EXISTS (SELECT 1 FROM SOURCE sub WHERE sub.parent_id = my.node_id) AS is_leaf,
      array [row_number() over(order by my."SIBLINGRANK" NULLS FIRST, my."NODE_ID" NULLS FIRST)] AS sort_path
    FROM SOURCE my
    WHERE parent_id is NULL
    UNION ALL
    SELECT
      my."NODE_ID", my."NAME", my."ID", my."PARENT_ID",
      up.hierarchy_level + 1 as hierarchy_level,
      up.hierarchy_path || my.node_id AS hierarchy_path,
      NOT EXISTS (SELECT 1 FROM SOURCE sub WHERE sub.parent_id = my.node_id) AS is_leaf,
      up.sort_path || row_number() over(order by my."SIBLINGRANK" NULLS FIRST, my."NODE_ID" NULLS FIRST) as sort_path
    FROM SOURCE my JOIN REC_ROOT_HIERARCHY up ON my.parent_id = up.node_id
  )
  SELECT * FROM REC_ROOT_HIERARCHY
) SQ ...

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "MY.SIBLINGRANK" not found [42122-232]