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

Fellow Monks,

I have been playing with the handy Geo::Distance module, however I noticed some abnormalities using the 'closest' function for certain lat/lon data when doing DB lookups...

I couldn't work out why this was wrong and looked a bit further, simply put this 'closest' function creates some simple sql to fine destinations within a certain range of lat/lon

However I noticed this info was wrong, however when I did the same sql directly on the DB it did what I expected. Further testing showed that it was only incorrect when placeholders were used, and I think its related to using negative numbers in the logic. Take a look at the following code snippet


y $lon = -0.232323127347249;
my $sql = qq| SELECT lon,lat,location_id
                FROM lat_long
                WHERE lon>= $lon|;

my $sth = $sp->dbh->prepare($sql);
$sth->execute();
print STDERR "Rows not using placeholders ".$sth->rows."\n";
$sql = qq| SELECT lon,lat,location_id
                FROM lat_long
                WHERE lon >= ?|;
$sth = $sp->dbh->prepare($sql);
$sth->execute($lon);
print STDERR "Rows using placeholders ".$sth->rows."\n";

[download]

I would have expected that the 2 SQL would return exactly the same number of results but it doesn't !! see below

Rows not using placeholders 5488
Rows using placeholders 12888

[download]

Maybe I am missing something obvious but I do not see how this could happen ? any ideas how to debug further ?

Update : Adding some sample data

__DATA__
insert into lat_long (location_id, lat, lon) values(44977, 48.3547, 11
+.7875);
insert into lat_long (location_id, lat, lon) values(53280, 48.1369, 11
+.5456);
insert into lat_long (location_id, lat, lon) values(53281, 48.1394, 11
+.5454);
insert into lat_long (location_id, lat, lon) values(53282, 47.6869, 11
+.7609);
insert into lat_long (location_id, lat, lon) values(53283, 48.3547, 11
+.7875);
insert into lat_long (location_id, lat, lon) values(60105, 48.3547, 11
+.7875);
insert into lat_long (location_id, lat, lon) values(80766, 48.3507, 11
+.7328);
insert into lat_long (location_id, lat, lon) values(71776, 48.1435, 11
+.5574);
insert into lat_long (location_id, lat, lon) values(65883, 48.1269, 11
+.6363);
insert into lat_long (location_id, lat, lon) values(65884, 48.1515, 11
+.6178);

[download]

Replies are listed 'Best First'.
Re: Placeholders and Negative Numbers..
by jZed (Prior) on Dec 11, 2007 at 17:17 UTC
    From the DBI documentation for $sth->rows():
    Generally, you can only rely on a row count after a non-SELECT execute (for some specific operations like UPDATE and DELETE), or after fetching all the rows of a SELECT statement. For SELECT statements, it is generally not possible to know how many rows will be returned except by fetching them all.
    So $sth->rows() is, by definition, the wrong thing to use with SELECT statements and is not guaranteed to give you the answer you expect. Do as the docs say and count the fetches or else do a separate COUNT query.
[reply]
Re: Placeholders and Negative Numbers..
by andreas1234567 (Vicar) on Dec 11, 2007 at 11:53 UTC
    It seems to work for me on MySQL 4.1 using $dbh instead of $sp->dbh: 
    use strict;
use warnings;

use DBI;
my $dbh = DBI->connect( 'DBI:mysql:test', 'foo', 'bar' ) or die;
my $lon = -0.232323127347249;
my $sql = qq| SELECT lon,lat,location_id FROM lat_long WHERE lon>= $lo
+n|;
my $sth = $dbh->prepare($sql);
$sth->execute();
print STDERR "Rows not using placeholders ".$sth->rows."\n";

$sql = qq| SELECT lon,lat,location_id FROM lat_long WHERE lon >= ?|;
$sth = $dbh->prepare($sql);
$sth->execute($lon);
print STDERR "Rows using placeholders ".$sth->rows."\n";
__END__

    [download]
    create table lat_long(lon double not null, lat double not null, locati
+on_id integer);
insert into lat_long (location_id, lat, lon) values(44977, 48.3547, 11
+.7875);
insert into lat_long (location_id, lat, lon) values(53280, 48.1369, 11
+.5456);
insert into lat_long (location_id, lat, lon) values(53281, 48.1394, 11
+.5454);
insert into lat_long (location_id, lat, lon) values(53282, 47.6869, 11
+.7609);
insert into lat_long (location_id, lat, lon) values(53283, 48.3547, 11
+.7875);
insert into lat_long (location_id, lat, lon) values(60105, 48.3547, 11
+.7875);
insert into lat_long (location_id, lat, lon) values(80766, 48.3507, 11
+.7328);
insert into lat_long (location_id, lat, lon) values(71776, 48.1435, 11
+.5574);
insert into lat_long (location_id, lat, lon) values(65883, 48.1269, 11
+.6363);
insert into lat_long (location_id, lat, lon) values(65884, 48.1515, 11
+.6178);

    [download]
     
    $ perl 656378.pl
Rows not using placeholders 10
Rows using placeholders 10

    [download]
    Update Tue Dec 11 14:48:09 CET 2007: Re-ran using data supplied by ropey, still the same results.
    --
    Andreas
[reply]
[d/l]
[select]
      Afraid not - think you do not have the same sample data - see my updated node
[reply]
      Your negative values are being typecast to strings. Putting CAST(XXX as signed integer) around your placeholder and column would solve it.
[reply]
Re: Placeholders and Negative Numbers..
by moritz (Cardinal) on Dec 11, 2007 at 12:08 UTC
    It could be an issue with floating point precision.

    You could check that by using floating point numbers with less trailing decimals, and compare the results.

[reply]

      Unfortunately that didnt seem to help either - dropped it down to -0.232 but same problem persists

[reply]

Back to Seekers of Perl Wisdom