Wrong results of cast( power(x, n) as bigint) for some values
-------------------------------------------------------------
Key: CORE-4187
URL: http://tracker.firebirdsql.org/browse/CORE-4187
Project: Firebird Core
Issue Type: Bug
Components: Engine
Reporter: Pavel Zotov
Some values that returns from built-in POWER( x, k) function can not be
properly casted to BIGINT type.
The following is simple test for x = 3...1024 and 'k' up to some bound that
provide result be exactly less than 2^63-1 (to avoid numeric overflow ).
DDL:
====
set term ^;
-- stored proc for calculating precise results via only integer arithemetics
create or alter procedure sp_pwr(a_base int, n_power int) returns (p bigint) as
declare i int = 2;
begin
p=cast(decode(n_power, 0,1, a_base) as bigint);
while (i<=n_power) do begin
p = p * a_base;
i=i+1;
end
suspend;
end^
set term ;^
commit;
Query (requires FB 2.1 and later due to recursive CTE):
=====
with recursive
r1 as(select 3 x from rdb$database union all select x+1 from r1 where x<1024)
,r2 as(select x, cast(floor(63*ln(2)/ln(x)) as int)-1 p_max from r1)
,nx as(select 2 k from rdb$database union all select k+1 from nx where k<63)
,p2 as(
select
r2.x,
x.k,
cast( power(r2.x,x.k) as bigint) "power(x,k)_bigint",
power(r2.x,x.k) "power(x,k)_double",
s.p "power(x,k)_precise"
from r2
join nx x on x.k<=r2.p_max
left join sp_pwr( r2.x, x.k ) s on 1=1
)
select
p2.*,
p2."power(x,k)_bigint" - p2."power(x,k)_precise" diff_bigint,
p2."power(x,k)_double" - p2."power(x,k)_precise" diff_double
from p2
where p2."power(x,k)_bigint" <> p2."power(x,k)_precise"
order by abs(p2."power(x,k)_bigint" - p2."power(x,k)_precise") desc,x
rows 10
;
Result:
======
X K power(x,k)_bigint power(x,k)_double power(x,k)_precise
DIFF_BIGINT DIFF_DOUBLE
== === ==================== ====================== =====================
=========== ==================
3 38 1350851717672992000 1.350851717672992e+18 1350851717672992089
-89 0.000000000000000
5 26 1490116119384765696 1.490116119384766e+18 1490116119384765625
71 0.000000000000000
13 16 665416609183179904 6.654166091831799e+17 665416609183179841
63 0.000000000000000
5 25 298023223876953152 2.980232238769532e+17 298023223876953125
27 0.000000000000000
7 21 558545864083284032 5.585458640832840e+17 558545864083284007
25 0.000000000000000
15 15 437893890380859392 4.378938903808594e+17 437893890380859375
17 0.000000000000000
3 36 150094635296999136 1.500946352969991e+17 150094635296999121
15 0.000000000000000
9 18 150094635296999136 1.500946352969991e+17 150094635296999121
15 0.000000000000000
27 12 150094635296999136 1.500946352969991e+17 150094635296999121
15 0.000000000000000
3 37 450283905890997376 4.502839058909974e+17 450283905890997363
13 0.000000000000000
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Introducing Performance Central, a new site from SourceForge and
AppDynamics. Performance Central is your source for news, insights,
analysis and resources for efficient Application Performance Management.
Visit us today!
http://pubads.g.doubleclick.net/gampad/clk?id=48897511&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
