Issues

ZF-1508: Implement CROSS JOIN functionality for DB2

Issue Type: New Feature Created: 2007-06-06T21:01:56.000+0000 Last Updated: 2012-08-31T09:11:51.000+0000 Status: Open Fix version(s): Reporter: Salvador Ledezma (ledezma) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db_Select

Related issues: - ZF-3072

Attachments:

Description

DB2 does not support the CROSS JOIN syntax:

SELECT "zfproducts".*, "zfbugs_products".* FROM "zfproducts" CROSS JOIN "zfbugs_products"

but this query should produce the equivalent result:

SELECT * FROM "zfproducts", "zfbug_products"

To implement this, there should probably be a Zend_Db_Select_Db2 class that inherits from Zend_Db_Select.

Comments

Posted by Bill Karwin (bkarwin) on 2007-06-07T11:25:53.000+0000

Assign to Bill Karwin.

Posted by Bill Karwin (bkarwin) on 2007-06-08T20:50:42.000+0000

We need to investigate whether this runs afoul of the same SQL semantics that have plagued MySQL 5.0.12. SQL-99 defined an order of precedence for comma-style and JOIN-style joins that causes some grief:

<pre class="highlight">
SELECT *
FROM TableA AS a, 
  TableB AS b JOIN TableC AS c ON a.foo = c.bar

In the above example, SQL-99 says that JOIN should be evaluated before the comma-join, despite the comma appearing first. So the correlation name for a.foo doesn't exist at this time, and the SQL parser gives up. I don't know whether IBM DB2 implements the SQL-99 semantics, or if it will do so in the future.

The correct solution is to avoid mixing comma-style (aka SQL-89) and JOIN-style (aka SQL-92), and only use one or the other consistently in a given query. That's why CROSS JOIN was defined in SQL-92, so there is a non-comma syntax to perform that type of join.

In any case, it's probably not a high priority to provide CROSS JOIN support, since few people even know about this syntax. So I'm marking this issue Postponed for now.

Also, a possible workaround is to use INNER JOIN with a condition that is always true, resulting in the same query result as a CROSS JOIN:

<pre class="highlight">
SELECT *
FROM TableA AS a INNER JOIN TableB AS b ON 1 = 1

Posted by Bill Karwin (bkarwin) on 2007-06-08T20:54:39.000+0000

Another possibility for extending Zend_Db_Select is that Zend_Db_Select_Db2 implements joinCross() as:

<pre class="highlight">
public function joinCross($name, $cols = '*', $schema = null)
{
    return $this->_join(self::INNER_JOIN, $name, '(1 = 1)', $cols, $schema);
}

Posted by Wil Sinclair (wil) on 2008-04-18T13:11:51.000+0000

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

Posted by Wil Sinclair (wil) on 2008-12-04T13:17:30.000+0000

Reassigning to Ralph since he's the new maintainer of Zend_Db

Posted by Ralph Schindler (ralph) on 2009-01-09T13:52:25.000+0000

will evaluate within 2 weeks

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