What is bigger than infinity?

Nan


select
  BINARY_DOUBLE_INFINITY INF, 
  BINARY_DOUBLE_NAN NAN,
  greatest(BINARY_DOUBLE_INFINITY, BINARY_DOUBLE_NAN) GRE
from t;

INF NAN GRE
--- --- ---
Inf Nan Nan

Nan means not a number. It could be square root of -1, log of -1, 0/0, acos(1000), Inf-Inf, etc…


select
  SQRT(-1d),
  LN(-1d),
  0/0d,
  acos(1000d),
  BINARY_DOUBLE_INFINITY-BINARY_DOUBLE_INFINITY
from t;
SQR LN- 00D ACO BIN
--- --- --- --- ---
Nan Nan Nan Nan Nan

According to the doc, it is greater than any value, inclusive positive infinity.

To check if a value is nan, it could be compared to BINARY_DOUBLE_NAN.
where :z = BINARY_DOUBLE_NAN
There is a function NANVL(:z, :y) which evaluates to :y when :z is equal Nan. if :z is not equal to Nan and :y is not null, then it evaluates to :z. NANVL evaluates to NULL when :z or :y is null.


select NANVL(1,null) from dual;
NANVL
------
[null]

Published by

Laurent Schneider

Oracle Certified Master

11 thoughts on “What is bigger than infinity?”

  1. but note than twice infinity is not bigger than infinity :-)

    select *
    from dual
    where binary_double_infinity*2
    > binary_double_infinity;

    No rows selected.

    Anyway, I will ask him next time I see him on TV

  2. “According to the doc, it [BINARY_DOUBLE_NAN] is greater than any value, inclusive positive infinity.”

    it is not greater than itself:

    SQL > select 1 from dual where BINARY_DOUBLE_NAN > BINARY_DOUBLE_NAN;

    no rows selected

  3. I can’t wait till Oracle will have implemented constants
    Aleph-0, Aleph-1, …

    Then, finally, continuum hypothesis will be trivially solved by
    select 1 from dual where power(Aleph-0) = Aleph-omega

    :-D

  4. I can’t wait till Oracle will have implemented constants
    Aleph-0, Aleph-1, …

    Then, finally, continuum hypothesis will be trivially solved by
    select 1 from dual where power(Aleph-0) = Aleph-omega

  5. I can’t wait till Oracle will have implemented constants
    Aleph-0, Aleph-1, …
    Then, finally, continuum hypothesis will be trivially solved by
    select 1 from dual where power(Aleph-0) = Aleph-omega

  6. I can’t wait until Oracle will have implemented the different infinite cardinals
    Aleph-0, Aleph-1, …
    Then, continuum hypothesis will be very elegantly decided by the query

    select null
    from dual
    where power(2, ALEPH_0) = ALEPH_1

    bets on the result are welcome

  7. I can’t wait till Oracle will have implemented the different infinite cardinals
    aleph-0, aleph-1, …

    then, continuum hypothesis will very elegantly be decided by the query

    select null
    from dual
    where power(2, ALEPH_0) = ALEPH_1

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>