ClickHouse.Client icon indicating copy to clipboard operation
ClickHouse.Client copied to clipboard

Incorrect conversion to Decimal type

Open Alex-ok2005 opened this issue 1 year ago • 1 comments

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?

Alex-ok2005 avatar May 29 '24 13:05 Alex-ok2005

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).

MikeAmputer avatar Jul 11 '24 16:07 MikeAmputer