You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Following query show comparison results between different DECFLOAT values when arithmetic semantic is used for this:
set list on;
set decfloat traps to;
select
t.*
,iif( "-nan" < "-snan", '-nan LSS -snan', iif( "-nan" > "-snan", '-nan GTR -snan', iif("-nan" = "-snan", '-nan EQU -snan', 'UNKNOWN') ) ) as "Arithmetic: -nan vs to -snan:"
,iif( "-snan" < "-inf", '-snan LSS -inf', iif( "-snan" > "-inf", '-snan GTR -inf', iif("-snan" = "-inf", '-snan EQU -inf', 'UNKNOWN') ) ) as "Arithmetic: -snan vs to -inf:"
,iif( "-inf" < "-0.1", '-inf LSS -0.1', iif( "-inf" > "-0.1", '-inf GTR -0.1', iif("-inf" = "-0.1", '-inf EQU -0.1', 'UNKNOWN') ) ) as "Arithmetic: -inf vs to -0.1:"
,iif( "-0.1" < "-0.10", '-0.1 LSS -0.10', iif( "-0.1" > "-0.10", '-0.1 GTR -0.10', iif("-0.1" = "-0.10", '-0.1 EQU -0.10', 'UNKNOWN') ) ) as "Arithmetic: -0.1 vs to -0.10:"
,iif( "-0.10" < "-0", '-0.10 LSS -0', iif( "-0.10" > "-0", '-0.10 GTR -0', iif("-0.10" = "-0", '-0.10 EQU -0', 'UNKNOWN') ) ) as "Arithmetic: -0.10 vs to -0:"
,iif( "-0" < "0", '-0 LSS 0', iif( "-0" > "0", '-0 GTR 0', iif("-0" = "0", '-0 EQU 0', 'UNKNOWN') ) ) as "Arithmetic: -0 vs to 0:"
,iif( "0" < "0.10", '0 LSS 0.10', iif( "0" > "0.10", '0 GTR 0.10', iif("0" = "0.10", '0 EQU 0.10', 'UNKNOWN') ) ) as "Arithmetic: 0 vs to 0.10:"
,iif( "0.10" < "0.1", '0.10 LSS 0.1', iif( "0.10" > "0.1", '0.10 GTR 0.1', iif("0.10" = "0.1", '0.10 EQU 0.1', 'UNKNOWN') ) ) as "Arithmetic: 0.10 vs to 0.1:"
,iif( "0.1" < "inf", '0.1 LSS inf', iif( "0.1" > "inf", '0.1 GTR inf', iif("0.1" = "inf", '0.1 EQU inf', 'UNKNOWN') ) ) as "Arithmetic: 0.1 vs to inf:"
,iif( "inf" < "snan", 'inf LSS snan', iif( "inf" > "snan", 'inf GTR snan', iif("inf" = "snan", 'inf EQU snan', 'UNKNOWN') ) ) as "Arithmetic: inf vs to snan:"
,iif( "snan" < "nan", 'snan LSS nan', iif( "snan" > "nan", 'snan GTR nan', iif("snan" = "nan", 'snan EQU nan', 'UNKNOWN') ) ) as "Arithmetic: snan vs to nan:"
from (
select
-cast('foo' as decfloat) as "-nan"
,-cast('snan' as decfloat) as "-snan"
,-cast(1/1e-9999 as decfloat) as "-inf"
,-cast(0.1 as decfloat) as "-0.1"
,-cast(0.10 as decfloat) as "-0.10"
,-cast(0 as decfloat) as "-0"
,cast(0 as decfloat) as "0"
,cast(0.10 as decfloat) as "0.10"
,cast(0.1 as decfloat) as "0.1"
,cast(1/1e-9999 as decfloat) as "inf"
,cast('snan' as decfloat) as "snan"
,cast('bar' as decfloat) as "nan"
from rdb$database
) t;
Result:
---------
Arithmetic: -nan vs to -snan: -nan EQU -snan
Arithmetic: -snan vs to -inf: -snan EQU -inf
Arithmetic: -inf vs to -0.1: -inf LSS -0.1
Arithmetic: -0.1 vs to -0.10: -0.1 EQU -0.10
Arithmetic: -0.10 vs to -0: -0.10 LSS -0
Arithmetic: -0 vs to 0: -0 EQU 0
Arithmetic: 0 vs to 0.10: 0 LSS 0.10
Arithmetic: 0.10 vs to 0.1: 0.10 EQU 0.1
Arithmetic: 0.1 vs to inf: 0.1 LSS inf
Arithmetic: inf vs to snan: inf EQU snan
Arithmetic: snan vs to nan: snan EQU nan
It is strange, from my POV, why [s]Nan is equal to infinity - but currently this is no matter; perhaps it is needed for proper index maintenance or work of WHERE-expressions etc.
My question is: why the same result can not be obtained when we use ORDER BY or windowed functions ?
Consider this example:
recreate table test0(n decfloat);
commit;
insert into test0 values( cast('-0' as decfloat ) );
insert into test0 values( cast('NaN' as decfloat ) );
insert into test0 values( cast('sNaN' as decfloat ) );
insert into test0 values( cast('-NaN' as decfloat ) );
insert into test0 values( cast('-SNaN' as decfloat ) );
insert into test0 values( cast('0' as decfloat ) );
insert into test0 values( cast('-inf' as decfloat ) );
insert into test0 values( cast('-0.10' as decfloat ) );
insert into test0 values( cast('0.10' as decfloat ) );
insert into test0 values( cast('inf' as decfloat ) );
insert into test0 values( cast('0.100' as decfloat ) );
insert into test0 values( cast('-0.100' as decfloat ) );
commit;
select n from test0 order by n; -------------------- [ 1 ]
select lead(n)over(order by n) from test0; ----- [ 2 ]
commit;
Added support for special (inf/nan) values when sorting decfloats. The only remaining but unavoidable issue is +/- zero - in order to sort multisegment index correctly we should not distinguish this values, therefore when sorting last/single segment with decfloat order of this values may look surprising: -0 may go after +0.
summary: DECFLOAT values and queries with ORDER BY and/or windowed (analitical) functions => Add support for special (inf/nan) values when sorting DECFLOAT values
Submitted by: @pavel-zotov
Following query show comparison results between different DECFLOAT values when arithmetic semantic is used for this:
set list on;
set decfloat traps to;
select
t.*
,iif( "-nan" < "-snan", '-nan LSS -snan', iif( "-nan" > "-snan", '-nan GTR -snan', iif("-nan" = "-snan", '-nan EQU -snan', 'UNKNOWN') ) ) as "Arithmetic: -nan vs to -snan:"
,iif( "-snan" < "-inf", '-snan LSS -inf', iif( "-snan" > "-inf", '-snan GTR -inf', iif("-snan" = "-inf", '-snan EQU -inf', 'UNKNOWN') ) ) as "Arithmetic: -snan vs to -inf:"
,iif( "-inf" < "-0.1", '-inf LSS -0.1', iif( "-inf" > "-0.1", '-inf GTR -0.1', iif("-inf" = "-0.1", '-inf EQU -0.1', 'UNKNOWN') ) ) as "Arithmetic: -inf vs to -0.1:"
,iif( "-0.1" < "-0.10", '-0.1 LSS -0.10', iif( "-0.1" > "-0.10", '-0.1 GTR -0.10', iif("-0.1" = "-0.10", '-0.1 EQU -0.10', 'UNKNOWN') ) ) as "Arithmetic: -0.1 vs to -0.10:"
,iif( "-0.10" < "-0", '-0.10 LSS -0', iif( "-0.10" > "-0", '-0.10 GTR -0', iif("-0.10" = "-0", '-0.10 EQU -0', 'UNKNOWN') ) ) as "Arithmetic: -0.10 vs to -0:"
,iif( "-0" < "0", '-0 LSS 0', iif( "-0" > "0", '-0 GTR 0', iif("-0" = "0", '-0 EQU 0', 'UNKNOWN') ) ) as "Arithmetic: -0 vs to 0:"
,iif( "0" < "0.10", '0 LSS 0.10', iif( "0" > "0.10", '0 GTR 0.10', iif("0" = "0.10", '0 EQU 0.10', 'UNKNOWN') ) ) as "Arithmetic: 0 vs to 0.10:"
,iif( "0.10" < "0.1", '0.10 LSS 0.1', iif( "0.10" > "0.1", '0.10 GTR 0.1', iif("0.10" = "0.1", '0.10 EQU 0.1', 'UNKNOWN') ) ) as "Arithmetic: 0.10 vs to 0.1:"
,iif( "0.1" < "inf", '0.1 LSS inf', iif( "0.1" > "inf", '0.1 GTR inf', iif("0.1" = "inf", '0.1 EQU inf', 'UNKNOWN') ) ) as "Arithmetic: 0.1 vs to inf:"
,iif( "inf" < "snan", 'inf LSS snan', iif( "inf" > "snan", 'inf GTR snan', iif("inf" = "snan", 'inf EQU snan', 'UNKNOWN') ) ) as "Arithmetic: inf vs to snan:"
,iif( "snan" < "nan", 'snan LSS nan', iif( "snan" > "nan", 'snan GTR nan', iif("snan" = "nan", 'snan EQU nan', 'UNKNOWN') ) ) as "Arithmetic: snan vs to nan:"
from (
select
-cast('foo' as decfloat) as "-nan"
,-cast('snan' as decfloat) as "-snan"
,-cast(1/1e-9999 as decfloat) as "-inf"
,-cast(0.1 as decfloat) as "-0.1"
,-cast(0.10 as decfloat) as "-0.10"
,-cast(0 as decfloat) as "-0"
,cast(0 as decfloat) as "0"
,cast(0.10 as decfloat) as "0.10"
,cast(0.1 as decfloat) as "0.1"
,cast(1/1e-9999 as decfloat) as "inf"
,cast('snan' as decfloat) as "snan"
,cast('bar' as decfloat) as "nan"
from rdb$database
) t;
Result:
---------
Arithmetic: -nan vs to -snan: -nan EQU -snan
Arithmetic: -snan vs to -inf: -snan EQU -inf
Arithmetic: -inf vs to -0.1: -inf LSS -0.1
Arithmetic: -0.1 vs to -0.10: -0.1 EQU -0.10
Arithmetic: -0.10 vs to -0: -0.10 LSS -0
Arithmetic: -0 vs to 0: -0 EQU 0
Arithmetic: 0 vs to 0.10: 0 LSS 0.10
Arithmetic: 0.10 vs to 0.1: 0.10 EQU 0.1
Arithmetic: 0.1 vs to inf: 0.1 LSS inf
Arithmetic: inf vs to snan: inf EQU snan
Arithmetic: snan vs to nan: snan EQU nan
It is strange, from my POV, why [s]Nan is equal to infinity - but currently this is no matter; perhaps it is needed for proper index maintenance or work of WHERE-expressions etc.
My question is: why the same result can not be obtained when we use ORDER BY or windowed functions ?
Consider this example:
recreate table test0(n decfloat);
commit;
insert into test0 values( cast('-0' as decfloat ) );
insert into test0 values( cast('NaN' as decfloat ) );
insert into test0 values( cast('sNaN' as decfloat ) );
insert into test0 values( cast('-NaN' as decfloat ) );
insert into test0 values( cast('-SNaN' as decfloat ) );
insert into test0 values( cast('0' as decfloat ) );
insert into test0 values( cast('-inf' as decfloat ) );
insert into test0 values( cast('-0.10' as decfloat ) );
insert into test0 values( cast('0.10' as decfloat ) );
insert into test0 values( cast('inf' as decfloat ) );
insert into test0 values( cast('0.100' as decfloat ) );
insert into test0 values( cast('-0.100' as decfloat ) );
commit;
select n from test0 order by n; -------------------- [ 1 ]
select lead(n)over(order by n) from test0; ----- [ 2 ]
commit;
Query [ 1 ] will issue:
-0.100
0
Infinity
NaN
sNaN
-0
-Infinity
-NaN
-sNaN
0.10
0.100
(and - imo - this is pointless order)
Query [ 2 ] will fail on:
==========================================
-0.100
0
Infinity
Statement failed, SQLSTATE = 22000
Decimal float invalid operation. An indeterminant error occurred during an operation.
(yes, it is more clear result: Infinity compared with [s]Nan must produce exception, unlless we suppress it using "set decfloat traps to;" statement).
But if we issue in 1st line set decfloat traps to; - result of [ 2 ] become also unclear:
-0.100
0
Infinity
NaN
sNaN
-0
-Infinity
-NaN
-sNaN
0.10
0.100
<null>
PS.
Ticket was created after discuss with Alex.
Commits: e3631c4
The text was updated successfully, but these errors were encountered: