Bug #79596 [Opn->Ana]: MySQL FLOAT truncates to int in locales with comma separated decimals
Edit report at https://bugs.php.net/bug.php?id=79596&edit=1
ID: 79596
Updated by: cmb@php.net
Reported by: teemu dot gronqvist at goodgameltd dot com
Summary: MySQL FLOAT truncates to int in locales with comma
separated decimals
-Status: Open
+Status: Analyzed
Type: Bug
Package: MySQL related
Operating System: Ubuntu 19.10
PHP Version: 7.4.6
-Assigned To:
+Assigned To: cmb
Block user comment: N
Private report: N
New Comment:
Thanks for the very good bug report!
The fix appears to be trivial; we just have to use our own
snprintf() (or similar) with the F specifier (which is locale
independent).
Previous Comments:
------------------------------------------------------------------------
[2020-05-14 10:16:32] teemu dot gronqvist at goodgameltd dot com
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 this bug report at https://bugs.php.net/bug.php?id=79596&edit=1
Thread (6 messages)