[FIXED] Hibernate @Formula lazy-loading for JPA Sort

Issue

I have Entity with @Formula field, which contains select expression. Then I refer to this field as Hibernate meta-model's string attribute and use it in Sort.by() and therefore in PageRequest.of() methods. It works just fine until I add @Basic(fetch = FetchType.Lazy), after that I get Caused by: org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list with o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42P10.

What exactly I'm doing wrong?

P. S. I've turned on hibernate-enhance with enableLazyInitialization = true (otherwise lazy-loading for @Formula field won't work, as much as I know)

Field code looks like this:

    @Formula("(SELECT st.fio " +
            "FROM conjunction_table ct " +
            "JOIN second_table st ON st.id = ct.second_table_id " +
            "WHERE ct.first_table_id = id " +
            "ORDER BY st.fio " +
            "LIMIT 1)")
    @Basic(fetch = FetchType.LAZY)
    private String secondTableFio;

Solution

The lazy fetching does not include the computed property in your SELECT DISTINCT clause, which then causes this error, since there is now a property in the ORDER BY clause, which is not selected.

This is restricted by postgres because the DISTINCT could remove duplicate rows from the result which may actually have different ORDER BY values, so that the decision on how to order the selected rows cannot be made: PG::Error: SELECT DISTINCT, ORDER BY expressions must appear in select list

Answered By - fladdimir
Answer Checked By - Katrina (JavaFixing Volunteer)