Submitted by: Paulius Pazera (ppazera)

when we have slightly different double precision values in database, e.g.

1 (stored as 3ff0 0000 0000 000016 64-bit double precision value, see http://en.wikipedia.org/wiki/Double_precision)
≈ 1.0000000000000002 (3ff0 0000 0000 000116)
≈ 1.0000000000000004 (3ff0 0000 0000 000216, more precise decimal representation could be found via http://www.binaryconvert.com)

in the code they are correctly treated as different values,
but currently isql does not have a way to display all 17 significant digits and they look the same:

select cast(1.0000000000000000 as double precision) firstValue,
/* isql incorrectly shows following two values being the same as first one */
cast(1.0000000000000002 as double precision) secondValue,
cast(1.0000000000000004 as double precision) thirdValue,
/* following is just to show that those two double precision values are really different */
cast(1.0000000000000004 as double precision)-cast(1.0000000000000002 as double precision) difference,
/* as opposed to following two values which have the same 64-bit binary representation */
cast(1.00000000000000021 as double precision)-cast(1.00000000000000024 as double precision) no_difference,
/* following is to show that comparison works as expected on those values */
(select 'different' from rdb$database where cast(1.0000000000000004 as double precision)<>cast(1.0000000000000002 as double precision)) different,
(select 'equal' from rdb$database where cast(1.0000000000000002 as double precision)=cast(1.0000000000000002 as double precision)) equal
from rdb$database;

         FIRSTVALUE             SECONDVALUE              THIRDVALUE              DIFFERENCE           NO\_DIFFERENCE DIFFERENT EQUAL  

======================= ======================= ======================= ======================= ======================= ========= ======
1.000000000000000 1.000000000000000 1.000000000000000 2.220446049250313e-16 0.0000000000000000 different equal

that confuses user, makes him think that values in DB are identical, that the code should produce one result, but he sees completely different results, and even may start to suspect that code isn't working fine, that comparison operator doesn't work well on floating point values. Basically it makes code debugging difficult

if isql had a way to display all 17 significant digits, then user would see that all three values are different, e.g.

         FIRSTVALUE             SECONDVALUE              THIRDVALUE              

======================= ======================= =======================
1.0000000000000000 1.0000000000000002 1.0000000000000004

and then he would see which code blocks are really executed and will understand easier why he gets the result he sees

please note that this request is not about floating point decimal to binary conversion issues which introduces some discrepancy (i.e. not about casting string to double precision), and not about calculations which could increase that discrepancy significantly making e.g. 13th significant digit unreliable (i.e. not about how those values were derived, not about if can trust those values up to the last bit/digit, not about which digit is unreliable), it's just about ability to see what we already have in database (if two values are different only by last significant 17th digit, then we want ability to see that using isql)

other users may want to see only 2 or 4 digits after decimal point, so probably it would be good idea to extend this request and allow user to specify double precision formatting mask for isql, e.g. via command line switch/option or something else