Zend Framework

Implement CROSS JOIN functionality for DB2

Details

  • Type: New Feature New Feature
  • Status: Open Open
  • Priority: N/A N/A
  • Resolution: Unresolved
  • Affects Version/s: 1.0.0 RC1
  • Fix Version/s: Next Minor Release
  • Component/s: Zend_Db_Select
  • Labels:
    None

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.

Issue Links

This issue is related to:
ZF-3072 Support for DB2 on i5 Major Closed

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Bill Karwin added a comment -

Assign to Bill Karwin.

Show
Bill Karwin added a comment - Assign to Bill Karwin.
Hide
Permalink
Bill Karwin added a comment -

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
Show
Bill Karwin added a comment - 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
Hide
Permalink
Bill Karwin added a comment -

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);
}
Show
Bill Karwin added a comment - 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);
}
Hide
Permalink
Wil Sinclair added a comment -

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

Show
Wil Sinclair added a comment - This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.
Hide
Permalink
Wil Sinclair added a comment -

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

Show
Wil Sinclair added a comment - Reassigning to Ralph since he's the new maintainer of Zend_Db
Hide
Permalink
Ralph Schindler added a comment -

will evaluate within 2 weeks

Show
Ralph Schindler added a comment - will evaluate within 2 weeks

People

Vote (0)
Watch (1)

Dates

  • Created:
    Updated:

Time Tracking

Estimated:
1w
Original Estimate - 1 week
Remaining:
1w
Remaining Estimate - 1 week
Logged:
Not Specified
Time Spent - Not Specified
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Zend Framework. Try JIRA - bug tracking software for your team.