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:
Nan-Pro-1

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.

4 comments

Leave a Reply to Sayan Malakshinov Cancel reply

Your email address will not be published. Required fields are marked *

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