Rounding problems

Started by Paolo Saudinalmost 17 years ago5 messagesgeneral

paolo@ecometer.it

almost 17 years ago

Hi,

I have a problem with a query wich simple aggregate values. In the sample
below I have two values, 1.3 and 1.4. Rounding their average with one
decimals, should give 1.4.

The first query with - cast( tables_seb.tbl_arvier_chamencon.id_1 AS
numeric) AS value - give the expected result, while the second one with -
tables_seb.tbl_arvier_chamencon.id_1 AS value - give 1.3.

Which could be the reason ??

-- data

fulldate timestamp; tables_seb.tbl_arvier_chamencon.id_1- reals

2009-03-29 00:00:00; 1.3

2009-03-29 00:30:00; 1.4

--Good query

SELECT date_trunc('hour', data) AS data, round(cast(avg(value) AS numeric),
1 ) AS value

FROM

(

SELECT _master_30.fulldate AS data,

cast( tables_seb.tbl_arvier_chamencon.id_1 AS numeric) AS
value

--tables_seb.tbl_arvier_chamencon.id_1 AS value

FROM _master_30

LEFT JOIN tables_seb.tbl_arvier_chamencon ON
_master_30.fulldate = tables_seb.tbl_arvier_chamencon.fulldate

WHERE _master_30.fulldate between '2009-03-29 00:00:00' AND
'2009-03-29 00:59:59'

ORDER BY data

) foo

GROUP BY 1 ORDER BY 1;

-- value = 1.4 OK

--wrong query

SELECT date_trunc('hour', data) AS data, round(cast(avg(value) AS numeric),
1 ) AS value

FROM

(

SELECT _master_30.fulldate AS data,

--cast( tables_seb.tbl_arvier_chamencon.id_1 AS numeric) AS
value

tables_seb.tbl_arvier_chamencon.id_1 AS value

FROM _master_30

LEFT JOIN tables_seb.tbl_arvier_chamencon ON
_master_30.fulldate = tables_seb.tbl_arvier_chamencon.fulldate

WHERE _master_30.fulldate between '2009-03-29 00:00:00' AND
'2009-03-29 00:59:59'

ORDER BY data

) foo

GROUP BY 1 ORDER BY 1

-- value = 1.3 NOT OK

-- test

select round(cast( (1.3 + 1.4)::real / 2 as numeric), 1);

-- value = 1.4 OK

Using PostgreSQL 8.3.7 on Windows Server 2008

Thank in advance,

Paolo Saudin

Justin

justin@emproshunts.com

almost 17 years ago

In reply to: Paolo Saudin (#1)

Re: Rounding problems

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
 
 
Paolo Saudin wrote:
<blockquote cite="mid:002801c9cbeb$f6b59c20$e420d460$@it" type="cite">
<meta http-equiv="Content-Type" content="text/html; ">
<meta name="Generator" content="Microsoft Word 12 (filtered medium)">
<style>

</style>

<div class="Section1">
Hi,<o:p></o:p>
<o:p> </o:p>
I have a problem with a query wich simple
aggregate values.
In the sample below I have two values, 1.3 and 1.4. Rounding their
average with
one decimals, should give 1.4.<o:p></o:p>
The first query with  -  cast(
tables_seb.tbl_arvier_chamencon.id_1  AS numeric) AS value  - give
the expected result, while the second one with -
tables_seb.tbl_arvier_chamencon.id_1
AS value - give 1.3. <o:p></o:p>
<o:p> </o:p>
Which could be the reason ??<o:p></o:p>
<o:p> </o:p>
 
</div>
</blockquote>
My first thought is whats with all the castings???   
 
Castings  are mostly likely the cause of your problems,   What
is tbl_arvier_chamencon.id_1 data type??? 
 
I'm guessing its something other than numeric. All other floating point
data types will have problems caused by Binary Floating-Point Arithmetic 
 
Numeric data type uses different functions to do its math for the
stated purpose of being exact yet being allot slower. 
 
In one query casting is done prior to avg() yet in the other casting is
done after avg().   This will allow Postgres to use different functions
to calculate average giving an unexpected result. 
</body>
</html>

Tom Lane

tgl@sss.pgh.pa.us

almost 17 years ago

In reply to: Paolo Saudin (#1)

Re: Rounding problems

"Paolo Saudin" <paolo@ecometer.it> writes:

I have a problem with a query wich simple aggregate values. In the sample
below I have two values, 1.3 and 1.4. Rounding their average with one
decimals, should give 1.4.

You seem way overoptimistic about float4 values being exact. They are
not. The actual computation being done here is more like

regression=# select (1.3::real + 1.4::real) / 2 ;
?column?
------------------
1.34999990463257
(1 row)

If you want an exact sum with no roundoff error you should be storing
all your values as numeric (and taking the consequent speed and space
hit :-().

regards, tom lane

Paolo Saudin

paolo@ecometer.it

almost 17 years ago

In reply to: Justin (#2)

R: Rounding problems

Paolo Saudin wrote:

Hi,

I have a problem with a query wich simple aggregate values. In the sample

below I have two values, 1.3 and 1.4. Rounding their average with one
decimals, should give 1.4.

The first query with - cast( tables_seb.tbl_arvier_chamencon.id_1 AS

numeric) AS value - give the expected result, while the second one with -
tables_seb.tbl_arvier_chamencon.id_1 AS >value - give 1.3.

Which could be the reason ??

My first thought is whats with all the castings???

Castings are mostly likely the cause of your problems, What is

tbl_arvier_chamencon.id_1 data type???

I'm guessing its something other than numeric. All other floating point

data types will have problems caused by Binary Floating-Point Arithmetic

Numeric data type uses different functions to do its math for the stated

purpose of being exact yet being allot slower.

In one query casting is done prior to avg() yet in the other casting is

done after avg(). This will allow Postgres to use different functions to
calculate average giving an unexpected >result.

Here is the table layout

CREATE TABLE tables_seb.tbl_arvier_chamencon

(

fulldate timestamp without time zone NOT NULL DEFAULT '2000-01-01
00:00:00'::timestamp without time zone,

id_1 real,

id_1_cod smallint,

id_2 real,

id_2_cod smallint,

id_3 real,

id_3_cod smallint,

id_4 real,

id_4_cod smallint,

CONSTRAINT tbl_arvier_chamencon_pkey PRIMARY KEY (fulldate)

) WITH ( OIDS=FALSE);

Thanks,

Paolo Saudin

paolo@ecometer.it

almost 17 years ago

In reply to: Tom Lane (#3)

R: Rounding problems

"Paolo Saudin" <paolo@ecometer.it> writes:
I have a problem with a query wich simple aggregate values. In the sample
below I have two values, 1.3 and 1.4. Rounding their average with one
decimals, should give 1.4.

You seem way overoptimistic about float4 values being exact. They are
not. The actual computation being done here is more like

regression=# select (1.3::real + 1.4::real) / 2 ;
?column?
------------------
1.34999990463257
(1 row)

If you want an exact sum with no roundoff error you should be storing
all your values as numeric (and taking the consequent speed and space
hit :-().

regards, tom lane

I converted all the fields in numeric type instead of real and now both
queries return the same result !
Now I need to test about performances ...

Thank you very much !!
Paolo Saudin