ZF-4338: Add parentheses around UNION'ed selects

Issue Type:
Bug
Created:
2008-09-20T08:20:46.000+0000
Last Updated:
2011-10-28T14:22:59.000+0000
Status:
Resolved
Fix version(s):
    Reporter:
    Jaka Jancar (jaka)
    Assignee:
    Pádraic Brady (padraic)
    Tags:
    • Zend_Db_Select
    • zf-crteam-padraic
    • zf-crteam-priority
    Related issues:
    Attachments:

    Description

    MySQL manual says:

    {quote} To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. The following example uses both clauses:

    
(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

    [...] To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

    
(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

    {quote}

    Zend_Db_Select::_renderUnion() doesn't add parentheses around selects so ORDER BY is ambiguous.

    Comments

    Posted by Martin Panel (xorax) on 2009-02-10T03:02:59.000+0000

    Is so difficult to implement it ??? That affect all version after 1.6.0 and extends Zend_Db_Select is not easy !

    
protected function _renderUnion($sql)
{
    if ($this->_parts[self::UNION]) {
        $parts = count($this->_parts[self::UNION]);
        foreach ($this->_parts[self::UNION] as $cnt => $union) {
            list($target, $type) = $union;
            if ($target instanceof Zend_Db_Select) {
                $target = $target->assemble();
            }
            $sql .= ' ( '.$target.' ) ';
            if ($cnt < $parts - 1) {
                $sql .= ' ' . $type . ' ';
            }
        }
    }
     return $sql;
}

    Posted by Petre Balau (coditza) on 2009-02-26T09:17:00.000+0000

    To get the parentheses around the individual selects, use something like this:

    
$select_1 = $db->select()->...;
$select_2 = $db->select()->...;

$main_select = $db->select()->union( array(  '('.$select_1.')',  '('.$select_2.')' ) );

    Posted by Jaka Jancar (jaka) on 2009-02-26T10:41:34.000+0000

    That's a workaround, of course.

    But that's not the point. This should be done inside Zend_Db_Select, otherwise it produces ambiguous SQL.

    Posted by Marco (p0l0) on 2009-07-23T22:34:07.000+0000

    I have made this patch on latest version, I don't think it breaks anything if each Union uses parentheses.

    Without parentheses we can not use LIMIT or ORDER BY, except if we use the union( array( '('.$select_1.')', '('.$select_2.')' ) ); workaround, which is not very "Zend"

    Posted by Marco (p0l0) on 2009-07-23T22:41:57.000+0000

    Sorry, this is the correct patch file.

    Posted by Pierre-Yves Beaudouin (pybeaudouin) on 2010-02-18T07:47:13.000+0000

    Marco, thank you for your patch ! Unfortunately it does not seem to work with Framework 10.1, I get another error:

    No table has been specified for the FROM clause

    I can not add parenthesis around my selects because it will convert them as strings and I need them as objects. Maybe your patch work on an older version of the framework ?

    Posted by Pierre-Yves Beaudouin (pybeaudouin) on 2010-02-19T00:22:43.000+0000

    Sorry for the spam - this bug is for ZF 1.7.5. Your patch works well with ZF 1.10.1.

    Posted by Christoph Roensch (croensch) on 2011-05-26T22:24:23.000+0000

    I believe this is non-standard SQL, because of that it cannot be included in Zend_Db_Select.

    Posted by Pádraic Brady (padraic) on 2011-08-14T12:07:11.000+0000

    Marked as Won't Fix on the basis of:

    1. This is not standard SQL and is MySQL specific
    2. A known workaround exists and the fix would breach backwards compatibility.

    Reporters and Assignee are recommended to fix in ZF2 if still a problem - or open a ZF2 issue in its respect.