File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Max length of a query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Max length of a query" Watch "Max length of a query" New topic
Author

Max length of a query

Abhishek Ralhan
Ranch Hand

Joined: Aug 01, 2010
Posts: 40

Hello Ranchers,

I am using JPA. And in it I fire a query which comes out to be:
SELECT DISTINCT d FROM table_name d WHERE d.col1.col2.col3 in
(1003713,0001002750,0001024523,0001021484,0001010011,0001003496,0001028583,0001021690,0001002758,0001010034,0001034260,0001025623,0001002765,0001007371,0001022650,0001019684,0001007642,0001027533,0001017113,0001002127,0001018804,0001025417,0001029838,0001030677,0001002780,0001007495,0001025434,0001004385,0001002008,0001011199,0001029506,0001006463,0001006474,0001028575,0001011182,0001008273,0001004161,0001003489,0001006469,0001010440,0001008276,0001006464,0001030665,0001024326,0001024057,0001021230,0001007368,0001004393,0001007360,0001002731,0001026370,0001001113,0001025051,0001026368,0001011204,0001002830,0001012602,0001004101,0001002277,0001028275,0001015916,0001010586,0001030685,0001028501,0001007358,0001029825,0001030796,0001013175,0001002725,0001025645,0001009524,0001007359,0001010443,0001026372,0001001094,0001011522,0001025046,0001029264,0001027449,0001003493,0001001114,0001030879,0001012196,0001004798,0001002494,0001013843,0001013426,0001013634,0001025401,0001011508,0001011677,0001034747,0001025619,0001002493,0001011509,0001002774,0001028690,0001007370,0001004104,0001008578,0001008575,0001002769,0001026016,0001026322,0001019974,0001002495,0001023825,0001007367,0001017864,0001025063,0001029837,0001035223,0001013844,0001003715,0001002129,0001031126,0001035071,0001007373,0001024027,0001022647,0001021997,0001002772,0001019351,0001002019,0001002761,0001025954,0001010892,0001005025,0001022008,0001004102,0001006471,0001004108,0001009523,0001003495,0001029836,0001004805,0001010459,0001007782,0001004391,0001009036,0001007364,0001009398,0001011502,0001031110,0001008274,0001013176,0001011769,0001027234,0001002766,0001019355,0001016220,0001007365,0001028168,0001007106,0001025065,0001024154,0001008707,0001024558,0001029559,0001033631,0001002748,0001004629,0001003515,0001021998,0001021680,0001001982,0001021481,0001009040,0001008574,0001004389,0001002275,0001026815,0001024020,0001004804,0001025564,0001024025,0001023286,0001021482,0001006465,0001021461)
ORDER by d.col4 DESC

Now when this query is hit it throws a below exception:
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: 84 near line 1, column 126

Below is the stacktrace:

at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:93)
at org.jboss.ejb3.entity.TransactionScopedEntityManager.createQuery(TransactionScopedEntityManager.java:141)


The reason for this I think is that, may be, I am fitting too many values in the IN () function and it looks like JPA has initial size for the parameters or the queries, which is may be crossed.
Please suggest if this is the case, and what should I do to counter this kind of scenario.


-Abhishek
I came to this world on a Learner's License
Ninad Kulkarni
Ranch Hand

Joined: Aug 31, 2007
Posts: 802

You can obtain explain plan for following query and try to tune query as per your need
Query tuning is important than query length this my opinion


SCJP 5.0 - JavaRanch FAQ - Java Beginners FAQ - SCJP FAQ - SCJP Mock Tests - Tutorial - JavaSE7 - JavaEE6 -Generics FAQ - JLS - JVM Spec - Java FAQs - Smart Questions
Karthik Shiraly
Ranch Hand

Joined: Apr 04, 2009
Posts: 539
    
    7
This is just a guess, but I think the query plan AST implementation treats numbers that start with 0 as octals. Since 8 is not a valid digit in octal system, it treats it as invalid token.
If you notice, the first digit in that list of values that is not a valid octal digit is an 8 followed by 4 in the 4th value.
I searched for "hibernate octal" and got this bug report.

What's with all the 000s anyway - is it how they're stored in DB or as strings or something? If they're supposed to be decimal numbers, try this same query but with all the prefix 000s stripped out and see if it goes through.
Abhishek Ralhan
Ranch Hand

Joined: Aug 01, 2010
Posts: 40

Thanks Karthik Shiraly,

We get a list of these codes from another application through webservice request. And we hit the dB with the same list. This functionality is very old but very recently the other application sending codes with leading 0's.
And those leading 0's were the problem and I was not at all aware of this bug in Hibernate earlier.
Thanks very much for your inputs. It solved my one incident in Production
Karthik Shiraly
Ranch Hand

Joined: Apr 04, 2009
Posts: 539
    
    7
good to know it worked out.

I feel a query with many IN values is risky. If you're currently sending all values received through WS to db blindly, consider checking the number of elements and breaking them into batches.
There are limits on query size for all databases - typically it's the maximum size of the network packet that's sent from driver to db. You may inadvertently break that limit if there are no size checks.
And as Ninad points out, check the query explain plan and possible optimizations, keeping your use cases in mind. Perhaps it's not necessary to retrieve so many records, especially if they're being displayed to a user.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Max length of a query