[BUG] Cannot load data using DataTable.Load due to "DataSet does not support System.Nullable<>" error

[Hermholtz]

Current Behavior

Using the following code:

using var connection = new ClickHouseConnection(
  "Host=127.0.0.1;Protocol=http;Port=8123;Username=default;Password=password;Database=default");

string sql = "SELECT * from system.tables";

using var command = connection.CreateCommand();
command.CommandText = sql;

using var reader = command.ExecuteReader();

var table = new DataTable();

try {
	table.Load(reader);
}
catch (Exception e)
{
	Console.WriteLine(e);

	var nullableColumns = reader.GetSchemaTable().Rows.Cast<DataRow>().Where(r => ((Type)r["DataType"]).Name.StartsWith("Nullable"));

	foreach (var row in nullableColumns)
	{
		Console.WriteLine($"ColumnName:{row["ColumnName"]}, DataType:{row["DataType"]}, AllowDBNull:{row["AllowDBNull"]}, ProviderType:{row["ProviderType"]}");
	}
}

I am getting NotSupportedException with the message "DataSet does not support System.Nullable<>" and the following stack trace:

   at System.Data.DataColumn..ctor(String columnName, Type dataType, String expr, MappingType type)
   at System.Data.Common.DataColumnMapping.CreateDataColumnBySchemaAction(String sourceColumn, String dataSetColumn, DataTable dataTable, Type dataType, MissingSchemaAction schemaAction)
   at System.Data.Common.DataColumnMapping.GetDataColumnBySchemaAction(String sourceColumn, String dataSetColumn, DataTable dataTable, Type dataType, MissingSchemaAction schemaAction)
   at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
   at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.FillMappingInternal(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
   at UserQuery.Main(), line 14

The issue is that DataTable.Load incorrectly fills the data table column data types for nullable columns, i.e. it puts "Nullable`1[System.UInt64]". It should just put the base type there.

I attribute it to ClickHouse driver, as it does not occur with other drivers.

Expected Behavior

I would expect it to just properly fill the data table.

Steps To Reproduce

I used .NET 8
Installed ClickHouse.Client from NuGet
Used the code above.

Environment

- OS: Windows 10 x64
- Runtime: net8.0

Anything else?

No response

[DarkWanderer]

Hi,

Can you try using ClickHouseDataAdapter?

        using var command = connection.CreateCommand();
        using var adapter = new ClickHouseDataAdapter();
        command.CommandText = sql;
        adapter.SelectCommand = command;
        var dataTable = new DataTable();
        adapter.Fill(dataTable);
        return dataTable;

Or, alternatively, ExecuteDataTable extension method which this library provides:

using ClickHouse.Client.Utility;

connection.ExecuteDataTable("SELECT * from system.tables");

[Hermholtz]

Thank you for the workaround, it works the way you proposed, and the ExecuteDataTable is a great addition!
You certainly need to document that, otherwise it's hard to discover all the goodies you put there.

It doesn't resolve the issue, though. The issue I face (and I think many other could have this as well) is that we've got an existing C#/.NET-based reporting solution that works with pure ADO.NET object model. It allows to select the source of data (by selecting a driver and providing property values), transform and visualize data, similarly to what Power BI does, but it's a custom one. Therefore I do not have the ability to change its code. If I had it, I would perhaps make and exception for ClickHouse.

It would therefore be the best option if the ClickHouse.Client worked properly using the usual ADO.NET object model and typical code such as in my example above. That will certainly make the CH client more applicable. Thank you in advance for fixing this.

The simplest query that shows the issue is SELECT name, total_rows from system.tables as the total_rows is a nullable column.

[DarkWanderer]

Are you able to check if 7.8.3-aplha1 fixes your issue?

[Hermholtz]

The 7.8.3-aplha1 version fixes the issue. I tried various data types:

string sql = "select toNullable(1), toNullable(1_000_000_000), toNullable(3.14), toNullable(now()), toNullable(True), toNullable('mystr')";

and it doesn't throw exception anymore.

Big THANK YOU for the fix! Hope you'll be able to release a non-alpha version soon!