[#ZF-1508] Implement CROSS JOIN functionality for DB2

Zend Framework

Implement CROSS JOIN functionality for DB2

Created: 06/Jun/07 09:01 PM Updated: Today 12:26 AM

Component/s:

Zend_Db_Select

Affects Version/s:

1.0.0 RC1

Fix Version/s:

Next Minor Release

Time Tracking:

Original Estimate:

1 week

Remaining Estimate:

1 week

Time Spent:

Not Specified

Issue Links:

Related

This issue is related to:

ZF-3072 Support for DB2 on i5

Fix Version Priority:

Nice to Have

Description

« Hide

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.

All

Comments

Work Log

Change History

FishEye

Crucible

Sort Order:

Ascending order - Click to sort in descending order

[ Permlink | « Hide ]

Bill Karwin - 07/Jun/07 11:25 AM

Assign to Bill Karwin.

[ Permlink | « Hide ]

Bill Karwin - 08/Jun/07 08:50 PM

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

[ Permlink | « Hide ]

Bill Karwin - 08/Jun/07 08:54 PM

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);
}

[ Permlink | « Hide ]

Wil Sinclair - 18/Apr/08 01:11 PM

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