Re^2: Difference between inline SQL and bound data

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.

Comment on Re^2: Difference between inline SQL and bound data Download Code

Replies are listed 'Best First'.
Re^3: Difference between inline SQL and bound data by polettix (Vicar) on May 17, 2010 at 15:46 UTC
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]

Replies are listed 'Best First'.

Re^3: Difference between inline SQL and bound data
by polettix (Vicar) on May 17, 2010 at 15:46 UTC

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)

moritz

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

Io ho capito... ma tu che hai detto?

[reply]
[d/l]