subqueries not supported for DetachedCriteria queries

[musketyr]

even it looks that detached criteria are supported by DetachedCriteria.inList(String,QueryableCriteria) method to perform subqueries they fails with

No converter found capable of converting from type org.hibernate.criterion.DetachedCriteria to type java.lang.Long. Stacktrace follows:
Message: No converter found capable of converting from type org.hibernate.criterion.DetachedCriteria to type java.lang.Long
    Line | Method
->>  440 | applyDetachedCriteria in org.grails.datastore.gorm.finders.DynamicFinder
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|   1200 | doCall                in grails.gorm.DetachedCriteria$_withPopulatedQuery_closure10
|    700 | doCall . . . . . . .  in org.grails.datastore.gorm.GormStaticApi$_withDatastoreSession_closure23
|    302 | execute               in org.grails.datastore.mapping.core.DatastoreUtils
|     37 | execute . . . . . . . in org.grails.datastore.gorm.AbstractDatastoreApi
|    699 | withDatastoreSession  in org.grails.datastore.gorm.GormStaticApi
|   1184 | withPopulatedQuery .  in grails.gorm.DetachedCriteria
|    843 | list                  in     ''
|     50 | getItems . . . . . .  in org.modelcatalogue.core.util.DetachedListWithTotalAndType
|     19 | getItems              in org.modelcatalogue.core.util.ListWithTotalAndTypeWrapper
|     23 | prepareJsonMap . . .  in org.modelcatalogue.core.util.marshalling.ListWrapperMarshaller
|     26 | doCall                in org.modelcatalogue.core.util.marshalling.AbstractMarshallers$_register_closure1
|    202 | value . . . . . . . . in grails.converters.JSON
|    134 | render                in     ''
|    116 | renderJson . . . . .  in org.grails.plugins.web.rest.render.json.DefaultJsonRenderer
|    110 | renderJson            in     ''
|     91 | render . . . . . . .  in     ''
|    177 | respond               in org.grails.plugins.web.rest.api.ControllersRestApi
|     59 | global . . . . . . .  in org.modelcatalogue.core.ChangeController
|    198 | doFilter              in grails.plugin.cache.web.filter.PageFragmentCachingFilter
|     63 | doFilter . . . . . .  in grails.plugin.cache.web.filter.AbstractFilter
|    150 | invoke                in net.bull.javamelody.JspWrapper
|    285 | invoke . . . . . . .  in net.bull.javamelody.JdbcWrapper$DelegatingInvocationHandler
|    198 | doFilter              in net.bull.javamelody.MonitoringFilter
|    176 | doFilter . . . . . .  in     ''
|     53 | doFilter              in grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter
|     49 | doFilter . . . . . .  in grails.plugin.springsecurity.web.authentication.RequestHolderAuthenticationFilter
|     82 | doFilter              in grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter
|   1145 | runWorker . . . . . . in java.util.concurrent.ThreadPoolExecutor
|    615 | run                   in java.util.concurrent.ThreadPoolExecutor$Worker
^    744 | run . . . . . . . . . in java.lang.Thread

Here is the simplified query which fails

DetachedCriteria<CatalogueElement> criteria = new DetachedCriteria<CatalogueElement>(CatalogueElement)

criteria.projections {
    property 'id'
}

List<Change> changes = new DetachedCriteria<Change>(Change).build  {
    inList 'changedId', criteria
    ne 'system', Boolean.TRUE
}.list()

The whole point is that the implementation in JpaQueryBuilder just expects an collection to be passed in not a query.

This happens in 2.4.3 but after taking look to the current code it doesn't seem to be fixed in later versions.

[zyro23]

just ran a variation of your simplified query against a 2.4.5 app and it works just fine:

import grails.gorm.DetachedCriteria
import my.package.MyDomain

DetachedCriteria<MyDomain> criteria = new DetachedCriteria<MyDomain>(MyDomain)
criteria.projections {
    property 'id'
}
List<MyDomain> results = new DetachedCriteria<MyDomain>(MyDomain).build  {
    inList 'id', criteria
}.list()

your stacktrace looks like grails.gorm.DetachedCriteria got mixed up with org.hibernate.criterion.DetachedCriteria - is that a possible cause?

[graemerocher]

Maybe he is referring to GORM-JPA since he mentions JpaQueryBuilder, it certainly works in the Hibernate version

[musketyr]

@zyro23 no, both are GORM criteria. JPA builder seems to be default implementations for GORM, is there a way how to switch to another implementation? Searching for Query.In restriction references hasn't returned any other.

[graemerocher]

JPA builder is not the default no, Hibernate is

[musketyr]

Ok, sorry for mystifying you. I was debugging again and the debugger felt in to Hibernate implementation. I'll try to upgrade the application and ping you back.

[musketyr]

Ok, so I've tried it again and it looks it works in runtime but fails in integration spec

[graemerocher]

Maybe upload an example to Github and we can take a look

[musketyr]

sorry, there must be something more than the setup present in that simple example so better to test it in real site

git clone https://github.com/MetadataRegistry/ModelCataloguePlugin.git
cd ModelCataloguePlugin
cd ModelCatalogueCorePlugin
# test without subquery is passing
./grailsw test-app integration: ClassificationServiceSpec

Remove .list() call on detached criteria in ClassificationService line 56 and run the test again

nano grails-app/services/org/modelcatalogue/core/ClassificationService.groovy
# run again, test now fails
./grailsw test-app integration: ClassificationServiceSpec

[CasperJon]

I've come across very similar scenario.

In my use case calling count on criteria object, makes some of subqueries to be converted to org.hibernate.criterion.DetachedCriteria. So when I try to call list(paginationParameters) afterwards they can not be converted to QueryableCriteria, and ClassCastException is thrown

java.lang.ClassCastException: org.hibernate.criterion.DetachedCriteria cannot be cast to org.grails.datastore.mapping.query.api.QueryableCriteria
at org.grails.datastore.mapping.query.Query$SubqueryCriterion.getValue(Query.java:843)
at org.grails.datastore.mapping.query.Query$SubqueryCriterion.getValue(Query.java:836)
at org.grails.datastore.gorm.finders.DynamicFinder.applyDetachedCriteria(DynamicFinder.java:440)
at grails.gorm.DetachedCriteria$_withPopulatedQuery_closure10.doCall(DetachedCriteria.groovy:1200)
at org.grails.datastore.gorm.GormStaticApi$_withDatastoreSession_closure23.doCall(GormStaticApi.groovy:700)
at org.grails.datastore.mapping.core.DatastoreUtils.execute(DatastoreUtils.java:302)
at org.grails.datastore.gorm.AbstractDatastoreApi.execute(AbstractDatastoreApi.groovy:37)
at org.grails.datastore.gorm.GormStaticApi.withDatastoreSession(GormStaticApi.groovy:699)
at grails.gorm.DetachedCriteria.withPopulatedQuery(DetachedCriteria.groovy:1184)
at grails.gorm.DetachedCriteria.list(DetachedCriteria.groovy:843)


I think both cases (musketyr's and mine) refer to this Jira ticket https://jira.grails.org/browse/GRAILS-12107

[Hc747]

Was this ever resolved..?