When does 1 != 1?

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

1 thought on “When does 1 != 1?

Comments are closed.

By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.

Close