likely hibernate/criteria bug in my domain; or how 'or' doesn't work in all cases

[Joe Little]

Much thanks to Burt, Shawn and others who helped me diagnose this and
devise a temporary work around. I'm indebted to your help. However, I
have likely touched on something that needs fixing at some level in
the grails stack.

I have a few domains with an association class:

class Faculty {
        String name
       
    static constraints = {
                name()
                assignments()
    }
       
        static hasMany = [assignments : Assignment]

}

class Student {
        String studentName
        Assignment advisorAssignment
        Date dateCreated
        Date lastUpdated
       
    static constraints = {
                studentName nullable : false, blank : false
                advisorAssignment (nullable: true)
                dateCreated nullable : true
                lastUpdated nullable : true
    }
}


class Assignment {
                Boolean planned
                Student advisee
                Faculty faculty
               

                static constraints = {
                        planned(nullable: true)
                        advisee(nullable: true)
                        faculty(nullable: true)
                }

                static mapping = {
                        advisee lazy:false
                        faculty lazy:false
                }
static belongtsTo = [Faculty, Student]
}

The idea is that Students get assigned Faculty members, and they
either don't yet have an assignment, or they have one, but it is
marked planned.

So, the query I've set up tests for (where, HQL, etc) is to find all
Students with that above condition, excluding those that "already
active" and thus planned=false.

        @Test
        void assignmentHQL() {
                def fullApplicants = Student.executeQuery(
                                "select s from Student as s inner join s.advisorAssignment a where
(s.advisorAssignment.faculty is null) or (s.advisorAssignment.planned
= true) order by s.studentName")
                assert fullApplicants.size() == 2
        }

    @Test
    void assignmentCriteriaPlannedOnly() {
        def applQuery = Student.where {
                        (advisorAssignment.faculty == null) || (advisorAssignment.planned == true)
                        }
                def Applicants = applQuery.list(sort:"studentName")
                assert Applicants.size() == 2
               
    }

In both cases, we end up with a cross join, and we hoped that the HQL
would resolve that. I only get back Students with "planned = true" and
not those lacking assignments. The strategy of having the two where
clauses without the '||' and the combining the results into a now
unsorted lists works, but the 'or' clause doesn't work.

At this point, the suspect is at the Hibernate level. Anything
obviously missing from the above?

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[Joe Little]

Resending ...


Begin forwarded message:

From: Joe Little <[hidden email]>
Date: August 1, 2012, 6:07:30 AM PDT
To: [hidden email]
Subject: likely hibernate/criteria bug in my domain; or how 'or' doesn't work in all cases

Much thanks to Burt, Shawn and others who helped me diagnose this and
devise a temporary work around. I'm indebted to your help. However, I
have likely touched on something that needs fixing at some level in
the grails stack.

I have a few domains with an association class:

class Faculty {
   String name
   
   static constraints = {
       name()
       assignments()
   }
   
   static hasMany = [assignments : Assignment]

}

class Student {
   String studentName
   Assignment advisorAssignment
   Date dateCreated
   Date lastUpdated
   
   static constraints = {
       studentName nullable : false, blank : false
       advisorAssignment (nullable: true)
       dateCreated nullable : true
       lastUpdated nullable : true
   }
}


class Assignment {
       Boolean planned
       Student advisee
       Faculty faculty
       

       static constraints = {
           planned(nullable: true)
           advisee(nullable: true)
           faculty(nullable: true)
       }

       static mapping = {
           advisee lazy:false
           faculty lazy:false
       }
static belongtsTo = [Faculty, Student]
}

The idea is that Students get assigned Faculty members, and they
either don't yet have an assignment, or they have one, but it is
marked planned.

So, the query I've set up tests for (where, HQL, etc) is to find all
Students with that above condition, excluding those that "already
active" and thus planned=false.

   @Test
   void assignmentHQL() {
       def fullApplicants = Student.executeQuery(
               "select s from Student as s inner join s.advisorAssignment a where
(s.advisorAssignment.faculty is null) or (s.advisorAssignment.planned
= true) order by s.studentName")
       assert fullApplicants.size() == 2
   }

   @Test
   void assignmentCriteriaPlannedOnly() {
       def applQuery = Student.where {
           (advisorAssignment.faculty == null) || (advisorAssignment.planned == true)
           }
       def Applicants = applQuery.list(sort:"studentName")
       assert Applicants.size() == 2
       
   }

In both cases, we end up with a cross join, and we hoped that the HQL
would resolve that. I only get back Students with "planned = true" and
not those lacking assignments. The strategy of having the two where
clauses without the '||' and the combining the results into a now
unsorted lists works, but the 'or' clause doesn't work.

At this point, the suspect is at the Hibernate level. Anything
obviously missing from the above?

[Joe Little]

This StackOverFlow link has the same case with HQL

http://stackoverflow.com/questions/3400772/hibernate-hql-strange-behavior-with-is-null

Appears that an left outer join needs to be invoked, but others note
that it doesn't work.

On Wed, Aug 1, 2012 at 6:07 AM, Joe Little <[hidden email]> wrote:

> Much thanks to Burt, Shawn and others who helped me diagnose this and
> devise a temporary work around. I'm indebted to your help. However, I
> have likely touched on something that needs fixing at some level in
> the grails stack.
>
> I have a few domains with an association class:
>
> class Faculty {
>         String name
>
>     static constraints = {
>                 name()
>                 assignments()
>     }
>
>         static hasMany = [assignments : Assignment]
>
> }
>
> class Student {
>         String studentName
>         Assignment advisorAssignment
>         Date dateCreated
>         Date lastUpdated
>
>     static constraints = {
>                 studentName nullable : false, blank : false
>                 advisorAssignment (nullable: true)
>                 dateCreated nullable : true
>                 lastUpdated nullable : true
>     }
> }
>
>
> class Assignment {
>                 Boolean planned
>                 Student advisee
>                 Faculty faculty
>
>
>                 static constraints = {
>                         planned(nullable: true)
>                         advisee(nullable: true)
>                         faculty(nullable: true)
>                 }
>
>                 static mapping = {
>                         advisee lazy:false
>                         faculty lazy:false
>                 }
> static belongtsTo = [Faculty, Student]
> }
>
> The idea is that Students get assigned Faculty members, and they
> either don't yet have an assignment, or they have one, but it is
> marked planned.
>
> So, the query I've set up tests for (where, HQL, etc) is to find all
> Students with that above condition, excluding those that "already
> active" and thus planned=false.
>
>         @Test
>         void assignmentHQL() {
>                 def fullApplicants = Student.executeQuery(
>                                 "select s from Student as s inner join s.advisorAssignment a where
> (s.advisorAssignment.faculty is null) or (s.advisorAssignment.planned
> = true) order by s.studentName")
>                 assert fullApplicants.size() == 2
>         }
>
>     @Test
>     void assignmentCriteriaPlannedOnly() {
>         def applQuery = Student.where {
>                         (advisorAssignment.faculty == null) || (advisorAssignment.planned == true)
>                         }
>                 def Applicants = applQuery.list(sort:"studentName")
>                 assert Applicants.size() == 2
>
>     }
>
> In both cases, we end up with a cross join, and we hoped that the HQL
> would resolve that. I only get back Students with "planned = true" and
> not those lacking assignments. The strategy of having the two where
> clauses without the '||' and the combining the results into a now
> unsorted lists works, but the 'or' clause doesn't work.
>
> At this point, the suspect is at the Hibernate level. Anything
> obviously missing from the above?

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

[ideasculptor]

On Fri, Aug 3, 2012 at 4:28 PM, Joe Little <[hidden email]> wrote:

This StackOverFlow link has the same case with HQL

http://stackoverflow.com/questions/3400772/hibernate-hql-strange-behavior-with-is-null

Appears that an left outer join needs to be invoked, but others note
that it doesn't work.

Have you tried it?  It doesn't look to me like that thread says it doesn't work.  It looks like someone just didn't implement the suggestion correctly, since the thread ends with a report that it works. I've used many left joins in HQL over the years and never had any trouble.

It is quite clear that if a student has no assignment, no query that doesn't include a left outer join to assignment could possibly return a student that has no assignment.  You could return students who have an assignment with null faculty and/or students with planned = true in an assignment, but those tests can only occur if there is an assignment to join to.  HQL translates to SQL in fairly obvious ways and the rules for joins between entities in HQL are the same as those for tables in SQL.  I see no reason why a left outer join to student.assignment wouldn't work, though your query would then have to include a test for (student.assignment is null or student.assignment.faculty is null or student.assignment.planned = true)

--not tested

select s from Student as s left outer join s.advisorAssignment a where s.advisorAssignment is null or s.advisorAssignment.faculty is null or s.advisorAssignment.planned = true order by s.studentName

you can probably shorten that to this:

select s from Student s left join s.advisorAssignment a where a is null or a.faculty is null or a.planned = true order by s.studentName

--sam

 

[Joe Little]

On Fri, Aug 3, 2012 at 10:19 PM, Samuel Gendler
<[hidden email]> wrote:

>
>
> On Fri, Aug 3, 2012 at 4:28 PM, Joe Little <[hidden email]> wrote:
>>
>> This StackOverFlow link has the same case with HQL
>>
>>
>> http://stackoverflow.com/questions/3400772/hibernate-hql-strange-behavior-with-is-null
>>
>> Appears that an left outer join needs to be invoked, but others note
>> that it doesn't work.
>
>
> Have you tried it?  It doesn't look to me like that thread says it doesn't
> work.  It looks like someone just didn't implement the suggestion correctly,
> since the thread ends with a report that it works. I've used many left joins
> in HQL over the years and never had any trouble.
>
> It is quite clear that if a student has no assignment, no query that doesn't
> include a left outer join to assignment could possibly return a student that
> has no assignment.  You could return students who have an assignment with
> null faculty and/or students with planned = true in an assignment, but those
> tests can only occur if there is an assignment to join to.  HQL translates
> to SQL in fairly obvious ways and the rules for joins between entities in
> HQL are the same as those for tables in SQL.  I see no reason why a left
> outer join to student.assignment wouldn't work, though your query would then
> have to include a test for (student.assignment is null or
> student.assignment.faculty is null or student.assignment.planned = true)
>
> --not tested
> select s from Student as s left outer join s.advisorAssignment a where
> s.advisorAssignment is null or s.advisorAssignment.faculty is null or
> s.advisorAssignment.planned = true order by s.studentName
>
> you can probably shorten that to this:
>
> select s from Student s left join s.advisorAssignment a where a is null or
> a.faculty is null or a.planned = true order by s.studentName
>
> --sam
>

Thanks. I did test this, and I had switched to forcing an assignment
to be created (with faculty == null), since with my tests and the
above, any record with "s.advisorAssignment is null" is NOT included
in the result set, so the query is still failing in that respect. When
Burt and I tried this it always was converted to a cross join.

Strangely, the first query is what I tried and that failed. Your
second query though does work. My guess is that the outer join is
incorrect. "select s from Student s left join s.advisorAssignment a
where a is null or  a.planned = true order by s.studentName" does what
I originally wanted, avoiding the need to pre-create an Assignment
record for every student.

Is there a way to get the same result in a criteria query/where query?
I can't find any syntax to enforce the same join using those. I've
tried setting the static mapping on the to-one associations to "fetch:
'join'" without affect.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email