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
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
a WHERE (active = 1)' at line 1' in Zend/Db/Statement/Mysqli.php:84
aWHERE (active = 1)")
aWHERE (active = 1)")
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
<pre class="highlight"> $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
Have you found an issue?
See the Overview section for more details.