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

I'm using a mysql database and I'm finding different results when I query depending on whether I use the search data 'inline' compared to when I use bound data in the execute() subroutine.

The table is called weathercache, with 4 columns, latitude (float), longitude (float), weather(text) and cached(datetime). Both floats are float(8,6).

I can insert the data fine:

use DBI;
$dbh = connect(.........);
$dbh->prepare('INSERT INTO weathercache (latitude, longitude, weather,
+ cached) VALUES (?, ?, ?, NOW())')->execute(50.530998, -4.949000, '')
+;

[download]

But the next line successfully finds this line again if I use:

$sth = $dbh->prepare('SELECT * FROM weathercache WHERE latitude = 50.5
+30998 AND longitude = -4.949000')->execute();

[download]

...but not if I use

$sth = $dbh->prepare('SELECT * FROM weathercache WHERE latitude = ? AN
+D longitude = ?')->execute(50.530998, -4.949000);

[download]

What am I doing wrong here? A float is a float right? Why does it matter how I use it?

Cheers

MattLG

Replies are listed 'Best First'.
Re: Difference between inline SQL and bound data
by moritz (Cardinal) on May 16, 2010 at 15:30 UTC
    What am I doing wrong here? A float is a float right? Why does it matter how I use it?

    The difference seems to be at one point Perl converts the decimal representation to float, and in the other occasion it's the database. If their algorithms differ, and result in just one bit being set differently, it would explain what you observe.

    The correct solution is not to compare floating point numbers for equality, but do a range comparison like WHERE ABS(latitude - ?) < 0.000001 AND ...

    If you really want to look for exact floating point values, consider storing them as strings in the database, not as actual numbers (but that might be a bad idea, depending on what else you want to do with them).

    Perl 6 - links to (nearly) everything that is Perl 6.
[reply]
[d/l]
      The difference seems to be at one point Perl converts the decimal representation to float, and in the other occasion it's the database. If their algorithms differ, and result in just one bit being set differently, it would explain what you observe.
      Not to me. There are three queries: the insert, the successful fetch, and the unsuccessful fetch. Both the insert and the unsuccessful fetch have ->execute(50.530998, -4.949000) (so Perl is doing the literal-to-float conversion), while in the successful fetch, the coordinates are hardcoded in the query, making MySQL do the literal-to-float conversion. If the problem lies in the difference between the algorithms used by Perl and MySQL, you would expect the now successful fetch to be unsuccessful, and the now unsuccessful to be successful.
[reply]
[d/l]
        You have a point. What I suspect it's happening is the following:
        • in the INSERT, Perl does the string-to-double conversion and provides MySql with it. In turn, MySql coerces this value to comply with the FLOAT(8,6) definition set by the OP
        • in the first SELECT, either MySql's definition of the provided numbers kicks in, or some kind of string comparison is performed and makes the test pass. In either case we're confronting the provided values with the coerced value;
        • in the second SELECT, Perl does the string-to-double conversion and provides MySql with it. In turn, this time MySql does NOT apply the FLOAT coercion as in the INSERT, but goes straight to the comparison (possibly upgrading the "limited" float to the same "size" of the provided value, like when you do this kind of comparisons in C)
        The bottom line remains moritz's: floats and exact comparisons have to be specifically dealt with.

        perl -ple'$_=reverse' <<<ti.xittelop@oivalf

        Io ho capito... ma tu che hai detto?
[reply]
[d/l]

      OIC. I assumed both methods left it up to mySQL.

      Cheers

      MattLG

[reply]
Re: Difference between inline SQL and bound data
by ww (Archbishop) on May 16, 2010 at 17:47 UTC
    Further re Moritz' well-made point: You may find this informative, if you're not already familiar with some variant of "What every (computer scientist | programmer | one) should know about floats."

    Basicly, no, a float is not necessarily a float in the sense that you can use it, willy-nilly, as a number. Alogrithms, truncation, repeating values can all trip you up.

[reply]

Back to Seekers of Perl Wisdom