Code: 69. DB::Exception: Decimal value is too big

[drook]

Describe the bug
I have a cloud Clickhouse instance that I want to have a local copy from. So I exported the metadata (and changed the ReplicatedMergeTree() to MergeTree(), plus I exteended the Decimals() because the metadata as-is was giving me errors "Code: 69. DB::Exception: Too many digits (11 > 10) in decimal value" for Decimal(10,7)) and data from MCS instance and I'm trying to insert it locally, into CH v19.11.5.28_6 running on FreeBSD.

At some point I have an error: Code: 69. DB::Exception: Decimal value is too big.

When bisecting the dump I managed to find the troublesome line:

"2020-01-08","2020-01-08 04:19:14",1986136,0,23.7283008,90.4046029,30.00,0.00,0.00,0.00,47,0,0,0,0,"system","gps","STILL","PASSIVE",0,0,0,0,"['PASSIVE']","['gps']","[23.7283008]","[90.4046029]","[-2147467.79]","[]","[]","[]","[]","[]","[]","[]","[]","[11]",11,0,"-1","[]","[]",1,100,"{""locale"":""ru"",""appVersion"":""199882"",""ts"":""2020-01-08 01:19:14+03:00"",""now"":""2020-01-08 01:19:15""}",0

and I really can't see in the line above the values that would exceed Decimal64(12).

Would be also nice if clickhouse-client would print the troublesome like inself, that would save a huuuge amount of time.

The table metadata is as follows:

CREATE TABLE geo.history (
    `dt` Date,
    `ts` DateTime,
    `user` UInt32,
    `parent` UInt32,
    `latitude` Decimal64(12),
    `longitude` Decimal64(12),
    `accuracy` Decimal64(12),
    `speed` Decimal64(12),
    `course` Decimal64(12),
    `altitude` Decimal64(12),
    `batteryLevel` Int32,
    `steps` Int32,
    `turnovers` Int32,
    `isIgnored` UInt8,
    `isOffline` UInt8,
    `reason` String,
    `source` String,
    `activityType` String,
    `provider` String,
    `totalSatellitesCount` Int16,
    `notUsedSatellitesCount` Int16,
    `usedSatellitesCount` Int16,
    `timeToFirstFix` Int32,
    `locations.provider` Array(String),
    `locations.source` Array(String),
    `locations.latitude` Array(Decimal64(12)),
    `locations.longitude` Array(Decimal64(12)),
    `locations.accuracy` Array(Decimal64(12)),
    `lbs.cellId` Array(Int32), `lbs.countryCode` Array(Int16),
    `lbs.lac` Array(Int32), `lbs.level` Array(Int16),
    `lbs.operatorId` Array(Int16),
    `wifi.bssid` Array(String),
    `wifi.level` Array(Int16),
    `wifi.state` Array(Int8),
    `locations.age` Array(UInt32),
    `age` UInt32,
    `isCharging` UInt8,
    `failureReason` String,
    `sensors.id` Array(Int16),
    `sensors.value` Array(Int16),
    `deviceOS` Int8,
    `confidence` Int16,
    `debug` String,
    `threshold` UInt32 DEFAULT CAST(0, 'UInt32'))
    ENGINE = MergeTree PARTITION BY dt ORDER BY (dt, user) SETTINGS index_granularity = 8192

[alexey-milovidov]

Decimal64 has maximum 18 digits, because it's represented by Int64, and the maximum power of ten, represented within Int64 is 1000000000000000000, so the maximum decimal value with 12 digits after decimal point will be 999999.999999999999.

[alexey-milovidov]

And the value of locations.accuracy: -2147467.79 looks suspicious.
It looks like it indicated an error in your application as it looks like -2^31 / 1000 with some additional error.

[alexey-milovidov]

So, it's not a bug.
But we have to improve diagnostics.