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

[simonedbarber]

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 — 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 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:

   -- 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:

   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<String> 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 (Join aliases support #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.

[shivamsaryar]

Some data on how direction currently resolves, since the thread doesn't cover it and it turns out not to be stable across versions.

Setup: two cubes, order_item (a fact table) and product (a dimension table), with the join declared in both directions. Each query below has exactly one measure and one dimension. I varied which cube each one comes from, then read the base table out of /cubejs-api/v1/sql.

measure from	dimension from	v1.6.72	v1.7.25
order_item	product	fct_order_item	dim_product
product	order_item	dim_product	fct_order_item
order_item	order_item	fct_order_item	fct_order_item
product	product	dim_product	dim_product

The two mixed cases invert between versions. The two single-cube controls don't move, which I think rules out this being a quirk of one query shape.

So the precedence looks like:

• 1.6.72: the base cube is the one supplying the measure
• 1.7.25: the base cube is the one supplying the dimension
• both versions: within a single kind (two measures, or two dimensions and no measure), the first requested member wins

The practical effect on a real model is that revenue by brand returns 2,754 rows on 1.6.72 and 2,756 on 1.7.25. The dimension-rooted plan includes two brands that have never sold anything. Same model, same query, different population. And since query builders assemble the member list in click order, the order a user clicks fields can decide which of the two they get.

I couldn't find this precedence documented anywhere, and I don't think the 1.6 to 1.7 change was announced. That seems like an argument for this RFC: if direction is explicit and declared once, none of the above is something users have to work out again on every upgrade.

Happy to share the repro if it's useful.