in reply to Difference between inline SQL and bound data

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.

Replies are listed 'Best First'.
Re^2: Difference between inline SQL and bound data
by JavaFan (Canon) on May 16, 2010 at 20:34 UTC
    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]
Re^2: Difference between inline SQL and bound data
by MattLG (Beadle) on May 16, 2010 at 17:55 UTC

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

    Cheers

    MattLG

[reply]

In Section Seekers of Perl Wisdom