ZF-9997: Zend_Db_Select::Union doesn't works with groupBy and Having clauses

Issue Type:
Bug
Created:
2010-06-16T07:08:34.000+0000
Last Updated:
2011-08-11T14:12:26.000+0000
Status:
Open
Fix version(s):
    Reporter:
    david quintard (dquintard)
    Assignee:
    Ralph Schindler (ralph)
    Tags:
    • Zend_Db_Select
    Related issues:
    Attachments:

    Description

    Here is my sql request:

    $select = $this->select(); $select->union(array($select1, $select2)); $select->order('edge2edge_id'); $select->group('edge2edge_id'); $select->group('module'); $select->group('aspen_connection_id'); $select->having('SUM(nbPaths) >= ?', $nbPaths);

    echo $select->assemble(); => SELECT ... UNION ... ORDER BY edge2edge_id !

    All other clauses after orderBy (groupBy and having clauses) are skipped !

    Comments

    Posted by Ralph Schindler (ralph) on 2010-06-18T13:18:48.000+0000

    Can you please provide a sample reproduction case. This would include schema, platform information, and a script that demonstrates the problem.

    See http://ralphschindler.com/2010/02/…

    Thanks for the report. -ralph

    Posted by Marc Lester (marclester) on 2011-08-11T14:12:26.000+0000

    I've also encountered this. Here is my test case

    $select = new Zend_Db_Select($adapter);

    $select1 = "SELECT * FROM table1"; $select2 = "SELECT * FROM table2";

    $select->union(array($select1, $select2)); $select->group('last'); $select->having(new Zend_Db_Expr('count(*) > 1'));

    //expected output //SELECT * FROM table1 UNION SELECT * FROM table2 GROUP BY last HAVING count(*) > 1 ORDER BY last ASC, first ASC LIMIT 40

    //outputs //SELECT * FROM table1 UNION SELECT * FROM table2 ORDER BY last ASC, first ASC LIMIT 40

    The problem is the _renderHaving and _renderGroup are checking for a self::FROM part which wont exist with a union. My suggestion is to only check for a self::FROM part if it isn't a union.