Power BI Direct Query Cannot Aggregate Nullable Columns

[slabko]

Power BI converts numeric types, such as Int32, into Decimal before running aggregate functions like SUM.

For example, to get the sum of all values in the volume column from the trades table, Power BI generates the following query:

SELECT sum(CAST(volume AS Decimal)) FROM trades;

This is likely done to avoid integer overflow, as Decimal typically supports a much larger range of values.

The Decimal type comes from SQLGetTypeInfo—specifically, it is the first type that matches ODBC's SQL_DECIMAL. Normally, SQL databases allow converting NULL to any type, because most types, including Decimal, are nullable. However, in the case of ClickHouse, this causes an issue if the volume column contains NULL values.

ClickHouse does not allow converting NULL to Decimal, only to Nullable(Decimal). Therefore, the query above results in an error, even though Power BI does see any problems with the generated query.

[slabko]

This problem has a simple solution: introduce Nullable(Decimal) in SQLGetTypeInfo as the most suitable type for SQL_DECIMAL. That works well, and instead of casting to Decimal, Power BI will produce the query we need:

SELECT sum(CAST(volume AS Nullable(Decimal))) FROM trades;

However, this approach has one major drawback: all projections that were written based on the fact that Power BI casts aggregations to Decimal will no longer work and must be rewritten so that these values are converted to Nullable(Decimal).

cast_keep_nullable could be another solution, however this option does now work for projections.

[slabko]

Fixed in https://github.com/ClickHouse/clickhouse-odbc/releases/tag/v1.5.2.20260217