Hibernate Search - MySQL error too many joins with Joined Inheritance model

Question

I've just encountered the following MySQL error in my web app

Too many tables; MySQL can only use 61 tables in a join

This is occurring when performing a Hibernate Search (version 5.5.2) query and I'm not entirely sure why that many joins are required. Here is a simplified example of my entity model:

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public class Profile {
    Integer id;

    @ManyToOne
    RelatedEntityOne joinOne;
}

@Indexed
@Entity
public class BusinessProfile extends Profile {
    @ManyToOne
    RelatedEntityTwo joinTwo;
}

@Indexed
@Entity
public class UserProfile extends Profile {
    @ManyToOne
    RelatedEntityThree joinThree;
}

Here is the code that performs the query

FullTextEntityManager ftem = Search.getFullTextEntityManager(em);
FullTextQuery fullTextQuery = ftem.createFullTextQuery(myQuery, UserProfile.class);
List result = fullTextQuery.getResultList();

And here is an example of the generated SQL

SELECT * 
FROM Profile root 
LEFT OUTER JOIN BusinessProfile join_1 ON root.id = join_1.id
LEFT OUTER JOIN UserProfile join_2 ON root.id = join_2.id
LEFT OUTER JOIN RelatedEntityOne join_3 ON root.x = join_3.x
LEFT OUTER JOIN RelatedEntityTwo join_4 ON join_1.x = join_4.x
LEFT OUTER JOIN RelatedEntityThree join_5 ON join_2.x = join_5.x
WHERE root.id IN (...)

So in this simplified example there are 5 joins. Which would make sense if I was performing a query on the parent class Profile. However I've passed the child class UserProfile to the createFullTextQuery method, so I would expect the generated SQL to look more like this:

SELECT * 
FROM UserProfile root 
LEFT OUTER JOIN Profile join_1 ON root.id = join_1.id
LEFT OUTER JOIN RelatedEntityOne join_2 ON join_1.x = join_2.x
LEFT OUTER JOIN RelatedEntityThree join_3 ON root.x = join_3.x
WHERE root.id IN (...)

I'm not sure if this is an issue with Hibernate, Hibernate Search, my own code, or if there is no issue and everything is behaving as intended. I don't see any reason for it to be joining the sibling tables given that we've identified which child table to use.

Answer 1

I confirm it's a bug in Hibernate Search. Here is the JIRA I just created: https://hibernate.atlassian.net/browse/HSEARCH-2301 .

I already wrote a fix but I have to go and doesn't have the time to clean it up; I'll post a PR later today.

Thanks for spotting this issue!

UPDATE we fixed it in 5.5.4.Final: http://in.relation.to/2016/06/29/Polishing-Polishing-And-More-Polishing-Hibernate-Search-5-5-4-Final/