【修复中】select cast ('18446744073709551614' as decimal(30,2)) 结果错误，数据精度丢失

Viewed 161
doris-webui 2.1 selectdb-enterprise-webui

cast('18446744073709551614' as DECIMALV3(30, 2))
18446744073709552000

结果错误，是什么原因？

Doris 版本：5.7.99

补充问题背景：我们这里使用 doris 作为数仓，通过 flink cdc 将 mysql 数据同步到 doris, 在 mysql 中有一个表的字段是: task_id bigint(11) unsigned NOT NULL DEFAULT '0' COMMENT '订单ID',
在 flink 中 将 bigint unsigned 映射为 decimal(20,0) https://nightlies.apache.org/flink/flink-cdc-docs-release-3.0/docs/connectors/legacy-flink-cdc-sources/mysql-cdc/#data-type-mapping
在 doris 中 task_id 字段,为 decimal(20,0)
然后就出现了，精度丢失问题：
select cast ( 2405140526081293291 as decimal(20,0))

目前的解决方案

decimal(20,0) 更改为: largeint

-- 2405140526081293291
select cast ( 2405140526081293291 as LARGEINT)

____5
3 Answers

1、show frontends\G;看看具体Doris的版本

2、将 MySQL bigint unsigned 映射是Flink CDC 3.0.x 的行为，mysql data type ->flink data type --> Doris data type。在 Flink-connector-doris 里面 MySQl bigint unsigned 是直接映射成 Doris largeint.

vinlee258

我用doris-2.1.2-rc04-b130df2488试了，没问题。

mysql [(none)]>show frontends\G
*************************** 1. row ***************************
              Name: fe_5c08a8f6_1a38_4941_98e6_b1a0756ed5e0
              Host: xx.xx.xx.xx
       EditLogPort: 19910
          HttpPort: 18930
         QueryPort: 19930
           RpcPort: 19920
ArrowFlightSqlPort: -1
              Role: FOLLOWER
          IsMaster: true
         ClusterId: 326842576
              Join: true
             Alive: true
 ReplayedJournalId: 19
     LastStartTime: 2024-05-15 15:59:38
     LastHeartbeat: 2024-05-15 16:00:29
          IsHelper: true
            ErrMsg: 
           Version: doris-2.1.2-rc04-b130df2488
  CurrentConnected: Yes
1 row in set (0.05 sec)

mysql [(none)]>select cast('18446744073709551614' as DECIMALV3(30, 2));
+--------------------------------------------------+
| cast('18446744073709551614' as DECIMALV3(30, 2)) |
+--------------------------------------------------+
|                          18446744073709551614.00 |
+--------------------------------------------------+
1 row in set (0.01 sec)
mysql [(none)]>select cast ( 2405140526081293291 as decimal(20,0)) ;
+-----------------------------------------------+
| cast(2405140526081293291 as DECIMALV3(20, 0)) |
+-----------------------------------------------+
|                           2405140526081293291 |
+-----------------------------------------------+
1 row in set (0.01 sec)

mysql [(none)]>select cast('18446744073709551614.12' as DECIMALV3(30, 2));
+-----------------------------------------------------+
| cast('18446744073709551614.12' as DECIMALV3(30, 2)) |
+-----------------------------------------------------+
|                             18446744073709551614.12 |
+-----------------------------------------------------+
1 row in set (0.02 sec)
jackteng11

select cast("18446744073709551614.12" as Decimalv3(30,2));

结果显示：
cast('18446744073709551614.12' as DECIMALV3(30, 2))
18446744073709552000

精度丢失

____5