SQL 插数报错 1292 - Truncated incorrect DECIMAL，查询正常。

OnTheRoad · 2022 年11 月 16 日 11:25

【 TiDB 使用环境】生产环境
【 TiDB 版本】TiDB v5.3.0、TiDB v6.1.2
【复现路径】

创建测试表及测试数据

CREATE TABLE `t_tbl02` (
  `贷款` decimal(40,2) DEFAULT NULL COMMENT '贷款'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


CREATE TABLE `t_test` (
  `企业名称` varchar(255) DEFAULT NULL,
  `信用代码` varchar(255) DEFAULT NULL,
  `年度` int(11) DEFAULT NULL,
  `yysr_2_ago` decimal(20,2) DEFAULT NULL,
  `yysr_1_ago` decimal(20,2) DEFAULT NULL,
  `yysr_current` decimal(20,2) DEFAULT NULL,
  `ldzc_1_ago` decimal(20,2) DEFAULT NULL,
  `ldzc_2_ago` decimal(20,2) DEFAULT NULL,
  `ldfz_1_ago` decimal(20,2) DEFAULT NULL,
  `ldfz_2_ago` decimal(20,2) DEFAULT NULL,
  `syzqy_1_ago` decimal(20,2) DEFAULT NULL,
  `gdzc_1_ago` decimal(20,2) DEFAULT NULL,
  `gdzc_2_ago` decimal(20,2) DEFAULT NULL,
  `wxzc_1_ago` decimal(20,2) DEFAULT NULL,
  `wxzc_2_ago` decimal(20,2) DEFAULT NULL,
  `cqjk_1_ago` decimal(20,2) DEFAULT NULL,
  `ch_1_ago` decimal(20,2) DEFAULT NULL,
  `ch_2_ago` decimal(20,2) DEFAULT NULL,
  `yszk_1_ago` decimal(20,2) DEFAULT NULL,
  `yszk_2_ago` decimal(20,2) DEFAULT NULL,
  `yfzk_1_ago` decimal(20,2) DEFAULT NULL,
  `yfzk_2_ago` decimal(20,2) DEFAULT NULL,
  `ljzj_1_ago` decimal(20,2) DEFAULT NULL,
  `dqjk_1_ago` decimal(20,2) DEFAULT NULL,
  `lrze_1_ago` decimal(20,2) DEFAULT NULL,
  `jlr_1_ago` decimal(20,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- 2.2 插入测试数据
INSERT INTO `t_test` (`企业名称`, `信用代码`, `年度`, `yysr_2_ago`, `yysr_1_ago`, `yysr_current`, `ldzc_1_ago`, `ldzc_2_ago`, `ldfz_1_ago`, `ldfz_2_ago`, `syzqy_1_ago`, `gdzc_1_ago`, `gdzc_2_ago`, `wxzc_1_ago`, `wxzc_2_ago`, `cqjk_1_ago`, `ch_1_ago`, `ch_2_ago`, `yszk_1_ago`, `yszk_2_ago`, `yfzk_1_ago`, `yfzk_2_ago`, `ljzj_1_ago`, `dqjk_1_ago`, `lrze_1_ago`, `jlr_1_ago`) VALUES ('PingCapTiDB', '91210102555343453T', 2017, 0.00, 0.00, 0.00, 2857907.48, 0.00, 11677684.98, 0.00, -8623888.37, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 2726700.50, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00);
INSERT INTO `t_test` (`企业名称`, `信用代码`, `年度`, `yysr_2_ago`, `yysr_1_ago`, `yysr_current`, `ldzc_1_ago`, `ldzc_2_ago`, `ldfz_1_ago`, `ldfz_2_ago`, `syzqy_1_ago`, `gdzc_1_ago`, `gdzc_2_ago`, `wxzc_1_ago`, `wxzc_2_ago`, `cqjk_1_ago`, `ch_1_ago`, `ch_2_ago`, `yszk_1_ago`, `yszk_2_ago`, `yfzk_1_ago`, `yfzk_2_ago`, `ljzj_1_ago`, `dqjk_1_ago`, `lrze_1_ago`, `jlr_1_ago`) VALUES ('PingCapTiDB', '91210102555343453T', 2018, 0.00, -0.01, 23300.97, 2992987.08, 2857907.48, 11742867.69, 11677684.98, -8739739.48, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 2726700.50, 2726700.50, 0.00, 0.00, 0.00, 0.00, -115851.11, -115851.11);
INSERT INTO `t_test` (`企业名称`, `信用代码`, `年度`, `yysr_2_ago`, `yysr_1_ago`, `yysr_current`, `ldzc_1_ago`, `ldzc_2_ago`, `ldfz_1_ago`, `ldfz_2_ago`, `syzqy_1_ago`, `gdzc_1_ago`, `gdzc_2_ago`, `wxzc_1_ago`, `wxzc_2_ago`, `cqjk_1_ago`, `ch_1_ago`, `ch_2_ago`, `yszk_1_ago`, `yszk_2_ago`, `yfzk_1_ago`, `yfzk_2_ago`, `ljzj_1_ago`, `dqjk_1_ago`, `lrze_1_ago`, `jlr_1_ago`) VALUES ('PingCapTiDB', '91210102555343453T', 2019, -0.01, 23300.97, 9265462.04, 2694362.03, 2992987.08, 13684986.55, 11742867.69, -10980483.39, 10141.13, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 2579575.50, 2726700.50, 0.00, 0.00, 0.00, 0.00, -2240743.91, -2240743.91);
INSERT INTO `t_test` (`企业名称`, `信用代码`, `年度`, `yysr_2_ago`, `yysr_1_ago`, `yysr_current`, `ldzc_1_ago`, `ldzc_2_ago`, `ldfz_1_ago`, `ldfz_2_ago`, `syzqy_1_ago`, `gdzc_1_ago`, `gdzc_2_ago`, `wxzc_1_ago`, `wxzc_2_ago`, `cqjk_1_ago`, `ch_1_ago`, `ch_2_ago`, `yszk_1_ago`, `yszk_2_ago`, `yfzk_1_ago`, `yfzk_2_ago`, `ljzj_1_ago`, `dqjk_1_ago`, `lrze_1_ago`, `jlr_1_ago`) VALUES ('PingCapTiDB', '91210102555343453T', 2020, 23300.97, 9265462.04, 14023278.00, 9336012.96, 2694362.03, 18636168.47, 13684986.55, -9290014.38, 10141.13, 10141.13, 0.00, 0.00, 0.00, 0.00, 0.00, 9103051.50, 2579575.50, 0.00, 0.00, 0.00, 0.00, 1690469.01, 1690469.01);

【遇到的问题：问题现象及影响】

问题 SQL

insert into t_tbl02(
贷款
)
SELECT
	CAST(xq1 AS DECIMAL(40,2))  贷款
FROM
	(
	SELECT 
		企业名称,
		信用代码,
		年度,
		CASE WHEN yysr_1_ago != 0 AND ldzc_1_ago+ldzc_2_ago-ldfz_1_ago-ldfz_2_ago != 0 THEN ROUND(yysr_1_ago*1.000*(1-lrze_1_ago*1.000/yysr_1_ago)*yysr_current/yysr_1_ago/(yysr_1_ago*2.000/(ldzc_1_ago+ldzc_2_ago-ldfz_1_ago-ldfz_2_ago)) - syzqy_1_ago+gdzc_1_ago+wxzc_1_ago-cqjk_1_ago - dqjk_1_ago,2) ELSE NULL END xq1
		,yysr_current 指数平滑预测值
		,gdzc_1_ago-gdzc_2_ago+ch_1_ago-ch_2_ago+yszk_1_ago-yszk_2_ago-yfzk_1_ago+yfzk_2_ago-ljzj_1_ago-jlr_1_ago xq2
		,gdzc_1_ago-gdzc_2_ago+wxzc_1_ago-wxzc_2_ago xq3
		,CASE WHEN (CASE WHEN yysr_1_ago != 0 AND ldzc_1_ago+ldzc_2_ago-ldfz_1_ago-ldfz_2_ago != 0 THEN yysr_1_ago*1.000*(1-lrze_1_ago*1.000/yysr_1_ago)*yysr_current/yysr_1_ago/(yysr_1_ago*2.000/(ldzc_1_ago+ldzc_2_ago-ldfz_1_ago-ldfz_2_ago)) - syzqy_1_ago+gdzc_1_ago+wxzc_1_ago-cqjk_1_ago - dqjk_1_ago ELSE NULL END) > 0 THEN 1 ELSE 0 END xqpg1
		,CASE WHEN gdzc_1_ago-gdzc_2_ago+ch_1_ago-ch_2_ago+yszk_1_ago-yszk_2_ago-yfzk_1_ago+yfzk_2_ago-ljzj_1_ago-jlr_1_ago > 0 THEN 1 ELSE 0 END xqpg2
		,CASE WHEN gdzc_1_ago-gdzc_2_ago+wxzc_1_ago-wxzc_2_ago > 0 THEN 1 ELSE 0 END xqpg3
	FROM t_test F 
	) G WHERE 企业名称  = 'PingCapTiDB' AND 年度= 2018;

报错的数值对应的应该是如下部分计算的中间结果 -237141453587819371373.322233045883214006350133

image2363×628 53.1 KB

yysr_1_ago*1.000*(1-lrze_1_ago*1.000/yysr_1_ago)*yysr_current/yysr_1_ago/(yysr_1_ago*2.000/(ldzc_1_ago+ldzc_2_ago-ldfz_1_ago-ldfz_2_ago))

问题现象

SELECT 查询语句没问题，加入 INSERT 时报错 1292 - Truncated incorrect DECIMAL value: '-237141453587819371373.322233045883214006350133'

在 MySQL 中测试，没有此种问题。

重现问题的 SQL 脚本。

t_test.sql (5.3 KB)

ealam_小羽 · 2022 年11 月 17 日 02:07

看上去和这个很像，237141453587819371373已经是21位，超长了

https://docs.pingcap.com/zh/tidb/stable/precision-math#decimal-数据类型的特性

OnTheRoad · 2022 年11 月 17 日 07:41

我看过这个文档，应该不是一回事。报错的数值是一个中间结果，并且在插入之前已经通过round函数取2位小数了。不存在数值太长的问题。

OnTheRoad · 2022 年11 月 17 日 07:47

不存在超长的情况。

目标表字段定义decimal(40,2)。报错的数值是个中间值（截图红色框线内的值），并不是插入目标表的最终值。
数值在插入前已经 round(value,2) 四舍五入了。
报错的数值直接插入目标表，可以成功插入。

我是咖啡哥 · 2022 年11 月 17 日 08:07

select查询出来的结果是：-237141453587810631633.84

报错的下面这段中间的一个结果。还没有执行round函数

SELECT 
    yysr_1_ago * 1.000 * (1 - lrze_1_ago * 1.000 / yysr_1_ago) * yysr_current / yysr_1_ago / (yysr_1_ago * 2.000 / (ldzc_1_ago + ldzc_2_ago - ldfz_1_ago - ldfz_2_ago)) - syzqy_1_ago + gdzc_1_ago + wxzc_1_ago - cqjk_1_ago - dqjk_1_ago xq1
FROM
    t_test F;

我是咖啡哥 · 2022 年11 月 17 日 08:17

这样看的话，应该是没有把完整的select执行完就报错了。。

OnTheRoad · 2022 年11 月 17 日 08:34

所以就很奇怪，还没有到插入目标表，就已经报错了。并且如果将ab/c/d改写成ab/(c*d)就可以避开这个报错。计算过程中，在内存存中间值时，超过数据类型的存储范围了？
但是，这个报错的中间值直接插入目标表，可以成功。

ealam_小羽 · 2022 年11 月 18 日 02:03

我这边实际验证了下，修改 sql_mode 后可以插入成功，参考图 1~图 3。
根据结果反推，是不是执行表达式的操作数据时，使用 t_test 里数据字段的 decimal(20,2) 的长度来计算数值（修改成 decimal(40,30) 确实出现了不一样的报错，应该是可以这样理解的）。
sql_mode 修改前插入失败：

sql_mode 清空插入成功：

修改 decimal(20, 2) 为 decimal(40,30)：

OnTheRoad · 2022 年11 月 18 日 08:38

我这边 sql_mode 为 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

OnTheRoad · 2022 年11 月 18 日 08:39

并且将 a*b/c/d 改写成 a*b/(c*d) 时，可以正常插入而无报错。

ealam_小羽 · 2022 年11 月 19 日 02:58

所以看上去好像就是上面的表达式中间的计算结果溢出的问题吧？

OnTheRoad · 2022 年11 月 29 日 06:08

在 MySQL 中，执行没有任何问题。暂未得到官方的确认，不清除是什么问题。希望此类问题引起官方重视。

ealam_小羽 · 2022 年12 月 6 日 11:32

这篇文章和这个问题很像
我试了一下是有改变的，但最后还有个 -8784829.055000000000000000000000000000 没弄出来，你再试试？

OnTheRoad · 2022 年12 月 8 日 07:17

不试了，太浪费精力了，已通过其他方式绕过了。毕竟是开源免费的，解决不了就想办法绕过。
在 MySQL 及 Oracle 都试过，没有此问题。