Invalid SQL generated in Multitenant App for tablePerHierarchy false

[bleurubin]

Steps to Reproduce

1. Create a multitenant application with GORM configured to use DISCRIMINATOR multitenancy.
2. Create domain object hierarchy with tablePerHierarchy false
3. Query on the child class

Expected Behaviour

Queries should generate valid SQL and return expected result sets.

Actual Behaviour

Invalid SQL is generated and org.hibernate.exception.SQLGrammarException is thrown.

StackTrace:

Foo.find{ name == 'foo1' }.name == 'foo1'
    |
    org.hibernate.exception.SQLGrammarException: could not prepare statement

Condition failed with Exception:

Foo.find{ name == 'foo1' }.name == 'foo1'
    |
    org.hibernate.exception.SQLGrammarException: could not prepare statement

	at multitenant.table.per.hierarchy.FooIntegrationSpec.$tt__$spock_feature_0_1(FooIntegrationSpec.groovy:31)
	at multitenant.table.per.hierarchy.FooIntegrationSpec.test find Foo_closure4(FooIntegrationSpec.groovy)
	at groovy.lang.Closure.call(Closure.java:418)
	at groovy.lang.Closure.call(Closure.java:434)
	at grails.gorm.transactions.GrailsTransactionTemplate$1.doInTransaction(GrailsTransactionTemplate.groovy:68)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
	at grails.gorm.transactions.GrailsTransactionTemplate.executeAndRollback(GrailsTransactionTemplate.groovy:65)
	at multitenant.table.per.hierarchy.FooIntegrationSpec.test find Foo(FooIntegrationSpec.groovy)
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148)
	at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1934)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1903)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1881)
	at org.hibernate.loader.Loader.doQuery(Loader.java:925)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342)
	at org.hibernate.loader.Loader.doList(Loader.java:2622)
	at org.hibernate.loader.Loader.doList(Loader.java:2605)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2434)
	at org.hibernate.loader.Loader.list(Loader.java:2429)
	at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:109)
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1787)
	at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:363)
	at org.grails.orm.hibernate.query.AbstractHibernateQuery.singleResultViaListCall(AbstractHibernateQuery.java:807)
	at org.grails.orm.hibernate.query.AbstractHibernateQuery.singleResult(AbstractHibernateQuery.java:794)
	at grails.gorm.DetachedCriteria.get_closure1(DetachedCriteria.groovy:116)
	at groovy.lang.Closure.call(Closure.java:418)
	at groovy.lang.Closure.call(Closure.java:434)
	at grails.gorm.DetachedCriteria.withPopulatedQuery_closure8(DetachedCriteria.groovy:769)
	at groovy.lang.Closure.call(Closure.java:418)
	at groovy.lang.Closure.call(Closure.java:434)
	at org.grails.datastore.gorm.GormStaticApi.withDatastoreSession_closure24(GormStaticApi.groovy:862)
	at groovy.lang.Closure.call(Closure.java:418)
	at org.grails.datastore.mapping.core.DatastoreUtils.execute(DatastoreUtils.java:319)
	at org.grails.datastore.gorm.AbstractDatastoreApi.execute(AbstractDatastoreApi.groovy:40)
	at org.grails.datastore.gorm.GormStaticApi.withDatastoreSession(GormStaticApi.groovy:861)
	at grails.gorm.DetachedCriteria.withPopulatedQuery(DetachedCriteria.groovy:740)
	at grails.gorm.DetachedCriteria.get(DetachedCriteria.groovy:115)
	at grails.gorm.DetachedCriteria.find(DetachedCriteria.groovy:86)
	at grails.gorm.DetachedCriteria.find(DetachedCriteria.groovy:85)
	at org.grails.datastore.gorm.GormStaticApi.find(GormStaticApi.groovy:245)
	at org.grails.datastore.gorm.GormEntity$Trait$Helper.find(GormEntity.groovy:485)
	... 8 more
Caused by: org.h2.jdbc.JdbcSQLException: Column "THIS_.TENANT_ID" not found; SQL statement:
select this_.id as id1_0_0_, this_1_.version as version2_0_0_, this_1_.tenant_id as tenant_i3_0_0_, this_1_.bar as bar4_0_0_, this_.name as name2_1_0_ from foo this_ inner join base_foo this_1_ on this_.id=this_1_.id where ? = this_.tenant_id and ? = this_1_.tenant_id limit ? [42122-197]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
	at org.h2.message.DbException.get(DbException.java:179)
	at org.h2.message.DbException.get(DbException.java:155)
	at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:150)
	at org.h2.expression.Comparison.optimize(Comparison.java:179)
	at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:130)
	at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:130)
	at org.h2.command.dml.Select.prepare(Select.java:861)
	at org.h2.command.Parser.prepareCommand(Parser.java:283)
	at org.h2.engine.Session.prepareLocal(Session.java:611)
	at org.h2.engine.Session.prepareCommand(Session.java:549)
	at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247)
	at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:76)
	at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:304)
	at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
	at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
	at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:75)
	at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
	at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
	at org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy$LazyConnectionInvocationHandler.invoke(LazyConnectionDataSourceProxy.java:376)
	at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:240)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)
	... 40 more

Environment Information

• Operating System: Linux
• GORM Version: 6.1.11
• Grails Version (if using Grails): 3.3.9
• JDK Version: 1.8

Example Application

Integration tests demonstrating the problem here: https://github.com/bleurubin/multitenant-table-per-hierarchy

Integration Test: https://github.com/bleurubin/multitenant-table-per-hierarchy/blob/master/src/integration-test/groovy/multitenant/table/per/hierarchy/FooIntegrationSpec.groovy

Queries on the parent class work as expected. The issue seems to be invalid SQL in the where clause specifying the tenant_id when querying on the child class.

The workaround is to wrap such queries in a withoutId closure. Developers can specify the tenantId manually if desired.

def currentId = Tenants.currentId()

Tenants.withoutId {
   Foo.find { name == 'foo1' && tenantId == currentId }
}