RFC: Explicit join direction support for Cube Semantic SQL API and View directions without reverse directions defined in model

**Is your feature request related to a problem? Please describe.**

Currently a key implementation detail in the cube semantic model is that all joins are directed, and that for the most part this is fine for exploration using CUBE Semantic SQL and for the REST API, however you are constrained in the way that cube decides the join path construction when using anything else except for views with join_path.

For SQL based use cases, the SQL join hints are a step in the right direction for diamond subgraphs, however still do not explicit definition of join paths which are resolved by the graph.
For me this is one of the key weaknesses remaining in CUBE vs other semantic layers and either requires additional modelling or a view preventing true analyst use cases.

Example:
A join declared as `orders → customers` only resolves when the planner traverses *from* orders *to* customers — there is no way for a view's `join_path: customers.orders` or a SQL query rooted at `customers` to use that same declared join. Today the workarounds are:

1. Declare the same join on both cubes (the "bidirectional joins" pattern explicitly [discouraged in the docs](https://cube.dev/docs/product/data-modeling/concepts/working-with-joins) — it duplicates the SQL, creates ambiguity for the path-finder, and means every direction-sensitive consumer of the model has to special-case the duplicate).
2. Rewrite the model with the join declared in the opposite direction, which then breaks every existing query that used the original direction.

This forces an awkward choice between (a) accurate model semantics, (b) flexibility for downstream queries, and (c) freedom for view authors. Concretely:

- A view of the form `customers_without_orders` documented in [working-with-joins](https://cube.dev/docs/product/data-modeling/concepts/working-with-joins#join-paths) cannot be expressed against a single-direction `orders → customers` model without redeclaring the join.
- A BI tool that puts the customers table on the FROM side of a `LEFT JOIN` against `orders` succeeds or fails depending on which direction was declared first — there is no syntactic way to ask Cube to honor the SQL clause order strictly.

**Describe the solution you'd like**
What I'm proposing:
An opt-in env var `CUBEJS_BIDIRECTIONAL_SQL_JOINS=true` (off by default) that lets the planner synthesize a reverse `JoinEdge` from a declared edge, that is not exposed to the planner except for **two narrowly documented entry points only**:

1. A new SQL API virtual column **`__cubeExplicitJoinField`** (parallel to `__cubeJoinField`). Use it in a `LEFT JOIN ... ON` to ask Cube to honor the SQL clause direction strictly:

```sql
-- Works against a model that declares only `orders → customers`.
-- Customers without orders ARE included (customers is the row-preserving root).
SELECT count(*) FROM customers c
LEFT JOIN orders o ON c.__cubeExplicitJoinField = o.__cubeExplicitJoinField;
```

2. **Views' existing `join_path`** when the path traverses against the declared direction without declaring the reverse direction in the model:

```yaml
views:
- name: customers_without_orders
cubes:
- join_path: customers
includes: [name]
- join_path: customers.orders # reverse of declared `orders → customers`
includes: [count]
```

**Guarantees:**

- Every other graph traversal — legacy `__cubeJoinField`, REST `joinHints`, member resolution, `/meta` export, connectedness analysis, pre-aggregation matching — continues to use the strictly directed graph regardless of the flag. Pre-PR behavior is preserved bit-for-bit when the flag is off.
- **Pre-aggregation safety:** a rollup defined for one direction will not be served for a query that requests the reverse direction — `MultiFactJoinGroups::resolve_join_path_*` produces different paths in the two cases, `are_join_paths_matching` rejects the mismatch, and the live join with the synthesized edge runs instead. To accelerate both directions, define matching pre-aggregations per direction.
- **Single-flag gate, no dependency on Tesseract or pushdown.** Reverse-edge decoding lives at the `JoinGraph.buildJoin` entry point, so both the JS and Tesseract pipelines pick it up through their normal bridge call. No per-query planner routing logic. **Here I am not sure if this is the correct implementation detail but from my LLM investigations seems to work, but may not be thefuture canonlical way if tesseract moves this into Rust**
- **REST/GraphQL clients cannot trigger reverse synthesis directly.** The explicit-direction hint is internal-only (not in the Join schema or OpenAPI spec). Though as mentioned in previous proposals we could hoist the functionality into rest if needed similar to how views do it, but seems un necessary as using the SQL endpoint is probably the canonical approach going forward.

**Working implementation:**

I have built a very rough initial draft to communicate my intent on a fork (please note I am putting this up while I'm still testing this to gather feedback): https://github.com/simonedbarber/cube/tree/feat/bidirectional-sql-joins

| Layer | Change |
|---|---|
| `packages/cubejs-backend-shared/src/env.ts` | New `bidirectionalSqlJoins` env var |
| `packages/cubejs-schema-compiler/src/compiler/JoinGraph.ts` | `ExplicitJoinHint` type, `synthesizeReverseEdge` (swap `from`/`to`/`originalFrom`/`originalTo`, invert relationship, preserve a new `declaredOn` for `${CUBE}` SQL resolution), sentinel decoding at `buildJoin` entry |
| `packages/cubejs-schema-compiler/src/adapter/BaseQuery.js` | `enrichHintsWithJoinMap` tags view-derived hints as explicit when flag is on; sentinel pre-parsing for defensive normalization |
| `packages/cubejs-schema-compiler/src/adapter/PreAggregations.ts` | SQL emission switched to `j.declaredOn` so synthetic reverse edges produce correct ON SQL |
| `rust/cubesql/cubesql/src/transport/ext.rs` + `analysis.rs` + `ctx.rs` + `rules/{members,filters,old_split}.rs` + `rewrite/converter.rs` | Register the virtual column; egraph rewrite recognizes the token under the flag and emits a sentinel-prefixed hint inside `joinHints` (preserves SQL clause order — no separate field) |
| `rust/cube/cubesqlplanner/cubesqlplanner/src/cube_bridge/join_item.rs` + `planners/join_planner.rs` | Add `declared_on: Option` to `JoinItemStatic` so the Rust planner uses it for ON SQL compilation, falling back to `original_from` for declared edges |
| `docs/content/product/{configuration/reference/environment-variables, apis-integrations/core-data-apis/sql-api/joins, data-modeling/concepts/working-with-joins}.mdx` | Env var entry + user-facing docs for the new field and the view interaction |

**Tests included:**
- 31 gated unit tests covering OFF/ON behavior, per-hint precedence, mixed-order through the full BaseQuery pipeline, view enrichment with real `view(...)` definitions, Tesseract bridge decoding, and gating edge cases.
- 113 regression tests in `views.test.ts` + `base-query.test.ts` unchanged.
- 1 Rust integration test on the mock bridge (asserts plain reverse hints still fail, only sentinel-prefixed ones synthesize).
- End-to-end script (`packages/cubejs-schema-compiler/test/integration/postgres/explicit-join-field-e2e.js`) verifies row-count semantics against Postgres: with the flag off, every direction collapses to declared; with the flag on, only sentinel-prefixed reverse queries include rows that would have been preserved by a reverse `LEFT JOIN`.

**Describe alternatives you've considered**
Other patterns I considered;
1. **Recommend declaring joins bidirectionally in the model.** Already the documented workaround and explicitly discouraged. Doesn't help BI-tool users who can't change the data model and doesn't address the view ergonomics problem.

2. **Make the `JoinGraph` undirected by default.** Would break the careful pre-aggregation matching that depends on directional paths, plus it's a backwards-incompatible behavior change for every existing query. Opt-in via env var avoids all of that.

3. **Auto-detect direction when traversal fails.** Considered. Rejected because it removes the operator's ability to forbid reverse traversal — a query that fails today as a misconfiguration could start silently succeeding with semantically different results. An explicit join token + flag keeps the failure mode visible.

**Additional context**

- Open questions for design feedback:
- Naming: is `__cubeExplicitJoinField` the right name for the SQL token? The chosen name emphasizes "explicit" because the token explicitly opts a single JOIN clause into direction-strict semantics; the env var carries the "bidirectional" label because that's the user-facing capability. Alternatives considered:
- `__cubeDirectedJoinField`
- matching standard postgres SQL syntax with a validation step that the join conditions match the conditions in the defined cube join.
- One clear confounding factor (https://github.com/cube-js/cube/issues/10265), this proposal clearly doesn't consider support these future named joins. So extension to support the named join to the same cube through a named join would be necessary.
- View semantics: should the feature flag only enable reverse-direction `join_path`, or should it also stop other graph traversals when explicit direction is requested? Current implementation gates by per-hint typing — only explicit hints (from the SQL token or view enrichment under the flag) use synthesis; everything else stays directed.
- SQL semantics: should we enforce that if one joins uses `__cubeExplicitJoinField` that all joins must be defined this way? **This is a key gap that I have yet to verify:** specifically how this affects planning as we may hit a deadlock situation if key subgraphs are locked in this way.
- Future work: should Tesseract eventually own its own `JoinGraph` natively rather than bridging to JS? Not blocking for this feature — the bridge approach works correctly today — but it informs whether to mirror more of `JoinGraph.synthesizeReverseEdge` into Rust later.
- I am unsure (and plan to research this more) how this impacts rollups and other cache internals. Also how this impacts the definition of rollups.
- Happy to walk through any specific design decision in detail. The branch is up-to-date with tests passing and a working E2E run against Postgres. Looking for feedback on the approach before promoting to a PR.