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
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:
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.
I came to this world on a Learner's License
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.
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
Joined: Apr 04, 2009
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.