# Floating down the infinite river

Sayan Malakshinov (@ora_sql)’s quiz inspired this post. Spoiler: if you’d like to take his quiz, do it before reading this post!

### FLOATing literals

Oracle’s number format specification allows for D, E, or F (case doesn’t matter) after the digits. E is exponent – eg. 3.00 x 10^-3 would be written as 3E-3, which is pretty straightforward. D is for BINARY_DOUBLE and F is for BINARY_FLOAT.

http://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF00220

```num fmt:
[ + | - ]
{ digit [ digit ]... [ . ] [ digit [ digit ]... ]
| . digit [ digit ]...
}
[ [ e | E ] [ + | - ] digit [ digit ]... ] [ f | F | d | D ]
f or F indicates that the number is a 32-bit binary floating point number
of type BINARY_FLOAT.
d or D indicates that the number is a 64-bit binary floating point number
of type BINARY_DOUBLE.```

### When is a FLOAT not like a NUMBER?

Sayan’s quiz introduced me to this surprising result:

```SQL> SELECT 0f/0 FROM dual;
0F/0
----------
Nan

SQL> SELECT 0/0f FROM dual;

0/0F
----------
Nan```

Why is this a surprise? because

```SQL> SELECT 0/0 FROM dual;
SELECT 0/0 FROM dual
*
ERROR at line 1:
ORA-01476: divisor IS equal TO zero```

and also:

```SQL> SELECT 1f/0 FROM dual;
1F/0
----------
Inf

SQL> SELECT -1f/0 FROM dual;

-1F/0
----------
-Inf```

but without the float or double:

```SQL> SELECT 1/0 FROM dual;
SELECT 1/0 FROM dual
*
ERROR at line 1:
ORA-01476: divisor IS equal TO zero```

In other words, Oracle throws an error when dividing any number literal by zero – unless the number, or the zero, is BINARY_DOUBLE or BINARY_FLOAT.

### FLOATing standards

This is due to Oracle’s implementation of the IEEE standard:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#SQLRF50963
“IEEE754 Conformance
The Oracle implementation of floating-point datatypes conforms substantially with the Institute of Electrical and Electronics Engineers (IEEE) Standard for Binary Floating-Point Arithmetic, IEEE Standard 754-1985 (IEEE754). The floating-point datatypes conform to IEEE754 in the following areas:
… The special values INF, -INF, and NaN are supported. See “Floating-Point Conditions”. ”

“… Comparison operators conform, except for comparisons with NaN. Oracle orders NaN greatest with respect to all other values, and evaluates NaN equal to NaN. See “Floating-Point Conditions”.”

### What’s bigger than infinity?

This is where Sayan’s quiz comes in (again, spoilers!) :

```SQL> SELECT COUNT(*) cnt FROM dual WHERE rownum < 0f/0;
CNT
----------
1```

Weirdest of all, that last quote does mean that Oracle evaluates NaN > INF :

```  1* SELECT * FROM (SELECT 0/0f flt FROM dual UNION ALL SELECT 1/0f FROM dual) ORDER BY 1
SQL> /

FLT
----------
Inf
Nan```

As expected, attempting to do arithmetic with NaN and/or INF values is a bad idea:

```SQL> SELECT sign(0f/0) FROM dual;

SIGN(0F/0)
----------
1

SQL> SELECT sign(1f/0 - 0f/0) FROM dual;

SIGN(1F/0-0F/0)
---------------
1```

… although NaN > INF, (INF – NaN) > 0 ! since INF-NaN = NaN and sign(NaN) > 0.

Another bit of fun:

```SQL> SELECT 1f/0 - 1F/0  FROM dual;

1F/0-1F/0
----------
Nan```

In general, adding and subtracting infinities isn’t likely to be helpful!

### Floating point conditions

There are a couple of floating point conditions , IS [NOT] NAN and IS [NOT] INFINITE.

```SELECT
CASE
WHEN &&IN IS NAN THEN 'I am NAN'
WHEN &IN IS INFINITE THEN 'I am Infinite'
ELSE 'I''m nothing special'
END AS "What am I?"
FROM dual;

SQL> undef IN
SQL> /
Enter VALUE FOR IN: 1f/0
OLD   3: WHEN &&IN IS NAN THEN 'I am NAN'
NEW   3: WHEN 1f/0 IS NAN THEN 'I am NAN'
OLD   4: WHEN &IN IS INFINITE THEN 'I am Infinite'
NEW   4: WHEN 1f/0 IS INFINITE THEN 'I am Infinite'

What am I?
-------------------
I am Infinite

SQL> undef IN
SQL> /
Enter VALUE FOR IN: 0f/0
OLD   3: WHEN &&IN IS NAN THEN 'I am NAN'
NEW   3: WHEN 0f/0 IS NAN THEN 'I am NAN'
OLD   4: WHEN &IN IS INFINITE THEN 'I am Infinite'
NEW   4: WHEN 0f/0 IS INFINITE THEN 'I am Infinite'

What am I?
-------------------
I am NAN```

See the docs for details on these conditions.

Note: Today’s image isn’t meant to promote the use of infant formula. Breast is best! But it’s pretty funny that there’s a formula branded “NaN” on the market. If I needed formula, I think I’d be looking for something a bit more definite about its contents.

1. Natalka, thanks for mentioning me!
Correct please my last name – Malakshinov ðŸ™‚

Regards,
Sayan Malakshinov

• Natalka says:

Oops! I am sorry. Fixed – and thank you Sayan for catching my typo.

2. Kirk Brocas says:

Nice,clear post Natalka,with good examples i was not aware of that.

This site uses Akismet to reduce spam. Learn how your comment data is processed.