History | Log In     View a printable version of the current page.  
Issue Details (XML | Word | Printable)

Key: ZF-1508
Type: New Feature New Feature
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Salvador Ledezma
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Google issue summary
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
Original Estimate - 1 week
Remaining Estimate: 1 week
Remaining Estimate - 1 week
Time Spent: Not Specified
Remaining Estimate - 1 week

Issue Links:
Related
 

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
Bill Karwin - 07/Jun/07 11:25 AM
Assign to Bill Karwin.

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

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

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.