3

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?

Steve Kuo
  • 61,876
  • 75
  • 195
  • 257

5 Answers5

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
cherouvim
  • 31,725
  • 15
  • 104
  • 153
2

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.

toolkit
  • 49,809
  • 17
  • 109
  • 135
  • Without the joins then Hibernate will make n+1 queries causing very poor performance. See http://www.hibernate.org/118.html#A23 – Steve Kuo Mar 16 '09 at 16:40
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?

James L
  • 16,456
  • 10
  • 53
  • 70
  • The SQL query works just fine. The problem is that Hibernate is trying to read a result field that doesn't exist. – Steve Kuo Jan 23 '09 at 00:40
0

Are you aliasing all of your inner joins? And using those aliases? I have seen some really strange behavior with Hibernate when you try and use implicit aliasing in the SELECT clause to do stuff like this (extremely simplified example):

select p.address.state from person p

But if you explicitly declare all your aliases it works just fine. Like this:

select s from person p join p.address a join a.state s

... or even this:

select s from person p join p.address.state s
Matt Brock
  • 5,337
  • 1
  • 27
  • 26
0

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.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348