Issue with TiFlash Decimal Calculation Precision

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

Original topic: tiflash decimal 运算精度问题

| username: bindsang

[TiDB Usage Environment] Production Environment / Testing
[TiDB Version] 6.1.4
[Reproduction Path]
Create a table

CREATE TABLE dec (
  dec1 DECIMAL(28,4),
  dec2 DECIMAL(28,4)
)

Insert a record

INSERT INTO dec (dec1, dec2) 
VALUES (176.9900, 176.1100)

Query 1

WITH kc AS (
  SELECT CAST(IFNULL(dec1,0) - IFNULL(dec2,0) AS DECIMAL(28,2)) book_sum_1
  FROM test.dec
)

Query 2

SELECT * FROM kc;
SELECT CAST(IFNULL(dec1,0) - IFNULL(dec2,0) AS DECIMAL(28,2)) book_sum_1
FROM test.dec mi;

When TiFlash is not enabled, the results of the above two queries are correct, both being 0.88.

[Encountered Problem]
Enable TiFlash

ALTER TABLE dec SET tiflash replica 3;
  1. The expected result of Query 1 is 0.88, but the result becomes 0.89; Query 2 returns the expected 0.88.
  2. If the CAST AS after DECIMAL(28,2) is changed to DECIMAL(28,1), the expected result of Query 1 is 0.9, but the result returned is 0.8, while Query 2 correctly returns 0.9.
| username: weixiaobing | Original post link

You can refer to this: TiFlash 兼容性说明 | PingCAP 归档文档站

| username: Meditator | Original post link

  1. Follow the rules and differences between TiKV and TiFlash;
  2. This precision is mainly in the design. If you want to retain a certain number of decimal places, it is generally multiplied by a certain number of integers. This is especially applicable to amounts. In general, the database stores integers and performs conversion when displaying.
| username: Meditator | Original post link

In some respects, design is particularly important. In terms of payments, regardless of the database, decimals are basically not stored. Instead, they are stored in the database after being multiplied by 100 or 1000 times, and then need to be converted when displayed to the user.

| username: bindsang | Original post link

If it were just the differences between TiFlash and TiDB, it would be one thing. But the problem now is that even under TiFlash, the results of Query One and Query Two are different. The only difference is that one has an additional CTE.

| username: tidb菜鸟一只 | Original post link

It feels like a bug.