ZF-4032: Paginator DBselect adapter not propperly counting rows when using DISTINCT()

Description

When using the Zend_Paginator_Adapter_DBselect() on a select statement as such;

$select = $this->db->select() ->from("IPLog", "DISTINCT(IP)");

or

$select = $this->db->select() ->from("IPLog", "IP") ->DISTINCT(true);

the counting of rows goes wrong, since in my case there's 2 unique IP's in the table, but I get 43 since there's 43 rows in total. This causes my paginator controls to show more pages then there really are. The problem seems to be that the count() method of the DBselect adapter resets the columns, without checking for result limitting expressions.

//update;

Thanks updating to latest svn and using the group by did the trick, doesn't work with 1.6.0RC2 though, so any change made after that did the trick.

Comments

Could you please try this with the latest version from the SVN? There have been a lot of improvements to the DbSelect adapter since RC2.

I've managed to write a fix for this for the simple usecase where you would want to select only one column. Full support for multiple columns is somewhat more complicated. Please note that I've not committed the fix just yet.

An alternate solution is to use GROUP BY instead of DISTINCT, so I'm inclined to use that fact as a solution to this issue. What would be a usecase where DISTINCT is preferred over GROUP BY?

Resolved in revision 10981

For me this is not completly fixed (tested with 1.6.1). I had to change a distinct in Group By for a query using several tables where one off the fields of the select were present in several tables of the request. I obtained something like: SELECT COUNT(DISTINCT ce_fiche_id,ce_fiche_foo,ce_fiche_bar) AS zend_paginator_row_count FROM ce_fiche LEFT JOIN ce_suivi_fiche ON ce_suivi_fiche.ce_fiche_id = ce_fiche.ce_fiche_id WHERE .... ==> this count query cannot work because ce_fiche_id is ambigous. Initial query was of this form (simplified, more complex in reality and getting the group by working was not so simple): SELECT DISTINCT ce_fiche.ce_fiche_id AS Id, ce_fiche.ce_fiche_foo AS foo, ce_fiche.ce_fiche_bar AS Bar, FROM ce_fiche LEFT JOIN ce_suivi_fiche ON ce_suivi_fiche.ce_fiche_id = ce_fiche.ce_fiche_id ORDER BY 1 ASC LIMIT 10;

weel, why removing table names?

The bug is still here !!!

The query below is an example :

    $query = parent::select(true)->setIntegrityCheck(false)->distinct(true)
        ->from(array('fu1'=>$this->_name), null)
            ->join(array('b'=>Model_Table_Blabla::$name), 'b.facebook_uid = fu1.facebook_uid', null)
            ->join(array('v'=>Model_Table_Vote::$name), 'v.id_blabla = b.id_blabla', null)
            ->join(array('fu2'=>$this->_name), 'v.facebook_uid = fu2.facebook_uid')
                ->where('fu1.facebook_uid = ?', $array['facebook_user']->getFacebookUid());