Postgresql weird result on having clause

Question

SELECT
T . ID,
T . NAME,
T .feed_id,
T .status,
T .budget,
(
    SELECT
        COUNT (*)
    FROM
        segment
    WHERE
        segment.campaign_id = T . ID
) AS total_segments,
(
    SELECT
        SUM (revenue)
    FROM
        TRANSACTION
    WHERE
        TRANSACTION .campaign_id = T . ID
    AND TRANSACTION .status = 3
    AND deleted = 0
) AS projected_revenue
  FROM
campaign T
 WHERE
(T .deleted = 0)
AND (site_id = 3)
GROUP BY
T ."id"
  HAVING
  (
    SELECT
        SUM (revenue)
    FROM
        TRANSACTION
    WHERE
        TRANSACTION .campaign_id = T . ID
    AND TRANSACTION .status = 3
    AND deleted = 0
    ) > 242.45
      ORDER BY
  T . ID DESC

The query works fine when having subquery is greater than (>) or less than (<) but when equals it does not show any result. But it works with:

HAVING (
  SELECT SUM (revenue)
  FROM TRANSACTION 
  WHERE TRANSACTION.campaign_id = T.ID 
    AND TRANSACTION.status = 3
    AND deleted = 0
)::NUMERIC = 242.45

Could it be that the NUMERIC type specifier lets you compare within a small interval? (`242.45` cannot be represented as a finite binary number.) — Gábor Bakos, Jun 05 '14 at 06:00

score 2 · Accepted Answer · edited May 23 '17 at 12:05

At a guess, revenue is float4 or float8, i.e. a float or double precision value.

As @GáborBakos accurately noted, some values (including 242.45) do not have an exact binary floating point representation. Observe:

regress=> SET extra_float_digits = 3;
SET
regress=> SELECT FLOAT4 '242.45';
   float4   
------------
 242.449997
(1 row)

In general, you should not compare floating point values for equality. Use a small range instead.

See:

Your query works with the cast to NUMERIC because, unlike float4 or float8, NUMERIC is a binary-coded decimal type with arbitrary precision and scale. It can precisely represent 242.45 and it doesn't suffer from the rounding artifacts and other oddities of floating point maths. However, that cast isn't always going to be enough - other values may still not work how you expect. Consider:

regress=> select (float4 '0.3' * 100000000) :: NUMERIC;
     numeric      
------------------
 30000001.1920929
(1 row)

As you can see those rounding errors can accumulate.

Rather than rely on comparing with numeric, you should really use a small range comparision instead of comparing for equality. Or, best of all, use numeric consistently to represent financial and exact quantities, never floating point.

many thanks for this link simple and clear http://www.peterbe.com/plog/comparing-real-values — A l w a y s S u n n y, Jun 05 '14 at 09:28

Postgresql weird result on having clause

1 Answers1