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

Natalka, thanks for mentioning me!

Correct please my last name – Malakshinov ðŸ™‚

Regards,

Sayan Malakshinov

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

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

Thanks Kirk!