[#ZF-6330] Zend_Paginator_Adapter_DbSelect make incorrect SQL query when use join keyword - Zend Framework Issue Tracker

Zend Framework

Zend_Paginator_Adapter_DbSelect make incorrect SQL query when use join keyword

Created: 16/Apr/09 05:55 PM Updated: 18/Jun/09 08:13 PM Resolved: 18/Jun/09 08:13 PM

Component/s:

Zend_Paginator

Affects Version/s:

1.7.8

Fix Version/s:

1.8.4

Time Tracking:

Not Specified

Description

« Hide

Hello,

OS: centos x64
PHP: PHP 5.1.6 (cli) (built: Apr 7 2009 08:00:18) x64
DB: psql (PostgreSQL) 8.3.7 x64

Zend_Paginator_Adapter_DbSelect make incorrect SQL query when use join keyword.
It make few columns as argument that passed to DISTINCT function than DISTINCT function make multi column result that COUNT function didn't understand.

The error is:

"Fatal error: Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42883]: Undefined function: 7 ERROR: function count(bigint, character varying, character varying) does not exist at character 8 HINT: No function matches the given name and argument types. You might need to add explicit type casts." when use Zend_Paginator with joinLeft functions.

Postgresql log:
ERROR: function count(bigint, character varying, character varying) does not exist at character 8
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
STATEMENT:

SELECT COUNT(DISTINCT "a"."id","a"."name","a"."description") AS "zend_paginator_row_count" FROM "a"
         LEFT JOIN "c" ON c.a_id = a.id
         LEFT JOIN "b" ON c.b_id = b.id

My model for table "a" is:

class A extends Zend_Db_Table_Abstract
{
    protected $_name = 'a';
    protected $_primary = 'id';
    public function fetchAllPaginator($pageNumber = 1, $itemCountPerPage = 20)
    {
        $select = $this->select()
        ->from("$this->_schema.$this->_name", array('id' => 'a.id', 'name' => 'a.name', 'description' => 'a.description'))
        ->joinLeft('c', "c.a_id = $this->_schema.$this->_name.id", array())
        ->joinLeft('b', "c.b_id = b.id", array('b_count' => 'COUNT(b.id)'))
        ->group(array('id' => 'a.id', 'name' => 'a.name', 'description' => 'a.description'))
        ->order("$order")
        ->setIntegrityCheck(false);

        $paginator = Zend_Paginator::factory($select);
        Zend_View_Helper_PaginationControl::setDefaultViewPartial('index/paginator.phtml');
        Zend_Paginator::setDefaultScrollingStyle('Sliding');
        $paginator->setItemCountPerPage($itemCountPerPage);
        $paginator->setPageRange(Zend_Registry::get('configuration')->pagerange);
        $paginator->setCurrentPageNumber($pageNumber);

        return $paginator;
    }
}

Description

Hello, OS: centos x64 PHP: PHP 5.1.6 (cli) (built: Apr 7 2009 08:00:18) x64 DB: psql (PostgreSQL) 8.3.7 x64 Zend_Paginator_Adapter_DbSelect make incorrect SQL query when use join keyword. It make few columns as argument that passed to DISTINCT function than DISTINCT function make multi column result that COUNT function didn't understand. The error is:

"Fatal error: Uncaught exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42883]: Undefined function: 7 ERROR: function count(bigint, character varying, character varying) does not exist at character 8 HINT: No function matches the given name and argument types. You might need to add explicit type casts." when use Zend_Paginator with joinLeft functions.

Postgresql log: ERROR: function count(bigint, character varying, character varying) does not exist at character 8 HINT: No function matches the given name and argument types. You might need to add explicit type casts. STATEMENT:

SELECT COUNT(DISTINCT "a"."id","a"."name","a"."description") AS "zend_paginator_row_count" FROM "a"
         LEFT JOIN "c" ON c.a_id = a.id
         LEFT JOIN "b" ON c.b_id = b.id

My model for table "a" is:

class A extends Zend_Db_Table_Abstract
{
    protected $_name = 'a';
    protected $_primary = 'id';
    public function fetchAllPaginator($pageNumber = 1, $itemCountPerPage = 20)
    {
        $select = $this->select()
        ->from("$this->_schema.$this->_name", array('id' => 'a.id', 'name' => 'a.name', 'description' => 'a.description'))
        ->joinLeft('c', "c.a_id = $this->_schema.$this->_name.id", array())
        ->joinLeft('b', "c.b_id = b.id", array('b_count' => 'COUNT(b.id)'))
        ->group(array('id' => 'a.id', 'name' => 'a.name', 'description' => 'a.description'))
        ->order("$order")
        ->setIntegrityCheck(false);

        $paginator = Zend_Paginator::factory($select);
        Zend_View_Helper_PaginationControl::setDefaultViewPartial('index/paginator.phtml');
        Zend_Paginator::setDefaultScrollingStyle('Sliding');
        $paginator->setItemCountPerPage($itemCountPerPage);
        $paginator->setPageRange(Zend_Registry::get('configuration')->pagerange);
        $paginator->setCurrentPageNumber($pageNumber);

        return $paginator;
    }
}

Show »

Sort Order:

[ Permalink | « Hide ]

Jurrien Stutterheim added a comment - 16/Apr/09 06:32 PM

Could you try this with the ZF 1.8.0 alpha release and see if this issue still exists?

[ Permalink | « Hide ]

Nikolay Goldschmidt added a comment - 17/Apr/09 05:35 AM

The problem exists in ZendFramework-1.8.0a1 too.

[ Permalink | « Hide ]

Nikolay Goldschmidt added a comment - 17/Apr/09 06:33 AM

I write patch that solve my problem:

--- Zend/Paginator/Adapter/DbSelect.php-       2009-02-22 02:25:39.000000000 +0300
+++ Zend/Paginator/Adapter/DbSelect.php        2009-04-17 17:28:19.000000000 +0400
@@ -191,6 +191,7 @@
                 $groupPart = implode(',', $groupParts);
             }

+            if (count($groupParts) > 0) $groupPart = $groupParts[0];
             $countPart  = empty($groupPart) ? 'COUNT(*)' : 'COUNT(DISTINCT ' . $groupPart . ')';
             $expression = new Zend_Db_Expr($countPart . ' AS ' . $db->quoteIdentifier(self::ROW_COUNT_COLUMN));

[ Permalink | « Hide ]

Matthew Ratzloff added a comment - 08/Jun/09 02:20 PM

I don't think randomly removing group clauses is the desired functionality.

[ Permalink | « Hide ]

Matthew Ratzloff added a comment - 08/Jun/09 02:22 PM

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

[ Permalink | « Hide ]

Jurrien Stutterheim added a comment - 18/Jun/09 08:13 PM

Resolved in r16151, merged to release-1.8 in r16152