[#ZF-5295] Failure on Oracle database - Zend Framework Issue Tracker

Details

Type: Bug
Status: Resolved
Priority: Critical
Resolution: Fixed
Affects Version/s: 1.7.1
Fix Version/s: 1.8.4
Component/s: Zend_Paginator
Labels:
None

Description

The DbSelect adapter fails under Oracle, because Oracle does not support statements like:

SELECT COUNT(DISTINCT column1, column2, column3) FROM table_name

Instead, in Oracle, the statement should be:

SELECT COUNT(DISTINCT column1||column2||column3) FROM table_name

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.

187,188d186
<           //ORACLE COUNT(DISTINCT col1||col2..) fix
<           $groupPart = str_replace(",", "||'###'||", $groupPart);

Sample code:

$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);

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Mickael Perraud added a comment - 17/Dec/08 2:07 AM

Please, could you provide a complete reproduce code?

Show

Mickael Perraud added a comment - 17/Dec/08 2:07 AM Please, could you provide a complete reproduce code?

Hide

Permalink

Mickael Perraud added a comment - 21/Dec/08 9:39 AM

I understood the problem but your fix could have problem:

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

Show

Mickael Perraud added a comment - 21/Dec/08 9:39 AM I understood the problem but your fix could have problem:

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

Hide

Permalink

Mickael Perraud added a comment - 23/Dec/08 1:06 AM

If we want something independent of the DBMS, we must use a request like:

SELECT COUNT(*) FROM (SELECT DISTINCT column1, column2, column3 FROM table_name)

Note: Problem also occurs with DB2

Show

Mickael Perraud added a comment - 23/Dec/08 1:06 AM If we want something independent of the DBMS, we must use a request like:

SELECT COUNT(*) FROM (SELECT DISTINCT column1, column2, column3 FROM table_name)

Note: Problem also occurs with DB2

Hide

Permalink

Jurrien Stutterheim added a comment - 23/Dec/08 1:14 AM

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?

SELECT COUNT(DISTINCT column1, column2, column3) FROM table_name

would become

SELECT COUNT(*) FROM table_name GROUP BY column1, column2, column3

Feel free to slap me if I'm saying stupid things atm... I need more coffee!

Show

Jurrien Stutterheim added a comment - 23/Dec/08 1:14 AM 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?

SELECT COUNT(DISTINCT column1, column2, column3) FROM table_name

would become

SELECT COUNT(*) FROM table_name GROUP BY column1, column2, column3

Feel free to slap me if I'm saying stupid things atm... I need more coffee!

Hide

Permalink

Mickael Perraud added a comment - 23/Dec/08 1:25 AM

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

Show

Mickael Perraud added a comment - 23/Dec/08 1:25 AM 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

Hide

Permalink

Jurrien Stutterheim added a comment - 23/Dec/08 11:08 AM

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).

Show

Jurrien Stutterheim added a comment - 23/Dec/08 11:08 AM 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).

Hide

Permalink

Jurrien Stutterheim added a comment - 30/May/09 8:44 PM

This thread: http://www.dbasupport.com/forums/showthread.php?t=11349 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 : )

Show

Jurrien Stutterheim added a comment - 30/May/09 8:44 PM This thread: http://www.dbasupport.com/forums/showthread.php?t=11349 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 : )

Hide

Permalink

Matthew Ratzloff added a comment - 08/Jun/09 2:23 PM

This isn't a blocking issue, so I'm lowering priority to critical. An alternative exists: passing a query to setRowCount().

Show

Matthew Ratzloff added a comment - 08/Jun/09 2:23 PM This isn't a blocking issue, so I'm lowering priority to critical. An alternative exists: passing a query to setRowCount().

Hide

Permalink

Jurrien Stutterheim added a comment - 18/Jun/09 7:41 PM

Resolved in r16149 and merged to release-1.8 in r16150

Show

Jurrien Stutterheim added a comment - 18/Jun/09 7:41 PM Resolved in r16149 and merged to release-1.8 in r16150

People

Assignee:

Jurrien Stutterheim

Reporter:

Bogdan Albei

Vote (1)

Watch (1)

Dates

Created:

17/Dec/08 2:02 AM

Updated:

18/Jun/09 7:41 PM

Resolved:

18/Jun/09 7:41 PM