From: teemu dot gronqvist at goodgameltd dot com
Operating system: Ubuntu 19.10
PHP version: 7.4.6
Package: MySQL related
Bug Type: Bug
Bug description:MySQL FLOAT truncates to int in locales with comma separated decimals
Description:
------------
Prerequisites
-----------------------
PDO::ATTR_EMULATE_PREPARES = false
setlocale(LC_ALL, 'fi_FI.UTF-8') // Or any other locale with comma
separated decimals
The bug
-----------------------
In locales that use comma separated decimals (eg. 4,7) the MySQLND
driver will truncate all FLOATs received from the server removing all
precision, basically making them into ints
When using PDO, ATTR_EMULATE_PREPARES needs to be set to false in order
for the driver to actually receive FLOATs from the server (otherwise PHP
seems to convert everything to strings from the get go)
The PHP userspace never even receives the original representation of the
value and thus has no time to mitigate this / no real workaround exists
For example trying to fetch a FLOAT type column from MySQL with the
value being 4.9 in MySQL will result in (double) 4.0 on PHP side
The cause
-----------------------
The root cause of this bug lies in ext/mysqlnd/mysql_float_to_double.h
in function mysql_float_to_double
On line 43 the function will first convert the FLOAT received from MySQL
server into PHP string (to be later converted back to PHP double)
However the format identifier f in standard C sprintf is locale
sensitive and thus will print a comma separated decimal value
After this the value is converted into PHP double, which does it's best
to convert comma separated decimal, causing for example 4,9 as a value
to be simply truncated into 4.0 (as this conversion does not support
commas)
This is caused by regression in commit
f2eadb93b9268bca86d3f67e8d8cf2fa2767a54d.
Before this commit there was a comment stating that localization is
specifically ignored:
/* Convert to string. Ignoring localization, etc.
* Following MySQL's rules. If precision is undefined (NOT_FIXED_DEC
i.e. 31)
* or larger than 31, the value is limited to 6 (FLT_DIG).
*/
However, the commit introduces localization to the MySQL FLOAT to string
conversion and removes this comment.
Suggested fix
-----------------------
Roll back the behavior to the one it was before commit
f2eadb93b9268bca86d3f67e8d8cf2fa2767a54d documented in the comment
There seems to be no way to force sprintf to print dot separated
decimals only
I'll try making a patch for this
Backwards compatibility
-----------------------
The suggested fix should pose no backwards incompatibility issues
Only those with the prerequisites set will see higher precision in
floats received from the MySQL server after this is fixed
Test script:
---------------
setlocale(LC_ALL, 'fi_FI.UTF-8'); // Do note: the locale probably needs
to be installed
$pdo = new PDO('mysql:host=localhost;dbname=database', 'user',
'password');
$pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
$pdo->query('CREATE TABLE test(broken FLOAT(2,1))');
$pdo->query('INSERT INTO test VALUES(4.9)');
var_dump($pdo->query('SELECT broken FROM test')->fetchColumn(0));
Expected result:
----------------
php shell code:1:
double(4.9)
Actual result:
--------------
php shell code:1:
doub1le(4)
--
Edit bug report at https://bugs.php.net/bug.php?id=79596&edit=1
--
Fix committed: https://bugs.php.net/fix.php?id=79596&r=fixed
Fixed in release: https://bugs.php.net/fix.php?id=79596&r=alreadyfixed
Need backtrace: https://bugs.php.net/fix.php?id=79596&r=needtrace
Need Reproduce Script: https://bugs.php.net/fix.php?id=79596&r=needscript
Try newer version: https://bugs.php.net/fix.php?id=79596&r=oldversion
Not developer issue: https://bugs.php.net/fix.php?id=79596&r=support
Expected behavior: https://bugs.php.net/fix.php?id=79596&r=notwrong
Not enough info: https://bugs.php.net/fix.php?id=79596&r=notenoughinfo
Submitted twice: https://bugs.php.net/fix.php?id=79596&r=submittedtwice
register_globals: https://bugs.php.net/fix.php?id=79596&r=globals
PHP version support discontinued: https://bugs.php.net/fix.php?id=79596&r=phptooold
Daylight Savings: https://bugs.php.net/fix.php?id=79596&r=dst
IIS Stability: https://bugs.php.net/fix.php?id=79596&r=isapi
Install GNU Sed: https://bugs.php.net/fix.php?id=79596&r=gnused
Floating point limitations: https://bugs.php.net/fix.php?id=79596&r=float
No Zend Extensions: https://bugs.php.net/fix.php?id=79596&r=nozend
MySQL Configuration Error: https://bugs.php.net/fix.php?id=79596&r=mysqlcfg