Different SQL queries for @ManyToMany relationships in Hibernate 5 and 6
Description
Attachments
Activity
David Kolb February 6, 2024 at 1:56 PM
Hi, maybe it was overlooked. I already created a follow-up ticket :
Summary: It seems that the problem is still present when using @Inheritance(strategy = InheritanceType.JOINED) but it has been fixed for @ManyToMany relationships.
In any case, thanks for your support so far, I think there is only a little thing missing.
Christian Beikov January 25, 2024 at 5:50 PM
I think we can safely avoid creating a join group if there is no user-supplied
ONcondition, which should make you all happy. I’ll see what I can do.
Actually, this is exactly what I did as part of the PR for this issue. So if any of you still encounter problems, please create a new Jira issue.
Christian Beikov January 25, 2024 at 5:47 PM
I asked on the PostgreSQL mailing list, but nobody cared to answer:
your workaround does not provide the same semantics and might lead to wrong results.
The problem is that a left join of a “join group” is not the same as left joining every table of the join group.
In case of 1-1 joins the semantics would be the same, but if the join group contains a 1-n inner join, the semantics are different.
Also note that “additional join conditions” on a HQL collection/join table join may use both, the collection/join table and the target table alias and the condition must be able to affect the whole join group.
I think we can safely avoid creating a join group if there is no user-supplied ON condition, which should make you all happy. I’ll see what I can do.
Johannes Dieckmann January 25, 2024 at 4:54 PM
Hi, i can confirm, that the issue regarding joined-inheritance is not fixed on our side with the new hibernate version. We are now on 6.4.1.Final.
David Kolb January 16, 2024 at 1:26 PM
As the “joined inheritance” is not part of the ticket description and issue with @ManyToMany has been fixed (I could confirm that with another reproducer), I created another ticket:
In Hibernate 5, two “cascading“ joins are generated for many-to-many relationships. In Hibernate 6, a join with a nested join is generated instead. I observed much longer response times for some queries generated by Hibernate 6. In Postgres, the execution plans are different.
Here is an example that illustrates this
When executing
Hibernate 5 generates
Hibernate 6 generates
In dataset that work with, the Hibernate 6 version of the queries results often in full table scans while Hibernate 5 versions use indices. The queries on that dataset are much more complex though than this simple example.
Questions
Was it a conscious decision to use different structure of the join clauses or a side effect of introducing SQM?
If conscious decision, is it possible to use a hint with Hibernate 6 that results in a query with two “cascading“ joins?