SQL Insert Error 1292 - Truncated Incorrect DECIMAL, Query Works Fine

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

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

| username: OnTheRoad

[TiDB Usage Environment] Production Environment
[TiDB Version] TiDB v5.3.0, TiDB v6.1.2
[Reproduction Path]

  1. Create test table and test data
CREATE TABLE `t_tbl02` (
  `loan` decimal(40,2) DEFAULT NULL COMMENT 'loan'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `t_test` (
  `company_name` varchar(255) DEFAULT NULL,
  `credit_code` varchar(255) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `revenue_2_years_ago` decimal(20,2) DEFAULT NULL,
  `revenue_1_year_ago` decimal(20,2) DEFAULT NULL,
  `current_revenue` decimal(20,2) DEFAULT NULL,
  `current_assets_1_year_ago` decimal(20,2) DEFAULT NULL,
  `current_assets_2_years_ago` decimal(20,2) DEFAULT NULL,
  `current_liabilities_1_year_ago` decimal(20,2) DEFAULT NULL,
  `current_liabilities_2_years_ago` decimal(20,2) DEFAULT NULL,
  `equity_1_year_ago` decimal(20,2) DEFAULT NULL,
  `fixed_assets_1_year_ago` decimal(20,2) DEFAULT NULL,
  `fixed_assets_2_years_ago` decimal(20,2) DEFAULT NULL,
  `intangible_assets_1_year_ago` decimal(20,2) DEFAULT NULL,
  `intangible_assets_2_years_ago` decimal(20,2) DEFAULT NULL,
  `long_term_loans_1_year_ago` decimal(20,2) DEFAULT NULL,
  `inventory_1_year_ago` decimal(20,2) DEFAULT NULL,
  `inventory_2_years_ago` decimal(20,2) DEFAULT NULL,
  `accounts_receivable_1_year_ago` decimal(20,2) DEFAULT NULL,
  `accounts_receivable_2_years_ago` decimal(20,2) DEFAULT NULL,
  `accounts_payable_1_year_ago` decimal(20,2) DEFAULT NULL,
  `accounts_payable_2_years_ago` decimal(20,2) DEFAULT NULL,
  `accumulated_depreciation_1_year_ago` decimal(20,2) DEFAULT NULL,
  `short_term_loans_1_year_ago` decimal(20,2) DEFAULT NULL,
  `total_profit_1_year_ago` decimal(20,2) DEFAULT NULL,
  `net_profit_1_year_ago` decimal(20,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- 2.2 Insert test data
INSERT INTO `t_test` (`company_name`, `credit_code`, `year`, `revenue_2_years_ago`, `revenue_1_year_ago`, `current_revenue`, `current_assets_1_year_ago`, `current_assets_2_years_ago`, `current_liabilities_1_year_ago`, `current_liabilities_2_years_ago`, `equity_1_year_ago`, `fixed_assets_1_year_ago`, `fixed_assets_2_years_ago`, `intangible_assets_1_year_ago`, `intangible_assets_2_years_ago`, `long_term_loans_1_year_ago`, `inventory_1_year_ago`, `inventory_2_years_ago`, `accounts_receivable_1_year_ago`, `accounts_receivable_2_years_ago`, `accounts_payable_1_year_ago`, `accounts_payable_2_years_ago`, `accumulated_depreciation_1_year_ago`, `short_term_loans_1_year_ago`, `total_profit_1_year_ago`, `net_profit_1_year_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` (`company_name`, `credit_code`, `year`, `revenue_2_years_ago`, `revenue_1_year_ago`, `current_revenue`, `current_assets_1_year_ago`, `current_assets_2_years_ago`, `current_liabilities_1_year_ago`, `current_liabilities_2_years_ago`, `equity_1_year_ago`, `fixed_assets_1_year_ago`, `fixed_assets_2_years_ago`, `intangible_assets_1_year_ago`, `intangible_assets_2_years_ago`, `long_term_loans_1_year_ago`, `inventory_1_year_ago`, `inventory_2_years_ago`, `accounts_receivable_1_year_ago`, `accounts_receivable_2_years_ago`, `accounts_payable_1_year_ago`, `accounts_payable_2_years_ago`, `accumulated_depreciation_1_year_ago`, `short_term_loans_1_year_ago`, `total_profit_1_year_ago`, `net_profit_1_year_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` (`company_name`, `credit_code`, `year`, `revenue_2_years_ago`, `revenue_1_year_ago`, `current_revenue`, `current_assets_1_year_ago`, `current_assets_2_years_ago`, `current_liabilities_1_year_ago`, `current_liabilities_2_years_ago`, `equity_1_year_ago`, `fixed_assets_1_year_ago`, `fixed_assets_2_years_ago`, `intangible_assets_1_year_ago`, `intangible_assets_2_years_ago`, `long_term_loans_1_year_ago`, `inventory_1_year_ago`, `inventory_2_years_ago`, `accounts_receivable_1_year_ago`, `accounts_receivable_2_years_ago`, `accounts_payable_1_year_ago`, `accounts_payable_2_years_ago`, `accumulated_depreciation_1_year_ago`, `short_term_loans_1_year_ago`, `total_profit_1_year_ago`, `net_profit_1_year_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` (`company_name`, `credit_code`, `year`, `revenue_2_years_ago`, `revenue_1_year_ago`, `current_revenue`, `current_assets_1_year_ago`, `current_assets_2_years_ago`, `current_liabilities_1_year_ago`, `current_liabilities_2_years_ago`, `equity_1_year_ago`, `fixed_assets_1_year_ago`, `fixed_assets_2_years_ago`, `intangible_assets_1_year_ago`, `intangible_assets_2_years_ago`, `long_term_loans_1_year_ago`, `inventory_1_year_ago`, `inventory_2_years_ago`, `accounts_receivable_1_year_ago`, `accounts_receivable_2_years_ago`, `accounts_payable_1_year_ago`, `accounts_payable_2_years_ago`, `accumulated_depreciation_1_year_ago`, `short_term_loans_1_year_ago`, `total_profit_1_year_ago`, `net_profit_1_year_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);

[Encountered Problem: Problem Phenomenon and Impact]

  1. Problem SQL
insert into t_tbl02(
loan
)
SELECT
	CAST(xq1 AS DECIMAL(40,2))  loan
FROM
	(
	SELECT 
		company_name,
		credit_code,
		year,
		CASE WHEN revenue_1_year_ago != 0 AND current_assets_1_year_ago+current_assets_2_years_ago-current_liabilities_1_year_ago-current_liabilities_2_years_ago != 0 THEN ROUND(revenue_1_year_ago*1.000*(1-total_profit_1_year_ago*1.000/revenue_1_year_ago)*current_revenue/revenue_1_year_ago/(revenue_1_year_ago*2.000/(current_assets_1_year_ago+current_assets_2_years_ago-current_liabilities_1_year_ago-current_liabilities_2_years_ago)) - equity_1_year_ago+fixed_assets_1_year_ago+intangible_assets_1_year_ago-long_term_loans_1_year_ago - short_term_loans_1_year_ago,2) ELSE NULL END xq1
		,current_revenue exponential_smoothing_forecast
		,fixed_assets_1_year_ago-fixed_assets_2_years_ago+inventory_1_year_ago-inventory_2_years_ago+accounts_receivable_1_year_ago-accounts_receivable_2_years_ago-accounts_payable_1_year_ago+accounts_payable_2_years_ago-accumulated_depreciation_1_year_ago-net_profit_1_year_ago xq2
		,fixed_assets_1_year_ago-fixed_assets_2_years_ago+intangible_assets_1_year_ago-intangible_assets_2_years_ago xq3
		,CASE WHEN (CASE WHEN revenue_1_year_ago != 0 AND current_assets_1_year_ago+current_assets_2_years_ago-current_liabilities_1_year_ago-current_liabilities_2_years_ago != 0 THEN revenue_1_year_ago*1.000*(1-total_profit_1_year_ago*1.000/revenue_1_year_ago)*current_revenue/revenue_1_year_ago/(revenue_1_year_ago*2.000/(current_assets_1_year_ago+current_assets_2_years_ago-current_liabilities_1_year_ago-current_liabilities_2_years_ago)) - equity_1_year_ago+fixed_assets_1_year_ago+intangible_assets_1_year_ago-long_term_loans_1_year_ago - short_term_loans_1_year_ago ELSE NULL END) > 0 THEN 1 ELSE 0 END xqpg1
		,CASE WHEN fixed_assets_1_year_ago-fixed_assets_2_years_ago+inventory_1_year_ago-inventory_2_years_ago+accounts_receivable_1_year_ago-accounts_receivable_2_years_ago-accounts_payable_1_year_ago+accounts_payable_2_years_ago-accumulated_depreciation_1_year_ago-net_profit_1_year_ago > 0 THEN 1 ELSE 0 END xqpg2
		,CASE WHEN fixed_assets_1_year_ago-fixed_assets_2_years_ago+intangible_assets_1_year_ago-intangible_assets_2_years_ago > 0 THEN 1 ELSE 0 END xqpg3
	FROM t_test F 
	) G WHERE company_name  = 'PingCapTiDB' AND year= 2018;
  1. The value corresponding to the error should be the intermediate result of the following calculation -237141453587819371373.322233045883214006350133
    image
    image1380×366 67.8 KB
revenue_1_year_ago*1.000*(1-total_profit_1_year_ago*1.000/revenue_1_year_ago)*current_revenue/revenue_1_year_ago/(revenue_1_year_ago*2.000/(current_assets_1_year_ago+current_assets_2_years_ago-current_liabilities_1_year_ago-current_liabilities_2_years_ago))
  1. Problem Phenomenon

The SELECT query statement is fine, but adding INSERT reports an error 1292 - Truncated incorrect DECIMAL value: '-237141453587819371373.322233045883214006350133'

Testing in MySQL does not have this issue.

  1. SQL script to reproduce the problem.

t_test.sql (5.3 KB)

2
| username: ealam_小羽 | Original post link

It looks very similar to this. 237141453587819371373 is already 21 digits long, which is too long.

Image
Image1163×747 104 KB

3
| username: OnTheRoad | Original post link

I have read this document, and it should not be the same issue. The error value is an intermediate result, and it has already been rounded to 2 decimal places using the round function before insertion. There is no issue with the value being too long.

4
| username: OnTheRoad | Original post link

There is no situation of excessive length.

  1. The target table field is defined as decimal(40,2). The error value is an intermediate value (the value within the red box in the screenshot), not the final value inserted into the target table.
  2. The value has already been rounded to two decimal places using round(value,2) before insertion.
  3. The error value can be directly inserted into the target table successfully.
5
| username: 我是咖啡哥 | Original post link

The result of the select query is: -237141453587810631633.84

The following segment that caused the error is one of the results. The round function has not been executed yet.

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;

image

6
| username: 我是咖啡哥 | Original post link

From this perspective, it seems that the complete select execution was not finished before the error occurred.

7
| username: OnTheRoad | Original post link

So it’s very strange, it reports an error even before inserting into the target table. However, if you rewrite ab/c/d as ab/(c*d), you can avoid this error. During the calculation process, when storing intermediate values in memory, it exceeds the storage range of the data type, right?
But, if you directly insert this intermediate value into the target table, it succeeds.

8
| username: ealam_小羽 | Original post link

I have verified it on my end, and after modifying sql_mode, the insertion was successful. Refer to Figures 1 to 3.

Based on the results, it seems that when executing the expression operation data, the length of the decimal(20,2) field in t_test is used to calculate the value (modifying it to decimal(40,30) indeed resulted in a different error, which should be understood this way).

Insertion failed before modifying sql_mode:
Image
Image

Insertion succeeded after clearing sql_mode:
Image

Modified decimal(20,2) to decimal(40,30):
Image

9
| username: OnTheRoad | Original post link

My sql_mode here is ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION.

10
| username: OnTheRoad | Original post link

And when rewriting a*b/c/d to a*b/(c*d), it can be inserted normally without any errors.

11
| username: ealam_小羽 | Original post link

So it seems like the issue is an overflow in the intermediate calculation result of the above expression, right?

12
| username: OnTheRoad | Original post link

In MySQL, there are no issues when executing. We have not yet received official confirmation, so it is unclear what the problem is. Hopefully, this kind of issue will get official attention.

13
| username: ealam_小羽 | Original post link

This article is very similar to this question.
I tried it and there were some changes, but in the end, there was still a -8784829.055000000000000000000000000000 that didn’t come out. Can you try again?

14
| username: OnTheRoad | Original post link

Not trying anymore, it’s too much effort. I’ve already found a workaround. After all, it’s open-source and free, so if it can’t be solved, find a way around it.
I’ve tried both MySQL and Oracle, and there’s no such issue.