Issues

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: - ZF-11392

Attachments: - Select.php.patch

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:

<pre class="highlight">
(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:

<pre class="highlight">
(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 !

<pre class="highlight">
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:

<pre class="highlight">
$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.

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts