Support Int128 and UInt128 for HUGEINT and UHUGEINT in 2.0: reading, parameters, the appender and the default type #370

A DuckDB HUGEINT is exactly an Int128, and a UHUGEINT is a UInt128. Since 1.5.6.1 the library converts through these types internally, but they aren't supported as .NET types: reading as Int128 throws, the mapped appender refuses an Int128 property, and a parameter is sent as text. This issue covers support for Int128 and UInt128 in reading, parameters and the appender.

What happens today (checked on 1.5.6.1 / develop)

Reading

• NumericConverter.Convert: add Int128.CreateChecked and UInt128.CreateChecked. A HUGEINT read as Int128 (and a UHUGEINT as UInt128) is then returned as it is. Every other pair is a checked conversion, so a UHUGEINT above Int128.MaxValue, or a negative HUGEINT read as UInt128, gives the reader's usual InvalidCastException.
• TypeExtensions.IsIntegralNumericType<T>: add both types, so BigIntegerVectorDataReader and NumericVectorDataReader take the typed path. VARINT and the narrower integer columns (TINYINT … UBIGINT) then read as Int128/UInt128 too.
• NullableHandler in VectorDataReaderBase: add Known<Int128>() and Known<UInt128>(), so Int128? has a compiled reader under Native AOT.
• The non-generic GetValue(offset, Type), which reads list items, struct fields and map values, uses Convert.ChangeType, which can't produce an Int128 (it isn't IConvertible). BigIntegerVectorDataReader.GetValue and NumericVectorDataReader.GetValue need their own case for the two types.
• Decided:
  • REAL/DOUBLE as Int128/UInt128: not supported. Refused with the usual "Cannot cast" message, as a HUGEINT read as float/double is refused today.
  • DECIMAL as Int128/UInt128: supported, like BigInteger today. The exact value when the fractional part is zero, InvalidCastException otherwise, and InvalidCastException when the value doesn't fit the type (a negative value as UInt128). DecimalVectorDataReader handles BigInteger in both GetValidValue and GetValue, and Int128/UInt128 need the same; every DECIMAL fits an Int128, so it can divide by the power of ten as an Int128, as the wide decimal read already does.
• Unchanged: GetValue() still returns BigInteger for HUGEINT and UHUGEINT, and GetProviderSpecificValue() still returns DuckDBHugeInt / DuckDBUHugeInt.

Parameters

• DuckDBTypeMap: map Int128 and UInt128 to a DbType (VarNumeric, as for BigInteger, or no DbType and a direct case in the converter).
• ClrToDuckDBConverter: bind with duckdb_create_hugeint / duckdb_create_uhugeint from the two halves, for a HUGEINT/UHUGEINT target and for a parameter with no target type. No text, and no BigInteger in between.
• Lists: Int128[] / List<Int128> as list parameters.

Appender

• AppendValue(Int128?) and AppendValue(UInt128?) on IDuckDBAppenderRow and DuckDBAppenderRow, writing the two halves directly. Adding members to a public interface breaks other implementations of it, and a new overload can make existing calls ambiguous or change which overload they bind to. That is why this goes into 2.0; the overloads still need checking for calls that become ambiguous.
• The mapped appender: accept Int128 for HUGEINT, and UInt128 for UHUGEINT (and possibly each for the other column, with a range check, as BigInteger is accepted for both).
• Function results: scalar and table function results of type Int128/UInt128 go through the same writers, so they're worth covering in the same change.

Tests and docs

• Cases in NumericReaderConversionTests:
  • the full range of each type, both overflow directions, and widening from BIGINT/UBIGINT;
  • VARINT in range and out of range;
  • Int128? with a NULL;
  • List<Int128>, a struct field and a map value;
  • DOUBLE refused;
  • DECIMAL of each storage width (16, 32, 64 and 128 bit) with and without a fraction, and negative as UInt128.
• Parameter and appender tests, for each column type, that compare with values DuckDB produces.
• docs/type-mapping.md: add Int128 and UInt128 to the HUGEINT and UHUGEINT rows.

Default type of HUGEINT and UHUGEINT (proposed, to decide)

Make Int128 the default .NET type of HUGEINT and UInt128 that of UHUGEINT, instead of BigInteger:

• Why:
  • It's the exact type of the column; BigInteger is wider than the column can hold.
  • GetValue() already goes through Int128 to build the BigInteger, and a large BigInteger allocates an array for its digits on top of the box.
  • A default type can change only in a major version.
  • It matches the rest of this issue, so the type is the same for reading, parameters and the appender.
• What changes:
  • GetValue(), ExecuteScalar(), the indexer, GetFieldType(), the schema table and DataTable columns.
  • sum() of INTEGER or BIGINT returns HUGEINT, so (BigInteger)command.ExecuteScalar() after a SUM throws. Use (Int128) or GetFieldValue<BigInteger>() instead. This is the first item for the upgrade guide.
  • Neither type implements IConvertible, so Convert.ToInt64(scalar) fails today and still fails afterwards.
• Stays the same:
  • VARINT stays BigInteger.
  • GetFieldValue<BigInteger>() keeps working for all three.
  • The provider-specific types stay DuckDBHugeInt / DuckDBUHugeInt.
• To check first: how Dapper and other mappers handle an Int128 value for a long or BigInteger property.

Release

DuckDB.NET 2.0, so the AppendValue overloads can be added to IDuckDBAppenderRow, and so that parameters change from text to typed values and the default type changes in a major version.