What is bigger than infinity?
October 1st, 2007
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]
You should ask Chuch Norris as he has counted to infinity, twice
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
“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
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
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
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
good point
and it is also not greater than BINARY_FLOAT_NAN and GREATEST(NULL,BINARY_DOUBLE_NAN) is NULL
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
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
I learnt something new