SUM and precision
Can someone please help me with this. I have a table RT with two columns - ID (VARCHAR2(2)) and rtt (NUMBER). Half of them is marked as EM, half as BI. As first query shows those parts are identical. So why does sum differ?!?
SQL> SELECT rtt
2 FROM RT
3 WHERE ID = 'EM'
4 MINUS
5 SELECT rtt
6 FROM RT
7 WHERE ID = 'BI'
8 /
no rows selected
SQL> SELECT SUM(rtt)
2 FROM RT
3 WHERE ID = 'EM'
4 MINUS
5 SELECT SUM(rtt)
6 FROM RT
7 WHERE ID = 'BI'
8 /
SUM(RTT)
----------
3.04844
Also none of those numbers has more then 41 digits after comma:
SQL> SELECT rtt FROM RT WHERE rtt != ROUND(rtt, 42)
2 /
no rows selected
I'd expect oracle to provide support up to 127 digits after comma. Is there any doc that could explain this or maybe I'm missing something?
In NUMBER reference (http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#45443) there is:
NUMBER Datatype
The NUMBER datatype stores zero, positive, and negative fixed and floating-point numbers with magnitudes between 1.0 x 10-130 and 9.9...9 x 10125 (38 nines followed by 88 zeroes) with 38 digits of precision. If you specify an arithmetic expression whose value has a magnitude greater than or equal to 1.0 x 10126, then Oracle returns an error.
Specify a fixed-point number using the following form:
NUMBER(p,s)
where:
p is the precision, or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.
s is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.
Specify an integer using the following form:
NUMBER(p)
This represents a fixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0).
Specify a floating-point number using the following form:
NUMBER
The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.
SQL> SELECT rtt
2 FROM RT
3 WHERE ID = 'EM'
4 MINUS
5 SELECT rtt
6 FROM RT
7 WHERE ID = 'BI'
8 /
no rows selected
SQL> SELECT SUM(rtt)
2 FROM RT
3 WHERE ID = 'EM'
4 MINUS
5 SELECT SUM(rtt)
6 FROM RT
7 WHERE ID = 'BI'
8 /
SUM(RTT)
----------
3.04844
Also none of those numbers has more then 41 digits after comma:
SQL> SELECT rtt FROM RT WHERE rtt != ROUND(rtt, 42)
2 /
no rows selected
I'd expect oracle to provide support up to 127 digits after comma. Is there any doc that could explain this or maybe I'm missing something?
In NUMBER reference (http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#45443) there is:
NUMBER Datatype
The NUMBER datatype stores zero, positive, and negative fixed and floating-point numbers with magnitudes between 1.0 x 10-130 and 9.9...9 x 10125 (38 nines followed by 88 zeroes) with 38 digits of precision. If you specify an arithmetic expression whose value has a magnitude greater than or equal to 1.0 x 10126, then Oracle returns an error.
Specify a fixed-point number using the following form:
NUMBER(p,s)
where:
p is the precision, or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.
s is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.
Specify an integer using the following form:
NUMBER(p)
This represents a fixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0).
Specify a floating-point number using the following form:
NUMBER
The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.
Hi
There can you do the minus statement round the other way??
Thanks
There can you do the minus statement round the other way??
Thanks
ASKER
Do You mean 'BI' MINUS 'EM'?
yes
going to lunch now i will be back in about an hour
ASKER
Of course result is as supposed - without SUM no rows returned; with SUM - one; when diff is taken - -1 * 10^(-38).
I noticed one thing more: when I remove rows with 42 digits after comma (32 of 172) it matches!
SQL> SELECT SUM(rtt)
2 FROM RT
3 WHERE ID = 'EM'
4 AND rtt NOT IN (SELECT rtt FROM RT WHERE rtt != ROUND(rtt, 41))
5 MINUS
6 SELECT SUM(rtt)
7 FROM RT
8 WHERE ID = 'BI'
9 AND rtt NOT IN (SELECT rtt FROM RT WHERE rtt != ROUND(rtt, 41))
10 /
no rows selected
I noticed one thing more: when I remove rows with 42 digits after comma (32 of 172) it matches!
SQL> SELECT SUM(rtt)
2 FROM RT
3 WHERE ID = 'EM'
4 AND rtt NOT IN (SELECT rtt FROM RT WHERE rtt != ROUND(rtt, 41))
5 MINUS
6 SELECT SUM(rtt)
7 FROM RT
8 WHERE ID = 'BI'
9 AND rtt NOT IN (SELECT rtt FROM RT WHERE rtt != ROUND(rtt, 41))
10 /
no rows selected
ASKER
Moreover, only those with 42 digits after comma also gives same result:
SQL> SELECT SUM(rtt)
2 FROM RT
3 WHERE ID = 'BI'
4 AND rtt IN (SELECT rtt FROM RT WHERE rtt != ROUND(rtt, 41))
5 MINUS
6 SELECT SUM(rtt)
7 FROM RT
8 WHERE ID = 'EM'
9 AND rtt IN (SELECT rtt FROM RT WHERE rtt != ROUND(rtt, 41))
10 /
no rows selected
Now I'm totally confused.
SQL> SELECT SUM(rtt)
2 FROM RT
3 WHERE ID = 'BI'
4 AND rtt IN (SELECT rtt FROM RT WHERE rtt != ROUND(rtt, 41))
5 MINUS
6 SELECT SUM(rtt)
7 FROM RT
8 WHERE ID = 'EM'
9 AND rtt IN (SELECT rtt FROM RT WHERE rtt != ROUND(rtt, 41))
10 /
no rows selected
Now I'm totally confused.
You face this result because you do not care to format the output.
Look at the following:
A. No formatting
SQL> select 23.345616534343434343434 from dual;
23.345616534343434343434
------------------------
23,3456165
SQL>
B. With formatting
column K format 999,999,999.99999999999999
select 23.345616534343434343434 K from dual
SQL> column K format 999,999,999.99999999999999
SQL> select 23.345616534343434343434 K from dual
2 /
K
--------------------------
23.345616534343434
Look at the following:
A. No formatting
SQL> select 23.345616534343434343434 from dual;
23.345616534343434343434
------------------------
23,3456165
SQL>
B. With formatting
column K format 999,999,999.99999999999999
select 23.345616534343434343434 K from dual
SQL> column K format 999,999,999.99999999999999
SQL> select 23.345616534343434343434 K from dual
2 /
K
--------------------------
23.345616534343434
ASKER
How can format matter when evaluating query??
Please take a look at two queries in my original question - table has two sets of rows (one marked as 'EM' one as 'BI'). Identical (excluding ID) rows. When I SUM them result differs by 1 * 10^(-38) !!!
Please take a look at two queries in my original question - table has two sets of rows (one marked as 'EM' one as 'BI'). Identical (excluding ID) rows. When I SUM them result differs by 1 * 10^(-38) !!!
Excuse me! I thought you are speaking about the format of the result.
I do not believe you can use MINUS selecting group functions so easy.
May be a good approach is:
SELECT sum(g.rtt)
FROM
( SELECT rtt
FROM RT
WHERE ID = 'EM'
MINUS
SELECT rtt
FROM RT
WHERE ID = 'BI') g;
I do not believe you can use MINUS selecting group functions so easy.
May be a good approach is:
SELECT sum(g.rtt)
FROM
( SELECT rtt
FROM RT
WHERE ID = 'EM'
MINUS
SELECT rtt
FROM RT
WHERE ID = 'BI') g;
ASKER
Your select will return no rows - we know inner select (g) returns nothing, so it cannot return anything else. Anyway I tried and null was returned (instead of no row - because of SUM).
I'm pretty sure minus/union/intersect work with aggregates as well as without them. But if You don't like it - see this:
SQL> set num 50
SQL> SELECT (SELECT SUM(rtt) FROM RT WHERE ID = 'EM') - (SELECT SUM(rtt) FROM RT WHERE ID = 'BI') diff
2 FROM dual
3 /
DIFF
--------------------------
.0000000000000000000000000
So if You keep in mind that rows with 'EM' are same as those with 'BI' it's a little strange, isn't it?!? That's why I'm stuck here and have no idea what's happening...
The task I'm trying to accomplish is to check if two sums are identical - and this is my problem; they aren't. But all rows taking part in this sum are perfectly identical! My temporary solution is to round results to 20 digits after comma and then compare them - but of course I'd prefer to compare entire sum.
I'm pretty sure minus/union/intersect work with aggregates as well as without them. But if You don't like it - see this:
SQL> set num 50
SQL> SELECT (SELECT SUM(rtt) FROM RT WHERE ID = 'EM') - (SELECT SUM(rtt) FROM RT WHERE ID = 'BI') diff
2 FROM dual
3 /
DIFF
--------------------------
.0000000000000000000000000
So if You keep in mind that rows with 'EM' are same as those with 'BI' it's a little strange, isn't it?!? That's why I'm stuck here and have no idea what's happening...
The task I'm trying to accomplish is to check if two sums are identical - and this is my problem; they aren't. But all rows taking part in this sum are perfectly identical! My temporary solution is to round results to 20 digits after comma and then compare them - but of course I'd prefer to compare entire sum.
This was my firs big lesson when I began my programmer career 35 years ago (1971).
Your approach can work only with integers. But Oracle NUMBER type in fact always is a special, very special kind of IEEE floating point numbers.
In floating-point world it is suicide to write:
IF x=y then ....
Instead this you have to write:
if abs(x-y) <= 0.000001 then .....
Sorry, this is the numbers life ...
Your approach can work only with integers. But Oracle NUMBER type in fact always is a special, very special kind of IEEE floating point numbers.
In floating-point world it is suicide to write:
IF x=y then ....
Instead this you have to write:
if abs(x-y) <= 0.000001 then .....
Sorry, this is the numbers life ...
understand why your confused, it would confuse me (already has).
The only thing i can think of is that there if there are more than one value attached to id but a different amount attached to the other....but in total there are the same amount for each ID This could lead to you scenario, see below:
If this does not help, them you have stumped me.
Name Null? Type
----------------------- -------- ----------------
ID VARCHAR2(2)
RTT NUMBER
select * from TEMP_TEMP order by ID ;
ID RTT
-- ----------
BI 1
BI 1
BI 2
BI 2
EM 1
EM 1
EM 1
EM 2
SELECT rtt
FROM TEMP_TEMP
WHERE ID = 'EM'
MINUS
SELECT rtt
FROM TEMP_TEMP
WHERE ID = 'BI';
no rows selected
SELECT SUM(rtt)
FROM TEMP_TEMP
WHERE ID = 'EM'
MINUS
SELECT SUM(rtt)
FROM TEMP_TEMP
WHERE ID = 'BI';
SUM(RTT)
----------
5
The only thing i can think of is that there if there are more than one value attached to id but a different amount attached to the other....but in total there are the same amount for each ID This could lead to you scenario, see below:
If this does not help, them you have stumped me.
Name Null? Type
----------------------- -------- ----------------
ID VARCHAR2(2)
RTT NUMBER
select * from TEMP_TEMP order by ID ;
ID RTT
-- ----------
BI 1
BI 1
BI 2
BI 2
EM 1
EM 1
EM 1
EM 2
SELECT rtt
FROM TEMP_TEMP
WHERE ID = 'EM'
MINUS
SELECT rtt
FROM TEMP_TEMP
WHERE ID = 'BI';
no rows selected
SELECT SUM(rtt)
FROM TEMP_TEMP
WHERE ID = 'EM'
MINUS
SELECT SUM(rtt)
FROM TEMP_TEMP
WHERE ID = 'BI';
SUM(RTT)
----------
5
ASKER
oogooglies:
No, this is not the case.
SQL> ed
Wrote file afiedt.buf
1 select sum(case id when 'BI' then 1 else 0 end) - sum(case id when 'EM' then 1 else 0 end) diff, rtt
2 from rt
3 group by rtt
4* having sum(case id when 'BI' then 1 else 0 end) - sum(case id when 'EM' then 1 else 0 end) != 0
SQL> /
no rows selected
schwertner:
I'm affraid that finally I'll have to agree with You...I can't find other explanation...but...
...in my specific situation it doesn't make sense! This is how do I see it: Oracle provided some quantity of bytes for storing my value (I mean on disk and in memory). The same quantity for all of those numbers. I've checked one by one - they're identical. When it sums them required precission cannot increase. So it doesn't need more space for result then for each of them. So there is no room for rounding/truncating them. So results for identical sources should be equal. But they are not.
No, this is not the case.
SQL> ed
Wrote file afiedt.buf
1 select sum(case id when 'BI' then 1 else 0 end) - sum(case id when 'EM' then 1 else 0 end) diff, rtt
2 from rt
3 group by rtt
4* having sum(case id when 'BI' then 1 else 0 end) - sum(case id when 'EM' then 1 else 0 end) != 0
SQL> /
no rows selected
schwertner:
I'm affraid that finally I'll have to agree with You...I can't find other explanation...but...
...in my specific situation it doesn't make sense! This is how do I see it: Oracle provided some quantity of bytes for storing my value (I mean on disk and in memory). The same quantity for all of those numbers. I've checked one by one - they're identical. When it sums them required precission cannot increase. So it doesn't need more space for result then for each of them. So there is no room for rounding/truncating them. So results for identical sources should be equal. But they are not.
SELECT SUM(a.rtt) - SUM(b.rtt)
FROM
(SELECT rtt
FROM TEMP_TEMP
WHERE ID = 'EM') a,
(SELECT rtt
FROM TEMP_TEMP
WHERE ID = 'BI') b;
But the query
SELECT SUM(rtt)
FROM TEMP_TEMP
WHERE ID = 'EM'
MINUS
SELECT SUM(rtt)
FROM TEMP_TEMP
WHERE ID = 'BI';
is crystal clear:
SELECT SUM(rtt)
FROM TEMP_TEMP
WHERE ID = 'EM'
selects
5
SELECT rtt
FROM TEMP_TEMP
WHERE ID = 'BI';
selects
6
These are different,
so the query returns 5
FROM
(SELECT rtt
FROM TEMP_TEMP
WHERE ID = 'EM') a,
(SELECT rtt
FROM TEMP_TEMP
WHERE ID = 'BI') b;
But the query
SELECT SUM(rtt)
FROM TEMP_TEMP
WHERE ID = 'EM'
MINUS
SELECT SUM(rtt)
FROM TEMP_TEMP
WHERE ID = 'BI';
is crystal clear:
SELECT SUM(rtt)
FROM TEMP_TEMP
WHERE ID = 'EM'
selects
5
SELECT rtt
FROM TEMP_TEMP
WHERE ID = 'BI';
selects
6
These are different,
so the query returns 5
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The topic about the precission is complex.
The native Oracle numbers are not intended to be used for complex calculations, because they are
not native to the processors and also PL/SQL ( the only native Oracle tool for writing procedural programs
is interpreter, not compiler). So Oracle numbers are for limited use in the case of complex calculations.
I have heard that in 10g Oracle has introduced also the native floating point numbers in IEEE
standart. This means that one can write c, C++ programs to do complex calculations.
I understand that this is not the expected answer, but this is the state of the art in my opinion.
The native Oracle numbers are not intended to be used for complex calculations, because they are
not native to the processors and also PL/SQL ( the only native Oracle tool for writing procedural programs
is interpreter, not compiler). So Oracle numbers are for limited use in the case of complex calculations.
I have heard that in 10g Oracle has introduced also the native floating point numbers in IEEE
standart. This means that one can write c, C++ programs to do complex calculations.
I understand that this is not the expected answer, but this is the state of the art in my opinion.
GGuzdziol .
I am interested in this. We should be able to find out the reason for that.
could you show me your table definition? and some of your data look like?
I am interested in this. We should be able to find out the reason for that.
could you show me your table definition? and some of your data look like?
one thing you could do is to narrow down rows you are investigating to eventually find out which kind of data are causing this problem.
let me know if you need help on doing this.
acton
let me know if you need help on doing this.
acton
ASKER
First of all - great thanks goes to pennnn. Your test case maybe wasn't answer to my question, but directed me to look for really nice examples.
actonwang - table definition is quite simple; to show strange results it's enough
create table t(id number, val number)
Some of my examples don't even need two columns.
Now I'm at home and I'm unable to show You examples that I have found. Anyway I can give brief description of the results. I have strong feeling that this is very rare case of specially prepared data.
One experiment was like that - I put one of those special values into a table. It was sth like 0.012500000000000000023418
When I ran update t set col = col + 0.1; update t set col = col - 0.1; the value remained the same. But when I ran update t set col = col + 1; update t set col = col - 1; it changed it value! (by about 10^(-38)).
Another - I took this special value, it look like 0.087500000000000013461347
I hope that I'll find some time tommorow to show You some copy&paste from sqlplus and those specific numbers that cause problems.
actonwang - table definition is quite simple; to show strange results it's enough
create table t(id number, val number)
Some of my examples don't even need two columns.
Now I'm at home and I'm unable to show You examples that I have found. Anyway I can give brief description of the results. I have strong feeling that this is very rare case of specially prepared data.
One experiment was like that - I put one of those special values into a table. It was sth like 0.012500000000000000023418
When I ran update t set col = col + 0.1; update t set col = col - 0.1; the value remained the same. But when I ran update t set col = col + 1; update t set col = col - 1; it changed it value! (by about 10^(-38)).
Another - I took this special value, it look like 0.087500000000000013461347
I hope that I'll find some time tommorow to show You some copy&paste from sqlplus and those specific numbers that cause problems.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SQL> set num 50
SQL> SELECT (SELECT SUM(rtt) FROM RT WHERE ID = 'EM') - (SELECT SUM(rtt) FROM RT WHERE ID = 'BI') diff
2 FROM dual
3 /
DIFF
--------------------------
.0000000000000000000000000