Wednesday, August 10, 2011

Oracle's Idea of Infinity

In version 10, Oracle introduced binary_double and binary_float datatypes. If you select 1c or 1e from dual, the number 1 is given an alias of C or E respectively:

SQL> select 1c from dual
  2  /

         C
----------
         1

SQL> select 1e from dual
  2  /

         E
----------
         1

SQL>

If you select 1d from dual, it is treated as a binary_double datatype:

SQL> select 1d from dual
  2  /

        1D
----------
  1.0E+000

SQL>

If you select 1f from dual, it is treated as a binary_float datatype:

SQL> select 1f from dual
  2  /

        1F
----------
  1.0E+000

SQL>

You cannot divide 1c or 1e by zero:

SQL> select 1c/0 from dual
  2  /
select 1c/0 from dual
         *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> select 1e/0 from dual
  2  /
select 1e/0 from dual
         *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL>

But 1d or 1f divided by zero is infinite. This is not what I learnt at school. In those days, any number divided by zero was undefined.

SQL> select 1d/0 from dual
  2  /

      1D/0
----------
       Inf

SQL> select 1f/0 from dual
  2  /

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

SQL>

Oracle also has the concept of negative infinity:

SQL> select -1d/0 from dual
  2  /

     -1D/0
----------
      -Inf

SQL> select -1f/0 from dual
  2  /

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

And you can even test if a value is infinite:

SQL> select 'Yes' as Yes from dual
  2  where 1d/0 is infinite
  3  /

YES
---
Yes

SQL> select 'Yes' as Yes from dual
  2  where -1f/0 is infinite
  3  /

YES
---
Yes

SQL>

No comments: