Cross posted to stack overflow for visibility
http://stackoverflow.com/questions/3789 ... ance-modelI've just encountered the following MySQL error in my web app
Code:
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:
Code:
@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
Code:
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
Code:
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:
Code:
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.
Login
FAQ
Search