Adding org.hibernate.criterion.Restriction to GORM createCriteria generates valid, but wrong SQL, delivering false data

[scaiandre]

Steps to Reproduce

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

class Book {

    String bookName
    Author author

    static constraints = {
    }
}

class Author {

    String authorName
    static constraints = {
    }
}

Have this clause in logback.xml:

    <logger name="org.hibernate.SQL" level="DEBUG"/>

Try to use the domain classes in a controller or service as follows: Use createCriteria and add a org.hibernate.criterion.Restriction to the query in a or-clause.

        final authors = Author.createCriteria().listDistinct {
            HibernateCriteriaBuilder hibernateCriteriaBuilder = delegate as HibernateCriteriaBuilder
            hibernateCriteriaBuilder.projections {
                hibernateCriteriaBuilder.property "id"
            }
            or {
                add Restrictions.eq("id", 1L)
                eq "authorName", "author1"
            }
        }

Execute the code.

Expected Behaviour

Expected generated sql (to be seen on console):

 SELECT this_.id AS y0_
FROM   author this_
WHERE  this_.id = ?
       OR ( this_.author_name = ? )  

Actual Behaviour

generated sql (to be seen on console):

 SELECT this_.id AS y0_
FROM   author this_
WHERE  this_.id = ?
       AND ( this_.author_name = ? )  

Note, that there is an AND clause generated instead of an OR clause.

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/brokenMixingOfHibernateCriterionWithFormCriteria/

[scaiandre]

Well, I guess, I found the reasoning behind this, although I find it cumbersome.

The delegate of the or-closure is of the interface org.grails.datastore.mapping.query.api.Criteria. That interface does not carry the add-method.

From what I see, the add method is somewhat added by reflection in AbstractHibernateCriteriaBuilder.invokeMethod. So, this looks like a case, where though something "runs", it is not intended to be used like that.

I'm using Grails for more than 10 years now, and back in those days, IDE's could not tell a lot about interfaces implemented of delegates, so I'm not used to actually respecting that, which might be wrong here.

Still, I would appreciate a comment, whether this is intended to not work.

[puneetbehl]

Thank you for reporting this issue. I think it definitely is a bug.