-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 
Author Message
 Post subject: MySQL error; too many joins with Joined Inheritance model
PostPosted: Sat Jun 18, 2016 3:10 am 
Newbie

Joined: Sat Feb 27, 2016 1:47 am
Posts: 16
Cross posted to stack overflow for visibility http://stackoverflow.com/questions/3789 ... ance-model

I'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.


Top
 Profile  
 
 Post subject: Re: MySQL error; too many joins with Joined Inheritance model
PostPosted: Sat Jun 18, 2016 9:13 pm 
Newbie

Joined: Sat Feb 27, 2016 1:47 am
Posts: 16
JIRA created for this issue: https://hibernate.atlassian.net/browse/HSEARCH-2301

Thanks to Guillaume Smet for looking into it!


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
cron
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.