I must be misunderstanding the issue .. I understood the OP's use of mysql_* as simply being for demonstrative purposes to show the difference in the returned results, and the actual question being why does db_query return a result of 0 for COUNT(*) when mysql_query returns a result of 4 for COUNT(*).
That is precisely correct.
I'd also like to know what the actual string value of $countsql is, and like Michael said, whether the query is being rewritten by another module.
Yes, it is rewritten I believe. Here is the actual code in question:
$r = pager_query($sql,10,0,$countsql); $rows = array(); $output = ''; while ($row = db_fetch_array($r)) { $rows[] = $row; } if (0 == count($rows)) { $output = '<p>No results found</p>'; }
I am getting the "No results found" whereas there should be 4 rows found. That last if has an else clause to display those, when found.
Here is the SQL:
SELECT COUNT(*) from (SELECT field_article_title_value FROM (SELECT content_type_book.field_article_title_value, term_node.tid, node_revisions.nid FROM (( node_revisions left join node on node.nid = node_revisions.nid ) left join content_type_book on content_type_book.nid = node_revisions.nid ) left join term_node on term_node.nid = node_revisions.nid WHERE (1=1) and ((UCASE(content_type_book.field_article_author_value) like UCASE('%Black%'))) and (1=1) and (1=1) and node.type='book' group by node_revisions.nid order by node_revisions.nid ) as XXXXX group by field_article_title_value, tid order by nid ) as temp_table;
Thanks.