Scalar Invertibility and Authored Inverses

Stability: Beta — see Stability Tiers.

How Quereus makes a projected column writable by inverting its scalar transform: the law-gated invertibility registry, authored inverses (with inverse), view-level insert defaults, and mutation tags. A satellite of View Updateability.

Scalar Invertibility

Invariant: VU-006

Scalar functions and operators expose an invertibility profile in their schema registration. The lineage walker consults the profile when threading a transformation through a column reference.

type InvertibilityProfile =
  | { kind: 'passthrough'; arg: number }
  | { kind: 'inverse'; fn: ScalarFn; domain?: PredicateExpr }
  | { kind: 'opaque' };

passthrough — the named argument is returned with a non-data-altering transformation. The lineage threads the argument's lineage as if the call were not there. Example: collate(x, 'NOCASE') is { kind: 'passthrough', arg: 0 }.
inverse — the function has a deterministic inverse, optionally restricted to a domain predicate. When inverting an assignment, the engine substitutes the inverse and conjoins domain into the row-identifying predicate. Example: integer addition by a constant — x + k has inverse y => y - k with unrestricted domain over integers.
opaque — no inverse known; columns whose lineage passes through this function become computed (read-only).

Built-in functions ship with profiles. cast-style conversions advertise inverse when lossless and opaque when lossy. coalesce(x, default) is passthrough on arg = 0 when the default branch is provably unreachable on the update path (via FD-driven not null proof). String functions are opaque by default; the few invertible cases are declared explicitly. User-defined functions declare their profile at registration. A predicate-typed UDF additionally declares which arguments it sees through (passing lineage through, leaving the row's update site untouched) versus which arguments it consumes opaquely. The same surface is reused by the assertion-derived-premises pipeline.

How writability follows from the profile. The plan-node backward walk resolves every projection to a base UpdateSite — identity / rename (b as bc), passthrough (an identity-on-value transform: b collate nocase, a no-op cast(b as <same logical type>); no inverse), or inverse (a non-identity invertible transform: b + 1; inverse present) — else computed / null-extended (read-only). Both mutation spines route the full writable-base set (identity + passthrough + inverse) on the UPDATE write path, applying a site's inverse only when present: set bp = 9 on a b + 1 as bp column lowers to set b = 9 - 1; set bc = v on a b collate nocase as bc passthrough column lowers to set b = v (no inverse applied). INSERT is insertable for the inverse-absent subset — identity / rename and passthrough store the value verbatim — while registry-inverse and opaque columns are non-insertable (the lowering writes the value raw, with no hook to apply an inverse; an authored inverse supplies exactly that hook and is insertable). The two spines share an identical insertability gate (writable && inverse === undefined, lifted by an authored inverse). The static view_info / column_info surfaces read the same plan-node lineage and report a base site (identity, passthrough, or inverse) writable, agreeing with the dynamic truth.

Authored inverses (`with inverse`)

The invertibility registry composes inverses it can infer (±k, identity, passthrough, no-op casts). When the forward expression is opaque to the registry but the author has a chosen inverse — the canonical case is a non-injective case mapping where writes should store a representative value — the inverse is authored inline on the result column, as a core select extension:

result_column := expr [ as alias ] [ with inverse ( column = expr { , column = expr } ) ]

select
  case code20 when 'A1' then 'A' when 'A2' then 'A' ... end as code
    with inverse (code20 = case new.code when 'A' then 'A1' ... end),
  b || ' ' || c as full_name
    with inverse (b = substr(new.full_name, 1, instr(new.full_name, ' ') - 1),
                  c = substr(new.full_name, instr(new.full_name, ' ') + 1))
from t;

Design rules:

Named targets only. Each assignment names a base column of the FROM sources and supplies the expression that computes it from the written view row. There is no inferred-target shorthand — explicit targets are self-documenting and stable under edits to the forward expression (an inferred form's validity would hinge on which base columns the forward happens to reference). A multi-input forward (b || ' ' || c) simply carries one assignment per base column. The assignment-list shape deliberately mirrors the with defaults clause.
Scoping is asymmetric, by design. The forward expression is in base terms; the inverse expressions are over the written view row, referenced with the mandatory NEW. qualifier (new.code — including new.<this column> itself as the written value). Requiring NEW. keeps the inverse unambiguous against the base columns otherwise in scope in the body.
Validation is position-independent. Wherever the clause appears, build-time checks require every assignment target to resolve to a column of the FROM sources and every NEW.* reference to resolve to an output column of the select — so a typo fails loud even when the relation is never used as a write target. Until the relation is a write target, the clause is inert metadata.
Consumption is the lineage walk. A computed output column carrying an authored inverse upgrades to a writable base site with supplied put expressions; the backward walk routes each target assignment to whichever base relation owns that column (so a multi-target inverse fans out across a join's sides or a decomposition's members through the same per-op routing as everything else). On UPDATE the assigned view value lowers through the authored expressions; on INSERT the envelope evaluates them over the supplied row per VALUES row — which is why an authored-inverse column is insertable where a registry-inverse column is not.
Authored wins. An authored inverse on a column the registry could already invert (or on a bare passthrough) overrides the inferred put — explicit overrides generated, the same stance as the lens layer. The redundant-on-passthrough case warrants an advisory, since it usually signals confusion.
The clause is total per column. The author writes the inverse of the whole forward term; the registry does not compose partial authored fragments with inferred steps. (Function-level declared inverses — registering an inverse alongside a UDF so the registry can compose it — are a possible convenience layer; the term-level clause alone is sufficient, since anything composable can be written out.)
Law treatment. PutGet (write-then-read reproduces the written value) must hold and is checked by composing forward(inverse(NEW)) ≡ NEW.col — decided by enumeration when the column's domain is constrained (a CHECK in (...) list): a proven violation is a deploy error naming the column and value; a non-enumerable domain degrades to the safe admit (mutation-time behavior governs, the prover's usual posture). GetPut is intentionally surrendered for a non-injective forward — a write-through normalizes the base value — and surfaces as an acknowledgeable advisory at the lens boundary (lens.getput-lossy), never a silent admit and never a hard error. Enumeration that proves the mapping bijective suppresses the advisory.

Because the clause lives on core select, every relation site gets it uniformly — lens bodies (where the lens prover consumes it), plain views, CTEs, and subqueries-in-from.

Status — what is wired today. The clause parses, round-trips, and is validated at build time wherever it appears (planner/analysis/authored-inverse.ts, run from the select-projection builder): target resolution against the FROM sources, new.* resolution against the select's output columns, the bare-reference rejection, and the cross-result-column duplicate-target rejection (an in-clause duplicate is a parse error). A clause on an aggregate result column is rejected outright (the aggregate phase never reaches the projection lineage the clause rides, and aggregate views are read-only — silent inertness would mask the typo'd intent). The lineage walk upgrades a clause-carrying projection to a writable authored UpdateSite (puts target-resolved through the child lineage's ownership routing; authored wins over identity / passthrough / registry-inverse alike, and an unroutable target degrades the column to computed rather than falling back to the inferred put). Consumption:

Single-source UPDATE / INSERT — fully wired. UPDATE lowers one base assignment per put, substituting new.<x> with the written view row's value of x: the assigned value when x is assigned in the statement (the carrying column itself, or any co-assigned sibling — every embedded value reads the pre-update row, so cross-references are order-independent), the column's view name otherwise — then riding the standard view→base lowering (the forward read image for unassigned columns). INSERT evaluates the puts per VALUES row, with new.<x> bound to the supplied cell, else the appended constant-FD / with defaults expression for x's base column, else NULL; an authored put target counts as supplied, so it takes the inverse-computed value ahead of any with defaults entry or base default. A SELECT-source insert through an authored column is rejected (unsupported-source — the per-row cell substitution needs VALUES, the same v1 boundary as the appended-defaults rewrite). Two supplied view columns landing one base column (authored put vs. verbatim target) reject with conflicting-assignment.
Multi-source (join) UPDATE — wired: each put routes to its owning join side (a two-sided target set yields two child ops, atomic, FK-parent-first), new.<x> binds the written view row exactly as on the single-source path (co-assigned siblings included), and a new.<x> whose forward image reads the partner side rides the same captured-read machinery (and gates) as a cross-source set value.
Multi-source (join) INSERT — deferred: evaluating puts through the shared-surrogate envelope is rejected with a sited diagnostic naming the column (see Current limitations).
Decomposition fan-out — deferred: a write targeting an authored column of a decomposition-backed logical table rejects with unsupported-decomposition-member, naming the member(s) the puts route to.
view_info / column_info — an authored column reports updatable (single-put inverses carry their base trace; a multi-target inverse reports a null base, like an existence flag) and its put targets count toward insert coverage on the single-source shape.
Lens bodies — the sparse-override merger carries the clause per covered column into the composed read body (a gap-filled column never has one), so a lens write consumes it through the same spine. The prover's law treatment is wired: PutGet is checked by enumeration over the column's CHECK in (...) domain (lens.putget-violation on a proven loss, degrade-to-safe otherwise), GetPut surfaces as the acknowledgeable lens.getput-lossy advisory (suppressed when the enumeration proves the forward bijective), an authored inverse satisfies quereus.lens.writable = true, a logical CHECK over an authored column enforces row-local via forward substitution (single-source bodies only — a multi-source forward reds lens.unrealizable-constraint rather than deploying a CHECK that could pass vacuously on a member write row), and quereus_effective_lens reports the per-column inverse disposition (authored / inferred / none). The redundant-on-passthrough advisory named above is still not emitted.

The clause rides ALTER TABLE … RENAME TO / RENAME COLUMN propagation alongside the body, by symmetry with with defaults: a rename of a FROM-table base column rewrites each assignment's target (targets are base columns — exactly what renames touch, riding the same scope-aware walk as an unqualified body reference); renamed tables/columns inside an assignment's expression (subqueries) rewrite scope-aware; and new.<col> references — which are by view-output name, so the body rewrite covers aliased projections — are retargeted where a rename shifts an output name: an unaliased bare projection of the renamed column, or a star projection covering the renamed table (unless an explicit projection still exposes the old name).

View defaults

A view (or materialized view) declares omitted-insert defaults first-class, as a trailing with defaults (…) clause of the core select — it binds to the whole query expression after limit/offset, before the DDL-level with tags:

create view dfi_v (id, name) as select id, name from dfi
  with defaults (created = epoch_ms('now'));

Because the clause lives on the select AST (SelectStmt.defaults), it parses wherever a select parses — a view body, a CTE body, a subquery, or a bare top-level select. It is inert metadata wherever no write path consumes it (mirroring an unused with inverse): a bare top-level select … with defaults (…) parses and runs, ignoring the clause; a VALUES-bodied view's defaults are dead metadata (the view is non-updateable). Only when the view is an actual INSERT write target does the rewrite fire.

Across the derived DML write targets the same consume-or-inert rule governs reach. The clause is active on a CTE-name INSERT target — with t as (<body> with defaults (…)) insert into t … fills the omitted columns through the ephemeral substrate exactly as a named view does (bodyDefaults reads it off the flattened body select). It is inert on an inline-subquery target — the only inline-subquery writes are UPDATE/DELETE, which never consult defaults, and inline-subquery INSERT is rejected (§ Inline subquery DML target). So the CTE name is the only derived target that fires defaults. A body-shape reject (aggregate / set-op / a SELECT-source insert that still needs a default appended — the rewrite is VALUES-only) fires regardless of the clause: defaults are appended only after the body is proven decomposable, so they never rescue a non-updateable body.

Each entry names a base column the view projects away (the dominant case — the column has no slot in the view's rename-only output column list) or a base-lineage view column, and carries a real SQL expression (a first-class AST value with a source location — not re-parsed tag text). At write-through the expression is evaluated per omitted-insert row at step 5 of the insert-defaulting chain (§ Projection): after the user value / constant-FD / FD-reconstruction / EC-propagation sources, ahead of the base column's declared default. The expression must be self-contained (literals, function calls, subqueries — no references to the inserted row's columns): the rewrite appends it as an extra cell on each VALUES row, where a column reference has nothing to bind against and fails at plan time. An entry naming a column that is neither a base column nor a base-lineage view column is a hard sited diagnostic at write time; the read-only view_info surface conservatively skips such an entry instead (never-throw posture), so is_insertable_into stays honest-conservative. Target resolution stays at write time (not create time): the base-column lineage the targets resolve against is only assembled when the view is a write target, so the default-target-not-found / conflicting-assignment diagnostics fire exactly when an insert actually flows through the view.

The clause is accepted identically by create materialized view (every MV is a single-source passthrough, so MV write-through shares the same rewrite spine; the defaulted source column is transparent to row-time backing maintenance) and by declarative view / materialized view items, and it round-trips through export_schema and the declarative renderers.

The clause rides ALTER TABLE … RENAME TO / RENAME COLUMN propagation alongside the body: because the clause is stored inside the select body AST, the scope-aware body rewrite (renameTableInAst / renameColumnInAst in schema/rename-rewriter.ts) descends select.defaults directly — a rename of a FROM-table base column rewrites the entry's target column via the same synthetic-probe path a with inverse target uses (the dominant projected-away case, which the body's projection rewrite alone would never touch), and a renamed table or column inside an entry's expr rewrites scope-aware within the select's FROM frame (an inner subquery ref binding a like-named column on its own FROM is disambiguated by that subquery's pushed scope frame). The declarative differ applies the same body walk inversely when reconciling a declared definition against a not-yet-renamed catalog. A clause-only rewrite (projection untouched) still fires exactly one view_modified / materialized_view_modified, so the regenerated DDL, the MV's bodyHash, and a store-backed catalog all carry the new name.

The clause is the only insert-default surface. Its precursor — the quereus.update.default_for.<column> reserved tag, at both its view-DDL and statement-level sites — has been removed: a stray occurrence is an unknown-reserved-tag error like any other retired key. A per-statement default has no replacement surface; supply the column an explicit value in the insert instead. (The earlier insert defaults (…) spelling — the clause hung off the DDL statement rather than the select — is likewise gone with no back-compat: re-spell it with defaults (…).)

Navigation Menu

Uh oh!

Scalar Invertibility and Authored Inverses