Pageable: incorrect totalSize of page when having one-to-many or many-to-many

[luisospina-sealed]

Expected Behavior

Page totalSize should be correct even if it has a list of children nested objects (one to many or many to many relationship)

Actual Behaviour

Page totalSize is greater than expected due to the join clause

Steps To Reproduce

Having an Author and a Book relationship (one Author can write multiple books, and one book can be written by one author).

Entities:

@MappedEntity
public class Author {

    @Id
    @GeneratedValue
    private Integer id;

    private String name;

    @Nullable
    @Relation(value = Relation.Kind.ONE_TO_MANY, mappedBy = "author")
    private List<Book> books;
}

@MappedEntity
public class Book {

    @Id
    @GeneratedValue
    private Integer id;

    private String name;

    @Relation(value = Relation.Kind.MANY_TO_ONE)
    private Author author;
}

Repository:

@JdbcRepository(dialect = Dialect.H2)
public interface AuthorRepository extends PageableRepository<Author, Integer> {

    @Join(value = "books", type = Join.Type.LEFT_FETCH)
    Page<Author> findAll(Pageable pageable);
}

The following test fails:

@Test
void testAuthorPageTotalSizeIs1() throws SQLException {
    Author author = authorRepository.save(new Author(null, "author"));
    Book book = bookRepository.save(new Book(null,  "book", author));
    Book book2 = bookRepository.save(new Book(null, "book2", author));

    Page<Author> authorPage = authorRepository.findAll(Pageable.UNPAGED);

    //Following assertion Fails. It is returning 2 as totalSize even when the content is just 1 Author
    Assertions.assertEquals(1, authorPage.getTotalSize());
}

Here's the count query generated by Micronauts data:

10:24:34.555 [Test worker] DEBUG io.micronaut.data.query - Executing Query: SELECT COUNT(*) FROM `author` author_ LEFT JOIN `book` author_books_ ON author_.`id`=author_books_.`author_id`

Should the count query not include the Join clause?

Environment Information

No response

Example Application

No response

Version

3.6.3

[PiotrBaczkowski]

@dstepanov it should also wrap main select query

so it should be

select mappedentity_.* from (select * from mapped entity where = :whereQuery order by :orderBy limit :limitSize) as mapped entity_ ....(all left joins here)

because now eventhou size = 10, and repository has 100 rows, if the first one has more than one nested entity, it will return wrong result(less than 10 items)

[dstepanov]

@PiotrBaczkowski Please create a new issue if it is not correct