Sorting decimal floats

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

Sorting decimal floats

Alex Peshkoff
Decimal float numbers have an interesting feature - same value may be
represented with different precision - compare for example 0.5 and
0.500. DB2 has special function to compare decimal float numbers, and it
works correctly with such values, but what about traditional compare (if
a=b then ...) treats them equal and it's correct. When sorting such
values they should also be treated as equal (see also And yes, decfloat
value can also be '-0'.

That does not cause problems when building index keys but is not ideal
for sort keys. The difference between that keys that is important here
is that for sort keys we should be able to restore original value from
the key. That requirement is already violated for floating point values
after fix for CORE-3547 (we loose -0 value) but looks like it does not
cause serious troubles here. Unfortunately for decimal floats things
look (on my mind) worse. An output of select decFloatValue from table
depends upon is it ordered by that value and what is even more worse
upon presence of an index - when index is used field's values are
extracted from table and are therefore precise. If you restore and
try three following statements:

select * from dtst; // plain unordered
select * from dtst plan (dtst natural) order by x; // plan frces use of
sort instead use of index
select * from dtst plan order by x; // use index to order data retrieval

you will see how 0.500 changes in case of sort.

You may also look at behavior of plus/minus zero, very well seen in same
selects from QTST table. I've left -0 and +0 different sort keys (like
it was before fix of 3547) in order to let you see an effect, this may
be changed in single line of code.

I wonder - how should we better handle that issues? Ignore like we did
for plus/minus 0 in 3547? Or do something better like keeping original
values in sort block?


Check out the vibrant tech community on one of the world's most
engaging tech sites,!
Firebird-Devel mailing list, web interface at