@OneToOne query issue with OR and IS NULL



I'm struggeling to get a query run right. I have 2 entities (simplified)

class A
    private Long key;
    @OneToOne(mappedBy = "other", cascade = CascadeType.ALL)    
    private B another

class B
    private Long key;

    private int counter;

    private A other;

so entity B is the owner. Now I persisted 2 entities of type A. In the first one, the member "another" is null... in the second
one "another" is set.

Now I want to query "select from A where another is null OR another.counter = 0

What I expected to get where the 2 entities I inserted before. But I always get just the entity A were "another" is not null.
Could someone help where to find my mistake?!

Many thanks in advance,





Your query:

WHERE another IS NULL OR another.counter = 0

is a short form of the this equivalent query:

SELECT a FROM A a JOIN a.another b
WHERE a.another IS NULL OR b.counter = 0

You missed the A instance with no another because navigation through a relationship is implemented using JOIN (i.e. INNER JOIN), and only the A instance that has another passes the FROM clause, which precedes the WHERE clause.


SELECT a FROM A a LEFT JOIN a.another b
WHERE a.another IS NULL OR b.counter = 0
ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)

Thank you very much... that works as expected!

Post Reply

To post a reply and/or subscribe to update notifications - please login