Incorrect generated SQLs with AnyAggregator

[stepapo]

Describe the bug

Generated SQLs are wrong for some of the filters using new aggregation features of 5.0 RC. Specifically a different behavior is expected when combining AnyAggregator with another AnyAggregator or with other aggregation function.

To Reproduce

1. Let's say I want to find all books that have both tags ID 1 and 2. Throughout development of 5.0 this was possible with correct result by doing:

   $books = $this->orm->books->findBy([
   ICollection::AND,
   [ICollection::AND, new AnyAggregator('1'), 'tags->id' => 1],
   [ICollection::AND, new AnyAggregator('2'), 'tags->id' => 2],       
   ]);
   Assert::same($books->count(), 1);

   However now ORM generates query that does not provide correct result:

   SELECT "books".* 
   FROM "books" AS "books" 
   LEFT JOIN "books_x_tags" AS "books_x_tags" ON ("books"."id" = "books_x_tags"."book_id") 
   LEFT JOIN "tags" AS "tags_1" ON ("books_x_tags"."tag_id" = "tags_1"."id") 
   LEFT JOIN "tags" AS "tags_2" ON ("books_x_tags"."tag_id" = "tags_2"."id") 
   WHERE ((("tags_1"."id" = 1)) AND (("tags_2"."id" = 2))) 
   GROUP BY "books"."id";

   Generated query has to be like this:

   SELECT "books".*
   FROM "books" AS "books" 
   LEFT JOIN "books_x_tags" AS "books_x_tags_1" ON ("books"."id" = "books_x_tags_1"."book_id") 
   LEFT JOIN "tags" AS "tags_1" ON ("books_x_tags_1"."tag_id" = "tags_1"."id") 
   LEFT JOIN "books_x_tags" AS "books_x_tags_2" ON ("books"."id" = "books_x_tags_2"."book_id") 
   LEFT JOIN "tags" AS "tags_2" ON ("books_x_tags_2"."tag_id" = "tags_2"."id")
   WHERE ((("tags_1"."id" = 1)) AND (("tags_2"."id" = 2))) 
   GROUP BY "books"."id";

2. Let's say I want to find all books with tag ID 3 and no other tag. In query logic that means I'm looking for all books with tag 3 that have one tag:

   $books = $this->orm->books->findBy([
   ICollection::AND,
   [ICollection::AND, new AnyAggregator('3'), 'tags->id' => 3],
   [CompareEqualsFunction::class, [CountAggregateFunction::class, 'tags->id'], 1],
   ]);
   Assert::same($books->count(), 1);

   Generated query is showing incorrect result again:

   SELECT "books".* FROM "books" AS "books" 
   LEFT JOIN "books_x_tags" AS "books_x_tags" ON ("books"."id" = "books_x_tags"."book_id") 
   LEFT JOIN "tags" AS "tags_3" ON ("books_x_tags"."tag_id" = "tags_3"."id") 
   LEFT JOIN "tags" AS "tags__COUNT" ON ("books_x_tags"."tag_id" = "tags__COUNT"."id") 
   GROUP BY "books"."id", "tags_3"."id" 
   HAVING ((("tags_3"."id" = 3)) AND (COUNT("tags__COUNT"."id") = 1));

   This is the correct version:

   SELECT "books".* 
   FROM "books" AS "books" 
   LEFT JOIN "books_x_tags" AS "books_x_tags_3" ON ("books"."id" = "books_x_tags_3"."book_id") 
   LEFT JOIN "tags" AS "tags_3" ON ("books_x_tags_3"."tag_id" = "tags_3"."id") 
   LEFT JOIN "books_x_tags" AS "books_x_tags__COUNT" ON ("books"."id" = "books_x_tags__COUNT"."book_id") 
   LEFT JOIN "tags" AS "tags__COUNT" ON ("books_x_tags__COUNT"."tag_id" = "tags__COUNT"."id") 
   GROUP BY "books"."id", "tags_3"."id" 
   HAVING ((("tags_3"."id" = 3)) AND (COUNT("tags__COUNT"."id") = 1));

   Since this was working throughout the development, namely one or two years ago, I hope the issue won't be too hard to fix. Great job and good luck with new version.

Versions::

• Database: PostgreSQL 13.13.0
• Orm: 5.0.0-rc1
• Dbal: 5.0.0-rc4

[hrach]

Thank you for the testing and detailed report. This makes me sure that the work makes sense, people are using it and even they're able to provide this great feedback. I'm starting to look into this :)

[stepapo]

Perfect, thank you!