When dealing with fixed point numbers, you have to be very careful – especially if you develop with PHP and MySQL. In this article, obstacles and subtleties of working with the PHP BCMath extension, MySQL fixed point expression handling and persisting fixed point data from PHP to MySQL are described. Despite the occurring barriers we try to figure out how to work with fixed point numbers and not to lose a digit.
Troubles with BCMath
BCMath documentation says:
For arbitrary precision mathematics PHP offers the Binary Calculator which supports numbers of any size and precision, represented as strings.
So BCMath function parameters should be represented as strings. Passing numeric values to bcmath
can lead to wrong results, the same precision loss as when we treat double value as string
Case 1
echo bcmul(776.210000, '100', 10) . PHP_EOL;
echo bcmul(776.211000, '100', 10) . PHP_EOL;
echo bcmul(776.210100, '100', 10) . PHP_EOL;
echo bcmul(50018850776.210000, '100', 10) . PHP_EOL;
echo bcmul(50018850776.211000, '100', 10) . PHP_EOL;
echo bcmul(50018850776.210100, '100', 10) . PHP_EOL;
Results are:
77621.00
77621.100
77621.0100
5001885077621.00
5001885077621.100
5001885077621.00 //here we can see precision loss
Never pass numeric values to BCMath functions, only string values that represent numbers. Even when not dealing with floating points, BCMath can output strange results:
Case 2
echo bcmul('10', 0.0001, 10) . PHP_EOL;
echo bcmul('10', 0.00001, 10) . PHP_EOL;
echo 10*0.00001 . PHP_EOL;
Results are:
0.0010
0 // thats really strange!!!
0.0001
The reason for this is that BCMath converts its arguments to strings, and there are cases in which a number’s string representation has exponential notation.
Case 3
echo bcmul('10', '1e4', 10) . PHP_EOL; //outputs 0 as well
PHP is a weakly typed language and in some cases you can’t control input in a strict way – you want to process as many requests as possible.
For example we can “fix” Case 2 and Case 3 by applying sprintf
transformation:
$val = sprintf("%.10f", '1e5');
echo bcmul('10', $val, 10) . PHP_EOL;
// gives us 0.0001000000
but applying the same transformation can break Case 1 “proper” behaviour:
$val = sprintf("%.10f", '50018850776.2100000000');
echo bcmul('10', $val, 10) . PHP_EOL;
echo bcmul('10', 50018850776.2100000000, 10) . PHP_EOL;
500188507762.0999908450 //WRONG
500188507762.10 //RIGHT
So the sprintf
solution is not suitable for BCmath. Assuming all user inputs are strings, we can implement a simple validator, catching all exponential notation numbers and converting them properly. This technique is done in phpbignumbers, so we can safely pass in arguments like 1e20
and 50018850776.2101
without losing precision.
echo bcmul("50018850776.2101", '100', 10) . PHP_EOL;
echo bcmul(Decimal::create("50018850776.2101"), '100', 10) . PHP_EOL;
echo bcmul(Decimal::create("1e8"), '100', 10) . PHP_EOL;
echo bcmul("1e8", '100', 10) . PHP_EOL;
echo bcmul(50018850776.2101, '100', 10) . PHP_EOL;
echo bcmul(Decimal::create(50018850776.2101), '100', 10) . PHP_EOL;
// Result
// 5001885077621.0100
// 5001885077621.0100
// 0.00000100
// 0
// 5001885077621.00
// 5001885077621.00982700
But the last two lines of the example show us that floating point caveats cannot be avoided by input parsing (which is completely logical – we can not deal with PHP internal double representation).
BCMath final guidelines
Never use floating point numbers as fixed point operation arguments. String conversion does not help, because we can not manage the precision loss in any way.
When using BCMath extension operations, be careful with arguments in exponential representation. BCMath functions do not process exponential arguments (i.e. ‘1e8’) correctly, so you should convert them manually. Be careful, do not use sprintf
or similar conversion techniques, because it leads to precision loss.
You can use the phpbignumbers library which handles input arguments in exponential form and provides users with fixed point math operations functions. However, its performance is worse than that of the BCMath extension, so it’s a kind of compromise between a robust package and performance.
MySQL and fixed point numbers
In MySQL, fixed point numbers are handled with the DECIMAL
column type. You can read the official MySQL documentation for data types and precision math operations.
The most interesting part is how MySQL handles expressions:
Handling of a numeric expression depends on the kind of values the expression contains:
If any approximate values are present, the expression is approximate and is evaluated using floatingpoint arithmetic.
If no approximate values are present, the expression contains only exact values. If any exact value contains a fractional part (a value following the decimal point), the expression is evaluated using DECIMAL exact arithmetic and has a precision of 65 digits. The term “exact” is subject to the limits of what can be represented in binary. For example, 1.0/3.0 can be approximated in decimal notation as .333…, but not written as an exact number, so (1.0/3.0)*3.0 does not evaluate to exactly 1.0.
Otherwise, the expression contains only integer values. The expression is exact and is evaluated using integer arithmetic and has a precision the same as BIGINT (64 bits).
If a numeric expression contains any strings, they are converted to doubleprecision floatingpoint values and the expression is approximate.
Here is a short example that demonstrates fractional part cases:
mysql> CREATE TABLE fixed_point (
> amount NUMERIC(40,20) NOT NULL
> ) engine=InnoDB, charset=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO fixed_point (amount) VALUES(0.2);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT amount, amount + 0.1, amount + 1e1, amount + '0.1' FROM fixed_point;
+++++
 amount  amount + 0.1  amount + 1e1  amount + '0.1' 
+++++
 0.20000000000000000000  0.30000000000000000000  0.30000000000000004  0.30000000000000004 
+++++
1 row in set (0.00 sec)
It may seen quite straightforward, but let’s look at how to deal with it within PHP.
Precision math in PHP & MySQL
So now we have to persist our fixed point values from PHP into MySQL. The right way is to use prepared statements and placeholders within our queries. Then we do parameter binding and everything is safe and secure.
$amount_to_add = "0.01";
$stmt = $dbh>prepare("UPDATE fixed_point SET amount = amount + :amount");
$stmt>bindValue("amount", $amount_to_add);
$stmt>execute();
When we bind a value to a statement placeholder, we can specify its type by the bindValue
third argument. Possible types are represented by constants PDO::PARAM_BOOL
, PDO::PARAM_NULL
, PDO::PARAM_INT
, PDO::PARAM_STR
, PDO::PARAM_LOB
and PDO::PARAM_STMT
. So the problem is that the PHP PDO extension does not have a decimal parameter type for binding. As a result, all math expressions in queries are treated as floating point expressions, not as fixed point expressions.
$dbh = new PDO("mysql:host=localhost;dbname=test", "root", "");
$dbh>setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "
CREATE TABLE IF NOT EXISTS fixed_point (
amount DECIMAL(43,20)
)
";
$dbh>query($sql);
$dbh>query("DELETE FROM fixed_point");
$dbh>query("INSERT INTO fixed_point VALUES(0.2)");
$amount_to_add = "0.1";
$stmt = $dbh>prepare("UPDATE fixed_point SET amount = amount + :amount");
$stmt>bindValue("amount", $amount_to_add);
$stmt>execute();
$stmt = $dbh>prepare("SELECT amount FROM fixed_point");
$stmt>execute();
var_dump($stmt>fetchColumn());
//output is string(22) "0.30000000000000004000"
If we want to take the advantage of prepared statements and work with fixed point numbers, the best way is to perform all math operations in PHP and save results to MySQL.
$amount_to_add = "0.1";
$stmt = $dbh>prepare("SELECT amount FROM fixed_point");
$stmt>execute();
$amount = $stmt>fetchColumn();
$new_amount = bcadd($amount, $amount_to_add, 20);
$stmt = $dbh>prepare("UPDATE fixed_point SET amount=:amount");
$stmt>bindValue("amount", $new_amount);
$stmt>execute();
$stmt = $dbh>prepare("SELECT amount FROM fixed_point");
$stmt>execute();
$amount_after_change = $stmt>fetchColumn();
echo $amount_after_change . PHP_EOL;
Conclusion
We’ve reached the following conclusions:
 Never use floating point numbers as fixed point operations arguments in BCMath PHP extension funcitons. Only strings.
 BCMath extension does not work with string numbers in exponential representation
 MySQL supports fixed point number expressions, but all operands have to be in decimal format. If at least one agrument is in exponential format or string, it is treated as floating point number and the expression is evaluated as floating point number.
 PHP PDO extension does not have
Decimal
parameter type, so if you use prepared statements and binding parameters in SQL expressions that contain fixed point operands – you won’t get precise results.  To perform precise math operations in PHP+MySQL applications you can choose two ways. The first one is to process all operations in PHP and persist data to MySQL only with
INSERT
orUPDATE
statements. In this case you can use prepared statements and parameter binding. The second one is to build SQL queries manually (you can still use prepared statements, but you have to escape parameters by yourself) so all SQL math expressions are in decimal number representation.
My personal favorite approach is the first one: all math operations in PHP. I agree that PHP and MySQL may be not the best choice for applications with precision math, but if you chose this technology stack, it’s good to know that there is a way to deal with it the right way.

Taylor Ren

http://aasiutin.com/ Aleksey Asiutin

Taylor Ren

http://aasiutin.com/ Aleksey Asiutin

Taylor Ren





Net