ZF-11765: A query with group by and sum() does not show any result

Description

I have a more or less complex query, with 4 joins, a sum and group aggregate function .. Anyway, while using the Zend_Paginator_Adapter, the query doesn't result anything .. i would say that Zend_Paginator_Adapter_DbSelect::getCountSelect is the souce of all evil :)

Comments

Please give an example to reproduce the problem.

Following query result one row (which is correct) , but as soon i use the Zend_Paginator, it doesn´t

Query

 
SELECT `pb_complain`.`id`,
  `pb_complain`.`cid`,
  `pb_complain`.`user`,
  `pb_complain`.`complainDate`,
  `pb_complain`.`inboundNr`,
  `pb_complain`.`supplierNr`,
  `pb_complain`.`notification`,
  `pb_complain`.`serial`,
  `pb_complain`.`PIN` AS `complain_PIN`,
  `pb_complain`.`report`,
  `pb_complain`.`amount`,
  sum(pb_complainReturnRelation.STATE) AS `amount_ready`,
  `pb_complain`.`complainRebuke`,
  `pb_complain`.`complainReason`,
  `pb_complain`.`rma`,
  `pb_complain`.`source`,
  `pb_complain`.`desire`,
  `pb_complainRelation`.`complainID` AS `complainID_relation`,
  `pb_complainSupplier`.`note` AS `complainSupplierNote`,
  `pb_complainSupplier`.`supplier`,
  `pb_complainSupplier`.`supplierid`,
  `pb_complainSupplier`.`producer`,
  `pb_complainSupplier`.`date`,
  `pb_complainSupplier`.`note`,
  `pb_complainReturn`.*,
  `pb_complainRelation_2`.`complainID` AS `complainID_relation`,
  `pb_complainReturnRelation`.`id` AS `pb_complainReturnRelation_id`
FROM `pb_complain`
    LEFT JOIN `pb_complainRelation` ON pb_complainRelation.id=pb_complain.id
    LEFT JOIN `pb_complainSupplier` ON pb_complainSupplier.complainID=pb_complainRelation.complainID
    LEFT JOIN `pb_complainReturn` ON pb_complainRelation.complainID=pb_complainReturn.c_id
    LEFT JOIN `pb_complainRelation` AS `pb_complainRelation_2` ON pb_complainRelation.id=pb_complain.id
    LEFT JOIN `pb_complainReturnRelation` ON pb_complainReturnRelation.complainID = pb_complainRelation.complainID
AND pb_complainReturnRelation.id = pb_complain.id
WHERE 
    (cid LIKE '%MANZLCD-PE12864WRF-TC%')
GROUP BY `pb_complainRelation`.`id`

PHP Code

 
//no sourcecode formatter for php ?! kidding me ?!
$select = $this->select ( )
    ->setIntegrityCheck ( false )
    ->from ( $this,
            array('id'=>'id',
                        'cid'=>'cid',
                        'user'=>'user',
                        'complainDate'=>'complainDate',
                        'inboundNr'=>'inboundNr',
                        'supplierNr' => 'supplierNr',
                        'notification'=>'notification',
                        'serial'=>'serial',
                        'complain_PIN' =>'PIN',
                        'report'=>'report',
                        'amount'=>'amount',
                        'amount_ready' => 'sum(pb_complainReturnRelation.state)',
                        'complainRebuke'=>'complainRebuke',
                        'complainReason'=>'complainReason',
                        'rma'=>'rma',
                        'source'=>'source',
                        'desire' => 'desire'
                    )
        )


    ->joinLeft("pb_complainRelation",
    "pb_complainRelation.id=pb_complain.id",
        Array (
                "complainID_relation" => "complainID"
            ))
    ->joinLeft("pb_complainSupplier",
    "pb_complainSupplier.complainID=pb_complainRelation.complainID",
        Array (
                "complainSupplierNote" => "note",
                "supplier" => "supplier",
                "supplierid" => "supplierid",
                "producer" => "producer",
                "date" => "date",
                "note" => "note",

            )   )
    ->joinLeft("pb_complainReturn",
    "pb_complainRelation.complainID=pb_complainReturn.c_id" )
    ->joinLeft("pb_complainRelation",
    "pb_complainRelation.id=pb_complain.id",
        Array (
                "complainID_relation" => "complainID",
            )   )
    ->joinLeft("pb_complainReturnRelation",
        "pb_complainReturnRelation.complainID = pb_complainRelation.complainID AND
          pb_complainReturnRelation.id = pb_complain.id",
    array("pb_complainReturnRelation_id" => "id"));

    $sql="";

    // this beautifull peace of code creates a sql condintion
    // where you can enter and/or key=value and the operators (<>!=like )
    if(is_array($data))
    {
        foreach($data['key'] as $key => $value)
        {
                $special=($data['operator'][$key]=="like" ? "%" : "");
                @$sql.=" ".$value." ".$data['operator'][$key]. " '".$special.$data['value'][$key].$special."' ".$data['relation'][$key];
        }
        $select->where($sql);
    }

    $select->group("pb_complainRelation.id");

DB Create Table statement

 
CREATE TABLE IF NOT EXISTS `pb_complain` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cid` varchar(255) NOT NULL,
  `user` int(11) NOT NULL,
  `complainDate` int(11) NOT NULL,
  `inboundNr` varchar(255) NOT NULL,
  `supplierNr` int(11) unsigned NOT NULL,
  `notification` text NOT NULL,
  `serial` varchar(255) NOT NULL,
  `PIN` varchar(8) NOT NULL,
  `report` varchar(255) NOT NULL,
  `amount` int(3) unsigned NOT NULL,
  `complainRebuke` enum('y','n') NOT NULL,
  `complainReason` text NOT NULL,
  `rma` varchar(255) NOT NULL,
  `source` varchar(10) NOT NULL,
  `status` enum('new','used') NOT NULL,
  `desire` varchar(50) NOT NULL,
  `suspected_cost` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `pb_complain`
--

INSERT INTO `pb_complain` (`id`, `cid`, `user`, `complainDate`, `inboundNr`, `supplierNr`, `notification`, `serial`, `PIN`, `report`, `amount`, `complainRebuke`, `complainReason`, `rma`, `source`, `status`, `desire`, `suspected_cost`) VALUES
(1, 'MANZLCD-PE12864WRF-TC', 114, 1317074400, '23456', 344, 'andreas@example.com, ', '', '', '', 2, 'y', 'Display defekt', '', 'inbound', 'new', '', 45),
(2, 'MANZLCD240X160', 114, 1317074400, '4567', 34567, 'andreas@example.com, ', '', '', '3', 3, 'y', 'display defekt ', '', 'production', 'used', '', 2345);

-- --------------------------------------------------------

--
-- Table structure for table `pb_complainRelation`
--

CREATE TABLE IF NOT EXISTS `pb_complainRelation` (
  `id` int(10) unsigned NOT NULL,
  `complainID` int(10) unsigned NOT NULL,
  UNIQUE KEY `id` (`id`,`complainID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `pb_complainRelation`
--

INSERT INTO `pb_complainRelation` (`id`, `complainID`) VALUES
(2, 1);

-- --------------------------------------------------------

--
-- Table structure for table `pb_complainReturn`
--

CREATE TABLE IF NOT EXISTS `pb_complainReturn` (
  `returnID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c_id` int(11) unsigned NOT NULL,
  `inboundID` varchar(255) NOT NULL COMMENT 'return inboundID',
  `errorReportNr` varchar(255) NOT NULL,
  `note` text NOT NULL,
  `charge` int(11) NOT NULL,
  PRIMARY KEY (`returnID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `pb_complainReturn`
--

INSERT INTO `pb_complainReturn` (`returnID`, `c_id`, `inboundID`, `errorReportNr`, `note`, `charge`) VALUES
(1, 2, '2345', '45', 'wurde reperariert', 234);

-- --------------------------------------------------------

--
-- Table structure for table `pb_complainReturnRelation`
--

CREATE TABLE IF NOT EXISTS `pb_complainReturnRelation` (
  `rID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `complainID` int(11) unsigned NOT NULL,
  `returnID` int(10) unsigned NOT NULL,
  `id` int(10) unsigned NOT NULL,
  `PIN` varchar(11) NOT NULL,
  `errorReport` tinytext NOT NULL,
  `warranty` tinyint(1) unsigned NOT NULL,
  `booking` tinyint(1) unsigned NOT NULL,
  `state` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`rID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `pb_complainReturnRelation`
--

INSERT INTO `pb_complainReturnRelation` (`rID`, `complainID`, `returnID`, `id`, `PIN`, `errorReport`, `warranty`, `booking`, `state`) VALUES
(1, 1, 1, 2, '', 'test', 0, 1, 1),
(2, 1, 0, 2, '', '', 0, 0, 0),
(3, 1, 0, 2, '', '', 0, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `pb_complainSupplier`
--

CREATE TABLE IF NOT EXISTS `pb_complainSupplier` (
  `complainID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `inboundNr` varchar(20) NOT NULL,
  `supplier` varchar(255) NOT NULL,
  `supplierid` int(11) unsigned NOT NULL,
  `producer` varchar(255) NOT NULL,
  `date` int(10) unsigned NOT NULL,
  `note` text NOT NULL,
  `suspected_cost` varchar(50) NOT NULL,
  PRIMARY KEY (`complainID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `pb_complainSupplier`
--

INSERT INTO `pb_complainSupplier` (`complainID`, `inboundNr`, `supplier`, `supplierid`, `producer`, `date`, `note`, `suspected_cost`) VALUES
(1, '34', 'Display Solution AG', 3300241, 'Display & more', 1317074400, 'Test', '344');

some additional info, the automatic generated count query from Zend_Paginator_Adapter_DbSelect::getCountSelect returns 0

but in my case it should not count(distinct) the group by value from the original query....

 
SELECT COUNT(DISTINCT `pb_complainRelation`.`id`) AS `zend_paginator_row_count` FROM `pb_complain` LEFT JOIN `pb_complainRelation` ON pb_complainRelation.id=pb_complain.id LEFT JOIN `pb_complainSupplier` ON pb_complainSupplier.complainID=pb_complainRelation.complainID LEFT JOIN `pb_complainReturn` ON pb_complainRelation.complainID=pb_complainReturn.c_id LEFT JOIN `pb_complainRelation` AS `pb_complainRelation_2` ON pb_complainRelation.id=pb_complain.id LEFT JOIN `pb_complainReturnRelation` ON pb_complainReturnRelation.complainID = pb_complainRelation.complainID AND pb_complainReturnRelation.id = pb_complain.id WHERE ( cid like '%MANZLCD-PE12864WRF-TC%' )