# SOQL Semi-Join Execution Cutover

Status: proposed

## Problem

Salesforce-compatible SOQL supports membership predicates whose right-hand side
is a subquery, for example `field IN (SELECT id FROM Object)` and `field NOT IN
(SELECT id FROM Object)`. The current parser preserves that shape as
`models.QueryRequest.SemiJoins`, then the Salesforce dialect resolver
materializes the inner IDs into the outer predicate before dispatch.

That materialized-ID path cannot satisfy large inner result sets because it is
capped before the records engine sees the request. However, moving the nested
shape into the L0 query contract while the materializing resolver remains live
would create two read paths for the same predicate. The cutover needs a single
source of truth at every intermediate state.

## Current-Tree Evidence

- `docs/design/l0-platform-substrate.md` defines the SOQL interpreter as an L1
  language concern and records that `ResolveSemiJoins` exists so the records
  engine never observes nested-query AST.
- `docs/architecture/l0-c-plan.md` defers the semi-join de-skin because the
  placeholder is produced in L1, substituted before dispatch, and the remaining
  L0 placeholder branch is only a defensive net.
- `shared/pkg/models.SemiJoinClause` is the parsed request shape used by the
  legacy placeholder resolver. Its `Index` is tied to the
  `__nx_semijoin_<n>__` placeholder.
- `backend/distributions/soqlexpr/resolver_e2e_test.go` is the current
  executable contract for placeholder substitution, including `NOT IN` behavior
  for empty inner result sets.
- `nexuscrm-analytics/query_clause_fls.go` still has a defensive branch for the
  placeholder token, proving the old dispatch invariant is not fully sealed.

## Decision

Until the cutover lands, the SOQL dialect resolver remains the owner and only
read path for parsed semi-join structure. L0 query contracts must not add a
second semi-join field or mapper while the resolver path can still feed the same
request through a substituted predicate.

The target state is one neutral correlated-membership predicate owned by the L0
query contract and consumed by QueryService and the repository. The production
v63 path must switch to that neutral predicate in the same cutover that removes
or quarantines production use of `ResolveSemiJoins`.

The fact and read path after cutover are:

- **Fact:** correlated membership predicate: outer field, negation, inner
  object, inner selected field, and allowed inner filter/scope clauses.
- **Authoring surface:** the L1 SOQL grammar at the REST/query boundary.
- **Normalized owner:** one neutral L0 query predicate after the L1 parser
  lowers grammar into platform intent.
- **Read path:** parse SOQL -> build one neutral correlated-membership predicate
  -> QueryService validates both sides -> repository lowers the predicate with
  the active read target, RLS, soft-delete, and formula helpers.

No production path may read both the substituted placeholder predicate and the
neutral correlated predicate for the same request.

## Rejected Alternatives

- **Add `QuerySpec.SemiJoins` while `ResolveSemiJoins` remains active.** This
  gives L0 two sources for the same predicate: the substituted outer filter and
  the nested query shape.
- **Patch repository `NOT EXISTS` behavior before the contract cutover.** That
  fixes one symptom while continuing to negotiate ownership inside code review.
- **Keep materializing IDs and only raise the cap.** It preserves the wrong
  execution model and does not match Salesforce-compatible join semantics.
- **Put repository runtime payloads in shared request models.** Shared models
  should carry request intent, not repository-specific metadata or RLS runtime
  objects.

## Implementation Plan

1. Keep the existing resolver path unchanged until a reviewed cutover PR is
   ready.
2. In the cutover PR, add the neutral correlated-membership predicate and wire
   every v63 read door to emit exactly that predicate for semi-/anti-joins.
3. In the same cutover, remove production v63 calls to `ResolveSemiJoins` and
   fail closed if both placeholder and neutral correlated predicate state are
   present in one request.
4. Validate both sides in QueryService using existing object-read, FLS, formula,
   and malformed-query helpers.
5. Lower the predicate once in the repository for row queries, reusing current
   read-target, physical-name, formula-filter, soft-delete, and RLS helpers.
6. Extend aggregate query lowering only after the row-query lowering and
   semantics are accepted, or as a separate PR that reuses the same neutral
   predicate and repository helper without adding another read path.
7. Remove the defensive `__nx_semijoin_` handling from L0 once current-tree
   evidence proves no production path can send unresolved placeholders to the
   engine.

## Verification

The cutover must include tests for:

- `IN (SELECT ...)` with more than 1000 inner matches.
- `NOT IN (SELECT ...)` with more than 1000 inner matches.
- `NOT IN` with an empty inner result set and a nullable outer field.
- A non-empty anti-join inner result set and nullable outer field behavior.
- Row-query SQL shape that contains no `__nx_semijoin_` placeholder and no
  materialized 1000-ID literal list.
- Aggregate `COUNT(Id)` with a semi-join filter.
- FLS, RLS, soft-delete, physical-name, and formula-view read-target mapping on
  both sides of the predicate.

The final implementation PR should run the relevant shared/backend/dialect tests
plus `git diff --check`.

## Residuals

This document does not implement the cutover. It freezes the ownership decision
so implementation PRs can be reviewed against one read path instead of
negotiating the source of truth in code.