v1.0.23

v1.0.23

Added: lateralJoin() — a reference navigation joined as a LATERAL probe of its target's key

A reference navigation always rendered as a plain join, and PostgreSQL chose how to run it. For a query that
joins a FEW rows (one parent's lines) to a LARGE table, that choice follows the statistics of the foreign-key
column: when the rows hold keys far above the range its histogram has seen, the planner expects a merge join to
stop after a sliver of the target's primary-key index — and then reads all of it, for every execution of the
cached plan. Nothing in the query could rule that plan out.

lateralJoin(row => row.<reference>) renders ONE navigation of ONE query as a key probe per row, the only join
that can be made of it:

await db.books
  .where(b => eq(b.shelfId, shelfId))
  .lateralJoin(b => b.author)
  .select(b => ({ title: b.title, author: b.author.name, region: b.author.region.name }))
  .toList();
// LEFT JOIN LATERAL (SELECT "author__probe".* FROM "authors" "author__probe"
//                    WHERE "author__probe"."id" = "books"."author_id" OFFSET 0) "author" ON true
// LEFT JOIN "regions" AS "region" ON "author"."region_id" = "region"."id"

• Same rows as the plain join: LEFT JOIN LATERAL … ON true for an optional navigation (an unmatched key keeps
  its row), INNER JOIN LATERAL … ON true for a required one. Every key pair — a constant key part included —
  goes into the probe's WHERE. The probe reads its table under "<alias>__probe", so a navigation of a table to
  itself never reads the foreign key off the probed row. OFFSET 0 keeps PostgreSQL from pulling the subquery
  up into a plain join. Navigations reached through it join off its alias, as before.
• Where: root queries (db.<table>.lateralJoin(…), after where(), QueryBuilder, SelectQueryBuilder) and
  collections (p.items.lateralJoin(it => it.owner).select(…), before select()) under the lateral, cte and
  temptable strategies, a collection's count() / exists() included; and so everything that renders the
  query — count(), exists(), min() / max() / sum(), future(), prepare(), QueryBatch legs, union()
  legs. Built twice, the query renders the same text: a prepared statement keeps one cached plan.
• Per navigation and opt-in, never model-wide: for navigations that READ values of rows the query found by other
  means. On a navigation the WHERE filters by, the probe forbids PostgreSQL to start from the target's matching
  rows — see the guide.
• Refused when the query is built, never rendered as a plain join instead: a selector returning a column, a
  collection or a navigation reached through another one; update() / delete() (PostgreSQL lets no LATERAL
  subquery read the row they write — read the value through a correlated scalar subquery); groupBy(); a
  collection flattened by selectMany(), in either order.

The in-memory database runs the probe and returns the rows PostgreSQL returns; nothing changed there.

Docs: docs/guides/lateral-navigation-joins.md (new), listed in docs/README.md.

Tests: tests/queries/lateral-navigation-join.test.ts — on PostgreSQL, PGlite and in memory, each probed query
against the same query without lateralJoin() (identical rows) and its exact SQL: the opted-in navigation probes
while a required sibling stays a plain INNER join; INNER JOIN LATERAL; a constant key part; a navigation off the
probe's alias in the projection and the WHERE; a navigation of a table to itself; count / exists / max; the
untyped builders; a collection's item navigation under each strategy; an exists() and a count() of a
collection filtering through the probe; a QueryBatch leg and a unionAll() leg; one prepared text and
prepare(); every refusal; and, on PostgreSQL, the plans — with hash joins and nested loops switched off the
plain join becomes a merge join while the probe stays a nested loop over an index scan of the target's key.

Backward compatible: a query without lateralJoin() renders the SQL it rendered before — the 43 936 statements
the suite runs, recorded in memory on 1.0.22 and on this release, are byte-identical except for exactly the
statements two recordings of 1.0.22 already differ in (table and role names carrying the process id) — and the
full suite passes unchanged on both engines. The public API only gains members — lateralJoin() on DbEntityTable,
IEntityQueryable, EntityCollectionQuery, QueryBuilder, SelectQueryBuilder, CollectionQueryBuilder, and
the optional NavigationJoin.lateral; the SelectQueryBuilder constructor takes one more optional argument
(last). Nothing was narrowed or removed.