ZF-1508: Implement CROSS JOIN functionality for DB2

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

Assign to Bill Karwin.

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:


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}}:


SELECT *
FROM TableA AS a INNER JOIN TableB AS b ON 1 = 1

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


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

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

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

will evaluate within 2 weeks