PHP/MySQLi returning incorrect float values using prepared statements

OK. I may be being thick here (it has been known) but have I found an undocumented "feature" of prepared statements in MySQLi in PHP 5.3.x or have I missed something pretty fundamental.

Short version - Prepared statements in PHP via MySQLi and mysqlnd driver return incorrect float values

Long version

First of all, some test data

mysql> CREATE TABLE `t2` (`v` float(6,2) NOT NULL DEFAULT '0.00');
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t2 (v) values (0.03);
Query OK, 1 row affected (0.00 sec)

The problem arises when retrieving the above value via a prepared statement. When using the legacy mysql_... or standard mysqli... query calls, the correct data is returned. However, using the same query via a prepared statement returns an incorrect value:

Test code:

atom:~/testScripts> cat f1.php
<?php
require('passwds.php');

$q="select * from t2"; // same query for all

echo "/* Old style MySQL statements (deprecated) */".PHP_EOL;

$h1=mysql_connect(MYSQL_SERVER,MYSQL_USER,MYSQL_PASSWORD);
mysql_select_db('test',$h1);
$r1=mysql_query($q);
while ($f1=mysql_fetch_assoc($r1))
{
    echo print_r($f1,true).PHP_EOL;
}

echo "/* New style MySQLi statements */".PHP_EOL;

$h2=new mysqli(MYSQL_SERVER,MYSQL_USER,MYSQL_PASSWORD,'test');
$r2=$h2->query($q);
while ($f2=$r2->fetch_assoc())
{
    echo print_r($f2,true).PHP_EOL;
}

echo "/* New style MySQLi prepared statements */".PHP_EOL;

$h3=new mysqli(MYSQL_SERVER,MYSQL_USER,MYSQL_PASSWORD,'test');
$s3=$h3->stmt_init();
$s3->prepare($q);
$s3->execute(); // no binding required
$r3=$s3->get_result();
while ($f3=$r3->fetch_assoc())
{
    echo print_r($f3,true).PHP_EOL;
}

Results:

atom:~/testScripts> php -f f1.php
/* Old style MySQL statements (deprecated) */
Array
(
    [v] => 0.03
)

/* New style MySQLi statements */
Array
(
    [v] => 0.03
)

/* New style MySQLi prepared statements */
Array
(
    [v] => 0.029999999329448
)

If I change the type of v in table t2 to a DOUBLE rather than a FLOAT the value returned by a prepared statement is correct.

I get the same response when running the script on a Linux box (OpenSuse 12.1 32 bit with MySQL and PHP compiled from source) and from a Windows 7 (64 bit with PHP installed from binaries with data coming from the Linux machine). I have also tried this on an OpenSuse 12.1 64bit installation (again, PHP and MySQL installed from source using mysqlnd driver) with the same results. All versions are using the mysqlnd driver supplied with PHP

Question Have I missed something pretty fundamental or should I be reporting a bug to PHP.NET?

Sorry it's a long question but I thought I would provide as much data as I could

Solution

It is actually the old (first two) statements that are wrong. Floating point values are by nature imprecise, e.g. there is no way to represent many precise decimals exactly in floating point format. Thus the closest floating point value to .03 is .0299999...something. Your first two statements are enjoying the "benefit" of the MySQL driver and/or PHP rounding the values for you during datatype conversion. However, MySQLi prepared statements use a binary result format that translates values 'as-is' directly to PHP datatypes such at int, float, string etc. (http://php.net/manual/en/mysqli.quickstart.prepared-statements.php)

Do some more research on floating-point precision problems (e.g., http://dev.mysql.com/doc/refman/5.5/en/problems-with-float.html) to see if they really fit your needs. If you want precise decimals and are willing to sacrifice a bit of storage space and/or calculation speed, try the MySQL DECIMAL(X,Y) type. However MySQLi may still convert this to a PHP float/double internally.