But what I don’t understand (well, one of the things) is why in the ORM 5, although the generated SQL does not have any pagination clauses, the results that I get when executing the code are what is expected, i.e. executing em.getResultList() does come with only 20 results, which corresponds to the page size and with the proper offset.
You can start debugging in org.hibernate.hql.internal.ast.QueryTranslatorImpl#list to see how the pagination is implemented. Potentially, the pagination happens in-memory, but according to the information that you have provided until now, this should not be the case.
If needsDistincting is true, you will see that pagination is applied in-memory. The other place where such in-memory skipping happens is in org.hibernate.loader.Loader#processResultSet, but you wrote that the advance method is never entered.
In the ORM 6 context, I am trying to understand these entities and how they might relate to the issue we have and, roughly, it’s this:
- a
Profileentity - a
ProfileAttributeentity - a
ProfileSingleAttributeentity
The Profile represents a table of profiles and the ProfileAttribute represents a table of profile attributes. ProfileSingleAttribute implements the same interface that ProfileAttribute and is supposed, as far as I understand, to represent a specific profile attribute.
In theory Profile could have a one-to-many relation with ProfileAttributes but it doesn’t. What it does have is a relation of one-to-one with a specific profile attribute that’s DisplayNameAttribute, like this:
@OneToOne(fetch = FetchType.LAZY, mappedBy = "profile")
private ProfileAttribute.DisplayNameAttribute displayNameAttribute;
As you can see this DisplayNameAttribute a static class declared inside ProfileAttribute that extends ProfileSingleAttribute.
The profile_attributes table has both a profile_id and a profile_key and these columns form a composite primary key used by the entity ProfileAttribute and there’s a many-to-one relation with Profile, like this:
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "PROFILE_ID", insertable = false, updatable = false)
private Profile profile;
In its turn ProfileSingleAttribute declares only profile_id as an id and profile_key is just a normal column, like this:
@Id
@Column(name = "PROFILE_ID", updatable = false)
private String profileId;
@Column(name = "PROFILE_KEY", updatable = false, insertable = false)
private String key;
but in the top of the class we have this:
@DiscriminatorColumn(length = 254, name = "PROFILE_KEY", discriminatorType = DiscriminatorType.STRING)
I am not sure what this @DiscriminatorColumn does exactly but I noticed that the query statement that we run:
FROM Profile p LEFT JOIN FETCH p.displayNameAttribute dna WHERE p.customer.id = :customerId
gives rise to an SQL query that instead of having:
from PROFILES a1_0
left join PROFILE_ATTRIBUTES c1_0 on a1_0.PROFILE_ID = c1_0.PROFILE_ID AND c1_0.PROFILE_KEY = 'displayname'
has:
from PROFILES a1_0
left join PROFILE_ATTRIBUTES c1_0 on a1_0.PROFILE_ID = c1_0.PROFILE_KEY AND c1_0.PROFILE_KEY = 'displayname'
Notice that it’s comparing id to key and not id to id.
I am not sure exactly why it’s behaving like this but I am hoping that you can grasp something out of it faster than me.
I also noticed that removing the FETCH from the query statement no longer causes the temporary space exception but it’s a much slower process because instead of one query I got 1 + 2n queries and that mistake I mentioned above about comparing profile_id to profile_key still remains and no pagination is applied.
Do you have any idea or suggestion regarding all of this?
I suppose the comparison of id to key happens in ORM 6? That would be a bug, but I don’t have the full entity model to understand if you might have a wrong mapping, so please try to create a reproducer with our test case template (hibernate-test-case-templates/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java at main · hibernate/hibernate-test-case-templates · GitHub) and if you are able to reproduce the issue, create a bug ticket in our issue tracker(https://hibernate.atlassian.net) and attach that reproducer.
I’d need at least the code for the involved entity classes.
I copied the relevant entities to my fork of the test repository you pointed to and I cannot reproduce the behavior in any of the versions I tried, 6.1.7.Final, 6.2.22.Final or 6.4.2.Final. In my application the SQL output for:
"FROM Profile$Active p LEFT JOIN FETCH p.commonDisplayNameAttribute dna WHERE p.customer.id = :customerId order by LOWER(COALESCE(dna.value, p.name)) asc"
is:
select *
from (select a1_0.PROFILEID c0,
a1_0.LASTMODIFIEDATTRIBUTESTS c1,
c1_0.PROFILEID c2,
c1_0.AUTOMATICALLYSET c3,
c1_0.PROFILEKEY c4,
c1_0.PROFILEVALUE c5,
a1_0.CREATETS c6,
a1_0.CUSTOMERID c7,
a1_0.DELETETS c8,
a1_0.DISABLETS c9,
a1_0.LASTMODIFIEDUSER c10,
a1_0.LASTMODIFIEDCOMMENT c11,
a1_0.LASTMODIFIEDTS c12,
a1_0.PROFILENAME c13,
row_number() over(order by lower(coalesce(c1_0.PROFILEVALUE,a1_0.PROFILENAME))) rn
from PROFILES a1_0
left join PROFILES_ATTRIBUTES c1_0 on a1_0.PROFILEID = c1_0.PROFILEKEY
where a1_0.CUSTOMERID = ?
and CASE
WHEN a1_0.DELETETS IS NOT NULL AND a1_0.DELETETS != '1970-01-01 01:00:00' THEN 'DELETED'
WHEN a1_0.DISABLETS IS NOT NULL AND a1_0.DISABLETS != '1970-01-01 01:00:00' THEN 'DISABLED'
ELSE 'ACTIVE' end = 'ACTIVE') r_0_
where r_0_.rn <= ? + ?
and r_0_.rn > ?
order by r_0_.rn
and in the test repository:
select
a1_0.PROFILEID,
a1_0.LASTMODIFIEDATTRIBUTESTS,
c1_0.PROFILEID,
c1_0.AUTOMATICALLYSET,
c1_0.PROFILEKEY,
c1_0.PROFILEVALUE,
a1_0.CREATETS,
a1_0.CUSTOMERID,
a1_0.DELETETS,
a1_0.DISABLETS,
a1_0.LASTMODIFIEDUSER,
a1_0.LASTMODIFIEDCOMMENT,
a1_0.LASTMODIFIEDTS,
a1_0.PROFILENAME
from
PROFILES a1_0
left join
PROFILE_ATTRIBUTES c1_0
on a1_0.PROFILEID=c1_0.PROFILEID
where
a1_0.CUSTOMERID=?
and CASE
WHEN a1_0.DELETETS IS NOT NULL
AND a1_0.DELETETS != '1970-01-01 01:00:00' THEN 'DELETED'
WHEN a1_0.DISABLETS IS NOT NULL
AND a1_0.DISABLETS != '1970-01-01 01:00:00' THEN 'DISABLED'
ELSE 'ACTIVE'
end='ACTIVE'
order by
lower(coalesce(c1_0.PROFILEVALUE,a1_0.PROFILENAME))
offset
? rows
fetch
first ? rows only
It’s quite different, and the code is the same. Not only the in the application is matching wrong columns, PROFILEID with PROFILEKEY, but neither of the queries has the other join for the displayName attribute as it was in ORM 5:
left join PROFILE_ATTRIBUTES c1_0 on a1_0.PROFILEID = c1_0.PROFILEID AND c1_0.PROFILEKEY = 'common.displayname'
I cannot understand what the hell is going on. The code is the same, the entities are the same. I checked the entityManager to see if any crazy configuration was there but just standard stuff.
Feel free to take a look at the repository if you think it might help:
Are you 100% certain that you are using 6.2.17.Final in your application? Note that the queries might be different because of the database version that your using for the test.
Yes, I was using 6.2.17 but yesterday I bumped it to 6.2.22 to be sure that I was using the same version as the test and it’s the same.
Hello @arqaos, I’ve also tried running your tests here:
the test is passing and I can confirm the behavior looks coorect to me: the column used in the join condition is PROFILEID on both sides and the discriminator formula is filtering 'ACTIVE' profiles only, which looks correct. I also see the left join with the PROFILE_ATTRIBUTES join table. Here’s the full generated query:
select
a1_0.PROFILEID,
a1_0.LASTMODIFIEDATTRIBUTESTS,
c1_0.PROFILEID,
c1_0.AUTOMATICALLYSET,
c1_0.PROFILEKEY,
c1_0.PROFILEVALUE,
a1_0.CREATETS,
a1_0.CUSTOMER_ID,
a1_0.DELETETS,
a1_0.DISABLETS,
a1_0.LASTMODIFIEDUSER,
a1_0.LASTMODIFIEDCOMMENT,
a1_0.LASTMODIFIEDTS,
a1_0.PROFILENAME
from
PROFILES a1_0
left join
PROFILE_ATTRIBUTES c1_0
on a1_0.PROFILEID=c1_0.PROFILEID
where
a1_0.CUSTOMER_ID=?
and CASE
WHEN a1_0.DELETETS IS NOT NULL
AND a1_0.DELETETS != '1970-01-01 01:00:00' THEN 'DELETED'
WHEN a1_0.DISABLETS IS NOT NULL
AND a1_0.DISABLETS != '1970-01-01 01:00:00' THEN 'DISABLED'
ELSE 'ACTIVE'
end='ACTIVE'
order by
lower(coalesce(c1_0.PROFILEVALUE,a1_0.PROFILENAME))
offset
? rows
fetch
first ? rows only
I’m not sure why the generated query in your application is different, maybe it has something to do with Spring (or another component manipulating the query)?
Anyways, I would suggest adding data in your test case and some assertions as to what you expect the results of the query to be and see if you can trigger an error. If you can, it will be much easier for us to understand what might be going wrong.
Thank you for your reply. I am going to pause this effort for the moment, I have no viable pathway to follow at this point. I will try to get back at it later. Thank you for trying to help.
When Hibernate ORM boots up it will print out the version. Trying looking for that in the logs. My gut feeling tells me you are using an old version.
