Community
Participate
Working Groups
If you have a named query that looks like this: SELECT r FROM RIGHTS r WHERE r.group NOT IN :groups Some DatabasePlatforms like Oracle have a known limitation how many arguments are allowed in the in clause. Please consider a generic workaround for this problem. One Solution could be to divide the list into smaller chunks and use an OR clause in between.
It would be great if EclipseLink could handle this automatically. On Oracle preferably by first writing the items into a temporary table instead of chunking into multiple queries.
Setting target and priority. See the following page for the meanings of these fields: http://wiki.eclipse.org/EclipseLink/Development/Bugs/Guidelines Community: Please vote for this bug if it is important to you. Votes are one of the main criteria we use to determine which bugs to fix next.
Created attachment 224001 [details] patch for this problem
I quite like the proposed patch as it is a very neat solution to the problem. I would however have expected the 1000 constant to be part of the OraclePlatform and the translateQueryStringForParameterizedIN() method to call session.getPlatform() to decide whether this "workaround" is required or not. For the future I think it would be interesting if it were possible to let the platform handle IN lists differently. The current solution has the problem that every query which has a different number of elements in the IN list results in a new distinct query which needs to be parsed and optimized. Also long IN lists can be inefficient on some platforms (e.g. H2). These platforms typically suggest to use an alternative approach (e.g. temporary tables as proposed in comment #1 or using ARRAYs on H2). Should I open a separate enhancement request for this?
We will take a look at this for our next patch release and consider the merits of doing this. Ultimately, we feel the application should handle this issue by either using a join or by doing it's own batching, but will consider this as it provides a more convenient way getting around the issue for some cases. Some comments: 1. This will have to be configurable and disabled by default 2. The default limit will have to be set per database platform 3. Even with this fix, eventually you will get to a max parameters limit 4. We likely would not augment a solution like this to execute multiple statements due to issues such as ordering
These are good news. But I don't agree with you opinion, the application should handle such an issue. It doesnt work if you use the query-by-example-mechanism in a generic way. I like query-by-example so much because its easy to use AND (!!) understand.
Deferring. 2.4.2 is closed. We have not determined another appropriate target.
Another implementation option I was thinking about would be to at some point delegate the building of these SQL query predicates to the DatabasePlatform (or query it for an object to perform this task). That would allow users to subclass classes like H2Platform and Oracle11Platform to implement smarter handling for IN LIST predicates. I have never really studied the EclipseLink code, so I don't really know if what I propose makes any sense or would be possible without breaking any API. FWIW, we did some tracing on Oracle of queries with such IN LIST predicates. Not only do we get a separate SQL query for every JPQL query execution with a distinct number of elements in the IN LIST (which of course uses resources of the shared pool and also requires a hard parse), also the Oracle parse time for such queries could be very high (we observed parse times of up to 10 seconds with 1000 elements in the list!). Unfortunately we have quite a few of these queries and would rather not translate them all to native SQL.
An alternative with EclipseLink 2.4 would probably be to use the new TABLE extension function (http://eclipse.org/eclipselink/documentation/2.4/jpa/extensions/j_table.htm). The approach for Oracle with the temporary table would then become more explicit in the user code, as a native INSERT query would first be used to fill the temporary table with the elements previously in the IN LIST. In a second step this table would then be joined in the JPQL query using the mentioned TABLE function. Or would it possibly even make sense to somehow map that temporary table using JPA and insert the elements using the EntityManager? I assume an advantage of the IN LIST predicate is that the EntityManager then has the ability to execute the query directly against the cache if the IN LIST predicate pertains to the queried entity's primary key, as in e.g. "select o from foo o where o.id in :list". Although I don't know if EclipseLink does that. For other platforms like H2 it would however still be nice if it were possible to customize the SQL generated for an IN LIST predicate.
Here is what I think would be the most helpful solution in our particular situation: If a JPQL query or a criteria API query has an IN-list predicate where the right-hand side is a variable, there would be the option (also provided the database platform supports this) to have this translated to an SQL predicate using a JDBC array parameter. So for example a JPQL query like: select obj from foo obj where bar in :bars ; where the "bars" variable is set to a value of type String[] could on Oracle be translated to the following SQL query: select obj.* from foo obj where exists (select null from table(?) where column_value = obj.bar) ; The OraclePlatform would be responsible for converting the String[] value to a corresponding JDBC array object. Using some dirty tricks (prefixSQL() and postfixSQL() for Criteria API queries and using the SQL() function in JPQL) we have been able to successfully employ this technique, but we would really prefer if EclipseLink had direct support for this. If there is any interest I would be willing to contribute a patch for this. But possibly someone can already say that this won't make it into EclipseLink because there is no demand or because it won't be possible to implement this in a backward compatible way. If not I would be grateful for any pointers and hints where to start.
We recently migrated off of a *really* old ORM (http://db.apache.org/ojb/) and encountered this issue. Sadly, our old and crusty ORM handled this beautifully by splitting the large IN into multiple smaller ones that were OR'd together. It sounds like there is some discussion on here as the whether or not this is even something that EclipseLink should be handling automatically. Fwiw, I would vote for "Yes". Is there any chance that a fix for this will make it into a future release?
After working a while with eclipselink and solving other problems (with other databases) I think there is no simple solution. We sometimes have a problem with the parameter count on MSSQL because the maximum parameters here are 2100. So only splitting the IN-clause into chunks of e.g. 1000 pieces and join them together with or doesn't solve this problem. One solution can be: EL must be able to split one JPQL- or Criteria-Query into many SQL-Statements which are executed one after the other and the result is joined together. But this doesn't solve anything. I'm not sure if EL can solve this problems (I also don't understand why Oracle or MSSQL or any other database have such restrictions).
Any news after a couple of years? Running into this issue during migration from openJPA, where it is solved since a long time, transparent for the user. Maybe the Apache openJPA concept can be ported to EclipseLink?
https://github.com/eclipse-ee4j/eclipselink/issues/628
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
