1!=1

In the process of validating some calculation, I noticed some strange differences from one run to another run, with exactly the same data and exactly the same function.

I suppose the only difference was the order in which the transactions were processed (and since I had no ORDER BY, the order was not deterministic).

To limit it to the minimum, I noticed that, in Oracle, 1/3*3 is not 1 !

SQL> select * from dual where 1/3*3=1
no rows selected.

Let me tell you, this was unexpected. Ok, on my first pocket calculator 30 years ago, I noticed than 1/3=.3333333 and .3333333*3=.9999999. But since then, I used to expect 1.

perl :

$ perl -e 'print 1 if 1/3*3==1'
1

OK, I tried in java

public class X {
public static void main(String[] args){
if (1/3*3<1) { System.out.println("<\n"); } } } $ javac X.java $ java X <

In java it seems different too.

So what is 1/3*3 in Oracle?

SQL> set numw 50
SQL> select 1/3*3 from dual;

1/3*3
--------------------------------------------------
.9999999999999999999999999999999999999999

Surprised?

23 thoughts on “1!=1

  1. Andreas Piesk

    surprised? no, not really. given the precision of numbers and operator order i would be surprised if Oracle returned 1.

  2. Nicolas Gasparotto

    Fortunately, 10=10, and with the inflation nowadays, that’s not bad πŸ™‚
    SQL> select 10/30*30,1/3*3 from dual;

    10/30*30
    ————————————————–
    1/3*3
    ————————————————–
    10
    .9999999999999999999999999999999999999999

  3. coskan

    What about my situation ?

    Connected to:
    Oracle Database 10g Release 10.2.0.3.0 – Production

    SQL> select 10/30*30,1/3*3 from dual;

    10/30*30 1/3*3
    ———- ———-
    10 1

    SQL> select * from dual where 1/3*3=1
    2 ;

    no rows selected

    SQL> select 1/3*3 from dual
    2 ;

    1/3*3
    ———-
    1

    results are same on windows and unix for 10.2.0.3 and 10.2.0.2

    Any explanation ?

  4. coskan

    it is set numw 50 setting

    SQL> set numw 50
    SQL> select 10/30*30,1/3*3 from dual;

    10/30*30 1/3*3
    ————————————————– ————————————————–
    10 .9999999999999999999999999999999999999999

    Thank god πŸ™‚

  5. Tony

    tony@WQDEV> set numformat 999,999,999.9999999999
    tony@WQDEV> set numwidth 50
    tony@WQDEV> select 1/3*3 from dual;

    1/3*3
    ———————–
    1.0000000000

    1 row selected.

    Elapsed: 00:00:00.00
    tony@WQDEV> select * from v$version;

    BANNER
    ——————————————————————————–
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
    PL/SQL Release 11.1.0.7.0 – Production
    CORE 11.1.0.7.0 Production
    TNS for 32-bit Windows: Version 11.1.0.7.0 – Production
    NLSRTL Version 11.1.0.7.0 – Production

  6. Sokrates

    SQL> select * from dual where 1/3*3=1;

    no rows selected

    SQL> select * from dual where 1/3*3*2=2;

    D

    X

    SQL> set pages 100
    SQL> select level n, case when 1/level*level=1 then ‘X’ end ok from dual connect by level<=20;

    N O
    ———- –
    1 X
    2 X
    3
    4 X
    5 X
    6 X
    7 X
    8 X
    9
    10 X
    11
    12
    13
    14
    15 X
    16 X
    17 X
    18 X
    19
    20 X

    20 rows selected.

  7. Leo Anderson

    Very strange…. looks like a big bug, no ?
    1/3*3
    ———————
    0,9999999999999999999999999999999999999999

    1 rows selected

    3*1/3
    —–
    1

    1 rows selected

    3/3*1
    —–
    1

  8. Laurent Schneider Post author

    Great to see so many math fanatics among my reader!

    Leo, no it is not a bug, 3*1=3, 3/3=1, 1/3=.333…

    It is about rounding. But it really puzzled me 😈

  9. Gary

    Fun isn’t it.
    Had a similar issue when a PL/SQL program and a Java program were coming out with different results for the same calculation. In that case it seemed to be the divide by 12 (months in a year).
    Try explaining to a business person that both different results were ‘right’.
    Using BINARY_FLOAT / BINARY_DOUBLE as the PL/SQL datatype, instead of NUMBER, got them to agree.

  10. Leo Anderson

    3*(1/3)
    ———————
    0,9999999999999999999999999999999999999999

    1 rows selected

    3*1/3
    —–
    1

    ?!

  11. Sokrates

    you can use binary_float or binary_double in which case you get “better” results:

    SQL> select ‘ok’ from dual where 3*cast(1/3 as binary_float)=1;

    ‘O

    ok

    SQL> select level n, case when cast(1/level as binary_float)*level=1 then ‘X’ end ok from dual connect by level select count(*), count(ok) from
    2 (
    3 select level n, case when cast(1/level as binary_float)*level=1 then ‘X’ end ok from dual connect by level select count(*), count(ok) from
    2 (
    3 select level n, case when cast(1/level as binary_double)*level=1 then ‘X’ end ok from dual connect by level<=1000
    4 );

    COUNT(*) COUNT(OK)
    ———- ———-
    1000 918

  12. Sokrates

    SQL> select level n, case when cast(1/level as binary_float)*level=1 then ‘X’ end ok from dual connect by level select count(*), count(ok) from
    2 (
    3 select level n, case when cast(1/level as binary_float)*level=1 then ‘X’ end ok from dual connect by level<=1000
    4 );

    COUNT(*) COUNT(OK)
    ———- ———-
    1000 865

  13. Bob Carlton

    Use round when doing division to avoid leaving this operation to chance.

    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
    SQL> select * from dual where 1/3*3=1;
    no rows selected

    SQL> select * from dual where 1/3*3*2=2;
    D

    X

    SQL> select 1/3*3 from dual;
    1/3*3
    ———-
    1

    SQL> select 1/3*3*2 from dual;
    1/3*3*2
    ———-
    2

    SQL> select round(1/3*3) from dual;
    ROUND(1/3*3)
    ————
    1

    SQL> select * from dual where round(1/3*3)=1;
    D

    X

  14. Pingback: Little known but useful facts about Oracle « Klein Oracle denkraam

  15. Marius NITU

    Hi Laurent,

    I think there is only one way in order to right this test correctly (in any programming language)
    Select * from dual where abs(1/3*3 – 1) < 1.E06

  16. Laurent Schneider

    This quite funny you write this because it is exactly the kind of code that made me write the article.

    what ever you chose 1e6, 1e-6, or whatever, it will not solve the case 1/n*n=1 because n could be huge or tiny.

    I had exactly that piece of code where the difference was check at the 6 decimal point, kind of round(m/n,6)…

  17. Marius NITU

    Laurent,
    Sorry but I don’t get your point. If you want to compare two floating point values there is only one way to do it. The problem is not in the errors about rounding but in the inherently error of the representation of the floating point values. The choice of the value 1.E-06 or whatever (1.E06 was my mistake, I was in a hurry) depends on the nature of the application. And I think that in fact what you try to compare is not 1/n*n = 1 but 1/n*n = 1.0.

  18. Laurent Schneider Post author

    The choice of the value 1.E-06 or whatever (1.E06 was my mistake, I was in a hurry) depends on the nature of the application

    that is quite true, and to give an example I am working on foreign exchange. So when comparing currency1 to currency2 using rate, the code may look like :

    if round(currency1*rate,6)=round(currency2,6) then do something

    And for most currencies like dollar, euros, chf, 6 positions after the points are more than enough… But please note that trading one million dollar in ZWD will be in trillions !

    Therefore I had a hot discussion here to review the current comparison mechanism…

    My two cents !

  19. Jonathan Lewis

    On a related topic – this is why you should avoid using number as a column type, but choose the explicit number(p,s) form; otherwise you could (a) run into lots of reporting errors and (b) waste space when you store the value 1 (two bytes if stored in number(6,0) say) by generating 1/3 * 3 (21 bytes as Oracle if you store it in number).

  20. Laurent Schneider

    Design design…

    The issue I am currently facing is that I have 2 currencies, CCY1 and CCY2, and a rate RATE. I need to figure out if CCY1=CCY2*RATE or CCY2=CCY1*RATE because the external sources of our systems provide messy datas…

    The current code who check if round(CCY1,6)=round(CCY2*RATE,6) is innacurate. But due to the unreliable data source, there are mistakes if 1CHF~=1USD or when buying for 2 cents of gold …

  21. Pingback: Laurent Schneider » How many decimals do you need?

  22. Laurent Schneider Post author

    This works by accident. Actually it shouldn’t work. And it has implicit casting TO_NUMBER(TO_CHAR(1/3*3)) and buggy rounding.

    According to the doc, If you omit fmt, then n is converted to a VARCHAR2 value exactly long enough to hold its significant digits..


    SQL> select to_char(.9999999999999999999999999999999999999999,'FM9.99999999999999999999999999999999999999999999999') from dual;

    TO_CHAR(.9999999999999999999999999999999999999999,
    --------------------------------------------------
    .9999999999999999999999999999999999999999

    SQL> select to_char(.9999999999999999999999999999999999999999) from dual;

    T
    -
    1

    SQL> select TO_NUMBER(to_char(1/3*3,'FM9.99999999999999999999999999999999999999999999999')) from dual;

    TO_NUMBER(TO_CHAR(1/3*3,'FM9.99999999999999999999999999999999999999999999999'))
    -------------------------------------------------------------------------------
    .9999999999999999999999999999999999999999

Comments are closed.