MySQL Bugs: #34955: People -> Search -> Pagination error

Bug #34955	People -> Search -> Pagination error
Submitted:	29 Feb 2008 4:51	Modified:	3 Mar 2008 19:35
Reporter:	Diego Medina	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Websites: MySQLForge	Severity:	S2 (Serious)
Version:	forge1.mysql.com	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When on the list view for "people", if you click the >> link (Last page link) It takes you too far and you get an error message saying that "it couldn't find any listing"

How to repeat:
1- go here http://forge1.mysql.com/people/search.php/nada?page=24
2- click the >> link on the top right corner
3- And you will see the error

Suggested fix:
I imagine there is an error on calculating the last page (I got page 83 as the last one with an error) page 82 or 81 also return no records.

Thank you for a bug report.

FIXED in r372

These are fixes for Bug #34955 People -> Search -> Pagination error:

The problem was in queries which followed the below pattern:

SELECT COUNT(*)
FROM table
LEFT JOIN other_table
GROUP BY table.field

The HippoReader::get_count() function was incorrectly stripping the GROUP BY clause and
resulting in an incorrect count returned.  I added the following check to get_count()
to account for the problem:

                if ($this->groupby_sql == "")
            $sql= 'SELECT COUNT(*) ' . substr($this->select_sql, strpos($this->select_sql, 'FROM') -1) . $this->get_filters();
                else {
                        $group_fields= substr($this->groupby_sql, strpos($this->groupby_sql, 'GROUP BY') + strlen('GROUP BY'));
                        $sql= "SELECT COUNT(DISTINCT $group_fields) " . substr($this->select_sql, strpos($this->select_sql, 'FROM') -1) . $this->get_filters();
                }

This seems to fix the issues, however some edge cases might still 
cause incorrect results.

In cls/peoplehandler, I also added search on the ForgeUser.display_name field when searching

In cls/searchrequest.php -- Added checks to ensure over last page could not happen

I'm not sure if this is a new error due to the fix or if it was already present, but if you go to http://forge1.mysql.com/people/search.php?page=54 (right now there are a total of 1044 records) it says that that page shows records from 1021 to 1044, but it actually shows records from 1021 to 1040 (so that last 4 records are not shown)

FIXED in r377.  Now calculating everything in the SearchRequest class.  Tested calculations of last_page, last_record, first_record, and offset.