Implement new 16 byte floating point datatype [CORE4188]

[firebird-automations]

Submitted by: @pavel-zotov

Relate to CORE4187

Usage of DOUBLE type often leads to wrong results, e.g.: POWER(2, 52) gives *odd* number (this can not occur with any power of 2 except 0),
POWER(3, 38) gives 1350851717672992000 rather than (correct) 1350851717672992089 (delta = 89) and so on.

This errors can lead to wrong result of calculations even with numbers of small magnitude, e.g:

SQL> set list on; select mon$sql_dialect from mon$database;

MON$SQL_DIALECT 3

SQL> select round(1608.90 * 5 / 100, 2) from rdb$database;

ROUND 80.44 -- NB: correct result is 80.45 !

SQL> show version; ISQL Version: LI-T3.0.0.30590 Firebird 3.0 Alpha 1 Server version: Firebird/Linux/AMD/Intel/x64 (access method), version "LI-T3.0.0.30590 Firebird 3.0 Alpha 1"

I think that necessity of new datatype (maybe name it "EXTENDED" ?) is evident.

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

Link: This issue relate to [CORE4187](https://github.com/FirebirdSQL/firebird/issues?q=CORE4187+in%3Atitle) \[ [CORE4187](https://github.com/FirebirdSQL/firebird/issues?q=CORE4187+in%3Atitle) \]

[firebird-automations]

Commented by: @krilbe

Delphi has "since forever" had an extra floating point type called Extended that uses 10 byte (80 bit) and according to the specs i supports 19 significant digits and a an exponent (base 10) ranging from -4932 to +4932. I'm not sure if this is a type that exists outside Delphi, e.g. in hardware, but perhaps it would be a good idea for Firebird? Seems large enough for most real world scenarios and instead of yet another type/format, why not use something that's already "out there"?

After checking the net I found this. http://en.wikipedia.org/wiki/Floating_point It claims that "Double extended" is hardware supported by x86 hardware, and it seems to be the same type that Delphi offers. It also mentions that C usually makes it available as "long double". Could this type be added to Firebird and would it meet your needs Pavel?

[firebird-automations]

Commented by: @pavel-zotov

> Could this type be added to Firebird and would it meet your needs Pavel?

I think that only bulk of tests can show is it (80 bits) enough for daily routines or not.

Another (and I think - better) alternative is pass arguments from FB to java stored procedure which, in turn, uses built-in BigDecimal class functionality. Results of calculations in this manner can be obtained with arbitrary precision, no rounding errors at all (see: http://docs.oracle.com/javase/1.5.0/docs/api/java/math/BigDecimal.html ) Unfortunatelly (AFAIK) Adriano not yet finished the work on this subject.

[firebird-automations]

Commented by: @dyemanov

We had plans to have BigDecimal supported natively inside the engine, but this has nothing to do with Java related work by Adriano. However, this project is about long *exact* numerics, not approximate numerics. So this is unlikely to affect the POWER issue which the SQL standard require to return an approximate number.

[firebird-automations]

Commented by: @pavel-zotov

> this project is about long *exact* numerics, not approximate numerics

So, this would be like BigInteger in java, isn`t ? http://docs.oracle.com/javase/1.5.0/docs/api/java/math/BigInteger.html

[firebird-automations]

Commented by: @dyemanov

Yes, more or less.

[firebird-automations]

Commented by: @pavel-zotov

> We had plans to have BigDecimal supported natively

You say "had" rather than "have" :-) Does it mean that these intentions were cancelled or they are frozen for some time ?

[firebird-automations]

Commented by: @dyemanov

Just postponed. Please let's avoid polluting this ticket with unrelated questions.

[firebird-automations]

Commented by: @dyemanov

Shouldn't this ticket be closed now, given the new DECFLOAT data type?