A computed column's nullability is not inferred, so a correlated subquery that matches nothing fails at scan time instead of at declaration time

[jryannel]

Porting a module whose list did LEFT JOIN projects p ON te.project_id = p.id and projected p.name. Declared as a computed column:

schema.Computed("project_name", schema.TypeText,
    schema.FromSQL("(SELECT p.name FROM projects p WHERE p.id = time_entries.project_id)")).
    Searchable(),

Generated model:

ProjectName string `db:"project_name" ... sqlb:"type:text,...,readonly"`

First request against real data:

rest: unclassified error answering as 500 resource=time_entry
err="sqlb: scanning timeentriesdata.TimeEntry: can't scan into dest[17] (col: project_name): cannot scan NULL into *string"

.Nullable() fixes it and the port carries that now. The report is about where the failure lands.

Why non-null is the wrong default here

A correlated subquery returns NULL when it matches nothing. For this column that is not an edge case — it is every row with no project, which is most of them, plus every row pointing at a deleted project, which is possible precisely because a cross-module reference has no FK. The LEFT JOIN this replaced had exactly these semantics and the hand-written DTO spelled the field *string.

So the declaration defaulted to the one reading the expression cannot satisfy, and it did it silently. A stored column gets its nullability from NOT NULL in the DDL, which the round trip checks; a computed column has no DDL to read it from, so the default is doing all the work and it points the wrong way for the shape that motivated the feature.

Why the diagnosis is more expensive than it looks

• It surfaces as a 500 at runtime, not as an error from sqlb generate or from the drift gate. Both were green: generation has no opinion, and migrate.Diff correctly ignores a column that is not in the database.
• It needs data to reproduce. A test fixture where every row happens to have a project passes. Ours only failed because one test seeded an entry without one.
• The message names the Go type, not the declaration. cannot scan NULL into *string sends you to the model, which is generated, before it sends you to the Computed call that produced it.

Suggested fix, in order

1. Default a computed column to nullable, and let NotNull() (or similar) be the opt-in. Any expression can be NULL unless the author says otherwise; the current default assumes the opposite of what SQL does.
2. Or infer it. A (SELECT …) subquery is nullable; a AND b over two non-null columns is not. Probably more machinery than it is worth, and wrong in the unsafe direction whenever the inference is incomplete.
3. Or refuse the ambiguity: require every Computed to state nullability explicitly, so neither default can be silently wrong. Breaking, but the declaration is where this belongs and there are not many of them in any one schema.

1 is what I would want. It matches SQL's own default and it fails in the safe direction: a nullable column typed *string scans a non-null value fine, where the reverse does not.

Related

Same port as #142, #143, #144, #146. This one and #144 are the two that cost real debugging time; the rest announced themselves.

[jryannel]

Fixed in 93612a4, on claude/github-issues-c69665. Took option 1, as you'd want.

schema.Computed defaults to nullable; NotNull() is the opt-in for an expression that cannot produce one. example/computed now shows both sides, which turned out to be the clearest argument for the default: is_overdue earns NotNull() with the leading due_date IS NOT NULL it already carried, and progress keeps the default because NULLIF is what it divides by. The declaration that had to be read carefully to see it was safe is now the one that says so.

Options 2 and 3 declined, both on your own reasoning. Inference is wrong in the unsafe direction wherever it is incomplete; requiring an explicit statement is breaking without being safer, since the safe value is now the default.

Two things beyond the change itself, since the report was really about where the failure lands:

• Proved against a real Postgres, not asserted. pgtest reads a correlated subquery that matches nothing into a *string and into a plain string, and requires the second to fail naming the NULL. Your point that a fixture where every row happens to match will pass is exactly why the second half is there.
• Broken on purpose per ADR-0016: with the default reverted, the schema test fails naming the column.

ADR-0041 gets a revision recording that the record never stated a default and inherited the wrong one, and docs/compatibility.md carries it under Will move with the mechanical edit — NotNull() on every computed column whose expression genuinely cannot be NULL. It is a breaking change to generated code, and leaving it off is the safe direction, so the migration can be done lazily.