Blazebit
Issue with IsNull applied to the entire table (All Ids) when using IsNull in the Id column
Version Info
Spring-boot & JPA : 3.0.6 Hibarnate : 6.2.7.Final blazePersistence : 1.6.9 Database : Mariadb 10.4 JDK : JAVA SE 17
Description
Entity
It is a table without a primary key in the actual DB, so I'm using fake IdClass.
@Entity
@IdClass(OriginLogIntegrityFakeKey.class)
public class OriginLogIntegrity {
@Id
private ZonedDateTime recvTime;
@Id
private String eqpIp;
...
@Id
private Integer curFileSize;
@Id
private String curMd5;
@Id
private String curMd5Dt;
@Id
private String clientSvcId;
}
query
It is a clause where the problem arises.
.where(
originLogIntegrity.recvTime.goe(startTime),
originLogIntegrity.recvTime.lt(endTime),
originLogIntegrity.curMd5.isNotNull(),
originLogIntegrity.curMd5.isNotEmpty()
)
Expected behavior
where
o1_0.recv_time>=?
and o1_0.recv_time<?
and o1_0.cur_md5 is not null
and character_length(o1_0.cur_md5)!=0
Actual behavior
where
o1_0.recv_time>=?
and o1_0.recv_time<?
and o1_0.client_svc_id is not null
and o1_0.cur_file_size is not null
and o1_0.cur_md5 is not null
and o1_0.cur_md5_dt is not null
and o1_0.eqp_ip is not null
and o1_0.file_name is not null
and o1_0.file_path is not null
and o1_0.org_file_size is not null
and o1_0.org_md5 is not null
and o1_0.org_md5_dt is not null
and o1_0.recv_time is not null
and o1_0.src_path_name is not null
and character_length(o1_0.cur_md5)!=0
Suspected code
JoinManager's 3167 line
private boolean isSingleValuedAssociationId(Type<?> type, Expression idExpression) {
AttributeHolder maybeSingularAssociationIdJoinResult = JpaUtils.getAttributeForJoining(metamodel, type, idExpression, null);
Attribute<?, ?> maybeSingularAssociationId = maybeSingularAssociationIdJoinResult.getAttribute();
if (!(maybeSingularAssociationId instanceof SingularAttribute<?, ?>)) {
return false;
}
if (!((SingularAttribute<?, ?>) maybeSingularAssociationId).isId()) {
return false;
}
return true;
}
Temporary solution
.where(
originLogIntegrity.recvTime.goe(startTime),
originLogIntegrity.recvTime.lt(endTime),
originLogIntegrity.curMd5.nullif(Expressions.nullExpression()).isNotNull(),
originLogIntegrity.curMd5.isNotEmpty()
)
where
o1_0.recv_time>=?
and o1_0.recv_time<?
and nullif(o1_0.cur_md5,null) is not null
and character_length(o1_0.cur_md5)!=0
Thanks for the report and problem analysis. I'd very much appreciate if you could provide a PR with a test and fix.
I do believe that when using composite keys with Hibernate, none of the Id columns are allowed to be nullable. Or was this requirement dropped in later versions of Hibernate?
This is the exact reason why I started using a single @Id field mapped to a ROW_NUMBER functions in my CTE's, compared to IdClass on a logical key.
@beikov Unfortunately, I don't know the internal logic exactly. I just found the cause by just taking a brut force debug..
If there's a situation it's absolutely necessary for all IDs to be 'ISNULL',
It would be fine if it could be adjusted with an option value such as IMPLICIT_GROUP_BY_FROM_SELECT.
@jwgmeligmeyling Yeah, In a typical situation, all IDs will be not null, so this issue may not be a big problem in general. However, it is not intuitive for users to have different results from the query that the user intended.
And in my case, The project I'm applying JPA and Blaze persistence is an old project. so there are tables that don't have an ID and It's a situation where I can't add an ID here arbitrarily.
The CTE proposal is interesting. I'm still a beginner, so I've only used CTE in select sentences. I don't know how to change the row of DB with cte yet, but I'll study it. Thank you.
I do believe that when using composite keys with Hibernate, none of the Id columns are allowed to be nullable. Or was this requirement dropped in later versions of Hibernate?
This is correct, though it is not "enforced" in any way. Materializing an entity with a null id column value will make the entity null as a whole.
Unfortunately, I don't know the internal logic exactly.
No worries, it will take some time though until I can look into this topic. Luckily, you have a workaround ;)
The CTE proposal is interesting.
The CTE approach is a good way to deal with this sort of situation. I guess @jwgmeligmeyling means you should add a @Id @Formula("row_number() over ()") Long rowNumber to that entity?
The CTE approach is a good way to deal with this sort of situation. I guess @jwgmeligmeyling means you should add a @Id @Formula("row_number() over ()") Long rowNumber to that entity?
I primarily meant that it's not outrageous to assume all id columns must be non-null if one of the id columns is non-null. However, the other columns definitely do not have to be checked. I am surprised a bit surprised that those too are rendered in the select clause.
W.r.t. the CTE, never tried it with a Formula, I always just bind it directly, but this is the approach I typically use yes 😉