BUG: Calling QueryResult->count() returns incorrect number of results when criterions on related entities are used

[tvt]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

Calling count() on a QueryResult that use properties of related entities in criterions returns wrong counts.

It's caused by the fix in #3140.

The SQL generated by the Query uses LEFT JOINs for criterions on related entities. If there are more than one related entity that matches the criterions the SQL will return multiple rows if the DISTINCT clause is not added.

I hope this makes sense, but otherwise the test case should speak for itself.

Expected Behavior

Calling count() on a QueryResult should give back the same number as getting the entities from the QueryResult and counting them.

Steps To Reproduce

Easiest way is to add the following test case to QueryTest.php.

It'll fail in the last assert of the test case.

    /**
     * @test
     */
    public function subpropertyQueriesFailCountAlias()
    {
        $testEntityRepository = new \Neos\Flow\Tests\Functional\Persistence\Fixtures\TestEntityRepository();
        $testEntityRepository->removeAll();

        $testEntity = new \Neos\Flow\Tests\Functional\Persistence\Fixtures\TestEntity;
        $testEntity->setName('Flow');

        $subEntity1 = new \Neos\Flow\Tests\Functional\Persistence\Fixtures\SubEntity;
        $subEntity1->setContent('foo');
        $subEntity1->setParentEntity($testEntity);
        $testEntity->addSubEntity($subEntity1);
        $this->persistenceManager->add($subEntity1);

        $subEntity2 = new \Neos\Flow\Tests\Functional\Persistence\Fixtures\SubEntity;
        $subEntity2->setContent('foo');
        $subEntity2->setParentEntity($testEntity);
        $testEntity->addSubEntity($subEntity2);
        $this->persistenceManager->add($subEntity2);

        $testEntityRepository->add($testEntity);

        $this->persistenceManager->persistAll();

        $query = new Query(\Neos\Flow\Tests\Functional\Persistence\Fixtures\TestEntity::class);

        $constraint = $query->logicalAnd($query->equals('subEntities.content', 'foo'));
        $result = $query->matching($constraint)->execute();

        $count = $result->count();
        $arrayCount = $result->toArray();

        self::assertEquals(1, count($arrayCount), 'This correctly returns 1');
        self::assertEquals(1, $count, 'this returns 2');
    }

Environment

- Flow: 7.3 and up
- PHP: 7.4 and up

Anything else?

No response

[kitsunet]

@kdambekalns would be nice to discuss this, as you have done a lot around the DB parts of Flow.