• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Max length of a query

 
Ranch Hand
Posts: 40
Eclipse IDE Chrome Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 814
Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 1210
25
Android Python PHP C++ Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 40
Eclipse IDE Chrome Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Bartender
Posts: 1210
25
Android Python PHP C++ Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic