Lose 1 Unit When Inserting Number Into Mysql

Archives Bug Discussions
1

PHP calculate as 110400 but it inserts 110399 into MySQL

I have a model TestFloat with the schema as below.


CREATE TABLE tbl_test_float (

  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,

  `amount` int(11) DEFAULT NULL

);

As I run the code below


$testFloat = new TestFloat();

$testFloat->amount = 36.8 * 3000;

echo "TestFloat 1: {$testFloat->amount}<br />";

$testFloat->save();


$testFloat = new TestFloat();

$testFloat->amount = 110400;

echo "TestFloat 2: {$testFloat->amount}<br />";

$testFloat->save();

it displays


TestFloat 1: 110400

TestFloat 2: 110400

but it insert different values of "amount" into MySQl (110399 vs 110400).

The sql log is as below


2014/06/05 01:32:28 [trace] [system.db.CDbCommand] Executing SQL: INSERT INTO `tbl_test_float` (`amount`) VALUES (:yp0). Bound with :yp0=110399

2014/06/05 01:32:28 [trace] [system.db.CDbCommand] Executing SQL: INSERT INTO `tbl_test_float` (`amount`) VALUES (:yp0). Bound with :yp0=110400

My question is: Is this a bug of Yii?

2

Could be related to float precision. Casting to float and back to integer may not result in the original value.

3

Yes, it is float precision problem.

In PHP:


var_export(36.8 * 3000);

displays 110399.99999999999

A work around is to cast it to string, then cast it back to float.


var_export((float) (string) 36.8 * 3000)

displays 110400

I haven’t dug into Yii code to find the cause and solution.

4

Alternatively, use an arbitrary precision maths library, like BCMath.

If you need precision, you shouldn’t be calculating with floats directly. Use a library such as the above and use strings to represent the numbers. In your case, it would look like this:




$result = bcmul('36.8', '3000', 1);

The last parameter determines how many decimal places of precision you’ll get in the result.