Hibernate: Conditional query not evaluated correctly

Question

I'm trying to get the following query to work in Hibernate:

SELECT m FROM MyEntity m WHERE m.owner = :user 
                         AND m.field1 IN (:field1Vals) 
                         AND m.field2 IN (:field2Vals) 
                         AND m.obj1 IS NOT NULL 
                         AND (
                                 m.obj1.count > 0
                             OR 
                                 (m.obj2 IS NOT NULL 
                                  AND m.obj2.count > 0)
                             ) 
                         ORDER BY m.createDate DESC

The problem is, it seems to always evaluate the part that goes:

AND (
        m.obj1.count > 0
    OR 
        (m.obj2 IS NOT NULL 
         AND m.obj2.count > 0)
)

...as:

AND (m.obj2 IS NOT NULL 
     AND m.obj2.count > 0)

In other words, it is only returning objects that satisfy the condition that follows the OR, and ignoring any results that satisfy the first condition. If I remove the condition following the OR, the query correctly brings back objects that satisfy the m.obj1.count > 0 condition.

I assume I've made some basic error in how I have structured my query, but I'm not sure what it might be. Any ideas?

Update

I've found a variant that works, it uses a self-join and adds some superfluous parenthesis:

SELECT DISTINCT m FROM MyEntity m, MyEntity m2 WHERE m.owner = :user 
                                               AND m.field1 IN (:field1Vals) 
                                               AND m.field2 in (:field2Vals) 
                                               AND m.obj1 IS NOT NULL 
                                               AND (
                                                       (m.obj1.count > 0) 
                                                   OR 
                                                       (m2.obj2 IS NOT NULL 
                                                        AND (m2.obj2.count > 0))
                                                   ) 
                                               ORDER BY m.createDate DESC"

This same query without the self-join does not work. Same goes for the seemingly superfluous parenthesis. With them, it returns an incorrect result.

So now my question is, why is it necessary to structure the query this way, both with respect to the self-join and the "superfluous" parenthesis?

It may be worth noting that obj1 and obj2 are different instances of the same foreign entity. So the table that I am querying is referencing two different rows in a single foreign table. I suspect that this is the reason why a self-join was necessary (and it is why I decided to try a self-join), but I'm not sure exactly what the reason behind it is. Any explanation would be welcome.

Randy · Accepted Answer · 2011-07-06 11:46:24Z

1

looks ok on inspection... you might try this verbose method:

SELECT m FROM MyEntity m WHERE m.owner = :user 
                         AND m.field1 IN (:field1Vals) 
                         AND m.field2 IN (:field2Vals) 
                         AND m.obj1 IS NOT NULL 
                         AND (
                                 m.obj1.count > 0
                             ) 
union
SELECT m FROM MyEntity m WHERE m.owner = :user 
                         AND m.field1 IN (:field1Vals) 
                         AND m.field2 IN (:field2Vals) 
                         AND m.obj1 IS NOT NULL 
                         AND      (m.obj2 IS NOT NULL 
                                  AND m.obj2.count > 0)
ORDER BY m.createDate DESC

answered Jul 6, 2011 at 11:46

Randy

16.7k2 gold badges40 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

aroth Over a year ago

Good idea, but unfortunately doesn't seem to work. Strangely this only brings back the results of the first query, and the ORDER BY is now being ignored. If I try the second query by itself, it works fine. Is it possible that something is causing Hibernate to bail out after the first query?

aroth Over a year ago

And perhaps worth noting, obj1 and obj2 both refer to the same foreign entity type (so they're referencing different rows in the same table). Could that be causing a problem?

aroth Over a year ago

I found a variant of the query that works. But I'd still like to know why. See the original post for details.

aroth · Accepted Answer · 2011-07-10 10:08:22Z

To get around this issue I ultimately had to revise my query as follows:

SELECT DISTINCT m FROM MyEntity m, MyEntity m2 WHERE m.owner = :user 
                                               AND m.field1 IN (:field1Vals) 
                                               AND m.field2 in (:field2Vals) 
                                               AND m.obj1 IS NOT NULL 
                                               AND (
                                                       (m.obj1.count > 0) 
                                                   OR 
                                                       (m2.obj2 IS NOT NULL 
                                                        AND (m2.obj2.count > 0))
                                                   ) 
                                               ORDER BY m.createDate DESC

I thank Randy for his very good suggestion, but in my specific case it did not work, and I had to rewrite my original query using a self-join in order to get the correct result back.

Collectives™ on Stack Overflow

Hibernate: Conditional query not evaluated correctly

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related