Issues

ZF-3168: Zend_Db_Select naturalJoin with Oracle

Issue Type: Bug Created: 2008-04-23T12:51:49.000+0000 Last Updated: 2012-11-20T20:53:28.000+0000 Status: Closed Fix version(s): Reporter: Mickael Perraud (mikaelkael) Assignee: None Tags: - Zend_Db_Select

Related issues: Attachments: - zf3168.patch

Description

On Oracle, columns that are used for a named-join (either a NATURAL join or a join with a USING clause) cannot have an explicit qualifier. This code:

<pre class="highlight">
$select = new Zend_Db_Select();
$select->form('table1');
$select->joinNatural('table2');

renders

<pre class="highlight">
SELECT table1.*, table2.* FROM table1 NATURAL JOIN table2;

But in this case, Oracle only accepts:

<pre class="highlight">
SELECT * FROM table1 NATURAL JOIN table2;

Another code:

<pre class="highlight">
$select = new Zend_Db_Select();
$select->form('table1');
$select->joinNatural('table2');
$select->join('table3','table1.col = table3.col'); // Column doesn't present in table2

renders

<pre class="highlight">
SELECT table1.*, table2.*, table3.* FROM table1 NATURAL JOIN table2 INNER JOIN table3 ON table1.col = table3.col;

In this case, Oracle accepts:

<pre class="highlight">
SELECT * FROM table1 NATURAL JOIN table2 INNER JOIN table3 ON table1.col = table3.col;
    or
SELECT table3.* FROM table1 NATURAL JOIN table2 INNER JOIN table3 ON table1.col = table3.col;

Last piece of code:

<pre class="highlight">
$select = new Zend_Db_Select();
$select->form('table1','col1');  // Column col1 can be in table2 or not
$select->joinNatural('table2',null);

renders

<pre class="highlight">
SELECT table1.col1, table2. FROM table1 NATURAL JOIN table2;

In this case, Oracle accepts:

<pre class="highlight">
SELECT col1 FROM table1 NATURAL JOIN table2;

Comments

Posted by Mickael Perraud (mikaelkael) on 2008-04-25T12:57:32.000+0000

Correction

Posted by Mickael Perraud (mikaelkael) on 2008-04-26T03:05:01.000+0000

New patch with associated tests

Posted by Mickael Perraud (mikaelkael) on 2008-04-26T04:29:57.000+0000

Another correction, this time on tests (default autoQutoeIdentifier is true)

Posted by Wil Sinclair (wil) on 2008-06-09T12:30:15.000+0000

Ralph, it might be worth checking in to whether Simon can take this one on. If not, please evaluate and fix/categorize as necessary.

Posted by Ralph Schindler (ralph) on 2009-01-10T11:59:37.000+0000

Mickael, work with me next week to get this resolved.

Posted by Mickael Perraud (mikaelkael) on 2009-01-22T11:07:55.000+0000

I have also 2 tests failures that can be linked to this issue:

  1. testTableRelationshipFindManyToManyRowsetSelect(Zend_Db_Table_Relationships_OracleTest) Zend_Db_Statement_Oracle_Exception: 918 ORA-00918: dÚfinition de colonne ambigu SELECT z2.* FROM ( SELECT z1.**, ROWNUM AS "zend_db_rownum" FROM ( SELECT "i".*, "m".* FROM "zfbugs_products" "i" INNER JOIN "zfproducts" "m" ON "i"."product_id" = "m"."product_id" WHERE ("bug_id" = 1) AND ("i"."bug_id" = 1.000000) O RDER BY "product_name" ASC ) z1 ) z2 WHERE z2."zend_db_rownum" BETWEEN 1 AND 2 D:\web\ZendFramework\standard\trunk\library\Zend\Db\Statement\Oracle.php:274 D:\web\ZendFramework\standard\trunk\library\Zend\Db\Statement.php:283 D:\web\ZendFramework\standard\trunk\library\Zend\Db\Adapter\Abstract.php:457 D:\web\ZendFramework\standard\trunk\library\Zend\Db\Select.php:639 D:\web\ZendFramework\standard\trunk\library\Zend\Db\Table\Row\Abstract.php:1043

  2. testTableRelationshipMagicFindManyToManyRowsetSelect(Zend_Db_Table_Relationships_OracleTest) Zend_Db_Statement_Oracle_Exception: 918 ORA-00918: dÚfinition de colonne ambigu SELECT z2.* FROM ( SELECT z1.**, ROWNUM AS "zend_db_rownum" FROM ( SELECT "i".*, "m".* FROM "zfbugs_products" "i" INNER JOIN "zfproducts" "m" ON "i"."product_id" = "m"."product_id" WHERE ("bug_id" = 1) AND ("i"."bug_id" = 1.000000) O RDER BY "product_name" ASC ) z1 ) z2 WHERE z2."zend_db_rownum" BETWEEN 1 AND 2

Posted by Thomas VEQUAUD (echo_94) on 2009-02-28T01:29:08.000+0000

Hi there,

I've got an exception with an oracle query executed via Zend but it works when I execute it with Toad...

Here is the method building the query : $select = $this->select(); $select->setIntegrityCheck(false); $select->from(array("INDIVIDUS" => "EAI_TA_INDIVIDUS")); $select->joinInner(array("AFF" => "EAI_TA_AFFECTATIONS"), 'INDIVIDUS.IND_ID = AFF.IND_ID'); $select->where('INDIVIDUS.ANN_ANNEE = ?', (int)$annee); $select->where('INDIVIDUS.IND_MATRICULE = ?', $matricule);

Here is the query : SELECT "INDIVIDUS".*, "AFF".* FROM "EAI_TA_INDIVIDUS" "INDIVIDUS" INNER JOIN "EAI_TA_AFFECTATIONS" "AFF" ON INDIVIDUS.IND_ID = AFF.IND_ID WHERE (INDIVIDUS.ANN_ANNEE = 2009) AND (INDIVIDUS.IND_MATRICULE = '17483')

And here is the exception

exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[HY000]: General error: 918 OCIStmtExecute: ORA-00918: column ambiguously defined (ext\pdo_oci\oci_statement.c:146)' in D:\www\Zend Framework\Zend\Db\Statement\Pdo.php:238

Posted by Rob Allen (rob) on 2012-11-20T20:53:28.000+0000

Bulk change of all issues last updated before 1st January 2010 as "Won't Fix".

Feel free to re-open and provide a patch if you want to fix this issue.

Have you found an issue?

See the Overview section for more details.

Copyright

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

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

Contacts