Stackoverflow with Hibernate using sql IN (id, id, id, id..id)
I am getting below error saying that a stack overflow has occurred. This is because a sql statement with IN (id, id, id ... id) has a ton of parameters. Is there anyway to fix this? This is happening in my local environment with Eclipse.
JPA
@Query(value="SELECT p FROM PendingCourseRegistration p WHERE p.sisId IN ?1 AND p.testId = ?2")
List<PendingCourseRegistration> findPendingCourseRegistrationInSisIdsAndTestId(List<String> sisIds, Long testID);
Mistake
java.lang.StackOverflowError: null
at java.lang.Abstract witingBuilder.append(AbstractStringBuilder.java:416) ~[na:1.7.0_17]
at java.lang.StringBuffer.append(StringBuffer.java:237) ~[na:1.7.0_17]
at antlr.BaseAST.toStringList(BaseAST.java:341) ~[antlr-2.7.7.jar:na]
at antlr.BaseAST.toStringList(BaseAST.java:347) ~[antlr-2.7.7.jar:na]
at antlr.BaseAST.toStringList(BaseAST.java:347) ~[antlr-2.7.7.jar:na]
at antlr.BaseAST.toStringList(BaseAST.java:347) ~[antlr-2.7.7.jar:na]
Sleep request
2:26.763 [ocPifScheduler-1] DEBUG o.s.o.j.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler - Creating new EntityManager for shared EntityManager invocation
09:52:26.788 [Scheduler-1] DEBUG org.hibernate.hql.internal.ast.QueryTranslatorImpl - parse() - HQL: SELECT p FROM com.test.PendingCourseRegistration p WHERE p.sisId IN (:x10_, :x11_, :x12_, :x13_, :x14_, :x15_, :x16_, :x17_, :x18_, :x19_, :x110_, :x111_, :x112_, :x113_, :x114_, :x115_, :x116_, :x117_, :x118_, :x119_, ...:xN) AND p.id = ?2
09:52:26.891 [Scheduler-1] DEBUG org.hibernate.hql.internal.ast.QueryTranslatorImpl - --- HQL AST ---
\-[QUERY] Node: 'query'
+-[SELECT_FROM] Node: 'SELECT_FROM'
| +-[FROM] Node: 'FROM'
| | \-[RANGE] Node: 'RANGE'
| | +-[DOT] Node: '.'
| | | +-[DOT] Node: '.'
| | | | +-[DOT] Node: '.'
| | | | | +-[DOT] Node: '.'
| | | | | | +-[DOT] Node: '.'
| | | | | | | +-[DOT] Node: '.'
| | | | | | | | +-[IDENT] Node: 'com'
| | | | \-[IDENT] Node: 'model'
| | | \-[IDENT] Node: 'PendingCourseRegistration'
| | \-[ALIAS] Node: 'p'
| \-[SELECT] Node: 'SELECT'
| \-[IDENT] Node: 'p'
\-[WHERE] Node: 'WHERE'
\-[AND] Node: 'AND'
+-[IN] Node: 'in'
| +-[DOT] Node: '.'
| | +-[IDENT] Node: 'p'
| | \-[IDENT] Node: 'sisId'
| \-[IN_LIST] Node: 'inList'
| +-[COLON] Node: ':'
| | \-[IDENT] Node: 'x10_'
| +-[COLON] Node: ':'
| | \-[IDENT] Node: 'x11_'
| +-[COLON] Node: ':'
| | \-[IDENT] Node: 'x12_'
| +-[COLON] Node: ':'
| | \-[IDENT] Node: 'x13_'
| +-[COLON] Node: ':'
| | \-[IDENT] Node: 'x14_'
| +-[COLON] Node: ':'
| | \-[IDENT] Node: 'x15_'
| +-[COLON] Node: ':'
| | \-[IDENT] Node: 'x16_'
| +-[COLON] Node: ':'
| | \-[IDENT] Node: 'x17_'
source to share
In our Grails project (2.3.6) with Hibernate 4.3.1.Final, we never had this error, but we ran into another error due to the request buffer size limitation :
Since you are using in (?, ..., ?)
, you write as much ?,
as you have items in your list, which means for a large list (says 50,000) you can write a query of 100,000 characters and you can have this exception (here with pgSQL drivers) :
Foobar.executeQuery("select f from Foobar f where f.id in (:ids)",
[ids: 1L..100000L]); // Groovy way of creating a list of 100000 items.
And the error:
SqlExceptionHelper:146 - An I/O error occured while sending to the backend.
SqlExceptionHelper:146 - This connection has been closed.
That's why I think you might need
-
Either split your list of ids into a smaller list (like 500 items) and copy the result manually, which is more work than the standard spring data you are using.
-
Or, save the list of ids to a temporary table (which can be a painful task using JPA). The temporary table will be a tuple (key, sids). You have to generate a temporary key (for session), batch insert ids into this table with key, reset to hibernate change to database, use this table with subquery (
p.sidIds in (select sisIds from IdTable where key = ?1)
), delete data from this table. While painful, it can lead to increased productivity.
source to share
I could not replicate this using Hibernate 4.2.4 Can you provide the version of Hibernate you are using? Another thing I recommend is to try to put a breakpoint on "antlr.BaseAST.toStringList ()" when you execute your method and see the runtime variables and the method calling the antlr method and calls the loop
Hope it helps
source to share