GetDateTimeOffset() and GetSchemaTable() ignore LowCardinality/SimpleAggregateFunction wrappers: GetEffectiveClickHouseType only unwraps Nullable

[polyglotAI-bot]

Describe the bug

ClickHouseDataReader.GetEffectiveClickHouseType(int) unwraps only NullableType. LowCardinalityType and SimpleAggregateFunctionType are pure pass-throughs on the read path (Read delegates straight to UnderlyingType.Read, FrameworkType delegates to UnderlyingType.FrameworkType), so a LowCardinality(DateTime(tz)) or SimpleAggregateFunction(any, DateTime(tz)) column decodes to a perfectly normal DateTime value — but every consumer of GetEffectiveClickHouseType sees the wrapper instead of the date/time type underneath.

Two observable consequences:

1. GetDateTimeOffset(ordinal) throws InvalidCastException for wrapper-wrapped date/time columns, even though GetValue(ordinal) returns a DateTime, GetFieldType(ordinal) reports System.DateTime, and the timezone is right there in the column type — so the offset is fully knowable.
2. GetSchemaTable() silently drops NumericPrecision / NumericScale / ColumnSize for SimpleAggregateFunction-wrapped Decimal and DateTime64 columns (SchemaDescriber.cs:59 uses the same helper). This is the same class of gap that [backfill: ClickHouse/clickhouse-cs] GetSchemaTable() does not report DateTime64 precision (NumericScale) #438 / Report DateTime64/Time64 fractional scale in GetSchemaTable() #446 fixed for the unwrapped types; wrappers were missed.

SimpleAggregateFunction(...) is the case that matters most in practice: it needs no special settings and is the normal column type in AggregatingMergeTree tables. (LowCardinality(DateTime) additionally requires allow_suspicious_low_cardinality_types=1, but LowCardinality(Nullable(DateTime)) and the Decimal/DateTime64 schema cases do not.)

Note that these wrappers are already treated as transparent elsewhere in the driver — HttpParameterFormatter explicitly unwraps Nullable / LowCardinality / Variant before formatting — so the read-side accessors are the outlier.

Steps to reproduce

1. Run against any supported server (reproduced on 26.5.1.882).
2. SELECT anySimpleState(toDateTime('2024-01-15 12:30:45', 'Europe/Amsterdam'))
3. Call reader.GetDateTimeOffset(0) on the resulting ClickHouseDataReader.

Expected behaviour

GetDateTimeOffset() should return 2024-01-15T12:30:45.0000000+01:00 — the same value it already returns for the unwrapped and Nullable-wrapped forms of the identical column — rather than throwing. Likewise GetSchemaTable() should report the underlying type's precision/scale.

The server agrees the value is a timezone-carrying DateTime:

$ curl -s --data-binary @q.sql http://clickhouse:8123/
saf                                                            v
SimpleAggregateFunction(any, DateTime('Europe/Amsterdam'))      2024-01-15 12:30:45

Code example

using var reader = (ClickHouseDataReader)await connection.ExecuteReaderAsync(
    "SELECT anySimpleState(toDateTime('2024-01-15 12:30:45', 'Europe/Amsterdam'))");
reader.Read();

reader.GetDataTypeName(0);      // SimpleAggregateFunction(any, DateTime('Europe/Amsterdam'))
reader.GetFieldType(0);         // System.DateTime
reader.GetValue(0);             // 01/15/2024 12:30:45   <- decodes fine
reader.GetDateTimeOffset(0);    // throws InvalidCastException

Also reproducible on a real table column:

CREATE TABLE t (k UInt8, ts SimpleAggregateFunction(any, DateTime('Europe/Amsterdam')))
ENGINE AggregatingMergeTree ORDER BY k;
INSERT INTO t VALUES (1, toDateTime('2024-01-15 12:30:45', 'Europe/Amsterdam'));
SELECT ts FROM t;   -- reader.GetDateTimeOffset(0) throws

Error log

Probe over the whole matrix (controls first, then the wrapped forms). Each line is GetDataTypeName / GetFieldType / GetValue / GetDateTimeOffset:

[plain DateTime]                       DateTime('Europe/Amsterdam')                                       System.DateTime          01/15/2024 12:30:45  ->  2024-01-15T12:30:45.0000000+01:00
[Nullable(DateTime)]                   Nullable(DateTime('Europe/Amsterdam'))                             System.DateTime          01/15/2024 12:30:45  ->  2024-01-15T12:30:45.0000000+01:00
[DateTime64(3)]                        DateTime64(3, 'Europe/Amsterdam')                                  System.DateTime          01/15/2024 12:30:45  ->  2024-01-15T12:30:45.1230000+01:00
[SAF(any, DateTime)]                   SimpleAggregateFunction(any, DateTime('Europe/Amsterdam'))         System.DateTime          01/15/2024 12:30:45  ->  THREW InvalidCastException
[SAF(any, DateTime64(3))]              SimpleAggregateFunction(any, DateTime64(3, 'Europe/Amsterdam'))    System.DateTime          01/15/2024 12:30:45  ->  THREW InvalidCastException
[SAF(any, Nullable(DateTime))]         SimpleAggregateFunction(any, Nullable(DateTime('...')))            System.DateTime?         01/15/2024 12:30:45  ->  THREW InvalidCastException
[LowCardinality(DateTime)]             LowCardinality(DateTime('Europe/Amsterdam'))                       System.DateTime          01/15/2024 12:30:45  ->  THREW InvalidCastException
[LowCardinality(Nullable(DateTime))]   LowCardinality(Nullable(DateTime('Europe/Amsterdam')))             System.DateTime?         01/15/2024 12:30:45  ->  THREW InvalidCastException

GetSchemaTable() on the same connection, showing the second affected path:

[Decimal(18,4)]              DataType=ClickHouseDecimal  NumericPrecision=18  NumericScale=4  ColumnSize=8
[Nullable(Decimal(18,4))]    DataType=ClickHouseDecimal  NumericPrecision=18  NumericScale=4  ColumnSize=8
[SAF(sum, Decimal(18,4))]    DataType=ClickHouseDecimal  NumericPrecision=     NumericScale=   ColumnSize=-1     <- lost
[DateTime64(3)]              DataType=System.DateTime    NumericPrecision=     NumericScale=3  ColumnSize=-1
[SAF(any, DateTime64(3))]    DataType=System.DateTime    NumericPrecision=     NumericScale=   ColumnSize=-1     <- lost

Root cause

ClickHouse.Driver/ADO/Readers/ClickHouseDataReader.cs:115

internal ClickHouseType GetEffectiveClickHouseType(int ordinal)
{
    var type = RawTypes[ordinal];
    return type is NullableType nt ? nt.UnderlyingType : type;   // LowCardinality / SimpleAggregateFunction not unwrapped
}

Consumers that then fail to match:

• ClickHouseDataReader.cs:157 — GetDateTimeOffset requires is AbstractDateTimeType, otherwise throw new InvalidCastException().
• Utility/SchemaDescriber.cs:59 — matches DecimalType / DateTime64Type / Time64Type for precision/scale.

Suggested fix

Unwrap the transparent wrappers in GetEffectiveClickHouseType — loop while the type is NullableType, LowCardinalityType, or SimpleAggregateFunctionType, taking UnderlyingType each time (a loop, not a single step, since these nest: LowCardinality(Nullable(DateTime)), SimpleAggregateFunction(any, Nullable(DateTime))).

Contrast cases that must keep their current behaviour:

• GetFieldType / SchemaDescriber's DataType column already resolve correctly through FrameworkType delegation — don't double-unwrap there.
• AllowDBNull must stay keyed on the outer type being NullableType... except that it is arguably already wrong for LowCardinality(Nullable(T)); whether to change that is a separate call, and this issue does not ask for it.
• GetDataTypeName must keep returning the full wrapped type name (GetClickHouseType, not the effective type).
• Genuinely non-transparent wrappers (AggregateFunction, as opposed to SimpleAggregateFunction) must not be unwrapped.

A regression test belongs alongside the existing TimezoneHandlingTests / DateTimeTests coverage, asserting the wrapped forms return the same DateTimeOffset as the unwrapped control, plus a GetSchemaTable() case for SimpleAggregateFunction(sum, Decimal(18,4)).

Configuration

Environment

• Client version: main @ 7d3a2d9
• Language version: C# / net10.0
• .NET version: 10.0.203
• OS: Ubuntu 24.04 (linux-x64)

ClickHouse server

• ClickHouse Server version: 26.5.1.882 (official build, docker)
• ClickHouse Server non-default settings, if any: none, except allow_suspicious_low_cardinality_types=1 on the two LowCardinality(DateTime...) probes only — the SimpleAggregateFunction cases need no settings
• CREATE TABLE statements for tables involved: see the AggregatingMergeTree example above; the rest are settings-free SELECTs with no table
• Sample data: inline above

---

_{Found by automated analysis of this client while working on #515, then verified in a container against a live 26.5.1 server (not by inspection alone) — reported by @polyglotAI-bot. Filed as a separate issue rather than folded into the #515 fix, since it is a distinct defect.}