MemberJunction / MemberJunction/MJ
CodeGen: base-view label joins are INNER for NOT NULL FKs, so a display label can empty the view
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 2d 1h
- Merged PRs (30d)
- 323
Description
## Summary
CodeGen emits a base-view "label" join as `INNER JOIN` whenever the foreign-key column is NOT NULL. A join that exists purely to fetch a display name can therefore **delete every row of the view**.
## Cause
`packages/CodeGenLib/src/Database/sql_codegen.ts:2095`:
```ts
`${ef.AllowsNull ? 'LEFT OUTER' : 'INNER'} JOIN ... ON x. = alias.`
```
The join's only purpose is to add `_Virtual`, the related entity's name field. Its cardinality is a display concern, but its join type decides whether rows survive.
## Impact
Observed in production: a wrong `RelatedEntityFieldName` on a NOT NULL column (from a generator bug, companion issue) made the predicate match nothing, so a view backed by a 67,389-row table returned **0 rows**. The same defect on a nullable column would have produced NULL labels and no data loss.
Two properties make this hard to notice:
- The failure is silent. No error, no warning, and consumers see an empty result set as "no data".
- Any aggregate over the view returns 0 rather than failing, so it can be presented as a real answer.
There is currently no config to force LEFT joins; the only escapes are per-field (`IncludeRelatedEntityNameFieldInBaseView = 0`, `RelatedEntityJoinFields.mode = 'disable'`, or removing the related entity's name field).
## Suggested fix
Emit `LEFT OUTER JOIN` for label joins unconditionally. A missing label should render as NULL, never remove a row. If INNER is wanted for genuine referential-integrity cases, make it opt-in per field rather than derived from `AllowsNull`.
At minimum, restrict INNER to cases where the target is the related entity's primary key **and** the FK is enforced by a real constraint.
Contributor guide
Research direction
Start at packages/CodeGenLib/src/Database/sql_codegen.ts:2095 and trace how base-view label joins are generated from foreign-key metadata. Verify the generated view preserves source rows when a related display label is missing or mismatched, while still producing the label when available; add or run regression coverage for both nullable and NOT NULL foreign keys.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql, typescript
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 75/100