GORM's where closure with an "in" Query Drops the where clause in generated SQL

Question

Source Code: https://github.com/HybridProgrammer/GormIn

Grails Version: 3.2.8

I'm trying to run the following query:

def query = UserData.where {
    teams { id in teamIds }
}

When running the query in Spock test it works correctly. When running it from a Grails service it returns all rows in UserData and does not filter out only those users belonging to the team in teamIds list.

Spock Tests

Running Query from Spock Test - Always Passes

void "never fails - direct approach"() {
    given:
    setupData()
    def me = User.first()
    def teamIds = me.getAuthorities().id

    when:
    def query = UserData.where {
        teams { id in teamIds }
    }

    then:
    teamIds.size() == 1
    query.size() == 2
}

Generated SQL

select count(*) as y0_ 
from user_data this_ inner join workflow_role_teams teams3_ 
    on this_.id=teams3_.user_data_teams_id inner join role teams_alia1_ 
    on teams3_.role_id=teams_alia1_.id 
 where teams_alia1_.id in (?) 
 limit ?

Running Query from Grails Service

Occasionally passes in IntelliJ, Never form CLI

void "sometimes fails"() {
    given:
    setupData()
    def me = User.first()

    when:
    def query = exampleService.getMyOrMyTeamsData(me)

    then:
    me.getAuthorities().size() == 1
    query.size() == 2
}

exampleService.getMyOrMyTeamsData

def getMyOrMyTeamsData(User me) {
    def teamIds = me.getAuthorities().id

    def query = UserData.where {
        teams { id in teamIds }
    }

    return query
}

Generated SQL

select count(*) as y0_ 
from user_data this_ inner join workflow_role_teams teams3_ 
    on this_.id=teams3_.user_data_teams_id inner join role teams_alia1_ 
    on teams3_.role_id=teams_alia1_.id 
limit ?

Solution

Please see solution branch https://github.com/HybridProgrammer/GormIn/commit/d510e63b3e85b82ce06e24d8a392c10873a8306c

Answer 1

Question was answered here:

Seemingly Random 1=1 where clause generated by GORM's where closure

In grails-app/domain/oneisone/UserData.groovy

List teams = new ArrayList()

Change to

List<Role> teams = new ArrayList()

Run grails clean then rerun the tests.