Try executing a query with offset set to 1 with ORM 5 and show the query that is generated. I bet you will have the same problem, you just never noticed because probably no user in your app ever requested the second page.
Looking to the SQL generated by ORM 5 when I execute the query there’s no offset information whatsoever, even when requesting pages that are not the first. It, also, doesn’t fail either. The results arrive with correct offset and limit, though, so I am assuming this is, somehow, done afterwards?
In summary, the logged SQL, when I do query.getResultList(), is the same one I already posted.
I am happy to keep providing information as I really need this solved but I bit puzzled by all of this. Let me highlight this again, no code changes were performed, the differences in the queries are the result of changing hibernate 5 to hibernate 6. I am surely missing something here.
And by the way, thank you for trying to help, I really need it 
Well, the only possible explanation then is that you are using a custom Dialect with a custom LimitHandler in ORM 5, because DB2Dialect wraps the SQL query into a subquery. You can debug into org.hibernate.loader.Loader#processResultSet and org.hibernate.loader.Loader#doQuery to see that the result set rows are actually skipped and figure out the condition that leads to this inefficient behavior.
I have no custom dialects in none of the versions. Running the application with both ORM 5 and ORM 6 I have this in the logs:
org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.DB2Dialect
I don’t even have this manually set in my application properties. The only thing I have referring to db2 is
database.somedatabase.datasource.driverClassName: com.ibm.db2.jcc.DB2Driver
and
database.somedatabase.jpa.database: db2
I am not sure I got your advice about debugging into those classes. Should I do it on ORM 5? This is the one that works, what would I be looking for?
Yes, debug in ORM 5. The behavior that you are mentioning for ORM 5 sounds buggy to me.
To be honest, my primary goal here is to make it work with Hibernate 6 rather than understanding why it does work in Hibernate 5. Do you think there’s something that can be done regarding that goal? What options do I have? Excluding asking for more temp space in the database because I already did that without success.
We first need to understand why it worked in ORM 5 to see if there is a problem with ORM 6. When we realize it only worked by accident in ORM 5, it should be easier to argue with your database guy that you need more temp space.
Which DB2 version do you use? That affects how the SQL is rendered. Maybe the offset .. fetch first .. rows only syntax performs better in DB2, though that requires at least DB2 LUW version 11.1.
So debugging into the classes you mentioned didn’t give me any idea of any strange behavior. I’ll post it here, maybe something catches your eye.
Here, as you can see, the maxRow is 20 which corresponds to the page size and everything runs without problem.
Here, none of the conditions evaluated to true and the flow just continues until the end.
(see second post as I can only attach one screenshot)
The behavior is always the same regardless of what page I am requesting.
In the this variable the query inside the QueryLoader is the exact same query I posted before, corresponding to the ORM 5, of course.
The DB2 version is 10.5, but from what I read in the docs it should still be compatible with ORM 6.
Does the code enter into the advance method? If so, please post the predicate that leads to entering that if-branch.
If the ORM 5 query that is executed does not contain an offset clause or row_number() query wrapper and does not enter into the advance method, the firstRow from setFirstResult() is not applied correctly and you retrieve wrong information.
If the advance method is entered, I need to know why, because it shouldn’t based on the code that I see in the ORM 5 repository.
In the processResultSet method none of those conditions evaluates to true so the answer is, no, it does not enter to the advance method.
Regarding the query, it’s the one I posted before, I am not sure why it does not have any offset information but it does result in paginated results and, again, the source code whence it originated is the same one that generates the ORM 6 query.
Are you join fetching a collection?
I am not sure I understood your question… it’s basically joining an attributes table. The main entity can have several attributes of this attributes table. In this case we are getting a specific attribute that will match the p.someAttribute that is specified in the query statement in the code.
Does your query contain a join fetch/left join fetch or an entity graph for an attribute that is of a subtype of Collection/Map i.e. mapped as @OneToMany/@ManyToMany or @ElementCollection?
This attribute has relation of OneToOne with the main entity.
There’s also an entity graph that’s being used:
final EntityGraph<?> fullGraph = em.getEntityGraph("MyEntity.full");
query.setHint("javax.persistence.fetchgraph", fullGraph);
that maps to the entiy:
@NamedEntityGraphs({
@NamedEntityGraph(name = "MyEntity.full", attributeNodes = {
@NamedAttributeNode(value = "client"),
@NamedAttributeNode(value = "displayNameAttribute")
})
})
public class MyEntity implements Serializable {
The only thing I changed in the code here was that I replaced query.setHint("javax.persistence.fetchgraph", fullGraph); by query.setHint("jakarta.persistence.fetchgraph", fullGraph);
I apologize for not mentioning this graph thing before but I didn’t think it was relevant, my bad.
… entity graph for an attribute that is of a subtype of
Collection/Mapi.e. mapped as@OneToMany/@ManyToManyor@ElementCollection?
So does client or displayNameAttribute match this ^^ description?
client has @ManyToOne relation and displayNameAttribute has @OneToOne relation but let me highlight that the displayNameAttribute is the one, apparently, causing the problem.
I tried reproducing your scenario with ORM 5.6, but couldn’t. See DB2 pagination test · beikov/hibernate-orm@5720fef · GitHub which contains a test case.
The resulting query always contains a fetch first 2 rows only clause at the end. If I add e.g. setFirstResult(2) it always wraps the query and uses the row number emulation.
You can checkout that commit/branch and run ./gradlew :hibernate-core:test --tests "org.hibernate.dialect.DB2PaginationTest" -Pdb=db2 "-DdbHost=<host-of-db2>". It expects a user/password db2inst/db2inst1-pwd to have access to a database called orm_test on port 50000. The host of the database is configurable and defaults to localhost.
I have no idea what sort of customizations you do or what you are not telling me that is relevant, but as long as I can’t reproduce this behavior with ORM 5, there is nothing I can do to help you.
I am not able top run db2 version 10.5 in localhost to execute that test case, unfortunately, but I kept obsessing about this issue and I think I have some new information about the root of the issue.
I was comparing both versions side by side and I noticed that when I alter the query in ORM 6, e.g. changing COALESCE(dna.value, p.name) to COALESCE(dna.id, p.name), to prevent the error from happening (the value column has a length of 10240, whereas id only 254) I get ALL the results, no pagination whatsoever is applied to the query, although it’s being set in the code with:
query.setFirstResult(Math.toIntExact(page.getOffset()));
query.setMaxResults(page.getPageSize());
before executing:
final List<MyEntity> result = query.getResultList();
So I am assuming that the temporary space issue occurs because we get 1000+ results from this query instead of getting 20 and it only works with the id column because it’s much smaller.
So the question now is, why isn’t the pagination applied?
We already noticed that in the SQL query that is generated does not have any offset or limit information but that goes both to ORM 5 and 6 and not just the 6.
It’s also worth noting that I am able to run directly in the database the SQL generated by ORM 5 but not the one generated by ORM 6.
So in the end, my theory does not stand very well. At this point I am just sharing whatever information I can in hopes that it means something to you that I was not able to achieve.
Well, let me know and in any case, thanks for trying.
So far, the queries you posted were consistently showing that ORM 5 does not do pagination (no row_number()), but ORM 6 does.
Since you’re using setFirstResult()/setMaxResults(), ORM 5 should also do pagination i.e. render a query wrapper with the row_number().
Based on the information you provided, there appears to be a bug in ORM 5 or your configuration/integration code. AFAICT, ORM 6 behaves properly.
I get ALL the results, no pagination whatsoever is applied to the query, although it’s being set in the code with
I don’t know anything about your query and data model since you obfuscate it so much, but usually when you try to paginate, you will have to order by something useful and additionally order by something unique, so that you get a consistent ordering.
I would suggest you rather use order by LOWER(COALESCE(dna.value, p.name)) asc, p.id