Wrong SQL generated for IN operation and multi-tenancy add-on

[gorbunkov]

Jmix version: 1.3.3

1. Create new project
2. Create two entities: Task and Person
3. Add multitenancy add-on
4. Add tenant field in new entities

Open the tasks browser and try to add a filter condition for a Person reference and IN condition. Or execute any JPQL that looks like this:

List<Task> tasks = dataManager.load(Task.class)
	.query("select t from Task_ t where t.person in :persons")
	.parameter("persons", persons)
	.list();

As a result, you'll get an exception:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.9.6-jmix): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: unexpected token: A4DF1BB required: )
Error Code: -5581
Call: SELECT ID, NUMBER_, TENANT_ID, PERSON_ID FROM TASK_ WHERE ((PERSON_ID IN (0a4df1bb-289e-f616-cdb7-8d5c7a5b0fd1)) AND (('no_tenant' = 'no_tenant') OR (TENANT_ID = 'no_tenant')))
Query: ReadAllQuery(referenceClass=Task sql="SELECT ID, NUMBER_, TENANT_ID, PERSON_ID FROM TASK_ WHERE ((PERSON_ID IN ?) AND ((? = ?) OR (TENANT_ID = ?)))")
FetchGroup(){number, person, tenantId, id}
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.9.6-jmix): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: unexpected token: A4DF1BB required: )
Error Code: -5581
Call: SELECT ID, NUMBER_, TENANT_ID, PERSON_ID FROM TASK_ WHERE ((PERSON_ID IN (0a4df1bb-289e-f616-cdb7-8d5c7a5b0fd1)) AND (('no_tenant' = 'no_tenant') OR (TENANT_ID = 'no_tenant')))
Query: ReadAllQuery(referenceClass=Task sql="SELECT ID, NUMBER_, TENANT_ID, PERSON_ID FROM TASK_ WHERE ((PERSON_ID IN ?) AND ((? = ?) OR (TENANT_ID = ?)))")
FetchGroup(){number, person, tenantId, id}
	at app//org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:391)
	at app//org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:264)
	at app//org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:482)
	at app//io.jmix.eclipselink.impl.JmixEclipseLinkQuery.getResultFromCache(JmixEclipseLinkQuery.java:824)
	at app//io.jmix.eclipselink.impl.JmixEclipseLinkQuery.getResultList(JmixEclipseLinkQuery.java:138)
	at app//io.jmix.eclipselink.impl.JpaDataStore.executeQuery(JpaDataStore.java:574)
	at app//io.jmix.eclipselink.impl.JpaDataStore.loadAll(JpaDataStore.java:167)
	at app//io.jmix.core.datastore.AbstractDataStore.loadList(AbstractDataStore.java:127)
	at app//io.jmix.core.impl.UnconstrainedDataManagerImpl.loadList(UnconstrainedDataManagerImpl.java:112)
	at app//io.jmix.core.FluentLoader$ByQuery.list(FluentLoader.java:486)
	at app//com.company.sample.MultiTenancyInQueryHsqlApplicationTests.contextLoads(MultiTenancyInQueryHsqlApplicationTests.java:72)

The UUID is put right into the query with no quotes.

Sample project: multi-tenancy-in-query-hsql.zip

Run the MultiTenancyInQueryHsqlApplicationTests to see the error.

When multitenancy add-on is excluded from the project, everything works fine.

It also seems that error depends on DBMS. With PostreSQL everything works fine, with Oracle database a different error occurs when you try to execute such JPQL.

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.9.6-jmix): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Invalid column type
Error Code: 17004
Call: SELECT ID, NUMBER_, TENANT_ID, PERSON_ID FROM TASK_ WHERE ((PERSON_ID IN (?)) AND ((? = ?) OR (TENANT_ID = ?)))
	bind => [eafeb726-7e29-ec06-2a75-a74d8e5cbed8, no_tenant, no_tenant, no_tenant]
Query: ReadAllQuery(referenceClass=Task sql="SELECT ID, NUMBER_, TENANT_ID, PERSON_ID FROM TASK_ WHERE ((PERSON_ID IN ?) AND ((? = ?) OR (TENANT_ID = ?)))")
FetchGroup(){number, person, tenantId, id}
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.9.6-jmix): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Invalid column type
Error Code: 17004
Call: SELECT ID, NUMBER_, TENANT_ID, PERSON_ID FROM TASK_ WHERE ((PERSON_ID IN (?)) AND ((? = ?) OR (TENANT_ID = ?)))
	bind => [eafeb726-7e29-ec06-2a75-a74d8e5cbed8, no_tenant, no_tenant, no_tenant]
Query: ReadAllQuery(referenceClass=Task sql="SELECT ID, NUMBER_, TENANT_ID, PERSON_ID FROM TASK_ WHERE ((PERSON_ID IN ?) AND ((? = ?) OR (TENANT_ID = ?)))")
FetchGroup(){number, person, tenantId, id}
	at app//org.eclipse.persistence.internal.jpa.QueryImpl.getDetailedException(QueryImpl.java:391)
	at app//org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:264)
	at app//org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:482)
	at app//io.jmix.eclipselink.impl.JmixEclipseLinkQuery.getResultFromCache(JmixEclipseLinkQuery.java:824)
	at app//io.jmix.eclipselink.impl.JmixEclipseLinkQuery.getResultList(JmixEclipseLinkQuery.java:138)
	at app//io.jmix.eclipselink.impl.JpaDataStore.executeQuery(JpaDataStore.java:574)
	at app//io.jmix.eclipselink.impl.JpaDataStore.loadAll(JpaDataStore.java:167)
	at app//io.jmix.core.datastore.AbstractDataStore.loadList(AbstractDataStore.java:127)
	at app//io.jmix.core.impl.UnconstrainedDataManagerImpl.loadList(UnconstrainedDataManagerImpl.java:112)
	at app//io.jmix.core.FluentLoader$ByQuery.list(FluentLoader.java:486)
	at app//com.company.sample.MultiTenancyInQueryHsqlApplicationTests.contextLoads(MultiTenancyInQueryHsqlApplicationTests.java:72)

See forum topic