@OneToOne query issue with OR and IS NULL

#1

Hello,

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

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

class B
{
    @Id
    @GeneratedValue
    private Long key;

    private int counter;

    @OneToOne
    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,

Patrick

 

 

#2

Your query:

SELECT FROM A
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.

Try LEFT OUTER JOIN:

SELECT a FROM A a LEFT JOIN a.another b
WHERE a.another IS NULL OR b.counter = 0
ObjectDB Support
#3

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

Reply