php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

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
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
MUST BE VALID
Solve the problem:
28 - 15 = ?
Subscribe to this entry?

 
 [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-2024 The PHP Group
All rights reserved. 		Last updated: Fri Apr 26 08:01:30 2024 UTC