Solved

SUM and precision

Posted on 2006-06-21
5,588 Views
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?

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.
0
Question by:GGuzdziol
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 8
• 6
• 4
• +2

LVL 14

Author Comment

ID: 16950287
The difference isn't very big...

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
--------------------------------------------------
.00000000000000000000000000000000000001
0

Expert Comment

ID: 16950375
Hi

There can you do the minus statement round the other way??

Thanks
0

LVL 14

Author Comment

ID: 16950403
Do You mean 'BI' MINUS 'EM'?
0

Expert Comment

ID: 16950415
yes
0

Expert Comment

ID: 16950419
going to lunch now i will be back in about an hour
0

LVL 14

Author Comment

ID: 16950448
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
0

LVL 14

Author Comment

ID: 16950463
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.
0

LVL 48

Expert Comment

ID: 16950497
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.999999999999999
select 23.345616534343434343434 K from dual

SQL> column K format 999,999,999.999999999999999
SQL> select 23.345616534343434343434 K from dual
2  /

K
----------------------------
23.345616534343434
0

LVL 14

Author Comment

ID: 16950519
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) !!!
0

LVL 48

Expert Comment

ID: 16950697
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;

0

LVL 14

Author Comment

ID: 16950771
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
--------------------------------------------------
.00000000000000000000000000000000000001

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.
0

LVL 48

Expert Comment

ID: 16950959
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 ...
0

Expert Comment

ID: 16951052
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
0

LVL 14

Author Comment

ID: 16951170
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.
0

LVL 48

Expert Comment

ID: 16951351
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

0

LVL 11

Assisted Solution

pennnn earned 250 total points
ID: 16952302
I'm not sure I have answers to all the questions, but here's something that might help.
The Oracle documentation says that Oracle guarantees precision up to 38 digits.
It seems that Oracle, at least in SQL, is rounding the NUMBER datatype to the 40 digit when you haven't specified precision. I'm not sure how your data was stored to hold more than 40 digits even though it's defined as just NUMBER.
My guess is that in your queries Oracle is doing an implicit conversion of your data to NUMBER, thus rounding it to 38 or 40 digits - my tests showed it rounding to 40 digits, but I'm not sure if that's the case on all versions of the database. So the sum is rounded to let's say 40 digits, which causes the difference. You said that if you ignore the rows with 42 digits everything matches, so there probably is a difference in the 42-nd digit for some of the rows.
What makes it even more interesting is that you happened to have a case where the rounded individual numbers match, but the rounded sum doesn't, i.e. the individual numbers end in digits either less than 5 or more than 6, but with the sums one rounds down and the other up.
Here's an example which shows how Oracle rounds NUMBER to the 40-th digit.
I have a table (I won't show the create statements, it's pretty simple) which has one column defined as NUMBER and a unique constraint on it:
SQL> INSERT INTO ttt VALUES (0.123456789012345678901234567890123456789012);

1 row created.

SQL> INSERT INTO ttt VALUES (0.123456789012345678901234567890123456789013);
INSERT INTO ttt VALUES (0.123456789012345678901234567890123456789013)
*
ERROR at line 1:
ORA-00001: unique constraint (X.UQ_TTT_AAA) violated

SQL> INSERT INTO ttt VALUES (0.123456789012345678901234567890123456789016);
INSERT INTO ttt VALUES (0.123456789012345678901234567890123456789016)
*
ERROR at line 1:
ORA-00001: unique constraint (X.UQ_TTT_AAA) violated

SQL> INSERT INTO ttt VALUES (0.1234567890123456789012345678901234567896);

1 row created.

SQL> set num 50
SQL> select * from ttt;

AAA
--------------------------------------------------
.123456789012345678901234567890123456789
.1234567890123456789012345678901234567896

SQL> INSERT INTO ttt VALUES (0.12345678901234567890123456789012345678906);

1 row created.

SQL> select * from ttt;

AAA
--------------------------------------------------
.123456789012345678901234567890123456789
.1234567890123456789012345678901234567896
.1234567890123456789012345678901234567891

Again, I'm not sure if this answers your question, but I hope it helps!
0

LVL 48

Expert Comment

ID: 16957674
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.
0

LVL 19

Expert Comment

ID: 16962254
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?
0

LVL 19

Expert Comment

ID: 16962260
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
0

LVL 14

Author Comment

ID: 16962496
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.0125000000000000000234182361352935, it had ~40 digits.
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.0875000000000000134613476137138451. I ran nine times update t set col = col + 0.1. Value looked then 0.9875000000000000013blabla. And then I added 0.0125 (or 0.0124 - I really don't remember) to this - and it gave me 1. Strange.

I hope that I'll find some time tommorow to show You some copy&paste from sqlplus and those specific numbers that cause problems.
0

LVL 48

Accepted Solution

schwertner earned 250 total points
ID: 16966556
GGuzdziol :
You make a fundamental error trying to use Oracle numbers for pure "scientific" calculations.
What you try to do belongs to "scientific algebra"!
Believe me! I graduated mathematics and am old Assembler programmer and now the number coding systems and application
of the numbers of all computers beginning with IBM-360.
Oracle numbers are not designed for such calculations.
They are dereivate (modification) of IBM's "packed numbers"
and are designed to work with economics data - mainly integers and with small fraction.
If you want to calculate "scientific data" use C or C++ and native floating point
numbers. I believe they can be stored in the Oracle as RAW columns.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle 12c Default Isolation Level 17 62