Firebird Hibernate dialect misplaces first ? and skip ? parameters in statement

Question

Configuration: Firebird 2.5, Hibernate 6.2 or 6.5

When I call custom method in PagingAndSortingRepository with Pageable object set to page > 0 the generated statement has misplaced first and skip parameters (They are inserted by Spring + Hibernate) so I do not have any control over them.

Example:

-- Controller

Pageable paging = PageRequest.of(1, 10);
Page<MaterialBrand> items = repository.findByMaterialSizesQuery(25, paging);

-- Repository

@Query(
value = "select t_m_brands.id...... where gmin <= :size and gmax >= :size",
nativeQuery = true
)
Page<MaterialBrand> findByMaterialSizesQuery(@Param("size") Float size, Pageable pageable);

Log output: Hibernate: select skip ? first ? t_m_brands.id,....

Which results in Error: There was an unexpected error (type=Internal Server Error, status=500). could not prepare statement [Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 21; ? [SQLState:42000, ISC error code:335544634]]

Certainly properly generated statement should look like this

Hibernate: select first ? skip ? t_m_brands.id,....

I have to add that when I use standard methods e.g repository.findAll(paging) everything is OK and first and skip are at proper positions.

Hoping for any solutions.

Tried to implement QueryRewriter with Repository but the query in rewrite method is still not prepared statement so no effect.

Answer 1

As a workaround, you can define a custom Firebird dialect in your project to override its LimitHandler implementation so it provides the correct output:

package nl.lawinegevaar.hb.custom;

import org.hibernate.community.dialect.FirebirdDialect;
import org.hibernate.dialect.pagination.AbstractLimitHandler;
import org.hibernate.dialect.pagination.LimitHandler;
import org.hibernate.query.spi.Limit;

public class CustomFirebirdDialect extends FirebirdDialect {

    @Override
    public LimitHandler getLimitHandler() {
        return FirstSkipLimitHandler.INSTANCE;
    }

    private static class FirstSkipLimitHandler extends AbstractLimitHandler {

        private static final LimitHandler INSTANCE = new FirstSkipLimitHandler();

        @Override
        public String processSql(String sql, Limit limit) {
            boolean hasFirstRow = hasFirstRow(limit);
            boolean hasMaxRows = hasMaxRows(limit);

            if (!hasFirstRow && !hasMaxRows) {
                return sql;
            }

            StringBuilder firstSkip = new StringBuilder();

            if (hasMaxRows) {
                firstSkip.append(" first ?");
            }
            if (hasFirstRow) {
                firstSkip.append(" skip ?");
            }

            return insertAfterSelect(firstSkip.toString(), sql);
        }

        @Override
        public final boolean supportsLimit() {
            return true;
        }

        @Override
        public boolean supportsOffset() {
            return true;
        }

        @Override
        public final boolean bindLimitParametersFirst() {
            return true
        }

        @Override
        public boolean bindLimitParametersInReverseOrder() {
            return true;
        }

        @Override
        public final boolean supportsVariableLimit() {
            return true;
        }
    }
}

Change your Hibernate configuration to use this custom dialect instead of org.hibernate.community.dialect.FirebirdDialect.

Feel free to use it (I'd suggest using your own package name ;). As it is derived from Hibernate, its license is LGPL 2.1.

Looking at the Hibernate commit history, it seems to have been broken during refactoring done for Hibernate 6.0 by reusing a handler that generates skip ? first ? instead of the one the dialect originally had in Hibernate 5 and older to generate first ? skip ?.

As an aside, while writing tests to confirm my solution worked, I found out that Hibernate generates the correct query when using JPQL/HQL queries (as that uses a different method of adding offset and limit), but the wrong query when adding limit and offset to a native query. So another workaround would be to not use a native query, and instead make it a JPQL query.

I have reported this to Hibernate as issue HHH-18213, and created a pull request against Hibernate 6.5. This pull request has been accepted and should be released together with Hibernate 6.5.3.