Issue Details (XML | Word | Printable)

Key: CORE-6344
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Alexander Peshkov
Reporter: Alexander Peshkov
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Invalid return type for functions with INT128 /Numeric(38)/ argument

Created: 24/Jun/20 05:35 PM   Updated: 13/Jul/20 11:15 AM
Component/s: Engine
Affects Version/s: 4.0 Beta 2
Fix Version/s: 4.0 RC 1

QA Status: Covered by another test(s)
Test Details:
See:
functional\datatypes\int128-math-functions.fbt
functional\datatypes\int128-agregate-functions.fbt

NOTE-1: currently (06-jun-2020) there are several issues related to TRUNC() and ROUND() results. See .fbt for details.
Test can be adjusted later after additional fixes.

NOTE-2:
Test for windowed functions deferred, see CORE-6357



 Description  « Hide
Some builtin functions, using int128 argument, should return same type value (CEIL, FLOOR). Others (like SQRT, LOG, EXP) should return decfloat(34) and use it for internal calculations. Currently all of them return and use internally double precision.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 24/Jun/20 05:41 PM - edited
Because this change will make existing code (not updated with any direct usage of the new types) use them, I think it requires a compatibility option for client libraries not supporting the new types returned.

Alexander Peshkov added a comment - 25/Jun/20 07:25 AM
It exists in B2, DataTypeCompatibility in firebird.conf

Pavel Zotov added a comment - 06/Jul/20 06:51 PM
SQL> set heading off;
SQL> select cos( 9223372036854775807) from rdb$database;

    0.01180007651280024

But:

https://www.wolframalpha.com/input/?i=cos%289223372036854775807%29&assumption=%22TrigRD%22+-%3E+%22R%22

-- shows:
0.847788007348018782808156148748237678724694266423254799125...

PS

All fine for values up to 2^32 and even more:

SQL> select cos(2147483648) from rdb$database;

     0.2378161945728034

SQL> select cos(2147499999) from rdb$database;

    0.6813789777108772

SQL> select cos(4294967296) from rdb$database;

    -0.8868869151978210

SQL> select cos(5000000000) from rdb$database;

    -0.9677602034277024

Pavel Zotov added a comment - 06/Jul/20 07:20 PM
1) select round(170141183460469231731687303715884105727,0) from rdb$database;
2) select round(170141183460469231731687303715884105727,1) from rdb$database;

Second statement issues:
=======
Statement failed, SQLSTATE = 22003
arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range
=======

3) select round(-170141183460469231731687303715884105728,0) from rdb$database;

Statement failed, SQLSTATE = 22000
Decimal float invalid operation. An indeterminant error occurred during an operation.

4) select round(-170141183460469231731687303715884105728,1) from rdb$database;

Statement failed, SQLSTATE = 22003
arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range


Alexander Peshkov added a comment - 08/Jul/20 02:26 PM
A cos() function always converts it's argument to double precision - therefore it's OK as long as argument's precision does not overflow double's precision. With values larger than it cos() as implemented in firebird becomes a kind of random generator, which is bad, but absolutely not related with this ticket.

Pavel Zotov added a comment - 08/Jul/20 10:33 PM
> A cos() function always converts it's argument to double precision - therefore it's OK as long as argument's precision does not overflow double's precision

COS() is periodical function, its full period 2*pi().

Using DECFLOAT34 datatype it is easy to get values for such numbers as 2^63 by emulating MOD() and substitute literal value for PI (first 39 digits) instead of call FB math function PI().

set sqlda_display on;
-- Value of PI from Wolfram: 3.14159265358979323846264338327950288419716939937510582097494459230781640628620899862803482534211706798214808651328...
recreate table test(x decfloat);
insert into test(x) values(9223372036854775807);

select
  x as SOURCE_ANGLE_IN_RADIANS
 ,cos( x - floor( x / 3.141592653589793238462643383279502884197/2) * 3.141592653589793238462643383279502884197 * 2 ) as ALMOST_EXACT_COSINE
 ,cos( x - floor(x / pi() / 2) * pi() * 2 ) as TOTALLY_WRONG_COSINE
from test;


Output:
=======
SOURCE_ANGLE_IN_RADIANS 9223372036854775807
ALMOST_EXACT_COSINE 0.8477880073480192
TOTALLY_WRONG_COSINE 0.2317799904467769

PS.

0xFF.
What about MOD() ? Why it was not implemented for full support of DECFLOAT datatype ?

Alexander Peshkov added a comment - 10/Jul/20 10:06 AM - edited
I slightly remember school trigonometry :)
To work well for all decfloat values pi should have number of digits comparable with max power of decfloat, i.e. abot 6000. Even for decimal/numeric max scale (127) existing in firebird high precision libraries are not enough.
Also I'm sure that if user needs to calculate trigonometric results for such huge values that means something is wrong, may be with his brain :D

0xff. MOD is also not supported for double precision, can you explain what means mod for *floating point* values?