JPA/Hibernate maximum number of joins?

Question

Is there a limit to the number of joins permitted in a JPA/Hibernate query?

Since Hibernate doesn't automatically join, I have to explicitly specify the joins in my JPA/Hibernate query. For example, person has an address, an address has a state. The following query retrieves person(s) with address and state fully loaded:

select p, a, s from person p left join p.address a left join a.state s where ...

As I keep adding joins, I eventually (after 12-13 left joins) reach a limit where Hibernate generates invalid SQL:

Caused by: java.sql.SQLException: Column 'something69_2_' not found.

I do have Hibernate's dialect set to my database implementation, MySQL:

<property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>

Is there an limit to the number joins Hibernate can handle in a single query?

Edit 1: The following is in the log file:

could not read column value from result set: something69_2_; Column 'something69_2_' not found.

However, something69_2_ doesn't appear in the SQL query. It's like Hibernate generated a SQL query and is expecting something69_2_ to be in the results, which is not.

Edit 2: Similar problem documented as an unfixed Hibernate bug HHH-3035

Edit 3: This is a documented Hibernate bug HHH-3636, which has been fixed but is not part of any release yet.

Edit 4: I built hibernate-core 3.3.2-SNAPSHOT which includes bug fix HHH-3636 and it did not address this problem.

Edit 5: The bug behavior seems to be triggered by multiple LEFT JOIN FETCH on ManyToMany or OneToMany relationships. One will work, two or three results in the bug.

Edit 6: Here's the stack trace:

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
        at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629)
        at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.loader.Loader.doList(Loader.java:2214)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
        at org.hibernate.loader.Loader.list(Loader.java:2090)
        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:388)
        at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
        at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
        at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
        at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:64)
        ... 69 more
Caused by: java.sql.SQLException: Column 'something69_2_' not found.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
        at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1136)
        at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2777)
        at org.hibernate.type.IntegerType.get(IntegerType.java:28)
        at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
        at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102)
        at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1088)
        at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:553)
        at org.hibernate.loader.Loader.doQuery(Loader.java:689)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
        at org.hibernate.loader.Loader.doList(Loader.java:2211)
        ... 77 more

Edit 7: The reason for all these joins is to avoid Hibernate doing n+1 queries, see Hibernate FAQ on How can I avoid n+1 SQL SELECT queries when running a Hibernate query?

Answer 1

There is nothing within the Hibernate code which limits the number of joins. This could be a bug in the dialect, or a limitation of the database engine. But my money's on a bug unrelated to the number of joins! Have you tried running the SQL directly in an interactive query session?

Answer 2

I've once hit the MySQL 5.0 61 table limit with Hibernate:

ERROR 1116 (HY000): Too many tables; MySQL can only use 61 tables in a join

Answer 3

Have you tried to execute with the actual jdbc driver in use? It might be a issue of the jdbc driver.

Although judging from the name of the column, it is looking for, I'd guess there is some trimming/construction of names going wrong. Definitely looks more like a bug than a intended limit.

Answer 4

The question is why are you trying to make such a complex query in the first place?

Have you considered different approaches? The documentation on improving performance makes some suggestions.