ZF-3168: Zend_Db_Select naturalJoin with Oracle

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:


$select = new Zend_Db_Select();
$select->form('table1');
$select->joinNatural('table2');

renders


SELECT table1.*, table2.* FROM table1 NATURAL JOIN table2;

But in this case, Oracle only accepts:


SELECT * FROM table1 NATURAL JOIN table2;

Another code:


$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


SELECT table1.*, table2.*, table3.* FROM table1 NATURAL JOIN table2 INNER JOIN table3 ON table1.col = table3.col;

In this case, Oracle accepts:


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:


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

renders


SELECT table1.col1, table2. FROM table1 NATURAL JOIN table2;

In this case, Oracle accepts:


SELECT col1 FROM table1 NATURAL JOIN table2;

Comments

Correction

New patch with associated tests

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

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

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

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

2) 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

3) 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

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

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.