Exponential values manipulation in perl

Question

I have a select statement which return capacity as exponential value e.g.

Capacity=5.4835615662E+003

in Perl code

I am using a db2 database, and if I explicitly run a query in database it returns

5483.5615662

but when I use next select query when I use capacity value in condition it doesn't match

e.g. pseudo code is as below,

my $capacity = 'SELECT capacity FROM table';

# it returns $capacity = 5.4835615662E+003

my $result = "SELECT MEASUREMENT FROM TABLE WHERE CAPACITY = $capacity";

Here $capacity is 5.4835615662E+003, so it does not match any row in the table. It should be 5483.5615662.

How to convert exponential value to float without rounding off?

Don't interpolate `$capacity` into the SQL. Instead, use [placeholders](https://metacpan.org/pod/DBI#Placeholders-and-Bind-Values). — Sinan Ünür, May 03 '17 at 16:47
`SELECT MEASUREMENT FROM TABLE WHERE CAPACITY=(SELECT CAPACITY FROM TABLE where ..?)` if you want to leave all decimal numbers at `db2` level (no need to fetch capacity). — mpapec, May 03 '17 at 16:53
If you *really* have `5.4835615662+003` then something has gone wrong and you need to show exactly how you got that string from the database. It should be `5.4835615662E+003`. Is that a mistake, or is the value really like that? — Borodin, May 03 '17 at 17:08

Sinan Ünür · Answer 1 · 2017-05-03T19:00:55.087

3

You are interpolating the value of $capacity into a string. Instead, you should use placeholders as in:

 my $sth = $dbh->prepare(q{SELECT MEASUREMENT FROM TABLE WHERE CAPACITY=?});
 $sth->execute($capacity);

It is hard to say if there are any other problems because the code snippets you provide don't really do anything.

It is likely that the number stored in the database is not exactly 5483.5615662 and that is just the displayed string when you query it.

If possible, I would recommend taking @Сухой27's advice and letting the database do the work for you:

SELECT MEASUREMENT FROM TABLE
WHERE CAPACITY = (SELECT CAPACITY FROM TABLE where ..?)

Alternatively, decide ahead of time how many digits past the decimal point really matter and use ROUND or similar functionality:

 my $sth = $dbh->prepare(q{
     SELECT MEASUREMENT FROM TABLE
     WHERE ROUND(CAPACITY, 6)=ROUND(?, 6)
 });
 $sth->execute($capacity);

edited May 03 '17 at 19:00

answered May 03 '17 at 16:49

Sinan Ünür

113,391
15
187
326

1

Is there some guarantee that perl will always follow db2 idea of what is the exact decimal value? – mpapec May 03 '17 at 16:57
1

@Сухой27: Perl DBI will pass a binary floating point value to the SQL engine for comparison. However, ther is no guarantee that a value that has been converted to a text string and back again (as must have happened here since Perl will not show `5.4835615662+003` when asked to print `5483.5615662`) will be equal to the original value. Floating-point comparisons require an exact bit-for-bit equality, and a discrepancy that isn't visible in printed output can easily cause a test for equality to fail. – Borodin May 03 '17 at 17:20
1

Your diagnosis is wrong. `perl -E '$x = 5483.5615662; say qq{$x};'` prints `5483.5615662` as expected. Only `sprintf '%e'` will result in that format. There *must* be some code that forces scientific notation on that value, converting it to a string with only 11 significant digits, which is *bound* to be unequal (when converted back to a float) to the original database value. – Borodin May 03 '17 at 18:48
@Borodin Note `perl -E "say 1 if '5.4835615662E+003'==5483.5615662"` ... If the value is passed in a placeholder, if the column in the database is numeric, the driver will pass along a number. On the other hand, if the entire statement is a string, then we are relying on DB2's parser to convert `5.4835615662E+003` correctly to a number. I'd rather take that step out of the equation. Of course, I do not have access to a DB2 database nor do I have any experience with it, so I don't know what happens. – Sinan Ünür May 03 '17 at 19:04
1

@SinanÜnür: I agree. I was challenging *"You are interpolating the value of $capacity into a string"*, assuming you meant that the exponent was being added by stringification. I showed a counter example to that. But this whole thing is a complication of the old *never compare floating point values for equality*, and I wouldn't expect a Perl value—even passed by placeholder—to be equal to the database value where it originated. – Borodin May 03 '17 at 19:13
1

@SinanÜnür: The [comment from **Сухой27**](http://stackoverflow.com/questions/43765741/exponential-values-manipulation-in-perl/43766140?noredirect=1#comment74573037_43765741) is the only resolution possible, but even then I wouldn't expect values from different tables, which represent the same value only nominally, to be identical. This is why we have ID fields on tables. – Borodin May 03 '17 at 19:18

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

Please take a look at Why doesn't this sql query return any results comparing floating point numbers?

I'm concerned about the 5.4835615662+003 that you show in your question. That isn't a valid representation of a number, and it means just 5.4835615662 + 3. You need an E or an e before the exponent to use it as it is

There is also an issue with comparing floating-point values, whereby two numbers that are essentially equal may have a slightly different binary representation, and so will not compare as equal. If your value has been converted to a string (and that seems highly likely, as Perl will not use an exponent to display 5483.5615662 unless told to do so) and back again to floating point, then it is extremely unlikely to result in exactly the same value. Your comparisons will always fail

In Perl, and most other languages, a numeric values has no specific format. For example, if I run this

perl -E 'say 5.4835615662E+003'

I get the output

5483.5615662

showing that the two string representations are equivalent

It would help to see exactly how you got the value of $capacity from the database, because if it were a simple number then it wouldn't use the scientific representation. You would have to use sprintf to get what you have shown

SQL is the same and doesn't care about the format of the number as long as it's valid, so if you wrote

SELECT measurement FROM table WHERE capacity = 5.4835615662E+003

then you would get a result where capacity is exactly equal to that value. But since it has been trimmed to eleven significant digits, you are hugely unlikely to find the record that the value came from, unless it contains 5483.56156620000000000

Update

If I run

perl -MMath::Trig=pi -E 'for (0 .. 20) { $x = pi * 10**$_; say qq{$x}; }'

I get this result

3.14159265358979
31.4159265358979
314.159265358979
3141.59265358979
31415.9265358979
314159.265358979
3141592.65358979
31415926.5358979
314159265.358979
3141592653.58979
31415926535.8979
314159265358.979
3141592653589.79
31415926535897.9
314159265358979
3.14159265358979e+015
3.14159265358979e+016
3.14159265358979e+017
3.14159265358979e+018
3.14159265358979e+019
3.14159265358979e+020

So by default Perl won't resort to using scientific notation until the value reaches 10¹⁵. It clearly doesn't apply to 5483.5615662. Something has coerced the floating-point value in the question to a much less precise string in scientific notation. Comparing that for equality doesn't stand a chance of succeeding

my mistake it is 5.4835615662E+003 only.but when I use it in where condition it does not return any result as it expects 5483.5615662 — Sharda Deshmukh, May 03 '17 at 17:30
@ShardaDeshmukh: I think your diagnosis is wrong. Comparing floating-point numbers for equality is inadvisable, especially when one of the values has been converted to a string and back. Neither Perl nor any SQL I know would return `5.4835615662E+003` for `5483.5615662`, and somehow you have converted the value to a string with that format. You show no real code, and it is impossible to know how that has happened. Have you read the link in my answer? — Borodin, May 03 '17 at 18:34

Exponential values manipulation in perl

2 Answers2

Update