StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POjava.lang.StackOverflowError from OpenJPA CriteriaQuery using large queries
text
Body
copied!<p><strong>Overview</strong></p> <p>I'm trying to use an OpenJPA CriteriaQuery to count the number of objects in a DB table that meet certain properties. </p> <p>I need to be able to apply a filter - to count the number of objects that meet the certain properties only if they have an ID in a known list of IDs. </p> <p>I am not able to do this if the list is very large. </p> <p><strong>Detailed description</strong></p> <p>I'm using OpenJPA 2.2.1 with an underlying Derby DB. </p> <p>My CriteriaQuery is being mapped to an SQL query with a WHERE clause that includes <code>t1.qid = ? OR t1.qid = ? OR t1.qid = ? ...</code> for each of the known IDs. </p> <p>This normally works fine. </p> <p>If this list gets large (e.g. a thousand or more IDs in the list), this fails with a java.lang.StackOverflowError. </p> <p><strong>Questions</strong></p> <ul> <li><p>Is there a better way to apply a filter that can scale to any arbitrary number of object IDs? </p></li> <li><p>Is there a way to avoid this error?</p></li> </ul> <p><em>I've included a simplified version of my code below, together with OpenJPA exceptions and derby.log errors that resulted.</em> </p> <hr> <p>A simplified version of my code:</p> <pre><code>Collection<Integer> qids = A_LARGE_SET_OF_INTEGERS; // prepare a criteria query to count objects that match some parameters CriteriaQuery<Long> criteria = builder.createQuery(Long.class); Root<MyObjectType> myrootobj = criteria.from(MyObjectType.class); // counting the number of qids - a nonunique ID value in MyObjectType criteria.select(builder.countDistinct(myrootobj.get(MyObjectType_.qid))); // prepare a filter based on a property of the object Predicate someProperty = builder.equal(myrootobj.get(MyObjectType_.someattr).get(MyOtherObjectType_.id), somefilterid); // prepare a filter to limit to objects with an ID in the provided set Predicate filteredObjects = myrootobj.get(MyObjectType_.qid).in(qids); // apply the filter criteria.where(builder.and(someProperty, filteredObjects)); long count = em.createQuery(criteria).getSingleResult(); </code></pre> <hr> <p>The only SQL exception errors I can see are SQLState:XJ001 Error Code:-1</p> <p>The exception thrown looks like this:</p> <pre><code> <openjpa-2.2.1-r422266:1396819 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: Failed to execute query "null". Check the query syntax for correctness. See nested exception for details. at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:872) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:794) at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:542) at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:286) at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:302) at org.apache.openjpa.persistence.QueryImpl.getSingleResult(QueryImpl.java:330) at my.code.that.uses.CriteriaQuery at sun.reflect.GeneratedMethodAccessor335.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:55) at java.lang.reflect.Method.invoke(Method.java:613) at ... Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ001, SQLERRMC: java.lang.StackOverflowErrorXJ001.U {SELECT COUNT(DISTINCT t1.id) FROM SomeObjectType t0 INNER JOIN Something t1 ON t0.SOMETHING_ID = t1.id WHERE (t0.attr = ? AND t1.RUN_ID = ? AND (t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ?) AND t1.qid IS NOT NULL)} [code=-1, state=XJ001] at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:219) at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:199) at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$000(LoggingConnectionDecorator.java:59) at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:251) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:133) at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:140) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:133) at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1646) at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:122) at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:449) at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:429) at org.apache.openjpa.jdbc.sql.SelectImpl.prepareStatement(SelectImpl.java:479) at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:420) at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:391) at org.apache.openjpa.jdbc.sql.LogicalUnion$UnionSelect.execute(LogicalUnion.java:427) at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:230) at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:220) at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:94) at org.apache.openjpa.kernel.QueryImpl$PackingResultObjectProvider.open(QueryImpl.java:2070) at org.apache.openjpa.kernel.QueryImpl.singleResult(QueryImpl.java:1320) at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1242) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:1007) at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:863) ... 75 more </code></pre> <p><em>Note that I've snipped the SQL query in the ReportingSQLException because it was very long - I removed hundreds of the <code>OR t1.qid = ?</code> clauses. There were over a thousand of them.</em></p> <hr> <p>derby.log contains the following:</p> <pre><code>2013-06-30 01:03:16.279 GMT Thread[DRDAConnThread_36,5,main] (XID = 11562784), (SESSIONID = 23), (DATABASE = /full/path/to/my/db), (DRDAID = NF000001.CE86-507216535478407432{22}), Failed Statement is: SELECT COUNT(DISTINCT t1.id) FROM SomeObjectType t0 INNER JOIN Something t1 ON t0.SOMETHING_ID = t1.id WHERE (t0.attr = ? AND t1.RUN_ID = ? AND (t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ? OR t1.qid = ?) AND t1.qid IS NOT NULL) java.lang.StackOverflowError at org.apache.derby.impl.sql.compile.OrNode.changeToCNF(Unknown Source) at org.apache.derby.impl.sql.compile.OrNode.changeToCNF(Unknown Source) <snip - over a thousand more lines like these> at org.apache.derby.impl.sql.compile.OrNode.changeToCNF(Unknown Source) at org.apache.derby.impl.sql.compile.OrNode.changeToCNF(Unknown Source) at org.apache.derby.impl.sql.compile.OrNode.changeToCNF(Unknown Source) at org.apache.derby.impl.sql.compile.OrNode.changeToCNF(Unknown Source) at org.apache.derby.impl.sql.compile.OrNode.changeToCNF(Unknown Source) at org.apache.derby.impl.sql.compile.AndNode.changeToCNF(Unknown Source) at org.apache.derby.impl.sql.compile.AndNode.changeToCNF(Unknown Source) at org.apache.derby.impl.sql.compile.AndNode.changeToCNF(Unknown Source) at org.apache.derby.impl.sql.compile.AndNode.changeToCNF(Unknown Source) at org.apache.derby.impl.sql.compile.AndNode.changeToCNF(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.normExpressions(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.preprocess(Unknown Source) at org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unknown Source) at org.apache.derby.impl.sql.compile.CursorNode.optimizeStatement(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source) at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.apache.derby.impl.drda.DRDAStatement.prepare(Unknown Source) at org.apache.derby.impl.drda.DRDAStatement.explicitPrepare(Unknown Source) at org.apache.derby.impl.drda.DRDAConnThread.parsePRPSQLSTT(Unknown Source) at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown Source) at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload