ZF-4177: sql syntax exception when paginator used with select-distinct query

Issue Type:

Bug

Created:

2008-09-03T09:33:20.000+0000

Last Updated:

2008-10-10T19:25:55.000+0000

Status:

Resolved

Fix version(s):

1.6.2 (13/Oct/08)

Reporter:

Karol Grecki (antych)

Assignee:

Jurrien Stutterheim (norm2782)

Tags:

Zend_Paginator

Related issues:

ZF-3809

Attachments:

Description

I have some code built with early version of Paginator when it was still in 1.6RC It stop working after recent upgrade, it can't handle a select query with a DISTINCT clause

Zend_Db_Statement_Mysqli_Exception' with message 'Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) AS zend_paginator_row_count FROM foo AS a WHERE (active = 1)' at line 1' in Zend/Db/Statement/Mysqli.php:84

Stack trace:

0 /home/sites/ZendLibrary/Zend/Db/Statement.php(109): Zend_Db_Statement_Mysqli->_prepare("SELECT COUNT(DISTINCT *) AS zend_paginator_row_count FROM `foo` AS `a` WHERE (active = 1)")

1 /home/sites/ZendLibrary/Zend/Db/Adapter/Mysqli.php(343): Zend_Db_Statement->__construct(Array(1), "SELECT COUNT(DISTINCT *) AS zend_paginator_row_count FROM `foo` AS `a` WHERE (active = 1)")

2 /home/sites/ZendLibrary/Zend/Db/Adapter/Abstract.php(429): Zend_Db_Adapter_Mysqli->prepare('SELECT COUNT(DI...')

3 /home/sites/ZendLibrary/Zend/Db/Select.php(639): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Select))

4 /home/sites/ZendLibrary/Zend/Paginator/Adapter/DbSelect.php(105): Zend_Db_Select->query(2)

5 /home/sites/ZendLibrary/Zend/Paginator/Adapter/DbSelect.php(175): Zend_Paginator_Adapter_DbSelect->setRowCount("* RECURSION (Zend_Db_Select) *")

6 /home/sites/ZendLibrary/Zend/Paginator.php(702): Zend_Paginator_Adapter_DbSelect->count()

7 /home/sites/ZendLibrary/Zend/Paginator.php(476): Zend_Paginator->_calculatePageCount()

Comments

Posted by Jurrien Stutterheim (norm2782) on 2008-09-03T09:53:24.000+0000

Could you post the code you used for the select query?

Posted by Karol Grecki (antych) on 2008-09-04T01:11:24.000+0000

Any query with distinct clause should do. I can replicate it with this code


        $select = $db->select()->distinct()->from('foo');
        $adapter = new Zend_Paginator_Adapter_DbSelect($select);
        $paginator = new Zend_Paginator($adapter);
        $paginator->setItemCountPerPage(20);

Last line trigger the same exception.

Posted by Jurrien Stutterheim (norm2782) on 2008-09-04T03:41:13.000+0000

Is that the actual query you were using at that time? I can see where it fails with that sample query.... Could you explain why you would want to execute a SELECT DISTINCT ? A possible solution for this would be to check wether or not the columns() are * in case of a distinct query. In that case, it should just COUNT() instead of COUNT(DISTINCT *).

Posted by Karol Grecki (antych) on 2008-09-04T04:14:59.000+0000

It was part of a more complex query where I was reading columns from only one table but other were used for filtering. This was creating duplicate entries so I used distinct. My code changed since then and I don't need distinct clause at the moment, but it was still in the code and I noticed it stopped working after update.

The query would like this: select a.* from foo a left join bar b on a.id = b.foo_id where bar_id IN(1,2,3,4,5)

Because each entity in foo can have more then one entry in bar some rows will appear more then once.

Posted by Jurrien Stutterheim (norm2782) on 2008-09-18T05:57:07.000+0000

Resolved in revision 11426