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?

21 thoughts on “1!=1”

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

  2. 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. 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. 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@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. 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. 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. 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.

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

    1 rows selected

    3*1/3
    —–
    1

    ?!

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

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

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

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

  14. 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)…

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

  16. 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 !

  17. 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).

  18. 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 …

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>