Why does perl DBI returns 0 rows when statement from trace returns 1

Question

I have been working with perl DBI for the first time this week.

Majority of queries / inserts are working okay, however I am having issues with one specific query that is returning 0 rows. When I enable tracing for perl DBI, and copy the exact same statement from the trace to the server (via HeidiSQL), 1 row is returned.

Is there some ambiguity in the original SQL query? The intention was to retrieve the row with the most recent timestamp. There are no duplicates in the timestamp column.

Initial setup for the DB connection:

$dsn = 'dbi:mysql:<servername>:<port>';
$dbh = DBI->connect($dsn, "<username>","<password>") or die "unable to connect    $DBI::errstr\n";

Preparing and executing statement: The code reaches print 'no rows found'

my $sth = $dbh->prepare("SELECT name, location, timestamp, notified FROM storage
  WHERE name = ? AND location = ? 
  AND timestamp = (SELECT MAX(timestamp) FROM storage)");

$sth->execute($strg_data->{name}, $strg_data->{location});

my @latest = $sth->fetchrow_array();

if (@latest) {
   <snipped>
}
else {
  print "no rows found!\n";
}

Extract from the perl DBI trace (level set to 2):

 -> prepare for DBD::mysql::db (DBI::db=HASH(0xebe4c0)~0xec0010 'SELECT name, location, timestamp, notified FROM storage
WHERE name = ? AND location= ? AND timestamp = (SELECT MAX(timestamp) FROM storage)')
Setting mysql_use_result to 0
<- prepare= DBI::st=HASH(0xecd7d0) at monitor.pl line 147
-> execute for DBD::mysql::st (DBI::st=HASH(0xecd7d0)~0xec9e50 'xxxx' '/tmp/')
-> dbd_st_execute for 00ecd7a0
  -> mysql_st_interal_execute
  Binding parameters: SELECT name, location, timestamp, notified FROM storage
WHERE name = 'xxxx' AND location= '/tmp/' AND timestamp = (SELECT MAX(timestamp) FROM storage)
  <- mysql_st_internal_execute returning rows 0
<- dbd_st_execute returning imp_sth->row_num 0
<- execute= '0E0' at monitor.pl line 152

Answer 1

SELECT MAX(timestamp) FROM storage finds the maximum timestamp without regard to name and location. If the name and location you specify don't have a record with that timestamp, you'll get 0 rows.

You probably want this query instead:

SELECT name, location, timestamp, notified FROM storage
  WHERE name = ? AND location = ? 
  ORDER BY timestamp desc LIMIT 1