JPQL IS NOT NULL is returning objects with NULL

Question

I have the following JPQL query:

@Query("select p FROM Partner p where p.partnerIdentifier IS NOT NULL")
    List<Partner> findAfterDates();

This should return all Partner Entities that DO NOT have NULL for partnerIdentifier.

However, I am running the code and debugging, and I am seeing that the returned Collection contains entities that has null for this field.

Is this a bug in JPQL?

Μenelaοs · Accepted Answer · 2016-08-31 11:33:52Z

10

A colleague came by and suggested I use a join since the inner object was a one to one mapped object.

The following now returns correct results:

@Query("select p FROM Partner p join p.partnerIdentifier pi where pi is not null")
List<Partner> findAfterDates();

Once again a reminder that underneath hibernate we have a relational database and that something that logically should work using oop/jpql doesn't.

answered Aug 31, 2016 at 11:33

Μenelaοs

24.5k20 gold badges98 silver badges166 bronze badges

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

1 Comment

Viktor Reinok Over a year ago

In case You want to use IS NULL remember to use LEFT JOIN.

Lucas Amaral · Accepted Answer · 2024-11-06 19:22:07Z

-1

@Query("select p FROM Partner p JOIN p.partnerIdentifier")
    List<Partner> findAfterDates();

You don't need NOT NULL statement you'd just get null values if using LEFT JOIN

answered Nov 6, 2024 at 19:22

Lucas Amaral

616 bronze badges

Collectives™ on Stack Overflow

JPQL IS NOT NULL is returning objects with NULL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related