DECFLOAT values and queries with ORDER BY and/or windowed (analitical) functions
--------------------------------------------------------------------------------
Key: CORE-6219
URL: http://tracker.firebirdsql.org/browse/CORE-6219
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Affects Versions: 4.0 Beta 1
Reporter: Pavel Zotov
Priority: Minor
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.10
-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:
LEAD
==========================================
-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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
