On MSSQL an `OUTER APPLY` is not correlated, so a nullable lateral join behaves as an inner join

[phdoerfler]

On how this was written

This issue report was mostly done by an LLM. I have poked and prodded at it, encouraged it to verify rather than assume, enforced a proper software engineering workflow including TDD and reviews on top of which I did a manual review. It was an iterative process. The issue is now at a stage where I can't find anything obviously wrong with it but I will admit that the SQL generation is a bit arcane and things might have slipped past me.

The Issue

MSSQL has no LATERAL keyword, so DoobieMSSqlMapping.mkLateral renders a lateral join as CROSS/OUTER APPLY. APPLY takes no ON clause, so the join's conditions can't be rendered with the join and are instead lifted into the enclosing select's WHERE clause by Laterality.Apply.joinPredicates, via mkNested's extraWheres.

For CROSS APPLY that's equivalent to an ON clause. For OUTER APPLY it isn't: the WHERE clause is applied after the join, and discards precisely the null padded rows the outer join produced. The subquery is never correlated to the enclosing select at all — it contains no reference to the outer table — so the APPLY behaves as a cross join whose result is then filtered down to matching rows. An OUTER APPLY is therefore an inner join in all but name.

Reproducing it

Any query which puts a nullable field over a lateral subquery will do. The one which surfaced this uses the schema from #888 — type A { name: String!, b: B } with b nullable, and type B { name: String!, c: C! } with c non-null — over three rows of A, one with a matching B, one whose b_id dangles, and one whose b_id is null:

query { as { name b { name c { name } } } }

MSSQL emits:

SELECT nested.id, nested.id_alias_0, nested.name, nested.name_alias_1,
       nullable_parent_a.id AS id_alias_2, nullable_parent_a.name AS name_alias_3
FROM nullable_parent_a
OUTER APPLY (
  SELECT nullable_parent_b.id AS id_alias_0, nullable_parent_c.id, nullable_parent_c.name,
         nullable_parent_b.name AS name_alias_1
  FROM nullable_parent_b
  INNER JOIN nullable_parent_c ON (nullable_parent_c.id = nullable_parent_b.c_id)
) nested
WHERE (nullable_parent_a.b_id = nested.id_alias_0)

which returns one row. Postgres emits the same subquery as a LEFT JOIN LATERAL ... ON (...) and returns all three, two of them with "b": null. Executing the MSSQL statement directly confirms it isn't a mapping or decoding artefact: as written it yields one row, and moving the correlation inside the APPLY and dropping the outer WHERE yields three.

Note

This is independent of #888 and older than it. #888 needs a specific schema shape to bite; this affects any lateral join under a nullable field on MSSQL. It only became visible through #888 because before that fix the nested select was flattened into the join chain and no APPLY was emitted at all, so the two have to be fixed together for either to be observable.

There's a neighbouring case this doesn't cover, left alone deliberately: the inner mkNested runs with outer = !multiTable, so in the multiTable path an APPLY join built there contributes no join conditions at all — the guard sits ahead of the distribution either way, so this is unchanged by a fix. Nothing in the suite fails on it, though that's the absence of a witness rather than evidence there isn't one.