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
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;
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:
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
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.