Possible bug in EclipseLink when using JPQL IN with EmbeddedId

[njr-11]

Is it expected that the JPQL IN operation should work with EmbeddedId? EcilpseLink appears to be trying to support it but generates the SQL in a way that results in an error.

To reproduce, uncomment these lines in an existing test case and run it,

open-liberty/dev/io.openliberty.data.internal_fat_jpa/test-applications/DataJPATestApp/src/test/jakarta/data/jpa/web/DataJPATestServlet.java

Lines 860 to 862 in 14b5803

	// System.out.println("findByIdInOrOwner: " + accounts.findByIdInOrOwner(List.of(AccountId.of(1004470, 30372),
	// AccountId.of(1006380, 22158)),
	// "Emma TestEmbeddedId"));

The error raised is:

jakarta.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.2.v202306161219): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "," at line 1, column 102.
Error Code: 30000
Call: SELECT BALANCE, BANKNAME, CHECKING, OWNER, ACCOUNTNUM, ROUTINGNUM FROM WLPAccount WHERE (((ACCOUNTNUM, ROUTINGNUM) IN (AccountId:1004470:30372, AccountId:1006380:22158)) OR (OWNER = 'Emma TestEmbeddedId')) ORDER BY OWNER DESC
Query: ReadAllQuery(referenceClass=Account sql="SELECT BALANCE, BANKNAME, CHECKING, OWNER, ACCOUNTNUM, ROUTINGNUM FROM WLPAccount WHERE (((ACCOUNTNUM, ROUTINGNUM) IN ?) OR (OWNER = ?)) ORDER BY OWNER DESC")
at io.openliberty.data.internal.persistence.RepositoryImpl.failure(RepositoryImpl.java:518)
at io.openliberty.data.internal.persistence.RepositoryImpl.invoke(RepositoryImpl.java:2749)
at jdk.proxy9/jdk.proxy9.$Proxy96.findByIdInOrOwner(Unknown Source)
at test.jakarta.data.jpa.web.DataJPATestServlet.testEmbeddedId(DataJPATestServlet.java:860)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at componenttest.app.FATServlet.doGet(FATServlet.java:73)
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:527)
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:614)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1260)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:748)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:445)
at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:1361)
at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:1077)
at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:77)
at com.ibm.ws.webcontainer40.servlet.CacheServletWrapper40.handleRequest(CacheServletWrapper40.java:87)
at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:969)
at com.ibm.ws.webcontainer.osgi.DynamicVirtualHost$2.run(DynamicVirtualHost.java:293)
at com.ibm.ws.http.dispatcher.internal.channel.HttpDispatcherLink$TaskWrapper.run(HttpDispatcherLink.java:1260)
at com.ibm.ws.http.dispatcher.internal.channel.HttpDispatcherLink.wrapHandlerAndExecute(HttpDispatcherLink.java:476)
at com.ibm.ws.http.dispatcher.internal.channel.HttpDispatcherLink.ready(HttpDispatcherLink.java:435)
at com.ibm.ws.http.channel.internal.inbound.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:569)
at com.ibm.ws.http.channel.internal.inbound.HttpInboundLink.handleNewRequest(HttpInboundLink.java:503)
at com.ibm.ws.http.channel.internal.inbound.HttpInboundLink.processRequest(HttpInboundLink.java:363)
at com.ibm.ws.http.channel.internal.inbound.HttpInboundLink.ready(HttpInboundLink.java:330)
at com.ibm.ws.tcpchannel.internal.NewConnectionInitialReadCallback.sendToDiscriminators(NewConnectionInitialReadCallback.java:169)
at com.ibm.ws.tcpchannel.internal.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:77)
at com.ibm.ws.tcpchannel.internal.WorkQueueManager.requestComplete(WorkQueueManager.java:516)
at com.ibm.ws.tcpchannel.internal.WorkQueueManager.attemptIO(WorkQueueManager.java:586)
at com.ibm.ws.tcpchannel.internal.WorkQueueManager.workerRun(WorkQueueManager.java:970)
at com.ibm.ws.tcpchannel.internal.WorkQueueManager$Worker.run(WorkQueueManager.java:1059)
at com.ibm.ws.threading.internal.ExecutorServiceImpl$RunnableWrapper.run(ExecutorServiceImpl.java:280)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:857)
Caused by: jakarta.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.2.v202306161219): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "," at line 1, column 102.
Error Code: 30000
Call: SELECT BALANCE, BANKNAME, CHECKING, OWNER, ACCOUNTNUM, ROUTINGNUM FROM WLPAccount WHERE (((ACCOUNTNUM, ROUTINGNUM) IN (AccountId:1004470:30372, AccountId:1006380:22158)) OR (OWNER = 'Emma TestEmbeddedId')) ORDER BY OWNER DESC
Query: ReadAllQuery(referenceClass=Account sql="SELECT BALANCE, BANKNAME, CHECKING, OWNER, ACCOUNTNUM, ROUTINGNUM FROM WLPAccount WHERE (((ACCOUNTNUM, ROUTINGNUM) IN ?) OR (OWNER = ?)) ORDER BY OWNER DESC")
at org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:392)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:265)
at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:483)
at jakarta.persistence.TypedQuery.getResultStream(TypedQuery.java:87)
at io.openliberty.data.internal.persistence.RepositoryImpl.invoke(RepositoryImpl.java:2459)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.2.v202306161219): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "," at line 1, column 102.
Error Code: 30000
Call: SELECT BALANCE, BANKNAME, CHECKING, OWNER, ACCOUNTNUM, ROUTINGNUM FROM WLPAccount WHERE (((ACCOUNTNUM, ROUTINGNUM) IN (AccountId:1004470:30372, AccountId:1006380:22158)) OR (OWNER = 'Emma TestEmbeddedId')) ORDER BY OWNER DESC
Query: ReadAllQuery(referenceClass=Account sql="SELECT BALANCE, BANKNAME, CHECKING, OWNER, ACCOUNTNUM, ROUTINGNUM FROM WLPAccount WHERE (((ACCOUNTNUM, ROUTINGNUM) IN ?) OR (OWNER = ?)) ORDER BY OWNER DESC")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:343)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:702)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:569)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2048)
at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:611)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:263)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:280)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:266)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:352)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:792)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2848)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2801)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:584)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1236)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:913)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1195)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:485)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1283)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3025)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1841)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1823)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1788)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:263)
Caused by: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "," at line 1, column 102.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source)
at org.apache.derby.iapi.jdbc.BrokeredStatement.executeQuery(Unknown Source)
at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeQuery(WSJdbcStatement.java:576)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1024)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:662)
Caused by: ERROR 42X01: Syntax error: Encountered "," at line 1, column 102.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.ParserImpl.parseStatementOrSearchCondition(Unknown Source)
at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source) 

The JPQL used is:
SELECT o FROM Account o WHERE (o.accountId IN ?1 OR o.owner=?2) ORDER BY o.owner DESC

[Riva-Tholoor-Philip]

See EclipseLink Issue : 2196

[njr-11]

This issue asks the question, "Is it expected that the JPQL IN operation should work with EmbeddedId?". It's fine that EclipseLink has answered that question with "No". However, given that it is not supported, EclipseLink needs to be raising a better error to the user saying that it isn't supported rather than trying to run some SQL command that fails in the database with a parse error. This makes it look to our users like it is broken rather than intentionally unsupported.