MemberJunction / MemberJunction/MJ

CodeGen: base-view label joins are INNER for NOT NULL FKs, so a display label can empty the view

Open
#4,533 0 comments 0 reactions 0 assignees View on GitHub
bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.