expression calculation will lose precision in special order.

[AilinKid]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

Admittedly, TiDB has some optimizations that were not considered in expression calculation.
But these points had been polished up in MySQL for the past few decades.
We still need trails and errors to backfill more of this.

one is in expression calculation.

mysql> select '971580' * (5/6);
+-------------------+
| '971580' * (5/6)  |
+-------------------+
| 809617.6140000001 |
+-------------------+
1 row in set (0.00 sec)

mysql> select '971580' * 5/6;
+----------------+
| '971580' * 5/6 |
+----------------+
|         809650 |
+----------------+
1 row in set (0.00 sec)

2. What did you expect to see?

The first case above will lose precision in (5/6)(default keep 4 digits in new decimal), then multiple with cast('971580' as double) to got 809617.6140000001, but the second won't.

More interesting things, when we use 971580 directly, it will get 809649.9997 which is more precise than case 1.

3. What did you see instead?

the case 1 calculation should be more precise, at least be the same result as using 971580 directly.

4. What version of TiDB are you using? (tidb-server -V or run select tidb_version(); on TiDB)

all the branch

SIG slack channel

#sig-exec

Score

• 300

Mentor

• @qw4990

[hidehalo]

@AilinKid @qw4990 Hi! I am very interested in this issue, and I found some problems.
Shall we add more detail comments for FieldType structure?
I believe that most developers who want to solve this issue will be a little confused when they see this.
Maybe it already has some related documents for explanation?