current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 3 down vote favorite

We've got a table in an Oracle DB defined as:

CREATE TABLE AVALUES
(
  ACODE   VARCHAR2(4) NOT NULL,
  ATYPE   NUMBER NOT NULL,
  ANAME   VARCHAR2(50),
  CREATED DATE DEFAULT SYSDATE
)

Within Delphi we have a query in an ADOQuery component akin to this which returns the value to our application:

with qryComp do
begin
  Close;
  SQL.Text := 
    'SELECT ATYPE FROM AVALUES ORDER BY CREATED';
  Open;
  while not EOF do
  begin
    AddComponents('NAME' + FieldByName('ATYPE').AsString);
    Next;
  end;
  Close;
end;

Deployed on many various client PCs this has worked fine for years, and nothing in our code has changed. On a few client PCs however it's recently started returning, say, 1.999999999969 instead of 2, which causes the application to crash. We've tried looking for the problem but it's very intermittent - connected via remote desktop to the client computer, we can't replicate it at all.

Any suggestions for things I can do to investigate this further? As it's intermittent and only happens on a few computers it's difficult to debug. I think it might be a problem with the Oracle client, but I'm not sure how we can actually verify that.

Thanks for any help.

share|improve this question
1  
Integers are exactly representable in IEEE754 floating point. There would have to be calculations performed for a value like this to be returned. Seems pretty odd. How are you putting the values into the database? Are you performing floating point calculations at all? If you think this is an integer why are you storing as floating point? If it really is an integer then the solution is to store as an integer. – David Heffernan May 22 '12 at 12:53
    
Does ATYPE really needs Oracle NUMBER full floating point precision? Or could you declare it something alike NUMBER(6)? NUMBER handles different situations depending on how you declare it. Using the proper declaration will also save space for the column size, which is given by the formula ROUND((length(p)+s)/2))+1 where p is the precision and s is 0 or 1 depending on the number sign. If you just use NUMBER, p = 38. Also NUMBER doesn't store data in IEEE754 format. BINARY_FLOAT and BINARY_DOUBLE do. See "Oracle Database SQL Reference" for details. – Mad Hatter May 24 '12 at 7:19
up vote 13 down vote accepted

The only thing I can see possible is differences between the FPU control word on client machines causing the precision to be handled differently, because of inherent rounding issues with dealing with floating point types. (See Set8087CW in the Delphi docs; the link is for XE2's documentation, but there's no significant change recently that I'm aware of, so they should work.)

There are four ways to fix it (one not probable, three fairly easy):

  • Change the database column to actually be an integer type instead of a NUMBER

  • Directly ask for an integer value, and convert it yourself

    AddComponent('Name' + IntToStr(FieldByName('ATYPE').AsInteger));

  • Change the code that uses the column, either at the point of use:

    AddComponents(Format('NAME%d', [FieldByName('ATYPE').AsInteger]));

or in AddComponents itself:

procedure AddComponents(Prefix: string; Value: Integer);
begin
  DoWhateverIDo(Prefix + IntToStr(Value));
end;

// calling code
AddComponents('Name', FieldByName('ATYPE').AsInteger);
  • Explicitly set the value of the 8087CW before working with the database, and set it back when you're finished. This seems to be the worst option to me; there's an example of doing this at the documentation link I posted above.
share|improve this answer
1  
Hi Ken! Thanks for the pointer towards Set8087CW - it turns out the problem is that a 3rd party DLL is fiddling with this value under certain circumstances and now we know that, we can stop it. :) – Kieran May 22 '12 at 14:33
2  
Excellent! Glad I could help. :) – Ken White May 22 '12 at 14:47
4  
If the problem is with the control word then you must be doing floating point calcs. But if that is so, and you app is sensitive to the difference between 2 and 1.999999999 then you have a problem. If the sensitivity leads to a crash then I really think you are doing something badly wrong. Just running on different hardware could lead to similar differences and maintaining control of the FP control word is difficult. If this really is an integer then you must deal with the problem at source and not try to paper over the cracks. – David Heffernan May 22 '12 at 15:09
1  
@David, there are known issues with Delphi and some DLLs that change the FP control word, resulting in exceptions in Delphi after the DLL's functionality is used. Try searching CodeNewsFast with 'Set8087CW`, and you'll find 500+ related articles. IIRC, there are a couple of specific products, possibly from MS, like IE9 that cause problems with Delphi when they're used, but I'll leave identifying them from the articles as an exercise for you. :) – Ken White May 23 '12 at 1:28
    
@Ken as the author of an finite element structural analysis software, I happen to know more than I care to about the troubles of floating point control words. I wouldn't say the issues are with Delphi per se, more that the whole system is anarchy. My point in this question is that if code doing integer calcs is sensitive to the fp control word then it is fatally flawed. I understand perfectly how such sensitivity can arise. But if it does, then OP really should stop using FP as if it were exact. It is not. – David Heffernan May 23 '12 at 6:14
up vote 2 down vote

I encountered a similar problem with the data type Currency (Blog article: "Why 1.99 suddenly no longer equals 1.99"). It looks like some DLLs modify a FPU (processor) control word. This explains why the problem is machine-dependent. I also found that Delphi contains a SafeLoadLibrary function, which restores the control word. But it is not a big help, because after loading the DLL, still any call of a DLL function could mess up the control word again.

share|improve this answer
    
Thanks, this is actually the problem. – Kieran May 23 '12 at 11:43
up vote 1 down vote

Oracle NUMBER data type is floating point data type, so you have problem of float / double approximation. Be sure to read What Every Computer Scientist Should Know About Floating-Point Arithmetic.

share|improve this answer
    
I'm aware it's a float data type. It just seems strange that in 99 cases out of 100 it returns the value we wanted stored, and in that 1 odd case it doesn't, but not all the time. I assumed it would be consistent if it was related to approximation. – Kieran May 22 '12 at 12:32
    
Do you set FPU control word ? (docwiki.embarcadero.com/Libraries/en/System.Set8087CW) – Antonio Bakula May 22 '12 at 12:40
    
It should be consistent. Control word should not have any effect on reading an exactly representable value like this. – David Heffernan May 22 '12 at 13:21
4  
While Oracle's NUMBER type is a floating point type, it is a base-100 floating point numbering system as documented here and thus does not suffer from many of the nasty problems found in base-2^n floating point numbers. As others have pointed out this error has something to do with the translation from NUMBER to the PC's base-2^n (i.e. IEEE) floating point format, and later manipulation of those values. Unfortunately Delphi chooses to convert NUMBER to Double or some other floating point type. Bad-bad-BAD! – Bob Jarvis May 22 '12 at 16:08
    
@Bob "does not suffer from many of the nasty problems found in base-2^n floating point" is a skewed view of the world. NUMBER is right for some problems, binary floating point is right for other problems. You wouldn't suggest that NUMBER would be appropriate for scientific or engineering calculations would you? And I wouldn't suggest IEEE-754 is appropriate for currency calculations. – David Heffernan May 22 '12 at 19:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.