[Ticket] QL: Apply Column Fragment Filtering After LIMIT on Every Level

[Martin187187]

User Story

As a BaSyx user/developer,
I want column fragment filtering (via Fragment FieldIdentifier) to be applied after LIMIT on the result set,
so that queries remain fast and predictable even when root-level column filters are used.

---

Problem Description

Currently, when a fragment contains a FieldIdentifier that filters out a specific column, the generated SQL applies this filter before the LIMIT clause.

This leads to:

• Large intermediate result sets
• Inefficient execution plans
• Severe performance degradation, especially when:
  • The fragment filter is applied at the root level
  • The underlying table is large
  • The filter is selective but applied too early

---

Expected Behavior

The query execution order must be:

1. Select root entities
2. Apply ordering (if defined)
3. Apply LIMIT / OFFSET
4. Apply column fragment filters on the reduced result set

This is achieved by wrapping the limited root query in a subquery and applying fragment-level filters on top of it.

---

Rules

What

• Root-level fragment column filtering based on FieldIdentifier must not reduce rows before LIMIT.
• LIMIT and OFFSET must always be applied to the root entity selection first.
• Column fragment filters must be applied only to the already-limited dataset.

When

• A fragment defines a FieldIdentifier that filters a column
• The fragment is attached to the root entity
• A LIMIT (and optionally OFFSET) is present

How

• Generate SQL using a subquery pattern:
  • Inner query: root selection + joins + ordering + limit
  • Outer query: applies fragment column filters

---

Entry Points

• Query builder responsible for SELECT generation
• Fragment handling logic (FieldIdentifier evaluation)
• SQL rendering layer

Example (conceptual):

SELECT
    root_limited.id,
    root_limited.created_at,
    CASE
        WHEN :include_some_column = true
        THEN root_limited.some_column
        ELSE NULL
    END AS some_column
FROM (
    SELECT
        id,
        created_at,
        some_column
    FROM root_table
    ORDER BY created_at DESC
    LIMIT 50 OFFSET 0
) root_limited;

---

Acceptance Criteria

• Root-level column fragment filters are applied after LIMIT
• Generated SQL uses a subquery when required
• Query results remain semantically identical to previous behavior
• Performance improves measurably for large datasets

---

Risks and Assumptions

Risks

• Incorrect subquery aliasing could break column resolution
• Query planner changes might affect existing optimizations
• Potential impact on database-specific SQL dialects

Assumptions

• The database supports subqueries in FROM
• Ordering is deterministic when LIMIT is applied
• Fragment filters are semantically valid after limiting

---

References and Notes

• SQL execution order: FROM → WHERE → ORDER BY → LIMIT
• Performance issues observed especially with root-level fragments
• This change aligns execution order with user expectations

---

Dependencies and Blockers

• Dependencies:
  • Existing fragment resolution logic
  • SQL renderer supporting nested queries

---

Notes for Reviewers

This change is primarily a performance fix and query correctness improvement.
No functional behavior should change from the user's perspective—only execution efficiency.