DDC-3346: findOneBy returns an object with partial collection for the properties with mapping oneToMany/Fetch Eager

[doctrinebot]

Jira issue originally created by user adrienrusso:

findOneBy returns an object with partial collection for the properties with mapping oneToMany/Fetch Eager. This bug appear only for entities without inheritance.

Test\Bar:
    type: entity
    table: bar
    fields:
        code:
            type: string
    oneToMany:
        posts:
            targetEntity: Test\Post
            fetch: EAGER
            mappedBy: bar
            cascade: ['all']

Test\Post:
    type: entity
    table: post
    fields:
        content:
            type: text
    manyToOne:
        bar:
            targetEntity: Test\Bar
            cascade: []
            joinColumn:
                name: bar_id
                referencedColumnName: id

$bar = new \Test\Bar('foo');
$bar->addPost(
  new Test\Post('toto')
);
$bar->addPost(
  new Test\Post('tata')
);

$bar->getPosts()->count(); #value is 2
$manager->persist($bar);
$manager->flush();

$bar = $repository->findOneBy(['code' => 'foo']);
$bar->getPosts()->count(); #value is 1

$bar = $repository->findOneBy(['code' => 'foo']);
$bar->getPosts()->count(); #value is 2

I think this bug is due to the LIMIT 1 clause happening on findOneBy which also applies on joins generated here.

For instance, the generated SQL statement generated might look like

SELECT
    t0. ID AS id_1,
    t0.code AS code_2,
    t1. ID AS id_3,
    t1.content AS content_4,
    t1.bar*id AS bar_id*5
FROM
    bar t0
LEFT JOIN post t1 ON t1.bar_id = t0. ID
WHERE
    t0. code = 'foo'
LIMIT 1

[doctrinebot]

• depends on DDC-3531: [GH-1277] [DDC-3346] Failing test for issue (bad findOneBy behaviour with eager fetch)
• depends on DDC-3534: [GH-1280] [DDC-3346] #1277 find one with eager loads is failing

[doctrinebot]

Comment created by scaytrase:

Still expiriencing it at 2.5.0-alpha (b889e18). findOneBy cuts many-to-many association with fetch eager by 'LIMIT 1'

[doctrinebot]

Comment created by @Ocramius:

[~scaytrase] yeah, we don't have a fix for it yet: I suggest providing a PR with the failing test first, and if we can't get to it, trying to patch it yourself, or at least find out which code bit affects this behavior.

[doctrinebot]

Comment created by scaytrase:

I'm finishing such PR now. Will supply it to github soon

[doctrinebot]

Comment created by scaytrase:

here it is
#1277
http://www.doctrine-project.org/jira/browse/[DDC-3531](http://www.doctrine-project.org/jira/browse/DDC-3531)

Here is the failed travis build
https://travis-ci.org/scaytrase/doctrine2/jobs/47921568

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-1277] was assigned:
#1277

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-1277] was closed:
#1277

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-1280] was labeled:
#1280

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-1277] was labeled:
#1277

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-1280] was assigned:
#1280

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-1280] was merged:
#1280

[doctrinebot]

Comment created by @Ocramius:

Handled in DDC-3534

[doctrinebot]

Issue was closed with resolution "Fixed"

[doctrinebot]

Comment created by mbeccati:

FYI the test is failing on: https://revive.beccati.com/bamboo/browse/PHP-DOCTR-PHP54-249/test/case/18357533

As far as I can tell from https://www.sqlite.org/lang_select.html SQLite doesn't support OFFSET w/o LIMIT.

Doctrine\Tests\ORM\Functional\Ticket\DDC3346Test::testFindWithEagerFetchAndOffsetWillNotHydrateLimitedCollection
Exception: [Doctrine\DBAL\Exception\SyntaxErrorException] An exception occurred while executing 'SELECT t0.id AS id*1, t0.username AS username_2 FROM ddc3346*users t0 WHERE t0.username = ? OFFSET 0' with params ["bwoogy"]:

SQLSTATE[HY000]: General error: 1 near "OFFSET": syntax error

With queries:
6. SQL: '"COMMIT"' Params: 
5. SQL: 'INSERT INTO ddc3346*articles (user*id) VALUES (?)' Params: '1'
4. SQL: 'INSERT INTO ddc3346*articles (user*id) VALUES (?)' Params: '1'
3. SQL: 'INSERT INTO ddc3346_users (username) VALUES (?)' Params: 'bwoogy'
2. SQL: '"START TRANSACTION"' Params: 

Trace:
/home/atlassian/bamboo/xml-data/build-dir/PHP-DOCTR-PHP54/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:116
/home/atlassian/bamboo/xml-data/build-dir/PHP-DOCTR-PHP54/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:838
/home/atlassian/bamboo/xml-data/build-dir/PHP-DOCTR-PHP54/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php:875
/home/atlassian/bamboo/xml-data/build-dir/PHP-DOCTR-PHP54/lib/Doctrine/ORM/EntityRepository.php:181
/home/atlassian/bamboo/xml-data/build-dir/PHP-DOCTR-PHP54/tests/Doctrine/Tests/ORM/Functional/Ticket/DDC3346Test.php:53
/home/atlassian/bamboo/xml-data/build-dir/PHP-DOCTR-PHP54/vendor/phpunit/phpunit/src/Framework/TestCase.php:860
/home/atlassian/bamboo/xml-data/build-dir/PHP-DOCTR-PHP54/vendor/phpunit/phpunit/src/Framework/TestCase.php:737
/home/atlassian/bamboo/xml-data/build-dir/PHP-DOCTR-PHP54/vendor/phpunit/phpunit/src/Framework/TestResult.php:609
/home/atlassian/bamboo/xml-data/build-dir/PHP-DOCTR-PHP54/vendor/phpunit/phpunit/src/Framework/TestCase.php:693
/home/atlassian/bamboo/xml-data/build-dir/PHP-DOCTR-PHP54/vendor/phpunit/phpunit/src/Framework/TestSuite.php:716
/home/atlassian/bamboo/xml-data/build-dir/PHP-DOCTR-PHP54/vendor/phpunit/phpunit/src/Framework/TestSuite.php:716
/home/atlassian/bamboo/xml-data/build-dir/PHP-DOCTR-PHP54/vendor/phpunit/phpunit/src/TextUI/TestRunner.php:398
phar:///home/atlassian/bin/phpunit.phar/phpunit/TextUI/Command.php:179
phar:///home/atlassian/bin/phpunit.phar/phpunit/TextUI/Command.php:132
/home/atlassian/bin/phpunit.phar:584

[doctrinebot]

Comment created by @deeky666:

[~mbeccati] I believe this is fixed already by doctrine/dbal#782

[doctrinebot]

Comment created by mbeccati:

My mistake. The build wasn't cleaning up the vendor dir before running composer update, so it was still using an old dbal.