DDC-1960: mapping joins in native queries breaks if select columns are starting with columns from joined table

[doctrinebot]

Jira issue originally created by user dready:

Using a simple Testcase like in http://docs.doctrine-project.org/projects/doctrine-orm/en/2.1/reference/native-sql.html there are two Tables:

*) users:

   Column   |  Type   | Modifiers | Storage  | Description 
------------<ins>---------</ins>-----------<ins>----------</ins>-------------
 u_id       | integer | not null  | plain    | 
 u_name     | text    | not null  | extended | 
 address_id | integer | not null  | plain    | 

*) address:

  Column  |  Type   | Modifiers | Storage  | Description 
----------<ins>---------</ins>-----------<ins>----------</ins>-------------
 a_id     | integer | not null  | plain    | 
 a_street | text    | not null  | extended | 
 a_city   | text    | not null  | extended | 

address_id is a foreign key to address;

Now i created the Entities and setup a native query using ResultSetMappingBuilder:

$rsm = new \Doctrine\ORM\Query\ResultSetMappingBuilder($entityManager);
$rsm->addRootEntityFromClassMetadata('MyProject\Entity\Users', 'u');
$rsm->addJoinedEntityFromClassMetadata('MyProject\Entity\Address', 'a', 'u', 'address');

$query = '
    SELECT
        u.*,
        a.*
    FROM
        users u
    LEFT JOIN address a ON (u.address*id = a.a*id)
';

/*** @var $native \Doctrine\ORM\NativeQuery **/
$native = $entityManager->createNativeQuery($query, $rsm);

$ret = $native->getResult();

This returns the Entities correctly:

array(2) {
  [0] =>
  class MyProject\Entity\Users#61 (3) {
    protected $id =>
    int(1)
    protected $name =>
    string(5) "Smith"
    protected $address =>
    class MyProject\Entity\Address#63 (4) {
      protected $id =>
      int(1)
      protected $street =>
      string(8) "Broadway"
      protected $city =>
      string(8) "New York"
      protected $users =>
      class Doctrine\ORM\PersistentCollection#64 (9) {
        ...
      }
    }
  }
  [1] =>
  class MyProject\Entity\Users#66 (3) {
    protected $id =>
    int(2)
    protected $name =>
    string(7) "Sherlok"
    protected $address =>
    class MyProject\Entity\Address#67 (4) {
      protected $id =>
      int(2)
      protected $street =>
      string(13) "Oxford Street"
      protected $city =>
      string(6) "London"
      protected $users =>
      class Doctrine\ORM\PersistentCollection#68 (9) {
        ...
      }
    }
  }
}

BUT if you change the order of the select columns starting with ones from address you get borked Data:

$query = '
    SELECT
        a.*,
        u.*
    FROM
        users u
    LEFT JOIN address a ON (u.address*id = a.a*id)
';

array(2) {
  [0] =>
  class MyProject\Entity\Users#61 (3) {
    protected $id =>
    int(1)
    protected $name =>
    string(5) "Smith"
    protected $address =>
    class MyProject\Entity\Address#63 (4) {
      protected $id =>
      int(2)
      protected $street =>
      string(13) "Oxford Street"
      protected $city =>
      string(6) "London"
      protected $users =>
      class Doctrine\ORM\PersistentCollection#64 (9) {
        ...
      }
    }
  }
  [1] =>
  class MyProject\Entity\Users#66 (3) {
    protected $id =>
    int(2)
    protected $name =>
    string(7) "Sherlok"
    protected $address =>
    NULL
  }
}

This happens because the function Doctrine\ORM\Internal\Hydration\AbstractHydrator::_gatherRowData does not consider the Mapping i set up. Instead it just add the columns as they get starting with address ones.

Doctrine\ORM\Internal\Hydration\ObjectHydrator::_hydrateRow then knows the Mapping and ignores the first Address as there is no User to map on, cycling to the next row will then add the address of the second row to the user from the first one.

There are multiple ways to fix this. One would be to consider the mapping in *gatherRowData, the second to rewrite the *hydrateRow generating the Entities first and then the mapping in a second foreach loop.

This bugger had me for 2 days until i finally figured it out.

thanks

[doctrinebot]

Comment created by frederes:

Hello,

Has same issue with using DQL /createQuery() ! Try all the day to find where was my mistake but seems to be a CRITICAL bug !
How did you solve this ?

Doctrine version used : 2.3.1-DEV

$query = $this->getEntityManager()->createQuery(" SELECT cc, oc FROM category cc JOIN cc.offer_category oc WHERE cc.catalog = :catalog_id ORDER BY oc.name ASC ") ->setParameter(":catalog_id", $catalog_id) ;

Problem is that the order of the Aliases (cc, oc) is not considered on building SQL .
In my case, in the ObjectHydrator::hydrateRowData method :

$rowData = $this->gatherRowData($row, $cache, $id, $nonemptyComponents);

returns

Array
(

[oc] => Array
(
[id] => 14
[name] => toto
)
[cc] => Array
(
[catalog_id] => 1
[offer_category_id] => 14
)
)

As "oc" is a mapping, on the first loop the $parentAlias is not yet known and so :

if ($this->_rsm->isMixed && isset($this->_rootAliases[$parentAlias])) {
echo "parentObject 1\n";
$first = reset($this->_resultPointers);
$parentObject = $first[key($first)];
} else if (isset($this->_resultPointers[$parentAlias])) {
echo $parentAlias." parentObject 2\n";
$parentObject = $this->_resultPointers[$parentAlias];
} else {
// HERE : on first loop, for "oc", parent not yet known so skipped !!!
continue;
}


using a workaround on ObjectHydrator::hydrateRowData like this :
$rowData = array_reverse($rowData);

make it work...

Sorry for my dirty explanation...

[doctrinebot]

Comment created by sustmi:

Here is a failing test for this issue:
sustmi@584163f

Release v2.5.0 is still affected.

I think there is a problem with ObjectHydrator::$resultPointers array.
What is the purpose of this array anyway?
Is it supposed to hold the data between multiple ObjectHydrator::hydrateRowData() calls?

Side note: This error occurs even with pure DQL queries (without NativeQueries and custom ResultSetMappings).

[doctrinebot]

Comment created by sustmi:

Another simpler test case: sustmi@42a4da6

Also, I think this issue is a duplicate of http://www.doctrine-project.org/jira/browse/[DDC-2649](http://www.doctrine-project.org/jira/browse/DDC-2649) .

[sustmi]

I made a hotfix for this issue here: sustmi@de83b79
It needs some refactoring but I hope you get the idea.
(Array|Object)Hydrator needs $rowData['data'] to be in order from parent DQL alias to child DQL alias.
If the foreach ($rowData['data'] as $dqlAlias => $data) { loop processes child DQL alias before parent DQL, $this->_resultPointers does not contain pointer to parent's data from the current row but from the previous one.
See the failing test: sustmi@a6db93c#diff-9878dde71ae0e3ea89570bbdf876d29bR2041 .

[mnowaczyk]

Any news? This just cost me a few hours of debugging, googling and swearing when my heavily optimized native query suddenly started lazy-loading hundreds of objects one by one.

[sustmi]

I've just found old issues that are relevant to this one: #5249, #5443, #5454 .

These issues try to solve the problem that hydrators depend on the order of identifiers in SELECT clause. The issues try solve the problem on a different level - in DQL parser.
See:
https://github.com/doctrine/doctrine2/blob/bb1fe1d6c9e3b2ab837445a73b0e76e57683fb78/lib/Doctrine/ORM/Query/Parser.php#L265
and
https://github.com/doctrine/doctrine2/blob/bb1fe1d6c9e3b2ab837445a73b0e76e57683fb78/lib/Doctrine/ORM/Query/Parser.php#L383 .

It is strange because I remember the problem to happen even when using DQL (this code-path should be fixed). Sadly, I cannot remember the DQL code that would reproduce the issue. Maybe the query mentioned in #2633 (comment) reproduces the issue even in DQL.

[sustmi]

This test https://gist.github.com/doctrinebot/044a2dc627a11e5c3a9e#file-10846_ddc736test-php is relevant but it does not test hydrating multiple rows.
The problem occurs only when there are multiple rows to hydrate. The ArrayHydrator then sometimes incorrectly uses wrong data from previous row.

[boris-brtan]

Hi,
what is the status of this bug? is it fixed and merged already?

[boris-brtan]

Hi, we found some way by creating own package that is already on packagist.

[Ocramius]

@boris-brtan which package?

[grossmannmartin]

Hi,

I have created a test for this issue and it seems like it's already resolved. Is it possible to verify it? #9602

I prepared the fix for ArrayHydrator and test cases to prove it's fixed #9606. Is it possible to verify?
Thank you for your time :)