ClickHouse exception, message: Code: 407. DB::Exception: Decimal math overflow 十进制溢出错误

记一次ClickHouse使用中遇到的问题

报错：

org.springframework.jdbc.UncategorizedSQLException:
### Error querying database.  Cause: ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, message: Code: 407. DB::Exception: Decimal mathoverflow: while executing 'FUNCTION multiply(toDecimal32OrZero(xxx, 0) :: 7, 100 :: 3) -> multiply(toDecimal32OrZero(xxx, 0), 100) Decimal(9, 0): 5'. (DECIMAL_OVERFLOW) (version  (official build))
### The error may exist in class path resource [mapper/ClickHouse/com/rs/smp/speedResult/SpeedResultDescCkMapper.xml]
### The error may involve com.rs.smp.speedResult.dao.clickHouse.SpeedResultDescCkDao.queryUpload-Inline
### The error occurred while setting parameters
### SQL: SELECT count(0) FROM tbl_day_speed_aaa_list WHERE intDivOrZero(toDecimal32OrZero(xxx, 0) * 100, toDecimal32OrZero(replace(broadwidth, 'M', ''), 0)) >= 90
### Cause: ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, message: Code: 407. DB::Exception: Decimal math overflow: while executing'FUNCTION multiply(toDecimal32OrZero(xxx, 0) :: 7, 100 :: 3) -> multiply(toDecimal32OrZero(xxx, 0), 100) Decimal(9, 0) : 5'. (DECIMAL_OVERFLOW) (version  (official build))
; uncategorized SQLException; SQL state [null]; error code [1002]; ClickHouse exception, message: Code: 407. DB::Exception: Decimal math overflow: while executing'FUNCTION multiply(toDecimal32OrZero(xxx, 0) :: 7, 100 :: 3) -> multiply(toDecimal32OrZero(xxx, 0), 100) Decimal(9, 0) : 5'. (DECIMAL_OVERFLOW) (version  (official build)); nested exception is ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, message: Code: 407. DB::Exception: Decimal math overflow: while executing 'FUNCTION multiply(toDecimal32OrZero(xxx, 0) :: 7, 100 :: 3) -> multiply(toDecimal32OrZero(xxx, 0), 100) Decimal(9, 0) : 5'. (DECIMAL_OVERF

问题原因：

clickhoue表中数据类型为String，正常数据为10000以内的数字，但存在异常数据例如将手机号错入进了该字段。导致toDecimal32OrZero(xxx, 0)函数将String转为数字进行计算时出现了十进制溢出错误。

解决方式：

toDecimal32OrZero函数 替换为 toDecimal64OrZero函数

记俩篇常用ClickHouse函数的博客：

ClickHouse 函数极简教程:https://blog.csdn.net/universsky2015/article/details/123587836
clickhouse 数据类型转换:https://blog.csdn.net/weixin_38424594/article/details/123545926