For those who are unfamiliar with Dolt, it is a version controlled SQL database. If you know what GitHub and MySQL are, you already know how to use Dolt! We have been improving our compatibility with MySQL everyday by implementing MySQL features we did not support before and fixing current feature bugs as we test deeper into making Dolt 100% compatible with MySQL. Today, we will go over couple issues found in Dolt with decimal data arithmetics and how we resolved them to match MySQL.

This bug was found from a very simple query in Dolt. It was a single arithmetic operation, which allowed us to learn about an interesting behavior of MySQL. As we dug in deeper, many of the arithmetic operations were not working correctly for a general case of handling floating point data.

The first query is a very simple one that gave an incorrect result. This is the result on Dolt before the fix:

dolt> select 2 / 4;
+-------+
| 2 / 4 |
+-------+
| 0     |
+-------+
1 row in set (0.00 sec)

The way we handled all arithmetic operation was that first, we attempted to get the common type between the two operands and convert both of them into that type. It is not possible to use Golang arithmetic operations on two values of different types, so we need both operands to be the same type. Then, we simply use Go arithmetic operations. This is a very simple and straightforward approach but it missed many edge cases like the one we have found above. When both operands are integer numbers, the result of division is going to be an integer. For example, if we convert both the values into int64, then the result of Go division operation will return an int64 value. If the result value has a fractional number, then it will be rounded down to an integer number.

To resolve this, we need to convert the operands into a type that will not truncate the result value. The first idea would be float64 type in Go, but what MySQL returns is a value with specific precision and scale. This suggests that the result value of division operations in MySQL is in DECIMAL data type. To avoid loss of precision, we cannot use Go's float64 type. The MySQL div_precision_increment system variable defines how the scale of the result value changes. For every division operation, the scale is incremented by the amount defined by div_precision_increment. By default, it is 4.

After fixing that issue, Dolt now returns the exact same value as MySQL:

dolt> select 2 / 4;
+--------+
| 2 / 4  |
+--------+
| 0.5000 |
+--------+
1 row in set (0.00 sec)

We found another interesting query that triggered some broken edge cases in our previous arithmetic logic when several division operations were chained in the same expression. Here's the result of that query on Dolt before our work to improve arithmetic logic:

dolt> select (14620 / 9432456) / (24250 / 9432456);
+---------------------------------------+
| (14620 / 9432456) / (24250 / 9432456) |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
1 row in set (0.00 sec)

The reason the result is NULL is because of a division by 0. The first two division result in 0s from the issue we looked at above. The correct behavior of this case would creating a warning in the case of division by 0. A warning is now added in the latest version of Dolt.

After fixing the first query, this query was returning a different result from MySQL. Initially, it was very confusing because the value we got was a correct value from an actual calculator. If you calculate this expression (14620 / 9432456) / (24250 / 9432456) on an actual calculator and round it to 8 decimal places, then you get 0.60288660. It was not a rounding error. The interesting part is that it looked like MySQL just returns an incorrect result. We found MySQL has a special way of handling fractional values for division operation – MySQL only stores the value with a scale of 9 increments per division operation. This means MySQL truncates the result at every 9th scale for each of continuous division operation, but the maximum scale is 30 as MySQL's default. The result value of first division is stored with a scale of 9 and the second continuous division result is stored with a scale of 18. The final result will be in scale of 18, but it will be truncated and rounded at the number defined by div_precision_increment times the number of continuous division, which is 8 for this query.

To resolve this issue, we needed to pre-calculate the size of the scale for the final value and keep track of division operations and whether the current division operation is the last one or not. Meanwhile, the size of the scale for intermediate result values are incremented as we process more division operations. This resolved our current issue for this query, but not every edge case of the division operations.

Here's result of the query on the latest version of Dolt, which now exactly matches MySQL's result:

dolt> select (14620 / 9432456) / (24250 / 9432456);
+---------------------------------------+
| (14620 / 9432456) / (24250 / 9432456) |
+---------------------------------------+
| 0.60288653                            |
+---------------------------------------+
1 row in set (0.00 sec)

As mentioned above, many of other arithmetic operations were not working as expected. Our initial implementation did not work well with floating point data for operations such as mod, int division and so on. These operations were only expected to work on integer operands.

This is an example of a mod operation failed on floating point operands on Dolt before our work:

dolt> select 0.05 % 0.024;
+--------------+
| 0.05 % 0.024 |
+--------------+
| NULL         |
+--------------+
1 row in set (0.00 sec)

As our initial implementation of arithmetic operation handling, we converted both operands to integer values which we got 0s for both the operands. This query result gives us NULL because of division by 0 case.

To resolve this issue for this operation and the other arithmetic operations, we decided to go over every arithmetic operation one by one testing them with floating point data. This allowed us to improve overall handling of arithmetic operations and learn how MySQL handles special cases for some operations.

Now in the latest version of Dolt, the query returns a correct result.

dolt> select 0.05 % 0.024;
+--------------+
| 0.05 % 0.024 |
+--------------+
| 0.002        |
+--------------+
1 row in set (0.00 sec)

Arithmetic operations on floating point values work much better now on Dolt! As this was a first deep dive into decimal precision issues in Dolt, we made a lot of progress, but we know there are still more edge cases we need to find and fix so that we can match MySQL's behavior 100% for any query.

Try out Dolt and cut bugs on GitHub if you find anything. Join our Discord channel and let us know if you find more interesting MySQL behaviors.