Skip to content

Grails 3.3 detached criteria appears to give incorrect result #14573

Open

Description

@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 apache/grails-data-mapping#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

Activity

  1. puneetbehl commented on May 9, 2018

    @puneetbehl
    Contributor

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

  2. monetschemist commented on May 9, 2018

    @monetschemist
    Author

    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?

  3. monetschemist commented on May 11, 2018

    @monetschemist
    Author

    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).

  4. changed the title [-]grails 3.3 detachedcriteria appears to give incorrect result[/-] [+]Grails 3.3 detached criteria appears to give incorrect result[/+] on May 14, 2018
  5. puneetbehl commented on May 14, 2018

    @puneetbehl
    Contributor

    @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.

  6. self-assigned this
    on May 22, 2018
  7. monetschemist commented on Jul 2, 2018

    @monetschemist
    Author

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

  8. monetschemist commented on Jul 17, 2018

    @monetschemist
    Author

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

  9. jeffscottbrown commented on Jul 17, 2018

    @jeffscottbrown
    Contributor

    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.

  10. borinquenkid commented on Nov 15, 2026

    @borinquenkid
    Member

    Hibernate 5 and in-memory part in #17009 (stacked on #16931); the Hibernate 7 part follows on 8.0.x.

  11. borinquenkid commented on Nov 15, 2026

    @borinquenkid
    Member

    Hibernate 7 part in #17010 (stacked on #16930).

  12. borinquenkid commented on Nov 15, 2026

    @borinquenkid
    Member

    Neo4j part in #17011 (stacked on #16836).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Type

No type

Projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions