Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add support for special (inf/nan) values when sorting DECFLOAT values [CORE6219] #6463

Closed
firebird-automations opened this issue Jan 9, 2020 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

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\.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.

Commits: e3631c4

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 2 [ 10888 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: DECFLOAT values and queries with ORDER BY and/or windowed (analitical) functions => Add support for special (inf/nan) values when sorting DECFLOAT values

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants