[DC-1035] ORA-01791 due to bad driver name in Doctrine_Adapter_Oracle Created: 01/Sep/11  Updated: 17/Apr/14

Status: Open
Project: Doctrine 1
Component/s: Connection
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Blocker
Reporter: Jayson LE PAPE Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows 7 64 bits, PHP 5.2.11, Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi, Symfony 1.4.13



 Description   

When i execute this code:

 
$q = Doctrine_Query::create()
            ->from('AGENT ag')
            ->leftJoin('ag.CHANTIER_AGENT cag)
            ->orderBy('ag.nom')
            ->limit(10)
            ->execute();
$q2 = Doctrine_Query::create()
            ->from('AGENT ag')
            ->leftJoin('ag.CHANTIER_AGENT cag)
            ->orderBy('ag.nom')
            ->limit(10)
            ->execute();

Doctrine executes :

 
SELECT a.pk AS a__pk, a.ts AS a__ts, a.ck_agent AS a__ck_agent, a.matricule AS a__matricule, a.nom AS a__nom, 
a.prenom AS a__prenom, a.agent_maitrise AS a__agent_maitrise, c.pk AS c__pk, c.ts AS c__ts, c.ck_chantier_agent AS c__ck_chantier_agent, 
c.ek_chantier AS c__ek_chantier, c.fk_chantier AS c__fk_chantier, c.ek_agent AS c__ek_agent, c.fk_agent AS c__fk_agent 
FROM AGENT a 
LEFT JOIN CHANTIER_AGENT c ON a.ck_agent = c.ek_agent 
WHERE a.ck_agent IN (
SELECT a2.ck_agent FROM ( 
SELECT DISTINCT a2.ck_agent, a2.nom FROM AGENT a2 LEFT JOIN CHANTIER_AGENT c2 ON a2.ck_agent = c2.ek_agent ORDER BY a2.nom ) 
a2 WHERE ROWNUM <= 10) ORDER BY a.nom

SELECT a.pk AS a__pk, a.ts AS a__ts, a.ck_agent AS a__ck_agent, a.matricule AS a__matricule, a.nom AS a__nom, 
a.prenom AS a__prenom, a.agent_maitrise AS a__agent_maitrise, c.pk AS c__pk, c.ts AS c__ts, c.ck_chantier_agent AS c__ck_chantier_agent, 
c.ek_chantier AS c__ek_chantier, c.fk_chantier AS c__fk_chantier, c.ek_agent AS c__ek_agent, c.fk_agent AS c__fk_agent 
FROM AGENT a 
LEFT JOIN CHANTIER_AGENT c ON a.ck_agent = c.ek_agent 
WHERE a.ck_agent IN (
SELECT a2.ck_agent FROM ( 
SELECT DISTINCT a2.ck_agent FROM AGENT a2 LEFT JOIN CHANTIER_AGENT c2 ON a2.ck_agent = c2.ek_agent ORDER BY a2.nom ) 
a2 WHERE ROWNUM <= 10) ORDER BY a.nom

This causes "Oracle DB Error ORA-01791 not a SELECTed expression" because the sql query don't have a2.nom in SELECT DISTINCT and it's indispensable for ORDER BY a2.nom
The problem is caused by the variable $attributes in Doctrine_Adapter_Oracle :

 
protected $attributes = array(Doctrine_Core::ATTR_DRIVER_NAME    => "oci8",
                                  Doctrine_Core::ATTR_ERRMODE        => Doctrine_Core::ERRMODE_SILENT);

The problem is in Query.php line 1417 :

	if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'mssql' || $driverName == 'odbc') {

The driver name declared in Doctrine_Adapter_Oracle not in this conditional.
To resolve this we have to modify the declaration of $attributes in Doctrine_Adapter_Oracle to :

protected $attributes = array(Doctrine_Core::ATTR_DRIVER_NAME    => "oracle",
                                  Doctrine_Core::ATTR_ERRMODE        => Doctrine_Core::ERRMODE_SILENT);

An other problem is probably located at line 1409

 if (($driverName == 'oracle' || $driverName == 'oci') && $this->_isOrderedByJoinedColumn()) {

and 1497

        if (($driverName == 'oracle' || $driverName == 'oci') && $this->_isOrderedByJoinedColumn()) {

if don't correct the declaration of $attributes in Doctrine_Adapter_Oracle.


Generated at Thu Dec 18 05:49:14 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.