http://qs321.pair.com?node_id=97064

THuG has asked for the wisdom of the Perl Monks concerning the following question:

First off, I realize I am at a disadvantage. I'm using PerlScript to write ASPs that make SQL Queries to an Access DB. All I can say in my defense is I got sick of VBScript.

My current problem is with floating point numbers getting weird values when I pull them from the DB. For example, the ".98" in the DB field defined as a single becomes "0.980000019073486" when I pull it into a PerlScript scalar. I can redefine the DB field to be text and let the script do the conversion to float when I need it, but I'm hoping there is an answer that doesn't involve changing the DB.

Any ideas?

-Travis

Replies are listed 'Best First'.
Re: Filthy Floats
by MZSanford (Curate) on Jul 16, 2001 at 20:13 UTC
    It sounds like while Acess is displaying .98, it is holding 0.980000019073486. I don't use perlscript, but the normal perl way to truncate down to 2 decimal places would be :
    $number = 0.980000019073486; $number = sprintf("%.2f",$number);
    So, adding that to the code should turn your unsightly "0.980000019073486" into a "0.98" ... your whites whiter, you brights birghter, and leaves your clothes smelling fresher :-)
    OH, a sarcasm detector, that’s really useful
(tye)Re: Filthy Floats
by tye (Sage) on Jul 16, 2001 at 23:09 UTC

    When you say 0.98 to Perl, it creates a "double" that has one of the closest possible values to 0.98. That isn't quite exactly 0.98, but it is pretty darn close. Close enough that if you asked Perl to convert this value back into a string, it would probably just give you "0.98".

    But you stored that value into a "single" in the database. Well, that got you one of the closest values to 0.98 that can fit into a "single". When you pull that back out of the database, Perl stores it into a "double" and later you ask it to convert it to a string.

    Well, Perl can tell that the number is close to 0.98 but not nearly as close as is possible when using a "double", so Perl doesn't just stop at "0.98".

    You see, when you convert 0.98 to a "double", you get something that is closer to 0.98 than 0.980000000000001 is. But in a "single", the closest you can get is either 0.979999542236328 or 0.980000019073486.

    So you need to grab only about the first 7 significant digits when converting a single to a string. Something like: $num= 0 + sprintf "%.6e", $num;

            - tye (but my friends call me "Tye")

      I'm glad someone finally did the math and showed the closest singles to .98. But...

      When I set the DB to double, I get the exact same result in the ASP. And after your response, I'm know thinking that someone is converting it to a single before they give it to PerlScript.

      Or perhaps PerlScript doesn't use double. *shrug*

      Thanks anyway, we are leaving the DB at text for now.

      -Travis

Re: Filthy Floats
by bikeNomad (Priest) on Jul 16, 2001 at 20:13 UTC
    Use sprintf to set the number of significant digits you want:

    # rounds to 0.01 (assumes positive number): my $cleanFloat = sprintf "%.2f", $dirtyFloat + 0.004; # truncates to 0.01: my $cleanFloat = sprintf "%.2f", $dirtyFloat;

    update: it appears that sprintf internally rounds, which makes my "truncates" example not do so. However, the "rounds" example may be better, as it gets around sprintf's buggy rounding.

    update2: but since sprintf does buggy rounding for 0.5, it would be better to add 0.004; changed this.

      Watchout, (s)printf does the rounding itself, it doesn't truncate!

      printf "%.3f => %.2f\n", 1.023, 1.023; printf "%.3f => %.2f\n", 1.026, 1.026; #prints 1.023 => 1.02 1.026 => 1.03
      but sadly this rounding is buggy on numbers ending in .5:
      printf "%.5f => %.2f\n", 1.025, 1.025; # prints instead of the correct 1.03 1.025 => 1.02
      A quick and ugly 'fix' is something like printf "%.5f => %.2f\n", 1.025+1e-10, 1.025+1e-10; For better solutions and some discussion see this recent thread.

      -- Hofmator

        Hmm... not so sure that is buggy. I think a fairly common usage of round is for numbers that end in with 5 in the last place is to round to the even number. Wierd but true.

        No I don't have a reference, so that is just my recollection.

        Scott

        Actually, round to even would be correct for numbers ending in .5
        But in this case, 1.025 really is closer to 1.02
        printf "%.20f => %.2f\n", 1.025, 1.025;
      Hello, The function sprintf does the rounding automatically. You don't need to add the 0.005.

      From the sprintf manual:

      f,F  The  double  argument  is  rounded and 
      converted to decimal notation in the style -ddd.ddd, 
      where the number  of digits after the decimal-point 
      character is equal to the precision  specification.  
      
      Doing a small test reveals the same behavior in perl:
      #!/usr/bin/perl -w
      print `cat $0`;
      for $i (1..10){
      	my $num = 0;
      	for(1..9800*$i){
      		$num += 0.0001/$i;
      	}
      	print "$num = ";
      	$num = sprintf("%.4f", $num);
      	print "$num\n";
      }
      0.979999999999908 = 0.9800
      0.9799999999999 = 0.9800
      0.979999999999902 = 0.9800
      0.980000000000962 = 0.9800
      0.980000000000699 = 0.9800
      0.979999999999897 = 0.9800
      0.980000000000605 = 0.9800
      0.979999999999383 = 0.9800
      0.979999999998648 = 0.9800
      0.979999999998175 = 0.9800
      
      Aziz,,,
Re: Filthy Floats
by TheoPetersen (Priest) on Jul 16, 2001 at 20:15 UTC
    You could create the query using a sprintf call that prints the number of digits you expect, i.e.
    $fv = sprintf("%1.2f", $dv);
    where $fv is the value to put in the query and $dv is the value from the database.

    What that really does is use decimal representation and string conversion to set the accuracy of the floating point value. It's equivalent to querying the database for matches to a given level of accuracy:

    SELECT * FROM table WHERE abs(float - 0.98) < 0.001;
    For some reason, the comparison version bothers a lot of programmers, where chopping off digits in a string representation doesn't.
Re: Filthy Floats
by THuG (Beadle) on Jul 16, 2001 at 20:31 UTC

    Well, what I would likely do is find the first two or three consecutive 0s and drop everything after that. But I'm hoping there is a solution that doesn't involve manipulating the data to cover up the error, but something to compensate for this floating point misrepresentation. And I don't know who's fault it is, Access, W2K, PerlScript, or ActivePerl.

    This is the kind of problem that someone needs to fix. Guess I can write to ActiveState and let them figure out if it is their problem or MS's.

      It is the fault of mathematics, and not any particular one the groups you named.

      Floating point numbers simply cannot be stored precisely on a computer. And the most efficient representations to use do not round off to nice-looking numbers in base 10. So what is probably happening is that Access is storing the floating point number as a float. Perl stores it as a double. And since Perl stores it with much more accuracy than it was stored in the database with, when you display it in Perl you can see the round-off error.

      UPDATE
      merlyn is of course right. There are a finite number of exceptions. How many and what they are depends on the particular hardware you have, and the particular physical representation of a floating point number that you choose to use.

        Floating point numbers simply cannot be stored precisely on a computer.
        I know what you mean, but it's not what you said. {grin}

        Insert Some at the beginning of that sentence to make it true. I can certainly represent "0.5" precisely in IEEE floating point. And actually, it might make more sense to say:

        Most fixed-decimal values cannot be represented precisely as binary floating-point numbers, no matter what the precision, because 1/10th is an infinite repeating fraction in binary. Unless the number is an integer divided by a power of two, you'll get some sort of truncation error.

        -- Randal L. Schwartz, Perl hacker

      The problem isn't in ActiveState's product or Microsoft's (but feel free to blame Microsoft anyway; I use them as a general blame sink). The problem is that floating point values do not represent exact decimal numbers. If you want exact decimal numbers, use integers to represent them and then shift the decimal point to the correct position.

      You'll find most database products offer to do this for you. Unfortunately when copying the stored value from the database to Perl, it gets stored as a floating point value again. To preserve accuracy you need to keep the value as an integer until it presentation.

        Okay, I went to University for a BS in Computer Science, so I understand the idea behind floating point representation (and single and double precision, and underflow errors).

        But I am finding it hard to believe that Perl can't represent 0.98. So I did a little test. The DB field is set to string and it is literally ".98". I can pull it from the DB and when I go to display it, I make sure it is displayed as a numerical value and not a string (<%=$target * 1%>).

        If I leave the * 1 out, it shows the string as it is in the DB ".98". If I put the * 1 in, it shows the value "0.98".

        Is Perl doing some magic and not treating ".98" as a float when it needs to computer it with an integer? I figured I would try * 0.5 instead of * 1. Guess what, I get "0.49".

        Is Perl doing that much magic, or can it represent 0.98 as a single or double float?