ZF-4772: Zend_Db_Select - union() doesn't work at all

Description

Zend_Db_Select::union() function does not produce expected result.


$selectA = $db->select()
 ->from(array('u' => 'user'), 'name')
 ->where('u.id >= 5');

$selectB = $db->select()
 ->from(array('u' => 'user'), 'name')
 ->where('u.id < 5')
 ->union(array($selectA));

echo $selectB->__toString();

outputs


SELECT `u`.`name`SELECT `u`.`name` FROM `user` AS `u` WHERE (u.id >= 5) FROM `user` AS `u` WHERE (u.id < 5)

which is not a valid query. Expected result:


(SELECT `u`.`name` FROM `user` AS `u` WHERE (u.id < 5)) UNION (SELECT `u`.`name` FROM `user` AS `u` WHERE (u.id >= 5))

Comments

I was able to get unions working by using this syntax:

$selectA = $db->select()
    ->from(array('u' => 'user'), 'name')
    ->where('u.id >= 5');

$selectB = $db->select()
    ->from(array('u' => 'user'), 'name')
    ->where('u.id < 5');

$select = $db->select()
    ->union(array($selectA, $selectB));

or alternatively

$select = $db->select()
    ->union(array(
        $db->select()
            ->from(array('u' => 'user'), 'name')
            ->where('u.id >= 5'),
        $db->select()
            ->from(array('u' => 'user'), 'name')
            ->where('u.id < 5')
        ));

It won't wrap each select clause in parenthesis though. See ZF-4338 for that issue.

This is mainly a documentation issue. The union() method doc comment described functionality it did not have.

The only working example for union is to pass an array of sql query strings or Zend_Db_Select instances into its first parameter and render that.

I have added documentation on the issue into the Zend_Db_Select manual.