[Epic] Canonical Substrait algebra + dialect-boundary unparse, with a Calcite-compatibility mode

[nielspardon]

Thesis

Calcite's core relational algebra conflates two things it should keep separate: the logical
operation and its SQL-dialect spelling. Dialect-specific operators (REGEXP_EXTRACT vs
REGEXP_SUBSTR, the SAFE_* family, IFNULL vs COALESCE, BIT_AND vs BITAND_AGG, …)
leak straight into the algebra and its operator tables. That leakage is the root of the
fragmentation and the N:1 mapping problems in Isthmus today.

Substrait's whole purpose is to be the clean, dialect-agnostic layer Calcite lacks: one
canonical semantic operation, with dialect spelling pushed to the edge. This epic proposes
that Isthmus adopt that separation directly:

• Carry canonical Substrait operations in the (Calcite) algebra when converting
  Substrait → Calcite, rather than mapping onto near-duplicate dialect-specific Calcite
  operators.
• Render dialect-specific SQL at the boundary via our own SqlDialects that know how to
  unparse Substrait operations.
• Offer a Calcite-compatibility mode that instead lowers Substrait onto the subset of
  native Calcite operators/relations, accepting the semantic compromises — for users who
  need to hand results to Calcite's own ecosystem (optimizer rules, Calcite-based engines,
  stock unparse).

Users then choose, eyes open, whether they are committing to Substrait fidelity or to
Calcite interop.

The two modes

• Substrait-faithful mode. Where Substrait and Calcite disagree, use synthetic
  Substrait-native operators/relations that preserve Substrait semantics exactly. Requires
  our dialect-boundary unparse layer to render them.
• Calcite-compatible mode. Lower Substrait onto native Calcite operators/relations,
  accepting the compromises (nullability differences, N:1 lossiness, operations Calcite
  lacks or models differently). Gets Calcite's optimizer and stock unparse for free.

Design principles

Function / relation asymmetry (the key insight)

The cost of synthetic operators is not symmetric:

• Functions are cheap to synthesize. Calcite's planner rules match RelNode patterns
  (FilterIntoJoin, ProjectMerge, AggregateProjectMerge, …); to them a function call is
  an opaque payload inside a Rex tree. Synthesizing a function operator forfeits only
  unparse and Rex-level rewriting for that operator — no structural optimization.
• Relations are the expensive case, because relations are exactly what the planner rules
  match on.

⇒ Synthesize functions freely; be selective about relations. Functions default to
canonical/synthetic. Relations map to Calcite relations wherever a faithful equivalent
exists and synthesize only the genuinely-unmappable remainder (e.g. SetOp
MINUS_MULTISET/INTERSECTION_PRIMARY — #303, ConsistentPartitionWindow, GROUP_ID
synthesis, CLUSTERED sort).

Rex-level "optimization" is often a fidelity liability, not a loss

Calcite's Rex rewriting already reaches into converted expressions in ways that fight
Substrait semantics — e.g. RexSimplify (run by RelBuilder.project) re-derives decimal
arithmetic at Calcite's default precision-19 cap and injects a truncating
CAST(… AS DECIMAL(19,0)), which we only avoid by forcing SubstraitTypeSystem into the
RelBuilder. A synthetic operator that Calcite declines to simplify is therefore
protected from a class of fidelity bugs — reinforcing "synthesize functions freely".

The dialect-boundary unparse layer is the linchpin

Canonical operations in the algebra are only useful if we can render them. Our own
SqlDialects (overriding unparseCall) are what make the canonical representation honest;
this is most of the real work and is no longer a "separate track" — it is the boundary the
whole model depends on. (It remains separable in time: faithful mode can initially unparse
synthetic ops in a default Substrait spelling, with real per-dialect spellings layered on
later.)

Honest cost summary

	Faithful (synthetic)	Compatible (Calcite subset)
Substrait semantic fidelity	preserved exactly	compromised where they disagree
Calcite planner rules (relations)	lost for synthetic relations	full
Calcite Rex simplification (functions)	not applied (often a benefit)	applied (sometimes harmful)
SQL unparse	we own it (our dialects)	Calcite stock
Interop with Calcite-based engines	reduced for synthetic ops	full

Work breakdown

• Function-mapping foundation — Refactor Isthmus function mapping into a unified bidirectional model #1012 (unified bidirectional FunctionMapping model;
  each mapping carries a canonical/synthetic binding plus an optional lossy Calcite
  binding; structurally removes the N:1 reverse problem in the synthetic path).
• Relation-mapping: faithful vs. synthetic handling — apply the same canonical/lossy
  model to relations; synthesize only the unmappable ones (SetOps remove invalid mappings from SetOps to Calcite relations #303,
  ConsistentPartitionWindow, GROUP_ID, CLUSTERED sort, …). (New issue to file.)
• Dialect-boundary unparse layer — our own SqlDialects that unparse canonical
  Substrait operations; start with a default Substrait spelling, then per-dialect
  (BigQuery, Spark, …). (New issue to file; supersedes the informal dialect-rendering
  design.)
• Mode selection & configuration — expose Substrait-faithful vs. Calcite-compatible
  at the ConverterProvider level; decide granularity (global vs. per-operation override)
  and the default. (New issue to file.)

Design decisions already settled

• Breaking changes are acceptable — prefer the clean end state over source compatibility.
• Scalar / aggregate / window converters stay separate — Substrait models aggregate and
  window as distinct categories; only Calcite flattens them into operators. Share only the
  kind-agnostic engine.
• Niladic context variables (CURRENT_TIMESTAMP/CURRENT_DATE/CURRENT_TIMEZONE) stay
  as first-class Substrait ExecutionContextVariable expression types, not functions.
• Return-type behaviour is YAML-driven — the hand-coded AggregateFunctions shims are
  replaced by one generic policy derived from the spec YAML (verified per round-trip test).

Open forks (to settle within the child issues)

1. Default mode — Calcite-compatible (today's behaviour, least surprising) initially,
   with Substrait-faithful opt-in until the dialect layer matures?
2. Function synthesis: uniform or hybrid? Uniform-synthetic for all functions (one clean
   path) vs. Calcite ops where they agree exactly (a per-mapping faithful/lossy flag).
3. Granularity — global mode switch vs. per-operation override.
4. Compatible mode implementation — a lowering pass over the canonical representation vs.
   a separate mapping registry.

Related

• Refactor Isthmus function mapping into a unified bidirectional model #1012 — unified function-mapping model (this epic's foundation)
• (isthmus) wrong Substrait to Calcite mapping for subtract:date_iday #377 — wrong Substrait → Calcite mapping for subtract:date_iday (the N:1 reverse bug)
• [isthmus][SubstraitToCalcite] Aggregation nullability differs from Calcite #336 — aggregation nullability differs from Calcite
• remove invalid mappings from SetOps to Calcite relations #303 — remove invalid SetOp → Calcite relation mappings (a relation-side disagreement)
• allow functions with identical signatures in different extensions to coexist #275 — allow functions with identical signatures in different extensions to coexist
• AggregateCall conversion ignores collations #215 — AggregateCall conversion ignores collations