diving into ClickHouse for my new DB setup.
Read UPDATE in ClickHouse, trying to keep it super minimal (<0.1% on 2 billion rows).
Here's the kicker: I'm trying to update a Decimal(19,5) field from 19.39999 to 19.4, but it's pulling a disappearing act. Any idea how to tweak settings to make it behave?
Or should I just go old school and delete-insert if the difference is < X? What's this mysterious X that's playing hard to get? Any intel from the documentation sleuths out there?
An Example:
CREATE TABLE TestTable (ID Int64, Name String, Value Float64, val Decimal(19,5), Date Date ) ENGINE = MergeTree() ORDER BY ID;
INSERT INTO TestTable (ID, Name, Value, val, Date) VALUES (1, 'John Doe', 42.5, 19.39999, '2023-01-01');
ALTER TABLE TestTable UPDATE val = 19.4 WHERE ID = 1
SELECT * FROM TestTable
Query id: 0568d5fb-b182-4a3d-967d-910f2ecce942
┌─ID─┬─Name─────┬─Value─┬──────val─┬───────Date─┐
│ 1 │ John Doe │ 42.5 │ 19.39999 │ 2023-01-01 │
└────┴──────────┴───────┴──────────┴────────────┘
1 row in set. Elapsed: 0.001 sec.
Untortunatelly 19.4 is float64 type value by defaultm which have internal representation related to IEE 754 https://en.wikipedia.org/wiki/IEEE_754 AS 19.399999999(9) (in period), which will lose precision during conversion to Decimal, and show as 19.4 because it's a string during output
Look details https://fiddle.clickhouse.com/859a26b7-4182-4d07-bd88-79448ea213ab as a trick try to use
ALTER TABLE TestTable UPDATE val = toDecimal32(toString(19.4),5) WHERE ID = 1