Issue with IsNull applied to the entire table (All Ids) when using IsNull in the Id column about blaze-persistence

[beikov]

Thanks for the report and problem analysis. I'd very much appreciate if you could provide a PR with a test and fix.

[jwgmeligmeyling]

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.

[kakaruu]

@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.

[beikov]

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?

[jwgmeligmeyling]

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 😉