Reject database NULL for non-nullable model columns consistently across all read paths

[bazer]

Reject database NULL for non-nullable model columns consistently across all read paths

Summary

DataLinq does not enforce its model nullability contract consistently across its SQL read paths.

When a database column contains NULL while the DataLinq model declares the property/column as non-nullable, the
failure can currently surface in three materially different ways:

1. the canonical v0.9 materialization path throws a column-aware decoding/materialization exception,
2. legacy RowData(IDataLinqDataReader, ...) paths store null and a later generated property getter throws the
   misleading ArgumentNullException: Value cannot be null. (Parameter 'columnIndex'), or
3. direct IDataLinqDataReader.GetValue<T>(...) calls return default(T) for SQL NULL, silently changing values such
   as char, DateTime, numeric types, or bool into valid-looking CLR defaults.

The second behavior makes a schema/model nullability mismatch look like an invalid column ordinal or damaged row
shape. The third behavior can hide the mismatch completely and corrupt application semantics.

Verified against commit 3854927d323f55d38e4d9c1f5b0d98441accd071 on branch v0.9. The misleading getter behavior
also exists in DataLinq 0.7.1.

Current behavior

Immutable.GetValue(int columnIndex) still throws using the index parameter name:

protected object GetValue(int columnIndex) =>
    GetNullableValue(columnIndex) ?? throw new ArgumentNullException(nameof(columnIndex));

The index is valid in this failure mode. The value read from that column is null, so columnIndex is not the bad
argument and the exception sends diagnosis in the wrong direction.

Both the MySQL/MariaDB and SQLite readers currently return default before considering the column's declared
nullability or requested CLR type:

if (IsDbNull(ordinal))
    return default;

The public RowData(IDataLinqDataReader, ...) constructor uses these generic reader methods directly and does not
validate the value against ColumnDefinition.Nullable or ValueProperty.CsNullable before storing it.

The newer ProviderRowDecoder/CanonicalProviderValueRow path is better: it represents SQL NULL as null, checks
the column metadata, and includes table.column plus expected provider type in the resulting diagnostic. However,
not every read path uses that boundary. For example, the current code still constructs RowData directly from a
reader in legacy query, transaction, select, and cache-loading paths.

Minimal reproduction shape

1. Create a table with a nullable column, for example status CHAR(1) NULL or created_at DATETIME NULL.
2. Map it to a generated DataLinq property without [Nullable], for example char Status or
   DateTime CreatedAt.
3. Insert a row where the column is SQL NULL.
4. Read the row through each supported model-loading path and through IDataLinqDataReader.GetValue<T>.
5. Access the generated non-nullable property.

Observed historically on 0.7.1 through normal model loading:

System.ArgumentNullException: Value cannot be null. (Parameter 'columnIndex')

Observed through direct generic reader access for value types: default(T) is returned, for example \0 for
char or DateTime.MinValue for DateTime.

Expected behavior

SQL NULL for a model column declared non-nullable should fail immediately at the shared reader/materialization
boundary on every provider and every public model-loading path.

The failure should:

• use a public, DataLinq-owned exception type that consumers can catch without matching message text,
• identify the database table and column and, when available, the generated model property,
• state that SQL returned NULL while the DataLinq model declares the value non-nullable,
• include a non-sensitive logical source/read-path label,
• suggest checking schema drift or marking the model property nullable,
• not include the actual database value or connection details.

Direct generic reader behavior should be explicit as well: return null only when the requested/model contract permits
it; otherwise throw the same focused exception rather than returning a valid-looking CLR default.

Suggested implementation direction

• Route all model-row construction through the shared ProviderRowDecoder and ProviderRowMaterializer contract, or
  apply equivalent validation inside the remaining RowData(IDataLinqDataReader, ...) constructor paths.
• Make the focused decoding/materialization exception public, or expose a public stable superclass containing table,
  column, property, source, and mismatch kind.
• Replace the ArgumentNullException(nameof(columnIndex)) fallback. A null database value is not an invalid index
  argument.
• Define and document the null contract of IDataLinqDataReader.GetValue<T> for reference types, nullable value types,
  and non-nullable value types.

Acceptance criteria

• MySQL, MariaDB, and SQLite produce the same focused failure class and equivalent safe context for SQL NULL read
  against a non-nullable model column.
• Coverage includes non-nullable reference and value-type properties.
• Coverage includes normal query roots, read-only/raw-command model loading, transaction loading, relation/cache
  loading, direct projections, and direct IDataLinqDataReader.GetValue<T> access where those are supported APIs.
• No path reports columnIndex as the null argument for this mismatch.
• No path silently converts SQL NULL to default(T) for a non-nullable model value.
• Nullable model columns continue to materialize SQL NULL as null.
• Diagnostics do not expose the database value or connection details.

Related prevention already available in v0.9

datalinq validate already compares live provider metadata with generated model metadata, including column
nullability. That is valuable CI prevention, but it does not replace deterministic runtime behavior because deployed
schema drift and legacy databases can still occur.

[bazer]

I traced this against current master (66c73700) as well as the issue's verification commit (3854927d). The issue is valid and should be treated as a high-priority correctness/data-integrity bug.

Confirmed failure modes

Two materially different bugs coexist:

1. Legacy model-loading paths call RowData(IDataLinqDataReader, ...), store SQL NULL as null, and fail only when a generated immutable getter calls Immutable.GetValue(int). That produces the misleading ArgumentNullException(nameof(columnIndex)) even though the ordinal is valid.
2. Both provider implementations return default(T) immediately from GetValue<T> when IsDbNull(ordinal) is true. For non-nullable value types this silently produces values such as 0, false, DateTime.MinValue, or '\0'.

The affected legacy surfaces still include:

• Select.ReadRows / ReadFirstRow
• read-only raw query and command loading
• transaction query and command loading
• provider-sensitive relation/cache paths that do not use neutral canonical source loading
• direct IDataLinqDataReader.GetValue<T> calls

There is also an additional bypass worth including in the fix: KeyFactory.GetKey(IDataLinqDataReader, ...) reads simple integer and string keys through GetInt32 / GetString, so hardening only generic GetValue<T> would not cover every key-selection path.

Current-master correction

The direct-projection part of the issue is now partly stale. PR #14 changed model-bound scalar, row, and compatible grouped-key projections to use ProviderRowDecoder plus ProviderRowMaterializer. Those paths now reach the canonical nullability validation on master. Aggregate values that are not model columns correctly remain governed by projection-result nullability.

Recommended implementation

Introduce a public DataLinq-owned exception, e.g. DataLinqNullabilityMismatchException, with stable non-sensitive context:

• table name
• column name
• generated property name
• logical source/read-path label
• mismatch kind
• expected/requested CLR type where applicable

Back it with one shared internal null-contract helper used by:

• ProviderRowDecoder
• ProviderRowMaterializer
• SQLite and MySQL/MariaDB GetValue<T>
• legacy RowData reads
• column-aware key-selection paths

The contract should evaluate three distinct layers:

1. database metadata: ColumnDefinition.Nullable
2. model metadata: ValueProperty.CsNullable
3. requested CLR type: whether T can represent null

SQL NULL may be returned only when every relevant layer permits it. Mismatch precedence should be database/schema, then model property, then requested CLR type. The new focused exception must be excluded from the current decoder/materializer catch-and-wrap filters so it remains publicly catchable.

I would not begin by forcing every legacy RowData call through DecodeFullRow. RowData supports partial selections, while CanonicalProviderValueRow deliberately requires a complete table row. A missing/unselected cell is not SQL NULL; conflating those states would create a new correctness bug.

The Immutable.GetValue(int) fallback should also be replaced with a table/column/property-aware failure and must never identify columnIndex as the null argument.

Semantic guardrails

The fix must preserve these distinctions:

• an unselected partial-row cell is missing, not SQL NULL
• a null-extended outer-join source is an absent joined row, not schema drift
• a converter returning null from a non-null provider value is a converter-contract failure
• nullable aggregate results follow projection-result nullability, not the source model property's nullability
• nullable model columns continue to materialize SQL NULL as null

Required coverage

At minimum:

• SQLite, MySQL, and MariaDB
• non-nullable reference and value properties
• schema-declared non-nullable and model-declared non-nullable mismatch kinds
• nullable-model positive controls
• GetValue<object>, GetValue<int>, and GetValue<int?>
• read-only query/command and transaction query/command paths
• Select.ReadRows / ReadFirstRow
• canonical and legacy cache-loading shapes
• integer/string key fast paths
• scalar and constructor projections as regression coverage
• diagnostics proving that values and connection details are never rendered

In short: this is a real architectural seam, not just a bad exception message. The appropriate fix is a shared, public nullability-contract failure at every column-aware reader boundary, while keeping partial rows, joins, aggregates, and converter failures semantically distinct.