Using multi-tenant discriminator mode, IN queries are broken

[rvanderwerf]

I have an example app to show the issue. Just start it and bootstrap will fail. I have 3 levels of objects using multi-tenant support (subdomain based). Anyhow when I do a query from a child object for related objects I get a SQL error that one of the parameters (the IN statement criteria) is missing.

Caused by: org.h2.jdbc.JdbcSQLException: Parameter "#2" is not set; SQL statement:
select this_.id as id1_3_0_, this_.version as version2_3_0_, this_.tenant_id as tenant_i3_3_0_, this_.date_created as date_cre4_3_0_, this_.last_updated as last_upd5_3_0_, this_.name as name6_3_0_, this_.activity as activity7_3_0_, this_.date_trashed as date_tra8_3_0_ from contact_activity this_ where this_.date_trashed=? and this_.id in (?) [90012-193]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
	at org.h2.message.DbException.get(DbException.java:179)
	at org.h2.message.DbException.get(DbException.java:155)
	at org.h2.expression.Parameter.checkSet(Parameter.java:81)
	at org.h2.command.Prepared.checkParameters(Prepared.java:164)
	at org.h2.command.CommandContainer.query(CommandContainer.java:109)
	at org.h2.command.Command.executeQuery(Command.java:201)
	at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:110)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
	... 88 common frames omitted

This example has a namedQuery but also I've tried where queries and regular criteria queries with the same result.

[rvanderwerf]

multi-tenant-hibernate-issue.zip sample app

[graemerocher]

as far as I can see the “issue” is very old

I tried it in Grails 3.1.x and and older versions of GORM and the behaviour is the same

I can go back further versions but this is nothing new it seems the issue can be worked around simply by replacing

inList("books", [book])

with

books {
                inList('id', book.id)
}

Should it work? Probably.. it would be a matter of altering AbstractHibernateQuery to detect that the query is on books and that books is an association and transforming the query into the latter example as above that could probably be regarded as a new feature / improvement since there is a current workaround.

Duplicates this report from a while back:

https://github.com/grails/grails-core/issues/10796

[rvanderwerf]

Thanks for looking into this. The only reason I assumed it should work because it was for them with gorm and mongo( at least on the older version they were running). I think the workaround will work fine for them now, I'll try that out today.