Issue Type: Bug Created: 2008-12-17T02:02:52.000+0000 Last Updated: 2009-06-18T19:41:13.000+0000 Status: Resolved Fix version(s): - 1.8.4 (23/Jun/09)
Reporter: Bogdan Albei (bogdan.albei) Assignee: Jurrien Stutterheim (norm2782) Tags: - Zend_Paginator
Related issues: Attachments:
The DbSelect adapter fails under Oracle, because Oracle does not support statements like:
<pre class="highlight"> Instead, in Oracle, the statement should be:
I made a quick fix by changing Zend/Paginator/Adapter/DbSelect.php, but then this would only support Oracle(which is no problem in my case) The fix is not perfect, there is a chance of failure, but I suppose the '###' string could be changed to something unique.
<pre class="highlight"> 187,188d186 < //ORACLE COUNT(DISTINCT col1||col2..) fix < $groupPart = str_replace(",", "||'###'||", $groupPart);
<pre class="highlight"> $select = new Zend_Db_Select($this->db); $select->from("product_transfers", array( "quantity" => "SUM(quantity)", "product_id", "name", "batch_number")); $select->group(array( "product_id", "name", "batch_number")); $paginator = Zend_Paginator::factory($select);
Posted by Mickael Perraud (mikaelkael) on 2008-12-17T02:07:22.000+0000
Please, could you provide a complete reproduce code?
Posted by Mickael Perraud (mikaelkael) on 2008-12-21T09:39:18.000+0000
I understood the problem but your fix could have problem:
<pre class="highlight"> create table foo( bar number(3) not null, baz varchar2(50) ); insert into foo( 1, '0abc'); insert into foo( 10, 'abc'); select count(distinct bar || baz) from foo; // this will return 1 because '||' will concat the 2 columns and won't apply a real distinct
Posted by Mickael Perraud (mikaelkael) on 2008-12-23T01:06:39.000+0000
If we want something independent of the DBMS, we must use a request like:
<pre class="highlight"> SELECT COUNT(*) FROM (SELECT DISTINCT column1, column2, column3 FROM table_name)
Note: Problem also occurs with DB2
Posted by Jurrien Stutterheim (norm2782) on 2008-12-23T01:14:25.000+0000
I'd prefer to avoid subqueries if possible because they're a lot slower than regular queries. Wouldn't a GROUP BY on the multiple columns provide the same result?
<pre class="highlight"> would become
Feel free to slap me if I'm saying stupid things atm... I need more coffee!
Posted by Mickael Perraud (mikaelkael) on 2008-12-23T01:25:02.000+0000
No, this wouldn't function ;) I f you take my example above, you will receive 2 lines of result. I understand though the problem of performance. Take a coffee :D
Posted by Jurrien Stutterheim (norm2782) on 2008-12-23T11:08:41.000+0000
Hehe, coffee taken ; ) We could probably detect these problematic queries and create subqueries if there are no other nice ways of supporting them. The solution would preferably work on every DB. If we go for subqueries, we should make an entry in the manual saying these kind of queries are better off with a custom COUNT query (also supported by the DbSelect adapter).
Posted by Jurrien Stutterheim (norm2782) on 2009-05-30T20:44:25.000+0000
This thread: http://dbasupport.com/forums/showthread.php/… might have a solution. The Paginator Adapter solution would check the no. of columns. If there's one column, we keep doing things the way they're done now. If there are multiple columns, we apply the suggestion from that forum thread. Currently haven't got time to fully investigate this, so feedback is welcome : )
Posted by Matthew Ratzloff (mratzloff) on 2009-06-08T14:23:35.000+0000
This isn't a blocking issue, so I'm lowering priority to critical. An alternative exists: passing a query to setRowCount().
Posted by Jurrien Stutterheim (norm2782) on 2009-06-18T19:41:01.000+0000
Resolved in r16149 and merged to release-1.8 in r16150
Have you found an issue?
See the Overview section for more details.