Pageable and Criteria API - incorrect totalSize for many-to-many relations

[GeitV]

Expected Behavior

totalSize should be correct, counting only the final records.

Actual Behaviour

When response has only 1 member, totalSize still shows value of 2, when that one member has 2 values from join.

Probably the easiest fix would be to count only distinct ID-s.

Steps To Reproduce

Entities:

@MappedEntity
data class Author(
    @field:Id
    val id: Long,
    val name: String,
    @Relation(value = Relation.Kind.MANY_TO_MANY)
    val genres: List<Genre>
)

@MappedEntity
data class Genre(
    @field:Id
    val id: Long,
    val name: String
)

Create interface:

@JdbcRepository(dialect = Dialect.POSTGRES)
@Join(value = "genres", type = Join.Type.LEFT_FETCH)
interface AuthorRepository : CrudRepository<Author, Long>, JpaSpecificationExecutor<Author>

Create tables with test data:

create table author
(
    id   bigserial primary key,
    name text not null
);

insert into author (name) values ('Stephen King');

create table genre
(
    id   bigserial primary key,
    name text not null
);

insert into genre (name) values ('Horror'), ('Thriller'), ('Comedy');

create table author_genre
(
    author_id bigint not null references author (id) on delete cascade,
    genre_id  bigint not null references genre (id) on delete cascade,
    unique (author_id, genre_id)
);

insert into author_genre (author_id, genre_id) values (1, 1), (1, 2);

Do test:

@MicronautTest
class TotalSizeIssueTest(
    private val authorRepository: AuthorRepository
) : StringSpec({

    "test totalSize should match the number of authors" {
        val response = authorRepository.findAll(where {
            val genresJoin =
                root.joinList<Author, Genre>("genres", JoinType.LEFT)

            or {
                genresJoin[Genre::name] eq "Horror"
                genresJoin[Genre::name] eq "Thriller"
            }
        }, Pageable.from(0, 10))

        response.totalSize shouldBe response.content.size
    }
})

The sizes should match, but the assertion fails, as response size is 1 but total size is 2.

Environment Information

• Kotlin 1.9.21
• Micronaut 3.10.1

Example Application

No response

Version

3.10.1

[dstepanov]

@radovanradic This might be the same problem with JOIN entries missing when added in the citeria

[radovanradic]

This is 3.10.1, maybe works in newer versions? Will investigate later

[dstepanov]

@GeitV Can you please try the latest version?

[GeitV]

@dstepanov tried the same setup in Micronaut 4.2.2 and got the same result - bug still exists

[radovanradic]

@dstepanov You might be right, this could be solved with #2695 and using similar approach to criteria (using distinct)

[GeitV]

@radovanradic another issue emerged. When paginating, with MANY_TO_MANY relations, if you limit to x results you might get less than x results, even when you have totalSize > x.

Really bad experience, when user paginates for 10 results, and only gets 3, but it shows like there's still multiple pages of results to go.

Should I create separate issue for it or it is closely tied to this one?

[dstepanov]

@GeitV Does it happen with non-criteria queries?

[GeitV]

I can create test for it next week, to see if it's only related to Criteria or happens even without using Criteria.

[radovanradic]

We had similar issue https://github.com/micronaut-projects/micronaut-data/issues/1882 while ago and it probably needs better fix to handle this issue as well. The problem is we count records from main table + joined child entities and this gives wrong result. Might apply distinct count here for the count query and it might cover old issue and this one.

[tuantrannav]

we also encountered this bug, when you have one_to_many or many_to_many. It only returned 3 items. I think the bug is in the size. It seems like it is counting the size of the relation table. If you set the size > relation table size then it will return all items. We ended up rewriting out of JPA join, it seems using offset, LIMIT is not easy with joins.

[jayvdk]

I'm using the latest version (4.7.1 currently) and the bug is still persisting as decribed. This is really annoying for user interfaces. Is there any workaround ?

[dstepanov]

At this moment you need to do the filtering without any joins and then fetch the records again with joins.

[GeitV]

Would this get worked on at any point? We've hit this limitation multiple times in our project, getting bug tickets where some devs have again made this mistake of using joins. It's quite frustrating and writing it without Criteria makes for quite unreadable code.

The issue is that sometimes users would like to filter by join values, so we cannot use the workaround of "first fetch without joins and then with joins".

[dstepanov]

I will take a look at it at some point.

What do you mean by saying you cannot write it with criteria?

[GeitV]

Writing it with criteria would mean the count is invalid. And when you have page size 10, it might happen that you only get 1 record in response, when in actuality, there might be more than 10 records.

I don't see a way to only get IDs from result set either (using criteria), so currently, only workaround would be to fetch all records and do the pagination on the application-side (not so great). Or, another option is to write the plaintext SQL ourselves. A bit uglier, but more performant.

[dstepanov]

Looks like we don't support returning just IDs, but you can use a simple DTO:

        List<BookDto> ids = abstractBookRepository.findAll(new CriteriaQueryBuilder<BookDto>() {

            @Override
            public CriteriaQuery<BookDto> build(CriteriaBuilder criteriaBuilder) {
                CriteriaQuery<BookDto> query = criteriaBuilder.createQuery(BookDto.class);
                Root<Book> bookRoot = query.from(Book.class);
                query.select(bookRoot.get("id"));
                return query;
            }
        });
    
    @Introspected
    record BookDto(Long id) {
    }

Executing Query: SELECT book_.`id` FROM `book` book_