php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #75920 precision problem with decimals read from sql server
Submitted: 2018-02-05 13:29 UTC Modified: 2018-02-06 09:18 UTC
From: km dot wrona at gmail dot com Assigned:
Status: Not a bug Package: PDO DBlib
PHP Version: 7.1.14 OS: Alpine
Private report: No CVE-ID: None
 [2018-02-05 13:29 UTC] km dot wrona at gmail dot com
Description:
------------
The number is not represented properly when the column is defined as decimal.

Test script:
---------------
var_dump($this->selectRaw('CAST(\'1.6\' as decimal(10,2)) as dec, CAST(\'1.6\' as float) as flo')->limit(1)->get());
exit;
  
array(1) {
[0]=> object(stdClass)#789 (2) {
    ["dec"]=> float(1.5999999999999)
    ["flo"]=> float(1.6)
  }
}

//default behaviour
["dec"]=> float(1.5999999999999)


//with
//ini_set('precision', 25);
//ini_set('serialize_precision', 25);

["dec"]=> float(1.599999999999909050529823)

Expected result:
----------------
1.6 no matter if decimal or float


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2018-02-06 06:30 UTC] rasmus@php.net
-Status: Open +Status: Not a bug
 [2018-02-06 06:30 UTC] rasmus@php.net
Floating point values have a limited precision. Hence a value might 
not have the same string representation after any processing. That also
includes writing a floating point value in your script and directly 
printing it without any mathematical operations.

If you would like to know more about "floats" and what IEEE
754 is, read this:
http://www.floating-point-gui.de/

Thank you for your interest in PHP.

1.6 can't be represented exactly as a floating point value.
 [2018-02-06 08:59 UTC] km dot wrona at gmail dot com
Thank you for your explanation, except I am not sure if you've read my post. 

1) That 1.6 and any other works OK and is represented properly when defined in php code
2) That 1.6 is represented properly when the column in SqlServer table is defined as float (or when a value is casted to float)
3) That 1.6 IS NOT represented properly when the column in SqlServer table is defined as decimal(10,2) (or when a value is casted to decimal)
 [2018-02-06 09:18 UTC] requinix@php.net
1. 1.6 written literally in PHP is also imprecise. https://3v4l.org/IMCc5
2. FLOAT is a double. With a high enough precision setting you will see it's not exactly 1.6 either.
3. DECIMAL(10,2) = 10 significant digits. In PHP that corresponds to precision=10. Anything lower will round, anything higher will be unpredictable.

Floating-point problems are not specific to PHP. If you want to PHP to act a certain way then you need to learn how it all works and change your php.ini settings and/or code to suit.
 
PHP Copyright © 2001-2018 The PHP Group
All rights reserved.
Last updated: Thu Nov 15 03:01:25 2018 UTC