Re^3: DBI SQL Returns Less than Direct SQL

Pure speculation here - but the only difference between the SQL's that I noticed is the "LIMIT" clause.

One (wierd) possibility is that one of the "FROM" sources is a view with a LIMIT clause with a value of 1.

This may determine the rows returned, so if you add a "LIMIT 0" clause to your perl code, that may get overriden.

Disclaimer: I have no SQL qualifacitions, and I have not RTFM.

"XML is like violence: if it doesn't solve your problem, use more."

Comment on Re^3: DBI SQL Returns Less than Direct SQL

Replies are listed 'Best First'.
Re^4: DBI SQL Returns Less than Direct SQL by Anonymous Monk on Oct 18, 2011 at 09:59 UTC
phpMyAdmin will reformat the SQL statement and add "LIMIT 0, 30" meaning first page, 30 rows.	[reply]
Re^5: DBI SQL Returns Less than Direct SQL by Corion (Patriarch) on Oct 18, 2011 at 10:14 UTC
I would now run the SQL statement that phpMyAdmin produces with the Perl code. I would also run the `select count(*) from ... where ...` statement, to see what the database thinks how many rows there would be.	[reply] [d/l]
Re^6: DBI SQL Returns Less than Direct SQL by DaveNagy (Initiate) on Oct 18, 2011 at 13:21 UTC
I crafted the SQL in PHPAdmin to get what I wanted from the table, tuning it until I was happy with the results. Then I ported it to PERL but simply copying it to the source and plugging in the variable to let me specify the search words when the program ran. By this technique, I could see what results I expected when the PERL program ran. I will try the COUNT(*) and see what MySQL says. Dave	[reply]
Re^6: DBI SQL Returns Less than Direct SQL by DaveNagy (Initiate) on Oct 18, 2011 at 13:31 UTC
I ran the COUNT(*) SQL in PHPAdmin and it returned a value of 6 indicating as expected that there were 6 rows that matched the criteria specified in the SQL. When run from PERL, it is returning 1. Dave	[reply]
Re^7: DBI SQL Returns Less than Direct SQL by Corion (Patriarch) on Oct 18, 2011 at 13:55 UTC
Re^4: DBI SQL Returns Less than Direct SQL by DaveNagy (Initiate) on Oct 18, 2011 at 12:21 UTC
The LIMIT is a difference but if I use a different search term (like 'rust'), the PERL program returns more than one row. LIMIT is inserted by PHPAdmin when I paste the SQL (without the LIMIT) into it's scratch pad. Dave	[reply]