Hibernate 6.4+ drops join conditions

Hibernate ORM
1

Starting with Hibernate 6.4, if there is a Book entity containing an @ElementCollection of BookPage @Embeddable-s and I try executing following HQL query:

SELECT b.id, INDEX(p)
FROM Book b JOIN b.pages p ON p.text LIKE '%important%'

I get an SQL query missing the explicit join condition ON p.text LIKE '%important%':

select
        b1_0.id,
        p1_0.number 
    from
        books b1_0 
    join
        book_pages p1_0 
            on b1_0.id=p1_0.book_id

To work around this, I have to use LEFT JOIN instead of INNER JOIN (and then check for NULL-s in the end) because for some reason queries with LEFT JOIN-s are not prone to this issue.

I have created a bug for this in JIRA - [HHH-17830] - Hibernate JIRA - and even though I realize that this topic can be considered an undesired cross-post, I figured that the issue looked bad enough to justify drawing a bit of additional attention to it.

1 Like
2

As you’ve discovered, using LEFT JOIN instead of INNER JOIN appears to work around the issue. While this isn’t ideal, you can filter out the unwanted results by checking for NULL values in your application code or in the query itself. Another approach, if the HQL workaround is not sufficient, is to use native SQL queries. This gives you full control over the SQL being executed, though it means giving up some of the benefits of using HQL/JPQL.

String sql = "SELECT b.id, p.number " +
"FROM books b " +
“JOIN book_pages p ON b.id = p.book_id AND p.text LIKE ‘%important%’”;

Query query = entityManager.createNativeQuery(sql);
List<Object> results = query.getResultList();

3

Or just update to 6.4.5 or newer since [HHH-17830] - Hibernate JIRA was already fixed.