Hierarchical query is loosing context

[floliver]

Summary

When reading from a hierarchy entity (recurse) with a WHERE condition (e.g. exists(SELECT ... WHERE $m.someColumn = ?)), the filter is used only to define the seed set for the recursive traversal. It is not applied again to the final result. Ancestor (or descendant) rows are taken from the full table, so the result can contain rows that do not satisfy the original WHERE (e.g. different someColumn). Users expect the result set to be restricted to the same filter (e.g. only rows for one header ID).

---

Environment

• Package: @cap-js/db-service
• File: lib/cqn2sql.js
• Behaviour: SELECT with recurse and where (e.g. exists(...))
• Versions :
  @sap/cds: 9.7.0
  @sap/cds-dk: 9.7.0
  @sap/cds-common-content: 3.1.0
  @sap/cds-compiler: 6.4.2
  @sap/cds-fiori: 2.1.0
  @sap/cds-mtxs: 3.7.0
  @cap-js/asyncapi: 1.0.3
  @cap-js/cds-test: 0.4.1
  @cap-js/cds-typer: 0.38.0
  @cap-js/cds-types: 0.15.0
  @cap-js/db-service: 2.8.2
  @cap-js/hana: 2.6.0
  @cap-js/openapi: 1.3.1
  @cap-js/sqlite: 2.1.3
  @sap/eslint-plugin-cds: 4.1.1
  Node.js: v22.17.1

---

Expected behaviour

• The WHERE condition (e.g. mrpCalculationHeader_ID = <guid>) is applied both to:
  1. Define which nodes are the “start” set for the hierarchy (current behaviour), and
  2. Filter the final result so that only rows satisfying that condition are returned (e.g. exclude ancestors that belong to another header).

So the API returns only hierarchy rows that match the user’s filter (e.g. same header ID).

---

Actual behaviour

• The WHERE is used only for (1). The generated SQL:
  • Builds H0, H1, H2, H3 from the full hierarchy table (no WHERE).
  • Uses the WHERE only in the H4 anchor (e.g. cutListPosition_ID_ID IN (SELECT ... WHERE EXISTS(..., mrpCalculationHeader_ID = ?))).
  • H4’s recursive part adds all parents from H3, so ancestors from other headers are included.
• The main SELECT has no WHERE (see cqn2sql.js line 267: if (!recurse && !_empty(where)) — when recurse is true, WHERE is never appended).
• Result: the response can contain rows with a different mrpCalculationHeader_ID (or other filtered column), so the filter appears “lost” on the result set.

---

Reproduction context

CQN (simplified):

{
  "limit": { "offset": { "val": 0 }, "rows": { "val": 210 } },
  "from": { "ref": ["msgfood.disassembly.ManageMRPCalculationsAppService.MRPCalculationPositionHierarchies"], "as": "$m" },
  "where": [
    "exists",
    {
      "SELECT": {
        "from": { "ref": ["localized.msgfood.disassembly.ManageMRPCalculationsAppService.MRPCalculationHeaders"], "as": "$M2" },
        "columns": [{ "val": 1 }],
        "where": [
          { "ref": ["$M2", "ID"] }, "=", { "ref": ["$m", "mrpCalculationHeader_ID"] },
          "and",
          { "ref": ["$M2", "ID"] }, "=", { "val": "a5608fd3-3799-429f-8d86-1f3ebaf33816" }
        ]
      }
    }
  ],
  "columns": [
    { "ref": ["$m", "DrillState"] },
    { "ref": ["$m", "calculatedQuantity"] },
    { "ref": ["$m", "mrpCalculationHeader_ID"] },
    ...
  ]
}

Intent: Only hierarchy rows where mrpCalculationHeader_ID = 'a5608fd3-3799-429f-8d86-1f3ebaf33816'.

What happens: The generated SQL uses this filter only in the recursive CTE’s “start” condition (H4 anchor). The final result set is not filtered by mrpCalculationHeader_ID, so ancestors from other headers can appear. The corresponding request : MRPCalculationHeaders(ID=1a5608fd3-3799-429f-8d86-1f3ebaf33816,IsActiveEntity=true)/mrpCalculationPositionHierarchies?$apply=com.sap.vocabularies.Hierarchy.v1.TopLevels(HierarchyNodes=$root/MRPCalculationHeaders(ID=a5608fd3-3799-429f-8d86-1f3ebaf33816,IsActiveEntity=true)/mrpCalculationPositionHierarchies,HierarchyQualifier=%27MRPCalculationPositionHierarchies%27,NodeProperty=%27cutListPosition_ID_ID%27,Levels=1)&$select=DrillState,calculatedQuantity,cutListPosition_ID_ID,cutListPosition_type,mrpCalculationHeader_ID,overrideSurplusPercentage&$expand=cutListPosition($select=ID;$expand=cutHeader($select=ID,cutIdentifier,description),cutPosition($select=ID,material_Product;))&$count=true&$skip=0&$top=210

Schema context (CDS entities)

Below are the relevant CDS entities so the team has an idea of the DB design. The hierarchy is built over CutListPositions (parent/children); MRPCalculationPositions link a CutListPosition to an MRPCalculationHeader. The hierarchy view MRPCalculationPositionHierarchies projects MRPCalculationPositions with the cut-list structure and is filtered by mrpCalculationHeader_ID.

This issue can happen when we are reusing a Tree as template for a following object. The following entities design would lead to this issue.

DB schema (db/schema.cds):

entity CutListHeaders : cuid, managed {
    identifier                   : String                         @mandatory;
    description                  : localized String               @required: true;
    validFrom                    : Date default $now;
    validTo                      : Date default '9999-12-31';
    material                     : Association to one Products    @mandatory;
    plantGroup                   : Association to one PlantGroups @mandatory;
    calculationQuantity          : types.PositiveUnsignedDecimalQuantity;
    cutListPositions             : Composition of many CutListPositions
                                       on cutListPositions.cutListHeader = $self;
    // ... other associations
}

entity CutListPositions : cuid, managed {
    cutListHeader  : Association to one CutListHeaders @mandatory;
    parent         : Association to one CutListPositions;
    children       : Composition of many CutListPositions
                             on children.parent = $self;
    cutHeader      : Association to one CutHeaderVersions;
    cutPosition    : Association to one CutPositions;
    percentage     : types.PositiveUnsignedDecimalQuantity;
    surplusPercentage : types.UnsignedDecimalQuantity @Measures.Unit: '%';
    type           : String(1) enum { CutHeader = 'H'; CutPosition = 'P'; };
    mrpCalculationPositions : Association to many MRPCalculationPositions
                                   on mrpCalculationPositions.cutListPosition = $self;
    // ... virtuals
}

entity MRPCalculationHeaders : cuid, managed {
    description                    : localized String;
    cutListHeader                  : Association to one CutListHeaders @mandatory;
    plant                          : Association to one Plants         @mandatory;
    mrpCalculationPositions        : Composition of many MRPCalculationPositions
                                        on mrpCalculationPositions.mrpCalculationHeader = $self;
    mrpCalculationMaterials        : Composition of many MRPCalculationMaterials ...;
    startDate                      : Date default $now                 @mandatory;
    endDate                        : Date default $now                 @mandatory;
}

@assert.unique: { constraint: [cutListPosition, mrpCalculationHeader] }
entity MRPCalculationPositions : cuid, managed {
    cutListPosition               : Association to one CutListPositions;
    mrpCalculationHeader          : Association to one MRPCalculationHeaders;
    overrideCutQuantity           : types.UnsignedDecimalQuantity ...;
    calculatedQuantity           : types.UnsignedDecimalQuantity ...;
    overrideCutMarkedAsDeleted    : Boolean default false;
    overrideSurplusPercentage     : types.UnsignedDecimalQuantity @Measures.Unit: '%';
    // ... virtuals
}

---

Root cause (short)

1. No top-level WHERE when recurse is true
   In cqn2sql.js (around line 267):

   if (!recurse && !_empty(where)) sql += ` WHERE ${this.where(where)}`;

   So for hierarchy queries the main SELECT never gets a WHERE.

2. WHERE is rewritten only for START WHERE
   In SELECT_recurse (around 288–305), the user’s WHERE is replaced with
   where = [{ list: keys }, 'in', clone]
   so that the recursive CTE can use “NODE_ID IN (subquery)”. The subquery keeps the original EXISTS, but that is only used to define the seed set, not to filter the output of the hierarchy.

3. Ancestors/descendants are taken from the full table
   The recursive part of the second CTE (H4) joins back to H3, which contains all nodes. So the result set = seed nodes + their ancestors (or descendants), without a second filter on the hierarchy entity (e.g. mrpCalculationHeader_ID).

---

Solution proposal (implemented and tested)

Apply the same filter that defines the start set also to the hierarchy result (the SELECT that wraps the HIERARCHY_* function), so that only rows satisfying the user’s WHERE are returned.

1. Keep the original WHERE before rewriting

Before overwriting where with [keys, 'in', clone], store it in a variable (e.g. originalWhere) so it can be used later to build a result filter.

// In SELECT_recurse, when _target && where:
let originalWhere = null;
if (_target && where) {
	originalWhere = where;
	// ... existing keys + clone logic ...
	where = [{ list: keys }, 'in', clone];
}

2. Extract “result filter” conditions from the original WHERE

Add a helper that, for the common pattern
where = ['exists', { SELECT: { where: inner } }],
parses inner and derives conditions that restrict the hierarchy entity (the “$m” alias), e.g.:

• From $M2.ID = $m.mrpCalculationHeader_ID and $M2.ID = val → derive mrpCalculationHeader_ID = val.
• From direct $m.column = val → use column = val.

Return a CQN-style array suitable for this.where() (e.g. [{ ref: [column] }, '=', val]).

Example implementation (logic only):

• Split inner by 'and' into conjuncts.
• For each conjunct left = right:
  • Build maps: ref → val, ref → ref (for transitivity).
• For each conjunct that references the hierarchy alias (e.g. $m):
  • If it’s $m.col = val, add { ref: [col], '=', val }.
  • If it’s $m.col = $Other.x and $Other.x = val exists, add { ref: [col], '=', val }.
• Return a flat array of conditions (with 'and' between them) for the graph WHERE.

3. Merge the result filter into the graph’s WHERE

• After getStableFrom(from) (so the hierarchy alias is known), call
  resultFilter = originalWhere ? extractHierarchyResultFilter(originalWhere, alias) : [].
• Build the graph’s WHERE as the concatenation of the existing filter (e.g. expandedFilter) and the result filter:

  const graphWhere = [
  	...expandedFilter,
  	...(resultFilter.length ? ['and', ...resultFilter] : []),
  ].filter(Boolean);

• Use graphWhere for the graph SELECT’s where (both the branch that uses source() only and the branch that uses HIERARCHY_ANCESTORS / HIERARCHY_DESCENDANTS).

Effect: the hierarchy result set is restricted to rows that satisfy the original WHERE (e.g. mrpCalculationHeader_ID = ?), so ancestors from other headers are excluded.

4. Code locations in cqn2sql.js

• Store originalWhere: at the start of if (_target && where) (before overwriting where).
• Define extractHierarchyResultFilter(whereArg, fromAlias): e.g. after const alias = stableFrom.as inside SELECT_recurse.
• Compute resultFilter: right after the helper, using originalWhere and alias.
• Build graphWhere and use it: replace the previous use of expandedFilter alone for the graph’s where with graphWhere (for both graph branches).

 const graph = distanceType === 'DistanceFromRoot' && !where
 -- ? { SELECT: { columns: columnsOut, from: source(), where: expandedFilter } }
   ++ ? { SELECT: { columns: columnsOut, from: source(), where: graphWhere.length ? graphWhere : undefined } }

---

## Suggested scope for upstream

- **When:** Only when the user query has a `where` that was rewritten to `[keys, 'in', clone]` (i.e. hierarchy with a WHERE).
- **What:** Parse the **original** WHERE (before that rewrite) and, for the `exists(SELECT ... WHERE ...)` pattern, extract conditions that restrict the hierarchy entity; apply them as an additional WHERE on the **result** of the hierarchy (the graph SELECT).
- **Backward compatibility:** Only adds an extra AND to the graph WHERE when such conditions can be derived; otherwise behaviour unchanged.

---

## Appendix: Full `extractHierarchyResultFilter` implementation

Helper used in the solution to derive result-filter conditions from `exists(SELECT ... WHERE ...)` (e.g. `$m.mrpCalculationHeader_ID = val` from `$M2.ID = $m.mrpCalculationHeader_ID AND $M2.ID = val`). Place after `const alias = stableFrom.as` in `SELECT_recurse`, then call `resultFilter = originalWhere ? extractHierarchyResultFilter(originalWhere, alias) : []`.

```javascript
function extractHierarchyResultFilter(whereArg, fromAlias) {
	if (!whereArg || whereArg[0] !== 'exists' || !whereArg[1]?.SELECT?.where) return [];
	const inner = whereArg[1].SELECT.where;
	const conjuncts = [];
	let i = 0;
	while (i < inner.length) {
		const part = [];
		while (i < inner.length && inner[i] !== 'and') part.push(inner[i++]);
		if (part.length >= 3 && part[1] === '=') conjuncts.push({ left: part[0], right: part[2] });
		if (inner[i] === 'and') i++;
	}
	const refKey = (r) => (r?.ref && Array.isArray(r.ref) ? r.ref.join('.') : null);
	const refToVal = new Map();
	const refToRef = new Map();
	for (const { left, right } of conjuncts) {
		const kL = refKey(left);
		const kR = refKey(right);
		if (kL && right?.val !== undefined) refToVal.set(kL, right);
		if (kR && left?.val !== undefined) refToVal.set(kR, left);
		if (kL && right?.ref) refToRef.set(kL, right);
		if (kR && left?.ref) refToRef.set(kR, left);
	}
	const result = [];
	for (const { left, right } of conjuncts) {
		const r = left?.ref?.[0] === fromAlias ? left : right?.ref?.[0] === fromAlias ? right : null;
		if (!r || r.ref.length < 2) continue;
		const col = r.ref[1];
		let val = refToVal.get(refKey(r));
		if (val === undefined && refToRef.has(refKey(r)))
			val = refToVal.get(refKey(refToRef.get(refKey(r))));
		if (val !== undefined) result.push({ ref: [col] }, '=', val, 'and');
	}
	if (result.length) result.splice(-1, 1); // remove trailing 'and'
	return result;
}

---


---

To be honest, I can’t say for certain that the correction is complete or that nothing is missing. However, I’ve tested my solution and it works as expected in my context.

The file after modifications : 
[cqn2sql.js](https://github.com/user-attachments/files/25071660/cqn2sql.js)