ZF-3792: joinUsing() produces wrong SQL

Issue Type: Bug Created: 2008-07-29T09:15:53.000+0000 Last Updated: 2011-10-14T17:54:11.000+0000 Status: Closed Fix version(s): Reporter: Bill Karwin (bkarwin) Assignee: Adam Lundrigan (adamlundrigan) Tags: - Zend_Db_Select

  • zf-caretaker-adamlundrigan
  • zf-crteam-padraic
  • zf-crteam-priority

Related issues: - ZF-5372



<pre class="highlight">
$db->select()->from('A')->joinUsing('B', 'colname')

Should produce SQL syntax with the USING clause:

<pre class="highlight">

But it currently tries to guess at the tables being joined, and output a traditional equi-join:

<pre class="highlight">
SELECT * FROM A JOIN B ON A.colname = B.colname


Posted by Bill Karwin (bkarwin) on 2008-07-29T09:40:33.000+0000

This actually results in semantic problems in some queries, because Zend_Db_Select always assumes that a "Using" join is joining to the first table referenced in the query. This is not how the USING clause works in SQL.

<pre class="highlight">
$db->select()->from('A')->joinUsing('B', 'colname1')->joinUsing('C', 'colname2')

Should produce SQL syntax:

<pre class="highlight">
SELECT * FROM A JOIN B USING (colname1) JOIN C USING (colname2)

This should give the same results as this query:

<pre class="highlight">
SELECT * FROM A JOIN B ON B.colname1 = A.colname1 JOIN C ON C.colname2 = B.colname2

But in Zend_Db_Select, it produces the following SQL:

<pre class="highlight">
SELECT * FROM A JOIN B ON B.colname1 = A.colname1 JOIN C ON C.colname2 = A.colname2

Of course if table A has no colname2, this query returns an error.

If both A and B have a column named colname2, a query with USING (column2) is ambiguous, and should return an error. The SQL produced by Zend_Db_Select is not ambiguous, but it assumes that C is joined to A. This is in improper assumption.

Posted by Bill Karwin (bkarwin) on 2008-07-29T09:51:27.000+0000

Schema for above example:

<pre class="highlight">
CREATE TABLE A ( colname1 INT);
CREATE TABLE B ( colname1 INT, colname2 INT);
CREATE TABLE C ( coname2 INT);

SQL's USING clause actually knows which columns exist in which tables, and it decides which table you're trying to join based on which table has a column of the same name. So it knows that you intend to join C.colname2 = B.colname2 because it knows B has a column colname2.

When multiple tables in the query contain a column of the same name, this is ambiguous and the semantics for USING can't guess what you intended. So it throws an error, which is the right thing to do.

Posted by Ralph Schindler (ralph) on 2009-08-27T09:36:57.000+0000

Updating project management info: Minor, Should Have in Next Mini Release, assumed 2h of work.

Posted by Guilherme Blanco (guilhermeblanco) on 2009-08-27T13:35:51.000+0000

I think that joinUsing should change to support USING like it should and at the same time it should support a joinOn. I spoke with Ralph about this issue and I came with a suggested implementation:

<pre class="highlight">
    public function joinOn($table, $onCondition) {
        if (strpos($onCondition, ' ') === false) {
            $onCondition = $this->_name . '.' . $this->_primary . ' = ' . $table . '.' . $onCondition;

        $this->... = 'JOIN ' . $table . ' ON (' . $onCondition . ')';

It's a pseudo-code... I'm not too focused on Zend Framework internals. So it's just an idea. Just a quick explanation:

The condition is there to support custom queries too. Examples:

<pre class="highlight">
$q->joinOn('B', 'column'); // JOIN B ON (A.column = B.column)

$q->joinOn('B', ' = B.column2'); // JOIN B ON ( = B.column2)

Posted by Adam Lundrigan (adamlundrigan) on 2011-10-14T17:54:10.000+0000

After speaking with [~ralph] on the issue, it was decided to close this as "Won't Fix" for these two reasons:

  • SQL Server and Sybase do not support USING clause
  • JOIN USING and JOIN ON differ in the way they return columns. ie: JOIN ON would have t1.col and t2.col in the result set, but JOIN USING would have only col, so references to t1.col or t2.col in the WHERE clause would fail.

More information here:…

Have you found an issue?

See the Overview section for more details.


© 2006-2018 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.