1

Here's more craziness from my PostgreSQL 9.5.2 output. Why would this return false instead of true for the last column?

dts=# SELECT o.st, SUM(w.st), SUM(w.st) >= o.st
dts-# FROM orders o
dts-# LEFT JOIN production_work w ON o.ident = w.order_id
dts-# WHERE o.ident = 8436
dts-# GROUP BY o.ident;
  st  | sum  | ?column? 
------+------+----------
 20.7 | 20.7 | f
(1 row)

It's like it simply can't do floating point math all of a sudden.

dts=# select st from production_work where order_id = 8436;
 st  
-----
 8.4
 6.4
 5.9
(3 rows)
Gargoyle
  • 9,590
  • 16
  • 80
  • 145
  • 3
    Possible duplicate of [Is floating point math broken?](http://stackoverflow.com/questions/588004/is-floating-point-math-broken) – IMSoP Feb 24 '17 at 18:14
  • Not a good duplicate post. That simply says that floating point can be way rounded off. It does not at all address how to deal with the situation. What I'm showing is a single decimal point and a very standard mathematical computation in SQL. – Gargoyle Feb 24 '17 at 18:18
  • Actually it is common issue, example for sqlite3: `sqlite> with t(x) as (values(8.4),(6.4),(5.9)) select sum(x), sum(x) = 20.7, 20.7=20.7 from t;` and result is `20.7|0|1` Use `numeric` data type for accuracy. – Abelisto Feb 24 '17 at 19:04
  • 1
    http://floating-point-gui.de/ –  Feb 24 '17 at 20:51
  • 1 decimal point does not mean one binary point. That is the fundamental point to understand floating point "problems". – IMSoP Feb 25 '17 at 01:39

0 Answers0