Bugzilla – Full Text Bug Listing
| Summary: | Using criteria.in(…) with ParameterExpression of type Collection creates invalid SQL | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Product: | z_Archived | Reporter: | Oliver Drotbohm <odrotbohm> | ||||||||||
| Component: | Eclipselink | Assignee: | Nobody - feel free to take it <nobody> | ||||||||||
| Status: | RESOLVED FIXED | QA Contact: | |||||||||||
| Severity: | normal | ||||||||||||
| Priority: | P2 | CC: | amd2002a, dazeydev.3, dominik.grupp, eclipselink.orm-inbox, fenik17, kenp21, knut.wannheden, leo, linket, lukas.jungmann, mail.twerner, mauromol, nath, omidatbiz, orangelumpycustard, pfurbacher, philippn, piotr.pejas, privatejava, swen, ted, tom.ware, webmaster | ||||||||||
| Version: | unspecified | ||||||||||||
| Target Milestone: | --- | ||||||||||||
| Hardware: | All | ||||||||||||
| OS: | All | ||||||||||||
| See Also: |
https://bugs.eclipse.org/bugs/show_bug.cgi?id=349728 https://github.com/eclipse-ee4j/eclipselink/pull/9 |
||||||||||||
| Whiteboard: | hsql h2 | ||||||||||||
| Attachments: |
|
||||||||||||
We have a test that does the following:
CriteriaBuilder qb = em.getCriteriaBuilder();
CriteriaQuery<Employee> cq = qb.createQuery(Employee.class);
cq.where(cq.from(Employee.class).get("id").in(qb.parameter(List.class, "result")));
List result = em.createQuery(cq).setParameter("result", expectedResultList).getResultList();
Does writing your query in that type of format help?
(In reply to comment #1) Thanks for the hint Tom. It brought me on the right track. It seems to boil down to an HSQL SQL issue. It simply doesn't seem to be able to cope with the double parentheses ((?,?)). Using H2 both Collection and List just work fine. So we actually have two issues here: 1. SQL generated for Collection and List does generate valid SQL for HSQL. 2. Arrays should be supported. Just let me know if I should rather open a separate issue for one of the above. Thanks! Please file a 2nd bug and indicate which of the two bugs this issue represents in this posting. I've checked the test case and it seams to cover hand in single element collections only. Speaking of the example I posted initially it works fine if I just pipe in a one-argument list (e.g. Arrays.asList("Dave)). If I add up multiple values (e.g. Arrays.asList("Dave", "Carter")) the query does not return any result. I've tried diving into the sources and find the place where the collection elements are bound to the query but haven't been sucessful unfortunately.
Is there a chance you point me to that place?
That observation might actually relate to my initial observation that arrays are bound as single object rather than their elements. See the linked ticket. Take a look at org.eclipse.persistence.internal.expressions.ExpressionSQLPrinter There are a bunch of methods in that class for printing parts of the expression to SQL. A good start is the printParameter(ParameterExpression expression) method. Yet another thank you for the hint. It helped to find the root cause of the problem again. It seems the issue actually boils down to the same one already discovered with HSQL but resulting in a different invalid behavior. I tried running the EclipseLink generated query against H2 and it works for a single element but not for multiple ones: SELECT ID FROM USER WHERE (FIRSTNAME IN ((?))) -> bind 'Dave' returns expected result SELECT ID FROM USER WHERE (FIRSTNAME IN ((?, ?))) -> bind 'Dave', 'Carter' returns empty result SELECT ID FROM USER WHERE (FIRSTNAME IN (?,?)) -> bind 'Dave' returns expected result So to sum up: IN queries generate invalid SQL for H2 and HSQL, where H2 blows up with an exception whereas HSQL silently returns no results as all. 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. I just ran into the same problem today. But I don't understand where the HSQL and H2 DB specificness comes in. From what I can tell the problem is that the prepare() of the query will end up in org.eclipse.persistence.internal.expressions.ExpressionSQLPrinter.printValuelist(Collection) which ends up appending "(?)" to the query. When the query then is executed the '?' will be "bound" in org.eclipse.persistence.internal.databaseaccess.DatabaseCall.translateQueryStringForParameterizedIN(AbstractRecord, AbstractRecord, AbstractSession) which writes the value as e.g. "(?,?,?)". I ran into the same problem on Derby. But since I can't see how this would be any different when on Oracle, I assume I would have the same problem there, as Oracle doesn't understand a predicate like "a in ((?,?))" either. I would be very interested in any workarounds for this problem. The workarounds I am aware of, but which may not always be applicable: 1. Use JPQL. In JPQL a predicate like "firstname in :param" works as expected. 2. Use Criteria API without ParameterExpression. Use the overloaded in(Collection) method instead of in(Expression) and pass in the collection directly instead of using the ParameterExpression indirection. In my case I would really like to use the Criteria API *with* a ParameterExpression. The reason is that the query is constructed dynamically and looks differently depending on input parameters (therefore workaround 1 is not really suitable) and the query is also performance critical and shouldn't always result in a new SQL query which needs parsing and a new execution plan. Does anyone know of other workarounds? When I initially encountered this problem, I thought it might be caused by something in Spring Data JPA. I was using EclipseLink 2.3.x and MySQL. I upgraded to EclipseLink 2.4.x; the problem remained. I switched the JPA provider to Hibernate; the problem went away. I ran the generated query (the SQL generated by EclipseLink, I presume) that was in the stack trace. MySQL choked on the extra set of parens around the parameter list. (It generates a slightly different error message, but the cause is the same.) I then reconfigured my unit tests to use the following combinations: 1. EclipseLink 2.4.x and PostgreSQL 9.2 2. Hibernate 4.x and PostgreSQL 9.2 The extra pair of parens generated by EclipseLink caused PostgreSQL to choke as well. The second combination worked just fine -- no extra parens. Hibernate's implementation of whatever is creating the raw SQL appears to work, whereas the equivalent in EclipseLink does not. This issue is nearly 2 years old. Isn't it about time it got some real, code-level attention? We will do some additional triage for 2.4.2 In above comment... triage is for 2.4.3 (not 2.4.2, which is essentially closed) this bug also exists in Postgresql. I'm wondering why eclipselink puts '(' and ')' on every expression ? I realized this bug is caused by printList(Collection values) method in ExpressionSQlPrint.java or printSQL(ExpressionSQLPrinter printer) method in CompoundExpression.java is it ok if I remove one of those parentheses ?
Created attachment 231209 [details]
remove parentheses from In query when parameter is List or Collection
(In reply to comment #14) > ... I realized this bug is caused by > printList(Collection values) method in ExpressionSQlPrint.java or > printSQL(ExpressionSQLPrinter printer) method in CompoundExpression.java .... It took a long time to step through the process of creating the SQL from the JPQL, and I have come to the conclusion that the problem is not necessarily in ExpressionSQLPrint. It could be in DatabaseCall.translateQueryStringForParameterizedIN. It's a matter of design as to which is responsible.) For background, the JPQL I'm starting with at EJBQueryImpl<X>.buildEJBQLDatabaseQuery(String jpql, AbstractSession session) is select m from Member m where m.lastname IN ?1 The actual parameter values are in a List<String>. Here's a small portion of the stack leading up to DatabaseCall.translateQueryStringForParameterizedIN(); it starts at the point where execution enters org.eclipse.persistence classes: SQLCall(DatabaseCall).translate(AbstractRecord, AbstractRecord, AbstractSession) line: 1064 ExpressionQueryMechanism(DatasourceCallQueryMechanism).executeCall(DatasourceCall) line: 206 ExpressionQueryMechanism(DatasourceCallQueryMechanism).executeCall() line: 193 ExpressionQueryMechanism(DatasourceCallQueryMechanism).executeSelectCall() line: 264 ExpressionQueryMechanism(DatasourceCallQueryMechanism).selectAllRows() line: 648 ExpressionQueryMechanism.selectAllRowsFromTable() line: 2706 ExpressionQueryMechanism.selectAllRows() line: 2659 ReadAllQuery.executeObjectLevelReadQuery() line: 421 ReadAllQuery(ObjectLevelReadQuery).executeDatabaseQuery() line: 1150 ReadAllQuery(DatabaseQuery).execute(AbstractSession, AbstractRecord) line: 852 ReadAllQuery(ObjectLevelReadQuery).execute(AbstractSession, AbstractRecord) line: 1109 ReadAllQuery.execute(AbstractSession, AbstractRecord) line: 393 ReadAllQuery(ObjectLevelReadQuery).executeInUnitOfWork(UnitOfWorkImpl, AbstractRecord) line: 1197 RepeatableWriteUnitOfWork(UnitOfWorkImpl).internalExecuteQuery(DatabaseQuery, AbstractRecord) line: 2875 RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, AbstractRecord, int) line: 1602 RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, AbstractRecord) line: 1584 RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, List) line: 1549 EJBQueryImpl<X>(QueryImpl).executeReadQuery() line: 231 EJBQueryImpl<X>(QueryImpl).getResultList() line: 411 ... Here are some important lines in the translation of the query: DatabaseCall.java public void translateQueryStringForParameterizedIN(...) ... String queryString = getQueryString(); ... This gives us queryString as "SELECT ID, EMAILADDRESS, firstname, JOINED, LASTNAME, LASTPAID FROM member WHERE (LASTNAME IN (?))". Note the single set of parens around "?". Now, later in the same method, ... if (parameter instanceof Collection) ... If this is true (which is the case we are discussing in this bug report), an additional opening paren is added at line 1101 (in 2.4.1.v20121003-ad44345). At line 1132, the closing paren is written. The result of the translation is this: SELECT ID, EMAILADDRESS, firstname, JOINED, LASTNAME, LASTPAID FROM member WHERE (LASTNAME IN ((?,?,?))) I suspect that this is the source of the formatting problem. However, I'm thinking that it's really a question of which object should be responsible for writing the parens in this case. Should it be the IN expression operator which calls CollectionExpression.printSQL(), which calls ExpressionSQLPrinter.printList()? Or should it be the DatabaseCall which translates the IN expression? Without knowing the intent of the EclipseLink designers, I cannot say for sure. Just a bit more documentation of how the IN expression is generated.
The background is the same as in my previous comment:
JPQL: select m from Member m where m.lastname IN ?1
parameter 1: List<String>
The stack from the point where Spring hands off the org.eclipselink.persistence classes:
ExpressionSQLPrinter.printList(Collection) line: 224
CollectionExpression.printSQL(ExpressionSQLPrinter) line: 37
ExpressionOperator.printDuo(Expression, Expression, ExpressionSQLPrinter) line: 2239
RelationExpression(CompoundExpression).printSQL(ExpressionSQLPrinter) line: 278
RelationExpression.printSQL(ExpressionSQLPrinter) line: 868
ExpressionSQLPrinter.translateExpression(Expression) line: 306
ExpressionSQLPrinter.printExpression(Expression) line: 129
SQLSelectStatement.printSQL(ExpressionSQLPrinter) line: 1588
MySQLPlatform(DatabasePlatform).printSQLSelectStatement(DatabaseCall, ExpressionSQLPrinter, SQLSelectStatement) line: 2991
MySQLPlatform.printSQLSelectStatement(DatabaseCall, ExpressionSQLPrinter, SQLSelectStatement) line: 682
SQLSelectStatement.buildCall(AbstractSession, DatabaseQuery) line: 785
SQLSelectStatement.buildCall(AbstractSession) line: 795
RelationalDescriptor(ClassDescriptor).buildCallFromStatement(SQLStatement, DatabaseQuery, AbstractSession) line: 805
ExpressionQueryMechanism(StatementQueryMechanism).setCallFromStatement() line: 387
ExpressionQueryMechanism(StatementQueryMechanism).prepareSelectAllRows() line: 312
ExpressionQueryMechanism.prepareSelectAllRows() line: 1701
ReadAllQuery.prepareSelectAllRows() line: 721
ReadAllQuery.prepare() line: 657
ReadAllQuery(DatabaseQuery).checkPrepare(AbstractSession, AbstractRecord, boolean) line: 614
ReadAllQuery(ObjectLevelReadQuery).checkPrepare(AbstractSession, AbstractRecord, boolean) line: 883
ReadAllQuery(DatabaseQuery).checkPrepare(AbstractSession, AbstractRecord) line: 575
ReadAllQuery(DatabaseQuery).execute(AbstractSession, AbstractRecord) line: 820
ReadAllQuery(ObjectLevelReadQuery).execute(AbstractSession, AbstractRecord) line: 1109
ReadAllQuery.execute(AbstractSession, AbstractRecord) line: 393
ReadAllQuery(ObjectLevelReadQuery).executeInUnitOfWork(UnitOfWorkImpl, AbstractRecord) line: 1197
RepeatableWriteUnitOfWork(UnitOfWorkImpl).internalExecuteQuery(DatabaseQuery, AbstractRecord) line: 2875
RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, AbstractRecord, int) line: 1602
RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, AbstractRecord) line: 1584
RepeatableWriteUnitOfWork(AbstractSession).executeQuery(DatabaseQuery, List) line: 1549
EJBQueryImpl<X>(QueryImpl).executeReadQuery() line: 231
EJBQueryImpl<X>(QueryImpl).getResultList() line: 411
... Spring classes hand off to eclipselink here ...
At the point which the code enters "ExpressionSQLPrinter.printList()", "writer.toString()" returns
SELECT ID, EMAILADDRESS, firstname, JOINED, LASTNAME, LASTPAID FROM member WHERE (LASTNAME IN
The method iterates through the Collection values passed in, and prints a marker ("?") for each value. In this case, it is only one "?".
Upon exit from the method, it adds a closing paren. At this point, "writer.toString()" returns
SELECT ID, EMAILADDRESS, firstname, JOINED, LASTNAME, LASTPAID FROM member WHERE (LASTNAME IN (?)
At
RelationExpression(CompoundExpression).printSQL(ExpressionSQLPrinter) line: 279
the closing paren is added, so that "writer.toString()" returns
SELECT ID, EMAILADDRESS, firstname, JOINED, LASTNAME, LASTPAID FROM member WHERE (LASTNAME IN (?))
From here, execution leads to the writing of the actual parameter values as described in my previous comment. It is there that an additional set of parens around the parameters is added.
I hope these details help speed along a resolution to this problem.
Paul, Have you applied my patch ? I'm working with Postgresql and it works for me. I had it tested with a namedQuery. select d from Document d where d.id IN :ids and CriteriaBuilder predicateList.add(cb.in(root.get(field.getName()).get(prop)).value(pe.as(List.class))); I haven't applied your patch, and I don't doubt that it works in your use case. Indeed, you did some good sleuthing to find "ExpressionSQLPrinter.printList()". That prompted me to dig into the code once again. (The first time I tried it several months ago, I came up empty handed.) Thanks for your efforts. So, my purpose wasn't to dispute that your changes would work. Rather, my effort was to track down all places at which parentheses are added around the parameter list. I think it's important to know all the points in the code, and then to know what the design logic or intent is: i.e., which object should be responsible for writing these parens. There are probably a good number of use cases, and the design logic should cover all these. So, while patching ExpressionSQLPrinter may cover one case, or a few, does it cover all cases? Unit testing that covers all use cases needs to be done. C'mon this is a real issue with EclipseLink. One cannot use a collection in a IN predicate? It's like having a Ferrari but one two gears, the rest of them broken. And EclipseLink is a vehicle that has been broken for many years! For me, I cannot patch the EclipseLink code with the Java Instrumentation API (requires JVM parameters I cannot use in production), nor can I compile a new EclipseLink JAR, we have to use what is supplied from a repository. I tried my ways during run time using the Java Reflection API, but I ran into too many problems to manage. Anyone have an idea of how one can fix EclipseLink during run time - or at all? FYI: This is scheduled to be fixed in version 2.4.3, our next patch. I will look into a fix shortly, but I believe the problem is not in foundation code, but in the ExpressionImpl.in implementations for Criteria Api, and the method signature forced on it: (Expression<?>... values). The code iterates over the values and adds each to another collection which it then passes to the EclipseLink expression framework in function. This differs from JPQL and how the expression framework handle in parameters - if there is only a single parameter expression, it should be passed in directly instead of wrapped in a collection. This causes the CollectionExpression to be used, so the parameter is really a collection within a collection, which is what is printed off in the SQL "((?,?))" instead of a single collection "(?,?)". Created attachment 233327 [details]
proposed fix and test case
Fix checked into master (2.6), 2.5.1 and 2.4.3 Created attachment 238129 [details]
Sort of Testcase showing problem still exists in 2.5.1
Unfortunately, I'm still seeing this problem with 2.5.1. Please see attached testprogram
Environment:
JPA Provider: EclipseLink 2.5.1
Database: Oracle Version: Oracle Database 10g Release 10.2.0.1.0 - Production
Driver: Oracle JDBC driver Version: 11.2.0.3.0
Followup to my previous post: - please edito the META-INF/persistence.xml file to reproduce the problem. - I'm testing with an Oracle database, and it throws an ORA-00907: Missing right parenthesis - The erroneous SQL being generated by EclipseLink is: SELECT configkey, value FROM project_config WHERE (configkey IN ((?,?,?))) Phillip, the attached test case is using:
Predicate p = cb.in(e.get("key")).value(cb.parameter(Iterable.class, "ids"));
As mentioned in the bug, calling value(cb.parameter(Iterable.class, "ids")); adds the parameter to the collection of values to be passed to the IN expression. So you are creating a collection wrapped in a collection, which results in ((?,?,?)). If you want this to work, you need to pass in the parameter as the list of values:
e.get("key").in(cb.parameter(Iterable.class, "ids"))
I have not run or verified your test case, but the API mentioned is the only one that is supported, and is run in the build tests added for the fix.
Hi, unfortunately I am encountering this bug as well with eclipselink 2.5.1 and oracle 11g. criteriaQuery.where(root.get(columnName).in(criteriaBuilder.parameter(List.class,parameterName))); javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLException: ORA-00907: missing right parenthesis Error Code: 907 Call: SELECT DISTINCT PERMISSIONID, ACTIONS, "CLASS", DESCRIPTION, NAME FROM T_PERMISSIONS WHERE (ACTIONS IN ((?,?))) bind => [test1, test2] Query: ReadAllQuery(referenceClass=Permission sql="SELECT DISTINCT PERMISSIONID, ACTIONS, "CLASS", DESCRIPTION, NAME FROM T_PERMISSIONS WHERE (ACTIONS IN (?))") best regards, thomas The nightly tests show that test testInParameterCollection added with the patch is run successfully in the 2.5.2 stream, and the source code for 2.5.1 shows the fix is there. The test uses:
ParameterExpression pe = qbuilder.parameter(java.util.Collection.class, "param");
cquery.where(emp.join("responsibilities").in(pe));
List<Employee> result = em.createQuery(cquery).setParameter("param",respons).getResultList();
So it could be Expression<List> is matching to Expression<?>.. somehow instead of Expression<Collection>.
One quick check that would indicate the problem is to check the class type of the object returned from in(..).
ie call root.get(columnName).in(criteriaBuilder.parameter(List.class,parameterName)).getClass(). If it is a CompoundExpressionImpl, please file a new bug to look into why the "Predicate in(Expression<?>... values) method is being used and to add code to possibly handle a single parameter expression being passed into it. Can you print off the class of the object returned, and also test if using Collection.class instead of List.class works?
I think the test case you uses is applying quite of a hack to the way the Criteria API is used. Let me summarize for the ones lost in the conversation. If you use the Criteria API as intended… ParameterExpression<Collection> param = builder.param(Collection.class, "name"); you cannot hand this parameter to path.in(…) as this effectively binds to ….in(Expression<?>). You have to apply some Jedi generics skills to realize that you rather have to fallback to raw types like this: ParameterExpression param = builder.param(Collection.class, "name"); path.in((ExpressionCollection<?>) param); While I perfectly realize that this is another really subtle cool feature of the criteria API, I wonder if you really want to rely on a developer knowing and finding out about this or rather simply add the necessary tweaks to in(Expression<?>) to gracefully handle the scenario. However, trying to apply that hack to the Spring Data code base I still run into exceptions: Caused by: javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: unexpected token: ) Error Code: -5581 Call: SELECT ID, DTYPE, ACTIVE, AGE, CREATEDAT, EMAILADDRESS, FIRSTNAME, LASTNAME, CITY, COUNTRY, STREETNAME, STREETNO, MANAGER_ID FROM USER WHERE (ID IN ((?,?))) bind => [2 parameters bound] Query: ReadAllQuery(referenceClass=User sql="SELECT ID, DTYPE, ACTIVE, AGE, CREATEDAT, EMAILADDRESS, FIRSTNAME, LASTNAME, CITY, COUNTRY, STREETNAME, STREETNO, MANAGER_ID FROM USER WHERE (ID IN (?))") We're using HSQLDB for tests. I think the test case you uses is applying quite of a hack to the way the Criteria API is used. Let me summarize for the ones lost in the conversation. If you use the Criteria API as intended… ParameterExpression<Collection> param = builder.param(Collection.class, "name"); you cannot hand this parameter to path.in(…) as this effectively binds to ….in(Expression<?>). You have to apply some Jedi generics skills to realize that you rather have to fallback to raw types like this: ParameterExpression param = builder.param(Collection.class, "name"); path.in((ExpressionCollection<?>) param); While I perfectly realize that this is another really subtle cool feature of the criteria API, I wonder if you really want to rely on a developer knowing and finding out about this or rather simply add the necessary tweaks to in(Expression<?>) to gracefully handle the scenario. However, trying to apply that hack to the Spring Data code base I still run into exceptions: Caused by: javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: unexpected token: ) Error Code: -5581 Call: SELECT ID, DTYPE, ACTIVE, AGE, CREATEDAT, EMAILADDRESS, FIRSTNAME, LASTNAME, CITY, COUNTRY, STREETNAME, STREETNO, MANAGER_ID FROM USER WHERE (ID IN ((?,?))) bind => [2 parameters bound] Query: ReadAllQuery(referenceClass=User sql="SELECT ID, DTYPE, ACTIVE, AGE, CREATEDAT, EMAILADDRESS, FIRSTNAME, LASTNAME, CITY, COUNTRY, STREETNAME, STREETNO, MANAGER_ID FROM USER WHERE (ID IN (?))") We're using HSQLDB for tests. To verify this: - git clone https://github.com/spring-projects/spring-data-jpa.git - cd spring-data-jpa - open up e.g. EclipseLinkNamespaceUserRepositoryTests and remove the redeclared methods annotated with links to this ticket (349477) - run mvn clean test - see the test methods fail. This problem is still present in EclipseLink 2.6.0. No activity on this bug report since January 2014?? :-( This problem is preventing any Spring Data query method with an IN clause from working (at least with MySql), whichever is the type used for the IN operand (Collection, array...). The workaround I found was to annotate the query method with a redundant @Query annotation which does not put parenthesis around the IN operand, for instance:
long countByIdentityAndStateIn(Identity i, Collection<State> states);
becomes:
@Query("SELECT COUNT(e) FROM MyEntity e WHERE e.identity = ?1 AND e.state IN ?2")
long countByIdentityAndStateIn(Identity i, Collection<State> states);
Running into this bug in the same way as Mauro Molinari; using the JPQL workaround as well. No activity for over a year? Surprising given that Oliver provided a test case - that seemed to end the discussion :/. Voted! Over a year ago, I fixed an issue as described, allowing collection parameters to work in queries using criteria expressions. I did not set about fixing issues with generics, as I did not see this issue in my testing. I could only guess that these issues arise because the parameter is being sent to method Predicate in(Expression<?>... values) instead of Predicate in(Expression<Collection<?>> values) As has been pointed out, this is a result of the finer details of generics and has many workarounds. I suggested a new bug be filed, but instead everyone decide it is better to reopen this one, which I was not working on and no longer looking into. This is open source, so that should be expected, but developers do come and go to projects. Anyone is more then welcome to look into this issue and file a fix - the place to start looking is the org.eclipse.persistence.internal.jpa.querydef.ExpressionImpl's Predicate in(Expression<?>... values) method. Since ParameterExpression pe = qbuilder.parameter(java.util.Collection.class, "param") works, and only ParameterExpression<Collection> pe = qbuilder.parameter(java.util.Collection.class, "param") is causing a problem, then it is likely that ParameterExpression<Collection<String>> pe = qbuilder.parameter(java.util.Collection.class, "param") Would work as well, and seems to be what the specification really intended when they created the Expression.in(Expression<Collection<?>>) method signature. I will leave this bug in a 'reopened' state and have assigned it back to the generic inbox so that everyone can tell I am not actively working on it. Thanks for the feedback Chris; I'll have a look. I can't believe even after 4 years this bug still hasn't fixed yet, either eclipselink is a dead project or no one using it. Am still very much using it.
And am still waiting for this bug to be fixed. I have to just write it in JPQL myself @Query("SELECT e FROM entity e WHERE e.field in ?1")
Could someone from the project please comment on whether that bug will be fixed and if so, when? The same problem exists with Oracle. Still persists ==> when will there be an update? AFAICT last feedback was some 2.5 years ago and before that another 1.5 years. I hope that doesn’t mean the project is dead. I'd rather ask what this means for the TCK because Eclipselink is the reference implementation and there's obviously no test case checking that this actually works. I am still waiting for this bug to be fixed. I fixed the issue for which there was test case, so to me the bug is closed and I'm no longer looking into it. In the years since, others have reopened it with what is a different issue relating to how java handles generics but not one person actually provided the requested additional information. Source code is there, a description of what I suspected might be happening is there too, so if anyone really needs a fix, they are able to make it or suggest the change that would be required and I'm sure others would make sure the fix got in. The original example still fails with the same exception described in 2011:
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.4.v20160829-44060b6): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: unexpected token: ) in statement [SELECT ID, EMAILADDRESS, FIRSTNAME, LASTNAME FROM Foo WHERE (FIRSTNAME IN ((?,?)))]
Error Code: -5581
Call: SELECT ID, EMAILADDRESS, FIRSTNAME, LASTNAME FROM Foo WHERE (FIRSTNAME IN ((?,?)))
bind => [Dave, Carter]
Query: ReadAllQuery(referenceClass=MyUser sql="SELECT ID, EMAILADDRESS, FIRSTNAME, LASTNAME FROM Foo WHERE (FIRSTNAME IN (?))")
at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:382)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:473)
at example.springdata.jpa.eclipselink.Issue349477IntegrationTest.testname(Issue349477IntegrationTest.java:74)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:73)
at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:83)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:538)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:760)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:206)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.4.v20160829-44060b6): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: unexpected token: ) in statement [SELECT ID, EMAILADDRESS, FIRSTNAME, LASTNAME FROM Foo WHERE (FIRSTNAME IN ((?,?)))]
Error Code: -5581
Call: SELECT ID, EMAILADDRESS, FIRSTNAME, LASTNAME FROM Foo WHERE (FIRSTNAME IN ((?,?)))
bind => [Dave, Carter]
Query: ReadAllQuery(referenceClass=MyUser sql="SELECT ID, EMAILADDRESS, FIRSTNAME, LASTNAME FROM Foo WHERE (FIRSTNAME IN (?))")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:684)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:560)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2056)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:306)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2740)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2693)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:559)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1175)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:904)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1134)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:460)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1222)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1857)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
... 33 more
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: ) in statement [SELECT ID, EMAILADDRESS, FIRSTNAME, LASTNAME FROM Foo WHERE (FIRSTNAME IN ((?,?)))]
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1565)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1514)
at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:778)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:621)
... 53 more
Caused by: org.hsqldb.HsqlException: unexpected token: )
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
at org.hsqldb.ParserDQL.XreadInValueList(Unknown Source)
at org.hsqldb.ParserDQL.XreadInValueListConstructor(Unknown Source)
at org.hsqldb.ParserDQL.XreadInPredicateRightPart(Unknown Source)
at org.hsqldb.ParserDQL.XreadPredicateRightPart(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanPrimaryOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanTestOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanFactorOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanTermOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanValueExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanPrimaryOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanTestOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanFactorOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanTermOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadBooleanValueExpression(Unknown Source)
at org.hsqldb.ParserDQL.readWhereGroupHaving(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 59 more
To reproduce:
$ git clone https://github.com/spring-projects/spring-data-examples
$ cd spring-data-examples
$ git co -b eclipselink-349477
$ cd jpa/eclipselink
$ mvn clean test
The test case to look at is Issue349477IntegrationTest at https://github.com/spring-projects/spring-data-examples/blob/5a7b625edfc45d3471b28ccb51860a4c82de27ee/jpa/eclipselink/src/test/java/example/springdata/jpa/eclipselink/Issue349477IntegrationTest.java#L55-L76
The sample fails both on 2.6.4 and 2.7.1.
Aside from that, I think it's remarkable that a JavaEE specification's reference implementation is resorting to the argument "if you care, feel free to provide a fix" as unless you argue, what I presented almost 7 years ago is invalid code in the first place, you're basically at the mercy of the TCK not properly testing the implementation. You can of still decide to argue that way, but I guess you'll then have to live with people choosing other implementations over Eclipselink if core features like this stay broken for such a long time. Your call.
Other than one test case that was trying to use .values incorrectly and having similar symptoms, I have not said anyone's code is invalid nor have I tried to. What I have done is fixed the code issue that prevented the method accepting Expression<Collection<?>> from working. Feel free to keep this issue open, or as I tried to hint at, file a new bug to look at specifically resolving your remaining issue - this one is has a lot of people saying they are getting issues with ((?,?)) that are not all going to have the same causes or even be bugs. For what ever reason, I did NOT hit the issue when I was trying to debug originally and no one since as put in the debug information I explicitly requested. Being rude and demanding years later isn't going to entice me to pick this back up. This IS open source, and others who are interested either need to entice someone to look into it, pay for support, or look into it themselves. As for questions on the TCK, you should bring them up to the specification. I suspect though that since the specification did make two separate methods, they did intend there to be different behaviour on an Expression<?> vs Expression<Collection<?>> and not require providers to account for the loss of generic information that is causing this remaining issue. JPA providers have the flexibility to make one method work as you are expecting it to though, so it should just be a matter of changing the in(Expression<?>... values) method to check for the type appropriately for your situation without breaking any other uses of the method. For those who are willing to look into it, since I don't know how Java generic processing or the spec can be changed, take a look at ExpressionImpl's in(Expression<?>... values) method and routing it to the in(Expression<Collection<?>> values) method when appropriate. For anyone else hitting this, just add casting so it goes to the in(Expression<Collection<?>> values) method and it seems to work as intended. I've added 2 test methods to Oliver Gierke test, and I found that wrong `in` method from `javax.persistence.criteria.Expression` interface is executed.
So it goes like this:
builder.parameter(Collection.class) returns: ParameterExpression<Collection>
and method we expect to execute has signature:
Predicate in(Expression<Collection<?>> values);
but instead jvm executes:
Predicate in(Expression<?>... values);
To fix a problem I added literal cast to (Expression<Collection<?>>) and correct method is executed, and everything works fine (second test method).
In my humble opinion it is JPA specification flow and second method should have had signature:
Predicate in(Expression<Collection> values);
Out of curiosity I've switched to hibernate, and hibernate also executes wrong method but handles it internally. And both tests pass.
[1]
@Test
public void testCriteriaExpression() {
Customer dave = new Customer("Dave", "Matthews");
em.persist(dave);
em.flush();
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Customer> criteria = builder.createQuery(Customer.class);
Root<Customer> root = criteria.from(Customer.class);
ParameterExpression<Collection> parameter =
builder.parameter(Collection.class);
criteria.where(root.get("firstname").in(parameter));
TypedQuery<Customer> query = em.createQuery(criteria);
for (ParameterExpression param : criteria.getParameters()) {
query.setParameter(param, Arrays.asList("Dave", "Carter"));
}
Customer dbDave = query.getSingleResult();
assertThat(dbDave).isNotNull();
}
@Test
public void testCriteriaExpressionCast() {
Customer dave = new Customer("Dave", "Matthews");
em.persist(dave);
em.flush();
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Customer> criteria = builder.createQuery(Customer.class);
Root<Customer> root = criteria.from(Customer.class);
Object parameter = builder.parameter(Collection.class);
criteria.where(root.get("firstname")
.in((Expression<Collection<?>>)parameter));
TypedQuery<Customer> query = em.createQuery(criteria);
for (ParameterExpression param : criteria.getParameters()) {
query.setParameter(param, Arrays.asList("Dave", "Carter"));
}
Customer dbDave = query.getSingleResult();
assertThat(dbDave).isNotNull();
}
Just for reference as Chris seems to have unsubscribed from this ticket: I didn't mean to be rude. If that was the case, I'd like to apologize. However I might have felt a little bit offended by the following facts: 1. The ticket declared fixed, when it was obvious that the sample code I originally presented is still causing the same exception. 2. That in turn leaves two options: either my code is invalid (totally possible, but would require explanation how) or the fix is insufficient (which I'd argue is the case and renders the declared fix invalid). 3. Proven by the comments this ticket still gets, the community being interested in this to be fixed and the primary reaction by the maintainers being "This was fixed. Move on." when there's enough indicators that it hasn't. I've tried to be productive and make it easy for someone in the team to have something actionable. That's why I pushed the branch adding the executable test case for further investigation. EclipseLink being the reference implementation puts it into a special position. Rest assured I've done what I can to bring up the TCK issues with Oracle and the community [0]. [0] https://jaxenter.com/tck-access-controversy-chat-with-jpa-2-1-expert-group-member-oliver-gierke-105703.html I may be stepping into this quite late and there is a lot of content here to read so forgive me if I'm off base, but it seems to me that the issue is the extra parameter wrappings around the query, yes? ... WHERE (FIRSTNAME IN ((?,?))) vs. ... WHERE (FIRSTNAME IN (?,?)) The former is what I am seeing from EclipseLink and results in a "ORA-00907: missing right parenthesis" exception on Oracle, while the latter will run perfectly fine. I was under the impression that both are valid SQL for Oracle: https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions014.htm WHERE (FIRSTNAME IN (?,?))==> e.g. WHERE (FIRSTNAME IN (4,2)) ==> FIRSTNAME could be within the values of e.g. 4 and 2 WHERE (FIRSTNAME IN ((?,?))) ==> e.g. WHERE (FIRSTNAME IN ((4,2))) ==> FIRSTNAME is within a tuple (4,2) could be as well (6,9) or (42,100) or any other tuple.... if the database can handle it within itself to figure out that FIRSTNAME is not a tuple but a singleton ==> fine. But some databases are more strict! ==> the query is invalid! So, essentially the two (sub)queries are different! And I don't understand as well, why this is soooo hard to fix. The description is quite clear. I tried debugging EclipseLink to see how the Expressions end up being created for the same query in JPQL and there is a subtle difference:
JPQL Query:
Query q = em.createQuery("select t0 from SomeEntity t0 where t0.someString1 in ('Dave', 'Carter')");
JPQL Expression Structure:
RelationExpression
builder=ExpressionBuilder (@A)
firstChild=QueryKeyExpression (@B)
builder=ExpressionBuilder (@A)
secondChild=CollectionExpression
localbase=QueryKeyExpression (@B)
value=ArrayList<Expression>
[0]=ConstantExpression => "Dave"
[1]=ConstantExpression => "Carter"
Criteria Query:
I reused the same setup from: https://github.com/spring-projects/spring-data-examples/blob/5a7b625edfc45d3471b28ccb51860a4c82de27ee/jpa/eclipselink/src/test/java/example/springdata/jpa/eclipselink/Issue349477IntegrationTest.java#L55-L76
Criteria Expression Structure:
RelationExpression
builder=null
firstChild=QueryKeyExpression (@C)
builder=ExpressionBuilder (@D)
secondChild=CollectionExpression
localbase=QueryKeyExpression (@C)
value=Vector<Expression>
[0]=ParameterExpression
builder=null
localbase=QueryKeyExpression(@C)
------------
Now, there are two things here that I think are possibly relevant. The first is that the builder is null for the Criteria. From my understanding, the builder should not be null so this may have some relevancy on the issue here or another bug.
The second is that the value of the CollectionExpression is a List with only a single value! EclipseLink is not seeing the List as multiple parameters but rather one parameter. Now, this is not necessarily an issue. It actually sounds correct! The parameter is in fact 1 Collection so it makes sense to represent it this way. However, at the end of the day, it all comes down to how EclipseLink prints the SQL...
ExpressionSQLPrinter.printList(Collection values) creates the sql string that is set on the SQLCall:
SQLCall(SELECT ... WHERE (someString1 IN (?)))
Then later:
DatabaseCall.translateQueryStringForParameterizedIN() comes in to translate the ParameterExpression for '?'; into => (?,?)
Looking at 'translateQueryStringForParameterizedIN' I see that support was added for subcollections and translating this way adds all these extra parenthesis.
Do we need to print like this?:
WHERE (someString1 IN ('Dave','Carter'), ('Bob'), ('Charles', 'Smith'))
When this is logically the same:
WHERE (someString1 IN ('Dave','Carter', 'Bob', 'Charles', 'Smith'))
Now, I understand that this should work given that I see these as valid in Oracle's documentation:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions014.htm
But it seems that, at least on my tests, Oracle is having issues with these sub parenthesis.
Ok, I reread https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions014.htm a bit closer and I missed this part: The number of expressions in each set must equal the number of expressions in the first part of the condition. For example: SELECT * FROM employees WHERE (first_name, last_name, email) IN (('Guy', 'Himuro', 'GHIMURO'),('Karen', 'Colmenares', 'KCOLMENA')) Just for logical SQL-explanations - the query-element is basically something like WHERE <n-tuple-meta-name> in <n-tuple_Content> i.e. - a double-MetaName must match a double-Content (e.g. FirstName, LastName) - a triple-MetaName must match a triple-Content (e.g. FirstName, LastName, email) - ... I guess any SQL parser complains about the provided SQL-statement because adding additional parenthesis makes the content instead of a single-tuple_Content to n-tuple_Content - because the SQL-parsers treats the second parenthesis after the IN as a beginning of an n-tuple_Content. How should the SQL-parser figure out that instead of a triple content there is only a single-content? Honestly I do not understand how this issue could have been resolved. The only possibility I see is that it was tested with a single-tuple, i.e. an ArrayList with just a single content... Beside my clever talk: is there 'somebody' fixing this issue or will it be buried again with tooo many information? Created attachment 273115 [details] patch patch is also available in https://github.com/eclipse-ee4j/eclipselink/pull/2 GitHub Pull Request 9 created by [lukasj] https://github.com/eclipse-ee4j/eclipselink/pull/9 fixed in master and 2.7 Thanks for taking a spike at this, Lukas. Would you mind throwing a ping at this ticket as soon as EL snapshots containing the fix are available? I'D then go ahead and doublecheck the test cases we have using the fixed codepath in Spring Data JPA. I just tried but got 2.7.2-20180317.040550-46 jars, which (surprisingly) reports a version of 2.7.2.v20180316-65fdd86 at runtime and it doesn't seem to include the patch yet. (In reply to Oliver Gierke from comment #59) > [..]I'D > then go ahead and doublecheck the test cases we have using the fixed > codepath in Spring Data JPA. It took me some time to find out how to run your test but if I did it right, then it passed on my end. Feel free to double check > > I just tried but got 2.7.2-20180317.040550-46 jars, which (surprisingly) > reports a version of 2.7.2.v20180316-65fdd86 at runtime and it doesn't seem > to include the patch yet. you have to wait for something like 2.7.2-20180320, if it goes well, there should be new 2.7.2-SNAPSHOT build in <17hours (it runs once a day at https://ci.eclipse.org/eclipselink/) Thanks, Lukas. I've tried the most recent ones and our tests now produce this exception on HSQL:
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.2.v20180323-f96295b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: unexpected token: ? required: (
Error Code: -5581
Call: SELECT t0.ID, t0.DTYPE, t0.ACTIVE, t0.AGE, t0.BINARYDATA, t0.CREATEDAT, t0.DATEOFBIRTH, t0.EMAILADDRESS, t0.FIRSTNAME, t0.LASTNAME, t0.CITY, t0.COUNTRY, t0.STREETNAME, t0.STREETNO, t0.MANAGER_ID FROM SD_User t0 LEFT OUTER JOIN User_ATTRIBUTES t1 ON (t1.User_ID = t0.ID) WHERE (t1.ATTRIBUTES IN ?)
bind => [1 parameter bound]
Query: ReadAllQuery(referenceClass=User sql="SELECT t0.ID, t0.DTYPE, t0.ACTIVE, t0.AGE, t0.BINARYDATA, t0.CREATEDAT, t0.DATEOFBIRTH, t0.EMAILADDRESS, t0.FIRSTNAME, t0.LASTNAME, t0.CITY, t0.COUNTRY, t0.STREETNAME, t0.STREETNO, t0.MANAGER_ID FROM SD_User t0 LEFT OUTER JOIN User_ATTRIBUTES t1 ON (t1.User_ID = t0.ID) WHERE (t1.ATTRIBUTES IN ?)")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:688)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:564)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2093)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:309)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:270)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:256)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:327)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:722)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2743)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2696)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:563)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1221)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:911)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1180)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:464)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1268)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2979)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1892)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1874)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
... 62 more
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: ? required: (
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1590)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1539)
at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:804)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:625)
... 82 more
Caused by: org.hsqldb.HsqlException: unexpected token: ? required: (
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedTokenRequire(Unknown Source)
...
The exception I saw on 2.7.1 was this:
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.1.v20171221-bd47e8f): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: unexpected token: )
Error Code: -5581
Call: SELECT t0.ID, t0.DTYPE, t0.ACTIVE, t0.AGE, t0.BINARYDATA, t0.CREATEDAT, t0.DATEOFBIRTH, t0.EMAILADDRESS, t0.FIRSTNAME, t0.LASTNAME, t0.CITY, t0.COUNTRY, t0.STREETNAME, t0.STREETNO, t0.MANAGER_ID FROM SD_User t0 LEFT OUTER JOIN User_ATTRIBUTES t1 ON (t1.User_ID = t0.ID) WHERE (t1.ATTRIBUTES IN ((?,?)))
bind => [2 parameters bound]
Query: ReadAllQuery(referenceClass=User sql="SELECT t0.ID, t0.DTYPE, t0.ACTIVE, t0.AGE, t0.BINARYDATA, t0.CREATEDAT, t0.DATEOFBIRTH, t0.EMAILADDRESS, t0.FIRSTNAME, t0.LASTNAME, t0.CITY, t0.COUNTRY, t0.STREETNAME, t0.STREETNO, t0.MANAGER_ID FROM SD_User t0 LEFT OUTER JOIN User_ATTRIBUTES t1 ON (t1.User_ID = t0.ID) WHERE (t1.ATTRIBUTES IN (?))")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:688)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:564)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2093)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:309)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:270)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:256)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:327)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:722)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2740)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2693)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:563)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1221)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:911)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1180)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:464)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1268)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2979)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1892)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1874)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:260)
... 62 more
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: )
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1590)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1539)
at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:804)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:625)
... 82 more
Caused by: org.hsqldb.HsqlException: unexpected token: )
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
at org.hsqldb.ParserDQL.XreadInValueList(Unknown Source)
at org.hsqldb.ParserDQL.XreadInValueListConstructor(Unknown Source)
...
So it looks like the binding has indeed changed, it just still doesn't seem to render properly for HSQL.
Do you want me to create a new ticket for that or shall we try to get this sorted out on this one here?
create new one, possibly with back pointer (to Leo's comment #55), please, this one is getting hard to scan for relevant information The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink |
Build Identifier: see the following sample code: User user = new User("Dave", "Matthews", "foo@bar.de"); em.persist(user); em.flush(); CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<User> criteria = builder.createQuery(User.class); Root<User> root = criteria.from(User.class); criteria.where(root.get("firstname").in(builder.parameter(Collection.class))); TypedQuery<User> query = em.createQuery(criteria); for (ParameterExpression parameter : criteria.getParameters()) { query.setParameter(parameter, Arrays.asList("Dave", "Carter")); } List<User> result = query.getResultList(); assertThat(result.isEmpty(), is(false)); This fails with a java.sql.SQLException: Unexpected token: ) in statement [SELECT ID, DTYPE, EMAILADDRESS, FIRSTNAME, LASTNAME, MANAGER_ID FROM USER WHERE (FIRSTNAME IN ((?,?)))] If I use a parameter type of String[] and bind a new String[] {"Dave", "Carter"} I can see the log binding the value: [EL Fine]: 2011-06-15 18:48:51.047--ClientSession(557485745)--Connection(191764354)--SELECT ID, DTYPE, EMAILADDRESS, FIRSTNAME, LASTNAME, MANAGER_ID FROM USER WHERE (FIRSTNAME IN (?)) bind => [[Ljava.lang.String;@2206179e] Still the assertion fails as the array seems to be bound as single object not the values individually. This leaves me quite puzzled as it seems that using an in-predicate does not work at all when using ParameterExpressions. Am I just missing something? Reproducible: Always