ClickHouse / ClickHouse/clickhouse-cs
GetDateTimeOffset() and GetSchemaTable() ignore LowCardinality/SimpleAggregateFunction wrappers: GetEffectiveClickHouseType only unwraps Nullable
- Dominant language
- C#
- Stars
- 94
- Forks
- 22
- Avg merge
- 11h 26m
- Merged PRs (30d)
- 22
Description
### 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 #438 / #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
```csharp
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:
```sql
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`
```csharp
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 `SELECT`s 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.
Contributor guide
Research direction
Start in ClickHouse.Driver/ADO/Readers/ClickHouseDataReader.cs at GetEffectiveClickHouseType and inspect its callers, especially GetDateTimeOffset. Review Utility/SchemaDescriber.cs and the existing TimezoneHandlingTests and DateTimeTests. Done means nested transparent wrappers produce the same DateTimeOffset as unwrapped types, and GetSchemaTable() preserves precision and scale for wrapped Decimal and DateTime64 columns.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- clickhouse, csharp
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Quiet
- Clarity
- Clearly specified
- Newbie friendliness
- 78/100