Issues

ZF-11392: UNION

Issue Type: Bug Created: 2011-05-20T15:00:22.000+0000 Last Updated: 2011-08-14T12:08:31.000+0000 Status: Resolved Fix version(s): Reporter: Rodrigo Novelo Primolan (primolan) Assignee: Pádraic Brady (padraic) Tags: - Zend_Db_Select

  • zf-crteam-padraic
  • zf-crteam-priority

Related issues: - ZF-4338

Attachments: - ZF-11392.patch

Description

Line 295 should be change from:

$this->_parts[self::UNION][] = array($target, $type);

To:

$this->_parts[self::UNION][] = array("({$target})", $type);

Comments

Posted by Christian Ludwig (ludwig) on 2011-05-27T15:20:55.000+0000

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/…])

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

this will output something like:

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

<pre class="literal">
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;

Posted by Kim Blomqvist (kblomqvist) on 2011-05-27T17:27:10.000+0000

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

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

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

Posted by Christian Ludwig (ludwig) on 2011-05-27T22:03:16.000+0000

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.

<pre class="literal">
(SELECT FROM ...) UNION (SELECT FROM ... ORDER BY `id`);

Without parentheses it would end up in your query

<pre class="literal">
SELECT FROM ... UNION SELECT FROM ... ORDER BY `id`;

Or use a LIMIT for your tests:

<pre class="literal">
(SELECT FROM ...) UNION (SELECT FROM ... ORDER BY `id` LIMIT 1);

against

<pre class="literal">
(SELECT FROM ...) UNION (SELECT FROM ...) ORDER BY `id` LIMIT 1;

Posted by Kim Blomqvist (kblomqvist) on 2011-05-28T06:35:46.000+0000

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.

Posted by Kim Blomqvist (kblomqvist) on 2011-05-28T20:47:17.000+0000

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.

<pre class="highlight">
    /** @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);
    }

Posted by Kim Blomqvist (kblomqvist) on 2011-05-30T15:05:32.000+0000

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.

Posted by Pádraic Brady (padraic) on 2011-08-14T12:08:31.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.
  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.

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