php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #79596 MySQL FLOAT truncates to int in locales with comma separated decimals
Submitted: 2020-05-14 10:16 UTC Modified: 2020-06-18 14:04 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: teemu dot gronqvist at goodgameltd dot com Assigned: cmb (profile)
Status: Closed Package: MySQL related
PHP Version: 7.4.6 OS: Ubuntu 19.10
Private report: No CVE-ID: None
 [2020-05-14 10:16 UTC] 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)

Patches

Add a Patch

Pull Requests

Pull requests:

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-05-14 12:34 UTC] cmb@php.net
-Status: Open +Status: Analyzed -Assigned To: +Assigned To: cmb
 [2020-05-14 12:34 UTC] cmb@php.net
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).
 [2020-05-14 12:59 UTC] cmb@php.net
The following pull request has been associated:

Patch Name: Fix #79596: MySQL FLOAT truncates to int some locales
On GitHub:  https://github.com/php/php-src/pull/5574
Patch:      https://github.com/php/php-src/pull/5574.patch
 [2020-05-15 07:18 UTC] cmb@php.net
-Status: Analyzed +Status: Closed
 [2020-05-15 07:18 UTC] cmb@php.net
The fix for this bug has been committed[1].  If you are still
experiencing this bug, try to check out latest source from
https://github.com/php/php-src and re-test.

Thank you for the report, and for helping us make PHP better.

[1] <http://git.php.net/?p=php-src.git;a=commit;h=d1cd489a53c697898c2da5101c775bd6259f4be0>
 [2020-06-18 13:03 UTC] teemu dot gronqvist at goodgameltd dot com
Strange how this fix seems to have been removed from NEWS between commits https://github.com/php/php-src/commit/d4bd6fb491ccaf70006d6f671755126b47a71bcb#diff-ff4e2dc4962dc25a1512353299992c8d and https://github.com/php/php-src/commit/da801ba5e3553f4c217d2528b36a5977ee9a90ed#diff-ff4e2dc4962dc25a1512353299992c8d

It seems that the fix is applied in the release, but somehow removed from PHP's changelog, unless there's something I missed
 [2020-06-18 14:04 UTC] cmb@php.net
Thanks for reporting!  I forgot to update NEWS after merging
upward.  This should now be fixed in the PHP-7.4 branch as well as
in the changelog[1].

[1] <https://www.php.net/ChangeLog-7.php#7.4.7>
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Mar 28 15:01:29 2024 UTC