DarkWanderer / ClickHouse.Client

.NET client for ClickHouse
MIT License
315 stars 62 forks source link

Incorrect conversion to Decimal type #484

Open Alex-ok2005 opened 4 months ago

Alex-ok2005 commented 4 months ago
CREATE TABLE default.Sum
(
    id Int32,                    
    Total UInt32,                       -- payment amount *100
)
ENGINE = MergeTree
PRIMARY KEY (Clinic_id);

INSERT INTO default.Sum (1, 11000);

The following query returns the correct data:

SELECT
    id, 
    divide(toDecimal32(Total, 2), 100) AS total,
    toTypeName(toDecimal32(Total, 2), 100) AS type_name 
FROM default.Sum;   

|id|total  |type_name    |
+--+-------+-------------+
| 1| 110.00|Decimal(9, 2)|

But when converting data in the NET app, an error occurs.

var Total = row.Field<decimal>("total");

System.InvalidCastException: "The specified cast is not valid."

Converting on the Clickhouse side to other toDecimal64 and toDecimal128 types didn't help either.

Only such a code turned out to be working

var Total = Convert.ToDecimal(row.Field<object>("total"));

But I would like to receive data without double type conversion. Or am I doing something wrong?

MikeAmputer commented 2 months ago

It is ClickHouseDecimal by default, so you can use the explicit conversion operator:

var total = (decimal) row.Field<ClickHouseDecimal>("total")

If you want it to be a .Net decimal in the DataTable, you should add UseCustomDecimals=false to the connection string.

@DarkWanderer it is said in the wiki that UseCustomDecimals is false if omitted, but it seems to be true (link).