Bug #79596 [Ana->Csd]: 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: Analyzed +Status: Closed Type: Bug Package: MySQL related Operating System: Ubuntu 19.10 PHP Version: 7.4.6 Assigned To: cmb Block user comment: N Private report: N New Comment: 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> Previous Comments: ------------------------------------------------------------------------ [2020-05-14 12:59:33] 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-14 12:34:35] 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 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