The behavior of TiFlash Decimal calculation is different from TiDB

Some background:

• A Decimal type has two properties: precision and scale
• A SQL function needs to infer the result type based on the input type, because only type info is needed, usually, this type infer is done in compile time

So if a function returns Decimal type, the return Decimal type can be inferred based on the input types.

Consider the following example:

mysql> create table t (a decimal(3,0), b decimal(10, 0));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t values (-43, -1044774912);
Query OK, 1 row affected (0.03 sec)

mysql> alter table t set tiflash replica 1;
Query OK, 0 rows affected (0.07 sec)

If we want to calculate a/b, since the input type is Decimal(3,0) and Decimal(10,0), we can easily get the return type of a/b based on the type infer rules. In fact, both TiDB and TiFlash use Decimal(7,4) as its result type.

Everything works fine until we found in TiDB/Mysql runtime, looks like the compile time inferred result type is only guaranteed for the top level project expressions:

mysql> set session tidb_isolation_read_engines='tikv';
Query OK, 0 rows affected (0.00 sec)

mysql> select a/b, a/b + 0.0000000000001 from t where a/b;
+--------+-----------------------+
| a/b    | a/b + 0.0000000000001 |
+--------+-----------------------+
| 0.0000 |       0.0000000410001 |
+--------+-----------------------+
1 row in set (0.00 sec)

mysql> set session tidb_isolation_read_engines='tiflash';
Query OK, 0 rows affected (0.00 sec)

mysql> select a/b, a/b + 0.0000000000001 from t where a/b;
Empty set (0.01 sec)

From the query result of TiDB + TiKV, we can find:

• the result of a/b in the filter condition is not a Decimal(7,4)
• the result of a/b in a/b + 0.0000000000001 is not Decimal(7,4), from the result 0.0000000410001, we can infer that a/b's result is 0.000000041, so the result's scale is at least 9.

However, in TiFlash runtime, it guarantees that every function's return value is exactly the same type as the compile time inferred type, that is why query on TiDB + TiFlash returns empty result.

Luckily, it is kind of corner case because this bug will not be triggered in most scenarios except Decimal divide, but we still need to find a way to fix it in the future.

No file or test is identified. Start by tracing Decimal division type inference and runtime evaluation in TiFlash, then compare the filter and nested-expression cases with the TiDB/TiKV results shown here. Done means decimal division produces matching results across TiFlash and TiDB for these cases.