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!