Menu
▾
▴
firebird-devel
|
From: Gabriel J. <gj...@co...> - 2003-06-27 08:17:55
|
Hi List! I have a serious problem in computations in Firebird 1.5: First my environment: Firebird 1.5 Release Candidate 3 WI-T1.5.0.3481 Superserver on Windows XP Home Edition Processor: Athlon XP 1700 I discovered the problem in a stored procedure but I can replicate the problem in the following query: select 5655555.0000 * 32900.0000 from RDB$DATABASE Result: Unsuccessful execution caused by system error that does not preclude sccessful execution of subsequent statements. Integer overflow. The result of an integer operation caused the most significant bit of the result to carry. The same result for queries: select cast(5655555 as numeric(18,4)) * cast(32900 as numeric(18, 4)) from RDB$DATABASE select cast(5655555.0000 * 32900.0000 as numeric(18, 4)) from RDB$DATABASE But for queries: select 5655555.000 * 32900.000 from RDB$DATABASE select cast(5655555 as numeric(18,3)) * cast(32900 as numeric(18, 3)) from RDB$DATABASE I get the result: 186,067,759,500.000032 !!! (obviously wrong) In the stored procedure where I noticed this behavior, I have 3 local variables of type numeric(18,4) and I get the above error in a simple multiply operation: v2 = 5655555; v3 = 32900; v1 = v2 * v3; What am I doing wrong? Or is it a bug in Firebird? Regards, Gabriel Juncu |
|
From: Gabriel J. <gj...@co...> - 2003-06-27 08:36:29
|
Hi List, Friday, June 27, 2003, 11:17:49 AM, I wrote: GJ> Hi List! GJ> I have a serious problem in computations in Firebird 1.5: GJ> First my environment: GJ> Firebird 1.5 Release Candidate 3 WI-T1.5.0.3481 Superserver on Windows XP Home Edition GJ> Processor: Athlon XP 1700 GJ> I discovered the problem in a stored procedure but I can replicate the GJ> problem in the following query: GJ> select 5655555.0000 * 32900.0000 from RDB$DATABASE GJ> Result: GJ> Unsuccessful execution caused by system error that does not GJ> preclude sccessful execution of subsequent statements. GJ> Integer overflow. The result of an integer operation caused the most GJ> significant bit of the result to carry. GJ> The same result for queries: GJ> select cast(5655555 as numeric(18,4)) * cast(32900 as numeric(18, 4)) from RDB$DATABASE GJ> select cast(5655555.0000 * 32900.0000 as numeric(18, 4)) from RDB$DATABASE GJ> But for queries: GJ> select 5655555.000 * 32900.000 from RDB$DATABASE GJ> select cast(5655555 as numeric(18,3)) * cast(32900 as numeric(18, 3)) from RDB$DATABASE GJ> I get the result: 186,067,759,500.000032 !!! (obviously wrong) Sorry that I misinformed you, but the above result is obtained in IBExpert. In isql the result is correct: 186,067,500.000000 Sorry once again! Still I think that the bellow statement should work: GJ> In the stored procedure where I noticed this behavior, I have 3 local GJ> variables of type numeric(18,4) and I get the above error in a simple GJ> multiply operation: GJ> v2 = 5655555; GJ> v3 = 32900; GJ> v1 = v2 * v3; GJ> What am I doing wrong? Or is it a bug in Firebird? Thank you, Gabriel Juncu |
|
From: Calin I. P. <pc...@rd...> - 2003-06-27 15:57:26
|
----- Original Message ----- From: "Gabriel Juncu" > GJ> In the stored procedure where I noticed this behavior, I have 3 local > GJ> variables of type numeric(18,4) and I get the above error in a simple > GJ> multiply operation: > > GJ> v2 = 5655555; > GJ> v3 = 32900; > GJ> v1 = v2 * v3; > > GJ> What am I doing wrong? Or is it a bug in Firebird? > Firebird works corectly. You are trying to multiply 2 fixed precision numerics together. Both have precision 4 so you have 10 digits multiplied with 9 digits this results in 19 digits wich is the limit for int64 integer. Therefore you have integer overflow. If you want those two numbers multiplied then do: v1=Cast(cast(v2 as double precision)*cast(v3 as double precision) as numeric(18,4); There you go. Ciao, Best regards, Application Developer Calin Iancu, Pirtea S.C. SoftScape S.R.L. pc...@rd... |
|
From: Gabriel J. <gj...@co...> - 2003-06-30 07:09:27
|
Hi Calin,
Friday, June 27, 2003, 6:30:19 PM, you wrote:
CIP> ----- Original Message -----
CIP> From: "Gabriel Juncu"
>> GJ> In the stored procedure where I noticed this behavior, I have 3 local
>> GJ> variables of type numeric(18,4) and I get the above error in a simple
>> GJ> multiply operation:
>>
>> GJ> v2 = 5655555;
>> GJ> v3 = 32900;
>> GJ> v1 = v2 * v3;
>>
>> GJ> What am I doing wrong? Or is it a bug in Firebird?
>>
CIP> Firebird works corectly.
CIP> You are trying to multiply 2 fixed precision numerics together.
CIP> Both have precision 4 so you have 10 digits multiplied with 9 digits
CIP> this results in 19 digits wich is the limit for int64 integer.
CIP> Therefore you have integer overflow.
Calin,
First of all, thank you for your answer!
Well, I don't want to add more to this thread, and I guess that if the
case above is "as designed" I should move this to Firebird-Support, but I
still think that this is a problem.
I understand that it is required some internal scaling and the
multiply operation shoud be done on 128 bit, but in my opinion this
shoud be done internally transparent to user.
I don't think that I should get overflow from 4 decimal digits that I
don't request.
For example, Borland Delphi does this internal scaling for Currency
type (8 byte fixed point 4 decimals).
CIP> If you want those two numbers multiplied then do:
CIP> v1=Cast(cast(v2 as double precision)*cast(v3 as double precision) as
CIP> numeric(18,4);
Well, is not exactly the same thing, double precision has only 15 digits
precision. I use numeric(18,4) for monetary calculations and to store
Delphi currency, an if I pass trough double precision I loose 3
digits.
If Firebird would have a float type simmilar to Extended in Delphi (10
bytes float, so 18 digits in mantissa) then maybe the cast would be
acceptable.
A small example:
712345678.1234 x 123456.5678 = 87943752508278.50886652
Firebird:
CREATE PROCEDURE W_NUMTST
RETURNS (
n NUMERIC(18,4),
d double precision)
AS
declare variable n1 numeric(18,4);
declare variable n2 numeric(18,4);
declare variable d1 double precision;
declare variable d2 double precision;
begin
n1 = 712345678.1234;
n2 = 123456.5678;
d1 = 712345678.1234;
d2 = 123456.5678;
n = cast(n1 as double precision) * cast (n2 as double precision);
d = d1 * d2;
suspend;
end
select * from w_NUMTST;
Results:
N D
===================== =======================
87943752508278.5152 87943752508278.52
In Delphi:
Currency x Currency: 87943752508278.5089 (CORRECT!)
Double x Double: 87943752508278.5156
Extended x Extended: 87943752508278.5089 (CORRECT again)
So, can anybody tell me how can I get correct arithmetic in Firebird
on 18 digits without overflow errors?
Thank you,
Gabriel
|
|
From: Calin I. P. <pc...@rd...> - 2003-06-30 09:48:54
|
----- Original Message ----- From: "Gabriel Juncu" > So, can anybody tell me how can I get correct arithmetic in Firebird > on 18 digits without overflow errors? > Hmmm, yes. Use numeric(18,0) as intermediate values then you only get a overflow if the result realy overflows. Ex: CREATE PROCEDURE W_NUMTST (Param1 numeric(18,4), param2 numeric(18,4)) RETURNS ( Res NUMERIC(18,4)) AS declare variable n1 numeric(18,0); declare variable n2 numeric(18,0); declare variable n numeric(18,0); begin n1 = param1*10000; n2 = param2*10000; n = n1*n2; res = n/10000; suspend; end Ciao, Best regards, Application Developer Calin Iancu, Pirtea S.C. SoftScape S.R.L. pc...@rd... |
|
From: Gabriel J. <gj...@co...> - 2003-06-30 10:51:24
|
Hi Calin, Monday, June 30, 2003, 12:48:43 PM, you wrote: CIP> ----- Original Message ----- CIP> From: "Gabriel Juncu" >> So, can anybody tell me how can I get correct arithmetic in Firebird >> on 18 digits without overflow errors? >> CIP> Hmmm, yes. CIP> Use numeric(18,0) as intermediate values then you only get a overflow CIP> if the result realy overflows. CIP> Ex: CIP> CREATE PROCEDURE W_NUMTST (Param1 numeric(18,4), param2 numeric(18,4)) CIP> RETURNS ( CIP> Res NUMERIC(18,4)) CIP> AS CIP> declare variable n1 numeric(18,0); CIP> declare variable n2 numeric(18,0); CIP> declare variable n numeric(18,0); CIP> begin CIP> n1 = param1*10000; CIP> n2 = param2*10000; CIP> n = n1*n2; CIP> res = n/10000; CIP> suspend; CIP> end You hurried a bit. My first example (and problem) was: Param1 = 5655555; Param2 = 32900; Result = Param1 * Param2; so (56555555 * 10000) * (32900 * 10000) gives 20 digits -> and the result really overflows ;-) That is exactly what Firebird does intenally, and again, if it is made on 64 bits it gives numeric overflow (except this time is much easier to understand it ;-) All I am asking here is if the developers think that this is the normal way that numeric multiply shoud behave, so I should abbandon any hope of seeing this corrected (from my point of view) and start to pollute my stored procedures with computing artifices so I can get correct results for a simple multiply operation. Thanks again for your answer, Gabriel |
|
From: Calin I. P. <pc...@rd...> - 2003-06-30 11:50:09
|
----- Original Message ----- From: "Gabriel Juncu" > You hurried a bit. My first example (and problem) was: > > Param1 = 5655555; > Param2 = 32900; > Result = Param1 * Param2; > > so (56555555 * 10000) * (32900 * 10000) gives 20 digits -> and the > result really overflows ;-) :)) You're absolutely right. I didn't think it through enough. Of course there's always the posibility to write a UDF multiply lib with extended as intermediate type. ;-) As for firebird this is the intended behaviour, and too many rely on it already. Ciao, Best regards, Application Developer Calin Iancu, Pirtea S.C. SoftScape S.R.L. pc...@rd... |
|
From: Gabriel J. <gj...@co...> - 2003-06-30 12:54:02
|
Hi Calin, Monday, June 30, 2003, 2:49:40 PM, you wrote: CIP> ----- Original Message ----- CIP> :)) You're absolutely right. I didn't think it through enough. CIP> Of course there's always the posibility to write a UDF multiply lib with CIP> extended CIP> as intermediate type. ;-) Of course :) One can write an udf to do almost all than Firebird can't, but question is if some of these should'n go into Firebird. CIP> As for firebird this is the intended behaviour, and too many rely on it CIP> already. That's what I was afraid of... As of "rely on", I don't understand how can the "corrected" multiply broke existing applications. Are you saying that someone relies on the overflow exception to check that the internal result does not exceed 18 digits?! Because what it gets in the target variable will surely be less or equal to 18 digits anyway! The results that don't throw exception now will remain unchanged even if the internal multiplication is made on 128 bits! As I see it, it will have much less impact on existing applications than, let's say, short-circuit boolean evaluation that appeared in 1.5b3. I still think that current behaviour should be changed, but as I see I am the only one with that oppinion, so I'd better learn to live with it ;-) Thank you all for your patience, Gabriel |
|
From: Gabriel J. <gj...@co...> - 2003-06-30 13:03:42
|
Hi Ivan, Monday, June 30, 2003, 2:49:38 PM, you wrote: IP> Look at documentation, rules for multiplication: IP> "If both operands are exact numeric, then multiplying or dividing the operands produces IP> an exact numeric with a precision of 18 and a scale equal to the sum of the scales of the IP> operands." Whoa, where did you find this? I searched the documentation before even posting to the list, and now, after I received your answer, I redownloaded documentation, searched again and still didn't find the above paragraph! (is by any chance my new Adobe Reader 6 using fixed point numerics inside? :->) But never mind, I understand, this is as designed and is correct and there is no intention to change anything in this matter, so I should find alternative solutions. Thank you, Gabriel |
|
From: Ivan P. <Iva...@se...> - 2003-07-03 22:28:59
|
> IP> Look at documentation, rules for multiplication: > IP> "If both operands are exact numeric, then multiplying or dividing the operands produces > IP> an exact numeric with a precision of 18 and a scale equal to the sum of the scales of the > IP> operands." > > Whoa, where did you find this? I searched the documentation before > even posting to the list, and now, after I received your answer, I > redownloaded documentation, searched again and still didn't find the > above paragraph! It is in Release Notes. (Beta Doc for IB6) Ivan > > select 5655555.0000 * 32900.0000 from RDB$DATABASE > > > > Result: > > Unsuccessful execution caused by system error that does not > > preclude sccessful execution of subsequent statements. > > Integer overflow. The result of an integer operation caused the most > > significant bit of the result to carry. > > > Look at documentation, rules for multiplication: > "If both operands are exact numeric, then multiplying or dividing the operands produces > an exact numeric with a precision of 18 and a scale equal to the sum of the scales of the > operands." > > Since you are multiplying two numeric(18,4) values, the result must fit into numeric(18,8). > (But your result 186067759500.00000000 is numeric(20,8) ). |
|
From: Gabriel J. <gj...@co...> - 2003-07-04 07:16:57
|
Hi Ivan, Thursday, July 3, 2003, 9:59:48 PM, you wrote: >> IP> Look at documentation, rules for multiplication: >> IP> "If both operands are exact numeric, then multiplying or dividing the operands produces >> IP> an exact numeric with a precision of 18 and a scale equal to the sum of the scales of the >> IP> operands." >> >> Whoa, where did you find this? I searched the documentation before >> even posting to the list, and now, after I received your answer, I >> redownloaded documentation, searched again and still didn't find the >> above paragraph! IP> It is in Release Notes. (Beta Doc for IB6) Well, that explains it: Beta Doc (full set of manuals) downloaded from www.firebirdsql.org does not contain ReleaseNotes.pdf. Also Firebird15RC3 win32 kit does not contain ReleaseNotes.pdf. In ChangeLog.txt, WhatsNew.txt and InstallNotes.txt from Firebird 1.5RC3 there is no mention about this behaviour. So maybe I have to download Interbase 6.0 beta installation kit to find out such important (for me at least) behaviour of numerics (or to post a message on firebird-devel, wich I did ;-)) Thank you again, Gabriel |
|
From: Dmitry Y. <di...@us...> - 2003-07-04 07:40:48
|
Gabriel, > IP> It is in Release Notes. (Beta Doc for IB6) > > Well, that explains it: Beta Doc (full set of manuals) downloaded from > www.firebirdsql.org does not contain ReleaseNotes.pdf. > Also Firebird15RC3 win32 kit does not contain ReleaseNotes.pdf. > In ChangeLog.txt, WhatsNew.txt and InstallNotes.txt from Firebird > 1.5RC3 there is no mention about this behaviour. FB documentation reflects only changes done _after_ the source code has been forked, i.e. native FB innovations only. The mentioned behaviour was introduced in IB 6.0, so you should search Borland manuals for the details. Although it's weird that IB 6.0 Release Notes isn't included in the beta documentation set. Dmitry |
|
From: Nickolay S. <sk...@bs...> - 2003-07-04 19:09:59
|
Hello, Gabriel ! > Hi Ivan, > Thursday, July 3, 2003, 9:59:48 PM, you wrote: >>> IP> Look at documentation, rules for multiplication: >>> IP> "If both operands are exact numeric, then multiplying or dividing the operands produces >>> IP> an exact numeric with a precision of 18 and a scale equal to the sum of the scales of the >>> IP> operands." >>> >>> Whoa, where did you find this? I searched the documentation before >>> even posting to the list, and now, after I received your answer, I >>> redownloaded documentation, searched again and still didn't find the >>> above paragraph! IP>> It is in Release Notes. (Beta Doc for IB6) > Well, that explains it: Beta Doc (full set of manuals) downloaded from > www.firebirdsql.org does not contain ReleaseNotes.pdf. > Also Firebird15RC3 win32 kit does not contain ReleaseNotes.pdf. > In ChangeLog.txt, WhatsNew.txt and InstallNotes.txt from Firebird > 1.5RC3 there is no mention about this behaviour. > So maybe I have to download Interbase 6.0 beta installation kit to > find out such important (for me at least) behaviour of numerics (or to > post a message on firebird-devel, wich I did ;-)) This behaviour is very strictly defined by SQL99 standard (and possibly earlier standards too) this is why it is considered well-known and is not documented explicitly. Nickolay Samofatov |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X