Pagination not working when using native queries (Named query exists but its result type is not compatible)

[bodiam]

Hi all,

I'm having an issue with pagination + projections + orm.xml + native queries.

I've described the issue in full detail here: https://stackoverflow.com/questions/72859385/hibernate-pagination-not-working-when-using-native-queries-named-query-exists-b, and I've created a fully reproducible sample here: https://github.com/bodiam/hibernate-bug, but in a nutshell, the issue is that when I create a native count query, the result type of the count query is not compatible.

Most likely the issue is here:

spring-data-jpa/spring-data-jpa/src/main/java/org/springframework/data/jpa/repository/query/NamedQuery.java

Line 188 in bc4347c

countQuery = em.createNamedQuery(countQueryName, Long.class);

or here

spring-data-jpa/spring-data-jpa/src/main/java/org/springframework/data/jpa/repository/query/NamedQuery.java

Line 194 in bc4347c

countQuery = em.createQuery(countQueryString, Long.class);

.

I have a workaround for now, which is to specify the mapping of the result like so:

    <sql-result-set-mapping name="cntColumnResult">
        <column-result name="cnt"/>
    </sql-result-set-mapping>

But I was hoping there might be a slightly more user friendly way of dealing with this, since it's been giving me quite the troubles to fix this (thanks Eugene!)

[gregturn]

Does it have anything to do with duplicate "from" here?

    <named-native-query name="Person.findPeople.count">
        <query>select count(*) from
            from person
        </query>
    </named-native-query>

[bodiam]

That's an oops indeed, but I just fixed it (in my original code I don't have this bug), and it doesn't affect the bug. It seems that any query will result in the same exception, only with a result-set-mapping can I fix it.

[bodiam]

Do you need any more information to look into this issue? Happy to help.

[gregturn]

I'm wondering if this is related to #3085.

It looks like in any named query, we fetch the query from Hibernate using one particular API, and we seem to get varied answers where the named query has been slightly altered through Hibernate's own parsing. Hence, we run into trouble if we attempt to then manipulate it through our parser.

Now I have a tweak on a branch that isn't (yet) confirmed as the way to go, but you are free to test it out and let us know if it happens to make a difference in your scenario. You can see it at https://github.com/spring-projects/spring-data-jpa/tree/issue/gh-3085, or simply adjust your project to use Spring Data JPA 3.2.0-gh-3085-SNAPSHOT in your setup. (You need to have snapshots properly enabled on your project to do this). NOTE: You must also have the proper version of Spring Data Commons (see the pom.xml from that branch) configured as well.

If this actually works for you, it would be useful information to share on this ticket.

[bodiam]

Sorry, this is too long ago, I don't have access to that project anymore.

[gregturn]

Sorry, this is too long ago, I don't have access to that project anymore.

In light of that as well as our updated adjustment to named queries in #3085, I'm closing this ticket.