Grails 3.3 detached criteria appears to give incorrect result

[monetschemist]

Brief description: detached criteria used to filter instances of a self-referencing class deliver incorrect results in specific cases documented below.

Steps to Reproduce

I have created a simple Grails application with one self-referencing domain class, populated by BootStrap.groovy, that demonstrates the problem.

This application should be cleaned and compiled. There is a default CRUD controller and views but these aren't necessary for the demonstration of the problem.

Once the application is built, here are the specific steps that demonstrate the problem:

In order to ensure all is working, use the Grails console to execute the following commands:

animals.Animals.list().each { a -> println "a ${a.name} sire ${a.sire?.name} dam ${a.dam?.name} mgs ${a.dam?.sire?.name}" }

This will list all the animals currently loaded in the database. The result should appear as:

a Northern Dancer sire null dam null mgs null
a Sweet Alliance sire null dam null mgs null
a Formidable sire null dam null mgs null
a As Blessed sire null dam null mgs null
a Tentam sire null dam null mgs null
a Nimble Deb sire null dam null mgs null
a Excaliburs Lake sire null dam null mgs null
a Sade sire null dam null mgs null
a Shareef Dancer sire Northern Dancer dam Sweet Alliance mgs null
a Glory of Hera sire Formidable dam As Blessed mgs null
a Tecorno sire Tentam dam Nimble Deb mgs null
a Excaliburs Glory sire Excaliburs Lake dam Sade mgs null
a Glory of Dancer sire Shareef Dancer dam Glory of Hera mgs Formidable
a Delectable sire Tecorno dam Excaliburs Glory mgs Excaliburs Lake
a Delectable Baby sire Glory of Dancer dam Delectable mgs Tecorno
a Pas de Nom sire null dam null mgs null
a His Majesty sire null dam null mgs null
a Spring Adieu sire null dam null mgs null
a Raise a Native sire null dam null mgs null
a Gold Digger sire null dam null mgs null
a Fanfreluche sire null dam null mgs null
a Danzig sire Northern Dancer dam Pas de Nom mgs null
a Razyana sire His Majesty dam Spring Adieu mgs null
a Mr. Prospector sire Raise a Native dam Gold Digger mgs null
a La Voyageuse sire Tentam dam Fanfreluche mgs null
a Danehill sire Danzig dam Razyana mgs His Majesty
a Society Lady sire Mr. Prospector dam La Voyageuse mgs Tentam
a Nasmatt sire Danehill dam Society Lady mgs Mr. Prospector
Result: [animals.Animals : 1, animals.Animals : 2, animals.Animals : 3, animals.Animals : 4, animals.Animals : 5, animals.Animals : 6, animals.Animals : 7, animals.Animals : 8, animals.Animals : 9, animals.Animals : 10, animals.Animals : 11, animals.Animals : 12, animals.Animals : 13, animals.Animals : 14, animals.Animals : 15, animals.Animals : 16, animals.Animals : 17, animals.Animals : 18, animals.Animals : 19, animals.Animals : 20, animals.Animals : 21, animals.Animals : 22, animals.Animals : 23, animals.Animals : 24, animals.Animals : 25, animals.Animals : 26, animals.Animals : 27, animals.Animals : 28]

In that list we can see the animal "Society Lady" whose sire is "Mr. Prospector", dam is "La Voyageuse" and maternal grandsire is "Tentam".

To demonstrate that a single detached criteria works as expected, use the Grails console to execute the following commands:

criteria = new grails.gorm.DetachedCriteria(animals.Animals)
criteria = criteria.build {
    dam {
        sire {
            ilike 'name','Tentam'
        }
    }
}
criteria.list().each { a-> println "name ${a.name} bornOn ${a.bornOn} sire ${a.sire?.name} dam ${a.dam?.name} mgs ${a.dam?.sire?.name}" }

As expected, this will report

name Society Lady bornOn 1990-01-01 00:00:00.0 sire Mr. Prospector dam La Voyageuse mgs Tentam

To demonstrate that two chained detached criteria work as expected, use the Grails console to execute the following commands:

criteria = new grails.gorm.DetachedCriteria(animals.Animals)
criteria = criteria.build {
    dam {
        ilike 'name','La Voyageuse'
    }
}
criteria = criteria.build {
    dam {
        sire {
            ilike 'name','Tentam'
        }
    }
}
criteria.list().each { a-> println "name ${a.name} bornOn ${a.bornOn} sire ${a.sire?.name} dam ${a.dam?.name} mgs ${a.dam?.sire?.name}" }

As expected, this will report

name Society Lady bornOn 1990-01-01 00:00:00.0 sire Mr. Prospector dam La Voyageuse mgs Tentam

To demonstrate the case where the two chained detached criteria fail to work correctly, use the Grails console to execute the following commands:

criteria = new grails.gorm.DetachedCriteria(animals.Animals)
criteria = criteria.build {
    sire {
        ilike 'name','Mr. Prospector'
    }
}
criteria = criteria.build {
    dam {
        sire {
            ilike 'name','Tentam'
        }
    }
}
criteria.list().each { a-> println "name ${a.name} bornOn ${a.bornOn} sire ${a.sire?.name} dam ${a.dam?.name} mgs ${a.dam?.sire?.name}" }

Unexpectedly, no rows satisfy the chained criteria - the animal Society Lady is not reported.

Surprisingly (to me anyway), by reversing the order of the criteria in the above test to first select sire of dam name, then sire name:

criteria = new grails.gorm.DetachedCriteria(animals.Animals)
criteria = criteria.build {
    dam {
        sire {
            ilike 'name','Tentam'
        }
    }
}
criteria = criteria.build {
    sire {
        ilike 'name','Mr. Prospector'
    }
}
criteria.list().each { a-> println "name ${a.name} bornOn ${a.bornOn} sire ${a.sire?.name} dam ${a.dam?.name} mgs ${a.dam?.sire?.name}" 

The expected answer is once again reported.

name Society Lady bornOn 1990-01-01 00:00:00.0 sire Mr. Prospector dam La Voyageuse mgs Tentam

Expected Behaviour

See above: all tests with the detached criteria, whether singly or in pairs, should yield this line of output:

name Society Lady bornOn 1990-01-01 00:00:00.0 sire Mr. Prospector dam La Voyageuse mgs Tentam

Actual Behaviour

See above: one test, where the first criteria refers first to sire.name and the second to dam.sire.name fails to deliver that result. However if the order of the criteria are reversed, the correct answer is generated.

Environment Information

Linux 4.13.0-39-generic #44-Ubuntu SMP Thu Apr 5 14:25:01 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
Grails Version: 3.3.5
Groovy Version: 2.4.15
JVM Version: 1.8.0_171

Example Application

https://github.com/monetschemist/grails-detached-criteria-problem.git

[puneetbehl]

It very hard to follow the above description. I would request you to please explain the problem in a very simple way.

[monetschemist]

Not sure how to explain the problem more simply, but I will try.

The Animals domain class describes an animal with its name, date of birth and parents (sire = father, dam = mother) which are also animals, so the domain class is self referential.

For example, given the data set up in the sample appliation, I can execute the following code

def animal = Animals.findByName('Society Lady')   // find the animal whose name is Society Lady
assert animal.name == 'Society Lady'   // verify the animal's name
assert animal.sire.name == 'Mr. Prospector'  // verify the animal's father's name
assert animal.dam.name == 'La Voyageuse'   // verify the animal's mother's name
assert animal.dam.sire.name == 'Tentam'   // verify the animal's mother's father's name (maternal grandfather)

I can filter the Animals domain class using DetachedCriteria and I can combine or chain several DetachedCriteria together to make more elaborate filters. The reason I am taking this approach is that I have a browser front-end for Animals that provides column-level filtering using JQuery and DataTables, which works well with a set of chained DetachedCriteria on the back end.

Generally this filtering approach works EXCEPT if I try to filter first by mother's name, then by maternal grandfather's name.

Following the example above, if I filter first by animal.dam.name = 'La Voyageuse' , then by animal.dam.sire.name == 'Tentam', I expect to have the animal whose name is Society Lady returned. Instead, nothing is returned.

If I reverse the order of the filtering, that is I filter first by animal.dam.sire.name == 'Tentam', then by animal.dam.name = 'La Voyageuse' , I get the result I expect - the animal whose name is Society Lady is returned.

Does this seem clear enough?

[monetschemist]

Hmm maybe not. Here's attempt number 2 at greater simplicity.

In the linked application:

• 'Mr. Prospector' is the father (or sire) of 'Society Lady'
• 'La Voyageuse' is the mother (or dam) of 'Society Lady';
• 'Tentam' is the father (or sire) of 'La Voyageuse'
• therefore 'Tentam' is father of the mother of 'Society Lady'

Two combined DetachedCriteria of the form

criteria = criteria.build {
    sire {
        ilike 'name','Mr. Prospector'
    }
}
criteria = criteria.build {
    dam {
        sire {
            ilike 'name','Tentam'
        }
    }
}

should therefore return the record for 'Society Lady', but they don't return anything.

If their order is reversed, they correctly return the record for 'Society Lady'.

(also pardon my ignorance but I don't know whether I can / should untag "Awaiting Feedback" or whether that's up to @puneetbehl).

[puneetbehl]

@monetschemist I am in the middle of completing some other times. I will come back to this as soon as I am done with those.

[monetschemist]

@puneetbehl I see you've removed the status: in progress, does this mean the problem is fixed (sorry for the dumb question)

[monetschemist]

@puneetbehl wondering if there's anything I can do to contribute to solving this problem?

[jeffscottbrown]

I see you've removed the status: in progress, does this mean the problem is fixed

No. It means no one is actively working on the issue right now. Once it is fixed the issue would be closed.