There might be some explanation needed.

Without the parentheses you get the wrong results i. e. when you want to order the complete query.

The example in the documentation ([http://framework.zend.com/manual/en/…])

    $sql1 = $db->select();
    $sql2 = "SELECT ...";
     
    $select = $db->select()
        ->union(array($sql1, $sql2))
        ->order("id");

this will output something like:

So the ORDER BY will only sort the second query!

With the path it will work as expected and output:

Here once more the patch:

diff U3B /Zend/Db/Select.php
--- Select.php 23775 2011-03-01 17:25:24Z ralph
+++ Select.php Working Copy
@@ -292,7 +292,7 @@
         }
 
         foreach ($select as $target) {
-            $this->_parts[self::UNION][] = array($target, $type);
+            $this->_parts[self::UNION][] = array('(' . $target . ')', $type);
         }
 
         return $this;

I can see from mysql docs that parentheses are required, but still tried to write a unit test to see that the current code will fail. However couldn't make it happen. Could you help me with that? I modified {{_selectUniontString()}} method in {{tests/Zend/Db/Select/TestCommon.php}} and got the query like this

SELECT `bug_id` AS `id`, `assigned_to` AS `name` FROM `zfbugs` UNION SELECT `product_id` AS `id`, `product_name` AS `name` FROM `zfproducts` ORDER BY `name` ASC

and result like this

Array
(
    [0] => Array
        (
            [id] => 4
            [name] => goofy
        )

    [1] => Array
        (
            [id] => 2
            [name] => Linux
        )

    [2] => Array
        (
            [id] => 3
            [name] => mmouse
        )

    [3] => Array
        (
            [id] => 1
            [name] => mmouse
        )

    [4] => Array
        (
            [id] => 2
            [name] => mmouse
        )

    [5] => Array
        (
            [id] => 3
            [name] => OS X
        )

    [6] => Array
        (
            [id] => 1
            [name] => Windows
        )

Looks correct for me.

I don't know what your data source is looking like, but the parentheses should always be there. Think about a query where you want to order the results of the second select of the UNION only.

(SELECT FROM ...) UNION (SELECT FROM ... ORDER BY `id`);

Without parentheses it would end up in your query

SELECT FROM ... UNION SELECT FROM ... ORDER BY `id`;

Or use a LIMIT for your tests:

(SELECT FROM ...) UNION (SELECT FROM ... ORDER BY `id` LIMIT 1);

against

(SELECT FROM ...) UNION (SELECT FROM ...) ORDER BY `id` LIMIT 1;

Patch file attached as suggested by the author. Couldn't make a unit test though. Seems like this cannot be reproduced with the current test data.

Christian - thank you for the explanation.

Ok, looks like we still need the test. The test below cannot reproduce the issue atm. and should be fixed to do so. I'm pretty sure that I'm missing something and doing it wrong. You can find test data source from {{tests/Db/TestUtil/Common.php}}.

    /** @group ZF-11392 */
    protected function _selectUnionStringWhichFailsIfParenthesesAreNotUsed()
    {
        $bugs = $this->_db->quoteIdentifier('zfbugs');
        $bug_id = $this->_db->quoteIdentifier('bug_id');
        $bug_assigned = $this->_db->quoteIdentifier('assigned_to');
        $products = $this->_db->quoteIdentifier('zfproducts');
        $product_id = $this->_db->quoteIdentifier('product_id');
        $product_name = $this->_db->quoteIdentifier('product_name');
        $id = $this->_db->quoteIdentifier('id');
        $name = $this->_db->quoteIdentifier('name');
        $sql1 = "SELECT $bug_id AS $id, $bug_assigned AS $name FROM $bugs";
        $sql2 = "SELECT $product_id AS $id, $product_name AS $name FROM $products";

        $select = $this->_db->select()
            ->union(array($sql1, $sql2))
            ->order('name');
        return $select;
    }

    /** @group ZF-11392 */
    public function testSelectUnionStringHasToHaveParentheses()
    {
        $select = $this->_selectUnionStringWhichFailsIfParenthesesAreNotUsed();
        $stmt = $this->_db->query($select);
        $result = $stmt->fetchAll();
-->     $this->assertEquals('goofy', $result[0]['name']); // should fail without the patch, i'm expecting 'mmouse'
        //echo $select;
        //print_r($result);
    }

See the comment by Christoph Roensch from ZF-4338 ...

bq. 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.
3. This is also a duplicate issue (duplicated issue resolved on same basis as above).

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