Issues

ZF-4338: Add parentheses around UNION'ed selects

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

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;
}

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

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.

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"

Sorry, this is the correct patch file.

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 ?

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

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

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.