grails-data-mapping icon indicating copy to clipboard operation
grails-data-mapping copied to clipboard

Published 20 hours ago • verified publisher icon grails

DetachedCriteria cause QueryException when using property of alias in projection of detachedCriteria

Open scaiandre opened this issue 2 years ago • 5 comments

Steps to Reproduce

grails create-app detachedCriteria cd detachedCriteria ./grailsw create-domain-class Author ./grailsw create-domain-class Book

class Book {

    String bookName
    Author author

    static constraints = {
    }
}


class Author {

    String authorName
    static constraints = {
    }
}

Try to use the domain classes in a controller or service as follows while using a grails.gorm.DetachedCriteria for a sub-select in the query. The query below is of course inefficient, but demonstrates the issue.

        final authors = Author.createCriteria().listDistinct {
            HibernateCriteriaBuilder hibernateCriteriaBuilder = delegate as HibernateCriteriaBuilder
            hibernateCriteriaBuilder.projections {
                hibernateCriteriaBuilder.property "id"
            }
            hibernateCriteriaBuilder.inList "id", new grails.gorm.DetachedCriteria(Book, "alias_books").build({
                grails.gorm.DetachedCriteria hibernateCriteriaBuilder1 = delegate as grails.gorm.DetachedCriteria
                hibernateCriteriaBuilder1.createAlias("author", "alias_author")
                hibernateCriteriaBuilder1.projections {
                    property 'alias_author.id'
                }
            })
        }

Expected Behaviour

The above query should execute just fine.

Actual Behaviour

Stacktrace:

Caused by: org.hibernate.QueryException: could not resolve property: alias_author.id of: detachedcriteria.Book
	at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:77)
	at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:71)
	at org.hibernate.persister.entity.AbstractEntityPersister.toType(AbstractEntityPersister.java:2053)
	at org.hibernate.loader.criteria.CriteriaQueryTranslator.getType(CriteriaQueryTranslator.java:605)
	at org.hibernate.criterion.PropertyProjection.getTypes(PropertyProjection.java:42)
	at org.hibernate.criterion.ProjectionList.getTypes(ProjectionList.java:87)
	at org.hibernate.loader.criteria.CriteriaQueryTranslator.getProjectedTypes(CriteriaQueryTranslator.java:410)
	at org.hibernate.criterion.SubqueryExpression.createAndSetInnerQuery(SubqueryExpression.java:135)
	at org.hibernate.criterion.SubqueryExpression.toSqlString(SubqueryExpression.java:61)
	at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:428)
	at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:95)
	at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:75)
	at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:80)
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1903)
	at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:370)

Environment Information

  • Operating System: Ubuntu 20.04 LTS
  • Grails Version (if using Grails): Grails 5.1.2
  • JDK Version: Java 11.0.13

Demo

https://github.com/scaiandre/grails-gorm-issue-detachedCriteria

./gradlew bootRun curl http://localhost:8080/detachedCriteria

Related issue Unknown entity: null

When instead using the alias property in a filter clause, like "eq", the exception is different.

        final authors = Author.createCriteria().listDistinct {
            HibernateCriteriaBuilder hibernateCriteriaBuilder = delegate as HibernateCriteriaBuilder
            hibernateCriteriaBuilder.projections {
                hibernateCriteriaBuilder.property "id"
            }
            hibernateCriteriaBuilder.inList "id", new grails.gorm.DetachedCriteria(Book, "alias_books").build({
                grails.gorm.DetachedCriteria hibernateCriteriaBuilder1 = delegate as grails.gorm.DetachedCriteria
                hibernateCriteriaBuilder1.createAlias("author", "alias_author")
                hibernateCriteriaBuilder1.projections {
                    property 'author.id'
                }
                hibernateCriteriaBuilder1.eq "alias_author.id", 1L
            })
        }

Stacktrace

Caused by: org.hibernate.HibernateException: Unknown entity: null
	at org.hibernate.loader.criteria.CriteriaQueryTranslator.getPropertyMapping(CriteriaQueryTranslator.java:646)
	at org.hibernate.loader.criteria.CriteriaQueryTranslator.getType(CriteriaQueryTranslator.java:604)
	at org.hibernate.loader.criteria.CriteriaQueryTranslator.getTypeUsingProjection(CriteriaQueryTranslator.java:585)
	at org.hibernate.loader.criteria.CriteriaQueryTranslator.getTypedValue(CriteriaQueryTranslator.java:640)
	at org.hibernate.criterion.SimpleExpression.getTypedValues(SimpleExpression.java:100)
	at org.hibernate.loader.criteria.CriteriaQueryTranslator.getQueryParameters(CriteriaQueryTranslator.java:357)
	at org.hibernate.criterion.SubqueryExpression.createAndSetInnerQuery(SubqueryExpression.java:134)
	at org.hibernate.criterion.SubqueryExpression.toSqlString(SubqueryExpression.java:61)
	at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:428)
	at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:95)
	at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:75)
	at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:80)
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1903)
	at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:370)

Just using createAlias without referring to it, works

This does not raise an exception

        final authors = Author.createCriteria().listDistinct {
            HibernateCriteriaBuilder hibernateCriteriaBuilder = delegate as HibernateCriteriaBuilder
            hibernateCriteriaBuilder.projections {
                hibernateCriteriaBuilder.property "id"
            }
            hibernateCriteriaBuilder.inList "id", new grails.gorm.DetachedCriteria(Book, "alias_books").build({
                grails.gorm.DetachedCriteria hibernateCriteriaBuilder1 = delegate as grails.gorm.DetachedCriteria
                hibernateCriteriaBuilder1.createAlias("author", "alias_author")
                hibernateCriteriaBuilder1.projections {
                    property 'author.id'
                }
            })
        }

scaiandre avatar Feb 25 '22 11:02 scaiandre

Since we need to have this kind of functionality I'm trying to work out a workaround.

I stitched together this ugly piece of code, which I do not want to put into production, actually, since it relies on internal state of DetachedCriteria and uses private methods. Furthermore I do not really understand the implications of changing the specific internal state.

I just want to leave this here for you to see what might go wrong here.

    static void propertyInHelper(grails.orm.HibernateCriteriaBuilder hibernateCriteriaBuilder, String propertyIn, grails.gorm.DetachedCriteria detachedCriteria) {
        if (detachedCriteria.associationCriteriaMap.size() > 0) {
            final assocationMap = [:]
            for (detachedAssociationCriteriaEntry in detachedCriteria.associationCriteriaMap.entrySet()) {
                assocationMap[detachedAssociationCriteriaEntry.value.associationPath] = detachedAssociationCriteriaEntry.value.alias
            }
            detachedCriteria.associationCriteriaMap.clear()
            detachedCriteria.criteria.removeAll({
                detachedCriteria.criteria.findAll {
                    it instanceof DetachedAssociationCriteria
                }
            })
            final hibernateDetachedCriteria = hibernateCriteriaBuilder.convertToHibernateCriteria(detachedCriteria)
            for (detachedAssociationCriteriaEntry in assocationMap) {
                hibernateDetachedCriteria.createAlias(detachedAssociationCriteriaEntry.key, detachedAssociationCriteriaEntry.value)
            }
            hibernateCriteriaBuilder.add Subqueries.propertyIn(propertyIn, hibernateDetachedCriteria)
        } else {
            hibernateCriteriaBuilder.inList(propertyIn, detachedCriteria)
        }
    }

Can be used like this to make the query work

        final authors = Author.createCriteria().listDistinct {
            HibernateCriteriaBuilder hibernateCriteriaBuilder = delegate as HibernateCriteriaBuilder
            hibernateCriteriaBuilder.projections {
                hibernateCriteriaBuilder.property "id"
            }
            propertyInHelper hibernateCriteriaBuilder, "id", new grails.gorm.DetachedCriteria(Book, "alias_books").build({
                grails.gorm.DetachedCriteria hibernateCriteriaBuilder1 = delegate as grails.gorm.DetachedCriteria
                hibernateCriteriaBuilder1.createAlias("author", "alias_author")
                hibernateCriteriaBuilder1.projections {
                    // No exception due to this
                    property 'alias_author.id'
                }
                // No exception due to this
                hibernateCriteriaBuilder1.eq "alias_author.id", 1L
            })
        }

This does of course not qualify as a workaround!

scaiandre avatar Feb 25 '22 11:02 scaiandre

Any news on this one? I'm asking since for us this is a blocker to migrate to Grails 5/GORM 7, since we have 100+ queries using GORM DetachedCriteria like this. The workaround above is not feasible from my perspective, since it relies on modifying internal state which is clearly not understood by us.

Thank you!

scaiandre avatar Mar 09 '22 08:03 scaiandre

We have the same problem. Did you find any other solution?

PhilippeRossTraction avatar Nov 07 '22 22:11 PhilippeRossTraction

also the same issue after upgrading to Grails 5

practical-programmer avatar Sep 06 '23 07:09 practical-programmer