Problems in the way Zend\Paginator\Adapter\DbSelect count()s

[twmobius]

Currenty on Zend\Paginator\Adapter\DbSelect the count() method resets some parts of the Zend\Db\Sql\Select object and leaves others:

$select->reset(Select::COLUMNS);
$select->reset(Select::LIMIT);
$select->reset(Select::OFFSET);

On ZF1 the same was:

$rowCount->reset(Zend_Db_Select::COLUMNS)
               ->reset(Zend_Db_Select::ORDER)
               ->reset(Zend_Db_Select::LIMIT_OFFSET)
               ->reset(Zend_Db_Select::GROUP)
               ->reset(Zend_Db_Select::DISTINCT)
               ->reset(Zend_Db_Select::HAVING)

I can understand that not resetting the ORDER doesn't make any difference, but the GROUP makes a huge difference if there is a GROUP BY expression in the Select statement.

Imagine the following query:

SELECT id, MIN(date_added) FROM Table GROUP BY id

count() would have it

SELECT COUNT(1) as c FROM Table GROUP BY id;

thus returning the number of rows per id in the Table (at least in mySQL. Might complain in others)

Similar problem would occur by not resetting HAVING as well.

Finally on a side note $select->reset(Select::COLUMNS); does not reset the columns that are a result from join(). Is this expected?

[ralphschindler]

The resetting of ORDER was included in 2.1.0, can you please test?
https://github.com/zendframework/zf2/blob/master/library/Zend/Paginator/Adapter/DbSelect.php#L109

I need to get a working example of how the joins() affect count(). Is there any way you can reproduce that problem with my Zend_Db-Examples repository? https://github.com/ralphschindler/Zend_Db-Examples

[twmobius]

@ralphschindler I think you misunderstood me. English is not my native language so we might be lost in translation here :)

The problem I am stating here is that by not resetting GROUP/HAVING can cause wrong count() results. See my SELECT example above.

As for the second part, I am not saying that join()ed columns can affect count(). While I was investigating the issue, I realized that Zend\Db\Sql\Select->reset(Select::COLUMNS); does not reset the columns that are a result of a join. It only resets the columns for the initial SELECT. And I just mentioned it so as to see if this is an expected behavior.

[ThaDafinser]

@ralphschindler @twmobius I think your problem is (partly) solved here.
I've taken some parts from ZF1 over to ZF2...

#3863

[fatmuemoo]

+1, I have this issue too

[fatmuemoo]

Suggested fix:
#4590

[ralphschindler]

Could you have a look at the solution #4641 and let me know if it works for you?