I was playing about recently with some basic calculations and rounding within the Oracle database, using release 11.1.0.6 (and I’m sure it’s the same in earlier databases releases).  I’d been busy with Dr. Kawashima’s Brain Training: How Old is Your Brain? on my Nintendo DS and doing the mental calculations, and was thinking about the basic calculation 1/3*3.

Now, following my PEMDAS (I had two years in an American school when I was younger – for those in the UK, think BODMAS) – the sum would be calculated as (1/3) * 3 so would give 1 as the result – try it in any calculator and you should get the same result.

Try it in Oracle, and you get the same:

SQL> SELECT 1/3*3 FROM DUAL;
1/3*3
-----
1

That’s all fine – now try a slight variation:

SQL> SELECT 'OK' FROM DUAL WHERE 1/3*3 = 1;
no rows selected

Hmmmm – so even though it returns 1, it doesn’t equal 1….  Is it true for all values?

SQL> SELECT * FROM DUAL WHERE 1/3*3=1;
no rows selected

SQL> SELECT level, CASE WHEN 1/level*level = 1 THEN 'OK' END ok FROM DUAL CONNECT BY level <= 20;
L O
———- -
1 OK
2 OK
3
4 OK
5 OK
6 OK
7 OK
8 OK
9
10 OK
11
12
13
14
15 OK
16 OK
17 OK
18 OK
19
20 OK

So, there you have it – a handful (and there are more) of examples where 1 !=1 in Oracle!!

This entry was posted on Wednesday, July 15th, 2009 at 9:35 pm and is filed under Oracle, Technical. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.